bzr commit into mysql-5.0-bugteam branch (joro:2779) Bug#40113

[Georgi Kodinov]

#At file:///Users/kgeorge/mysql/work/B40113-5.0-bugteam/ based on revid:alfranio.correia@stripped

 2779 Georgi Kodinov	2009-07-13
      Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout 
      without error
      
      When using quick access methods for searching rows in UPDATE or 
      DELETE there was no check if a fatal error was not already sent 
      to the client while evaluating the quick condition.
      As a result a false OK (following the error) was sent to the 
      client and the error was thus transformed into a warning.
      
      Fixed by checking for errors sent to the client during 
      SQL_SELECT::check_quick() and treating them as real errors.
      
      Fixed a wrong test case in group_min_max.test
      Fixed a wrong return code in mysql_update() and mysql_delete()
     @ mysql-test/r/bug40113.result
        Bug #40013: test case
     @ mysql-test/r/group_min_max.result
        Bug #40013: fixed a wrong test case
     @ mysql-test/t/bug40113-master.opt
        Bug #40013: test case
     @ mysql-test/t/bug40113.test
        Bug #40013: test case
     @ mysql-test/t/group_min_max.test
        Bug #40013: fixed a wrong test case
     @ sql/sql_delete.cc
        Bug #40113: check for errors evaluating the quick select
     @ sql/sql_update.cc
        Bug #40113: check for errors evaluating the quick select

    added:
      mysql-test/r/bug40113.result
      mysql-test/t/bug40113-master.opt
      mysql-test/t/bug40113.test
    modified:
      mysql-test/r/group_min_max.result
      mysql-test/t/group_min_max.test
      sql/sql_delete.cc
      sql/sql_update.cc
=== added file 'mysql-test/r/bug40113.result'
--- a/mysql-test/r/bug40113.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/bug40113.result	2009-07-13 15:11:16 +0000
@@ -0,0 +1,29 @@
+#
+# Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout 
+#  without error
+#
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a,b)) ENGINE=InnoDB;
+INSERT INTO t1 (a,b) VALUES (1070109,99);
+CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB;
+INSERT INTO t2 (b,a) VALUES (7,1070109);
+SELECT * FROM t1;
+a	b
[phone]
+BEGIN;
+SELECT b FROM t2 WHERE b=7 FOR UPDATE;
+b
+7
+BEGIN;
+SELECT b FROM t2 WHERE b=7 FOR UPDATE;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7));
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+SELECT * FROM t1;
+a	b
[phone]
+DROP TABLE t2, t1;
+End of 5.0 tests

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2009-06-12 12:38:55 +0000
+++ b/mysql-test/r/group_min_max.result	2009-07-13 15:11:16 +0000
@@ -2278,12 +2278,10 @@ Handler_read_key	8
 Handler_read_next	0
 FLUSH STATUS;
 DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
-FROM t1) > 10000;
-Warnings:
-Error	1242	Subquery returns more than 1 row
+FROM t1 WHERE a = 1 AND b = 1) > 10000;
 SHOW STATUS LIKE 'handler_read__e%';
 Variable_name	Value
-Handler_read_key	8
+Handler_read_key	9
 Handler_read_next	1
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (a int, INDEX idx(a));

=== added file 'mysql-test/t/bug40113-master.opt'
--- a/mysql-test/t/bug40113-master.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/bug40113-master.opt	2009-07-13 15:11:16 +0000
@@ -0,0 +1 @@
+--innodb_lock_wait_timeout=1

=== added file 'mysql-test/t/bug40113.test'
--- a/mysql-test/t/bug40113.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/bug40113.test	2009-07-13 15:11:16 +0000
@@ -0,0 +1,46 @@
+--source include/have_innodb.inc
+
+--echo #
+--echo # Bug #40113: Embedded SELECT inside UPDATE or DELETE can timeout 
+--echo #  without error
+--echo #
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY  (a,b)) ENGINE=InnoDB;
+
+INSERT INTO t1 (a,b) VALUES (1070109,99);
+
+CREATE TABLE t2 (b int, a int, PRIMARY KEY (b)) ENGINE=InnoDB;
+
+INSERT INTO t2 (b,a) VALUES (7,1070109);
+
+SELECT * FROM t1;
+
+BEGIN;
+
+SELECT b FROM t2 WHERE b=7 FOR UPDATE;
+
+CONNECT (addconroot, localhost, root,,);
+CONNECTION addconroot;
+
+BEGIN;
+
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT b FROM t2 WHERE b=7 FOR UPDATE;
+
+--error ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1 (a) VALUES ((SELECT a FROM t2 WHERE b=7));
+
+--error ER_LOCK_WAIT_TIMEOUT
+UPDATE t1 SET a='7000000' WHERE a=(SELECT a FROM t2 WHERE b=7);
+
+--error ER_LOCK_WAIT_TIMEOUT
+DELETE FROM t1 WHERE a=(SELECT a FROM t2 WHERE b=7);
+
+SELECT * FROM t1;
+
+CONNECTION default;
+DISCONNECT addconroot;
+
+DROP TABLE t2, t1;
+
+--echo End of 5.0 tests

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2009-06-12 12:38:55 +0000
+++ b/mysql-test/t/group_min_max.test	2009-07-13 15:11:16 +0000
@@ -866,7 +866,7 @@ DELETE FROM t3 WHERE (SELECT MAX(b) FROM
 SHOW STATUS LIKE 'handler_read__e%';
 FLUSH STATUS;
 DELETE FROM t3 WHERE (SELECT (SELECT MAX(b) FROM t1 GROUP BY a HAVING a < 2) x 
-                      FROM t1) > 10000;
+                      FROM t1 WHERE a = 1 AND b = 1) > 10000;
 SHOW STATUS LIKE 'handler_read__e%';
 
 DROP TABLE t1,t2,t3;

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	2009-06-17 13:54:01 +0000
+++ b/sql/sql_delete.cc	2009-07-13 15:11:16 +0000
@@ -144,6 +144,14 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     delete select;
     free_underlaid_joins(thd, select_lex);
     thd->row_count_func= 0;
+    /* 
+      Error was already created by quick select evaluation (check_quick()).
+      TODO: Add error code output parameter to Item::val_xxx() methods.
+      Currently they rely on the user checking DA for
+      errors when unwinding the stack after calling Item::val_xxx().
+    */
+    if (thd->net.report_error)
+      DBUG_RETURN(TRUE);
     send_ok(thd,0L);
 
     /*
@@ -407,7 +415,7 @@ int mysql_prepare_delete(THD *thd, TABLE
 
   if (select_lex->inner_refs_list.elements &&
     fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
-    DBUG_RETURN(-1);
+    DBUG_RETURN(TRUE);
 
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2009-06-17 13:54:01 +0000
+++ b/sql/sql_update.cc	2009-07-13 15:11:16 +0000
@@ -230,7 +230,7 @@ int mysql_update(THD *thd,
 
   if (select_lex->inner_refs_list.elements &&
     fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
-    DBUG_RETURN(-1);
+    DBUG_RETURN(1);
 
   if (conds)
   {
@@ -247,7 +247,14 @@ int mysql_update(THD *thd,
   {
     delete select;
     free_underlaid_joins(thd, select_lex);
-    if (error)
+    /*
+      There was an error or the error was already sent by
+      the quick select evaluation.
+      TODO: Add error code output parameter to Item::val_xxx() methods.
+      Currently they rely on the user checking DA for
+      errors when unwinding the stack after calling Item::val_xxx().
+    */
+    if (error || thd->net.report_error)
     {
       DBUG_RETURN(1);				// Error in where
     }


Attachment: [text/bzr-bundle] bzr/[e-mail]