[
https://issues.apache.org/jira/browse/CALCITE-7678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18101143#comment-18101143
]
Julian Hyde commented on CALCITE-7678:
--------------------------------------
I asked Claude to fact-check and explain why Postgres chose its semantics, and
this is the response.
{quote}The claim that {{ROW(1, 2)}} and {{(1, 2)}} are different types in
Postgres is not correct. Per [4.2.13 Row
Constructors|https://www.postgresql.org/docs/current/sql-expressions.html], the
keyword {{ROW}} is optional when there is more than one expression in the list,
so {{(1,2)}} parses to the same row constructor as {{{}ROW(1,2){}}}. The only
asymmetry is the one-element case, and it goes the other way: {{ROW(1)}} is a
one-field row, {{(1)}} is a scalar in parens. So {{(NULL,NULL) = (NULL,NULL)}}
yields NULL, not TRUE.
What Postgres distinguishes is syntactic context, not type. From [9.25 Row and
Array
Comparisons|https://www.postgresql.org/docs/current/functions-comparisons.html]:
{quote}The SQL specification requires row-wise comparison to return NULL if the
result depends on comparing two NULL values or a NULL and a non-NULL.
PostgreSQL does this only when comparing the results of two row constructors or
comparing a row constructor to the output of a subquery. In other contexts
where two composite-type values are compared, two NULL field values are
considered equal, and a NULL is considered larger than a non-NULL.
{quote}
Three-valued logic is a narrow special case for the row-constructor syntax;
everywhere else (composite columns, {{{}t1.* = t2.*{}}}, subquery outputs held
as values) you get {{{}record_eq{}}}, where nulls are equal and the result is
never NULL.
The conclusion in the last comment is still right. {{ARRAY[ROW(NULL)] =
ARRAY[ROW(NULL)]}} is TRUE because array comparison dispatches through the
element type's default btree opclass, i.e. {{{}record_eq{}}}, while {{ROW(NULL)
= ROW(NULL)}} is NULL. But the operative distinction is not nesting depth, it
is whether the comparison goes through an operator class. Once it does, it must
be an equivalence relation and a total order, and 3VL equality is neither.
That is also why Postgres chose this. Tom Lane on composites, [BUG
#16465|https://www.postgresql.org/message-id/[email protected]]:
comparison of two non-null composite values cannot be allowed to yield null, or
they could not be sorted or indexed, and that would be too high a price for a
dubious spec detail. On arrays, [same objection,
2022|https://www.postgresql.org/message-id/cab4elo7afjgqfzoqfqfmba7zk1adwrkz9mun5jptzupurqt...@mail.gmail.com]:
without a total order on array values, btree indexes cannot be built on such
columns, so it is a deliberate deviation that will not be changed. Both threads
were raised by Lukas Eder from jOOQ, the second comparing against H2, which
returns NULL. Indexability beats the standard, decided twice.
Two implications. Testing against Postgres has to vary the syntax, not just the
values, or the results look inconsistent: {{ROW(1,NULL) = ROW(2,NULL)}} is
FALSE rather than NULL, {{ROW(1,2,NULL) < ROW(1,3,0)}} is TRUE because the
third pair is never reached, and the same values routed through a composite
column give different answers again. And less needs to change than the thread
suggests: standard 3VL for explicit row-constructor comparison plus Calcite's
existing nulls-equal ARRAY behaviour is exactly Postgres, so the ARRAY
behaviour is already the target rather than a defect. Since GROUP BY, DISTINCT
and UNION are defined on grouping equality anyway, the 3VL question only bites
{{=}} in WHERE/ON, {{{}IN{}}}, and quantified comparisons.
On foreign keys: the standard needed MATCH SIMPLE, PARTIAL and FULL precisely
because there is no single defensible answer for matching a partially-null row.
That supports Julian's "minefield" reading rather than being a digression.
Postgres implements SIMPLE and FULL, never PARTIAL.
{quote}
> Runtime equality for ROW values produces incorrect results
> ----------------------------------------------------------
>
> Key: CALCITE-7678
> URL: https://issues.apache.org/jira/browse/CALCITE-7678
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Mihai Budiu
> Priority: Minor
>
> GROUP BY, DISTINCT, JOIN, UNION, etc by ROW-typed values produces incorrect
> results, because ROWs are compiled to Object[] and are compared by reference
> instead of being compared by value.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
