Re: Nested comparison semantics are unpredictable

Richard Wesley <richard(at)duckdblabs(dot)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