3836 Mattias Jonsson 2012-05-22
WL4443:
Verified that tbl->embedding must be checked in ::optimize(),
and must not be checked in ::prepare().
Added tests for const propagations (does not work for locks).
Fixed rpl_auto_increment.test to avoid leaving files behind.
modified:
mysql-test/extra/rpl_tests/rpl_auto_increment.test
mysql-test/r/partition_locking.result
mysql-test/r/partition_pruning.result
mysql-test/t/partition_locking.test
sql/sql_optimizer.cc
sql/sql_resolver.cc
3835 Mattias Jonsson 2012-05-19
WL#4443
Fix for CREATE SELECT and delayed locking.
To be consistent with the protocol for locking
and THD::decide_logging_format(), we postpone
locking of target table to after all locks has been
taken on the source tables.
i.e. we create the table in create_select::prepare()
and lock it in create_select::prepare2().
prepare() is called from JOIN::prepare() and
prepare2() from JOIN::exec().
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/sql_class.h
sql/sql_insert.cc
sql/sql_select.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_auto_increment.test'
--- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test revid:mattias.jonsson@stripped
+++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test revid:mattias.jonsson@stripped
@@ -230,6 +230,7 @@ connection slave;
source include/start_slave.inc;
connection master;
--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.saved | $MYSQL test
+--remove_file $MYSQLD_DATADIR/master-bin.saved;
sync_slave_with_master;
let $diff_tables= master:t1, slave:t1;
=== 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
@@ -4737,6 +4737,330 @@ a b
2 new2 a: 2 new2 b: MultiUpdate2,(2) t1.b:MultiUpdate2
DROP TABLE t1, t2;
#
+# Test constant propagation in WHERE clause
+# (Currently no propagation is done before locking).
+CREATE TABLE t1 (a int, b varchar(128), KEY (b))
+ENGINE = InnoDB
+PARTITION BY RANGE (a)
+(PARTITION pNeg VALUES LESS THAN (0),
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION pMax VALUES LESS THAN MAXVALUE);
+CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128))
+ENGINE = InnoDB
+PARTITION BY RANGE (a)
+(PARTITION pNeg VALUES LESS THAN (0),
+PARTITION p0 VALUES LESS THAN (1),
+PARTITION p1 VALUES LESS THAN (2),
+PARTITION p2 VALUES LESS THAN (3),
+PARTITION p3 VALUES LESS THAN (4),
+PARTITION pMax VALUES LESS THAN MAXVALUE);
+INSERT INTO t1 VALUES (1, "Const1");
+INSERT INTO t2 VALUES (1, "Const1");
+INSERT INTO t1 VALUES (2, "Const2");
+INSERT INTO t2 VALUES (2, "Const2");
+INSERT INTO t1 VALUES (3, "Const3");
+INSERT INTO t2 VALUES (3, "Const3");
+# Test simple '=' propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a = 1;
+a b a b
+1 Const1 1 Const1
+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 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND_NEXT 2
+HANDLER_WRITE 17
+# 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a = 1;
+a b a b
+1 Const1 1 Const1
+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 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND_NEXT 2
+HANDLER_WRITE 17
+# 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+# Test OR propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND (t2.a = 1 OR t2.a = 2);
+a b a b
+1 Const1 1 Const1
+2 Const2 2 Const2
+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 20
+HANDLER_READ_FIRST 2
+HANDLER_READ_KEY 4
+HANDLER_READ_RND_NEXT 4
+HANDLER_WRITE 17
+# 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+# But it will be scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND (t1.a = 1 OR t1.a = 2);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 p1,p2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL
+# Test closed range propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
+a b a b
+1 Const1 1 Const1
+2 Const2 2 Const2
+3 Const3 3 Const3
+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 22
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 3
+HANDLER_READ_RND_NEXT 6
+HANDLER_WRITE 17
+# 22 locks (2 tables, 6 + 3 partitions lock/unlock)
+# But it will be scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 p1,p2,p3 range PRIMARY PRIMARY 4 NULL 3 Using where
+1 SIMPLE t1 p1,p2,p3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (Block Nested Loop)
+# Test open range propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a >= 1;
+a b a b
+1 Const1 1 Const1
+2 Const2 2 Const2
+3 Const3 3 Const3
+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 24
+HANDLER_READ_FIRST 4
+HANDLER_READ_KEY 7
+HANDLER_READ_RND_NEXT 7
+HANDLER_WRITE 17
+# 24 locks (2 tables, 6 + 4 partitions lock/unlock)
+# But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a >= 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p2,p3,pMax ALL NULL NULL NULL NULL 4 Using where
+1 SIMPLE t2 p1,p2,p3,pMax eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a <= 1;
+a b a b
+1 Const1 1 Const1
+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 22
+HANDLER_READ_FIRST 3
+HANDLER_READ_KEY 4
+HANDLER_READ_RND_NEXT 4
+HANDLER_WRITE 17
+# 22 locks (2 tables, 6 + 3 partitions lock/unlock)
+# But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a <= 1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 pNeg,p0,p1 ALL NULL NULL NULL NULL 3 Using where
+1 SIMPLE t2 pNeg,p0,p1 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL
+# Test IN propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a and t2.a IN (1, 3);
+a b a b
+1 Const1 1 Const1
+3 Const3 3 Const3
+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 20
+HANDLER_READ_FIRST 2
+HANDLER_READ_KEY 4
+HANDLER_READ_RND_NEXT 4
+HANDLER_WRITE 17
+# 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+# But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a IN (1, 3);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,p3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 p1,p3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL
+# Same for UPDATE
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
+WHERE t1.a = t2.a and t2.a IN (2, 3);
+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 20
+HANDLER_READ_FIRST 2
+HANDLER_READ_KEY 6
+HANDLER_READ_RND 2
+HANDLER_READ_RND_NEXT 7
+HANDLER_UPDATE 2
+HANDLER_WRITE 19
+# 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+# But is scanned pruned!
+EXPLAIN PARTITIONS UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
+WHERE t1.a = t2.a and t2.a IN (2, 3);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p2,p3 ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 p2,p3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 NULL
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
+t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
+WHERE t1.a = t2.a and t2.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_EXTERNAL_LOCK 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 3
+HANDLER_READ_RND 1
+HANDLER_READ_RND_NEXT 4
+HANDLER_UPDATE 2
+HANDLER_WRITE 18
+# 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
+t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
+WHERE t1.a = t2.a and t1.a = 2;
+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 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 3
+HANDLER_READ_RND 1
+HANDLER_READ_RND_NEXT 4
+HANDLER_UPDATE 2
+HANDLER_WRITE 18
+# 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a;
+a b
+1 Const1, t2.b:Const1
+2 Const2, t2.b:Const2, t2.b:Const2
+3 Const3, t2.b:Const3
+SELECT * FROM t2 ORDER BY a;
+a b
+1 Const1, t1.b:Const1
+2 Const2, t1.b:Const2, t2.b:Const2
+3 Const3
+# Same for DELETE
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a AND t2.a IN (1, 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_EXTERNAL_LOCK 20
+HANDLER_READ_FIRST 2
+HANDLER_READ_KEY 3
+HANDLER_READ_RND_NEXT 3
+HANDLER_WRITE 17
+# 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+# But is scanned pruned!
+EXPLAIN PARTITIONS DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a AND t2.a IN (1, 9);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1,pMax ALL NULL NULL NULL NULL 2 Using where
+1 SIMPLE t2 p1,pMax eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
+SELECT * FROM t1 ORDER BY a;
+a b
+2 Const2, t2.b:Const2, t2.b:Const2
+3 Const3, t2.b:Const3
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a and t2.a = 2;
+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 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND_NEXT 2
+HANDLER_WRITE 17
+# 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a and t1.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_EXTERNAL_LOCK 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND_NEXT 1
+HANDLER_WRITE 17
+# 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a;
+a b
+3 Const3, t2.b:Const3
+SELECT * FROM t2 ORDER BY a;
+a b
+1 Const1, t1.b:Const1
+2 Const2, t1.b:Const2, t2.b:Const2
+3 Const3
+FLUSH STATUS;
+DELETE t1, t2 FROM t1, t2
+WHERE t1.a = t2.a and t2.a = 3;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 2
+HANDLER_EXTERNAL_LOCK 18
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 3
+HANDLER_READ_RND 1
+HANDLER_READ_RND_NEXT 2
+HANDLER_WRITE 17
+# 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a;
+a b
+SELECT * FROM t2 ORDER BY a;
+a b
+1 Const1, t1.b:Const1
+2 Const2, t1.b:Const2, t2.b:Const2
+DROP TABLE t1, t2;
+#
# DO is not supported by WL#4443 !!!
# Test of DO (eg. SELECT without returning values)
#
=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result revid:mattias.jonsson@stripped
+++ b/mysql-test/r/partition_pruning.result revid:mattias.jonsson@stripped
@@ -2884,7 +2884,7 @@ Variable_name Value
Handler_read_rnd_next 0
show status like 'Handler_external_lock';
Variable_name Value
-Handler_external_lock 8
+Handler_external_lock 4
explain extended delete t1,t2 from t1, t2 where t1.a=t2.a and t2.a=3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
@@ -2900,7 +2900,7 @@ Variable_name Value
Handler_read_rnd_next 15
show status like 'Handler_external_lock';
Variable_name Value
-Handler_external_lock 10
+Handler_external_lock 8
select * from t1;
a
1
=== 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
@@ -1779,6 +1779,174 @@ SELECT * FROM t2 ORDER BY a;
DROP TABLE t1, t2;
--echo #
+--echo # Test constant propagation in WHERE clause
+--echo # (Currently no propagation is done before locking).
+CREATE TABLE t1 (a int, b varchar(128), KEY (b))
+ENGINE = InnoDB
+PARTITION BY RANGE (a)
+(PARTITION pNeg VALUES LESS THAN (0),
+ PARTITION p0 VALUES LESS THAN (1),
+ PARTITION p1 VALUES LESS THAN (2),
+ PARTITION p2 VALUES LESS THAN (3),
+ PARTITION p3 VALUES LESS THAN (4),
+ PARTITION pMax VALUES LESS THAN MAXVALUE);
+
+CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128))
+ENGINE = InnoDB
+PARTITION BY RANGE (a)
+(PARTITION pNeg VALUES LESS THAN (0),
+ PARTITION p0 VALUES LESS THAN (1),
+ PARTITION p1 VALUES LESS THAN (2),
+ PARTITION p2 VALUES LESS THAN (3),
+ PARTITION p3 VALUES LESS THAN (4),
+ PARTITION pMax VALUES LESS THAN MAXVALUE);
+
+
+INSERT INTO t1 VALUES (1, "Const1");
+INSERT INTO t2 VALUES (1, "Const1");
+INSERT INTO t1 VALUES (2, "Const2");
+INSERT INTO t2 VALUES (2, "Const2");
+INSERT INTO t1 VALUES (3, "Const3");
+INSERT INTO t2 VALUES (3, "Const3");
+
+--echo # Test simple '=' propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a = 1;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+#--echo # 8 locks (2 tables + 2 partitions lock/unlock)
+
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a = 1;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+#--echo # 8 locks (2 tables + 2 partitions lock/unlock)
+
+--echo # Test OR propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND (t2.a = 1 OR t2.a = 2);
+eval $get_handler_status_counts;
+--echo # 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+--echo # But it will be scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND (t1.a = 1 OR t1.a = 2);
+
+--echo # Test closed range propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
+eval $get_handler_status_counts;
+--echo # 22 locks (2 tables, 6 + 3 partitions lock/unlock)
+
+--echo # But it will be scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a >= 1 AND t1.a <=3;
+
+--echo # Test open range propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a >= 1;
+eval $get_handler_status_counts;
+--echo # 24 locks (2 tables, 6 + 4 partitions lock/unlock)
+--echo # But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a >= 1;
+
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a <= 1;
+eval $get_handler_status_counts;
+--echo # 22 locks (2 tables, 6 + 3 partitions lock/unlock)
+--echo # But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t2.a <= 1;
+
+--echo # Test IN propagation
+FLUSH STATUS;
+SELECT * FROM t1, t2
+WHERE t1.a = t2.a and t2.a IN (1, 3);
+eval $get_handler_status_counts;
+--echo # 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+--echo # But is scanned pruned!
+EXPLAIN PARTITIONS SELECT * FROM t1, t2
+WHERE t1.a = t2.a AND t1.a IN (1, 3);
+
+--echo # Same for UPDATE
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
+WHERE t1.a = t2.a and t2.a IN (2, 3);
+eval $get_handler_status_counts;
+--echo # 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+--echo # But is scanned pruned!
+EXPLAIN PARTITIONS UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b)
+WHERE t1.a = t2.a and t2.a IN (2, 3);
+
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
+ t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
+WHERE t1.a = t2.a and t2.a = 1;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+#--echo # 8 locks (2 tables, 2 partitions lock/unlock)
+
+FLUSH STATUS;
+UPDATE t1, t2
+SET t1.b = CONCAT(t1.b, ", t2.b:", t2.b),
+ t2.b = CONCAT(t2.b, ", t1.b:", t1.b)
+WHERE t1.a = t2.a and t1.a = 2;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+#--echo # 8 locks (2 tables, 2 partitions lock/unlock)
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+--echo # Same for DELETE
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a AND t2.a IN (1, 9);
+eval $get_handler_status_counts;
+--echo # 20 locks (2 tables, 6 + 2 partitions lock/unlock)
+--echo # But is scanned pruned!
+EXPLAIN PARTITIONS DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a AND t2.a IN (1, 9);
+
+SELECT * FROM t1 ORDER BY a;
+
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a and t2.a = 2;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+#--echo # 8 locks (2 tables, 2 partitions lock/unlock)
+FLUSH STATUS;
+DELETE t1 FROM t1, t2
+WHERE t1.a = t2.a and t1.a = 1;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 1 + 6 partitions lock/unlock)
+#--echo # 8 locks (2 tables, 2 partitions lock/unlock)
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+
+FLUSH STATUS;
+DELETE t1, t2 FROM t1, t2
+WHERE t1.a = t2.a and t2.a = 3;
+eval $get_handler_status_counts;
+--echo # 18 locks (2 tables, 6 + 1 partitions lock/unlock)
+#--echo # 8 locks (2 tables, 2 partitions lock/unlock)
+
+SELECT * FROM t1 ORDER BY a;
+SELECT * FROM t2 ORDER BY a;
+DROP TABLE t1, t2;
+
+--echo #
--echo # DO is not supported by WL#4443 !!!
--echo # Test of DO (eg. SELECT without returning values)
--echo #
@@ -1995,3 +2163,4 @@ eval $get_handler_status_counts;
SELECT * FROM t2;
DROP TABLE t1, t2;
+
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc revid:mattias.jonsson@stripped
+++ b/sql/sql_optimizer.cc revid:mattias.jonsson@stripped
@@ -270,12 +270,9 @@ JOIN::optimize()
This will try to prune non-static conditions, which can
be used after the tables are locked.
*/
- //DBUG_ASSERT(!tbl->embedding);
-#ifndef TODO_INVESTIGATE
if (!tbl->embedding)
-#endif
{
- Item *prune_cond= tbl->join_cond()? tbl->join_cond() : conds;
+ Item *prune_cond= tbl->join_cond() ? tbl->join_cond() : conds;
if (prune_partitions(thd, tbl->table, prune_cond))
{
error= 1;
=== modified file 'sql/sql_resolver.cc'
--- a/sql/sql_resolver.cc revid:mattias.jonsson@stripped
+++ b/sql/sql_resolver.cc revid:mattias.jonsson@stripped
@@ -385,10 +385,6 @@ JOIN::prepare(TABLE_LIST *tables_init,
TABLE_LIST *tbl;
for (tbl= select_lex->leaf_tables; tbl; tbl= tbl->next_leaf)
{
-#ifdef TODO_INVESTIGATE
- if (tbl->embedding)
- continue;
-#endif
/*
This will only prune constant conditions, which will be used for
lock pruning.
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (mattias.jonsson:3835 to 3836) | Mattias Jonsson | 22 May |
