bug: filter on float aggregate expression is folded to false

[SkyFan2002]

Search before asking

• I searched existing issues with these keywords and found no similar issue:
  • total_user_income WHERE aggregate float domain NaN
  • NaN domain filter false optimizer
  • aggregate derived filter float WHERE no rows

Version

upstream/main at commit c5ea44b9b82b

What's Wrong?

A query can return rows whose derived float aggregate expression is less than 10, but adding a WHERE predicate on the same expression returns no rows.

The predicate appears to be incorrectly optimized to false.

How to Reproduce?

WITH user_income AS (
  SELECT CAST(number % 2 AS STRING) AS user_id,
         SUM(number::FLOAT64) AS total_entry_fee,
         SUM(number::FLOAT64) + 1 AS total_actual_reward
  FROM numbers(5)
  GROUP BY user_id
)
SELECT user_id,
       total_actual_reward - total_entry_fee AS total_user_income
FROM user_income
ORDER BY user_id;

This returns:

0  1.0
1  1.0

But adding the filter returns no rows:

WITH user_income AS (
  SELECT CAST(number % 2 AS STRING) AS user_id,
         SUM(number::FLOAT64) AS total_entry_fee,
         SUM(number::FLOAT64) + 1 AS total_actual_reward
  FROM numbers(5)
  GROUP BY user_id
)
SELECT user_id,
       total_actual_reward - total_entry_fee AS total_user_income
FROM user_income
WHERE total_user_income < 10
ORDER BY user_id;

Expected result:

0  1.0
1  1.0

Actual result:

empty result

EXPLAIN shows the filter is folded to false, although matching rows exist.