Обсуждение: BUG #18221: Unexpected Query Result

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.

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