Bug #48464 SHOW CREATE TABLE does not display DEFAULT values for UCS2 columns
Submitted: 2 Nov 2009 12:00 Modified: 3 Nov 2009 11:56
Reporter: Alexander Barkov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.x, 5.1.41-bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[2 Nov 2009 12:00] Alexander Barkov
Description:
If I run this script:

set names utf8;
drop table if exists t1;
create table t1 (a varchar(1) default 0x2020 /* U+2020 DAGGER */) character set ucs2;
show create table t1;

I get correct output and can see the DAGGER character in SHOW CREATE TABLE output:

+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(1) DEFAULT '†'
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 |
+-------+-----------------------------------------------------------------------------------------+

If I now change the session character set to latin1
and run the same script, the DAGGER character in the
DEFAULT value gets lost:

set names latin1;
drop table if exists t1;
create table t1 (a varchar(1) default 0x2020 /* U+2020 DAGGER */) character set ucs2;
show create table t1;

+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(1) DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 |
+-------+---------------------------------------------------------------------------------------+

How to repeat:
set names latin1;
drop table if exists t1;
create table t1 (a varchar(1) default 0x2020 /* U+2020 DAGGER */) character set ucs2;
show create table t1;

Suggested fix:
If the column character set cannot be converted to the client character set,
then SHOW CREATE TABLE should switch to some other safe way to print the 
DEFAULT values. For example, HEX encoding could work:

CREATE TABLE `t1` (
  `a` varchar(1) DEFAULT 0x2020
) ENGINE=MyISAM DEFAULT CHARSET=ucs2
[2 Nov 2009 12:14] Valeriy Kravchuk
Thank you for the bug report. Verified just as described with recent 5.1.41 from bzr on Mac OS X:

77-52-222-60:5.1 openxs$ bin/mysql -uroot -pmysql 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.41-debug Source distribution

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

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

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t1 (a varchar(1) default 0x2020 /* U+2020 DAGGER */)
    -> character set ucs2;
Query OK, 0 rows affected (0.31 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(1) DEFAULT '†'
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a varchar(1) default 0x2020 /* U+2020 DAGGER */) character set ucs2;
Query OK, 0 rows affected (0.42 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(1) DEFAULT '?'
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[2 Nov 2009 12:59] Alexander Barkov
Sorry for a mistake in the initial bug report.
The problem with latin1 was because of wrong character set 
settings in the console program. U+2020 DAGGER is actually
a part of MySQL's latin1.

But this problem still happens with other character sets,
which don't include the DAGGER character:

set names latin2;
drop table if exists t1; create table t1 (a varchar(2) character set ucs2 default 0x2020);
show create table t1;

+-------+------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(2) CHARACTER SET ucs2 DEFAULT '?'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now dagger is replaced to the question mark '?'.
[2 Nov 2009 13:50] 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/88933

2920 Mikael Ronstrom	2009-11-02
      Fixed a few bugs in hex string generation, in call to val_str for partition expressions, also made code reusable for DEFAULT handling to fix BUG#48464 by introducing function get_cs_converted_string_value, added partition_utf8 test case for UTF8 outputs
      added:
        mysql-test/r/partition_utf8.result
        mysql-test/t/partition_utf8.test
      modified:
        BUILD/build_mccge.sh
        mysql-test/r/partition_column.result
        mysql-test/t/partition_column.test
        sql/mysql_priv.h
        sql/sql_partition.cc
        sql/sql_partition.h
        sql/sql_show.cc
[5 Nov 2009 6:51] Bugs System
Pushed into 6.0.14-alpha (revid:mikael@mysql.com-20091104090210-om5lq1v39ppduu0e) (version source revid:mikael@mysql.com-20091103112654-5ckwneo0dx2446p8) (merge vers: 6.0.14-alpha) (pib:13)
[12 Nov 2009 8:17] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102134926-l60k7ftv3865ej8n) (merge vers: 5.5.0-beta) (pib:13)