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
- 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