pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18221: Unexpected Query Result
3+ messages / 3 participants
[nested] [flat]
* BUG #18221: Unexpected Query Result
@ 2023-12-02 02:47 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-12-02 02:47 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bajinsheng@u.nus.edu
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re:BUG #18221: Unexpected Query Result
@ 2023-12-03 13:44 Sergei Kornilov <sk@zsrv.org>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Sergei Kornilov @ 2023-12-03 13:44 UTC (permalink / raw)
To: PG Bug reporting form <noreply@postgresql.org>; +Cc: bajinsheng@u.nus.edu; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re:BUG #18221: Unexpected Query Result
@ 2023-12-04 01:17 Ba Jinsheng <bajinsheng@u.nus.edu>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 0 replies; 3+ messages in thread
From: Ba Jinsheng @ 2023-12-04 01:17 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; PG Bug reporting form <noreply@postgresql.org>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-12-04 01:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-02 02:47 BUG #18221: Unexpected Query Result PG Bug reporting form <noreply@postgresql.org>
2023-12-03 13:44 ` Sergei Kornilov <sk@zsrv.org>
2023-12-04 01:17 ` Ba Jinsheng <bajinsheng@u.nus.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox