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#4443 | Mattias Jonsson | 10 Jan |
