Error when requesting upon a data type that is not in the public schema

[florence-henry]

Version Information

Server Version: v2.0.9
CLI Version (for CLI related issue): 2.0.9

Environment

Running into a local docker

FROM hasura/graphql-engine:v2.0.9.cli-migrations-v2

What is the expected behaviour?

Query a table with a column which type is a data type created by CREATE TYPE and stored in a schema which is not the public one

Keywords

hasura query type schema "does not exist"

What is the current behaviour?

The query fails with type xxx does not exist

How to reproduce the issue?

1. Database setup

CREATE schema pipeline;
CREATE TYPE compfoo_public AS ENUM ('a', 'b', 'c');
CREATE TYPE pipeline.compfoo_pipeline AS ENUM ('a', 'b', 'c');
CREATE TABLE pipeline.test_compfoo (c1 compfoo_public, c2 compfoo_pipeline);
INSERT INTO  pipeline.test_compfoo values ('a', 'b'), ('b', 'c'), ('c', 'c');

2. Go to Hasura DATA and track the table test_compfoo into the schema pipeline:
   Table test_compfooin Hasura :

3. Go to Hasura API
4. A simple query is working:

query MyQuery {
  pipeline_test_compfoo {
    c1
    c2
  }
}

Response is ok:

5. Querying upon c1 column (data type in the public schema) is also ok :

query MyQuery {
  pipeline_test_compfoo(
    where: {c1: {_eq: "b"}}
  ) {
    c1
    c2
  }
}

Response is also ok:

6. Querying upon c2 column (data type NOT in the public schema) is failing :

query MyQuery {
  pipeline_test_compfoo(
    where: {c2: {_eq: "b"}}
  ) {
    c1
    c2
  }
}

Response is

{
  "errors": [
    {
      "extensions": {
        "path": "$",
        "code": "constraint-error"
      },
      "message": "type \"compfoo_pipeline\" does not exist"
    }
  ]
}

Screenshots or Screencast

See screenshots included above

Please provide any traces or logs that could help here.

With the query on c1, which is successful, click on Analyzing gives the following SQL request:

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_1_e"
          FROM
            (
              SELECT
                "_0_root.base"."c1" AS "c1",
                "_0_root.base"."c2" AS "c2"
            ) AS "_1_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "pipeline"."test_compfoo"
        WHERE
          (
            ("pipeline"."test_compfoo"."c1") = (('b') :: compfoo_public)
          )
      ) AS "_0_root.base"
  ) AS "_2_root";

For the failing request, clicking on "Analyzing" just show the error type "compfoo_pipeline" does not exist

Suggestion : it should be useful to see the SQL resquest, even if it fails

In the server log I see however the request :

hasura_1    | {"type":"query-log","timestamp":"2021-10-05T15:26:21.700+0000","level":"info","detail":
{"kind":"database","request_id":"081269a8-248d-4051-93d6-818e51a76d00","generated_sql":
{"pipeline_test_compfoo":{"prepared_arguments":[[2,"b"]],"query":"SELECT  coalesce(json_agg(\"root\" ), '[]' ) 
AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_1_e\"  FROM  (SELECT  \"_0_root.base\".\"c1\" 
AS \"c1\", \"_0_root.base\".\"c2\" AS \"c2\"       ) AS \"_1_e\"      ) ) AS \"root\" FROM  (SELECT  *  
FROM \"pipeline\".\"test_compfoo\"  WHERE ((\"pipeline\".\"test_compfoo\".\"c2\") = (($2)::compfoo_pipeline))) 
AS \"_0_root.base\"      ) AS \"_2_root\"      "}},"query": {"operationName":"MyQuery","query":"query MyQuery 
{\n  pipeline_test_compfoo(where: {c2: {_eq: \"b\"}}) {\n    c1\n    c2\n }\n}\n"}}}

which is this SQL request :

SELECT
  coalesce(json_agg("root"), '[]') AS "root"
FROM
  (
    SELECT
      row_to_json(
        (
          SELECT
            "_1_e"
          FROM
            (
              SELECT
                "_0_root.base"."c1" AS "c1",
                "_0_root.base"."c2" AS "c2"
            ) AS "_1_e"
        )
      ) AS "root"
    FROM
      (
        SELECT
          *
        FROM
          "pipeline"."test_compfoo"
        WHERE
          (
            ("pipeline"."test_compfoo"."c2") = (('b') :: compfoo_pipeline)
          )
      ) AS "_0_root.base"
  ) AS "_2_root";

Indeed, when I make this request in the SQL server, I get

ERROR:  type "compfoo_pipeline" does not exist
LINE 26: ...     ("pipeline"."test_compfoo"."c2") = (('b') :: compfoo_pi...
                                                              ^

If I add the schema name when using the cast :: compfoo_pipeline :

        SELECT
          *
        FROM
          "pipeline"."test_compfoo"
        WHERE
          (
            ("pipeline"."test_compfoo"."c2") = (('b') :: pipeline.compfoo_pipeline)
          );

I get the expected result :

         root          
-----------------------
 [{"c1":"a","c2":"b"}]
(1 row)

Any possible solutions?

In my database, if I set the search_path variable to include the pipeline schema, request is OK without prefixing the data type :

SET search_path = public,pg_catalog,pipeline;
SET
SELECT ... -- big request above
         root          
-----------------------
 [{"c1":"a","c2":"b"}]

I see then 2 ways :

• prefixing all the datatypes with their own schema
• setting the search_path to all the tracked schemas

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?

If I can identify the location in the source code, I may try to search a solution, but I'm not sure to succeed

[florence-henry]

Precision : this is happening with a Postgresql server (v11)

[florence-henry]

Seems to be a duplicate of #4630

[florence-henry]

This could be a workaround :

#4630 (comment)