From: Date: November 13 2006 8:42pm
Subject: bk commit into 5.0 tree (kroki:1.2284) BUG#17047
List-Archive: http://lists.mysql.com/commits/15269
X-Bug: 17047
Message-Id: <200611131942.kADJg9uT019751@moonlight.intranet>

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-11-13 22:42:00+03:00, kroki@stripped +7 -0
  BUG#17047: select returning NULL fails when executed via cursor
  
  The bug has nothing to do with cursors (but requires them for one of the
  test cases).  The problem was that some functions (namely IN() and
  CHAR()) were returning NULL in certain conditions, while they didn't set
  their maybe_null flag.
  
  The fix is to set maybe_null correctly.

  mysql-test/r/func_in.result@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +8 -0
    Add result for bug#17047: select returning NULL fails when executed
    via cursor.

  mysql-test/r/func_str.result@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +20 -0
    Add result for bug#17047: select returning NULL fails when executed
    via cursor.

  mysql-test/t/func_in.test@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +21 -0
    Add test case for bug#17047: select returning NULL fails when executed
    via cursor.

  mysql-test/t/func_str.test@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +33 -0
    Add test case for bug#17047: select returning NULL fails when executed
    via cursor.

  sql/item_cmpfunc.cc@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +0 -1
    Remove assignment to maybe_null, as it was already set in fix_fields()
    based on all arguments, not only on the first.

  sql/item_strfunc.cc@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +14 -0
    Add Item_str_func::fix_fields() implementation, and set maybe_null to
    TRUE if we are in the SQL mode that requires some functions to return
    null even if they normally do not.

  sql/item_strfunc.h@stripped, 2006-11-13 22:41:56+03:00, kroki@stripped +3 -3
    Add declaration of Item_str_func::fix_fields().
    Do not reset maybe_null in Item_func_char::fix_length_and_dec().

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug17047

--- 1.223/sql/item_cmpfunc.cc	2006-11-13 22:42:09 +03:00
+++ 1.224/sql/item_cmpfunc.cc	2006-11-13 22:42:09 +03:00
@@ -2465,7 +2465,6 @@ void Item_func_in::fix_length_and_dec()
     if (cmp_type  == STRING_RESULT)
       in_item->cmp_charset= cmp_collation.collation;
   }
-  maybe_null= args[0]->maybe_null;
   max_length= 1;
 }
 

--- 1.286/sql/item_strfunc.cc	2006-11-13 22:42:09 +03:00
+++ 1.287/sql/item_strfunc.cc	2006-11-13 22:42:09 +03:00
@@ -80,6 +80,20 @@ String *Item_str_func::check_well_formed
 }
 
 
+bool Item_str_func::fix_fields(THD *thd, Item **ref)
+{
+  bool res= Item_func::fix_fields(thd, ref);
+  /*
+    In Item_str_func::check_well_formed_result() we may set null_value
+    flag on the same condition as in test() below.
+  */
+  maybe_null= (maybe_null ||
+               test(thd->variables.sql_mode &
+                    (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)));
+  return res;
+}
+
+
 my_decimal *Item_str_func::val_decimal(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);

--- 1.117/sql/item_strfunc.h	2006-11-13 22:42:09 +03:00
+++ 1.118/sql/item_strfunc.h	2006-11-13 22:42:09 +03:00
@@ -37,6 +37,7 @@ public:
   enum Item_result result_type () const { return STRING_RESULT; }
   void left_right_max_length();
   String *check_well_formed_result(String *str);
+  bool fix_fields(THD *thd, Item **ref);
 };
 
 class Item_func_md5 :public Item_str_func
@@ -525,9 +526,8 @@ public:
   { collation.set(cs); }  
   String *val_str(String *);
   void fix_length_and_dec() 
-  { 
-    maybe_null=0;
-    max_length=arg_count * collation.collation->mbmaxlen;
+  {
+    max_length= arg_count * collation.collation->mbmaxlen;
   }
   const char *func_name() const { return "char"; }
 };

--- 1.28/mysql-test/r/func_in.result	2006-11-13 22:42:09 +03:00
+++ 1.29/mysql-test/r/func_in.result	2006-11-13 22:42:09 +03:00
@@ -343,3 +343,11 @@ some_id
 1
 2
 drop table t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 SELECT 1 IN (2, NULL);
+SELECT should return NULL.
+SELECT * FROM t1;
+1 IN (2, NULL)
+NULL
+DROP TABLE t1;
+End of 5.0 tests.

--- 1.22/mysql-test/t/func_in.test	2006-11-13 22:42:09 +03:00
+++ 1.23/mysql-test/t/func_in.test	2006-11-13 22:42:09 +03:00
@@ -232,3 +232,24 @@ select some_id from t1 where some_id not
 select some_id from t1 where some_id not in(-4,-1,-4);
 select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 drop table t1;
+
+
+#
+# BUG#17047: select returning NULL fails when executed via cursor
+#
+# The problem was in the IN() function that ignored maybe_null flags
+# of all arguments except the first (the one _before_ the IN
+# keyword, '1' in the test case below).
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 SELECT 1 IN (2, NULL);
+--echo SELECT should return NULL.
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+
+--echo End of 5.0 tests.

--- 1.121/mysql-test/r/func_str.result	2006-11-13 22:42:09 +03:00
+++ 1.122/mysql-test/r/func_str.result	2006-11-13 22:42:09 +03:00
@@ -1148,4 +1148,24 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1003	select `test`.`t1`.`code` AS `code`,`test`.`t2`.`id` AS `id` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`code` = _latin1'a12') and (length(`test`.`t1`.`code`) = 5))
 DROP TABLE t1,t2;
+DROP PROCEDURE IF EXISTS p1;
+SET @orig_sql_mode = @@SQL_MODE;
+SET SQL_MODE=traditional;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE v CHAR(10);
+DECLARE c CURSOR FOR SELECT CHAR(0xff,0x8f USING utf8);
+OPEN c;
+FETCH c INTO v;
+CLOSE c;
+SELECT v;
+END |
+SET SQL_MODE=@orig_sql_mode;
+CALL should return NULL.
+CALL p1();
+v
+NULL
+Warnings:
+Error	1300	Invalid utf8 character string: 'FF8F'
+DROP PROCEDURE p1;
 End of 5.0 tests

--- 1.94/mysql-test/t/func_str.test	2006-11-13 22:42:09 +03:00
+++ 1.95/mysql-test/t/func_str.test	2006-11-13 22:42:09 +03:00
@@ -780,4 +780,37 @@ SELECT * FROM t1 INNER JOIN t2 ON code=i
 
 DROP TABLE t1,t2;
 
+
+#
+# BUG#17047: select returning NULL fails when executed via cursor
+#
+# The problem itself was in the CHAR() function (it never set
+# maybe_null flag before the fix), CURSOR and SQL_MODE switch are
+# needed only to reproduce the bug.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+SET @orig_sql_mode = @@SQL_MODE;
+SET SQL_MODE=traditional;
+delimiter |;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE v CHAR(10);
+  DECLARE c CURSOR FOR SELECT CHAR(0xff,0x8f USING utf8);
+  OPEN c;
+  FETCH c INTO v;
+  CLOSE c;
+  SELECT v;
+END |
+delimiter ;|
+SET SQL_MODE=@orig_sql_mode;
+
+--echo CALL should return NULL.
+CALL p1();
+
+DROP PROCEDURE p1;
+
+
 --echo End of 5.0 tests