On MSSQL a DISTINCT subquery can order by a CASE expression that is not in its select list

Co-authored by an LLM. Reviewed by me. And now without further ado:

MSSQL has no NULLS FIRST / NULLS LAST, so DoobieMSSqlMapping.orderToFragment emulates it by prepending a computed CASE WHEN ... IS NULL THEN 1 ELSE 0 END term to the ORDER BY. MSSQL also has no DISTINCT ON, so distinctOnToFragment renders a plain DISTINCT instead.

Where those two meet in the same select, the result is rejected:

com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Reproducing

SqlWorldMapping is already wired into doobie-mssql as WorldSuite, so no new fixture or mapping is needed:

query {
  countries(limit: 2) {
    name
    cities {
      name
    }
  }
}

The statement MSSQL receives, captured from the JDBC driver's own logging since the query never returns:

SELECT city.id, city.name, country.code, country.name AS name_alias_0
FROM country
INNER JOIN (
  SELECT DISTINCT country.code
  FROM country
  WHERE (country.code IS NOT NULL)
  ORDER BY CASE WHEN (country.code COLLATE DATABASE_DEFAULT) IS NULL THEN 1 ELSE 0 END ASC,
           (country.code COLLATE DATABASE_DEFAULT)
  OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY
) country_city_pred ON ((country_city_pred.code = country.code))
LEFT JOIN city ON ((city.countrycode = country.code))
WHERE ((country.code = country_city_pred.code))

The country_city_pred subquery selects country.code and nothing else. Its first ORDER BY term is the CASE expression, which is not in that select list. The second term, the collate-wrapped column, MSSQL is happy to match against country.code.

An offset alone reproduces it just as a limit does; countries(offset: 1) { name cities { name } } fails with the same message.

What has to line up for it to trigger

Three things at once, which is why it has gone unnoticed:

1. A to-many child in the selection (cities) together with a limit or an offset on the parent field. That combination is what produces the _pred subquery carrying the DISTINCT; the child join alone does not.
2. An ordering whose nulls handling takes the CASE branch of orderToFragment.
3. Ordering by the key column, so that distinctOrderColumn does not kick in.

Existing MSSQL coverage misses it by falling on the wrong side of one condition each time. SqlWorldSuite's "simple query with limit" is countries(limit: 3) { name }, which has the limit but no child join, so there is no DISTINCT subquery at all. "query with top-level ordering, limit and subobjects" is countries(limit: 3, byPopulation: true) { name population cities { name } }, which does have both, but ordering by a non-key column routes it through FirstValueColumn, splitting the query so the DISTINCT sits on an inner select and the CASE lands on the outer one, where it is legal. That split renders as:

SELECT dist.code FROM (
  SELECT DISTINCT country.code, first_value(country.population) OVER (...) AS population
  FROM country WHERE ... ORDER BY (country.code COLLATE DATABASE_DEFAULT) OFFSET 0 ROWS
) dist
ORDER BY CASE WHEN dist.population IS NULL THEN 1 ELSE 0 END ASC, dist.population, (dist.code COLLATE DATABASE_DEFAULT)
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY

(That fragment is from an equivalent capture at limit: 2, so its row count differs from the test's; the shape is the point.)

Not a general problem with the shape

The same query against the same mapping on doobie-pg returns the expected data. Postgres has both native nulls ordering and DISTINCT ON, so neither emulation applies and no computed term appears in the ORDER BY:

INNER JOIN (
  SELECT DISTINCT ON ((country.code COLLATE "C")) (country.code COLLATE "C")
  FROM country WHERE (country.code IS NOT NULL)
  ORDER BY (country.code COLLATE "C") LIMIT 2
) AS country_city_pred ...

It also appears to be specific to MSSQL among the backends that share the CASE WHEN emulation. The MySQL backend proposed in #872 and the MariaDB backend in #873 both emulate nulls ordering the same way, but MySQL 8.4 and MariaDB were checked against live servers and accept the shape, so neither looks affected.

Note

I ran into this while wiring #871's SqlQualifiedNamesSuite into doobie-mssql, where a test of this shape failed. Reproducing it on SqlWorldMapping, which has no schema-qualified tables in it, ruled out any connection to that PR. Six of that suite's eight tests pass on MSSQL; one fails on this bug, and one on an unrelated CHAR padding artifact in the MSSQL fixture I wrote, which is mine to sort out. The suite stays unwired on MSSQL until both are dealt with.

Start by reading DoobieMSSqlMapping.orderToFragment and distinctOnToFragment, then run the reproduced query through SqlWorldMapping and WorldSuite. Compare the MSSQL SQL and existing SqlWorldSuite coverage for child joins, limits or offsets, key ordering, and null handling. Done means the reproduced query executes successfully and the relevant MSSQL tests pass.