MSSQL SQL API pushdown emits invalid null ordering and reports a FETCH NEXT error

[davidda]

Describe the bug

The MSSQL expressions.sort template generates ORDER BY <expression> IS NULL ... to emulate PostgreSQL null ordering. SQL Server cannot use a predicate as a sort expression. When the query also has OFFSET/FETCH, the driver reports Invalid usage of the option NEXT in the FETCH statement. The earlier error in precedingErrors is Incorrect syntax near the keyword 'IS'.

Regular semantic queries can succeed because they use a different ordering template. SQL API queries that enter the pushdown path encounter this defect.

Reproduction

Run this on SQL Server or Azure SQL:

SELECT x
FROM (VALUES (CAST(NULL AS int)), (1), (2)) AS t(x)
ORDER BY x IS NULL ASC, x ASC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

Expected order for ASC NULLS LAST: 1, 2, NULL.

Actual result: syntax error near IS, followed by the FETCH NEXT error.

The same pagination succeeds when the null discriminator is a scalar:

SELECT x
FROM (VALUES (CAST(NULL AS int)), (1), (2)) AS t(x)
ORDER BY CASE WHEN x IS NULL THEN 1 ELSE 0 END ASC, x ASC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

To inspect the responsible template in the published Cube package:

const { MssqlQuery } = require('@cubejs-backend/schema-compiler');
const query = Object.create(MssqlQuery.prototype);
console.log(query.sqlTemplates().expressions.sort);

It returns:

{{ expr }} IS NULL {% if nulls_first %}DESC{% else %}ASC{% endif %}, {{ expr }} {% if asc %}ASC{% else %}DESC{% endif %}

Expected behavior and proposed fix

Wrap the null predicate in CASE WHEN ... THEN 1 ELSE 0 END. Keep the discriminator direction controlled by nulls_first, independently of the value direction controlled by asc.

CASE WHEN {{ expr }} IS NULL THEN 1 ELSE 0 END {% if nulls_first %}DESC{% else %}ASC{% endif %}, {{ expr }} {% if asc %}ASC{% else %}DESC{% endif %}

This preserves ASC/DESC and NULLS FIRST/LAST while generating valid T-SQL. No pagination or planner changes are needed.

Versions and scope

• Reproduced with cubejs/cube:v1.7.33.
• Azure SQL reports Microsoft SQL Azure 12.0.2000.8, database compatibility level 150.

Related reports

A search of open and closed Cube issues and pull requests on 2026-09-05 found no exact duplicate. #7392 also concerns MSSQL pagination, but its generated SQL lacks ORDER BY; it is a different failure. MSSQL support work in #10343 is related background.

The independent missing-join template defect and numeric literal precision issue are outside this patch.

[paveltiunov]

Fixed by #11776 (merged), which corrects expressions.sort in MssqlQuery to project the null-ordering predicate through CASE WHEN {{ expr }} IS NULL THEN 1 ELSE 0 END instead of emitting a bare IS NULL as a sort key. That also clears the Invalid usage of the option NEXT in the FETCH statement symptom, which was SQL Server surfacing the ORDER BY parse failure ahead of the OFFSET ... FETCH NEXT clause.

Available in v1.7.39 onward. Thanks @davidda for the report and the fix.

Two related items from the same investigation are deliberately not covered by that patch and remain open:

• SELECT DISTINCT combined with null ordering still fails with Msg 145 (T-SQL rejects an ORDER BY expression absent from the select list under DISTINCT). Not a regression — the previous template failed there too.
• Your other MSSQL pushdown reports are tracked separately in MSSQL SQL API pushdown emits boolean literals and predicates in invalid contexts #11826 and SQL API pushdown drops floating-point literal types #11780.

---

Generated by Claude Code