我在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;
此SQL查询的输出: | ?专栏?| UI| UI为null| UI不为空| | ———|———|———|———| | 1|("0000—00—00 00:00:00.00000 + 00",系统,"0000—00—00 00:00:00.0000000 + 00",系统,0000,正常,00000FEE,f00d00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|虚假|虚假|
问题是ui is null
和ui is not null
列的结果行值都是false
,而列row_value
包含row本身(不是null
).
奇怪的是,我无法设法用内联值来再现它,例如:
-- 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
甚至不使用虚拟表格:
-- 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
;
这两个最新的代码片段都按预期工作,即x is null
返回的值与x is not null
相反.
thanks in advance
EDIT:
当使用特定字段的语法时(如ui.user_id is (not) null
),那么问题就消失了.那么,我想问一个问题,这是普遍喜欢的形式,还是我原来的方法也应该工作?(我以前没有直接判断row的问题)