Thread

1. BUG #18221: Unexpected Query Result

   PG Bug reporting form <noreply@postgresql.org> — 2023-12-02T02:47:55Z

   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.
   
   

2. Re:BUG #18221: Unexpected Query Result

   Sergei Kornilov <sk@zsrv.org> — 2023-12-03T13:44:09Z

   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
   
   
   
   

3. Re: Re:BUG #18221: Unexpected Query Result

   Ba Jinsheng <bajinsheng@u.nus.edu> — 2023-12-04T01:17:57Z

   > Can you reproduce the unexpected behavior with explicit order by clause?
   Oh yes. For this query, the unexpected behavior disappears.
   SELECT DISTINCT ON (t2.c0) t2.c0, abs(t1.c0) FROM t1, t3, t2 ORDER BY t2.c0, t1.c0, t3.c0;
   
   
   Thanks for explanation!
   
   ________________________________
   From: Sergei Kornilov <sk@zsrv.org>
   Sent: Sunday, December 3, 2023 9:44 PM
   To: PG Bug reporting form <noreply@postgresql.org>
   Cc: Ba Jinsheng <bajinsheng@u.nus.edu>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
   Subject: Re:BUG #18221: Unexpected Query Result
   
           - External Email -
   
   
   
   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