maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #05494
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
