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: | |
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
[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.