BUG #15298: Array-array comparisons when arrays contain NULLs

Started by PG Bug reporting formalmost 8 years ago1 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 15298
Logged by: kena
Email address: knz@thaumogen.net
PostgreSQL version: 10.4
Operating system: FreeBSD + Linux
Description:

The SQL standard mandates ternary logic for values that involve NULL, where
NULL means "unknown".

This seems to be implemented only partially in pg:

"select 3 = any (array[1, null])" -> returns NULL, correct
"select null = any (array[1, null])" -> returns NULL, correct
"select 3 in (select unnest(array[1,null]))" -> returns NULL, correct
"select null in (select unnest(array[1,null]))" -> returns NULL, correct

However:

"select array[1,2] = array[1,null]" -> returns false !?
"select array[1,null] = array[1,null]" -> returns true !?

Is this intended behavior? If so, where is it documented?

If not intended, any suggestion as to how to work around it?

Thanks in advance,

--
Raphael 'kena' Poss