CODEWITHSUNDEEP
google-bigquery
Phil V1
Phil V1

Reputation: 5

BigQuery != "null" is it a Bug?

Set up the following data:

create table `<YOUR PROJECT>.exampledata.whatnulls`
AS (SELECT '' col1
UNION ALL SELECT null col1
UNION ALL SELECT 'null' col1
UNION ALL SELECT 'notnull' col1);

Perform this query:

select 'col1 is null' predicate, count(*) n
from `testset-2021.exampledata.whatnulls`
where col1 is null 
union all
select 'col1 = "null"' predicate, count(*)
from `testset-2021.exampledata.whatnulls`
where col1 = "null" 
union all
select 'col1 is not null' predicate, count(*)
from `testset-2021.exampledata.whatnulls`
where col1 is not null 
union all
select 'col1 != "null"' predicate, count(*)
from `testset-2021.exampledata.whatnulls`
where col1 != "null"

The result:

predicate        | n
---------------------   
col1 != "null"   | 2
col1 is null     | 1
col1 is not null | 3
col1 = "null"    | 1

Why is col1 != "null" matching 2 values rather than 3? Is this a bug in Big Query?

Upvotes: 0

Views: 77

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5503

Because for most SQL functions (here != is a type of comparison function), when input is NULL, the output is also NULL

SELECT CAST(NULL AS STRING) != "null" -- giving you NULL instead of false

Upvotes: 1

Related Questions