The following bug has been logged on the website:
Bug reference: 15297
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 works in many cases correctly in pg:
"select NULL in (1,2)" -> returns NULL, correct
"select (1, NULL::int) in ((1, 1), (1, 2))" -> returns NULL, correct
"select 1 < NULL" -> returns NULL, correct
"select (1, NULL::int) > (1, 2)" -> returns NULL, correct
However as soon as a tuple/composite value contains itself composites, the
rule is not obeyed any more recursively:
"select (1, (1, NULL::int)) in ((1, (1, 0)), (1, (1, 2)))" -> returns false
!?
"select (1, (1, NULL::int)) > (1, (1, 2))" -> returns true !?
Is this intended behavior? If so, where is it documented?
If not documented, any suggestion as to how to work around it?
Thanks in advance,
--
Raphael 'kena' Poss