BigQuery标准SQL中数组NULL判断结果不一致问题咨询

Let me break down exactly why you're seeing this mismatch—it boils down to how BigQuery handles NULL array values when writing them to a persistent table.

The Root Cause #

When you use cast(null as array<string>) in a CTE (Common Table Expression), BigQuery retains that as a true NULL array in the in-memory result set. That’s why your where x is null filter works as intended, correctly picking up that row.

However, BigQuery has a key limitation with array-type columns in persistent tables: it does not support storing NULL array values. When you write a NULL array to a table, BigQuery automatically converts it to an empty array ([]) under the hood. So when you query the table later, x is null no longer matches that row—because the value is now an empty array, not a NULL.

Let’s Confirm This #

You can easily verify this by running a quick check on your persisted table:

select 
  x,
  x is null as is_null_check,
  array_length(x) as array_length
from `tmp.a`

For the row that started as a NULL array, you’ll see:

• x displayed as [] (empty array)
• is_null_check returns false
• array_length returns 0

Compare that to running the same check on your original CTE:

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

Here, the NULL array row will show:

• x as NULL
• is_null_check returns true
• array_length returns NULL

Fixes & Workarounds #

If you need to identify rows that were originally NULL arrays after persisting to a table, use one of these approaches:

• Check for empty arrays: Since NULL arrays get converted to empty arrays, this will target the correct rows:

  select * from `tmp.a` where array_length(x) = 0
  

• Combine NULL and empty array checks: For cases where you might have both NULLs (in intermediate steps) and empty arrays, use this:

  select * from `tmp.a` where x is null or array_length(x) = 0
  

• Preserve NULL arrays with a struct: If you need to keep the NULL array semantics when writing to a table, wrap the array in a struct (structs support NULL values):

  with a as (
    select struct(cast(null as array<string>) as arr) as x union all
    select struct(['str1','str2'] as arr) as x
  )
  -- Write this to your table, then query with:
  select * from `tmp.a` where x.arr is null
  

内容的提问来源于stack exchange，提问作者augustin-barillec