From: | Richard Wesley <richard(at)duckdblabs(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Nested comparison semantics are unpredictable |
Date: | 2025-08-12 16:16:22 |
Message-ID: | B8388D26-947E-482E-8359-325E54083969@duckdblabs.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hey guys -
I was trying to make our comparison semantics for nested types <https://github.com/duckdb/duckdb/issues/18039> match Postgres, and I found that the semantics of comparisons change when a join is present.
psql (17.5 (Postgres.app))
Type "help" for help.
hawkfish=# with tbl_s_null as (
hawkfish(# select *
hawkfish(# from (
hawkfish(# values (row(1), row(0)), (row(1), row(1)), (row(1), NULL), (row(1), row(NULL::int))
hawkfish(# ) as tbl_s_null(col0, col1)
hawkfish(# )
hawkfish-# SELECT x.col1, y.col1, x.col1 = y.col1, x.col1 != y.col1
hawkfish-# FROM tbl_s_null x CROSS JOIN tbl_s_null y
hawkfish-# ORDER BY x.col1, y.col1 NULLS LAST;
col1 | col1 | ?column? | ?column?
------+------+----------+----------
(0) | (0) | t | f
(0) | (1) | f | t
(0) | () | f | t
(0) | | |
(1) | (0) | f | t
(1) | (1) | t | f
(1) | () | f | t
(1) | | |
() | (0) | f | t
() | (1) | f | t
() | () | t | f
() | | |
| (0) | |
| (1) | |
| () | |
| | |
(16 rows)
Note that in this query, row(0) <> row(NULL). But if I just issue the comparison directly:
hawkfish=# select row(0) = row(NULL);
?column?
----------
(1 row)
I originally suspected that it might the the CSE processing, but as the first query shows, it does it even with an explicit CROSS JOIN.
This was reproduced with 17.5 on a MacBook Pro 2.4 GHz 8-Core Intel Core i9 running MacOS 15.6 (24G84).
Met vriendelijke groet, best regards, mit freundlichen Grüßen,
Richard Wesley
Time Lord
richard(at)duckdblabs(dot)com <mailto:richard(at)duckdblabs(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2025-08-12 18:12:22 | Re: Nested comparison semantics are unpredictable |
Previous Message | Xuneng Zhou | 2025-08-12 14:41:32 | Re: BUG #19006: Assert(BufferIsPinned) in BufferGetBlockNumber() is triggered for forwarded buffer |