We reject the following query:
nconway=# create table abc (a int, b int, c int);
CREATE TABLE
nconway=# select distinct on (a) a, b, c from abc order by b, c, a;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
This works fine, of course:
nconway=# select distinct on (a) a, b, c from abc order by a, b, c;a | b | c
---+---+---
(0 rows)
src/backend/parser/parse_clause.c notes:
/* * If the user writes both DISTINCT ON and ORDER BY, then the * two expression lists must match (until
oneor the other * runs out). Otherwise the ORDER BY requires a different * sort order than the DISTINCT does,
andwe can't implement * that with only one sort pass (and if we do two passes, the * results will be rather
unpredictable).However, it's OK to * have more DISTINCT ON expressions than ORDER BY * expressions; we can just
addthe extra DISTINCT values to * the sort list, much as we did above for ordinary DISTINCT * fields. *
*Actually, it'd be OK for the common prefixes of the two * lists to match in any order, but implementing that check
* seems like more trouble than it's worth. */
Does this strike anyone else as being wrong?
-Neil
