Description:
Wrapping SUM(j) in CASE WHEN TRUE THEN SUM(j) ELSE SUM(j) END changes the result of a query using DISTINCT, WITH ROLLUP, and a STDDEV() window function. The direct SUM(j) query returns 17 rows, but the CASE query returns 14. The missing rows have (k, s) values (1, 2), (1, 12), and (1, 15).
The two expressions should give the same s value for every group. Although the window has no ORDER BY and its STDDEV() values may vary with row order, that does not explain why these three distinct (k, s) pairs disappear. The discrepancy reproduces without changing any session settings.
How to repeat:
Run the following in an empty schema:
CREATE TABLE t_window_std_var_optimized (
i INT,
j INT,
k INT
);
INSERT INTO t_window_std_var_optimized VALUES
(1, 1, 1), (1, 4, 1), (1, 2, 1), (1, 4, 1), (1, 4, 1),
(1, 1, 2), (1, 4, 2), (1, 2, 2), (1, 4, 2),
(1, 1, 3), (1, 4, 3), (1, 2, 3), (1, 4, 3),
(1, 1, 4), (1, 4, 4), (1, 2, 4), (1, 4, 4);
SELECT DISTINCT
k,
SUM(j) AS s,
STDDEV(k) OVER (
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS w
FROM t_window_std_var_optimized
GROUP BY k, j WITH ROLLUP;
SELECT DISTINCT
k,
CASE WHEN TRUE THEN SUM(j) ELSE SUM(j) END AS s,
STDDEV(k) OVER (
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS w
FROM t_window_std_var_optimized
GROUP BY k, j WITH ROLLUP;
Actual result:
The first query returns 17 rows. The second returns 14 rows, missing the rows with (k, s) values (1, 2), (1, 12), and (1, 15).
Expected result:
Both queries should return the same 17 distinct (k, s) pairs. The CASE condition is always true, and its two branches are identical.
Description: Wrapping SUM(j) in CASE WHEN TRUE THEN SUM(j) ELSE SUM(j) END changes the result of a query using DISTINCT, WITH ROLLUP, and a STDDEV() window function. The direct SUM(j) query returns 17 rows, but the CASE query returns 14. The missing rows have (k, s) values (1, 2), (1, 12), and (1, 15). The two expressions should give the same s value for every group. Although the window has no ORDER BY and its STDDEV() values may vary with row order, that does not explain why these three distinct (k, s) pairs disappear. The discrepancy reproduces without changing any session settings. How to repeat: Run the following in an empty schema: CREATE TABLE t_window_std_var_optimized ( i INT, j INT, k INT ); INSERT INTO t_window_std_var_optimized VALUES (1, 1, 1), (1, 4, 1), (1, 2, 1), (1, 4, 1), (1, 4, 1), (1, 1, 2), (1, 4, 2), (1, 2, 2), (1, 4, 2), (1, 1, 3), (1, 4, 3), (1, 2, 3), (1, 4, 3), (1, 1, 4), (1, 4, 4), (1, 2, 4), (1, 4, 4); SELECT DISTINCT k, SUM(j) AS s, STDDEV(k) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS w FROM t_window_std_var_optimized GROUP BY k, j WITH ROLLUP; SELECT DISTINCT k, CASE WHEN TRUE THEN SUM(j) ELSE SUM(j) END AS s, STDDEV(k) OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS w FROM t_window_std_var_optimized GROUP BY k, j WITH ROLLUP; Actual result: The first query returns 17 rows. The second returns 14 rows, missing the rows with (k, s) values (1, 2), (1, 12), and (1, 15). Expected result: Both queries should return the same 17 distinct (k, s) pairs. The CASE condition is always true, and its two branches are identical.