pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNested comparison semantics are unpredictable
2+ messages / 2 participants
[nested] [flat]
* Nested comparison semantics are unpredictable
@ 2025-08-12 16:16 Richard Wesley <richard@duckdblabs.com>
2025-08-12 18:12 ` Re: Nested comparison semantics are unpredictable Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Richard Wesley @ 2025-08-12 16:16 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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@duckdblabs.com <mailto:richard@duckdblabs.com>
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Nested comparison semantics are unpredictable
2025-08-12 16:16 Nested comparison semantics are unpredictable Richard Wesley <richard@duckdblabs.com>
@ 2025-08-12 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-08-12 18:12 UTC (permalink / raw)
To: Richard Wesley <richard@duckdblabs.com>; +Cc: pgsql-bugs@lists.postgresql.org
Richard Wesley <richard@duckdblabs.com> writes:
> 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.
It's not about joins. It's about the syntactic form of the
expression. When you write "ROW(...) = ROW(...)", that goes
through make_row_comparison_op(), which indeed behaves
differently from record_eq(), which is where the comparison
will end up when it looks like "var = var". In particular
"ROW(x) = ROW(y)" is optimized into "x = y" which is why
you get a NULL for "row(0) = row(NULL)". record_eq() is not
allowed to produce a null in such cases, though --- else it
would be unsuitable to use as a btree comparator.
There's a lot of historical baggage and spec-text-lawyering
behind all this, but the short answer is that we're unlikely
to change either behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-08-12 18:12 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-12 16:16 Nested comparison semantics are unpredictable Richard Wesley <richard@duckdblabs.com>
2025-08-12 18:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox