Description:
UCS2 constants in a VIEW definition are left-padded with extra zero bytes.
How to repeat:
mysql> set names latin1; set character_set_connection=ucs2; drop view if exists v1; create view v1 as select concat('1','2','3') as c1; show create table v1; select * from v1; select hex(c1) from v1;
+------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select concat('\0\0\01','\0\0\02','\0\0\03') AS `c1` | latin1 | ucs2_general_ci |
+------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
+--------+
| c1 |
+--------+
| 1 2 3 |
+--------+
1 row in set (0.00 sec)
+--------------------------+
| hex(c1) |
+--------------------------+
| 000000310000003200000033 |
+--------------------------+
1 row in set (0.00 sec)
Suggested fix:
It seems the problem resides in the "CREATE VIEW" statement.
In version 5.0.27 I get correct results for "SHOW CREATE",
and for SELECTs. Please return 5.0 behaviour.
+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| View | Create View |
+------+------------------------------------------------------------------------------------------------------------------------------------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`bar`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select concat(_ucs2'\01',_ucs2'\02',_ucs2'\03') AS `c1` |
+------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
+-----+
| c1 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)
+--------------+
| hex(c1) |
+--------------+
| 003100320033 |
+--------------+
1 row in set (0.00 sec)
Description: UCS2 constants in a VIEW definition are left-padded with extra zero bytes. How to repeat: mysql> set names latin1; set character_set_connection=ucs2; drop view if exists v1; create view v1 as select concat('1','2','3') as c1; show create table v1; select * from v1; select hex(c1) from v1; +------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select concat('\0\0\01','\0\0\02','\0\0\03') AS `c1` | latin1 | ucs2_general_ci | +------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec) +--------+ | c1 | +--------+ | 1 2 3 | +--------+ 1 row in set (0.00 sec) +--------------------------+ | hex(c1) | +--------------------------+ | 000000310000003200000033 | +--------------------------+ 1 row in set (0.00 sec) Suggested fix: It seems the problem resides in the "CREATE VIEW" statement. In version 5.0.27 I get correct results for "SHOW CREATE", and for SELECTs. Please return 5.0 behaviour. +------+------------------------------------------------------------------------------------------------------------------------------------------------+ | View | Create View | +------+------------------------------------------------------------------------------------------------------------------------------------------------+ | v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`bar`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select concat(_ucs2'\01',_ucs2'\02',_ucs2'\03') AS `c1` | +------+------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) +-----+ | c1 | +-----+ | 123 | +-----+ 1 row in set (0.00 sec) +--------------+ | hex(c1) | +--------------+ | 003100320033 | +--------------+ 1 row in set (0.00 sec)