bk commit into 5.0 tree (andrey:1.2334) BUG#23760

[ahristov]

Below is the list of changes that have just been committed into a local
5.0 repository of andrey. When andrey 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-14 14:18:10+01:00, andrey@stripped +4 -0
  Fix for bug#23760 ROW_COUNT() and store procedure not owrking together
  
  The problem was that THD::row_count_func was zeroed too early. It's zeroed
  as a fix for bug 4905 "Stored procedure doesn't clear for "Rows affected"

  mysql-test/r/sp.result@stripped, 2006-11-14 14:18:05+01:00, andrey@stripped +81 -0
    update result

  mysql-test/t/sp.test@stripped, 2006-11-14 14:18:05+01:00, andrey@stripped +77 -0
    test for bug#23760 ROW_COUNT() and store procedure not owrking together

  sql/sp_head.cc@stripped, 2006-11-14 14:18:05+01:00, andrey@stripped +11 -0
    Move zeroing of thd->row_count_func to execute_procedure() past
    evaluation of procedure parameters, because this affects ROW_COUNT()
    func call as parameter.

  sql/sql_parse.cc@stripped, 2006-11-14 14:18:06+01:00, andrey@stripped +7 -2
    Remove zeroing for thd->row_count_func, moved to sp_head.cc

# 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:	andrey
# Host:	example.com
# Root:	/work/bug23760/my50

--- 1.587/sql/sql_parse.cc	2006-11-14 14:18:23 +01:00
+++ 1.588/sql/sql_parse.cc	2006-11-14 14:18:23 +01:00
@@ -4456,8 +4456,13 @@ end_with_restore_list:
 	select_limit= thd->variables.select_limit;
 	thd->variables.select_limit= HA_POS_ERROR;
 
-        thd->row_count_func= 0;
-        
+        /*
+          Don't clear any variables which could be used by functions
+          passed as parameters to the procedure. For example ROW_COUNT()
+          relies on THD::row_count_func and if we zero it here, ROW_COUNT()
+          will return incorrect value. The proper place is after parameter
+          evaluation in execute_procedure().
+        */        
         /* 
           We never write CALL statements into binlog:
            - If the mode is non-prelocked, each statement will be logged

--- 1.213/mysql-test/r/sp.result	2006-11-14 14:18:23 +01:00
+++ 1.214/mysql-test/r/sp.result	2006-11-14 14:18:23 +01:00
@@ -5471,4 +5471,85 @@ CHF
 DROP FUNCTION bug21493|
 DROP TABLE t3,t4|
 End of 5.0 tests
+DROP TABLE IF EXISTS bug23760|
+DROP TABLE IF EXISTS bug23760_log|
+DROP PROCEDURE IF EXISTS bug23760_update_log|
+DROP PROCEDURE IF EXISTS bug23760_test_row_count|
+DROP FUNCTION IF EXISTS bug23760_rc_test|
+CREATE TABLE bug23760 (
+id INT NOT NULL AUTO_INCREMENT ,
+num INT NOT NULL ,
+PRIMARY KEY ( id ) 
+)|
+CREATE TABLE bug23760_log (
+id INT NOT NULL AUTO_INCREMENT ,
+reason VARCHAR(50)NULL ,
+ammount INT NOT NULL ,
+PRIMARY KEY ( id ) 
+)|
+CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT)
+BEGIN
+INSERT INTO bug23760_log (reason, ammount) VALUES(r, a);
+END|
+CREATE PROCEDURE bug23760_test_row_count()
+BEGIN
+UPDATE bug23760 SET num = num + 1;
+CALL bug23760_update_log('Test is working', ROW_COUNT());
+UPDATE bug23760 SET num = num - 1;
+END|
+CREATE PROCEDURE bug23760_test_row_count2(level INT)
+BEGIN
+IF level THEN
+UPDATE bug23760 SET num = num + 1;
+CALL bug23760_update_log('Test2 is working', ROW_COUNT());
+CALL bug23760_test_row_count2(level - 1);
+END IF;
+END|
+CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var|
+INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)|
+SELECT ROW_COUNT()|
+ROW_COUNT()
+7
+CALL bug23760_test_row_count()|
+SELECT * FROM bug23760_log ORDER BY id|
+id	reason	ammount
+1	Test is working	7
+SET @save_max_sp_recursion= @@max_sp_recursion_depth|
+SELECT @save_max_sp_recursion|
+@save_max_sp_recursion
+0
+SET max_sp_recursion_depth= 5|
+SELECT @@max_sp_recursion_depth|
+@@max_sp_recursion_depth
+5
+CALL bug23760_test_row_count2(2)|
+SELECT ROW_COUNT()|
+ROW_COUNT()
+0
+SELECT * FROM bug23760_log ORDER BY id|
+id	reason	ammount
+1	Test is working	7
+2	Test2 is working	7
+3	Test2 is working	7
+SELECT * FROM bug23760 ORDER by ID|
+id	num
+1	2
+2	3
+3	3
+4	4
+5	5
+6	7
+7	10
+SET max_sp_recursion_depth= @save_max_sp_recursion|
+SELECT bug23760_rc_test(123)|
+bug23760_rc_test(123)
+123
+INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)|
+SELECT bug23760_rc_test(ROW_COUNT())|
+bug23760_rc_test(ROW_COUNT())
+4
+DROP TABLE bug23760, bug23760_log|
+DROP PROCEDURE bug23760_update_log|
+DROP PROCEDURE bug23760_test_row_count|
+DROP FUNCTION bug23760_rc_test|
 drop table t1,t2;

--- 1.204/mysql-test/t/sp.test	2006-11-14 14:18:23 +01:00
+++ 1.205/mysql-test/t/sp.test	2006-11-14 14:18:23 +01:00
@@ -6427,6 +6427,83 @@ DROP TABLE t3,t4|
 
 
 #
+# BUG#23760: ROW_COUNT() and store procedure not owrking together
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug23760|
+DROP TABLE IF EXISTS bug23760_log|
+DROP PROCEDURE IF EXISTS bug23760_update_log|
+DROP PROCEDURE IF EXISTS bug23760_test_row_count|
+DROP FUNCTION IF EXISTS bug23760_rc_test|
+--enable_warnings
+CREATE TABLE bug23760 (
+  id INT NOT NULL AUTO_INCREMENT ,
+  num INT NOT NULL ,
+  PRIMARY KEY ( id ) 
+)|
+
+CREATE TABLE bug23760_log (
+ id INT NOT NULL AUTO_INCREMENT ,
+ reason VARCHAR(50)NULL ,
+ ammount INT NOT NULL ,
+  PRIMARY KEY ( id ) 
+)|
+
+CREATE PROCEDURE bug23760_update_log(r Varchar(50), a INT)
+BEGIN
+  INSERT INTO bug23760_log (reason, ammount) VALUES(r, a);
+END|
+
+CREATE PROCEDURE bug23760_test_row_count()
+BEGIN
+  UPDATE bug23760 SET num = num + 1;
+  CALL bug23760_update_log('Test is working', ROW_COUNT());
+  UPDATE bug23760 SET num = num - 1;
+END|
+
+
+CREATE PROCEDURE bug23760_test_row_count2(level INT)
+BEGIN
+  IF level THEN
+    UPDATE bug23760 SET num = num + 1;
+    CALL bug23760_update_log('Test2 is working', ROW_COUNT());
+    CALL bug23760_test_row_count2(level - 1);
+  END IF;
+END|
+
+CREATE FUNCTION bug23760_rc_test(in_var INT) RETURNS INT RETURN in_var|
+
+INSERT INTO bug23760 (num) VALUES (0), (1), (1), (2), (3), (5), (8)|
+SELECT ROW_COUNT()|
+
+CALL bug23760_test_row_count()|
+SELECT * FROM bug23760_log ORDER BY id|
+
+SET @save_max_sp_recursion= @@max_sp_recursion_depth|
+SELECT @save_max_sp_recursion|
+SET max_sp_recursion_depth= 5|
+SELECT @@max_sp_recursion_depth|
+CALL bug23760_test_row_count2(2)|
+SELECT ROW_COUNT()|
+SELECT * FROM bug23760_log ORDER BY id|
+SELECT * FROM bug23760 ORDER by ID|
+SET max_sp_recursion_depth= @save_max_sp_recursion|
+
+SELECT bug23760_rc_test(123)|
+INSERT INTO bug23760 (num) VALUES (13), (21), (34), (55)|
+SELECT bug23760_rc_test(ROW_COUNT())|
+
+DROP TABLE bug23760, bug23760_log|
+DROP PROCEDURE bug23760_update_log|
+DROP PROCEDURE bug23760_test_row_count|
+DROP FUNCTION bug23760_rc_test|
+
+#
+# NOTE: The delimiter is `|`, and not `;`. It is changed to `;`
+#       at the end of the file!
+#
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.224/sql/sp_head.cc	2006-11-14 14:18:23 +01:00
+++ 1.225/sql/sp_head.cc	2006-11-14 14:18:23 +01:00
@@ -1675,6 +1675,17 @@ sp_head::execute_procedure(THD *thd, Lis
 
     DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str));
   }
+  DBUG_PRINT("info", ("thd->row_count_func=%d", thd->row_count_func));
+  /*
+    Initialize here and not earlier because thd->row_func_count is used by
+    ROW_COUNT() and if we set it to 0 too early we lose the actual value.
+    This happens in the following case:
+    INSERT INTO xyz VALUES (1);
+    CALL some_proc(ROW_COUNT());
+    But is also possible inside SP, where CALL some_proc(ROW_COUNT()) is
+    called in the body.
+  */
+  thd->row_count_func= 0;
 
   thd->spcont= nctx;