sql: DISTINCT ON a computed key fails with ORDER BY ... NULLS LAST (v26.2 regression)

Open
#176,233 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
56/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
go, sql

Research direction

Start in SQL optimization around buildOrderByPreProjection, identified in the issue as a likely cause, and trace how computed ORDER BY columns are projected when NULLS ordering is explicit. Compare that path with how the DISTINCT ON key’s projection is matched. Reproduce with the supplied SQL and verify it succeeds while the listed plain-column, default-order, and subquery cases continue to work.

Written by the indexing model from the issue text.

Description

T-sql-queries X-move-internal

Describe the problem

Since v26.2.0-alpha.2, DISTINCT ON over a computed key errors when a later ORDER BY column has non-default NULLS ordering. v26.1 and Postgres accept the query.

To Reproduce

CREATE TABLE t (a INT, b INT);
INSERT INTO t VALUES (1, NULL), (1, 2), (2, 3);

SELECT DISTINCT ON (k) a + 1 AS k, b FROM t ORDER BY k, b NULLS LAST;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
SQLSTATE: 42P10

These still work: a plain-column key (DISTINCT ON (a) ... ORDER BY a, b NULLS LAST), default NULLS order (ORDER BY k, b), or computing the key in a subquery.

Expected behavior

v26.1.8 returns:

  k | b
----+----
  2 | 2
  3 | 3

Additional data

Version Result
v25.4.16, v26.1.0, v26.1.8, v26.2.0-alpha.1 OK
v26.2.0-alpha.2, v26.2.0, v26.2.11, v26.3.5, v26.4.0-rc.1 42P10

The likely cause is 35c5a88b3582 (sql/opt: support subqueries in ORDER BY with NULLS FIRST/LAST, #129956). With non-default NULLS ordering, buildOrderByPreProjection appears to move computed ORDER BY columns to new pre-projected IDs, so they no longer match the DISTINCT ON key's projection column.

Environment:

  • CockroachDB v26.2.11, v26.3.5 and v26.4.0-rc.1 (official darwin arm64 binaries, start-single-node)
  • Client: cockroach sql

Jira issue: CRDB-71580

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.