BUG #15297: Irregular comparison rules for NULLs in tuples

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: knz(at)thaumogen(dot)net
Subject: BUG #15297: Irregular comparison rules for NULLs in tuples
Date: 2018-07-26 13:35:27
Message-ID: 153261212701.1395.18260320763646237974@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15297
Logged by: kena
Email address: knz(at)thaumogen(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-07-26 13:43:06 BUG #15298: Array-array comparisons when arrays contain NULLs
Previous Message PG Bug reporting form 2018-07-26 10:26:31 BUG #15296: Not able to get query result "information_schema.constraint_column_usage"