Event trigger creation fails on MSSQL tables with columns named after reserved words

[nakanoasaservice]

Version Information

Server Version: 2.34.0

Environment

OSS

What is the current behaviour?

When trying to create an Event Trigger in Hasura that targets a MSSQL table containing columns with reserved words (like group), the process fails. The error message indicates there's an "Incorrect syntax near the keyword 'group'."

What is the expected behaviour?

The creation of the Event Trigger should be successful even when the table columns have names that match reserved words.

How to reproduce the issue?

1. Create a MSSQL table with a column name that matches a SQL reserved word, for example group.
2. Connect Hasura to the MSSQL instance and the specific database.
3. Attempt to create an Event Trigger on the table that includes the column with the reserved word.

Screenshots or Screencast

Please provide any traces or logs that could help here.

{
  "error": "Inconsistent object: database query error",
  "path": "$.args[0].args",
  "code": "invalid-configuration",
  "internal": [
    {
      "definition": {
        "configuration": {
          "definition": {
            "enable_manual": false,
            "insert": {
              "columns": "*"
            },
            "update": {
              "columns": "*"
            }
          },
          "headers": [],
          "name": "test_trigger",
          "retry_conf": {
            "interval_sec": 10,
            "num_retries": 0,
            "timeout_sec": 60
          },
          "webhook": "http://localhost:10000"
        },
        "table": {
          "name": "has_order_table",
          "schema": "dbo"
        }
      },
      "message": {
        "exception": {
          "message": "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'group'.",
          "type": "unsuccessful_return_code"
        },
        "query": "CREATE OR ALTER TRIGGER [dbo].[notify_hasura_test_trigger_INSERT]\nON [dbo].[has_order_table]\nAFTER INSERT\n\nAS\nBEGIN\nDECLARE @json NVARCHAR(MAX)\nSET @json =  (\n  SELECT\n     id as [payload.data.new.id],  group as [payload.data.new.group], NULL as [payload.data.old],\n    'INSERT' as [payload.op],\n    'dbo' as [schema_name],\n    'has_order_table' as [table_name],\n    'test_trigger' as [trigger_name]\n  FROM INSERTED\n  FOR JSON PATH, INCLUDE_NULL_VALUES\n)\n\nINSERT INTO hdb_catalog.event_log (schema_name,table_name,trigger_name,payload)\nSELECT * FROM OPENJSON (@json)\nWITH(\n  schema_name NVARCHAR(MAX) '$.schema_name',\n  table_name NVARCHAR(MAX) '$.table_name',\n  trigger_name NVARCHAR(MAX) '$.trigger_name',\n  [payload] NVARCHAR(MAX) AS JSON\n)\n\nEND;\n"
      },
      "name": "event_trigger test_trigger in table m_user in source main",
      "reason": "Inconsistent object: database query error",
      "type": "event_trigger"
    }
  ]
}

Any possible solutions/workarounds you're aware of?

One potential workaround could be to avoid using reserved words for column names in the database. However, this isn't always feasible, especially when integrating Hasura with existing databases. Ideally, Hasura should handle these scenarios natively.

A pull request addressing this issue is currently in progress: Hasura Pull Request #9926

Keywords

Hasura, MSSQL, Event Trigger, reserved word, Incorrect syntax