Nested comparison semantics are unpredictable

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>

Responses

Browse pgsql-bugs by date

  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