List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:January 9 2012 11:23pm
Subject:bzr push into mysql-trunk branch (mattias.jonsson:3708 to 3709) WL#4443
View as plain text  
 3709 Mattias Jonsson	2012-01-10
      WL#4443 cleanup.
      
      Added test of LOCK TABLES.
      
      Cleaned up some comments.
      
      Added second prune after locking/optmize for DELETE.
      
      Removed LOCK TABLES condition for INSERTs and UPDATEs.

    modified:
      mysql-test/r/partition_explicit_prune.result
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_explicit_prune.test
      mysql-test/t/partition_locking.test
      sql/handler.h
      sql/opt_explain.cc
      sql/opt_range.cc
      sql/sql_base.cc
      sql/sql_delete.cc
      sql/sql_insert.cc
      sql/sql_parse.cc
      sql/sql_resolver.cc
      sql/sql_update.cc
 3708 Mattias Jonsson	2012-01-08
      WL#4443
      
      Changed test to avoid hitting bug#13569548.

    modified:
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_locking.test
=== modified file 'mysql-test/r/partition_explicit_prune.result'
--- a/mysql-test/r/partition_explicit_prune.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/partition_explicit_prune.result	revid:mattias.jonsson@stripped
@@ -367,7 +367,7 @@ HANDLER_EXTERNAL_LOCK	2
 HANDLER_WRITE	17
 # 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443
 # explicit pruning says part_id 0 and implicit pruning says part_id 1
-# so no partition will be locked! (TODO: should we fail before locking?)
+# so no partition will be locked!
 # 0 rollback (since no locked partition)
 # 17 writes (I_S internal)
 FLUSH STATUS;
@@ -450,11 +450,10 @@ HANDLER_EXTERNAL_LOCK	9
 HANDLER_READ_KEY	1
 HANDLER_READ_NEXT	1
 HANDLER_READ_RND_NEXT	38
-HANDLER_ROLLBACK	1
 HANDLER_WRITE	53
 # Failed before start_stmt/execution.
 # + 19 rnd next (internal I_S)
-# + 1 rollback (failed statement)
+#   0 rollback (No partition had called start_stmt, all parts pruned)
 # + 18 write (internal I_S)
 REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE');
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
@@ -466,7 +465,6 @@ HANDLER_EXTERNAL_LOCK	9
 HANDLER_READ_KEY	1
 HANDLER_READ_NEXT	1
 HANDLER_READ_RND_NEXT	57
-HANDLER_ROLLBACK	1
 HANDLER_WRITE	72
 # + 1 commit
 # + 19 rnd next (internal I_S)
@@ -481,7 +479,6 @@ HANDLER_EXTERNAL_LOCK	9
 HANDLER_READ_KEY	2
 HANDLER_READ_NEXT	1
 HANDLER_READ_RND_NEXT	76
-HANDLER_ROLLBACK	1
 HANDLER_UPDATE	1
 HANDLER_WRITE	91
 # + 1 commit
@@ -504,7 +501,6 @@ HANDLER_READ_FIRST	1
 HANDLER_READ_KEY	3
 HANDLER_READ_NEXT	4
 HANDLER_READ_RND_NEXT	95
-HANDLER_ROLLBACK	1
 HANDLER_UPDATE	1
 HANDLER_WRITE	109
 # + 1 commit
@@ -524,7 +520,6 @@ HANDLER_READ_FIRST	1
 HANDLER_READ_KEY	3
 HANDLER_READ_NEXT	4
 HANDLER_READ_RND_NEXT	114
-HANDLER_ROLLBACK	1
 HANDLER_UPDATE	1
 HANDLER_WRITE	127
 # + 9 locks

=== modified file 'mysql-test/r/partition_locking.result'
--- a/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/partition_locking.result	revid:mattias.jonsson@stripped
@@ -501,6 +501,138 @@ d	c
 SELECT d, c FROM t3 PARTITION(`p11-100sp3`);
 d	c
 3	Default
+#
+# Test with LOCK TABLES
+#
+LOCK TABLES t3 PARTITION (`p11-100sp0`) WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (`p11-100sp0`) WRITE' at line 1
+FLUSH STATUS;
+LOCK TABLES t3 WRITE;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_EXTERNAL_LOCK	17
+HANDLER_WRITE	17
+# 17 locks (1 table, 16 partitions lock)
+# No further locks/unlocks until UNLOCK TABLES.
+FLUSH STATUS;
+DELETE FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_DELETE	1
+HANDLER_READ_KEY	1
+HANDLER_READ_NEXT	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+INSERT INTO t3 (b, d, e) VALUES (DEFAULT, DEFAULT, "All default!");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_WRITE	18
+FLUSH STATUS;
+UPDATE t3
+SET e = CONCAT(e, ", updated")
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_KEY	1
+HANDLER_READ_NEXT	1
+HANDLER_UPDATE	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+UPDATE t3
+SET a = DEFAULT, b = "Not DEFAULT!", e = CONCAT(e, ", updated2")
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_KEY	2
+HANDLER_READ_NEXT	1
+HANDLER_READ_RND	1
+HANDLER_UPDATE	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+REPLACE INTO t3 (e) VALUES ("New default row");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_WRITE	18
+FLUSH STATUS;
+SELECT * FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+a	b	c	d	e
+10	Default	Default	9	New default row
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_KEY	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	p10_p10sp1	const	PRIMARY	PRIMARY	140	const,const,const,const	1	
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_KEY	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+REPLACE INTO t3 (e) VALUES ("Newest default row");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_KEY	1
+HANDLER_UPDATE	1
+HANDLER_WRITE	18
+FLUSH STATUS;
+SELECT * FROM t3 PARTITION (p10);
+a	b	c	d	e
+10	Half part	Full subpart	1	Default-filler.filler.filler.
+10	Default	Default	1	Default-filler.filler.filler.
+10	Default	Default	9	Newest default row
+10	Full part, half subpart	Default	1	Default-filler.filler.filler.
+10	Not DEFAULT!	Default	9	All default!, updated, updated2
+10	Part expr fulfilled	Default	9	Default-filler.filler.filler.
+10	Default	Default	2	Default-filler.filler.filler.
+10	Default	Subpart expr fulfilled	1	Default-filler.filler.filler.
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_READ_FIRST	4
+HANDLER_READ_KEY	4
+HANDLER_READ_RND_NEXT	12
+HANDLER_WRITE	17
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t3 PARTITION (p10);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	p10_p10sp0,p10_p10sp1,p10_p10sp2,p10_p10sp3	ALL	NULL	NULL	NULL	NULL	9	
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_WRITE	17
+FLUSH STATUS;
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_EXTERNAL_LOCK	17
+HANDLER_WRITE	17
+# 17 locks (1 table, 16 partitions unlock)
 DROP TABLE t3;
 #
 # Test insert with timestamp column
@@ -2576,6 +2708,24 @@ HANDLER_WRITE	17
 # 13 read_key
 # 1 read_next (if more matches after the first match)
 # 1 delete
+FLUSH STATUS;
+DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME	VARIABLE_VALUE
+HANDLER_COMMIT	1
+HANDLER_DELETE	1
+HANDLER_EXTERNAL_LOCK	32
+HANDLER_READ_KEY	2
+HANDLER_WRITE	17
+# 32 locks (2 table + 13 + 1 partition lock/unlock)
+# 2 read_key
+# 2 read_next (if more matches after the first match)
+# 1 delete
+EXPLAIN PARTITIONS DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	p0	range	PRIMARY,b	PRIMARY	4	NULL	1	Using where
+2	SUBQUERY	t2	p1	const	PRIMARY	PRIMARY	4	const	1	Using index
 #
 # Test multi table delete
 #
@@ -2596,7 +2746,6 @@ a	b
 39	Fourth row, p0, updated 1
 52	Fifth row, p0, updated 1
 65	No duplicate, INSERT_DUP_KEY_UPDATE
-91	No duplicate, INSERT_DUP_KEY_UPDATE
 98	Test98
 104	No duplicate 104
 111	Test111
@@ -2707,7 +2856,6 @@ a	b
 39	Fourth row, p0, updated 1
 52	Fifth row, p0, updated 1
 65	No duplicate, INSERT_DUP_KEY_UPDATE
-91	No duplicate, INSERT_DUP_KEY_UPDATE
 98	Test98
 104	No duplicate 104
 111	Test111
@@ -2937,7 +3085,6 @@ a	b
 39	Fourth row, p0, updated 1
 52	Fifth row, p0, updated 1
 65	No duplicate, INSERT_DUP_KEY_UPDATE
-91	No duplicate, INSERT_DUP_KEY_UPDATE
 98	Test98
 104	No duplicate 104
 111	Test111
@@ -3030,7 +3177,6 @@ a	b
 39	Fourth row, p0, updated 1
 52	Fifth row, p0, updated 1
 65	No duplicate, INSERT_DUP_KEY_UPDATE
-91	No duplicate, INSERT_DUP_KEY_UPDATE
 104	No duplicate 104
 111	Test111
 113	Second row, p0, updated 1, updated 2 -> p8, updated 3, updated 4 -> p9
@@ -3099,6 +3245,7 @@ HANDLER_READ_FIRST	2
 HANDLER_READ_KEY	2
 HANDLER_READ_RND_NEXT	6
 HANDLER_WRITE	17
+# 8 locks (1 table + 3 partitions lock/unlock)
 SELECT @x;
 @x
 Five
@@ -3221,11 +3368,3 @@ INSERT INTO t1 VALUES (1, "test 1");
 INSERT INTO t1 VALUES (SELECT * FROM t1);
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM t1)' at line 1
 DROP TABLE t1;
-# TODO: Add variants for LOCK TABLES
-# TODO: Document that functions in WHERE clause can now be evaluated
-#       before any locks are taken (i.e. if optimization is possible
-#       before locking). Like GET_LOCK/RELEASE_LOCK ?
-# TODO: How to make a stored function or trigger prune?
-# Stored procedures does not prelock, so it will prune locks.
-# Stored functions, triggers and events prelocks all used tables
-# which makes prune locks impossible.

=== modified file 'mysql-test/t/partition_explicit_prune.test'
--- a/mysql-test/t/partition_explicit_prune.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/partition_explicit_prune.test	revid:mattias.jonsson@stripped
@@ -195,7 +195,7 @@ REPLACE INTO t1 PARTITION (subp0) VALUES
 eval $get_handler_status_counts;
 --echo # 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443
 --echo # explicit pruning says part_id 0 and implicit pruning says part_id 1
---echo # so no partition will be locked! (TODO: should we fail before locking?)
+--echo # so no partition will be locked!
 --echo # 0 rollback (since no locked partition)
 --echo # 17 writes (I_S internal)
 FLUSH STATUS;
@@ -236,7 +236,7 @@ REPLACE INTO t1 PARTITION (subp0) VALUES
 eval $get_handler_status_counts;
 --echo # Failed before start_stmt/execution.
 --echo # + 19 rnd next (internal I_S)
---echo # + 1 rollback (failed statement)
+--echo #   0 rollback (No partition had called start_stmt, all parts pruned)
 --echo # + 18 write (internal I_S)
 REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE');
 eval $get_handler_status_counts;

=== modified file 'mysql-test/t/partition_locking.test'
--- a/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/partition_locking.test	revid:mattias.jonsson@stripped
@@ -258,6 +258,58 @@ SELECT d, c FROM t3 PARTITION(`p11-100sp
 --sorted_result
 SELECT d, c FROM t3 PARTITION(`p11-100sp3`);
 
+--echo #
+--echo # Test with LOCK TABLES
+--echo #
+--error ER_PARSE_ERROR
+LOCK TABLES t3 PARTITION (`p11-100sp0`) WRITE;
+FLUSH STATUS;
+LOCK TABLES t3 WRITE;
+eval $get_handler_status_counts;
+--echo # 17 locks (1 table, 16 partitions lock)
+--echo # No further locks/unlocks until UNLOCK TABLES.
+FLUSH STATUS;
+DELETE FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+INSERT INTO t3 (b, d, e) VALUES (DEFAULT, DEFAULT, "All default!");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+UPDATE t3
+SET e = CONCAT(e, ", updated")
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+UPDATE t3
+SET a = DEFAULT, b = "Not DEFAULT!", e = CONCAT(e, ", updated2")
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+REPLACE INTO t3 (e) VALUES ("New default row");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t3
+WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
+eval $get_handler_status_counts;
+FLUSH STATUS;
+REPLACE INTO t3 (e) VALUES ("Newest default row");
+eval $get_handler_status_counts;
+FLUSH STATUS;
+SELECT * FROM t3 PARTITION (p10);
+eval $get_handler_status_counts;
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t3 PARTITION (p10);
+eval $get_handler_status_counts;
+
+FLUSH STATUS;
+UNLOCK TABLES;
+eval $get_handler_status_counts;
+--echo # 17 locks (1 table, 16 partitions unlock)
 DROP TABLE t3;
 
 --echo #
@@ -1186,6 +1238,14 @@ eval $get_handler_status_counts;
 --echo # 13 read_key
 --echo # 1 read_next (if more matches after the first match)
 --echo # 1 delete
+FLUSH STATUS;
+DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
+eval $get_handler_status_counts;
+--echo # 32 locks (2 table + 13 + 1 partition lock/unlock)
+--echo # 2 read_key
+--echo # 2 read_next (if more matches after the first match)
+--echo # 1 delete
+EXPLAIN PARTITIONS DELETE FROM t1 WHERE a = (SELECT a + 90 FROM t2 WHERE a = 1);
 
 --echo #
 --echo # Test multi table delete
@@ -1428,6 +1488,7 @@ SELECT @x;
 FLUSH STATUS;
 DO (SELECT @x:= b FROM t1 WHERE a = 5 or a = 1 ORDER BY b LIMIT 1);
 eval $get_handler_status_counts;
+--echo # 8 locks (1 table + 3 partitions lock/unlock)
 SELECT @x;
 
 DROP TABLE t1;
@@ -1493,18 +1554,3 @@ INSERT INTO t1 VALUES (1, "test 1");
 INSERT INTO t1 VALUES (SELECT * FROM t1);
 DROP TABLE t1;
 
---echo # TODO: Add variants for LOCK TABLES
---echo # TODO: Document that functions in WHERE clause can now be evaluated
---echo #       before any locks are taken (i.e. if optimization is possible
---echo #       before locking). Like GET_LOCK/RELEASE_LOCK ?
-
---echo # TODO: How to make a stored function or trigger prune?
---echo # Stored procedures does not prelock, so it will prune locks.
---echo # Stored functions, triggers and events prelocks all used tables
---echo # which makes prune locks impossible.
-
-
-# NOTEs:
-# 
-# To investigate:
-# * effects on replication (statement and row)

=== modified file 'sql/handler.h'
--- a/sql/handler.h	revid:mattias.jonsson@stripped
+++ b/sql/handler.h	revid:mattias.jonsson@stripped
@@ -2432,7 +2432,7 @@ public:
   typedef void (handler::*range_check_toggle_func_t)(bool on);
 
   DsMrr_impl()
-    : h2(NULL), rowids_buf(NULL), rowids_buf_cur(NULL) {};
+    : h2(NULL) {};
   
   /*
     The "owner" handler object (the one that calls dsmrr_XXX functions.

=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc	revid:mattias.jonsson@stripped
+++ b/sql/opt_explain.cc	revid:mattias.jonsson@stripped
@@ -1546,7 +1546,6 @@ bool mysql_explain_unit(THD *thd, SELECT
   {
     thd->lex->current_select= first;
     unit->set_limit(unit->global_parameters);
-    // TODO: Verify that this prune partition locks!
     res= mysql_select(thd,
                       first->table_list.first,
                       first->with_wild, first->item_list,

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	revid:mattias.jonsson@stripped
+++ b/sql/opt_range.cc	revid:mattias.jonsson@stripped
@@ -3036,11 +3036,13 @@ static void dbug_print_singlepoint_range
   @param      pprune_cond    Condition to use for partition pruning
   @param[out] no_parts_used  If no partitions can fulfil the condition
   
-  @note This function assumes that all partitions are marked as unused when it
+  @note This function assumes that lock_partitions are setup when it
   is invoked. The function analyzes the condition, finds partitions that
   need to be used to retrieve the records that match the condition, and 
   marks them as used by setting appropriate bit in part_info->read_partitions
-  In the worst case all partitions are marked as used.
+  In the worst case all partitions are marked as used. If the table is not
+  yet locked, it will also unset bits in part_info->lock_partitions that is
+  not set in read_partitions.
 
   This function returns promptly if called for non-partitioned table.
 

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_base.cc	revid:mattias.jonsson@stripped
@@ -5660,7 +5660,7 @@ void open_and_lock_query_tables_cleanup(
 
 
 /**
-  Open tables and open derived tables and prepares them.
+  Open query tables, open derived tables and prepares them.
 
   @param         thd            Thread context.
 

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_delete.cc	revid:mattias.jonsson@stripped
@@ -60,6 +60,9 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   bool          skip_record;
   bool          need_sort= FALSE;
   bool          err= true;
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+  bool no_parts_used;
+#endif
   ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
                            order_list->first : NULL);
   uint usable_index= MAX_KEY;
@@ -103,13 +106,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *
       DBUG_RETURN(TRUE);
     }
   }
-  
+
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   /*
     Non delete tables are pruned in JOIN::prepare,
     only the delete table needs this.
   */
-  bool no_parts_used;
   if (prune_partitions(thd, table, conds, &no_parts_used))
     DBUG_RETURN(true);
   if (no_parts_used)
@@ -126,7 +128,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
     DBUG_RETURN(0);
   }
 #endif
-  
+
   if (lock_query_tables(thd))
     DBUG_RETURN(true);
 
@@ -220,7 +222,25 @@ bool mysql_delete(THD *thd, TABLE_LIST *
   table->covering_keys.clear_all();
   table->quick_keys.clear_all();		// Can't use 'only index'
 
-  // TODO: verify that subqueries does re-prune here.
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+  /* Prune a second time to be able to prune on subqueries in WHERE clause. */
+  if (prune_partitions(thd, table, conds, &no_parts_used))
+    DBUG_RETURN(true);
+  if (no_parts_used)
+  {
+    /* No matching records */
+    if (thd->lex->describe)
+    {
+      err= explain_no_table(thd, "No matching rows after partition pruning");
+      goto exit_without_my_ok;
+    }
+
+    free_underlaid_joins(thd, select_lex);
+    my_ok(thd, 0);
+    DBUG_RETURN(0);
+  }
+#endif
+
   select=make_select(table, 0, 0, conds, 0, &error);
   if (error)
     DBUG_RETURN(TRUE);

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_insert.cc	revid:mattias.jonsson@stripped
@@ -83,7 +83,6 @@
 #include "sql_optimizer.h"    // JOIN
 #ifdef WITH_PARTITION_STORAGE_ENGINE
 #include "sql_partition.h"
-// TODO: make partition_info.h self sufficient!
 #include "partition_info.h"            // partition_info
 #endif
 
@@ -864,10 +863,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 #ifdef WITH_PARTITION_STORAGE_ENGINE
   if (!is_locked && table->part_info)
   {
-    /* TODO: Move this to partition_info.h/cc ? */
-    /* Only prune if the tables are not yet locked. */
-    if (thd->locked_tables_mode == LTM_NONE)
-      can_prune_partitions= PRUNE_YES;
+    /*
+      Start with enable pruning and disable if not possible.
+      If under LOCK TABLES it will skip start_stmt instead of external_lock
+      for unused partitions.
+    */
+    can_prune_partitions= PRUNE_YES;
     /*
       Cannot prune if there are BEFORE INSERT triggers,
       since they may change the row to be in another partition.
@@ -904,7 +905,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       But TIMESTAMP_AUTO_SET_ON_UPDATE cannot be pruned if the timestamp
       column is a part of any part/subpart expression.
       
-      TODO: Verify this again when WL#5874 completed.
+      TODO: Verify this again when merging with WL#5874.
     */
     if (can_prune_partitions && duplic == DUP_UPDATE)
     {
@@ -974,9 +975,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       DBUG_ASSERT(table->part_info->bitmaps_are_initialized);
 
       /*
-        Pruning probably possible, must mark none partitions for read/lock,
-        and add them on row by row basis (if any row does not set auto_inc
-        value explicitly, all partitions must be marked).
+        Pruning probably possible, must unmark all partitions for read/lock,
+        and add them on row by row basis.
       */
       num_partitions= table->part_info->lock_partitions.n_bits;
       bitmap_bytes= bitmap_buffer_size(num_partitions);
@@ -991,10 +991,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
         mem_alloc_error(bitmap_bytes);   /* Cannot happen, due to pre-alloc */
         goto exit_without_my_ok;
       }
-      /* Check the first INSERT value. */
       /*
+        Check the first INSERT value.
         Do not fail here, since that would break MyISAM behavior of inserting
-        all rows before failing row.
+        all rows before the failing row.
       */
       if (set_partition(thd, table, fields, *values,
                         prune_needs_default_values, &used_partitions))
@@ -1016,10 +1016,11 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 
 #ifdef WITH_PARTITION_STORAGE_ENGINE
     /*
-      TODO: Is it possible to store the calculated part_id and reuse in
+      TODO: Cache the calculated part_id and reuse in
       ha_partition::write_row()?
-      Should it also be done if num rows >> num partitions? Yes to increase
-      concurrancy on partitioned tables that use table locking, like MyISAM.
+      Should we check pruning even if num rows >> num partitions?
+      Yes to increase concurrancy on partitioned tables that use
+      table locking, like MyISAM.
     */
     if (can_prune_partitions == PRUNE_YES)
     {
@@ -1840,15 +1841,8 @@ int write_record(THD *thd, TABLE *table,
 	goto err;
       if (table->file->ha_table_flags() & HA_DUPLICATE_POS)
       {
-        if (table->file->ha_rnd_init(0))
-          goto err;
-        if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref))
-        {
-          (void) table->file->ha_rnd_end();
-          goto err;
-        }
-        if (table->file->ha_rnd_end())
-          goto err;
+	if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref))
+	  goto err;
       }
       else
       {

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_parse.cc	revid:mattias.jonsson@stripped
@@ -2393,7 +2393,8 @@ case SQLCOM_PREPARE:
   }
   case SQLCOM_DO:
     if (check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE)
-        || open_and_lock_tables(thd, all_tables, TRUE, 0))
+        || open_query_tables(thd)
+        || lock_query_tables(thd))
       goto error;
 
     res= mysql_do(thd, *lex->insert_list);
@@ -3434,9 +3435,9 @@ end_with_restore_list:
   {
     List<set_var_base> *lex_var_list= &lex->var_list;
 
-    if ((check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE)
-         // TODO: Verify for WL#4443
-         || open_and_lock_tables(thd, all_tables, TRUE, 0)))
+    if (check_table_access(thd, SELECT_ACL, all_tables, FALSE, UINT_MAX, FALSE)
+        || open_query_tables(thd)
+        || lock_query_tables(thd))
       goto error;
     if (!(res= sql_set_variables(thd, lex_var_list)))
     {
@@ -4212,11 +4213,6 @@ create_sp_error:
       */
       if (check_table_access(thd, SELECT_ACL, all_tables, FALSE,
                              UINT_MAX, FALSE) ||
-          /*
-            TODO: Check if possible to delay locking here
-            to support sp's arguments pruning in WL#4443.
-          */
-          //open_and_lock_tables(thd, all_tables, TRUE, 0))
           open_query_tables(thd) ||
           lock_query_tables(thd))
        goto error;
@@ -4787,7 +4783,6 @@ static bool execute_sqlcom_select(THD *t
       param->select_limit=
         new Item_int((ulonglong) thd->variables.select_limit);
   }
-  //if (!(res= open_and_lock_tables(thd, all_tables, TRUE, 0)))
   if (!(res= open_query_tables(thd)))
   {
     if (lex->describe)

=== modified file 'sql/sql_resolver.cc'
--- a/sql/sql_resolver.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_resolver.cc	revid:mattias.jonsson@stripped
@@ -376,7 +376,7 @@ JOIN::prepare(TABLE_LIST *tables_init,
         This will only prune constant conditions, which will be used for
         lock pruning.
       */
-     Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
+      Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
       if (prune_partitions(thd, tbl->table, prune_cond,
                            &tbl->table->no_partitions_used))
         goto err;

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_update.cc	revid:mattias.jonsson@stripped
@@ -393,18 +393,18 @@ int mysql_update(THD *thd,
     bool no_parts_used;
     bool prune_locks= true;
     MY_BITMAP lock_partitions;
-    if (table->file->get_lock_type() == F_UNLCK &&
-        table->triggers &&
+    if (table->triggers &&
         table->triggers->has_triggers(TRG_EVENT_UPDATE, TRG_ACTION_BEFORE))
     {
       /*
-        If the table is not locked, prune_partitions will also prune partition
-        locks. But BEFORE UPDATE triggers may change the records partitioning
+        BEFORE UPDATE triggers may change the records partitioning
         column, forcing it to another partition.
-        So it is not possible to prune locked partitions,
-        only read partitions.
+        So it is not possible to prune external_lock/start_stmt for
+        partitions (lock_partitions bitmap), only for scanning
+        (read_partitions bitmap).
         Copy the current lock_partitions bitmap and restore it after
-        prune_partitions call.
+        prune_partitions call, to lock all non explicitly selected
+        partitions.
       */
       uint32 *bitmap_buf;
       uint bitmap_bytes;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (mattias.jonsson:3708 to 3709) WL#4443Mattias Jonsson10 Jan