Hello,

we are seeing the below error when we use the _exists operator to set a permission rule

Add custom check in permission table:

{"_exists":{"_where":{"_and":[{"fldCrewID":{"_eq":"X-Hasura-User-Id"}},{"fldNoRights":{"_eq":0}}]},"_table":{"schema":"dbo","name":"tblSettingsPersonal"}}}

Run this query:

query MyQuery { Equipment { Amount } }

It fails with this error when we use the _exists operator to set a permission rule:

{ "errors": [ { "extensions": { "internal": { "tag": "unsuccessful_return_code", "contents": [ "odbc_SQLExecDirectW", -1, "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'fldCrewID'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'fldCrewID'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'fldNoRights'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'fldNoRights'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'fldNoRights'." ] }, "path": "$", "code": "unexpected" }, "message": "sql server exception" } ] }

The generated SQL is:

SELECT ISNULL((SELECT [t_Equipment1].[Amount] AS [Amount], [t_Equipment1].[ArrangementNum] AS [ArrangementNum], FROM [dbo].[Equipment] AS [t_Equipment1] WHERE ((EXISTS (SELECT 1 AS [exists_placeholder] FROM [dbo].[tblSettingsPersonal] AS [t_tblSettingsPersona1] WHERE ((((([t_Equipment1].[fldCrewID]) = (('123'))) OR ((([t_Equipment1].[fldCrewID]) IS NULL) AND ((('123')) IS NULL)))))))) FOR JSON PATH), '[]')

Below are the steps we did:

1. check permission table, noticed that the no schema shows in the schema list, but the schema name 'dto' shows in the expression area

2. check raw query which causes the error from hasura console, noticed that the table name is wrong from WHERE keyword, should be [t_tblSettingsPersona1].[fldCrewID] instead of [t_Equipment1].[fldCrewID]

FROM [dbo].[tblSettingsPersonal] AS [t_tblSettingsPersona1] WHERE ((((([t_Equipment1].[fldCrewID]) = (('123'))) OR ((([t_Equipment1].[fldCrewID]) IS NULL) AND ((('123')) IS NULL)))))))) FOR JSON PATH), '[]')

Does anyone have any idea how to fix this issue?

Appreciate it!