From: | Wolfgang Walther <walther(at)technowledgy(dot)de> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Wrong result for comparing ROW(...) with IS NOT NULL |
Date: | 2020-11-05 12:32:06 |
Message-ID: | 21ff8e9c-627a-f949-fb00-a41b9ddcc9d3@technowledgy.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2020-11-05 13:14:16 | Re: Wrong result for comparing ROW(...) with IS NOT NULL |
Previous Message | Henryk Korulski | 2020-11-05 12:19:31 | Re: BUG #16704: Segmentation Problem - SSL SYSCALL error: EOF detected |