GraphQL aliasing causes "database query error"

[noverby]

Version Information

Server Version: Hasura v2.8.3

Environment

nhost v2.0

What is the current behaviour?

The query:

query {
  nodes {
    relations_bcdb8_910e3: relations {
      node {
        children(order_by: [{ data: asc }, { createdAt: asc }]) {
          id
        }
      }
    }
  }
}

Outputs:

{
  "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.relations_bcdb8_910e3\".\"relations_bcdb8_910e3\" AS \"relations_bcdb8_910e3\"       ) AS \"_12_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"nodes\"  WHERE ('true')     ) AS \"_0_root.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"relations_bcdb8_910e3\" ), '[]' ) AS \"relations_bcdb8_910e3\" FROM  (SELECT  row_to_json((SELECT  \"_9_e\"  FROM  (SELECT  \"_8_root.ar.root.relations_bcdb8_910e3.or.node\".\"node\" AS \"node\"       ) AS \"_9_e\"      ) ) AS \"relations_bcdb8_910e3\" FROM  (SELECT  *  FROM \"public\".\"relations\"  WHERE ((\"_0_root.base\".\"id\") = (\"parent_id\"))     ) AS \"_1_root.ar.root.relations_bcdb8_910e3.base\" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  \"_7_e\"  FROM  (SELECT  \"_6_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children\".\"children\" AS \"children\"       ) AS \"_7_e\"      ) ) AS \"node\" FROM  (SELECT  *  FROM \"public\".\"nodes\"  WHERE ((\"_1_root.ar.root.relations_bcdb8_910e3.base\".\"node_id\") = (\"id\"))    LIMIT 1 ) AS \"_2_root.ar.root.relations_bcdb8_910e3.or.node.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"children\" ORDER BY \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data\" ASC NULLS LAST, \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at\" ASC NULLS LAST), '[]' ) AS \"children\" FROM  (SELECT  \"_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base\".\"created_at\" AS \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at\", row_to_json((SELECT  \"_4_e\"  FROM  (SELECT  \"_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base\".\"id\" AS \"id\"       ) AS \"_4_e\"      ) ) AS \"children\", \"_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base\".\"data\" AS \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data\" FROM  (SELECT  *  FROM \"public\".\"nodes\"  WHERE ((\"_2_root.ar.root.relations_bcdb8_910e3.or.node.base\".\"id\") = (\"parent_id\"))   ORDER BY \"data\" ASC NULLS LAST, \"created_at\" ASC NULLS LAST  ) AS \"_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base\"    ORDER BY \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data\" ASC NULLS LAST, \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at\" ASC NULLS LAST  ) AS \"_5_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children\"      ) AS \"_6_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children\" ON ('true')      ) AS \"_8_root.ar.root.relations_bcdb8_910e3.or.node\" ON ('true')      ) AS \"_10_root.ar.root.relations_bcdb8_910e3\"      ) AS \"_11_root.ar.root.relations_bcdb8_910e3\" ON ('true')      ) AS \"_13_root\"      ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "ORDER BY \"root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.\" is ambiguous",
            "status_code": "42702",
            "description": null
          },
          "arguments": [
            "(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"admin\\\",\\\"x-hasura-user-id\\\":\\\"<id>\\\"}\",Binary))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "database query error"
    }
  ]
}

What is the expected behaviour?

That the query actually runs.

If I change the alias (relations_bcdb8_910e3) to something shorter than 21 characters, then the query runs without any problems.

Please provide any traces or logs that could help here.

The SQL query:

SELECT coalesce(json_agg("root"), '[]') AS "root"
FROM (
    SELECT row_to_json(
        (
          SELECT "_12_e"
          FROM (
              SELECT "_11_root.ar.root.relations_bcdb8_910e3"."relations_bcdb8_910e3" AS "relations_bcdb8_910e3"
            ) AS "_12_e"
        )
      ) AS "root"
    FROM (
        SELECT *
        FROM "public"."nodes"
        WHERE ('true')
      ) AS "_0_root.base"
      LEFT OUTER JOIN LATERAL (
        SELECT coalesce(json_agg("relations_bcdb8_910e3"), '[]') AS "relations_bcdb8_910e3"
        FROM (
            SELECT row_to_json(
                (
                  SELECT "_9_e"
                  FROM (
                      SELECT "_8_root.ar.root.relations_bcdb8_910e3.or.node"."node" AS "node"
                    ) AS "_9_e"
                )
              ) AS "relations_bcdb8_910e3"
            FROM (
                SELECT *
                FROM "public"."relations"
                WHERE (("_0_root.base"."id") = ("parent_id"))
              ) AS "_1_root.ar.root.relations_bcdb8_910e3.base"
              LEFT OUTER JOIN LATERAL (
                SELECT row_to_json(
                    (
                      SELECT "_7_e"
                      FROM (
                          SELECT "_6_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children"."children" AS "children"
                        ) AS "_7_e"
                    )
                  ) AS "node"
                FROM (
                    SELECT *
                    FROM "public"."nodes"
                    WHERE (
                        (
                          "_1_root.ar.root.relations_bcdb8_910e3.base"."node_id"
                        ) = ("id")
                      )
                    LIMIT 1
                  ) AS "_2_root.ar.root.relations_bcdb8_910e3.or.node.base"
                  LEFT OUTER JOIN LATERAL (
                    SELECT coalesce(
                        json_agg(
                          "children"
                          ORDER BY "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data" ASC NULLS LAST,
                            "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at" ASC NULLS LAST
                        ),
                        '[]'
                      ) AS "children"
                    FROM (
                        SELECT "_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base"."created_at" AS "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at",
                          row_to_json(
                            (
                              SELECT "_4_e"
                              FROM (
                                  SELECT "_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base"."id" AS "id"
                                ) AS "_4_e"
                            )
                          ) AS "children",
                          "_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base"."data" AS "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data"
                        FROM (
                            SELECT *
                            FROM "public"."nodes"
                            WHERE (
                                (
                                  "_2_root.ar.root.relations_bcdb8_910e3.or.node.base"."id"
                                ) = ("parent_id")
                              )
                            ORDER BY "data" ASC NULLS LAST,
                              "created_at" ASC NULLS LAST
                          ) AS "_3_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.base"
                        ORDER BY "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.data" ASC NULLS LAST,
                          "root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children.pg.created_at" ASC NULLS LAST
                      ) AS "_5_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children"
                  ) AS "_6_root.ar.root.relations_bcdb8_910e3.or.node.ar.node.children" ON ('true')
              ) AS "_8_root.ar.root.relations_bcdb8_910e3.or.node" ON ('true')
          ) AS "_10_root.ar.root.relations_bcdb8_910e3"
      ) AS "_11_root.ar.root.relations_bcdb8_910e3" ON ('true')
  ) AS "_13_root"

Any possible solutions?

No

Can you identify the location in the source code where the problem exists?

No

If the bug is confirmed, would you be willing to submit a PR?

No, I'm not familiar with the Hasura code base.

Keywords

aliasing alias order_by database query error

[sourceful-karlson]

I see this on the cloud hasura v2.10.0 too, this is an urgent bug for us

[tomhasura]

@sourceful-karlson - Can you please share the error which you are getting on 2.10.0?

[sourceful-karlson]

we have asked the Hasura cloud team to downgrade to 2.9.0 and it has fixed the issue.

it has something to do with the length of your table name and the length of your schema name. If it is longer than certain charactoers, it is trunscated and the insert query doesn not work.

[rikinsk]

Fixed via https://github.com/hasura/graphql-engine/commit/e626d87a3c4bcf04361ce1ee2f4a02bbb49000fc and released in v2.10.1