From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | bajinsheng(at)u(dot)nus(dot)edu |
Subject: | BUG #18221: Unexpected Query Result |
Date: | 2023-12-02 02:47:55 |
Message-ID: | 18221-5d3b9545177fe006@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18221
Logged by: Jinsheng Ba
Email address: bajinsheng(at)u(dot)nus(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ivan Trofimov | 2023-12-02 20:56:43 | Re: BUG #17948: libpq seems to misbehave in a pipelining corner case |
Previous Message | Alvaro Herrera | 2023-12-01 14:20:28 | Re: BUG #17948: libpq seems to misbehave in a pipelining corner case |