Description:
Two queries that should be semantically equivalent produce different results when grouping by a FLOAT column.
The expression:
CASE WHEN c0 IS NULL THEN NULL ELSE c0 END
is equivalent to simply:
c0
Therefore, the following two queries should return identical results when grouped by c0.
However, they return significantly different values.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c0 FLOAT
);
INSERT INTO t0 (c0) VALUES (1323233.14);
SELECT CASE WHEN (t0.c0) IS NULL THEN NULL ELSE t0.c0 END
FROM t0
GROUP BY t0.c0;
SELECT MIN(CASE WHEN (t0.c0) IS NULL THEN NULL ELSE t0.c0 END)
FROM t0
GROUP BY t0.c0;
Expected result
Both queries should return the same value because:
CASE WHEN c0 IS NULL THEN NULL ELSE c0 END ≡ c0
and there is only a single row in the group.
Expected output (approximate):
1323233.14
Actual result
The first query returns:1323230
The second query returns:
1323233.125
The results differ significantly despite the expressions being logically equivalent.
Suggested fix:
The query result appears to be derived from an internal GROUP BY key rather than from the evaluated SELECT expression.
It may be safer to ensure that the SELECT expression is evaluated from the original row value rather than reusing the GROUP BY key when the expression is not identical to the grouping column.
In this case:
CASE WHEN c0 IS NULL THEN NULL ELSE c0 END
is semantically equivalent to c0, but the returned value differs from the aggregate version:
MIN(CASE WHEN c0 IS NULL THEN NULL ELSE c0 END)
This suggests that the optimizer may be reusing a rounded or truncated grouping key when producing the SELECT output.
Description: Two queries that should be semantically equivalent produce different results when grouping by a FLOAT column. The expression: CASE WHEN c0 IS NULL THEN NULL ELSE c0 END is equivalent to simply: c0 Therefore, the following two queries should return identical results when grouped by c0. However, they return significantly different values. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 FLOAT ); INSERT INTO t0 (c0) VALUES (1323233.14); SELECT CASE WHEN (t0.c0) IS NULL THEN NULL ELSE t0.c0 END FROM t0 GROUP BY t0.c0; SELECT MIN(CASE WHEN (t0.c0) IS NULL THEN NULL ELSE t0.c0 END) FROM t0 GROUP BY t0.c0; Expected result Both queries should return the same value because: CASE WHEN c0 IS NULL THEN NULL ELSE c0 END ≡ c0 and there is only a single row in the group. Expected output (approximate): 1323233.14 Actual result The first query returns:1323230 The second query returns: 1323233.125 The results differ significantly despite the expressions being logically equivalent. Suggested fix: The query result appears to be derived from an internal GROUP BY key rather than from the evaluated SELECT expression. It may be safer to ensure that the SELECT expression is evaluated from the original row value rather than reusing the GROUP BY key when the expression is not identical to the grouping column. In this case: CASE WHEN c0 IS NULL THEN NULL ELSE c0 END is semantically equivalent to c0, but the returned value differs from the aggregate version: MIN(CASE WHEN c0 IS NULL THEN NULL ELSE c0 END) This suggests that the optimizer may be reusing a rounded or truncated grouping key when producing the SELECT output.