hasura / hasura/graphql-engine

Invalid SQL generated in 2.1.0, 2.1.1, 2.2.0 in relation

Open
#8,122 4 comments 2 reactions 0 assignees View on GitHub
k/bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.