Thread: BUG #18221: Unexpected Query Result
The following bug has been logged on the website: Bug reference: 18221 Logged by: Jinsheng Ba Email address: bajinsheng@u.nus.edu PostgreSQL version: 16.1 Operating system: Ubuntu Description: Please see this test case: CREATE TABLE t0(c0 INT); CREATE TABLE t1(c0 serial); CREATE TABLE t2(c0 serial); CREATE TABLE t3(c0 INT); INSERT INTO t3(c0) VALUES(1), (1), (2), (3); INSERT INTO t2(c0) VALUES(0), (-1691506874), (514432934), (1678038555), (0), (1642626911); INSERT INTO t1(c0) VALUES(-1647179285), (-1273316451), (-922427340); INSERT INTO t1(c0) VALUES((NULL)::INT); INSERT INTO t0(c0) VALUES(4); CREATE VIEW v0(c1, c2) AS (SELECT DISTINCT ON (t2.c0) t2.c0, abs(t1.c0) FROM t1, t3, t2); ANALYZE(VERBOSE); SELECT (v0.c1) BETWEEN (1) AND (v0.c2) FROM t0, v0 CROSS JOIN t3; -- 4 trues SELECT t3.c0 FROM t0, v0 CROSS JOIN t3 WHERE (v0.c1) BETWEEN (1) AND (v0.c2); -- 8 rows The expression (v0.c1) BETWEEN (1) AND (v0.c2) is evaluated to true for 4 rows in the first query, while the second query returns 8 rows unexpectedly. If I remove the ANALYZE statement, the second returns 4 rows, which is expected.
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
> 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
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
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