Description:
Some FORMAT() builtins sometimes give truncated results when they're in the project list of a JOIN.
I think what's happening is that MySQL is incorrectly computing the max_length of the Item_func_format, and then using that incorrect (too-short) max_length when it goes to populate the intermediate temporary table and/or the file used for sorting the output. The result is that the output is truncated for such JOINs.
As demonstrated below, there's no problem with a plain old JOIN, but as soon as you ORDER or GROUP the output, MySQL will sort it, which means you'll see truncation.
How to repeat:
CREATE TABLE f (value FLOAT);
INSERT INTO f VALUES (340282346638528860000000000000000000000);
CREATE TABLE dummy (i INT);
INSERT INTO dummy VALUES ();
SELECT FORMAT(value,0) a FROM (f JOIN dummy) ORDER BY value;
SELECT FORMAT(value,0) a FROM (f JOIN dummy) GROUP BY a;
+---------------------------------------------------+
| a |
+---------------------------------------------------+
| 340,282,346,638,528,860,000,000,000,000,000,000,0 |
+---------------------------------------------------+
SELECT FORMAT(value,0) a FROM (f JOIN dummy);
+-----------------------------------------------------+
| a |
+-----------------------------------------------------+
| 340,282,346,638,528,860,000,000,000,000,000,000,000 |
+-----------------------------------------------------+
EXPLAIN SELECT FORMAT(value,0) FROM (f JOIN dummy) ORDER BY value;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | dummy | ALL | NULL | NULL | NULL | NULL | 1 | Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
Suggested fix:
The bug might be wherever Item_field or Item_float set their max_lengths; or the bug might be that Item_func_format assumes its output will be "about the same length as the input" instead of hard-coding something like "40" for arguments of type FLOAT.
