PostgreSQL: `row_alias is null` and `row_alias is not null` inconsistent return value

I have stumbled upon a peculiar phenomena in an SQL select:

select 1
, ui as row_value
, ui is null as "ui is null"
, ui is not null as "ui is not null"
from user_info ui
where ui.user_id = 1003;

Output of this SQL query: | ?column? | ui | ui is null | ui is not null | | :--- | :--- | :--- | :--- | | 1 | ("0000-00-00 00:00:00.000000+00",system,"0000-00-00 00:00:00.000000+00",system,0000,normal,00000FEE,f00d0000-0b0b-000a-0e00-0a0e00000b00,t,username1,username2,"username1",f,t,f,aad,username1,username2,,"username1 username2") | false | false |

The problem is that the result row values for both ui is null and ui is not null columns are both false while the column row_value contains the row itself (which is not null).

The strange thing is that I could not manage to reproduce this with inline values such as:

-- working as expected
select row(1, 2, 'Hello') as original_row
, row(1, 2, 'Hello') is null as row_is_null
, row(1, 2, 'Hello') is not null as row_is_not_null

and not even using dummy tables:

-- working as expected

create table table_1
(
    table_1_id int not null primary key generated always as identity,
    title      text
);

create table table_2
(
    table_2_id int not null primary key generated always as identity,
    title      text,
    table_1_id int references table_1 on delete cascade
);

create table table_3
(
    table_3_id int not null primary key generated always as identity,
    title      text,
    table_2_id int references table_2 on delete cascade
);

insert into table_1 (title)
values ('Table1 Row 1')
         , ('Table1 Row 2');

insert into table_2 (title, table_1_id)
values ('Table2 Row 1', 1)
         , ('Table2 Row 2', 2);

insert into table_3 (title, table_2_id)
values ('Table3 Row 1', 1)
         , ('Table3 Row 2', 2);

select t1.title
         , t2
         -- , t2 is null
         -- , t2 is not null
         , t3
         , t3 is null
         , t3 is not null
from table_1 t1
    left join public.table_2 t2 using (table_1_id)
    left join public.table_3 t3 on t2.table_2_id = t3.table_2_id and t3.title = 'Table3 Row 1'
where t1.table_1_id = 1
;

Both of the latest code snippets work as expected, that is, x is null returns the opposite value of x is not null.

Thanks in advance

EDIT:

when using the syntax with specific field (like ui.user_id is (not) null), then the problem disappears. Then I would like to ask question if this form is commonly preferred or my original approach should work too? (I had not have such issues with checking the row directly in the past)

Solution

Your result is quite consistent with the expected behavior. When you check for the is null tuple condition, you can get the results
is null (1,null) - is false
and
is not null (1,null) - is false.

Some parts of set is null, other not is null. Is null (null,null) - is true.

See short example

create table test (id int,val int);
insert into test values
 (1,100)
,(2,null)
,(null,null)
;
select id,val
  ,test is null as is_null
  ,test is not null as is_not_null
from test

Output is

id	val	is_null	is_not_null
1	100	f	t
2	null	f	f
null	null	t	f