MySQL Bugs: #92297: GROUP_CONCAT doesn't work properly with FLOAT(M,D) fields with value of M digits

Bug #92297	GROUP_CONCAT doesn't work properly with FLOAT(M,D) fields with value of M digits
Submitted:	5 Sep 2018 16:29	Modified:	6 Sep 2018 7:07
Reporter:	Stoil Todorov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.12, 5.7.23, 5.6.41, 5.5.61	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	D) bug, GROUP_CONCAT and FLOAT(M

Description:
if we execute this SQL -> SELECT GROUP_CONCAT(CONCAT('"',price,'"'))... and price is DB field with type FLOAT(M,D) and value of price is with M digits, the result will be without second quota.

Example, price is FLOAT(9,3) with value 100000.000, we expect "100000.000" but result will be "100000.000
We found this with MySQL version 5.6.33 but reproduce it with new version too.

How to repeat:
DROP DATABASE test_db; CREATE DATABASE test_db; USE test_db;

CREATE TABLE `test` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `price` FLOAT(9,3) DEFAULT NULL,
  PRIMARY KEY USING BTREE (`id`)
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

INSERT INTO `test` (`id`, `price`) VALUES
  (1, 1.000),
  (2, 100000.000),
  (3, 55555.000);

mysql> select * from test_db.test;
+----+------------+
| id | price      |
+----+------------+
|  1 |      1.000 |
|  2 | 100000.000 |
|  3 |  55555.000 |
+----+------------+
3 rows in set (0.01 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('"',t.price,'"')) AS PRICE FROM test_db.test AS t WHERE t.id=2;
+-------------+
| PRICE       |
+-------------+
| "100000.000 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('"',t.price,'"')) AS PRICE FROM test_db.test AS t WHERE t.id=3;
+-------------+
| PRICE       |
+-------------+
| "55555.000" |
+-------------+
1 row in set (0.00 sec)

The issue exists with DOUBLE(M,D) too, but doesn't exist with DECIMAL(M,D)

Hello Stoil Todorov,

Thank you for the report!

regards,
Umesh

Posted by developer:
 
The problem here is the internal assignment of max_length to the Item_func_concat: it gets assigned a value of
11 instead of 12, as a sum of 1 for the first quote, 9 for the float (wrong, when formatted it should be 10), and 1 for the final float, i.e. 11. The number 9 derived all the way from the original specification as FLOAT(9,3).