This will create a public issue which anybody can view and comment on.

Problem you have encountered:

The any_value command behaves differently than described in the reference. The reference states that NULL values can be selected. In the current implentation, however, only NULL is returned if there are no other entries.

ANY_VALUE behaves as if RESPECT NULLS is specified; rows for which expression is NULL are considered and may be selected." https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#any_value

Furthermore, it seems that the first non-NULL value is returned and that the sorting of the column or table is taken into account.

What you expected to happen:

Please correct the documentation to:

"ANY_VALUE behaves as if IGNORE NULLS is specified; NULL is only returned if all entries are NULL."

Please add the following description to the second example (with "ANY_VALUE(fruit) OVER (ORDER BY ... " ) :

"By using order by the column is sorted, any_value will return then the first not NULL entry. "

Steps to reproduce:

The behavior of the current implentation with respect to any_value can be tested with the following query:

WITH
  tmp AS (SELECT ARRAY_AGG(0) AS tmp0 FROM UNNEST(GENERATE_ARRAY(1,1000*1000))),
  tbl AS (
  SELECT
    NULLIF(x,0) AS x,
    IF(x!=0,x,NULL) AS y, 
    rand() AS rand
  FROM
    tmp,
    UNNEST(ARRAY_CONCAT(tmp0, [0,0,0,0,0,-100,0,-90,-80,3,4,5,6,7,8,9] , tmp0)) AS x )

SELECT "count rows", COUNT(1) FROM tbl
UNION ALL SELECT "count items not null", COUNT(x) FROM tbl 
UNION ALL SELECT "any_value(x): (returns first non null element in list: -100)", ANY_VALUE(x) FROM tbl
UNION ALL SELECT "2nd run", ANY_VALUE(x) FROM tbl 
UNION ALL SELECT "3rd run", ANY_VALUE(x) FROM tbl
UNION ALL SELECT "any_value(y)",  ANY_VALUE(y) FROM tbl
UNION ALL SELECT "order asc", ANY_VALUE(x) FROM (Select * from tbl order by x asc)
UNION ALL SELECT "order desc (returns largest element: 9)", ANY_VALUE(x) FROM (Select * from tbl order by x desc)
UNION ALL SELECT "order desc", ANY_VALUE(x) FROM (Select * from tbl order by x desc)
UNION ALL SELECT "order abs(x) desc", ANY_VALUE(x) FROM (Select * from tbl order by abs(x) desc )
UNION ALL SELECT "order abs(x) asc (smallest number: 3)", ANY_VALUE(x) FROM (Select * from tbl order by abs(x) asc )
UNION ALL SELECT "order rand asc", ANY_VALUE(x) FROM (Select * from tbl order by rand asc )
UNION ALL SELECT "order rand desc", ANY_VALUE(x) FROM (Select * from tbl order by rand desc )

Other information (workarounds you have tried, documentation consulted, etc):

https://stackoverflow.com/questions/70915066/inclusion-of-nulls-with-any-value-in-bigquery/73345175?noredirect=1#comment129541895_73345175

Issue 242438603

Links (3)

Issue metadata