Bug #120075 Silent Data Truncation of DOUBLE (DBL_MAX) in Subqueries/Temporary Tables

• Submitted: 16 Mar 14:26
• Modified: 18 Mar 3:12
• Reporter: 策 吕
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.45
• OS: Linux (ubuntu 20.04)
• CPU Architecture: x86

[16 Mar 14:26] 策 吕

Description:
When a DOUBLE column stores the minimum possible value (-1.7976931348623157E308), performing a GROUP BY within a subquery results in silent string truncation. The resulting string is truncated from 23 characters to 22 characters, effectively changing the value from e308 to e30.

mysql> -- -1.7976931348623157e308
mysql> SELECT CONCAT(t0.c1) FROM t0 GROUP BY t0.c1 HAVING COUNT(DISTINCT t0.c0) >= -1;
+-------------------------+
| CONCAT(t0.c1)           |
+-------------------------+
| -1.7976931348623157e308 |
+-------------------------+
1 row in set (0.00 sec)

mysql> -- -1.7976931348623157e30
mysql> SELECT ref0 FROM (SELECT CONCAT(t0.c1) AS ref0, (COUNT(DISTINCT t0.c0) >= -1) AS ref1 FROM t0 GROUP BY t0.c1) AS s WHERE ref1;
+------------------------+
| ref0                   |
+------------------------+
| -1.7976931348623157e30 |
+------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat:

CREATE TABLE t0(c0 FLOAT, c1 DOUBLE , c2 DECIMAL) ;
INSERT INTO t0(c0, c1, c2) VALUES(0, -1.7976931348623157E308,1617280315);

-- -1.7976931348623157e308
SELECT ALL CONCAT(t0.c1) FROM t0 GROUP BY t0.c1 HAVING COUNT(DISTINCT t0.c0) >= -1;
-- -1.7976931348623157e30
SELECT ALL ref0 FROM (SELECT CONCAT(t0.c1) AS ref0, (COUNT(DISTINCT t0.c0) >= -1) AS ref1 FROM t0 GROUP BY t0.c1) AS s WHERE ref1;

[17 Mar 10:49] Roy Lyseng

Thank you for the bug report.
Verified as described.

[18 Mar 3:12] 策 吕

Thank you for verifying this issue.

May I ask whether this bug has already been added to the internal fix queue or assigned to a development cycle? I would also appreciate it if you could share whether there is any tentative target version for the fix.

Thank you again for your time and help.