CODEWITHSUNDEEP
google-bigquery
augustin-barillec
augustin-barillec

Reputation: 531

BigQuery standard SQL : checking if an array is null does not work

If i launch this query : 

with a as (
select cast(null as array<string>) as x union all select ['str1','str2'] as x)
select * from a where x is null

I get this result :

enter image description here

This is the result i expected.

But if i first launch this query :

 select cast(null as array<string>) as x union all select ['str1', 'str2'] as x

whose result i save in a table "a" in a dataset "tmp" and then i launch this query : 

select * from `tmp.a` where x is null

I get this result :

enter image description here

I expected a result identical to the first one. Why is there a difference between the two results ?

Upvotes: 7

Views: 31274

Answers (2)

Bora &#199;olakoğlu
Bora &#199;olakoğlu

Reputation: 69

From official documentation

Currently, all existing data types are nullable, but conditions apply for ARRAYs.

An ARRAY can be NULL.
For example:

SELECT CAST(NULL AS ARRAY<INT64>) IS NULL AS array_is_null;

+---------------+
| array_is_null |
+---------------+
| TRUE          |
+---------------+

BigQuery translates a NULL ARRAY into an empty ARRAY in the query result, although inside the query, NULL and empty ARRAYs are two distinct values.

WITH Items AS (
  SELECT [] AS numbers UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>))
SELECT numbers FROM Items;

+---------+
| numbers |
+---------+
| []      |
| []      |
+---------+

TL;DR: An array can be null but if you check it with where condition it will act if it is not null. If you want a truly empty array you have to cast it CAST(NULL AS ARRAY<INT64>). You can see my example below.

enter image description here enter image description here

Upvotes: 0

tomgorb
tomgorb

Reputation: 551

Workaround : To get the same result for both cases you could use in your where clause the function ARRAY_LENGTH(x)=0.

Upvotes: 33

Related Questions