`ANY_VALUE` seems to ignore `HAVING` clause when used in conjunction with `GROUP BY`

[anonimitoraf]

What happened?

As per title (see below for more info)

What did you expect to happen?

I expected the query below to return:

+---------------------------------+---------------------------------+
|           least_sales           |           most_sales            |
+---------------------------------+---------------------------------+
|  {"sold":"10","fruit":"apples"} |  {"sold":"20","fruit":"apples"} |
| {"sold":"10","fruit":"bananas"} | {"sold":"30","fruit":"bananas"} |
| {"sold":"10","fruit":"oranges"} | {"sold":"10","fruit":"oranges"} |
|   {"sold":"40","fruit":"pears"} |   {"sold":"40","fruit":"pears"} |
+---------------------------------+---------------------------------+

Instead, it incorrectly returns (note apples and bananas:

+---------------------------------+---------------------------------+
|           least_sales           |           most_sales            |
+---------------------------------+---------------------------------+
|  {"sold":"10","fruit":"apples"} |  {"sold":"10","fruit":"apples"} |
| {"sold":"30","fruit":"bananas"} | {"sold":"30","fruit":"bananas"} |
| {"sold":"10","fruit":"oranges"} | {"sold":"10","fruit":"oranges"} |
|   {"sold":"40","fruit":"pears"} |   {"sold":"40","fruit":"pears"} |
+---------------------------------+---------------------------------+

How can we reproduce it (as minimally and precisely as possible)?

WITH
  Store AS (
    SELECT 20 AS sold, "apples" AS fruit
    UNION ALL
    SELECT 10 AS sold, "apples" AS fruit
    UNION ALL
    SELECT 40 AS sold, "pears" AS fruit
    UNION ALL
    SELECT 10 AS sold, "oranges" AS fruit
    UNION ALL
    SELECT 30 AS sold, "bananas" AS fruit
    UNION ALL
    SELECT 10 AS sold, "bananas" AS fruit
  )
SELECT
  ANY_VALUE(x HAVING MIN sold) AS least_sales,
  ANY_VALUE(x HAVING MAX sold) AS most_sales
FROM Store x
GROUP BY fruit

Anything else we need to know?

At face value, it looks like it just takes the first row.

[ohaibbq]

I've released a fix for this in the Recidiviz fork of the emulator - version v0.6.6-recdiviz.2 https://github.com/Recidiviz/bigquery-emulator/releases/tag/v0.6.6-recidiviz.2