BUG #18221: Unexpected Query Result

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.

Responses

Browse pgsql-bugs by date

  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