Graphql-engine: Database query error for object relationship with permission limit

[jcmoore0]

Team - I am getting a strange error on v1.3.0-beta.2.

{
  "errors": [
    {
      "extensions": {
        "path": "$",
        "code": "unexpected"
      },
      "message": "database query error"
    }

Additional Information:

Error occurs on v1.3.0-beta.2 - error does not occur on v1.2.1
Problem goes away when: 1) remove the order by, or 2) remove the 'x-hasura-role header

Database log states: ERROR: column _5_root.or.category.root.or.category.pg.id does not exist at character 119
HINT: Perhaps you meant to reference the column "_2_root.or.category.root.or.category.pg.id".

query topic {
  topic (order_by: { category: {importance: desc}}) {
      {
       category {
           id
      }
  }
}

Note: This is a 'approximation' of the actual query.
It looks like some indexing issue (eg. _5_root vs _2_root).

I am still investigating deeper.
I would appreciate any suggestions to help resolve.

[tirumaraiselvan]

@jcmoore0 Could you share the schema for the two tables in question: topic and category ?

[jcmoore0]

Hi @tirumaraiselvan - I altered the relationship names but the schema is like this:

topic   (view) 
    category         object relationship  (category_id -> id)
    other               object relationship  (other_id ->id)

Note:

1. When I remove the 'x-hasura-role', the query works (x1 LEFT OUTER JOIN LATERAL)
2. When I add the 'x-hasura-role', the query fails (x2 LEFT OUTER JOIN LATERAL ... see below)
   In v1.2.1, this query works and only as 1 LEFT OUTER JOIN LATERAL
3. I changed row select permissions to "without any checks" on topic and category, but this did not help.

The order by column is referencing the wrong LEFT JOIN.

SELECT  
    coalesce(json_agg("root" ORDER BY "root.or.category.pg.importance" DESC NULLS FIRST), '[]' ) AS "root" 
FROM 
    (
    SELECT  
        "_5_root.or.category"."root.or.category.pg.importance" AS "root.or.category.pg.importance", 
        row_to_json
        ((
            SELECT  "_6_e"  FROM  
                (
                SELECT  "_5_root.or.category"."category" AS "category"       
                ) AS "_6_e"      
        )) AS "root" 
    FROM  
        (
        SELECT  *  
        FROM "public"."topic"  
        WHERE ('true')     
        ) AS "_0_root.base" 
        LEFT OUTER JOIN LATERAL 
            (
            SELECT  
                "_1_root.or.category.base"."importance" AS "root.or.category.pg.importance" 
            FROM  
                (
                SELECT  *  
                FROM "public"."category"  
                WHERE (("_0_root.base"."category_id") = ("id"))     
                ) AS "_1_root.or.category.base"   
            ) AS "_2_root.or.category" ON ('true') 
        LEFT OUTER JOIN LATERAL 
            (
            SELECT  row_to_json
                ((
                SELECT  "_4_e"  
                FROM  
                    (
                    SELECT  "_3_root.or.category.base"."id" AS "id"       
                    ) AS "_4_e"      
                )) AS "category" 
            FROM  
            (
            SELECT  *  
                FROM "public"."category"  
                WHERE (("_0_root.base"."category_id") = ("id"))     
            ) AS "_3_root.or.category.base"     
            LIMIT 10000 
            ) AS "_5_root.or.category" ON ('true')    
    ORDER BY "root.or.category.pg.importance" DESC 
    NULLS FIRST 
    LIMIT 10000 
    ) AS "_7_root"

Let me know if this helps.
Thanks!
John

[tirumaraiselvan]

@jcmoore0 I tried to repro this with a similar table structure but did not find this error. Hence, your table schema might be crucial to reproduce this. Could you send the full table schemas for both?

[jcmoore0]

Hi @tirumaraiselvan - After trying to reproduce, I found:

The error does not occur when I remove the limit=10000 from the row select permission for role.

Can you try adding a limit to see if you can reproduce the error?

Thanks,
John

[tirumaraiselvan]

@jcmoore0 Yes, I can now reproduce this. Thanks for the report.

create table category (id serial primary key, name text);

create table topic (id serial primary key, title text, category_id integer, foreign key (category_id) references category (id));

Track and add all relationships.

Create role user with following permissions:

1. select permission "Without any check" + Row limit: 100 on category table.
2. select permission "Without any check" on topics table.

Run the query as role user:

query  {
  topic(order_by: {category: {name: asc}}) {
    category_id
    id
    title
    category {
      id
      name
    }
  }
}

Result: Database error.