Why BigQuery produces an empty array when ARRAY_CONCAT is applied on a non empty array in conjunction with ARRAY_AGG?

Question

I encountered a very curious behavior in BigQuery.

The following query produces the unexpected result empty array [], since I'm concatenating arrays I'm expecting an array with all the elements that I'm concatenating.

SELECT ARRAY_CONCAT(
    (
        SELECT ARRAY_AGG(col1) -- This is creating an empty array since condition is always false
        FROM ... 
        WHERE 1 = 2
    ),
    ['test']
)

While a very similar query produces the expected result ['test']:

SELECT ARRAY_CONCAT(
    [],
    ['test']
)

And ARRAY_AGG() produces indeed an empty array []:

SELECT ARRAY_AGG(col1) -- This is creating an empty array since condition is always false
FROM ... 
WHERE 1 = 2

Why the first query produces an empty array while it is a combination of the second and third query?

Gordon Linoff · Accepted Answer · 2020-05-19 19:16:53Z

3

This is a little tricky to explain. Your first query does not produce an empty array. It produce an array column whose value is NULL. That is why the concat returns NULL.

As a general rule, almost aggregation function in a subquery with no rows returns NULL. The only exception is COUNT() which returns 0.

It is tempting to think that NULL and an empty array are the same thing. They are not.

answered May 19, 2020 at 19:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why BigQuery produces an empty array when ARRAY_CONCAT is applied on a non empty array in conjunction with ARRAY_AGG?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related