IssueTracker

Problem you have encountered

When using the AVG aggregation and some values are Infinity, the result is NaN, which is numerically incorrect. For instance, consider a subquery where two values are Infinity and you want the average. Infinity + Infinity is Infinity, and Infinity divided by 2 is also Infinity. In BigQuery a query like this is producing NaN and not Infinity.

What you expected to happen:

My expectation is that we return numerically correct AVG values whenever possible, even when Infinity values are present.

Steps to reproduce:

Run some of the queries below:

select avg(c1)
from ((
  select cast('Infinity' as float64) as c1
) union all
(
  select cast('Infinity' as float64) as c1
))

select avg(c1)
from ((
  select cast('Infinity' as float64) as c1
) union all
(
  select cast(0.1 as float64) as c1
))

As can be seen, the expected avg for those two queries is Infinity but BigQuery returns NaN

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

Changing the query to the pattern below returns the expected results

select sum(c1) / count(c1)
from ((
  select cast('Infinity' as float64) as c1
) union all
(
  select cast(0.1 as float64) as c1
))

Trying the query in other engines such as Postgres returns expected results:

SELECT avg(c1)
FROM (
  SELECT 'Infinity'::float8 AS c1
  UNION ALL
  SELECT 'Infinity'::float8 AS c1
) s1;