> Can you reproduce the unexpected behavior with explicit order by clause?
Oh yes. For this query, the unexpected behavior disappears.
SELECT DISTINCT ON (t2.c0) t2.c0, abs(t1.c0) FROM t1, t3, t2 ORDER BY t2.c0, t1.c0, t3.c0;
Thanks for explanation!
From: Sergei Kornilov <sk@zsrv.org>
Sent: Sunday, December 3, 2023 9:44 PM
To: PG Bug reporting form <noreply@postgresql.org>
Cc: Ba Jinsheng <bajinsheng@u.nus.edu>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re:BUG #18221: Unexpected Query Result
- External Email -
Hello
Using "distinct on" without specifying an explicit "order by" is unpredictable in itself. I'll quote from the manual:
> Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.
Can you reproduce the unexpected behavior with explicit order by clause?
regards, Sergei