From: Mattias Jonsson
Date: May 21 2012 10:11pm
Subject: bzr push into mysql-trunk branch (mattias.jonsson:3835 to 3836)
List-Archive: http://lists.mysql.com/commits/143879
Message-Id: <201205212211.q4LMBUH5023164@acsmt357.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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).