hasura / hasura/graphql-engine
Invalid SQL generated in 2.1.0, 2.1.1, 2.2.0 in relation
- Dominant language
- TypeScript
- Stars
- 32.1k
- Forks
- 3k
- PR merge metrics
- PR metrics pending
Description
### Version Information
Server Version: 2.1.0+
### Environment
Linux docker with MSSQL database
### What is the expected behaviour?
Given the following query, run in 2.0.9, when there is no related entry in `ResolutionDetails`
```gql
query MyQuery {
TicketDetails {
TicketNumber
ResolutionCode
ResolutionDetails {
description
}
}
}
```
Produces the following functional though still incorrect output
```json
{
"data": {
"TicketDetails": [
{
"TicketNumber": "ABC123",
"ResolutionCode": null,
"ResolutionDetails": []
}
]
}
}
```
### Keywords
Hasura, mssql, relationship, null
### What is the current behaviour?
Running that same query in 2.1.0, 2.1.1, or 2.2.0, results in an error
```json
{
"errors": [
{
"extensions": {
"internal": {
"tag": "unsuccessful_return_code",
"contents": [
"odbc_SQLExecDirectW",
-1,
"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]JSON text is not properly formatted. Unexpected character 'n' is found at position 0.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]JSON text is not properly formatted. Unexpected character 'n' is found at position 0."
]
},
"path": "$",
"code": "unexpected"
},
"message": "sql server exception"
}
]
}
```
If I remove the `ResolutionDetails` part, there is no error.
`ResolutionCode` is the field relating `TicketDetails` to `ResolutionDetails`
The generated SQL in 2.0.9 is below. The line indicated has a value of 'null' instead of '[]' on 2.1.0, 2.1.1, and 2.2.0.
This 'null' should not be quoted.
```sql
SELECT
ISNULL(
(
SELECT
[t_TicketDetails1].[TicketNumber] AS [TicketNumber],
[t_TicketDetails1].[ResolutionCode] AS [ResolutionCode],
JSON_QUERY([or_ResolutionDetails1].[json]) AS [ResolutionDetails]
FROM
[dbo].[TicketDetailss] AS [t_TicketDetails1]
OUTER APPLY (
SELECT
ISNULL(
(
SELECT
[t_Jira_Resolution1].[description] AS [description]
FROM
[dbo].[Jira_Resolution] AS [t_Jira_Resolution1]
WHERE
(
([t_Jira_Resolution1].[id]) = ([t_TicketDetails1].[ResolutionCode])
) FOR JSON PATH,
INCLUDE_NULL_VALUES,
WITHOUT_ARRAY_WRAPPER
),
'[]' ------------ this is 'null'
)
) AS [or_ResolutionDetails1]([json]) FOR JSON PATH,
INCLUDE_NULL_VALUES
),
'[]'
)
```
Contributor guide
Research direction
Reproduce the GraphQL query against the MSSQL setup and compare the generated SQL in 2.0.9 with 2.1.0–2.2.0, focusing on the relation JSON fallback where `null` replaces `'[]'`. No source file or test is named in the issue; done means the query succeeds when `ResolutionDetails` has no related row and the generated SQL contains valid unquoted JSON handling.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- api, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100