pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedWrong behaviour of array comparison when arrays contain nulls
3+ messages / 2 participants
[nested] [flat]
* Wrong behaviour of array comparison when arrays contain nulls
@ 2022-04-06 12:22 Lukas Eder <lukas.eder@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Lukas Eder @ 2022-04-06 12:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
A discussion has been brought to my attention about the behaviour of H2 vs
PostgreSQL when it comes to comparing arrays that contain NULL values, see:
https://github.com/h2database/h2database/issues/3476
For the following query:
SELECT array[1, NULL] = array[1, NULL]
H2 returns NULL whereas PostgreSQL returns TRUE. In my opinion and
intuition, as well as according to ISO/IEC 9075-2:2016(E) 8.2 <comparison
predicate> GR 1) b) ii), H2 is right and PostgreSQL is wrong.
On the above H2 github issue, a slack discussion was linked, to which I
have no access, but the gist of the slack discussion was that NULL is
*identical* to NULL according to the SQL standard, but identical doesn't
mean equal, and the aboe 8.2 GR 1) b) ii) clearly requires all array
elements Xi and Yi to be equal for the arrays to be equal.
Best Regards,
Lukas
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong behaviour of array comparison when arrays contain nulls
@ 2022-04-06 13:13 Lukas Eder <lukas.eder@gmail.com>
parent: Lukas Eder <lukas.eder@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Lukas Eder @ 2022-04-06 13:13 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
For the record, while deviations from the standard related to ROW
expressions are documented here:
https://www.postgresql.org/docs/current/functions-comparisons.html#COMPOSITE-TYPE-COMPARISON
This particular deviation isn't documented on that page. Might be worth
adding?
On Wed, Apr 6, 2022 at 2:22 PM Lukas Eder <lukas.eder@gmail.com> wrote:
> Hello,
>
> A discussion has been brought to my attention about the behaviour of H2 vs
> PostgreSQL when it comes to comparing arrays that contain NULL values, see:
> https://github.com/h2database/h2database/issues/3476
>
> For the following query:
>
> SELECT array[1, NULL] = array[1, NULL]
>
> H2 returns NULL whereas PostgreSQL returns TRUE. In my opinion and
> intuition, as well as according to ISO/IEC 9075-2:2016(E) 8.2 <comparison
> predicate> GR 1) b) ii), H2 is right and PostgreSQL is wrong.
>
> On the above H2 github issue, a slack discussion was linked, to which I
> have no access, but the gist of the slack discussion was that NULL is
> *identical* to NULL according to the SQL standard, but identical doesn't
> mean equal, and the aboe 8.2 GR 1) b) ii) clearly requires all array
> elements Xi and Yi to be equal for the arrays to be equal.
>
> Best Regards,
> Lukas
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong behaviour of array comparison when arrays contain nulls
@ 2022-04-06 13:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lukas Eder <lukas.eder@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-04-06 13:37 UTC (permalink / raw)
To: Lukas Eder <lukas.eder@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Lukas Eder <lukas.eder@gmail.com> writes:
> For the following query:
> SELECT array[1, NULL] = array[1, NULL]
> H2 returns NULL whereas PostgreSQL returns TRUE. In my opinion and
> intuition, as well as according to ISO/IEC 9075-2:2016(E) 8.2 <comparison
> predicate> GR 1) b) ii), H2 is right and PostgreSQL is wrong.
If we don't impose a total order on array values, then we cannot build
btree indexes on such columns. So yes, this is a deviation from the
SQL standard, and no we are not going to change it.
As for documentation, section 9.19 says
The comparison operators compare the array contents
element-by-element, using the default B-tree comparison function for
the element data type, and sort based on the first difference.
which implies this behavior but perhaps could be more explicit.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-04-06 13:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-06 12:22 Wrong behaviour of array comparison when arrays contain nulls Lukas Eder <lukas.eder@gmail.com>
2022-04-06 13:13 ` Lukas Eder <lukas.eder@gmail.com>
2022-04-06 13:37 ` 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