| Bug #17903 | cast to char results in binary | ||
|---|---|---|---|
| Submitted: | 3 Mar 2006 18:49 | Modified: | 16 Jul 21:59 |
| Reporter: | Peter Gulutzan | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.0-BK, 5.1.8-beta | OS: | Linux (SUSE 10.0) |
| Assigned to: | Tatjana A. Nuernberg | Target Version: | |
[4 Mar 2006 18:06]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described. Also on 5.0.x.
[12 Jul 2006 21: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 22: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
[19 Jul 2006 1:56]
Jim Winstead
Pushed to mysql-5.0-maint.
[3 Aug 2006 18:46]
Magnus Blaudd
Pushed to 5.0.25
[4 Aug 2006 5: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 22:19]
Konstantin Osipov
Pushed into 5.1.12
[15 Aug 2006 5:28]
Paul DuBois
Noted in 5.1.12 changelog.
[26 Jun 5: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 15:17]
Tatjana A. Nuernberg
Queued for 5.4.4 in azalea-bugfixing.
[3 Jul 8: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 9: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 21: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.
[13 Aug 1: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 4:20]
Paul DuBois
Ignore previous comment about 5.4.2.
[28 Oct 10: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 6: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.
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)