bk commit into 5.0 tree (dlenev:1.2321) BUG#20390

[dlenev]

Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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-12-27 23:21:31+03:00, dlenev@stripped +3 -0
  Proposed fix for bug#20390 "SELECT FOR UPDATE does not release locks
  of untouched rows in full table scans".
  
  SELECT ... FOR UPDATE/LOCK IN SHARE MODE statements as well as
  UPDATE/DELETE statements which were executed using full table
  scan were not releasing locks on rows which didn't satisfy 
  WHERE condition.
  This bug surfaced in 5.0 and affected NDB tables. (InnoDB tables
  intentionally don't support such unlocking in default mode).   
  
  This problem occured because code implementing join didn't call
  handler::unlock_row() for rows which didn't satisfy part of condition
  attached to this particular table/level of nested loop. So we solve
  the problem adding this call. 
  Note that we already had this call in place in 4.1 but it was lost
  (actually misplaced) when we have introduced nested joins.
  
  Also note that additional QA should be requested once this patch is
  pushed as interaction between handler::unlock_row() and many recent
  MySQL features such as subqueries, unions, views is not tested enough.
  
  QQ marks question for reviewers.

  mysql-test/r/ndb_lock.result@stripped, 2006-12-27 23:21:28+03:00, dlenev@stripped
+35 -0
    Enabled back part of the test that covers bug #20390 "SELECT FOR
    UPDATE does not release locks of untouched rows in full table scans".
    Adjusted test in such way that it now covers both execution paths
    in which we unlock non-matching rows inspected during table scan.

  mysql-test/t/ndb_lock.test@stripped, 2006-12-27 23:21:28+03:00, dlenev@stripped +46
-6
    Enabled back part of the test that covers bug #20390 "SELECT FOR
    UPDATE does not release locks of untouched rows in full table scans".
    Adjusted test in such way that it now covers both execution paths
    in which we unlock non-matching rows inspected during table scan.

  sql/sql_select.cc@stripped, 2006-12-27 23:21:28+03:00, dlenev@stripped +1 -0
    evaluate_join_record() should call handler::unlock_row() for records
    which don't satisfy condition which was pushed-down to this table/level
    of nested loop.
    We just put back the thing that we already have in 4.1 and which was lost
    when we have introduced nested joins.

# 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:	dlenev
# Host:	mockturtle.local
# Root:	/home/dlenev/src/mysql-5.0-bg20390

--- 1.476/sql/sql_select.cc	2006-12-27 23:21:37 +03:00
+++ 1.477/sql/sql_select.cc	2006-12-27 23:21:37 +03:00
@@ -10387,6 +10387,7 @@
     */
     join->examined_rows++;
     join->thd->row_count++;
+    join_tab->read_record.file->unlock_row();
   }
   return NESTED_LOOP_OK;
 }

--- 1.12/mysql-test/r/ndb_lock.result	2006-12-27 23:21:37 +03:00
+++ 1.13/mysql-test/r/ndb_lock.result	2006-12-27 23:21:37 +03:00
@@ -87,11 +87,27 @@
 rollback;
 commit;
 begin;
+select * from t1 where y = 'one' or y = 'three' for update;
+x	y	z
+#	#	#
+#	#	#
+begin;
+select * from t1 where x = 2 for update;
+x	y	z
+2	two	2
+select * from t1 where x = 1 for update;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+rollback;
+commit;
+begin;
 select * from t1 where y = 'one' or y = 'three' order by x for update;
 x	y	z
 1	one	1
 3	three	3
 begin;
+select * from t1 where x = 2 for update;
+x	y	z
+2	two	2
 select * from t1 where x = 1 for update;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 rollback;
@@ -124,6 +140,22 @@
 rollback;
 commit;
 begin;
+select * from t1 where y = 'one' or y = 'three' lock in share mode;
+x	y	z
+#	#	#
+#	#	#
+begin;
+select * from t1 where y = 'one' lock in share mode;
+x	y	z
+1	one	1
+select * from t1 where x = 2 for update;
+x	y	z
+2	two	2
+select * from t1 where x = 1 for update;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+rollback;
+commit;
+begin;
 select * from t1 where y = 'one' or y = 'three' order by x lock in share mode;
 x	y	z
 1	one	1
@@ -132,6 +164,9 @@
 select * from t1 where y = 'one' lock in share mode;
 x	y	z
 1	one	1
+select * from t1 where x = 2 for update;
+x	y	z
+2	two	2
 select * from t1 where x = 1 for update;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 rollback;

--- 1.14/mysql-test/t/ndb_lock.test	2006-12-27 23:21:37 +03:00
+++ 1.15/mysql-test/t/ndb_lock.test	2006-12-27 23:21:37 +03:00
@@ -102,16 +102,36 @@
 commit;
 
 # table scan
+#
+# Note that there are two distinct execution paths in which we unlock
+# non-matching rows inspected during table scan - one that is used in
+# case of filesort and one that used in rest of cases. Below we cover
+# the latter (Bug #20390 "SELECT FOR UPDATE does not release locks of
+# untouched rows in full table scans").
 connection con1;
 begin;
-select * from t1 where y = 'one' or y = 'three' order by x for update;
+# We can't use "order by x" here as it will cause filesort
+--replace_column 1 # 2 # 3 #
+select * from t1 where y = 'one' or y = 'three' for update;
 
 connection con2;
 begin;
 # Have to check with pk access here since scans take locks on
 # all rows and then release them in chunks
-# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table
scans
-#select * from t1 where x = 2 for update;
+select * from t1 where x = 2 for update;
+--error 1205
+select * from t1 where x = 1 for update;
+rollback;
+
+connection con1;
+commit;
+
+# And now the test for case with filesort
+begin;
+select * from t1 where y = 'one' or y = 'three' order by x for update;
+connection con2;
+begin;
+select * from t1 where x = 2 for update;
 --error 1205
 select * from t1 where x = 1 for update;
 rollback;
@@ -157,15 +177,35 @@
 # table scan
 connection con1;
 begin;
-select * from t1 where y = 'one' or y = 'three' order by x lock in share mode;
+# We can't use "order by x" here as it will cause filesort
+--replace_column 1 # 2 # 3 #
+select * from t1 where y = 'one' or y = 'three' lock in share mode;
 
 connection con2;
 begin;
 select * from t1 where y = 'one' lock in share mode;
 # Have to check with pk access here since scans take locks on
 # all rows and then release them in chunks
-# Bug #20390 SELECT FOR UPDATE does not release locks of untouched rows in full table
scans
-#select * from t1 where x = 2 for update;
+select * from t1 where x = 2 for update;
+--error 1205
+select * from t1 where x = 1 for update;
+rollback;
+
+connection con1;
+commit;
+
+# And the same test for case with filesort
+#
+# QQ: Do we really need separate test case for share mode + filesort?
+#     May be it is better to reduce execution time for this test instead?
+connection con1;
+begin;
+select * from t1 where y = 'one' or y = 'three' order by x lock in share mode;
+
+connection con2;
+begin;
+select * from t1 where y = 'one' lock in share mode;
+select * from t1 where x = 2 for update;
 --error 1205
 select * from t1 where x = 1 for update;
 rollback;