Re: Wrong behaviour of array comparison when arrays contain nulls

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(dot)eder(at)gmail(dot)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
>