Wrong behaviour of array comparison when arrays contain nulls

From: Lukas Eder <lukas(dot)eder(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Wrong behaviour of array comparison when arrays contain nulls
Date: 2022-04-06 12:22:45
Message-ID: CAB4ELO7afJgQfZoQfqfMBA7Zk1AdWRkZ9mUN5jpTZupurQTRsA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Lukas Eder 2022-04-06 13:13:16 Re: Wrong behaviour of array comparison when arrays contain nulls
Previous Message wangsh.fnst@fujitsu.com 2022-04-06 02:14:26 "unexpected duplicate for tablespace" problem in logical replication