[
https://issues.apache.org/jira/browse/FLINK-40919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated FLINK-40919:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
>
> {{AVG}} on an INT column is computed without overflow on its own. When the
> planner rewrites it into {{{}SUM / COUNT{}}}, 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)
