bzr commit into mysql-5.1-bugteam branch (ramil:2687) Bug#39182

[Ramil Kalimullin]

#At file:///home/ram/mysql/mysql-5.0-5.1.29-rc/

 2687 Ramil Kalimullin	2008-10-01
      Fix for bug#39182: Binary log producing incompatible character set query
      from stored procedure. 
      
      Problem: we replace all references to local variables in stored procedures     
      with NAME_CONST(name, value) logging to the binary log. However, if the
      value's collation differs we might get an 'illegal mix of collation'           
      error as we don't pass the collation to the function.
      
      Fix: pass the value's collation to NAME_CONST().
      
      Note: actually we should pass to NAME_CONST() the value's derivation as well.
      It's impossible without the parser modifying. Now we always set the 
      derivation to DERIVATION_IMPLICIT, the same as local variables have.
modified:
  mysql-test/r/binlog.result
  mysql-test/r/ctype_cp932_binlog.result
  mysql-test/r/rpl_sp.result
  mysql-test/t/binlog.test
  sql/item.cc
  sql/sp_head.cc

per-file messages:
  mysql-test/r/binlog.result
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure.
      - test result.
  mysql-test/r/ctype_cp932_binlog.result
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure.
      - results adjusted.
  mysql-test/r/rpl_sp.result
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure.
      - results adjusted.
  mysql-test/t/binlog.test
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure.
      - test case.
  sql/item.cc
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure.
      - allow NAME_CONST() to get _charset'foo' COLLATE 'bar' strings
    (see Item_func_set_collation).
  sql/sp_head.cc
    Fix for bug#39182: Binary log producing incompatible character set query
    from stored procedure. 
      - pass the value's collation to NAME_CONST().
=== modified file 'mysql-test/r/binlog.result'
--- a/mysql-test/r/binlog.result	2008-02-08 10:55:55 +0000
+++ b/mysql-test/r/binlog.result	2008-10-01 09:48:47 +0000
@@ -582,4 +582,35 @@ master-bin.000001	4	Format_desc	1	98	Ser
 master-bin.000001	98	Query	1	219	use `test`; create table t1 (a bigint unsigned, b
bigint(20) unsigned)
 master-bin.000001	219	Query	1	343	use `test`; insert into t1 values
(9999999999999999,14632475938453979136)
 master-bin.000001	343	Query	1	419	use `test`; drop table t1
+CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+USE bug39182;
+CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE s1 VARCHAR(255);
+SET s1= "test";
+CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+SELECT 
+COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+COLLATION(s1) c3,
+COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+COERCIBILITY(s1) d3;
+DROP TEMPORARY TABLE tmp1;
+END//
+CALL p1();
+c1	c2	c3	d1	d2	d3
+utf8_general_ci	utf8_unicode_ci	utf8_unicode_ci	2	2	2
+SHOW BINLOG EVENTS FROM 1285;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	1285	Query	1	1483	use `bug39182`; CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%",  NAME_CONST('s1',_utf8'test' COLLATE
'utf8_unicode_ci'), "%")
+master-bin.000001	1483	Query	1	1575	use `bug39182`; DROP TEMPORARY TABLE tmp1
+DROP PROCEDURE p1;
+DROP TABLE t1;
+DROP DATABASE bug39182;
+USE test;
 End of 5.0 tests

=== modified file 'mysql-test/r/ctype_cp932_binlog.result'
--- a/mysql-test/r/ctype_cp932_binlog.result	2008-05-15 23:13:24 +0000
+++ b/mysql-test/r/ctype_cp932_binlog.result	2008-10-01 09:48:47 +0000
@@ -40,6 +40,6 @@ IN ind DECIMAL(10,2))
 BEGIN
 INSERT INTO t4 VALUES (ins1, ins2, ind);
 END
-master-bin.000001	777	Query	1	988	use `test`; INSERT INTO t4 VALUES (
NAME_CONST('ins1',_latin1 0x466F6F2773206120426172),  NAME_CONST('ins2',_cp932
0xED40ED41ED42),  NAME_CONST('ind',47.93))
-master-bin.000001	988	Query	1	1077	use `test`; DROP PROCEDURE bug18293
-master-bin.000001	1077	Query	1	1156	use `test`; DROP TABLE t4
+master-bin.000001	777	Query	1	1044	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	1044	Query	1	1133	use `test`; DROP PROCEDURE bug18293
+master-bin.000001	1133	Query	1	1212	use `test`; DROP TABLE t4

=== modified file 'mysql-test/r/rpl_sp.result'
--- a/mysql-test/r/rpl_sp.result	2008-05-16 15:26:29 +0000
+++ b/mysql-test/r/rpl_sp.result	2008-10-01 09:48:47 +0000
@@ -502,7 +502,7 @@ master-bin.000001	#	Query	1	#	use `test`
 master-bin.000001	#	Query	1	#	use `test`; CREATE TABLE t1(col VARCHAR(10))
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE
`p1`(arg VARCHAR(10))
 INSERT INTO t1 VALUES(arg)
-master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES(
NAME_CONST('arg',_latin1'test'))
+master-bin.000001	#	Query	1	#	use `test`; INSERT INTO t1 VALUES(
NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
 master-bin.000001	#	Query	1	#	use `test`; DROP PROCEDURE p1
 master-bin.000001	#	Query	1	#	use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE
`p1`()
 SET @a = 1
@@ -841,7 +841,7 @@ CREATE DEFINER=`root`@`localhost` PROCED
 INSERT INTO t1 VALUES(arg)
 /*!*/;
 SET TIMESTAMP=t/*!*/;
-INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
+INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
 /*!*/;
 SET TIMESTAMP=t/*!*/;
 DROP PROCEDURE p1

=== modified file 'mysql-test/t/binlog.test'
--- a/mysql-test/t/binlog.test	2008-02-08 10:55:55 +0000
+++ b/mysql-test/t/binlog.test	2008-10-01 09:48:47 +0000
@@ -123,4 +123,42 @@ drop table t1;
 --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server
ver: [^,]*,/Server version,/
 show binlog events from 0;
 
+
+#
+# Bug #39182: Binary log producing incompatible character set query from 
+# stored procedure.
+#
+CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+USE bug39182;
+CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DELIMITER //;
+
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE s1 VARCHAR(255);
+  SET s1= "test";
+  CREATE TEMPORARY TABLE tmp1
+    SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+  SELECT 
+    COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+    COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+    COLLATION(s1) c3,
+    COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+    COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+    COERCIBILITY(s1) d3;
+  DROP TEMPORARY TABLE tmp1;
+END//
+
+DELIMITER ;//
+
+CALL p1();
+SHOW BINLOG EVENTS FROM 1285;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+DROP DATABASE bug39182;
+USE test;
+
 --echo End of 5.0 tests

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2008-09-18 12:55:36 +0000
+++ b/sql/item.cc	2008-10-01 09:48:47 +0000
@@ -1217,10 +1217,12 @@ Item_name_const::Item_name_const(Item *n
   if (!(valid_args= name_item->basic_const_item() &&
                     (value_item->basic_const_item() ||
                      ((value_item->type() == FUNC_ITEM) &&
-                      (((Item_func *) value_item)->functype() ==
-                                                 Item_func::NEG_FUNC) &&
+                      ((((Item_func *) value_item)->functype() ==
+                         Item_func::COLLATE_FUNC) ||
+                      ((((Item_func *) value_item)->functype() ==
+                         Item_func::NEG_FUNC) &&
                       (((Item_func *) value_item)->key_item()->type() !=
-                       FUNC_ITEM)))))
+                         FUNC_ITEM)))))))
     my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST");
   Item::maybe_null= TRUE;
 }

=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc	2008-08-11 16:10:00 +0000
+++ b/sql/sp_head.cc	2008-10-01 09:48:47 +0000
@@ -123,6 +123,9 @@ sp_get_item_value(THD *thd, Item *item, 
         if (cs->escape_with_backslash_is_dangerous)
           buf.append(' ');
         append_query_string(cs, result, &buf);
+        buf.append(" COLLATE '");
+        buf.append(item->collation.collation->name);
+        buf.append('\'');
         str->copy(buf);
 
         return str;