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.