skip to Main Content

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)

2

Answers


  1. 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
    Login or Signup to reply.
  2. If you think that that behavior is crazy, I am the first to agree with you. However, it is explicitly required by the SQL standard, so PostgreSQL has little choice in the matter.

    A composite value IS NULL only if all its elements are, and it IS NOT NULL only if all its elements are. Other composite values are neither NULL nor NOT NULL.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search