Bug #54916 GROUP_CONCAT + IFNULL truncates output
Submitted: 30 Jun 2010 14:24 Modified: 23 Sep 2010 22:05
Reporter: Ian Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.4-m3-log, 5.5.6-m3 OS:Any (FreeBSD 7.2, Mac OS X, Ubuntu)
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: group_concat, ifnull, regression, truncate

[30 Jun 2010 14:24] Ian Lord
Description:
Mixing GROUP_CONCAT and IFNULL on a numeric value outputs a truncated result.

This doesnt happen on a varchar value and casting the value to char first works so it seems to be some sort of problem with auto casting.

How to repeat:
CREATE TABLE foo (
bar MEDIUMINT NULL
) ENGINE = MYISAM ;

INSERT INTO foo (bar) VALUES (1234567);

#### Outputs 123 instead of 1234567
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;

#### Switching to bigint produces different result
ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;

#### Outputs 123456 instead of 1234567
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;

#### Using IFNULL(bar,0) doesn't produce the error (outputs 1234567)
SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;

#### allowing of not null values doesnt seem to change anything
ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;

#### Outputs 123456 instead of 1234567
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;

#### Suggested temporaty workaround
#### CASTING THE VALUE to string first does the trick
SELECT GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) FROM foo;
[30 Jun 2010 14:39] Valeriy Kravchuk
Verified just as described with recent mysql-trunk from bzr:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE foo (
    -> bar MEDIUMINT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.41 sec)

mysql> 
mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123                          |
+------------------------------+
1 row in set (0.00 sec)

mysql> select * from bar;
ERROR 1146 (42S02): Table 'test.bar' doesn't exist
mysql> select * from foo;
+---------+
| bar     |
+---------+
| 1234567 |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 
mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;
+-----------------------------+
| GROUP_CONCAT(IFNULL(bar,0)) |
+-----------------------------+
| 1234567                     |
+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;
Query OK, 1 row affected (0.45 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) FROM foo;
+--------------------------------------------+
| GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) |
+--------------------------------------------+
| 1234567                                    |
+--------------------------------------------+
1 row in set (0.00 sec)
[30 Jun 2010 14:40] Valeriy Kravchuk
Not repeatable with current 5.1:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE foo ( bar MEDIUMINT NULL ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      |
+------------------------------+
1 row in set (0.00 sec)

So, this is a regression bug.
[27 Jul 2010 12:44] Valeriy Kravchuk
Still happens in current mysql-trunk with

macbook-pro:mysql-trunk openxs$ bzr revision-info
3074 alik@sun.com-20100723121820-jryu2fuw3pc53q9w
[27 Jul 2010 13:12] Guilhem Bichot
I wanted to search for what revision introduced this, but I cannot repeat the bug. Valeriy reconfirmed the bug today with this tree: trunk at revision alik@sun.com-20100723121820-jryu2fuw3pc53q9w , built with compile-pentium-debug-max. I did the same (except that I'm using Linux and not OS X like him), and cannot repeat the bug. Neither with the latest trunk-bugfixing.
Could this be OS-specific?
[27 Jul 2010 13:14] Guilhem Bichot
queries I ran are like Valeriy's, and they all return 1234567:
CREATE TABLE foo (
bar MEDIUMINT NULL
) ENGINE = MYISAM ;
INSERT INTO foo (bar) VALUES (1234567);
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
select * from foo;
ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;
ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;
SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
SELECT GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) FROM foo;
drop table foo;
[27 Jul 2010 13:42] Guilhem Bichot
I'm asking Valeriy to check whether he can repeat it on Linux, when he can.
[27 Jul 2010 13:53] Valeriy Kravchuk
This is what I see on Mac OS X 10.5.6:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (
    -> bar MEDIUMINT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.39 sec)

mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123                          |
+------------------------------+
1 row in set (0.00 sec)

mysql> select * from foo;
+---------+
| bar     |
+---------+
| 1234567 |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;
Query OK, 1 row affected (0.13 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;
+-----------------------------+
| GROUP_CONCAT(IFNULL(bar,0)) |
+-----------------------------+
| 1234567                     |
+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) FROM foo;
+--------------------------------------------+
| GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) |
+--------------------------------------------+
| 1234567                                    |
+--------------------------------------------+
1 row in set (0.00 sec)

Still a bug on Mac OS X. Will check on Linux later.
[28 Jul 2010 15:28] Alexey Kopytov
I cannot repeat this on my Mac OS X 10.5.8 machine (BUILD/compile-pentium-debug-max, i686-apple-darwin9-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5577)):

mysql> CREATE TABLE foo (
    -> bar MEDIUMINT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.07 sec)

mysql> 
mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      | 
+------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      | 
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;
+-----------------------------+
| GROUP_CONCAT(IFNULL(bar,0)) |
+-----------------------------+
| 1234567                     | 
+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      | 
+------------------------------+
1 row in set (0.00 sec)
[28 Jul 2010 15:32] Alexey Kopytov
I used the latest mysql-trunk (revision-id: alik@sun.com-20100723121820-jryu2fuw3pc53q9w).
[29 Jul 2010 6:56] Valeriy Kravchuk
I don't know what am I doing wrong, but I am able to repeat on Ubuntu also:

openxs@ubuntu:/home2/openxs/bzr2/mysql-trunk-work$ bzr revision-info
3074 alik@sun.com-20100723121820-jryu2fuw3pc53q9w
openxs@ubuntu:/home2/openxs/bzr2/mysql-trunk-work$ cd ../../dbs/trunk/
openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysqld_safe --no-defaults &[1] 5765
openxs@ubuntu:/home2/openxs/dbs/trunk$ 100729 09:44:49 mysqld_safe Logging to '/home2/openxs/dbs/trunk/data/ubuntu.err'.
100729 09:44:49 mysqld_safe Starting mysqld daemon with databases from /home2/openxs/dbs/trunk/data

openxs@ubuntu:/home2/openxs/dbs/trunk$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table foo;
ERROR 1051 (42S02): Unknown table 'foo'
mysql> CREATE TABLE foo (
    -> bar MEDIUMINT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123                          |
+------------------------------+
1 row in set (0.01 sec)

mysql> select * from foo;
+---------+
| bar     |
+---------+
| 1234567 |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT NULL DEFAULT NULL;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,0)) FROM foo;
+-----------------------------+
| GROUP_CONCAT(IFNULL(bar,0)) |
+-----------------------------+
| 1234567                     |
+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE foo CHANGE bar bar BIGINT(20) NOT NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) FROM foo;
+--------------------------------------------+
| GROUP_CONCAT(IFNULL(CAST(bar AS CHAR),'')) |
+--------------------------------------------+
| 1234567                                    |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
openxs@ubuntu:/home2/openxs/dbs/trunk$ gcc --version
gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4)
Copyright (C) 2007 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

openxs@ubuntu:/home2/openxs/dbs/trunk$ uname -a
Linux ubuntu 2.6.24-28-generic #1 SMP Fri Jun 18 12:02:15 UTC 2010 i686 GNU/Linux
openxs@ubuntu:/home2/openxs/dbs/trunk$ getconf GNU_LIBC_VERSION
glibc 2.7
[30 Jul 2010 8:14] MySQL Verification Team
my output with 5.5.5 on windows.

Attachment: bug54916_5.5.5_windows_output.txt (text/plain), 2.82 KiB.

[30 Jul 2010 8:17] MySQL Verification Team
you need "set names utf8" to be run first ....
from 5.5.5:
<cut>

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123456                       |
+------------------------------+
1 row in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      |
+------------------------------+
1 row in set (0.00 sec)

</cut>
[30 Jul 2010 8:22] Valeriy Kravchuk
Actually, this is related to utf8 used by default in 5.5 when it is set in LANG (5.1 does NOT have that). Look:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.6-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | latin1                                        |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |
| character_sets_dir       | /Users/openxs/dbs/trunk/share/mysql/charsets/ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | latin1                                        |
| character_set_connection | latin1                                        |
| character_set_database   | latin1                                        |
| character_set_filesystem | binary                                        |
| character_set_results    | latin1                                        |
| character_set_server     | latin1                                        |
| character_set_system     | utf8                                          |
| character_sets_dir       | /Users/openxs/dbs/trunk/share/mysql/charsets/ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

mysql> CREATE TABLE foo ( bar MEDIUMINT NULL ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.40 sec)

mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 1234567                      |
+------------------------------+
1 row in set (0.01 sec)

mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo ( bar MEDIUMINT NULL ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO foo (bar) VALUES (1234567);
Query OK, 1 row affected (0.00 sec)

mysql>  SELECT GROUP_CONCAT(IFNULL(bar,'')) FROM foo;
+------------------------------+
| GROUP_CONCAT(IFNULL(bar,'')) |
+------------------------------+
| 123                          |
+------------------------------+
1 row in set (0.00 sec)

So, add

set names utf8;

to the test and you should be able to reproduce.

Thanks Shane for testing and hint.
[30 Jul 2010 9:23] John Embretsen
Looks like this issue depends on server configuration, not necessarily platform.

I was not able to repeat on Ubuntu using mysql-test-run.pl (MTR), but I was able to repeat, both on Ubuntu and other platforms (e.g. OpenSolaris), when starting the server manually. I am not sure why there is this difference, but I will provide a script for my way of starting the server soon, allowing further analysis.
[30 Jul 2010 9:28] John Embretsen
Sorry, I did not notice the last comment before I posted. No need for me to provide extra scripts, since issue is reproducible also with MTR if adding

set names utf8;
[2 Aug 2010 12:32] Alexander Barkov
Another SQL script demonstrating the same problem:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 AS SELECT IFNULL(a,'') FROM t1;
SHOW CREATE TABLE t2;

+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `IFNULL(a,'')` varchar(3) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

varchar(3) is too short. Expected result is varchar(9).
[2 Aug 2010 13:54] Alexander Barkov
The problem can be observed with IF and CASE:

SET NAMES utf8;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IF(a,a,'')), GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) FROM t1;

+--------------------------+----------------------------------------------+
| GROUP_CONCAT(IF(a,a,'')) | GROUP_CONCAT(CASE WHEN a THEN a ELSE '' END) |
+--------------------------+----------------------------------------------+
| 123                      | 123                                          |
+--------------------------+----------------------------------------------+
1 row in set (0.00 sec)
[3 Aug 2010 5:40] Alexander Barkov
Similar problem with COALESCE:

SET NAMES utf8;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IF(a,a,'')), COALESCE(a,'') FROM t1;
SELECT COALESCE(a,'') FROM t1 GROUP BY a;
[3 Aug 2010 5:40] Alexander Barkov
The above script returns:

+----------------+
| COALESCE(a,'') |
+----------------+
| 123            |
+----------------+
[3 Aug 2010 5:43] Alexander Barkov
Another related problem with COALESCE:

mysql --column-type-info test << END
SET NAMES utf8;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
SELECT GROUP_CONCAT(IF(a,a,'')), COALESCE(a,'') FROM t1;
END

The second column is reported as VARBINARY(9):

Field   2:  `COALESCE(a,'')`
Type:       VAR_STRING
Collation:  binary (63)
Length:     9
Max_length: 7
Decimals:   31

The expected result is "VARCHAR(9) CHARACTER SET utf8".
[3 Aug 2010 7:53] Alexander Barkov
A similar script with COALESCE and explicit GROUP clause truncates output:

SET NAMES utf8;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
SELECT COALESCE(a,'') FROM t1 GROUP BY 1;

+----------------+
| COALESCE(a,'') |
+----------------+
| 123            |
+----------------+
1 row in set (0.00 sec)
[3 Aug 2010 8:29] Alexander Barkov
Similar truncation happens with CONCAT_WS:

SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT CONCAT_WS(1,2,3) FROM t1;
SHOW WARNINGS;
SHOW CREATE TABLE t2;

+---------+------+-------------------------------------------------------+
| Level   | Code | Message                                               |
+---------+------+-------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'CONCAT_WS(1,2,3)' at row 1 |
+---------+------+-------------------------------------------------------+
1 row in set (0.00 sec)

+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `CONCAT_WS(1,2,3)` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Expected data type is varchar(3).
[3 Aug 2010 8:33] Alexander Barkov
Truncation with INSERT:

SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT INSERT(1133,3,0,22) FROM t1;
SHOW CREATE TABLE t2;

+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `INSERT(1133,3,0,22)` varchar(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Expected data type is varchar(6).
[3 Aug 2010 8:35] Alexander Barkov
Truncation with LCASE:

SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT LCASE(a) FROM t1;
SHOW CREATE TABLE t2;

+-------+--------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `LCASE(a)` varchar(3) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Expected column type is varchar(9).
[3 Aug 2010 8:37] Alexander Barkov
SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT repeat(1,2) FROM t1;
SHOW CREATE TABLE t2;

+-------+------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `repeat(1,2)` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Expected column type is varchar(2).
[3 Aug 2010 8:51] Alexander Barkov
Truncation with left:

SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT left(123,2) FROM t1;
SHOW WARNINGS;
SHOW CREATE TABLE t2;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'left(123,2)' at row 1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `left(123,2)` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------+

Expected data type is varchar(2).

The same problem happens with right(123,2).
[3 Aug 2010 8:53] Alexander Barkov
Truncation with ltrim/rtrim:

SET NAMES utf8;
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (a MEDIUMINT NULL);
INSERT INTO t1 VALUES (1234567);
CREATE TABLE t2 AS SELECT rtrim(123) FROM t1;
SHOW WARNINGS;
SHOW CREATE TABLE t2;

+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'rtrim(123)' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `rtrim(123)` varchar(1) CHARACTER SET utf8 NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Expected data type is varchar(3).
[3 Aug 2010 9:01] Alexander Barkov
ELT also causes truncation in the same conditions:

CREATE TABLE t2 AS SELECT elt(1,111,222,333) FROM t1;

varchar(1) instead of varchar(3) is created.
[3 Aug 2010 12:03] Alexander Barkov
Replace() truncates in the same context:

CREATE TABLE t2 AS SELECT replace(111,2,3) FROM t1;

varchar(1) instead if varchar(3) is created.
[3 Aug 2010 12:10] Alexander Barkov
SUBSTRING_INDEX() truncates in the same context:

CREATE TABLE t2 AS SELECT substring_index(111,111,1) FROM t1;

varchar(1) is created instead of varchar(3).
[3 Aug 2010 12:15] Alexander Barkov
MAKE_SET() truncates in the same context:

CREATE TABLE t2 AS SELECT make_set(111,222,3) FROM t1;

varchar(2) is created instead of varchar(5).
[4 Aug 2010 8:44] Alexander Barkov
SOUNDEX also truncates:

CREATE TABLE t2 AS SELECT soundex(1) FROM t1;

varchar(2) is created instead of varchar(4) expected.
[4 Aug 2010 10:49] Alexander Barkov
EXPORT_SET() also returns wrong size for utf8.

CREATE TABLE t2 AS SELECT export_set(1,'Y','N','',8);

should create a varchar(64) column, not matter what 
the session character set is.
[4 Aug 2010 13:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115020

3162 Alexander Barkov	2010-08-04
      Bug#54916 GROUP_CONCAT + IFNULL truncates output
      
      Problem: a few functions did not calculate their max_length correctly.
      This is an after-fix for WL#2649 Number-to-string conversions".
      
      Fix: changing the buggy functions to calculate max_length
      using fix_char_length() introduced in WL#2649,
      instead of setting max_length directly
      
        mysql-test/include/ctype_numconv.inc
           Adding new tests
      
        mysql-test/r/ctype_binary.result
           Adding new tests
      
        mysql-test/r/ctype_cp1251.result
           Adding new tests
      
        mysql-test/r/ctype_latin1.result
           Adding new tests
      
        mysql-test/r/ctype_ucs.result
           Adding new tests
      
        mysql-test/r/ctype_utf8.result
           Adding new tests
      
        mysql-test/t/ctype_utf8.test
          Including ctype_numconv
      
        sql/item.h
          - Introducing new method fix_char_length_ulonglong(),
          for the cases when length is potentially greater
          than UINT_MAX32. This method removes a few
          instances of duplicate code, e.g. in item_strfunc.cc.
          - Setting collation in Item_copy properly. This change
          fixes wrong metadata on client side in some cases, when
          "binary" instead of the real character set was reported.
      
        sql/item_cmpfunc.cc
          - Using fix_char_length() and max_char_length() methods,
          instead of direct access to max_length, to calculate
          item length properly.
          - Moving count_only_length() in COALESCE after
          agg_arg_charsets_for_string_result(). The old
          order was incorrect and led to wrong length
          calucation in case of multi-byte character sets.
          
        sql/item_func.cc
          Fixing that count_only_length() didn't work
          properly for multi-byte character sets.
          Using fix_char_length() and max_char_length()
          instead of direct access to max_length.
      
        sql/item_strfunc.cc
          - Using fix_char_length(), fix_char_length_ulonglong(),
          max_char_length() instead of direct access to max_length.
          - Removing wierd condition: "if (collation.collation->mbmaxlen > 0)",
          which is never FALSE.
[17 Aug 2010 11:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115919

3162 Alexander Barkov	2010-08-17
      Bug#54916 GROUP_CONCAT + IFNULL truncates output
      
      Problem: a few functions did not calculate their max_length correctly.
      This is an after-fix for WL#2649 Number-to-string conversions".
      
      Fix: changing the buggy functions to calculate max_length
      using fix_char_length() introduced in WL#2649,
      instead of setting max_length directly
      
        mysql-test/include/ctype_numconv.inc
           Adding new tests
      
        mysql-test/r/case.result
          Adding new tests
      
        mysql-test/r/ctype_binary.result
           Adding new tests
      
        mysql-test/r/ctype_cp1251.result
           Adding new tests
      
        mysql-test/r/ctype_latin1.result
           Adding new tests
      
        mysql-test/r/ctype_ucs.result
           Adding new tests
      
        mysql-test/r/ctype_utf8.result
           Adding new tests
      
        mysql-test/t/case.test
           Adding new tests
      
        mysql-test/t/ctype_utf8.test
          Including ctype_numconv
      
        sql/item.h
          - Introducing new method fix_char_length_ulonglong(),
          for the cases when length is potentially greater
          than UINT_MAX32. This method removes a few
          instances of duplicate code, e.g. in item_strfunc.cc.
          - Setting collation in Item_copy properly. This change
          fixes wrong metadata on client side in some cases, when
          "binary" instead of the real character set was reported.
      
        sql/item_cmpfunc.cc
          - Using fix_char_length() and max_char_length() methods,
          instead of direct access to max_length, to calculate
          item length properly.
          - Moving count_only_length() in COALESCE after
          agg_arg_charsets_for_string_result(). The old
          order was incorrect and led to wrong length
          calucation in case of multi-byte character sets.
          - Check return code of agg_arg_charsets_for_string_result()
          and return from fix_char_length() immediately on error.
          
        sql/item_func.cc
          Fixing that count_only_length() didn't work
          properly for multi-byte character sets.
          Using fix_char_length() and max_char_length()
          instead of direct access to max_length.
      
        sql/item_strfunc.cc
          - Using fix_char_length(), fix_char_length_ulonglong(),
          max_char_length() instead of direct access to max_length.
          - Removing wierd condition: "if (collation.collation->mbmaxlen > 0)",
          which is never FALSE.
[17 Aug 2010 12:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115956

3183 Alexander Barkov	2010-08-17
           Bug#54916 GROUP_CONCAT + IFNULL truncates output
      
      Problem: a few functions did not calculate their max_length correctly.
      This is an after-fix for WL#2649 Number-to-string conversions".
      
      Fix: changing the buggy functions to calculate max_length
      using fix_char_length() introduced in WL#2649,
      instead of setting max_length directly
      
        mysql-test/include/ctype_numconv.inc
           Adding new tests
      
        mysql-test/r/ctype_binary.result
           Adding new tests
      
        mysql-test/r/ctype_cp1251.result
           Adding new tests
      
        mysql-test/r/ctype_latin1.result
           Adding new tests
      
        mysql-test/r/ctype_ucs.result
           Adding new tests
      
        mysql-test/r/ctype_utf8.result
           Adding new tests
      
        mysql-test/t/ctype_utf8.test
          Including ctype_numconv
      
        sql/item.h
          - Introducing new method fix_char_length_ulonglong(),
          for the cases when length is potentially greater
          than UINT_MAX32. This method removes a few
          instances of duplicate code, e.g. in item_strfunc.cc.
          - Setting collation in Item_copy properly. This change
          fixes wrong metadata on client side in some cases, when
          "binary" instead of the real character set was reported.
      
        sql/item_cmpfunc.cc
          - Using fix_char_length() and max_char_length() methods,
          instead of direct access to max_length, to calculate
          item length properly.
          - Moving count_only_length() in COALESCE after
          agg_arg_charsets_for_string_result(). The old
          order was incorrect and led to wrong length
          calucation in case of multi-byte character sets.
          
        sql/item_func.cc
          Fixing that count_only_length() didn't work
          properly for multi-byte character sets.
          Using fix_char_length() and max_char_length()
          instead of direct access to max_length.
      
        sql/item_strfunc.cc
          - Using fix_char_length(), fix_char_length_ulonglong(),
          max_char_length() instead of direct access to max_length.
          - Removing wierd condition: "if (collation.collation->mbmaxlen > 0)",
          which is never FALSE.
[19 Aug 2010 12:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116236

3190 Alexander Barkov	2010-08-19
      Bug#54916 GROUP_CONCAT + IFNULL truncates output
      
      Problem: a few functions did not calculate their max_length correctly.
      This is an after-fix for WL#2649 Number-to-string conversions".
      
      Fix: changing the buggy functions to calculate max_length
      using fix_char_length() introduced in WL#2649,
      instead of setting max_length directly
      
        mysql-test/include/ctype_numconv.inc
           Adding new tests
      
        mysql-test/r/ctype_binary.result
           Adding new tests
      
        mysql-test/r/ctype_cp1251.result
           Adding new tests
      
        mysql-test/r/ctype_latin1.result
           Adding new tests
      
        mysql-test/r/ctype_ucs.result
           Adding new tests
      
        mysql-test/r/ctype_utf8.result
           Adding new tests
      
        mysql-test/t/ctype_utf8.test
          Including ctype_numconv
      
        sql/item.h
          - Introducing new method fix_char_length_ulonglong(),
          for the cases when length is potentially greater
          than UINT_MAX32. This method removes a few
          instances of duplicate code, e.g. in item_strfunc.cc.
          - Setting collation in Item_copy properly. This change
          fixes wrong metadata on client side in some cases, when
          "binary" instead of the real character set was reported.
      
        sql/item_cmpfunc.cc
          - Using fix_char_length() and max_char_length() methods,
          instead of direct access to max_length, to calculate
          item length properly.
          - Moving count_only_length() in COALESCE after
          agg_arg_charsets_for_string_result(). The old
          order was incorrect and led to wrong length
          calucation in case of multi-byte character sets.
          
        sql/item_func.cc
          Fixing that count_only_length() didn't work
          properly for multi-byte character sets.
          Using fix_char_length() and max_char_length()
          instead of direct access to max_length.
      
        sql/item_strfunc.cc
          - Using fix_char_length(), fix_char_length_ulonglong(),
          max_char_length() instead of direct access to max_length.
          - Removing wierd condition: "if (collation.collation->mbmaxlen > 0)",
          which is never FALSE.
[23 Aug 2010 9:05] Alexander Barkov
Pushed into mysql-5.5-bugfixing (5.5.6-m3)
Pushed into mysql-trunk-bugfixing (5.6.1-m4)
Pushed into mysql-next-mr-bugfixing (5.6.99-m5)
[25 Aug 2010 9:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:31] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[23 Sep 2010 22:05] Paul DuBois
Noted in 5.5.6, 5.6.1 changelogs.

Some functions did not calculate their max_length metadata value
correctly.