You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Yaoxuan Wu (Jira)" <ji...@apache.org> on 2026/10/06 04:16:00 UTC

[jira] [Updated] (FLINK-40919) AVG on INT returns wrong result when the planner rewrites it into SUM/COUNT (INT sum overflows)

     [ https://issues.apache.org/jira/browse/FLINK-40919?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yaoxuan Wu updated FLINK-40919:
-------------------------------
    Description: 
{{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}
 

  was:
{{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}
 


> 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
>
> {{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)