graphql-engine: Can't sort by multiple nested fields

query

query MyQuery {
  object(where: {id: {_eq: "1"}}) {
    objectStages {
      objectStageSections(order_by: [
          {stageSection: {order: asc}}
          {stageSection: {name: asc}}
        ]) {
        stageSection {
          name
          code
          id
        }
      }
    }
  }
}

gives error

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_12_e\"  FROM  (SELECT  \"_11_root.ar.root.objectStages\".\"objectStages\" AS \"objectStages\"       ) AS \"_12_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"object\"  WHERE ((\"public\".\"object\".\"id\") = ($2))     ) AS \"_0_root.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"objectStages\" ), '[]' ) AS \"objectStages\" FROM  (SELECT  row_to_json((SELECT  \"_9_e\"  FROM  (SELECT  \"_8_root.ar.root.objectStages.ar.objectStages.objectStageSections\".\"objectStageSections\" AS \"objectStageSections\"       ) AS \"_9_e\"      ) ) AS \"objectStages\" FROM  (SELECT  *  FROM \"public\".\"objectStage\"  WHERE ((\"_0_root.base\".\"id\") = (\"objectId\"))     ) AS \"_1_root.ar.root.objectStages.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"objectStageSections\" ORDER BY \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" ASC NULLS LAST, \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" ASC NULLS LAST), '[]' ) AS \"objectStageSections\" FROM  (SELECT  \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\", row_to_json((SELECT  \"_6_e\"  FROM  (SELECT  \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"stageSection\" AS \"stageSection\"       ) AS \"_6_e\"      ) ) AS \"objectStageSections\", \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" FROM  (SELECT  *  FROM \"public\".\"objectStageSection\"  WHERE ((\"_1_root.ar.root.objectStages.base\".\"id\") = (\"objectStageId\"))     ) AS \"_2_root.ar.root.objectStages.ar.objectStages.objectStageSections.base\" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  \"_4_e\"  FROM  (SELECT  \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"name\" AS \"name\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"code\" AS \"code\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"id\" AS \"id\"       ) AS \"_4_e\"      ) ) AS \"stageSection\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"name\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"id\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" FROM  (SELECT  *  FROM \"public\".\"stageSection\"  WHERE ((\"_2_root.ar.root.objectStages.ar.objectStages.objectStageSections.base\".\"stageSectionId\") = (\"id\"))     ) AS \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\"      ) AS \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\" ON ('true')    ORDER BY \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" ASC NULLS LAST, \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" ASC NULLS LAST  ) AS \"_7_root.ar.root.objectStages.ar.objectStages.objectStageSections\"      ) AS \"_8_root.ar.root.objectStages.ar.objectStages.objectStageSections\" ON ('true')      ) AS \"_10_root.ar.root.objectStages\"      ) AS \"_11_root.ar.root.objectStages\" ON ('true')      ) AS \"_13_root\"      ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "column reference \"root.ar.root.objectStages.ar.objectStages.objectStageSections.o\" is ambiguous",
            "status_code": "42702",
            "description": null
          },
          "arguments": [
            "(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"admin\\\"}\",Binary))",
            "(Oid 0,Just (\"1\",Text))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "postgres query error"
    }
  ]
}

removing one of order rules gives normal result

query MyQuery {
  object(where: {id: {_eq: "1"}}) {
    objectStages {
      objectStageSections(order_by: [
          {stageSection: {order: asc}}
        ]) {
        stageSection {
          name
          code
          id
        }
      }
    }
  }
}

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Reactions: 13
  • Comments: 21 (3 by maintainers)

Most upvoted comments

Yeah just to be clear I can reliably reproduce this with a brand new Hasura instance and the schema posted above.

I don’t believe it has anything to do with changing relation settings or names of fields

Hey folks. Thanks again for your patience. We have merged a fix for this bug at https://github.com/hasura/graphql-engine/commit/ee15c804bc8f46fb2c9c0267200f29fe1ba49785 and it will be available in the next stable release within two weeks. In the meantime, you can try it out using the Docker image hasurabuild/graphql-engine:mono-branch-dev-58e5e6a-main.ubuntu.