Yaoxuan Wu created FLINK-40919:
----------------------------------
Summary: AVG on INT returns wrong result when the planner rewrites
it into SUM/COUNT (INT sum overflows)
Key: FLINK-40919
URL: https://issues.apache.org/jira/browse/FLINK-40919
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 2.3.0
Reporter: Yaoxuan Wu
{{AVG(x)}} on an INT column is computed without overflow on its own. When the
planner rewrites it into {{{}SUM(x) / COUNT(x){}}}, the sum keeps type INT and
wraps around, so the same AVG over the same rows returns a wrong value.
Trigger 1: SUM and COUNT of the same column in the SELECT list:
{code:java}
SELECT AVG(x) FROM (VALUES (2147483647), (2147483647), (2147483647)) AS v(x);
-- 2147483647 (correct)
SELECT AVG(x), SUM(x), COUNT(x) FROM (VALUES (2147483647), (2147483647),
(2147483647)) AS v(x);
-- AVG = 715827881 (wrong) {code}
Trigger 2: AVG over a join, where AVG is split into SUM/COUNT and pushed below
the join:
{code:java}
from pyflink.table import EnvironmentSettings, TableEnvironment, DataTypest_env
= TableEnvironment.create(EnvironmentSettings.in_batch_mode())
a = t_env.from_elements([(2147483647,), (2147483647,), (1,)],
DataTypes.ROW([DataTypes.FIELD('x', DataTypes.INT())]))
b = t_env.from_elements([(1,), (2,)], DataTypes.ROW([DataTypes.FIELD('y',
DataTypes.INT())]))
t_env.create_temporary_view('a', a)
t_env.create_temporary_view('b', b)
# Expected: 1431655765 = (2147483647 + 2147483647 + 1) / 3, each row repeated
twice by the join
t_env.execute_sql("SELECT AVG(x) FROM a").print() #
1431655765 (correct)
t_env.execute_sql("SELECT AVG(x) FROM a, b").print() # 0
(wrong)
t_env.execute_sql("SELECT AVG(CAST(x AS BIGINT)) FROM a, b").print() #
1431655765 (correct)
print(t_env.explain_sql("SELECT AVG(x) FROM a, b"))
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
