CODEWITHSUNDEEP
google-bigquery
Dominik
Dominik

Reputation: 792

NULL arrays get converted to empty arrays when written to a table

When I run this query below I get 7 as expected.

WITH Items AS (
  SELECT [] AS num1, CAST(NULL AS ARRAY<INTEGER>) as num2
)
SELECT COALESCE(num2, [7]) FROM Items;

When I save Items to a table and then reference the table by name I get [] instead, which I confirmed is not a NULL with the WHERE clause.

CREATE TEMP TABLE Items
AS
(
  SELECT [] AS num1, CAST(NULL AS ARRAY<INTEGER>) as num2
);

WITH t as
(
  SELECT COALESCE(num2, [7]) as test FROM Items
)
SELECT *
FROM t
WHERE array_length(test)=0

Is this expected behavior? This link suggests empty arrays and NULL arrays are distinct https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_nulls

Upvotes: 0

Views: 756

Answers (1)

Dominik
Dominik

Reputation: 792

I talked to someone and he pointed out this important statement (emphasis mine):

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.

It was also news to him but basically it seems when you write to a table you are writing the query result so the NULL ARRAY is now an empty array.

The work around is to use CREATE VIEW so that the NULLs are part of the query.

Upvotes: 1

Related Questions