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

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?

CodePudding user response：

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

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

CodePudding user response：

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