Fatal error (sintax error) after upgrading from 2.5.1 to 2.6.1 on insert mutation

[GiamBoscaro]

Version Information

Server Version:
2.6.1

Environment

Ubuntu 20

What is the expected behaviour?

Query should be working normally

Keywords

Fatal error, syntax error )

What is the current behaviour?

Getting fatal error

How to reproduce the issue?

mutation insertCustomerOneSocial(
              $email: String! ="[email protected]", 
              $password: String! = "randompsw", 
              $signupMethod: signup_methods_enum! = facebook, 
              $name: String = "Gastone",
              $phone: String, 
              $surname: String = "Birillo",
            ) {
              insert_customers_one(object: {
                meeting_type: talks,
                user: {data: {
                  email: $email, 
                  password: $password, 
                  role: customer, 
                  status: active, 
                  signup_method: $signupMethod, 
                  agreements: {data: {
                    base: true,
                    marketing: false, 
                    third_party: false,
                    profiling: false,
                  }}, 
                  registry: {data: {
                    name: $name, 
                    surname: $surname, 
                    phone: $phone,  
                    sex: male, # placeholder
                    birth_date: "1970-01-01" # placeholder  
                    address: {data: {}}   # all default or nullable values
                    birth_place: {data: {}}           # all default or nullable values   
                  }}
                }
              }}) {
                user {
                  id
                  email
                  role
                  status
                  signup_method
                  registry {
                    name
                    surname
                    phone
                  }                
                }
              }
            }

Please provide any traces or logs that could help here.

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "WITH \"addresses__mutation_result\" AS (INSERT INTO \"public\".\"addresses\" (  ) VALUES ()  RETURNING * , ('true')::boolean AS \"check__constraint\") SELECT  json_build_object('affected_rows', (SELECT  COUNT(*)  FROM \"addresses__mutation_result\"      ), 'returning_columns', (SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_1_e\"  FROM  (SELECT  (\"_0_root.base\".\"country\")::text AS \"country\", (\"_0_root.base\".\"zip\")::text AS \"zip\", (\"_0_root.base\".\"street\")::text AS \"street\", (\"_0_root.base\".\"city\")::text AS \"city\", (\"_0_root.base\".\"province\")::text AS \"province\", (\"_0_root.base\".\"id\")::text AS \"id\", (\"_0_root.base\".\"house_number\")::text AS \"house_number\"       ) AS \"_1_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"addresses__mutation_result\" WHERE ('true')     ) AS \"_0_root.base\"      ) AS \"_2_root\"      ) ) , (SELECT  coalesce(bool_and(\"check__constraint\" ), 'true' )  FROM \"addresses__mutation_result\"      )        ",
          "prepared": false,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "syntax error at or near \")\"",
            "status_code": "42601",
            "description": null
          },
          "arguments": []
        },
        "path": "$.selectionSet.insert_customers_one.args.object[0].user.data.registry.data.address.data",
        "code": "unexpected"
      },
      "message": "database query error"
    }
  ]
}

Any possible solutions?

No idea

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

Syntax error in insert query

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

Do not have the knowledge to fix and pr

[BenoitRanque]

I am able to trivially reproduce the issue. Give the following table:

CREATE TABLE test (
  id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

And the following mutation:

mutation Test {
  insert_test(objects: [{}]) {
    affected_rows
  }
}

We would expect to insert a row with the default value for an id, instead we get a syntax error.
Most likely related to #8443 which was included in the 2.6.1 release.

Explanation: the older hasura behavior was to explicitly insert DEFAULT into any columns that input was not provided for.
So for the above mutation the generated SQL would essentially be:

INSERT INTO test (id) VALUES (DEFAULT);

But this would often be very verbose, especially if input was only required for some columns.
It seems this was refactored to exclude columns input was not provided for. The issue being, if input is not provided for any column, this happens:

INSERT INTO test () VALUES ();

Which is not valid SQL.

[GiamBoscaro]

Well isn't the expected behavior to add default if no value is supplied? I am using default a lot and now it is not working anymore. I see issue #8443 could be related but it is marked as fixed in 2.6.1 while I do have a problem.
I do not understand what I am expected to do to make the mutations work with default values now.

[sassela]

@GiamBoscaro thanks for raising this bug report. The team's working on a fix as we speak and we'll update here as soon as it's released.

[GiamBoscaro]

@GiamBoscaro thanks for raising this bug report. The team's working on a fix as we speak and we'll update here as soon as it's released.

Oh ok, so it is a bug. I will wait for the next release then. Best regards!

[carlosbaraza]

I can confirm this regression is introduced in 2.5.2. 2.5.1 works fine.

[sassela]

Thanks for your patience! The fix for this regression is available from release v2.6.2 onwards. Please comment or reopen the issue if you have any problems after that.

[joserocha3]

@sassela from v2.7.0 (and possibly v2.6.2) passing more than one empty object results in a single record. On v2.6.0 it inserted two records.

Is a single record the expected functionality or is that a bug?

Mutation:

mutation InsertLabels {
  insert_labels(objects: [{}, {}]) {
    returning {
      id
    }
  }
}

Result:

{
  "data": {
    "insert_labels": {
      "returning": [
        {
          "id": "37980050-f057-4f5a-9057-bb3454896bfc",
        }
      ]
    }
  }
}

[BenoitRanque]

@joserocha3 can reproduce, this is a bug. Thank you for bringing it to our attention.
This may be due to using

INSERT INTO table_name DEFAULT VALUES RETURNING *

To cover this case. The above works for a single row, but not multiple.
This can be worked around by using the following statement instead (note the columns are not specified)

INSERT INTO table_name VALUES (DEFAULT), (DEFAULT) RETURNING *

[joserocha3]

@BenoitRanque thank you so much for confirming! Is a fix something we can expect to see soon?

[soupi]

@joserocha3 thank you for reporting the issue. I'm picking this up again. Hope to get a fix out soon.

[soupi]

A fix has been merged via ae0dae0 and will be available in the next release. In the meantime, you can try it out using the Docker image: hasurabuild/graphql-engine:mono-branch-dev-a4eebf7d7-main.