Re: Wrong result for comparing ROW(...) with IS NOT NULL

Hi

čt 5. 11. 2020 v 13:32 odesílatel Wolfgang Walther <walther(at)technowledgy(dot)de>
napsal:

> Hi,
>
> when I do the following on PG 12.4, I get some unexpected results:
>
> SELECT
> ROW() IS NULL, -- true
> ROW() IS NOT NULL; -- true
>
> Both return true here. In any case IS NULL should return the opposite
> from IS NOT NULL, right?
>

for composite types this sentence is not valid

https://til.cybertec-postgresql.com/post/2019-09-29-Composite-types-and-NULL-in-PostgreSQL/

is null - is true, when all fields are null, and is not null is true, when
all fields is not null.

Regards

Pavel

>
> The same happens here:
>
> SELECT
> ROW(NULL, NULL) IS NULL, -- returns: true (expected)
> ROW(NULL, NULL) IS NOT NULL, -- returns: false (expected)
> ROW(1, NULL) IS NULL, -- returns: false (expected)
> ROW(1, NULL) IS NOT NULL, -- returns: false !!
> ROW(1, 1) IS NULL, -- returns: false (expected)
> ROW(1, 1) IS NOT NULL; -- returns: true (expected)
>
> The docs[1] say:
>
> > Also, it is possible to [...] test a row with IS NULL or IS NOT NULL,
> for example:
> > [...]
> > SELECT ROW(table.*) IS NULL FROM table; -- detect all-null rows
>
> So I would expect the ROW(1, NULL) IS NOT NULL to be true, because it's
> not "all-null". I'm not sure what I would expect ROW() to be, but surely
> not the same for IS NULL and IS NOT NULL.
>
> Best
>
> Wolfgang
>
> [1]:
>
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
>
>
>