big query - array_agg nested within array_concat doesn't support ignore nulls properly

Question

I don't know if this is expected behaviour, but it seems pretty buggy to me:

If you run this query...

select array_concat( 
   array_agg(x)
)
from unnest([1,2,3,4]) as x

You get one row with an array of [1,2,3,4], which is expected.

If you add in an empty array, you get the same single row with [1,2,3,4] as expected...

select array_concat( 
   array_agg(x),
   []
)
from unnest([1,2,3,4]) as x

However if you add in array_agg(null ignore nulls), the whole thing breaks...

select array_concat( 
   array_agg(x),
   array_agg(null ignore nulls)
)
from unnest([1,2,3,4]) as x

this returns an empty array!

If the above simplified example looks strange, consider this instead, which works for x=4, and totally breaks for x=5

select array_concat( 
   array_agg(x),
   array_agg(case when x = 4 then x end ignore nulls)
)
from unnest([1,2,3,4]) as x

Question: is this expected? If so, why? If not, is anyone aware of an existing bug report?

Yun Zhang · Accepted Answer · 2022-08-24 19:18:20Z

2

This is expected behavior and here's why:

First, this query returns null instead of empty array

select array_concat( 
   array_agg(x),
   array_agg(null ignore nulls)
)
from unnest([1,2,3,4]) as x

array_agg document says: If there are zero input rows, this function returns NULL., therefore, array_agg(null ignore nulls) => null.

array_concat says The function returns NULL if any input argument is NULL.

so the above query end up looking like:

array_concat( 
   array_agg(x),
   null,
)

It is expected to return null to you.

If you're relying on some library which transforms the query result for you. The library may treat NULL array as empty array, this may be a problematic behavior.

answered Aug 24, 2022 at 19:18

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

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

1 Comment

Yun Zhang Over a year ago

Returning NULL when input argument is NULL is quite common in SQL world. In SQL, NULL is not empty but "I don't know what it is!"

Mikhail Berlyant · Accepted Answer · 2022-08-24 21:38:05Z

1

If the above simplified example looks strange, consider this instead, which works for x=4, and totally breaks for x=5

As you [hopefully] realized already - the behavior you see is as expected and is by design, so I will not repeat what already answered :o)

Meantime, I think, what you really need here - is just to rewrite your case into some thing like below

select array_concat_agg( 
   [x]  || 
   array(select x from unnest(['whatever']) where x = 4)
)
from unnest([1,2,3,4]) as x

with output

and now - if you replace x = 4 with x = 5

select array_concat_agg( 
   [x]  || 
   array(select x from unnest(['whatever']) where x = 5)
)
from unnest([1,2,3,4]) as x

the output is

answered Aug 24, 2022 at 21:38

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Comments

NickW · Accepted Answer · 2022-08-24 19:08:27Z

0

if you read the documentation for array_concat it states “The function returns NULL if any input argument is NULL” - so the behavior you are seeing is what is expected.

BTW an empty array is not the same as null

answered Aug 24, 2022 at 19:08

NickW

10k2 gold badges10 silver badges26 bronze badges

Collectives™ on Stack Overflow

big query - array_agg nested within array_concat doesn't support ignore nulls properly

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related