| Bug #17903 | cast to char results in binary | ||
|---|---|---|---|
| Submitted: | 3 Mar 2006 17:49 | Modified: | 6 Mar 2010 19:12 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-BK, 5.1.8-beta | OS: | Linux (SUSE 10.0) |
| Assigned to: | Tatiana Azundris Nuernberg | CPU Architecture: | Any |
[4 Mar 2006 17:06]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described. Also on 5.0.x.
[12 Jul 2006 19:36]
Jim Winstead
it's even worse than reported: select hex(cast('a' as char(2) binary)); now causes the server to crash.
[12 Jul 2006 20:16]
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/9098
[18 Jul 2006 23:56]
Jim Winstead
Pushed to mysql-5.0-maint.
[3 Aug 2006 16:46]
Magnus Blåudd
Pushed to 5.0.25
[4 Aug 2006 3:48]
Paul DuBois
Noted in 5.0.25 changelog. The character set was not being properly initialized for CAST() with a type like CHAR(2) BINARY, which resulted in incorrect results or even a server crash.
[14 Aug 2006 20:19]
Konstantin Osipov
Pushed into 5.1.12
[15 Aug 2006 3:28]
Paul DuBois
Noted in 5.1.12 changelog.
[26 Jun 2009 3:30]
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/77268 2811 Tatiana A. Nurnberg 2009-06-26 Bug#17903: cast to char results in binary Regression. The character set was not being properly initialized for CAST() with a type like CHAR(2) BINARY, which resulted in incorrect results or even a server crash. Added needed initialization. Re-enabled test case, and updated it. @ mysql-test/r/cast.result update results, Raider is now Twix, and the error's a warning now @ mysql-test/t/disabled.def re-enable "cast", that shoulda been on as per 5.1 anyway @ sql/sql_yacc.yy Need to reset charset here, otherwise, we'll get all kinds of random carry-overs, leading to wrong data, or even crashes. Do not want!
[26 Jun 2009 13:17]
Tatiana Azundris Nuernberg
Queued for 5.4.4 in azalea-bugfixing.
[3 Jul 2009 6:13]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626091141-s0jhc72bbgyqudc3) (merge vers: 5.4.4-alpha) (pib:11)
[9 Jul 2009 7:35]
Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:holyfoot@mysql.com-20090626091141-s0jhc72bbgyqudc3) (merge vers: 5.4.4-alpha) (pib:11)
[16 Jul 2009 19:59]
Paul DuBois
Noted in 5.4.4 changelog. The character set was not being properly initialized for CAST() with a type such as CHAR(2) BINARY, which resulted in incorrect results or a server crash.
[12 Aug 2009 23:03]
Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:20]
Paul DuBois
Ignore previous comment about 5.4.2.
[28 Oct 2009 9:26]
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/88462 2907 Alexander Barkov 2009-10-28 # # Bug#24690 Stored functions: RETURNing UTF8 strings # do not return UTF8_UNICODE_CI collation # # Bug#17903: cast to char results in binary # Regression. The character set was not being properly initialized # for CAST() with a type like CHAR(2) BINARY, which resulted in # incorrect results or even a server crash. # Backporting from mysql-6.0-codebase. mysql-test/r/sp-ucs2.result: mysql-test/t/sp-ucs2.test: Adding tests sql/mysql_priv.h: Adding prototype sql/sp.cc Remember COLLATE clause for non-default collations sql/sql_parse.cc Adding a new helper function sql/sql_yacc.yy - Allow "CHARACTER SET cs COLLATE cl" in SP parameters, RETURNS, DECLARE - Minor reorganization for "ASCII" and "UNICODE" related rules, to make the code more readable, also to allow these aliases: * "VARCHAR(10) ASCII BINARY" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) BINARY ASCII" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin Previously these four aliases returned the error "This version of MySQL does not yet support return value collation". Note: This patch allows "VARCHAR(10) CHARACTER SET cs COLLATE cl" and the above four aliases. "VARCHAR(10) COLLATE cl" is still not allowed i.e. when COLLATE is given without CHARACTER SET. If we want to support this, we need an architecture decision which character set to use by default.
[30 Oct 2009 5:01]
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/88668 2912 Alexander Barkov 2009-10-30 # # Bug#24690 Stored functions: RETURNing UTF8 strings # do not return UTF8_UNICODE_CI collation # # Bug#17903: cast to char results in binary # Regression. The character set was not being properly initialized # for CAST() with a type like CHAR(2) BINARY, which resulted in # incorrect results or even a server crash. # Backporting from mysql-6.0-codebase. mysql-test/r/sp-ucs2.result: mysql-test/t/sp-ucs2.test: Adding tests sql/mysql_priv.h: Adding prototype sql/sp.cc Remember COLLATE clause for non-default collations sql/sql_parse.cc Adding a new helper function sql/sql_yacc.yy - Allow "CHARACTER SET cs COLLATE cl" in SP parameters, RETURNS, DECLARE - Minor reorganization for "ASCII" and "UNICODE" related rules, to make the code more readable, also to allow these aliases: * "VARCHAR(10) ASCII BINARY" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) BINARY ASCII" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin Previously these four aliases returned the error "This version of MySQL does not yet support return value collation". Note: This patch allows "VARCHAR(10) CHARACTER SET cs COLLATE cl" and the above four aliases. "VARCHAR(10) COLLATE cl" is still not allowed i.e. when COLLATE is given without CHARACTER SET. If we want to support this, we need an architecture decision which character set to use by default.
[9 Nov 2009 11:21]
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/89772 2943 Alexander Barkov 2009-11-09 # # Bug#24690 Stored functions: RETURNing UTF8 strings # do not return UTF8_UNICODE_CI collation # # Bug#17903: cast to char results in binary # Regression. The character set was not being properly initialized # for CAST() with a type like CHAR(2) BINARY, which resulted in # incorrect results or even a server crash. # Backporting from mysql-6.0-codebase. mysql-test/r/sp-ucs2.result: mysql-test/t/sp-ucs2.test: Adding tests sql/mysql_priv.h: Adding prototype sql/sp.cc Remember COLLATE clause for non-default collations sql/sql_parse.cc Adding a new helper function sql/sql_yacc.yy - Allow "CHARACTER SET cs COLLATE cl" in SP parameters, RETURNS, DECLARE - Minor reorganization for "ASCII" and "UNICODE" related rules, to make the code more readable, also to allow these aliases: * "VARCHAR(10) ASCII BINARY" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) BINARY ASCII" -> CHARACTER SET latin1 COLLATE latin1_bin * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin Previously these four aliases returned the error "This version of MySQL does not yet support return value collation". Note: This patch allows "VARCHAR(10) CHARACTER SET cs COLLATE cl" and the above four aliases. "VARCHAR(10) COLLATE cl" is still not allowed i.e. when COLLATE is given without CHARACTER SET. If we want to support this, we need an architecture decision which character set to use by default.
[9 Nov 2009 11:34]
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/89775 3702 Alexander Barkov 2009-11-09 [merge] Null-merging Bug#24690 Bug#17903
[20 Nov 2009 12:57]
Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:epotemkin@mysql.com-20091109132131-ad1gk2d2tn9o5i3l) (merge vers: 6.0.14-alpha) (pib:13)
[23 Nov 2009 16:46]
Paul DuBois
Noted in 6.0.14 changelog. Setting report to NDI pending push to 5.6.x.
[11 Dec 2009 6:03]
Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:28]
Paul DuBois
Noted in 5.6.0 changelog.
[6 Mar 2010 10:55]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 19:12]
Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.
Description: If I cast as CHAR(2) BINARY, the result data type should be CHAR or VARCHAR. Sometimes it is. But if I used a cast as BINARY(2) in an earlier statement, and then do a cast as CHAR(2) BINARY, the result data type is BINARY or VARBINARY. This is clear from the padding (0x00 instead of 0x20), and it's clear from what SHOW CREATE TABLE says after I've done CREATE TABLE AS SELECT CAST(... AS CHAR(2) BINARY). How to repeat: Stop server. Restart server. mysql> select hex(cast('a' as char(2) binary)); +----------------------------------+ | hex(cast('a' as char(2) binary)) | +----------------------------------+ | 61 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select hex(cast('a' as binary(2))); +-----------------------------+ | hex(cast('a' as binary(2))) | +-----------------------------+ | 6100 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select hex(cast('a' as char(2) binary)); +----------------------------------+ | hex(cast('a' as char(2) binary)) | +----------------------------------+ | 6100 | +----------------------------------+ 1 row in set (0.00 sec) mysql> create table t1 as select cast('a' as char(2) binary); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `cast('a' as char(2) binary)` varbinary(2) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+-----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)