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:
None 
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

[3 Mar 2006 17:49] Peter Gulutzan
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)
[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.