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

Aperta
#176,233 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
56/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
go, sql

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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

Lingua principale
Go
Stelle
32.6k
Fork
4.2k
Metriche di merge delle PR
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di cockroachdb/cockroach

Tutte le issue di cockroachdb/cockroach

Issue simili

Altre issue su Go

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.