PostgreSQL › PostgreSQL - bugs

NULL handling in 1D array comparison

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

2 messages Options

Manvendra

NULL handling in 1D array comparison

postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.0 (Ubuntu 13.0-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
(1 row)

Comparison should return UNKNOWN but it's returning a true.

postgres=# select array[null]=array[null];
?column?
----------
t
(1 row)

postgres=# select array[1, null]=array[1, null];
?column?
----------
t
(1 row)

SQL-99 standard use to say it should come out as UNKNOWN

https://crate.io/docs/sql-99/en/latest/chapters/10.html#comparison

Thanks & Regards,

Manvendra Panwar

Email -

Tom Lane-2

Re: NULL handling in 1D array comparison

Manvendra < > writes:
> Comparison should return UNKNOWN but it's returning a true.

> postgres=# select array[null]=array[null];

Yeah, that's intentional, because we have to be able to sort arrays.
Comparison of composites behaves similarly, btw.

> SQL-99 standard use to say it should come out as UNKNOWN

We're going to politely ignore the spec on this.

regards, tom lane