issue summarized by claude:
Environment
Hasura v2.40.0
Data source: Microsoft SQL Server (MSSQL)
Driver: ODBC Driver 18
Description
When querying an MSSQL table with order_by, Hasura wraps every sorted column in an IIF(col IS NULL, 0, 1) expression regardless of whether the column is NOT NULL. This appears to prevent SQL Server from using indexes on unfiltered queries, causing dramatically slower execution compared to an equivalent hand-written query.
GraphQL query:
{
my_table(
limit: 100
order_by: [{ company_id: asc }, { record_id: asc }]
) {
company_id
record_id
category
quantity
amount
}
}
SQL Hasura generates:
SELECT ...
FROM [dbo].[my_table] AS [t1]
ORDER BY IIF([t1].[record_id] IS NULL, 0, 1),
[t1].[record_id] ASC,
IIF([t1].[company_id] IS NULL, 0, 1),
[t1].[company_id] ASC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
FOR JSON PATH, INCLUDE_NULL_VALUES
SQL I would expect (or write by hand):
SELECT TOP 100 ...
FROM [dbo].[my_table]
ORDER BY company_id ASC, record_id ASC
Both company_id and record_id are defined NOT NULL in the schema. The IIF(col IS NULL, ...) expression is therefore a no-op logically, but it prevents SQL Server's optimizer from using an index on those columns since the sort key is a computed expression rather than the raw column.
Performance impact:
Query Hasura (generated SQL) Raw ODBC (hand-written) Ratio
100 rows, no filter, order by 2 cols ~2,800 ms ~28 ms ~100× slower
100 rows, filtered on indexed col ~180 ms ~140 ms ~1.3× slower
The unfiltered case is the most affected. With a WHERE filter on an indexed column, SQL Server can seek first and then sort the small result set, making the IIF less costly. Without a filter, SQL Server must scan the entire table before sorting, and the IIF prevents early-termination via index seek.
Root cause (from reading the source)
In server/src-lib/Hasura/Backends/MSSQL/Instances/Schema.hs, msOrderByOperators maps plain asc to NullsFirst and desc to NullsLast:
( define Name._asc "in ascending order, nulls first",
(MSSQL.AscOrder, MSSQL.NullsFirst) -- always emits IIF
),
( define Name._desc "in descending order, nulls last",
(MSSQL.DescOrder, MSSQL.NullsLast) -- always emits IIF
),
This flows through fromAnnotatedOrderByItemG → fromOrderBy → fromNullsOrder in ToQuery.hs, which emits the IIF for anything other than NullsAnyOrder. There is no nullability check anywhere in the chain.
Possible fix
SQL Server's native NULL ordering already matches the intended behavior:
ORDER BY col ASC → NULLs sort first (NULL = lowest value in SQL Server)
ORDER BY col DESC → NULLs sort last
So NullsAnyOrder (which emits no IIF) would produce identical result ordering to the current NullsFirst/NullsLast behavior, while allowing the optimizer to use indexes normally.
The proposed change is to map plain asc/desc to NullsAnyOrder in msOrderByOperators, leaving the explicit asc_nulls_last, asc_nulls_first, desc_nulls_first, desc_nulls_last operators unchanged:
( define Name._asc "in ascending order, nulls first",
(MSSQL.AscOrder, MSSQL.NullsAnyOrder) -- defer to SQL Server native (= nulls first for ASC)
),
( define Name._desc "in descending order, nulls last",
(MSSQL.DescOrder, MSSQL.NullsAnyOrder) -- defer to SQL Server native (= nulls last for DESC)
),
Questions before I open a PR:
Is this analysis correct — is NullsAnyOrder on SQL Server guaranteed to produce NULLs-first for ASC and NULLs-last for DESC across all SQL Server versions and collations?
Would this be considered a breaking change, given that the generated SQL changes (even though result ordering should be identical)?
Is there a known reason the current behavior was chosen over NullsAnyOrder that I'm missing — e.g. a specific SQL Server edge case where native NULL ordering differs?
Are there existing integration tests for MSSQL ORDER BY NULL behavior that would catch a regression?
Happy to open a PR with the two-line change if this direction is confirmed.
issue summarized by claude:
Environment
Hasura v2.40.0
Data source: Microsoft SQL Server (MSSQL)
Driver: ODBC Driver 18
Description
When querying an MSSQL table with order_by, Hasura wraps every sorted column in an IIF(col IS NULL, 0, 1) expression regardless of whether the column is NOT NULL. This appears to prevent SQL Server from using indexes on unfiltered queries, causing dramatically slower execution compared to an equivalent hand-written query.
GraphQL query:
{
my_table(
limit: 100
order_by: [{ company_id: asc }, { record_id: asc }]
) {
company_id
record_id
category
quantity
amount
}
}
SQL Hasura generates:
SELECT ...
FROM [dbo].[my_table] AS [t1]
ORDER BY IIF([t1].[record_id] IS NULL, 0, 1),
[t1].[record_id] ASC,
IIF([t1].[company_id] IS NULL, 0, 1),
[t1].[company_id] ASC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
FOR JSON PATH, INCLUDE_NULL_VALUES
SQL I would expect (or write by hand):
SELECT TOP 100 ...
FROM [dbo].[my_table]
ORDER BY company_id ASC, record_id ASC
Both company_id and record_id are defined NOT NULL in the schema. The IIF(col IS NULL, ...) expression is therefore a no-op logically, but it prevents SQL Server's optimizer from using an index on those columns since the sort key is a computed expression rather than the raw column.
Performance impact:
Query Hasura (generated SQL) Raw ODBC (hand-written) Ratio
100 rows, no filter, order by 2 cols ~2,800 ms ~28 ms ~100× slower
100 rows, filtered on indexed col ~180 ms ~140 ms ~1.3× slower
The unfiltered case is the most affected. With a WHERE filter on an indexed column, SQL Server can seek first and then sort the small result set, making the IIF less costly. Without a filter, SQL Server must scan the entire table before sorting, and the IIF prevents early-termination via index seek.
Root cause (from reading the source)
In server/src-lib/Hasura/Backends/MSSQL/Instances/Schema.hs, msOrderByOperators maps plain asc to NullsFirst and desc to NullsLast:
( define Name._asc "in ascending order, nulls first",
(MSSQL.AscOrder, MSSQL.NullsFirst) -- always emits IIF
),
( define Name._desc "in descending order, nulls last",
(MSSQL.DescOrder, MSSQL.NullsLast) -- always emits IIF
),
This flows through fromAnnotatedOrderByItemG → fromOrderBy → fromNullsOrder in ToQuery.hs, which emits the IIF for anything other than NullsAnyOrder. There is no nullability check anywhere in the chain.
Possible fix
SQL Server's native NULL ordering already matches the intended behavior:
ORDER BY col ASC → NULLs sort first (NULL = lowest value in SQL Server)
ORDER BY col DESC → NULLs sort last
So NullsAnyOrder (which emits no IIF) would produce identical result ordering to the current NullsFirst/NullsLast behavior, while allowing the optimizer to use indexes normally.
The proposed change is to map plain asc/desc to NullsAnyOrder in msOrderByOperators, leaving the explicit asc_nulls_last, asc_nulls_first, desc_nulls_first, desc_nulls_last operators unchanged:
( define Name._asc "in ascending order, nulls first",
(MSSQL.AscOrder, MSSQL.NullsAnyOrder) -- defer to SQL Server native (= nulls first for ASC)
),
( define Name._desc "in descending order, nulls last",
(MSSQL.DescOrder, MSSQL.NullsAnyOrder) -- defer to SQL Server native (= nulls last for DESC)
),
Questions before I open a PR:
Is this analysis correct — is NullsAnyOrder on SQL Server guaranteed to produce NULLs-first for ASC and NULLs-last for DESC across all SQL Server versions and collations?
Would this be considered a breaking change, given that the generated SQL changes (even though result ordering should be identical)?
Is there a known reason the current behavior was chosen over NullsAnyOrder that I'm missing — e.g. a specific SQL Server edge case where native NULL ordering differs?
Are there existing integration tests for MSSQL ORDER BY NULL behavior that would catch a regression?
Happy to open a PR with the two-line change if this direction is confirmed.