Re: Nested comparison semantics are unpredictable

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