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

Поиск
Список
Период
Сортировка
От Wolfgang Walther
Тема Wrong result for comparing ROW(...) with IS NOT NULL
Дата
Msg-id 21ff8e9c-627a-f949-fb00-a41b9ddcc9d3@technowledgy.de
обсуждение исходный текст
Ответы Re: Wrong result for comparing ROW(...) with IS NOT NULL  (Pavel Stehule <pavel.stehule@gmail.com>)
Список 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



В списке pgsql-bugs по дате отправления:

Предыдущее
От: Henryk Korulski
Дата:
Сообщение: Re: BUG #16704: Segmentation Problem - SSL SYSCALL error: EOF detected
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Wrong result for comparing ROW(...) with IS NOT NULL