Description:
Two queries that should be semantically equivalent produce different results when grouping by a FLOAT column.
The expression:
CASE WHEN col1 THEN col1 END
is effectively equivalent to:
col1
(for non-zero, non-NULL values, which is the case here).
Therefore, the following two queries should return identical results when grouped by col1.
However, they return noticeably different values.
How to repeat:
DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(
col1 FLOAT
);
INSERT INTO table1 (col1) VALUES (5375293.321);
SELECT (CASE WHEN table1.col1 THEN table1.col1 END)
FROM table1
GROUP BY table1.col1;
SELECT MAX(CASE WHEN table1.col1 THEN table1.col1 END)
FROM table1
GROUP BY table1.col1;
Expected result:
Both queries should return the same value because:
CASE WHEN col1 THEN col1 END ≡ col1 (for non-zero values),
and there is only a single row in the group.
Expected output (approximate):
5375293.321
Actual result:
The first query returns:
5375290
The second query returns:
5375293.5
The results differ significantly despite the expressions being logically equivalent.
Suggested fix:
The bug suggests that the query engine may be reusing an internal GROUP BY key (which could be rounded or truncated due to FLOAT precision) when producing the SELECT result, instead of evaluating the full SELECT expression.
It would be safer to ensure that the SELECT expression is computed based on the original row value rather than relying on the grouped key, especially when the SELECT expression is not syntactically identical to the GROUP BY column.
In this case:
CASE WHEN col1 THEN col1 END
is semantically equivalent to col1, but the result differs from the aggregate version:
MAX(CASE WHEN col1 THEN col1 END)
This indicates that the optimizer may be introducing precision loss by reusing the grouping key when generating the final output.
Description: Two queries that should be semantically equivalent produce different results when grouping by a FLOAT column. The expression: CASE WHEN col1 THEN col1 END is effectively equivalent to: col1 (for non-zero, non-NULL values, which is the case here). Therefore, the following two queries should return identical results when grouped by col1. However, they return noticeably different values. How to repeat: DROP TABLE IF EXISTS table1; CREATE TABLE table1 ( col1 FLOAT ); INSERT INTO table1 (col1) VALUES (5375293.321); SELECT (CASE WHEN table1.col1 THEN table1.col1 END) FROM table1 GROUP BY table1.col1; SELECT MAX(CASE WHEN table1.col1 THEN table1.col1 END) FROM table1 GROUP BY table1.col1; Expected result: Both queries should return the same value because: CASE WHEN col1 THEN col1 END ≡ col1 (for non-zero values), and there is only a single row in the group. Expected output (approximate): 5375293.321 Actual result: The first query returns: 5375290 The second query returns: 5375293.5 The results differ significantly despite the expressions being logically equivalent. Suggested fix: The bug suggests that the query engine may be reusing an internal GROUP BY key (which could be rounded or truncated due to FLOAT precision) when producing the SELECT result, instead of evaluating the full SELECT expression. It would be safer to ensure that the SELECT expression is computed based on the original row value rather than relying on the grouped key, especially when the SELECT expression is not syntactically identical to the GROUP BY column. In this case: CASE WHEN col1 THEN col1 END is semantically equivalent to col1, but the result differs from the aggregate version: MAX(CASE WHEN col1 THEN col1 END) This indicates that the optimizer may be introducing precision loss by reusing the grouping key when generating the final output.