bzr push into mysql-trunk branch (Dmitry.Shulga:3794 to 3795) Bug#12872824

[Dmitry Shulga]

 3795 Dmitry Shulga	2012-01-27
      Patch for bug#12872824 (formerly known as 62125): testing stored function
      result for null incorrectly yields 1292 warning.
      
      The reason for this bug was that when server executed expression 'IS NULL'
      for return value of some stored function it calculated the return
      value before checking it for NULL. During return value calculation
      it's assumed that a value has INTEGER type that is not true in general.
      
      The solution is to evaluate return value for NULL without its calculation.
     @ mysql-test/r/sp.result
        Added results of test for bug#12872824.
     @ mysql-test/t/sp.test
        Added test for bug#12872824.
     @ sql/item_func.cc
        Added implementation for virtual method update_null_value into
        Item_func_sp. Implementation for this method doesn't call 
        Item::update_null_value() in order to get null flag for
        return value. Instead we call private method execute() that
        check return value for null and set null flag.

    modified:
      mysql-test/r/sp.result
      mysql-test/t/sp.test
      sql/item_func.cc
      sql/item_func.h
 3794 Yasufumi Kinoshita	2012-01-27 [merge]
      Fix for build break introduced at fix for Bug #13413535
      asked by Calvin

    modified:
      storage/innobase/lock/lock0lock.cc
=== modified file 'mysql-test/r/sp.result'
--- a/mysql-test/r/sp.result	2012-01-12 13:22:52 +0000
+++ b/mysql-test/r/sp.result	2012-01-27 05:57:35 +0000
@@ -7666,4 +7666,36 @@ CREATE FUNCTION f1 (p_value INT) RETURNS
 SELECT f1(1);
 ERROR 42S22: Unknown column 'x' in 'field list'
 DROP FUNCTION f1;
+#
+# BUG #12872824 (formerly known as 62125): testing stored function
+#                result for null incorrectly yields 1292 warning.
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+CREATE FUNCTION f1() RETURNS VARCHAR(1)
+BEGIN RETURN 'X'; END;/
+CREATE FUNCTION f2() RETURNS CHAR(1)
+BEGIN RETURN 'X'; END;/
+CREATE FUNCTION f3() RETURNS VARCHAR(1)
+BEGIN RETURN NULL; END;/
+CREATE FUNCTION f4() RETURNS CHAR(1)
+BEGIN RETURN NULL; END;/
+SELECT f1() IS NULL;
+f1() IS NULL
+0
+SELECT f2() IS NULL;
+f2() IS NULL
+0
+SELECT f3() IS NULL;
+f3() IS NULL
+1
+SELECT f4() IS NULL;
+f4() IS NULL
+1
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+#
 # End of 5.6 tests

=== modified file 'mysql-test/t/sp.test'
--- a/mysql-test/t/sp.test	2011-12-22 17:43:39 +0000
+++ b/mysql-test/t/sp.test	2012-01-27 05:57:35 +0000
@@ -8912,4 +8912,44 @@ CREATE FUNCTION f1 (p_value INT) RETURNS
 --error ER_BAD_FIELD_ERROR
 SELECT f1(1);
 DROP FUNCTION f1;
+
+--echo #
+--echo # BUG #12872824 (formerly known as 62125): testing stored function
+--echo #                result for null incorrectly yields 1292 warning.
+
+--disable_warnings
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+DROP FUNCTION IF EXISTS f3;
+DROP FUNCTION IF EXISTS f4;
+--enable_warnings
+
+delimiter /;
+
+CREATE FUNCTION f1() RETURNS VARCHAR(1)
+BEGIN RETURN 'X'; END;/
+
+CREATE FUNCTION f2() RETURNS CHAR(1)
+BEGIN RETURN 'X'; END;/
+
+CREATE FUNCTION f3() RETURNS VARCHAR(1)
+BEGIN RETURN NULL; END;/
+
+CREATE FUNCTION f4() RETURNS CHAR(1)
+BEGIN RETURN NULL; END;/
+
+delimiter ;/
+
+SELECT f1() IS NULL;
+SELECT f2() IS NULL;
+SELECT f3() IS NULL;
+SELECT f4() IS NULL;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP FUNCTION f3;
+DROP FUNCTION f4;
+
+--echo #
+
 --echo # End of 5.6 tests

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2011-12-13 18:18:20 +0000
+++ b/sql/item_func.cc	2012-01-27 05:57:35 +0000
@@ -6598,6 +6598,27 @@ void Item_func_sp::fix_length_and_dec()
 }
 
 
+void Item_func_sp::update_null_value()
+{
+  /*
+    This method is called when we try to check if the item value is NULL.
+    We call Item_func_sp::execute() to get value of null_value attribute
+    as a side effect of its execution.
+    We ignore any error since update_null_value() doesn't return value.
+    We used to delegate nullability check to Item::update_null_value as
+    a result of a chain of function calls:
+     Item_func_isnull::val_int --> Item_func::is_null -->
+      Item::update_null_value -->Item_func_sp::val_int -->
+        Field_varstring::val_int
+    Such approach resulted in a call of push_warning_printf() in case
+    if a stored program value couldn't be cast to integer (the case when
+    for example there was a stored function that declared as returning
+    varchar(1) and a function's implementation returned "Y" from its body).
+  */
+  execute();
+}
+
+
 /**
   @brief Execute function & store value in field.
 

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2012-01-17 14:44:49 +0000
+++ b/sql/item_func.h	2012-01-27 05:57:35 +0000
@@ -1995,6 +1995,8 @@ public:
   {
     return sp_result_field;
   }
+
+  virtual void update_null_value();
 };
 
 

No bundle (reason: useless for push emails).