Description:
Equivalent queries using VIEW, CTE, and temporary table representations return different results for MIN() on a FLOAT column.
Specifically, the VIEW and CTE queries return 5.207821014845463e37, whereas the temporary table query returns 5.20782e37.
All three representations operate on the same underlying data and perform the same aggregation. Further investigation shows that explicitly casting the MIN() results to DOUBLE produces identical values, suggesting that the inconsistency is related to result type inference or floating-point output precision rather than the underlying aggregation calculation.
How to repeat:
DROP TEMPORARY TABLE IF EXISTS temp_t;
DROP VIEW IF EXISTS v0;
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (c0 FLOAT);
INSERT INTO t0 VALUES (5.207821188276958e37);
-- Query 1: VIEW
CREATE VIEW v0 AS
SELECT c0 AS x FROM t0;
SELECT MIN(x) AS r FROM v0 GROUP BY x;
-- Query 2: CTE
WITH cte AS (
SELECT c0 AS x FROM t0
)
SELECT MIN(x) AS r FROM cte GROUP BY x;
-- Query 3: Temporary Table
CREATE TEMPORARY TABLE temp_t AS
SELECT c0 AS x FROM t0;
SELECT MIN(x) AS r FROM temp_t GROUP BY x;
Actual Result
Query 1 (VIEW):
+----------------------+
| r |
+----------------------+
| 5.207821014845463e37 |
+----------------------+
Query 2 (CTE):
+----------------------+
| r |
+----------------------+
| 5.207821014845463e37 |
+----------------------+
Query 3 (Temporary Table):
+------------+
| r |
+------------+
| 5.20782e37 |
+------------+
Thus, the VIEW and CTE queries produce the same result representation, while the temporary table query produces a different representation.
Expected Result
Since all three queries perform the same aggregation over equivalent intermediate query results, their returned values and output precision are expected to be consistent, unless the observed difference is caused by an intentional type conversion.
Additional Information
The temporary table column x is inferred as FLOAT. Further investigation indicates that the difference may be associated with the inferred result type of MIN().
When the aggregation results are explicitly cast to DOUBLE, all three representations return the same value:
5.207821014845463e37
I also tested the same three representations on MariaDB 12.2.2, where all three queries consistently returned:
5.20782e37
Description: Equivalent queries using VIEW, CTE, and temporary table representations return different results for MIN() on a FLOAT column. Specifically, the VIEW and CTE queries return 5.207821014845463e37, whereas the temporary table query returns 5.20782e37. All three representations operate on the same underlying data and perform the same aggregation. Further investigation shows that explicitly casting the MIN() results to DOUBLE produces identical values, suggesting that the inconsistency is related to result type inference or floating-point output precision rather than the underlying aggregation calculation. How to repeat: DROP TEMPORARY TABLE IF EXISTS temp_t; DROP VIEW IF EXISTS v0; DROP TABLE IF EXISTS t0; CREATE TABLE t0 (c0 FLOAT); INSERT INTO t0 VALUES (5.207821188276958e37); -- Query 1: VIEW CREATE VIEW v0 AS SELECT c0 AS x FROM t0; SELECT MIN(x) AS r FROM v0 GROUP BY x; -- Query 2: CTE WITH cte AS ( SELECT c0 AS x FROM t0 ) SELECT MIN(x) AS r FROM cte GROUP BY x; -- Query 3: Temporary Table CREATE TEMPORARY TABLE temp_t AS SELECT c0 AS x FROM t0; SELECT MIN(x) AS r FROM temp_t GROUP BY x; Actual Result Query 1 (VIEW): +----------------------+ | r | +----------------------+ | 5.207821014845463e37 | +----------------------+ Query 2 (CTE): +----------------------+ | r | +----------------------+ | 5.207821014845463e37 | +----------------------+ Query 3 (Temporary Table): +------------+ | r | +------------+ | 5.20782e37 | +------------+ Thus, the VIEW and CTE queries produce the same result representation, while the temporary table query produces a different representation. Expected Result Since all three queries perform the same aggregation over equivalent intermediate query results, their returned values and output precision are expected to be consistent, unless the observed difference is caused by an intentional type conversion. Additional Information The temporary table column x is inferred as FLOAT. Further investigation indicates that the difference may be associated with the inferred result type of MIN(). When the aggregation results are explicitly cast to DOUBLE, all three representations return the same value: 5.207821014845463e37 I also tested the same three representations on MariaDB 12.2.2, where all three queries consistently returned: 5.20782e37