From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: bajinsheng@u.nus.edu
Subject: BUG #18221: Unexpected Query Result
Date: Sat, 02 Dec 2023 02:47:55 +0000
Message-ID: <18221-5d3b9545177fe006@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, bajinsheng@u.nus.edu
Subject: Re: BUG #18221: Unexpected Query Result
In-Reply-To: <18221-5d3b9545177fe006@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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