Subscription in SQL Server using where on date field doesn't work

[lucasmpr]

Version Information

Server Version: 2.37.1

Environment

OSS

What is the current behaviour?

All subscriptions with a filter on a date field doesn't work on SQL Server 2019

subscription AAAA {
  exampleTable(where: {dateField: {_eq: "2024-03-01"}}) {
    id
  }
}

This returns:

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

What is the expected behaviour?

I hoped it would work.

How to reproduce the issue?

Using SQL Server 2019

1. Create a table with a date field
2. Do a subscription with a where condition in this date field
3. It should break instantly.
4. The same query should work using a query field.

Please provide any traces or logs that could help here.

I've tracked down a little bit and I understand why this is happenning.

This is the generated query, by using the explain button on the console (i've manually renamed the table names from my own instance):

SELECT
  [row].[result_id] AS [result_id],
  [result].[json] AS [result]
FROM
  OPENJSON(
    CAST(
      N'' + NCHAR(91) + '' + NCHAR(91) + '' + NCHAR(34) + '00000000-0000-0000-0000-000000000000' + NCHAR(34) + ',' + NCHAR(123) + '' + NCHAR(34) + 'cursor' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'query' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'session' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'synthetic' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(91) + '' + NCHAR(34) + '' + NCHAR(39) + '2024-03-01' + NCHAR(39) + '' + NCHAR(34) + '' + NCHAR(93) + '' + NCHAR(125) + '' + NCHAR(93) + '' + NCHAR(93) + '' AS NVARCHAR(MAX)
    )
  ) WITH (
    [result_id] uniqueidentifier '$[0]',
    [result_vars] NVARCHAR(MAX) '$[1]' AS JSON
  ) AS [row]
  OUTER APPLY (
    SELECT
      (
        SELECT
          ISNULL(
            (
              SELECT
                [t_exampleTable1].[id] AS [id]
              FROM
                [dbo].[exampleTable] AS [t_exampleTable1]
              WHERE
                (
                  (
                    (
                      ( [t_exampleTable1].[dateField]) = (
                        JSON_VALUE(
                          [row].[result_vars],
                          CAST(
                            N'' + NCHAR(36) + '.' + NCHAR(34) + 'synthetic' + NCHAR(34) + '' + NCHAR(91) + '0' + NCHAR(93) + '' AS NVARCHAR(MAX)
                          )
                        )
                      )
                    )
                  )
                ) FOR JSON PATH,
                INCLUDE_NULL_VALUES
            ),
            CAST(N'' + NCHAR(91) + '' + NCHAR(93) + '' AS NVARCHAR(MAX))
          ) AS [root]
      ) AS [exampleTable] FOR JSON PATH,
      INCLUDE_NULL_VALUES,
      WITHOUT_ARRAY_WRAPPER
  ) AS [result]([json]) FOR JSON PATH,
  INCLUDE_NULL_VALUES

I've compared it with a query that uses a non-date field as filter

And the difference is this:

N'' + NCHAR(91) + '' + NCHAR(91) + '' + NCHAR(34) + '00000000-0000-0000-0000-000000000000' + NCHAR(34) + ',' + NCHAR(123) + '' + NCHAR(34) + 'cursor' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'query' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'session' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'synthetic' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(91) + '' + NCHAR(34) + '' + NCHAR(39) + '2024-03-01' + NCHAR(39) + '' + NCHAR(34) + '' + NCHAR(93) + '' + NCHAR(125) + '' + NCHAR(93) + '' + NCHAR(93) + '' AS NVARCHAR(MAX)

Which renders to:

[["00000000-0000-0000-0000-000000000000",{"cursor":{},"query":{},"session":{},"synthetic":["'2024-03-01'"]}]]

For comparaison, using another field in the filter I get this:

[["00000000-0000-0000-0000-000000000000",{"cursor":{},"query":{},"session":{},"synthetic":["10"]}]]

The only difference between the two of them is:

NCHAR(34) + '' + NCHAR(39) + '2024-03-01' + NCHAR(39) + '' + NCHAR(34)

NCHAR(34) + '10' + NCHAR(34)

So date fields here are getting single quotes while other fields don't.
The generated query starts working when I erase the two rogue NCHAR(39).
So I'm thinking the problem is some string parsing/filtering that is failing for dates and letting the single quotes there.

I have no idea if there's a setting in SQL Server that can make this work. I found it a little bit weird I'm the first one reporting this. I've tried with multiple tables and the same result.

In postgres this works properly.

Any possible solutions/workarounds you're aware of?

I didn't found a workaround yet.

Keywords

sql server mssql date subscription error

[mateusgoncalonascimento]

Same problem.