[
https://issues.apache.org/jira/browse/FLINK-40935?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Yaoxuan Wu updated FLINK-40935:
-------------------------------
Affects Version/s: 2.4.0
> VAR_SAMP / STDDEV_SAMP return -1 for a group with a single row
> --------------------------------------------------------------
>
> Key: FLINK-40935
> URL: https://issues.apache.org/jira/browse/FLINK-40935
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 2.3.0, 2.4.0
> Reporter: Yaoxuan Wu
> Priority: Major
>
> {code:java}
> SELECT k, VAR_SAMP(c), STDDEV_SAMP(c) FROM (VALUES (1, 10), (2, 20)) AS v(k,
> c) GROUP BY k;
> -- Flink: (1, -1, -1), (2, -1, -1) expected: (1, NULL, NULL), (2, NULL,
> NULL)
> SELECT VAR_SAMP(c) FROM (VALUES (10)) AS v(c);
> -- NULL (correct, no GROUP BY)
> SELECT k, VAR_SAMP(c) FROM (VALUES (1, CAST(NULL AS INT)), (1, 10)) AS v(k,
> c) GROUP BY k;
> -- NULL (correct, nullable input) {code}
> The sample variance of a single value is NULL, and a variance can never be
> negative. With GROUP BY and a NOT NULL input, the result type is inferred as
> {{{}INT NOT NULL{}}}, although the value is NULL for a group with one row:
> {code:java}
> SELECT VAR_SAMP(c) AS r FROM (VALUES (1, 10)) AS v(k, c) GROUP BY k -- r:
> INT NOT NULL, returns -1
> SELECT VAR_SAMP(c) AS r FROM (VALUES (10)) AS v(c) -- r:
> INT, returns NULL {code}
> The optimized plan computes CAST(/($f1, CASE(=($f2, 1), null:BIGINT, -($f2,
> 1))) AS INTEGER), so the NULL produced by the CASE is lost and -1 (the
> default value of a non-nullable INT) is returned. DOUBLE inputs return -1.0.
> Batch and streaming mode behave the same.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
