Yaoxuan Wu created FLINK-40935:
----------------------------------
Summary: 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
Reporter: Yaoxuan Wu
{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)
