Subscriptions fail if more then 2-3 fields selected on any table [2.4.0-beta.1] #8267
Description
Version Information
Server Version: 2.4.0-beta.1
Environment
SQL Server DB
Using 2.4.0-beta.1 Docker Image
This is super easy to repro. Use the API explorer, pick any table, and try to create a subscription with > 4 or so fields.
I have noticed some slight variation as to when a subscription will fail depending on the table.
Downgraded to 2.3.0 main release and error is not occurring. Seems limited to 2.4.0-beta.1
Here's some examples:
Example:
(works the same way with several tables so showing this one for simplicity)
Works with 2 fields:
Fails with 3 fields:
Changed subscription to query and it works:
GraphQL Query that fails
subscription MySubscription {
Users {
Id
UserName
Email
}
}Error in Console:
{
"data": null,
"errors": [
{
"extensions": {
"path": "$",
"code": "unexpected"
},
"message": "database query error"
}
]
}Generated SQL (taken from Analyze button modal in Console):
SELECT
[row].[result_id] AS [result_id],
[result].[json] AS [result]
FROM
OPENJSON(
(
N'' + NCHAR(91) + '' + NCHAR(91) + '' + NCHAR(34) + '00000000-0000-0000-0000-000000000000' + NCHAR(34) + ',' + NCHAR(123) + '' + NCHAR(34) + 'synthetic' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(91) + '' + NCHAR(93) + ',' + NCHAR(34) + 'query' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + ',' + NCHAR(34) + 'session' + NCHAR(34) + '' + NCHAR(58) + '' + NCHAR(123) + '' + NCHAR(125) + '' + NCHAR(125) + '' + NCHAR(93) + '' + NCHAR(93) + ''
)
) WITH (
[result_id] UNIQUEIDENTIFIER '$[0]',
[result_vars] NVARCHAR(MAX) '$[1]' AS JSON
) AS [row]
OUTER APPLY (
SELECT
(
SELECT
ISNULL(
(
SELECT
[t_OPR_SECD1].[Id] AS [Id],
[t_OPR_SECD1].[UserName] AS [UserName],
[t_OPR_SECD1].[Email] AS [Email]
FROM
[dbo].[OPR_SECD] AS [t_OPR_SECD1] FOR JSON PATH,
INCLUDE_NULL_VALUES
),
(N'' + NCHAR(91) + '' + NCHAR(93) + '')
) AS [root]
) AS [Users] FOR JSON PATH,
INCLUDE_NULL_VALUES,
WITHOUT_ARRAY_WRAPPER
) AS [result]([json]) FOR JSON PATH,
INCLUDE_NULL_VALUESI copied that SQL into Azure Data Studio and executed it without any errors. It outputted a single column with JSON. I went a step further and used JSON.parse() in the Chrome console to make sure the JSON string was parseable and valid and it seemed to work fine and outputted the expected object.