← Back to team overview

maria-developers team mailing list archive

Re: is this a bug?

 

  Hi Sergei,


On 05/01/2013 01:27 PM, Sergei Golubchik wrote:
Hi, Alexander!

Could you report this bug and fix it, please?

The report is here:
https://mariadb.atlassian.net/browse/MDEV-4489

Please review a patch fixing this problem (attached).


Note, while working on the patch I noticed one suspicious thing,
in the old implementation of Item_hex_string::print().
Always doing this "min" to calculate the string length to print
looked strange:

  min(str_value.length(), sizeof(longlong))

I fixed this behaviour in the class implementing X'HHHH',
it now prints the full length.
But I preserved the old behaviour in the class implementing 0xHHHH.
Perhaps it should also be fixed somehow. What do you think?


Thanks.


On Apr 30, Alexander Barkov wrote:
Hello,

This is definitely a bug.

0xHHHH is a MySQL extension, and it's a hybrid thing.
It can behave as a number and a string depending on context.

Binary log could use the X'HHHH' notation instead:

INSERT INTO t1 VALUES (a) VALUES (X'31');

which is an SQL standard, and which must always be a string.

However, it seems the behaviour of X'HHHH' and of 0xHHHH
is exactly the same, and X'HHHH' can also act as a number.
...
I'd propose the following as a quick fix:
1. Fix X'HHHH' to work always as string.
2. Fix binlog to use X'HHHH'

Regards,
Sergei

=== modified file 'mysql-test/r/ctype_cp932_binlog_stm.result'
--- mysql-test/r/ctype_cp932_binlog_stm.result	2011-10-19 19:45:18 +0000
+++ mysql-test/r/ctype_cp932_binlog_stm.result	2013-05-06 15:12:24 +0000
@@ -10,7 +10,7 @@ show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1(f1 blob)
 master-bin.000001	#	Query	#	#	BEGIN
-master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(0x8300)
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(X'8300')
 master-bin.000001	#	Query	#	#	COMMIT
 SELECT HEX(f1) FROM t1;
 HEX(f1)
@@ -44,7 +44,7 @@ BEGIN
 INSERT INTO t4 VALUES (ins1, ins2, ind);
 END
 master-bin.000001	#	Query	#	#	BEGIN
-master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'),  NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'),  NAME_CONST('ind',47.93))
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 X'466F6F2773206120426172' COLLATE 'latin1_swedish_ci'),  NAME_CONST('ins2',_cp932 X'ED40ED41ED42' COLLATE 'cp932_japanese_ci'),  NAME_CONST('ind',47.93))
 master-bin.000001	#	Query	#	#	COMMIT
 master-bin.000001	#	Query	#	#	use `test`; DROP PROCEDURE bug18293
 master-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t4` /* generated by server */
@@ -19491,5 +19491,38 @@ EFBFA4	FA55
 EFBFA5	818F
 DROP TABLE t1;
 #
+# Bug#MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
+#
+SET NAMES cp932;
+CREATE TABLE t1 (a INT);
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (0x31);
+INSERT INTO t1 VALUES (X'31');
+PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (?)';
+SET @a='1';
+SELECT charset(@a);
+charset(@a)
+cp932
+EXECUTE stmt USING @a;
+DROP PREPARE stmt;
+DROP TABLE t1;
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (0x31)
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (X'31')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	BEGIN
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 (a) VALUES (X'31')
+master-bin.000001	#	Query	#	#	COMMIT
+master-bin.000001	#	Query	#	#	use `test`; DROP TABLE `t1` /* generated by server */
+#
 # End of 5.5 tests
 #

=== modified file 'mysql-test/r/select.result'
--- mysql-test/r/select.result	2013-05-05 04:56:45 +0000
+++ mysql-test/r/select.result	2013-05-06 07:05:27 +0000
@@ -2921,8 +2921,8 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 DROP TABLE t1,t2;
-select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
-x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0
+select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0;
+x'3136' + 0	X'3136' + 0	b'10' + 0	B'10' + 0
 16	16	2	2
 create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
 create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

=== modified file 'mysql-test/r/select_jcl6.result'
--- mysql-test/r/select_jcl6.result	2013-05-05 04:56:45 +0000
+++ mysql-test/r/select_jcl6.result	2013-05-06 07:27:38 +0000
@@ -2932,8 +2932,8 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 1	SIMPLE	t1	hash_ALL	NULL	#hash#$hj	5	test.t2.a	5	Using where; Using join buffer (flat, BNLH join)
 DROP TABLE t1,t2;
-select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
-x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0
+select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0;
+x'3136' + 0	X'3136' + 0	b'10' + 0	B'10' + 0
 16	16	2	2
 create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
 create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

=== modified file 'mysql-test/r/select_pkeycache.result'
--- mysql-test/r/select_pkeycache.result	2013-05-05 04:56:45 +0000
+++ mysql-test/r/select_pkeycache.result	2013-05-06 07:27:04 +0000
@@ -2921,8 +2921,8 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 DROP TABLE t1,t2;
-select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
-x'10' + 0	X'10' + 0	b'10' + 0	B'10' + 0
+select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0;
+x'3136' + 0	X'3136' + 0	b'10' + 0	B'10' + 0
 16	16	2	2
 create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);
 create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

=== modified file 'mysql-test/r/user_var-binlog.result'
--- mysql-test/r/user_var-binlog.result	2013-02-28 17:42:49 +0000
+++ mysql-test/r/user_var-binlog.result	2013-05-06 15:09:41 +0000
@@ -9,12 +9,12 @@ insert into t1 values (@var1),(@var2);
 show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Query	#	#	BEGIN
-master-bin.000001	#	User var	#	#	@`a b`=_latin1 0x68656C6C6F COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	#	#	@`a b`=_latin1 X'68656C6C6F' COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(@`a b`)
 master-bin.000001	#	Query	#	#	COMMIT
 master-bin.000001	#	Query	#	#	BEGIN
-master-bin.000001	#	User var	#	#	@`var1`=_latin1 0x273B616161 COLLATE latin1_swedish_ci
-master-bin.000001	#	User var	#	#	@`var2`=_binary 0x61 COLLATE binary
+master-bin.000001	#	User var	#	#	@`var1`=_latin1 X'273B616161' COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	#	#	@`var2`=_binary X'61' COLLATE binary
 master-bin.000001	#	Query	#	#	use `test`; insert into t1 values (@var1),(@var2)
 master-bin.000001	#	Query	#	#	COMMIT
 flush logs;
@@ -34,7 +34,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-SET @`a b`:=_latin1 0x68656C6C6F COLLATE `latin1_swedish_ci`/*!*/;
+SET @`a b`:=_latin1 X'68656C6C6F' COLLATE `latin1_swedish_ci`/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 INSERT INTO t1 VALUES(@`a b`)
@@ -45,8 +45,8 @@ COMMIT
 SET TIMESTAMP=10000/*!*/;
 BEGIN
 /*!*/;
-SET @`var1`:=_latin1 0x273B616161 COLLATE `latin1_swedish_ci`/*!*/;
-SET @`var2`:=_binary 0x61 COLLATE `binary`/*!*/;
+SET @`var1`:=_latin1 X'273B616161' COLLATE `latin1_swedish_ci`/*!*/;
+SET @`var2`:=_binary X'61' COLLATE `binary`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 insert into t1 values (@var1),(@var2)
 /*!*/;

=== modified file 'mysql-test/r/varbinary.result'
--- mysql-test/r/varbinary.result	2011-12-12 22:58:40 +0000
+++ mysql-test/r/varbinary.result	2013-05-06 07:26:13 +0000
@@ -5,9 +5,62 @@ A	65	9223372036854775807	184467440737095
 select 0x31+1,concat(0x31)+1,-0xf;
 0x31+1	concat(0x31)+1	-0xf
 50	2	-15
-select x'31',X'ffff'+0;
-x'31'	X'ffff'+0
+select x'31',0xffff+0;
+x'31'	0xffff+0
 1	65535
+select X'FFFF'+0;
+X'FFFF'+0
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '\xFF\xFF'
+SELECT x'31'+0, 0x31+0;
+x'31'+0	0x31+0
+1	49
+SELECT x'31'+0.1e0, 0x31+0.1e0;
+x'31'+0.1e0	0x31+0.1e0
+1.1	49.1
+SELECT x'312E39'+0e0, 0x312E39+0e0;
+x'312E39'+0e0	0x312E39+0e0
+1.9	3223097
+SELECT CAST(x'31' AS SIGNED), CAST(0x31 AS SIGNED);
+CAST(x'31' AS SIGNED)	CAST(0x31 AS SIGNED)
+1	49
+SELECT CAST(x'31' AS DECIMAL(10,1)), CAST(0x31 AS DECIMAL(10,1));
+CAST(x'31' AS DECIMAL(10,1))	CAST(0x31 AS DECIMAL(10,1))
+1.0	49.0
+SELECT CAST(x'312E39' AS SIGNED), CAST(0x312E39 AS SIGNED);
+CAST(x'312E39' AS SIGNED)	CAST(0x312E39 AS SIGNED)
+1	3223097
+Warnings:
+Warning	1292	Truncated incorrect INTEGER value: '1.9'
+SELECT CAST(x'312E39' AS DECIMAL(10,1)), CAST(0x312E39 AS DECIMAL(10,1));
+CAST(x'312E39' AS DECIMAL(10,1))	CAST(0x312E39 AS DECIMAL(10,1))
+1.9	3223097.0
+EXPLAIN EXTENDED SELECT X'FFFF', 0xFFFF;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select X'ffff' AS `X'FFFF'`,0xffff AS `0xFFFF`
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (X'31'),(0x31);
+INSERT INTO t1 VALUES (X'312E39'),(0x312E39);
+SELECT * FROM t1;
+a
+1
+49
+2
+3223097
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(10,1));
+INSERT INTO t1 VALUES (X'31'),(0x31);
+INSERT INTO t1 VALUES (X'312E39'),(0x312E39);
+SELECT * FROM t1;
+a
+1.0
+49.0
+1.9
+3223097.0
+DROP TABLE t1;
 create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) );
 insert into t1 set UNIQ=0x38afba1d73e6a18a;
 insert into t1 set UNIQ=123;
@@ -104,8 +157,8 @@ select 0x41;
 select b'01000001';
 b'01000001'
 A
-select x'41', 0+x'41';
-x'41'	0+x'41'
+select x'41', 0+x'3635';
+x'41'	0+x'3635'
 A	65
 select N'abc', length(N'abc');
 abc	length(N'abc')

=== modified file 'mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result'
--- mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result	2013-02-28 17:42:49 +0000
+++ mysql-test/suite/binlog/r/binlog_stm_ctype_ucs.result	2013-05-06 15:01:15 +0000
@@ -6,7 +6,7 @@ insert into t2 values (@v);
 show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Query	#	#	BEGIN
-master-bin.000001	#	User var	#	#	@`v`=_ucs2 0x006100620063 COLLATE ucs2_general_ci
+master-bin.000001	#	User var	#	#	@`v`=_ucs2 X'006100620063' COLLATE ucs2_general_ci
 master-bin.000001	#	Query	#	#	use `test`; insert into t2 values (@v)
 master-bin.000001	#	Query	#	#	COMMIT
 flush logs;
@@ -26,7 +26,7 @@ SET @@session.lc_time_names=0/*!*/;
 SET @@session.collation_database=DEFAULT/*!*/;
 BEGIN
 /*!*/;
-SET @`v`:=_ucs2 0x006100620063 COLLATE `ucs2_general_ci`/*!*/;
+SET @`v`:=_ucs2 X'006100620063' COLLATE `ucs2_general_ci`/*!*/;
 use `test`/*!*/;
 SET TIMESTAMP=10000/*!*/;
 insert into t2 values (@v)

=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result	2012-08-24 13:29:01 +0000
+++ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result	2013-05-06 14:24:32 +0000
@@ -946,10 +946,10 @@ show binlog events from <binlog_start>;
 Log_name	Pos	Event_type	Server_id	End_log_pos	Info
 master-bin.000001	#	Query	#	#	BEGIN
 master-bin.000001	#	Intvar	#	#	INSERT_ID=10
-master-bin.000001	#	User var	#	#	@`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	#	#	@`b`=_latin1 X'3135' COLLATE latin1_swedish_ci
 master-bin.000001	#	Begin_load_query	#	#	;file_id=#;block_len=#
 master-bin.000001	#	Intvar	#	#	INSERT_ID=10
-master-bin.000001	#	User var	#	#	@`b`=_latin1 0x3135 COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	#	#	@`b`=_latin1 X'3135' COLLATE latin1_swedish_ci
 master-bin.000001	#	Execute_load_query	#	#	use `test`; LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE `t4` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`, @`b`) SET `b`= @b + bug27417(2) ;file_id=#
 master-bin.000001	#	Query	#	#	ROLLBACK
 drop trigger trg_del_t2;

=== modified file 'mysql-test/suite/rpl/r/rpl_charset_sjis.result'
--- mysql-test/suite/rpl/r/rpl_charset_sjis.result	2010-12-19 17:07:28 +0000
+++ mysql-test/suite/rpl/r/rpl_charset_sjis.result	2013-05-06 14:43:03 +0000
@@ -20,4 +20,36 @@ hex(a)
 965C
 drop table t1;
 drop procedure p1;
+#
+# Start of 5.5 tests
+#
+#
+# Bug#MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
+#
+--- on master ---
+SET NAMES sjis;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0x31),(X'31'),('1');
+PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (?)';
+SET @a='1';
+EXECUTE stmt USING @a;
+DROP PREPARE stmt;
+SELECT * FROM t1;
+a
+49
+1
+1
+1
+--- on slave ---
+SELECT * FROM t1;
+a
+49
+1
+1
+1
+--- on master ---
+DROP TABLE t1;
+#
+# End of 5.5 tests
+#
 include/rpl_end.inc

=== modified file 'mysql-test/suite/rpl/r/rpl_mdev382.result'
--- mysql-test/suite/rpl/r/rpl_mdev382.result	2013-02-28 17:42:49 +0000
+++ mysql-test/suite/rpl/r/rpl_mdev382.result	2013-05-06 15:11:13 +0000
@@ -245,7 +245,7 @@ master-bin.000001	#	User var	#	#	@`a``3`
 master-bin.000001	#	User var	#	#	@`a``4`=18446744073709551615
 master-bin.000001	#	User var	#	#	@`b```=-1.234560123456789e125
 master-bin.000001	#	User var	#	#	@```c`=-1234501234567890123456789012345678901234567890123456789.0123456789
-master-bin.000001	#	User var	#	#	@```d```=_latin1 0x78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878 COLLATE latin1_swedish_ci
+master-bin.000001	#	User var	#	#	@```d```=_latin1 X'78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878' COLLATE latin1_swedish_ci
 master-bin.000001	#	Query	#	#	use `db1``; select 'oops!'`; INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98))
 master-bin.000001	#	Query	#	#	COMMIT
 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
@@ -283,7 +283,7 @@ SET @`a``3`:=9223372036854775807/*!*/;
 SET @`a``4`:=18446744073709551615/*!*/;
 SET @`b```:=-1.2345601234568e+125/*!*/;
 SET @```c`:=-1234501234567890123456789012345678901234567890123456789.0123456789/*!*/;
-SET @```d```:=_latin1 0x78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878 COLLATE `latin1_swedish_ci`/*!*/;
+SET @```d```:=_latin1 X'78787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878787878' COLLATE `latin1_swedish_ci`/*!*/;
 SET TIMESTAMP=1000000000/*!*/;
 INSERT INTO t1 VALUES (@`a``1`+1, @`a``2`*100, @`a``3`-1, @`a``4`-1, @`b```/2, @```c`, substr(@```d```, 2, 98))
 /*!*/;

=== modified file 'mysql-test/suite/rpl/t/rpl_charset_sjis.test'
--- mysql-test/suite/rpl/t/rpl_charset_sjis.test	2010-12-19 17:07:28 +0000
+++ mysql-test/suite/rpl/t/rpl_charset_sjis.test	2013-05-06 14:42:39 +0000
@@ -23,4 +23,37 @@ sync_slave_with_master;
 connection master;
 
 # End of 5.0 tests
+
+--echo #
+--echo # Start of 5.5 tests
+--echo #
+
+--echo #
+--echo # Bug#MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
+--echo #
+
+connection master;
+--echo --- on master ---
+SET NAMES sjis;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0x31),(X'31'),('1');
+PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (?)';
+SET @a='1';
+EXECUTE stmt USING @a;
+DROP PREPARE stmt;
+SELECT * FROM t1;
+sync_slave_with_master;
+connection slave;
+--echo --- on slave ---
+SELECT * FROM t1;
+connection master;
+--echo --- on master ---
+DROP TABLE t1;
+sync_slave_with_master;
+connection master;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
+
 --source include/rpl_end.inc

=== modified file 'mysql-test/t/ctype_cp932_binlog_stm.test'
--- mysql-test/t/ctype_cp932_binlog_stm.test	2011-10-19 19:45:18 +0000
+++ mysql-test/t/ctype_cp932_binlog_stm.test	2013-05-06 15:12:08 +0000
@@ -141,6 +141,23 @@ SELECT HEX(a), HEX(CONVERT(a using cp932
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug#MDEV-4489 Replication of big5, cp932, gbk, sjis strings makes wrong values on slave
+--echo #
+let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+SET NAMES cp932;
+CREATE TABLE t1 (a INT);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (0x31);
+INSERT INTO t1 VALUES (X'31');
+PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (?)';
+SET @a='1';
+SELECT charset(@a);
+EXECUTE stmt USING @a; 
+DROP PREPARE stmt;
+DROP TABLE t1;
+--source include/show_binlog_events.inc
+
 
 --echo #
 --echo # End of 5.5 tests

=== modified file 'mysql-test/t/select.test'
--- mysql-test/t/select.test	2013-05-05 04:56:45 +0000
+++ mysql-test/t/select.test	2013-05-06 07:05:02 +0000
@@ -2493,7 +2493,7 @@ DROP TABLE t1,t2;
 # Bug #10650
 #
 
-select x'10' + 0, X'10' + 0, b'10' + 0, B'10' + 0;
+select x'3136' + 0, X'3136' + 0, b'10' + 0, B'10' + 0;
 
 #
 # Bug #11398 Bug in field_conv() results in wrong result of join with index

=== modified file 'mysql-test/t/varbinary.test'
--- mysql-test/t/varbinary.test	2009-04-28 18:42:17 +0000
+++ mysql-test/t/varbinary.test	2013-05-06 07:25:58 +0000
@@ -13,7 +13,30 @@ drop table if exists t1;
 
 select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ;
 select 0x31+1,concat(0x31)+1,-0xf;
-select x'31',X'ffff'+0;
+select x'31',0xffff+0;
+select X'FFFF'+0;
+
+#
+# Hex string vs hex hybrid
+#
+SELECT x'31'+0, 0x31+0;
+SELECT x'31'+0.1e0, 0x31+0.1e0;
+SELECT x'312E39'+0e0, 0x312E39+0e0;
+SELECT CAST(x'31' AS SIGNED), CAST(0x31 AS SIGNED);
+SELECT CAST(x'31' AS DECIMAL(10,1)), CAST(0x31 AS DECIMAL(10,1));
+SELECT CAST(x'312E39' AS SIGNED), CAST(0x312E39 AS SIGNED);
+SELECT CAST(x'312E39' AS DECIMAL(10,1)), CAST(0x312E39 AS DECIMAL(10,1));
+EXPLAIN EXTENDED SELECT X'FFFF', 0xFFFF;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (X'31'),(0x31);
+INSERT INTO t1 VALUES (X'312E39'),(0x312E39);
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a DECIMAL(10,1));
+INSERT INTO t1 VALUES (X'31'),(0x31);
+INSERT INTO t1 VALUES (X'312E39'),(0x312E39);
+SELECT * FROM t1;
+DROP TABLE t1;
 
 #
 # Test of hex constants in WHERE:
@@ -115,7 +138,7 @@ select 0x41;
 
 select b'01000001';
 
-select x'41', 0+x'41';
+select x'41', 0+x'3635';
 
 select N'abc', length(N'abc');
 

=== modified file 'sql/item.cc'
--- sql/item.cc	2013-04-17 17:42:34 +0000
+++ sql/item.cc	2013-05-06 06:46:29 +0000
@@ -6125,17 +6125,8 @@ inline uint char_val(char X)
 		 X-'a'+10);
 }
 
-Item_hex_string::Item_hex_string()
-{
-  hex_string_init("", 0);
-}
-
-Item_hex_string::Item_hex_string(const char *str, uint str_length)
-{
-  hex_string_init(str, str_length);
-}
 
-void Item_hex_string::hex_string_init(const char *str, uint str_length)
+void Item_hex_constant::hex_string_init(const char *str, uint str_length)
 {
   max_length=(str_length+1)/2;
   char *ptr=(char*) sql_alloc(max_length+1);
@@ -6159,7 +6150,7 @@ void Item_hex_string::hex_string_init(co
   unsigned_flag= 1;
 }
 
-longlong Item_hex_string::val_int()
+longlong Item_hex_hybrid::val_int()
 {
   // following assert is redundant, because fixed=1 assigned in constructor
   DBUG_ASSERT(fixed == 1);
@@ -6173,17 +6164,7 @@ longlong Item_hex_string::val_int()
 }
 
 
-my_decimal *Item_hex_string::val_decimal(my_decimal *decimal_value)
-{
-  // following assert is redundant, because fixed=1 assigned in constructor
-  DBUG_ASSERT(fixed == 1);
-  ulonglong value= (ulonglong)val_int();
-  int2my_decimal(E_DEC_FATAL_ERROR, value, TRUE, decimal_value);
-  return (decimal_value);
-}
-
-
-int Item_hex_string::save_in_field(Field *field, bool no_conversions)
+int Item_hex_hybrid::save_in_field(Field *field, bool no_conversions)
 {
   field->set_notnull();
   if (field->result_type() == STRING_RESULT)
@@ -6216,22 +6197,27 @@ int Item_hex_string::save_in_field(Field
 }
 
 
-void Item_hex_string::print(String *str, enum_query_type query_type)
+void Item_hex_hybrid::print(String *str, enum_query_type query_type)
 {
-  char *end= (char*) str_value.ptr() + str_value.length(),
-       *ptr= end - min(str_value.length(), sizeof(longlong));
+  uint32 len= min(str_value.length(), sizeof(longlong));
+  const char *ptr= str_value.ptr() + str_value.length() - len;
   str->append("0x");
-  for (; ptr != end ; ptr++)
-  {
-    str->append(_dig_vec_lower[((uchar) *ptr) >> 4]);
-    str->append(_dig_vec_lower[((uchar) *ptr) & 0x0F]);
-  }
+  str->append_hex(ptr, len);
+}
+
+
+void Item_hex_string::print(String *str, enum_query_type query_type)
+{
+  str->append("X'");
+  str->append_hex(str_value.ptr(), str_value.length());
+  str->append("'");
 }
 
 
-bool Item_hex_string::eq(const Item *arg, bool binary_cmp) const
+bool Item_hex_constant::eq(const Item *arg, bool binary_cmp) const
 {
-  if (arg->basic_const_item() && arg->type() == type())
+  if (arg->basic_const_item() && arg->type() == type() &&
+      arg->cast_to_int_type() == cast_to_int_type())
   {
     if (binary_cmp)
       return !stringcmp(&str_value, &arg->str_value);
@@ -6241,7 +6227,7 @@ bool Item_hex_string::eq(const Item *arg
 }
 
 
-Item *Item_hex_string::safe_charset_converter(CHARSET_INFO *tocs)
+Item *Item_hex_constant::safe_charset_converter(CHARSET_INFO *tocs)
 {
   Item_string *conv;
   String tmp, *str= val_str(&tmp);

=== modified file 'sql/item.h'
--- sql/item.h	2013-03-13 21:33:52 +0000
+++ sql/item.h	2013-05-06 14:58:17 +0000
@@ -2724,36 +2724,112 @@ class Item_return_int :public Item_int
 };
 
 
-class Item_hex_string: public Item_basic_constant
+/**
+  Item_hex_constant -- a common class for hex literals: X'HHHH' and 0xHHHH
+*/
+class Item_hex_constant: public Item_basic_constant
 {
+private:
+  void hex_string_init(const char *str, uint str_length);
 public:
-  Item_hex_string();
-  Item_hex_string(const char *str,uint str_length);
+  Item_hex_constant()
+  {
+    hex_string_init("", 0);
+  }
+  Item_hex_constant(const char *str, uint str_length)
+  {
+    hex_string_init(str, str_length);
+  }
   enum Type type() const { return VARBIN_ITEM; }
+  enum Item_result result_type () const { return STRING_RESULT; }
+  enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
+  virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
+  bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  bool check_vcol_func_processor(uchar *arg) { return FALSE;}
+  bool basic_const_item() const { return 1; }
+  bool eq(const Item *item, bool binary_cmp) const;
+  String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; }
+};
+
+
+/**
+  Item_hex_hybrid -- is a class implementing 0xHHHH literals, e.g.:
+    SELECT 0x3132;
+  They can behave as numbers and as strings depending on context.
+*/
+class Item_hex_hybrid: public Item_hex_constant
+{
+public:
+  Item_hex_hybrid(): Item_hex_constant() {}
+  Item_hex_hybrid(const char *str, uint str_length):
+    Item_hex_constant(str, str_length) {}
   double val_real()
   { 
     DBUG_ASSERT(fixed == 1); 
-    return (double) (ulonglong) Item_hex_string::val_int();
+    return (double) (ulonglong) val_int();
   }
   longlong val_int();
-  bool basic_const_item() const { return 1; }
-  String *val_str(String*) { DBUG_ASSERT(fixed == 1); return &str_value; }
-  my_decimal *val_decimal(my_decimal *);
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    // following assert is redundant, because fixed=1 assigned in constructor
+    DBUG_ASSERT(fixed == 1);
+    ulonglong value= (ulonglong) val_int();
+    int2my_decimal(E_DEC_FATAL_ERROR, value, TRUE, decimal_value);
+    return decimal_value;
+  }
   int save_in_field(Field *field, bool no_conversions);
-  enum Item_result result_type () const { return STRING_RESULT; }
   enum Item_result cast_to_int_type() const { return INT_RESULT; }
-  enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; }
-  virtual void print(String *str, enum_query_type query_type);
-  bool eq(const Item *item, bool binary_cmp) const;
-  virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
-  bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
-  bool check_vcol_func_processor(uchar *arg) { return FALSE;}
-private:
-  void hex_string_init(const char *str, uint str_length);
+  void print(String *str, enum_query_type query_type);
+};
+
+
+/**
+  Item_hex_string -- is a class implementing X'HHHH' literals, e.g.:
+    SELECT X'3132';
+  Unlike Item_hex_hybrid, X'HHHH' literals behave as strings in all contexts.
+  X'HHHH' are also used in replication of string constants in case of
+  "dangerous" charsets (sjis, cp932, big5, gbk) who can have backslash (0x5C)
+  as the second byte of a multi-byte character, so using '\' escaping for
+  these charsets is not desirable.
+*/
+class Item_hex_string: public Item_hex_constant
+{
+public:
+  Item_hex_string(): Item_hex_constant() {}
+  Item_hex_string(const char *str, uint str_length):
+    Item_hex_constant(str, str_length) {}
+  longlong val_int()
+  {
+    DBUG_ASSERT(fixed == 1);
+    return longlong_from_string_with_check(str_value.charset(),
+                                           str_value.ptr(),
+                                           str_value.ptr()+
+                                           str_value.length());
+  }
+  double val_real()
+  { 
+    DBUG_ASSERT(fixed == 1);
+    return double_from_string_with_check(str_value.charset(),
+                                         str_value.ptr(), 
+                                         str_value.ptr() +
+                                         str_value.length());
+  }
+  my_decimal *val_decimal(my_decimal *decimal_value)
+  {
+    return val_decimal_from_string(decimal_value);
+  }
+  int save_in_field(Field *field, bool no_conversions)
+  {
+    field->set_notnull();
+    return field->store(str_value.ptr(), str_value.length(), 
+                        collation.collation);
+  }
+  enum Item_result cast_to_int_type() const { return STRING_RESULT; }
+  void print(String *str, enum_query_type query_type);
 };
 
 
-class Item_bin_string: public Item_hex_string
+class Item_bin_string: public Item_hex_hybrid
 {
 public:
   Item_bin_string(const char *str,uint str_length);

=== modified file 'sql/log_event.cc'
--- sql/log_event.cc	2013-03-28 19:04:14 +0000
+++ sql/log_event.cc	2013-05-06 15:08:52 +0000
@@ -622,16 +622,18 @@ static inline int read_str(const char **
 
 
 /**
-  Transforms a string into "" or its expression in 0x... form.
+  Transforms a string into "" or its expression in X'HHHH' form.
 */
 
 char *str_to_hex(char *to, const char *from, uint len)
 {
   if (len)
   {
-    *to++= '0';
-    *to++= 'x';
+    *to++= 'X';
+    *to++= '\'';
     to= octet2hex(to, from, len);
+    *to++= '\'';
+    *to= '\0';
   }
   else
     to= strmov(to, "\"\"");
@@ -652,7 +654,7 @@ append_query_string(THD *thd, CHARSET_IN
 {
   char *beg, *ptr;
   uint32 const orig_len= to->length();
-  if (to->reserve(orig_len + from->length()*2+3))
+  if (to->reserve(orig_len + from->length() * 2 + 4))
     return 1;
 
   beg= (char*) to->ptr() + to->length();
@@ -6289,7 +6291,7 @@ void User_var_log_event::pack_info(THD *
             buf.append(" "))
           return;
         old_len= buf.length();
-        if (buf.reserve(old_len + val_len*2 + 2 + sizeof(" COLLATE ") +
+        if (buf.reserve(old_len + val_len * 2 + 3 + sizeof(" COLLATE ") +
                         MY_CS_NAME_SIZE))
           return;
         beg= const_cast<char *>(buf.ptr()) + old_len;
@@ -6558,7 +6560,8 @@ void User_var_log_event::print(FILE* fil
       char *hex_str;
       CHARSET_INFO *cs;
 
-      hex_str= (char *)my_malloc(2*val_len+1+2,MYF(MY_WME)); // 2 hex digits / byte
+      // 2 hex digits / byte
+      hex_str= (char *) my_malloc(2 * val_len + 1 + 3, MYF(MY_WME));
       if (!hex_str)
         return;
       str_to_hex(hex_str, val, val_len);

=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc	2013-02-28 21:47:29 +0000
+++ sql/sql_lex.cc	2013-05-06 05:25:17 +0000
@@ -1411,7 +1411,7 @@ int lex_one_token(void *arg, void *yythd
       yylval->lex_str=get_token(lip,
                                 2,          // skip x'
                                 length-3);  // don't count x' and last '
-      return (HEX_NUM);
+      return HEX_STRING;
 
     case MY_LEX_BIN_NUMBER:           // Found b'bin-string'
       lip->yySkip();                  // Accept opening '

=== modified file 'sql/sql_string.h'
--- sql/sql_string.h	2012-11-22 09:19:31 +0000
+++ sql/sql_string.h	2013-05-06 14:20:02 +0000
@@ -364,6 +364,16 @@ class String
     }
     return 0;
   }
+  bool append_hex(const char *src, uint32 srclen)
+  {
+    for (const char *end= src + srclen ; src != end ; src++)
+    {
+      if (append(_dig_vec_lower[((uchar) *src) >> 4]) ||
+          append(_dig_vec_lower[((uchar) *src) & 0x0F]))
+        return true;
+    }
+    return false;
+  }
   bool fill(uint32 max_length,char fill);
   void strip_sp();
   friend int sortcmp(const String *a,const String *b, CHARSET_INFO *cs);

=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy	2013-04-18 20:17:29 +0000
+++ sql/sql_yacc.yy	2013-05-06 06:14:55 +0000
@@ -1021,6 +1021,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %token  HAVING                        /* SQL-2003-R */
 %token  HELP_SYM
 %token  HEX_NUM
+%token  HEX_STRING
 %token  HIGH_PRIORITY
 %token  HOST_SYM
 %token  HOSTS_SYM
@@ -1448,7 +1449,8 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %left  INTERVAL_SYM
 
 %type <lex_str>
-        IDENT IDENT_QUOTED TEXT_STRING DECIMAL_NUM FLOAT_NUM NUM LONG_NUM HEX_NUM
+        IDENT IDENT_QUOTED TEXT_STRING DECIMAL_NUM FLOAT_NUM NUM LONG_NUM
+        HEX_NUM HEX_STRING hex_num_or_string
         LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident ident_or_text
         IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
         NCHAR_STRING opt_component key_cache_name
@@ -5977,6 +5979,11 @@ now_or_signed_literal:
           { $$=$1; }
         ;
 
+hex_num_or_string:
+          HEX_NUM {}
+        | HEX_STRING {}
+        ;
+
 charset:
           CHAR_SYM SET {}
         | CHARSET {}
@@ -12242,7 +12249,7 @@ text_string:
           }
         | HEX_NUM
           {
-            Item *tmp= new (YYTHD->mem_root) Item_hex_string($1.str, $1.length);
+            Item *tmp= new (YYTHD->mem_root) Item_hex_hybrid($1.str, $1.length);
             if (tmp == NULL)
               MYSQL_YYABORT;
             /*
@@ -12252,6 +12259,14 @@ text_string:
             tmp->quick_fix_field();
             $$= tmp->val_str((String*) 0);
           }
+        | HEX_STRING
+          {
+            Item *tmp= new (YYTHD->mem_root) Item_hex_string($1.str, $1.length);
+            if (tmp == NULL)
+              MYSQL_YYABORT;
+            tmp->quick_fix_field();
+            $$= tmp->val_str((String*) 0);
+          }
         | BIN_NUM
           {
             Item *tmp= new (YYTHD->mem_root) Item_bin_string($1.str, $1.length);
@@ -12321,6 +12336,12 @@ literal:
           }
         | HEX_NUM
           {
+            $$ = new (YYTHD->mem_root) Item_hex_hybrid($1.str, $1.length);
+            if ($$ == NULL)
+              MYSQL_YYABORT;
+          }
+        | HEX_STRING
+          {
             $$ = new (YYTHD->mem_root) Item_hex_string($1.str, $1.length);
             if ($$ == NULL)
               MYSQL_YYABORT;
@@ -12331,7 +12352,7 @@ literal:
             if ($$ == NULL)
               MYSQL_YYABORT;
           }
-        | UNDERSCORE_CHARSET HEX_NUM
+        | UNDERSCORE_CHARSET hex_num_or_string
           {
             Item *tmp= new (YYTHD->mem_root) Item_hex_string($2.str, $2.length);
             if (tmp == NULL)


Follow ups

References