List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:May 9 2012 3:15pm
Subject:bzr push into mysql-trunk branch (mattias.jonsson:3799 to 3800) WL#4443
View as plain text  
 3800 Mattias Jonsson	2012-05-09
      WL#4443:
      Changes for the first 1/3 of dlenevs review comments chunk 2.
      
      Mostly test/result changes to be more clear about what
      is tested.
      
      Fix for bitmap_get_next_set on big-endian machines.
      
      Minor cleanups.

    removed:
      mysql-test/r/grant_cache_ps_prot.result
      mysql-test/t/grant_cache_ps_prot.test
    renamed:
      mysql-test/r/grant_cache_no_prot.result => mysql-test/r/grant_cache.result
      mysql-test/t/grant_cache_no_prot.test => mysql-test/t/grant_cache.test
    modified:
      mysql-test/include/commit.inc
      mysql-test/include/grant_cache.inc
      mysql-test/include/handler.inc
      mysql-test/r/commit_1innodb.result
      mysql-test/r/handler_innodb.result
      mysql-test/r/handler_myisam.result
      mysql-test/r/partition_explicit_prune.result
      mysql-test/r/partition_locking.result
      mysql-test/r/partition_pruning.result
      mysql-test/t/partition_explicit_prune.test
      mysql-test/t/partition_locking.test
      mysql-test/t/partition_pruning.test
      mysys/my_bitmap.c
      sql/ha_partition.cc
      sql/sql_base.cc
      sql/sql_delete.cc
      sql/sql_insert.cc
      sql/sql_partition_admin.cc
      sql/sql_select.cc
      mysql-test/t/grant_cache.test
 3799 Mattias Jonsson	2012-05-08
      WL#4443
      Avoid creating SEL_TREE when not needed in
      prune_partitions().

    modified:
      mysql-test/r/partition_pruning.result
      sql/opt_range.cc
      sql/partition_info.cc
      sql/partition_info.h
=== modified file 'mysql-test/include/commit.inc'
--- a/mysql-test/include/commit.inc	revid:mattias.jonsson@stripped
+++ b/mysql-test/include/commit.inc	revid:mattias.jonsson@stripped
@@ -764,10 +764,6 @@ truncate table t3;
 call p_verify_status_increment(2, 0, 2, 0);
 create view v1 as select * from t2;
 call p_verify_status_increment(0, 0, 0, 0);
-select * from v1;
-call p_verify_status_increment(1, 0, 1, 0);
-commit;
-call p_verify_status_increment(1, 0, 1, 0);
 check table t1;
 call p_verify_status_increment(2, 0, 2, 0);
 --echo # Sic: after this bug is fixed, CHECK leaves no pending transaction

=== modified file 'mysql-test/include/grant_cache.inc'
--- a/mysql-test/include/grant_cache.inc	revid:mattias.jonsson@stripped
+++ b/mysql-test/include/grant_cache.inc	revid:mattias.jonsson@stripped
@@ -2,12 +2,11 @@
 #
 # Test grants with query cache
 #
-# Last update:
-# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc
-#               - Remove the disabling of the ps-protocol
-#               - minor improvements like error names instead of numbers
-#               - Create two toplevel tests sourcing this routine
+# Now normal protocol and ps-protocol produces the same result.
+# (After splitting open and lock tables into different stages,
+# to be able to prune locks of individual partitions.)
 #
+# Before this change, the below was true:
 # Running this test with and without "--ps-protocol" produces different
 # Qcache_not_cached results because of the following reason:
 # In normal protocol, a SELECT failing due to insufficient privileges

=== modified file 'mysql-test/include/handler.inc'
--- a/mysql-test/include/handler.inc	revid:mattias.jonsson@stripped
+++ b/mysql-test/include/handler.inc	revid:mattias.jonsson@stripped
@@ -1844,8 +1844,10 @@ DROP TABLE t1;
 --echo # Bug#13008220 HANDLER SQL STATEMENT CAN MISS TO INITIALIZE
 --echo #              FOR RANDOM READ
 --echo #
+--echo # A handler can only have one active 'cursor' at a time,
+--echo # so switching between index and/or random should restart the cursor.
 
-CREATE TABLE t1(a INT, b INT, KEY b(b));
+CREATE TABLE t1(a INT, b INT, KEY b(b), KEY ab(a, b));
 INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30);
 HANDLER t1 OPEN;
 HANDLER t1 READ b FIRST;
@@ -1868,5 +1870,13 @@ HANDLER t1 READ b NEXT;
 HANDLER t1 READ b NEXT;
 HANDLER t1 READ NEXT;
 HANDLER t1 READ b NEXT;
+HANDLER t1 READ FIRST;
+HANDLER t1 READ b PREV;
+HANDLER t1 READ b LAST;
+HANDLER t1 READ NEXT;
+HANDLER t1 READ ab FIRST;
+HANDLER t1 READ b NEXT;
+HANDLER t1 READ ab LAST;
+HANDLER t1 READ b PREV;
 HANDLER t1 CLOSE;
 DROP TABLE t1;

=== modified file 'mysql-test/r/commit_1innodb.result'
--- a/mysql-test/r/commit_1innodb.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/commit_1innodb.result	revid:mattias.jonsson@stripped
@@ -855,17 +855,6 @@ create view v1 as select * from t2;
 call p_verify_status_increment(0, 0, 0, 0);
 SUCCESS
 
-select * from v1;
-a
-8
-8
-call p_verify_status_increment(1, 0, 1, 0);
-SUCCESS
-
-commit;
-call p_verify_status_increment(1, 0, 1, 0);
-SUCCESS
-
 check table t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK

=== renamed file 'mysql-test/r/grant_cache_no_prot.result' => 'mysql-test/r/grant_cache.result'
=== removed file 'mysql-test/r/grant_cache_ps_prot.result'
--- a/mysql-test/r/grant_cache_ps_prot.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/grant_cache_ps_prot.result	1970-01-01 00:00:00 +0000
@@ -1,221 +0,0 @@
-drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
-drop database if exists mysqltest;
-set GLOBAL query_cache_size=1355776;
-reset query cache;
-flush status;
------ establish connection root -----
-show grants for current_user;
-Grants for root@localhost
-GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
-show grants;
-Grants for root@localhost
-GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
-create database if not exists mysqltest;
-create table mysqltest.t1 (a int,b int,c int);
-create table mysqltest.t2 (a int,b int,c int);
-insert into mysqltest.t1 values (1,1,1),(2,2,2);
-insert into mysqltest.t2 values (3,3,3);
-create table test.t1 (a char (10));
-insert into test.t1 values ("test.t1");
-select * from t1;
-a
-test.t1
------ establish connection root2 -----
-select * from t1;
-a	b	c
-1	1	1
-2	2	2
-select a from t1;
-a
-1
-2
-select c from t1;
-c
-1
-2
-select * from t2;
-a	b	c
-3	3	3
-select * from mysqltest.t1,test.t1;
-a	b	c	a
-1	1	1	test.t1
-2	2	2	test.t1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits%";
-Variable_name	Value
-Qcache_hits	0
-grant SELECT on mysqltest.* to mysqltest_1@localhost;
-grant SELECT on mysqltest.t1 to mysqltest_2@localhost;
-grant SELECT on test.t1 to mysqltest_2@localhost;
-grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
------ establish connection user1 (user=mysqltest_1) -----
-show grants for current_user();
-Grants for mysqltest_1@localhost
-GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
-GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	0
-select "user1";
-user1
-user1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	1
-select * from t1;
-a	b	c
-1	1	1
-2	2	2
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	1
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	1
-select a from t1 ;
-a
-1
-2
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	2
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	1
-select c from t1;
-c
-1
-2
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	3
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	1
------ establish connection unkuser (user=unkuser) -----
-show grants for current_user();
-Grants for @localhost
-GRANT USAGE ON *.* TO ''@'localhost'
------ establish connection user2 (user=mysqltest_2) -----
-select "user2";
-user2
-user2
-select * from t1;
-a	b	c
-1	1	1
-2	2	2
-select a from t1;
-a
-1
-2
-select c from t1;
-c
-1
-2
-select * from mysqltest.t1,test.t1;
-a	b	c	a
-1	1	1	test.t1
-2	2	2	test.t1
-select * from t2;
-ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	7
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	2
------ establish connection user3 (user=mysqltest_3) -----
-select "user3";
-user3
-user3
-select * from t1;
-ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
-select a from t1;
-a
-1
-2
-select c from t1;
-ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
-select * from t2;
-ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't2'
-select mysqltest.t1.c from test.t1,mysqltest.t1;
-ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	6
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	7
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	4
------ establish connection user4 (user=mysqltest_1) -----
-select "user4";
-user4
-user4
-show grants;
-Grants for mysqltest_1@localhost
-GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
-GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
-select a from t1;
-ERROR 3D000: No database selected
-select * from mysqltest.t1,test.t1;
-a	b	c	a
-1	1	1	test.t1
-2	2	2	test.t1
-select a from mysqltest.t1;
-a
-1
-2
-select a from mysqltest.t1;
-a
-1
-2
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	8
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	8
-show status like "Qcache_not_cached";
-Variable_name	Value
-Qcache_not_cached	5
------ close connections -----
------ switch to connection default -----
-set names binary;
-delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
-delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
-delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
-delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
-flush privileges;
-drop table test.t1,mysqltest.t1,mysqltest.t2;
-drop database mysqltest;
-set GLOBAL query_cache_size=default;

=== modified file 'mysql-test/r/handler_innodb.result'
--- a/mysql-test/r/handler_innodb.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/handler_innodb.result	revid:mattias.jonsson@stripped
@@ -1748,7 +1748,9 @@ DROP TABLE t1;
 # Bug#13008220 HANDLER SQL STATEMENT CAN MISS TO INITIALIZE
 #              FOR RANDOM READ
 #
-CREATE TABLE t1(a INT, b INT, KEY b(b));
+# A handler can only have one active 'cursor' at a time,
+# so switching between index and/or random should restart the cursor.
+CREATE TABLE t1(a INT, b INT, KEY b(b), KEY ab(a, b));
 INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30);
 HANDLER t1 OPEN;
 HANDLER t1 READ b FIRST;
@@ -1809,5 +1811,29 @@ a	b
 HANDLER t1 READ b NEXT;
 a	b
 1	10
+HANDLER t1 READ FIRST;
+a	b
+2	20
+HANDLER t1 READ b PREV;
+a	b
+4	40
+HANDLER t1 READ b LAST;
+a	b
+4	40
+HANDLER t1 READ NEXT;
+a	b
+2	20
+HANDLER t1 READ ab FIRST;
+a	b
+1	10
+HANDLER t1 READ b NEXT;
+a	b
+1	10
+HANDLER t1 READ ab LAST;
+a	b
+4	40
+HANDLER t1 READ b PREV;
+a	b
+4	40
 HANDLER t1 CLOSE;
 DROP TABLE t1;

=== modified file 'mysql-test/r/handler_myisam.result'
--- a/mysql-test/r/handler_myisam.result	revid:mattias.jonsson@stripped
+++ b/mysql-test/r/handler_myisam.result	revid:mattias.jonsson@stripped
@@ -1744,7 +1744,9 @@ DROP TABLE t1;
 # Bug#13008220 HANDLER SQL STATEMENT CAN MISS TO INITIALIZE
 #              FOR RANDOM READ
 #
-CREATE TABLE t1(a INT, b INT, KEY b(b));
+# A handler can only have one active 'cursor' at a time,
+# so switching between index and/or random should restart the cursor.
+CREATE TABLE t1(a INT, b INT, KEY b(b), KEY ab(a, b));
 INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30);
 HANDLER t1 OPEN;
 HANDLER t1 READ b FIRST;
@@ -1805,6 +1807,30 @@ a	b
 HANDLER t1 READ b NEXT;
 a	b
 1	10
+HANDLER t1 READ FIRST;
+a	b
+2	20
+HANDLER t1 READ b PREV;
+a	b
+4	40
+HANDLER t1 READ b LAST;
+a	b
+4	40
+HANDLER t1 READ NEXT;
+a	b
+2	20
+HANDLER t1 READ ab FIRST;
+a	b
+1	10
+HANDLER t1 READ b NEXT;
+a	b
+1	10
+HANDLER t1 READ ab LAST;
+a	b
+4	40
+HANDLER t1 READ b PREV;
+a	b
+4	40
 HANDLER t1 CLOSE;
 DROP TABLE t1;
 #

=== 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
@@ -255,8 +255,8 @@ HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	4
 HANDLER_WRITE	18
 # Should be 1 commit
-# 4 external locks (6 before WL#4443)
-# (1 ha_partition + 2 ha_innobase) x 2 (lock + unlock)
+# 4 external locks (due to pruning of locks)
+# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
 # and 18 write (1 ha_innobase + 17 internal I_S write)
 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)");
 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0");

=== 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
@@ -33,7 +33,7 @@ HANDLER_EXTERNAL_LOCK	4
 HANDLER_ROLLBACK	1
 HANDLER_WRITE	18
 # 4 locks (table + partition lock/unlock)
-# 1 commit
+# 1 rollback
 FLUSH STATUS;
 INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'),
 (3, 'First row, p3'), (4, 'First row, p4');
@@ -64,7 +64,7 @@ VARIABLE_NAME	VARIABLE_VALUE
 HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	28
 HANDLER_WRITE	18
-# Auto increment value not know until write.
+# Auto increment value is not known until write.
 # 28 locks (table + 13 partition lock/unlock)
 # 1 commit
 FLUSH STATUS;
@@ -75,7 +75,7 @@ VARIABLE_NAME	VARIABLE_VALUE
 HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	28
 HANDLER_WRITE	18
-# Auto increment value not know until write.
+# Auto increment value is not known until write.
 # 28 locks (table + 13 partition lock/unlock)
 # 1 commit
 #
@@ -325,7 +325,7 @@ DROP TABLE t3;
 #
 # Test of insert pruning with subpartitions
 #
-# I've place the varchar column before the int column for better
+# I've placed the varchar column before the int column for better
 # distribution by LINEAR KEY.
 CREATE TABLE t3
 (a int DEFAULT 10,
@@ -462,6 +462,16 @@ HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	4
 HANDLER_WRITE	18
 # 4 locks (1 table, 1 subpartition lock/unlock)
+DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
+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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 subpartition lock/unlock)
 SELECT * FROM t3;
 a	b	c	d	e
 -1	ZZZzzzz	yyyYYY	0	Default-filler.filler.filler.
@@ -516,6 +526,14 @@ HANDLER_EXTERNAL_LOCK	17
 HANDLER_WRITE	17
 # 17 locks (1 table, 16 partitions lock)
 # No further locks/unlocks until UNLOCK TABLES.
+DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
+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;
 DELETE FROM t3
 WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
@@ -642,8 +660,6 @@ CREATE TABLE t3
 b char(10),
 PRIMARY KEY (a, b))
 PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
-FLUSH STATUS;
-INSERT INTO t3 (a) VALUES (NULL);
 SHOW CREATE TABLE t3;
 Table	Create Table
 t3	CREATE TABLE `t3` (
@@ -653,6 +669,18 @@ t3	CREATE TABLE `t3` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 /*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
 PARTITIONS 3 */
+FLUSH STATUS;
+SET TIMESTAMP = 1234567890;
+INSERT INTO t3 (a) VALUES (NULL);
+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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
 VARIABLE_NAME	VARIABLE_VALUE
@@ -699,9 +727,14 @@ HANDLER_READ_KEY	1
 HANDLER_UPDATE	1
 HANDLER_WRITE	18
 # 4 locks (1 table, 1 partition lock/unlock)
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-5
+SELECT * FROM t3;
+a	b
+0000-00-00 00:00:00	
+2009-02-14 02:31:30	
+2011-01-01 00:00:00	, DUP_KEY
+2011-01-01 00:00:03	
+2011-01-01 00:00:01	
+2011-01-01 00:00:02	
 DROP TABLE t3;
 CREATE TABLE t3
 (a timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
@@ -718,6 +751,7 @@ t3	CREATE TABLE `t3` (
 /*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
 PARTITIONS 3 */
 FLUSH STATUS;
+SET TIMESTAMP = 1234567890;
 INSERT INTO t3 (a) VALUES (NULL);
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
 WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
@@ -726,6 +760,7 @@ HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	4
 HANDLER_WRITE	18
 # 4 locks (1 table, 1 partition lock/unlock)
+# TODO Fix the bug in INSERT INTO t3 VALUES () and enable the test
 FLUSH STATUS;
 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
@@ -754,16 +789,25 @@ HANDLER_EXTERNAL_LOCK	6
 HANDLER_WRITE	19
 # 6 locks (1 table, 2 partition lock/unlock)
 FLUSH STATUS;
+SET TIMESTAMP = 1234567892;
 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
 ON DUPLICATE KEY UPDATE b = CONCAT(b, ", DUP_KEY");
-# May change partition, so gives different UPDATE/DELETE/WRITE count
-SHOW STATUS LIKE 'Handler_external_lock';
-Variable_name	Value
-Handler_external_lock	8
+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	8
+HANDLER_READ_KEY	1
+HANDLER_WRITE	19
 # 8 locks (1 table, 3 partition lock/unlock)
-SELECT COUNT(*) FROM t3;
-COUNT(*)
-5
+SELECT * FROM t3;
+a	b
+2009-02-14 02:31:30	
+2011-01-01 00:00:03	
+2011-01-01 00:00:01	
+2009-02-14 02:31:32	, DUP_KEY
+2011-01-01 00:00:02	
 DROP TABLE t3;
 CREATE TABLE t3
 (a timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
@@ -996,7 +1040,7 @@ HANDLER_WRITE	18
 # 1 update
 # 1 commit
 #
-# Test of failed update
+# Test of insert on duplicate key with failed update
 #
 FLUSH STATUS;
 INSERT INTO t1 VALUES (78, "No duplicate")
@@ -1030,7 +1074,7 @@ HANDLER_WRITE	18
 # 1 update
 # 1 rollback
 #
-# Test of update to different partition
+# Test of insert on duplicate key with update to different partition
 #
 FLUSH STATUS;
 INSERT INTO t1 VALUES (104, "No duplicate")
@@ -1075,6 +1119,10 @@ HANDLER_WRITE	18
 # 28 lock (1 table + 13 partition lock/unlock)
 # 1 write
 # 1 commit
+#
+# Test of insert on duplicate key with failed update to different
+# partition
+#
 FLUSH STATUS;
 INSERT INTO t1 VALUES (104, "No duplicate 104 + 1")
 ON DUPLICATE KEY UPDATE a = a + 1;
@@ -1342,7 +1390,6 @@ HANDLER_WRITE	17
 # Test pruning of non static values
 # They will need to lock all partitions, but will allow pruning
 # due to a second pruning call in optimize.
-# TODO: Partition wise joins ?
 #
 CREATE TABLE t3 (a INT);
 INSERT INTO t3 VALUES (1);
@@ -1392,7 +1439,6 @@ HANDLER_EXTERNAL_LOCK	30
 HANDLER_WRITE	17
 # 30 locks (2 table + 13 partitions lock/unlock)
 FLUSH STATUS;
-# EXPLAIN does not evaluate SUBQUERIES, so pruning will not show!
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = (SELECT a FROM t3);
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	p1	const	PRIMARY	PRIMARY	4	const	1	NULL
@@ -2420,12 +2466,6 @@ DROP TABLE t3;
 #
 # Test Stored procedures
 #
-DROP PROCEDURE IF EXISTS sp_insert;
-DROP PROCEDURE IF EXISTS sp_insert_partition;
-DROP PROCEDURE IF EXISTS sp_select_all;
-DROP PROCEDURE IF EXISTS sp_select_exact;
-DROP PROCEDURE IF EXISTS sp_select_partition;
-DROP PROCEDURE IF EXISTS sp_select_range;
 CREATE PROCEDURE sp_insert(a INT, b CHAR(16))
 INSERT INTO test.t1 VALUES (a, b);
 CREATE PROCEDURE sp_insert_partition(p CHAR(16), a INT, b CHAR(16))
@@ -2434,6 +2474,7 @@ SET @str = CONCAT("INSERT INTO test.t1 P
 SET @x = a, @y = b;
 PREPARE stmt FROM @str;
 EXECUTE stmt USING @x, @y;
+DEALLOCATE PREPARE stmt;
 END|
 CREATE PROCEDURE sp_select_all()
 SELECT * FROM test.t1;
@@ -2444,6 +2485,7 @@ BEGIN
 SET @str = CONCAT("SELECT * FROM test.t1 PARTITION(", p, ")");
 PREPARE stmt FROM @str;
 EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
 END|
 CREATE PROCEDURE sp_select_range(x INT, y INT)
 SELECT * FROM test.t1 WHERE a between x and y;
@@ -2866,6 +2908,9 @@ a	b
 4	First row, p4, t1.b:First row, p4
 #
 # Test triggers
+# Tables used in triggers cannot be pruned for locks.
+# Tables with triggers cannot be pruned for locks if
+# BEFORE INSERT/UPDATE trigger exists.
 #
 CREATE TABLE t3
 (old_a int,
@@ -3009,6 +3054,7 @@ VARIABLE_NAME	VARIABLE_VALUE
 HANDLER_COMMIT	1
 HANDLER_EXTERNAL_LOCK	52
 HANDLER_WRITE	17
+# No pruning possible, due to BEFORE UPDATE trigger
 # 52 locks (3 tables, 13 + 5 + 5 partition lock/unlock)
 # t1, before update, after update
 SELECT * FROM t1 WHERE a = 3;
@@ -3107,19 +3153,9 @@ INSERT INTO t2 VALUES (3, "First row, p3
 CREATE TRIGGER t2_before_insert BEFORE INSERT
 ON t2 FOR EACH ROW
 SET NEW.a = NEW.a + 1;
-# Test that one cannot update a row in BEFORE UPDATE
-CREATE TRIGGER t2_before_update BEFORE UPDATE
-ON t2 FOR EACH ROW
-SET OLD.a = OLD.a - 1;
-ERROR HY000: Updating of OLD row is not allowed in trigger
 CREATE TRIGGER t2_before_update BEFORE UPDATE
 ON t2 FOR EACH ROW
 SET NEW.a = NEW.a - 1;
-# Test that one cannot update a row in BEFORE DELETE
-CREATE TRIGGER t2_before_delete BEFORE DELETE
-ON t2 FOR EACH ROW
-SET OLD.a = OLD.a - 1;
-ERROR HY000: Updating of OLD row is not allowed in trigger
 FLUSH STATUS;
 INSERT INTO t2 VALUES (1, "Second row, p1");
 SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
@@ -3249,6 +3285,125 @@ HANDLER_WRITE	17
 SELECT @x;
 @x
 Five
+#
+# SET is not supported by WL#4443 !!!
+# Test of SET (eg. SELECT only setting an internal variable from
+# the returning value)
+#
+FLUSH STATUS;
+SET @x = (SELECT a FROM t1 WHERE a = 5);
+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	8
+HANDLER_READ_FIRST	1
+HANDLER_READ_KEY	1
+HANDLER_READ_RND_NEXT	3
+HANDLER_WRITE	17
+# 8 locks (1 table + 3 partitions lock/unlock)
+SELECT @x;
+@x
+5
+FLUSH STATUS;
+SET @y = (SELECT @x:= b FROM t1 WHERE a = 5);
+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	8
+HANDLER_READ_FIRST	1
+HANDLER_READ_KEY	1
+HANDLER_READ_RND_NEXT	3
+HANDLER_WRITE	17
+# 8 locks (1 table + 3 partitions lock/unlock)
+SELECT @x, @y;
+@x	@y
+Five	Five
+FLUSH STATUS;
+SET @y = (SELECT @x:= b FROM t1 WHERE a = 5 or a = 1 ORDER BY b LIMIT 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	8
+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, @y;
+@x	@y
+Five	Five
+#
+# LOAD DATA is not supported by WL#4443 !!!
+#
+FLUSH STATUS;
+SELECT * FROM t1 WHERE a IN (1, 4)
+INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.part1';
+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	4
+HANDLER_READ_FIRST	1
+HANDLER_READ_KEY	1
+HANDLER_READ_RND_NEXT	3
+HANDLER_WRITE	17
+# 4 locks (1 table + 1 partitions lock/unlock)
+DELETE FROM t1 WHERE a IN (1, 4);
+SELECT * FROM t1 ORDER BY a, b;
+a	b
+0	Zero
+2	Two
+3	Three
+5	Five
+6	Six
+FLUSH STATUS;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1;
+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	8
+HANDLER_WRITE	19
+# 8 locks (1 table + 3 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a, b;
+a	b
+0	Zero
+1	One
+2	Two
+3	Three
+4	Four
+5	Five
+6	Six
+DELETE FROM t1 WHERE a IN (1, 4);
+SELECT * FROM t1 ORDER BY a, b;
+a	b
+0	Zero
+2	Two
+3	Three
+5	Five
+6	Six
+# It is possible to avoid locking with explicit partitioning selection!
+FLUSH STATUS;
+LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1 PARTITION(p1);
+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	4
+HANDLER_WRITE	19
+# 4 locks (1 table + 1 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a, b;
+a	b
+0	Zero
+1	One
+2	Two
+3	Three
+4	Four
+5	Five
+6	Six
 DROP TABLE t1;
 #
 # Test EXCHANGE PARTITION to only lock exchanged partition
@@ -3381,6 +3536,24 @@ HANDLER_READ_RND_NEXT	4
 HANDLER_WRITE	18
 # 12 locks (3 tables + 3 partitions) x lock/unlock
 # I.e. No lock pruning possible
+FLUSH STATUS;
+INSERT INTO t2 VALUES (1 + (SELECT a FROM t1),
+CONCAT("subq: ", (SELECT b FROM t1)));
+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	12
+HANDLER_READ_FIRST	2
+HANDLER_READ_KEY	2
+HANDLER_READ_RND_NEXT	4
+HANDLER_WRITE	18
+# 12 locks (3 tables + 3 partitions) x lock/unlock
+# I.e. No lock pruning possible
+SELECT * FROM t2;
+a	b
+1	test 1
+2	subq: test 1
 DROP TABLE t1, t2;
 CREATE TABLE t1 (a INT, b INT);
 CREATE TABLE t2 (a INT, b INT) PARTITION BY HASH (a) PARTITIONS 3;

=== 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
@@ -2748,9 +2748,9 @@ show status like 'Handler_external_lock'
 Variable_name	Value
 Handler_external_lock	6
 # This will only scan partition p1
-explain extended update t1 set a=100 where a+1=5+1;
+explain extended update t1 set a=3 where a=4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	Using where; Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using temporary
 explain partitions update t1 set a=3 where a=4;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using temporary
@@ -2763,7 +2763,7 @@ show status like 'Handler_external_lock'
 Variable_name	Value
 Handler_external_lock	6
 # This will only scan partition p1 but with a LIMIT
-explain extended update t1 set a=3 where a=4;
+explain extended update t1 set a=4 where a=3 LIMIT 2;
 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; Using temporary
 explain partitions update t1 set a=4 where a=3 LIMIT 2;

=== renamed file 'mysql-test/t/grant_cache_no_prot.test' => 'mysql-test/t/grant_cache.test'
--- a/mysql-test/t/grant_cache_no_prot.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/grant_cache.test	revid:mattias.jonsson@stripped
@@ -1,11 +1,7 @@
-#################### t/grant_cache_no_prot.test ###################
+#################### t/grant_cache.test ###################
 #
 # Test grants with query cache to be run when mysqltest was started
-# without any "--<whatever>-protocol".
-#
-# Last update:
-# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc
-#               - Create this test as non "--<whatever>-protocol" variant.
+# without "--{view|sp|cursor}-protocol".
 #
 
 # We cannot run on embedded server because we use multiple sessions.
@@ -14,11 +10,10 @@
 --source include/have_query_cache.inc
 
 # The file with expected results fits only to a run without
-# ps-protocol/sp-protocol/cursor-protocol/view-protocol.
-if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL
-            + $VIEW_PROTOCOL > 0`)
+# sp-protocol/cursor-protocol/view-protocol.
+if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0`)
 {
-   --skip Need normal protocol
+   --skip Need normal or ps protocol
 }
 
 # The main testing script

=== removed file 'mysql-test/t/grant_cache_ps_prot.test'
--- a/mysql-test/t/grant_cache_ps_prot.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/grant_cache_ps_prot.test	1970-01-01 00:00:00 +0000
@@ -1,24 +0,0 @@
-#################### t/grant_cache_ps_prot.test ##################
-#
-# Test grants with query cache to be run when mysqltest was
-# started with the option "--ps-protocol".
-#
-# Last update:
-# 2007-05-03 ML - Move t/grant_cache.test to include/grant_cache.inc
-#               - Create this test as "--ps-protocol" only variant.
-#
-
-# We cannot run on embedded server because we use multiple sessions.
---source include/not_embedded.inc
-
---source include/have_query_cache.inc
-
-# The file with expected results fits only to a run with "--ps-protocol".
-if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0
-        OR $PS_PROTOCOL = 0`)
-{
-   --skip Need ps-protocol
-}
-
-# The main testing script
---source include/grant_cache.inc

=== 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
@@ -137,8 +137,8 @@ FLUSH STATUS;
 INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)");
 eval $get_handler_status_counts;
 --echo # Should be 1 commit
---echo # 4 external locks (6 before WL#4443)
---echo # (1 ha_partition + 2 ha_innobase) x 2 (lock + unlock)
+--echo # 4 external locks (due to pruning of locks)
+--echo # (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
 --echo # and 18 write (1 ha_innobase + 17 internal I_S write)
 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)");
 INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0");

=== 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
@@ -41,7 +41,7 @@ FLUSH STATUS;
 INSERT INTO t1 VALUES (1, 'First row, duplicate');
 eval $get_handler_status_counts;
 --echo # 4 locks (table + partition lock/unlock)
---echo # 1 commit
+--echo # 1 rollback
 FLUSH STATUS;
 INSERT INTO t1 VALUES (0, 'First row, p0'), (2, 'First row, p2'),
                       (3, 'First row, p3'), (4, 'First row, p4');
@@ -57,14 +57,14 @@ eval $get_handler_status_counts;
 FLUSH STATUS;
 INSERT INTO t2 VALUES (NULL, 'First auto-inc row');
 eval $get_handler_status_counts;
---echo # Auto increment value not know until write.
+--echo # Auto increment value is not known until write.
 --echo # 28 locks (table + 13 partition lock/unlock)
 --echo # 1 commit
 
 FLUSH STATUS;
 INSERT INTO t2 (b) VALUES ('Second auto-inc row');
 eval $get_handler_status_counts;
---echo # Auto increment value not know until write.
+--echo # Auto increment value is not known until write.
 --echo # 28 locks (table + 13 partition lock/unlock)
 --echo # 1 commit
 
@@ -174,7 +174,7 @@ DROP TABLE t3;
 --echo #
 --echo # Test of insert pruning with subpartitions
 --echo #
---echo # I've place the varchar column before the int column for better
+--echo # I've placed the varchar column before the int column for better
 --echo # distribution by LINEAR KEY.
 CREATE TABLE t3
 (a int DEFAULT 10,
@@ -249,6 +249,11 @@ FLUSH STATUS;
 INSERT INTO t3 (a, b, c, d) VALUES (1, "Full part", "Full subpart", 1);
 eval $get_handler_status_counts;
 --echo # 4 locks (1 table, 1 subpartition lock/unlock)
+DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
 
 --sorted_result
 SELECT * FROM t3;
@@ -271,6 +276,10 @@ 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.
+DELETE FROM t3 WHERE a = 10 AND b = 'Default' AND c = 'Default' AND D = 9;
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
+eval $get_handler_status_counts;
 FLUSH STATUS;
 DELETE FROM t3
 WHERE a = 10 AND b = "Default" AND c = "Default" AND d = 9;
@@ -323,9 +332,14 @@ CREATE TABLE t3
  b char(10),
  PRIMARY KEY (a, b))
 PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
 FLUSH STATUS;
+SET TIMESTAMP = 1234567890;
 INSERT INTO t3 (a) VALUES (NULL);
-SHOW CREATE TABLE t3;
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES ();
 eval $get_handler_status_counts;
 --echo # 4 locks (1 table, 1 partition lock/unlock)
 FLUSH STATUS;
@@ -345,7 +359,7 @@ INSERT INTO t3 (a) VALUES ('2011-01-01 0
 ON DUPLICATE KEY UPDATE b = CONCAT(b, ", DUP_KEY");
 eval $get_handler_status_counts;
 --echo # 4 locks (1 table, 1 partition lock/unlock)
-SELECT COUNT(*) FROM t3;
+SELECT * FROM t3;
 DROP TABLE t3;
 CREATE TABLE t3
 (a timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
@@ -354,9 +368,16 @@ CREATE TABLE t3
 PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
 SHOW CREATE TABLE t3;
 FLUSH STATUS;
+SET TIMESTAMP = 1234567890;
 INSERT INTO t3 (a) VALUES (NULL);
 eval $get_handler_status_counts;
 --echo # 4 locks (1 table, 1 partition lock/unlock)
+--echo # TODO Fix the bug in INSERT INTO t3 VALUES () and enable the test
+#FLUSH STATUS;
+#SET TIMESTAMP = 1234567891;
+#INSERT INTO t3 VALUES ();
+#eval $get_handler_status_counts;
+#--echo # 4 locks (1 table, 1 partition lock/unlock)
 FLUSH STATUS;
 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
 eval $get_handler_status_counts;
@@ -370,13 +391,14 @@ INSERT INTO t3 (a) VALUES ('2011-01-01 0
 eval $get_handler_status_counts;
 --echo # 6 locks (1 table, 2 partition lock/unlock)
 FLUSH STATUS;
+SET TIMESTAMP = 1234567892;
 INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
 ON DUPLICATE KEY UPDATE b = CONCAT(b, ", DUP_KEY");
-#eval $get_handler_status_counts;
---echo # May change partition, so gives different UPDATE/DELETE/WRITE count
-SHOW STATUS LIKE 'Handler_external_lock';
+eval $get_handler_status_counts;
+#--echo # May change partition, so gives different UPDATE/DELETE/WRITE count
+#SHOW STATUS LIKE 'Handler_external_lock';
 --echo # 8 locks (1 table, 3 partition lock/unlock)
-SELECT COUNT(*) FROM t3;
+SELECT * FROM t3;
 DROP TABLE t3;
 CREATE TABLE t3
 (a timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
@@ -503,7 +525,7 @@ eval $get_handler_status_counts;
 --echo # 1 commit
 
 --echo #
---echo # Test of failed update
+--echo # Test of insert on duplicate key with failed update
 --echo #
 FLUSH STATUS;
 INSERT INTO t1 VALUES (78, "No duplicate")
@@ -526,7 +548,7 @@ eval $get_handler_status_counts;
 --echo # 1 rollback
 
 --echo #
---echo # Test of update to different partition
+--echo # Test of insert on duplicate key with update to different partition
 --echo #
 FLUSH STATUS;
 INSERT INTO t1 VALUES (104, "No duplicate")
@@ -554,6 +576,11 @@ eval $get_handler_status_counts;
 --echo # 28 lock (1 table + 13 partition lock/unlock)
 --echo # 1 write
 --echo # 1 commit
+
+--echo #
+--echo # Test of insert on duplicate key with failed update to different
+--echo # partition
+--echo #
 FLUSH STATUS;
 --error ER_DUP_ENTRY
 INSERT INTO t1 VALUES (104, "No duplicate 104 + 1")
@@ -673,7 +700,6 @@ eval $get_handler_status_counts;
 --echo # Test pruning of non static values
 --echo # They will need to lock all partitions, but will allow pruning
 --echo # due to a second pruning call in optimize.
---echo # TODO: Partition wise joins ?
 --echo #
 CREATE TABLE t3 (a INT);
 INSERT INTO t3 VALUES (1);
@@ -695,7 +721,6 @@ EXPLAIN PARTITIONS SELECT t1.a, t1.b FRO
 eval $get_handler_status_counts;
 --echo # 30 locks (2 table + 13 partitions lock/unlock)
 FLUSH STATUS;
---echo # EXPLAIN does not evaluate SUBQUERIES, so pruning will not show!
 EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = (SELECT a FROM t3);
 eval $get_handler_status_counts;
 --echo # 30 locks (2 table + 13 partitions lock/unlock)
@@ -1105,14 +1130,6 @@ DROP TABLE t3;
 --echo #
 --echo # Test Stored procedures
 --echo #
---disable_warnings
-DROP PROCEDURE IF EXISTS sp_insert;
-DROP PROCEDURE IF EXISTS sp_insert_partition;
-DROP PROCEDURE IF EXISTS sp_select_all;
-DROP PROCEDURE IF EXISTS sp_select_exact;
-DROP PROCEDURE IF EXISTS sp_select_partition;
-DROP PROCEDURE IF EXISTS sp_select_range;
---enable_warnings
 CREATE PROCEDURE sp_insert(a INT, b CHAR(16))
   INSERT INTO test.t1 VALUES (a, b);
 
@@ -1123,6 +1140,7 @@ BEGIN
   SET @x = a, @y = b;
   PREPARE stmt FROM @str;
   EXECUTE stmt USING @x, @y;
+  DEALLOCATE PREPARE stmt;
 END|
 delimiter ;|
 
@@ -1138,6 +1156,7 @@ BEGIN
   SET @str = CONCAT("SELECT * FROM test.t1 PARTITION(", p, ")");
   PREPARE stmt FROM @str;
   EXECUTE stmt;
+  DEALLOCATE PREPARE stmt;
 END|
 delimiter ;|
 
@@ -1299,6 +1318,9 @@ SELECT * FROM t2 ORDER BY a;
 
 --echo #
 --echo # Test triggers
+--echo # Tables used in triggers cannot be pruned for locks.
+--echo # Tables with triggers cannot be pruned for locks if
+--echo # BEFORE INSERT/UPDATE trigger exists.
 --echo #
 CREATE TABLE t3
 (old_a int,
@@ -1387,6 +1409,7 @@ SELECT * FROM t1 WHERE a = 0;
 FLUSH STATUS;
 EXPLAIN PARTITIONS UPDATE t1 SET b = CONCAT(b, ", UPDATED2") WHERE a = 3;
 eval $get_handler_status_counts;
+--echo # No pruning possible, due to BEFORE UPDATE trigger
 --echo # 52 locks (3 tables, 13 + 5 + 5 partition lock/unlock)
 --echo # t1, before update, after update
 SELECT * FROM t1 WHERE a = 3;
@@ -1435,22 +1458,10 @@ CREATE TRIGGER t2_before_insert BEFORE I
 ON t2 FOR EACH ROW
 SET NEW.a = NEW.a + 1;
 
---echo # Test that one cannot update a row in BEFORE UPDATE
---error ER_TRG_CANT_CHANGE_ROW
-CREATE TRIGGER t2_before_update BEFORE UPDATE
-ON t2 FOR EACH ROW
-SET OLD.a = OLD.a - 1;
-
 CREATE TRIGGER t2_before_update BEFORE UPDATE
 ON t2 FOR EACH ROW
 SET NEW.a = NEW.a - 1;
 
---echo # Test that one cannot update a row in BEFORE DELETE
---error ER_TRG_CANT_CHANGE_ROW
-CREATE TRIGGER t2_before_delete BEFORE DELETE
-ON t2 FOR EACH ROW
-SET OLD.a = OLD.a - 1;
-
 FLUSH STATUS;
 INSERT INTO t2 VALUES (1, "Second row, p1");
 eval $get_handler_status_counts;
@@ -1494,6 +1505,55 @@ eval $get_handler_status_counts;
 --echo # 8 locks (1 table + 3 partitions lock/unlock)
 SELECT @x;
 
+--echo #
+--echo # SET is not supported by WL#4443 !!!
+--echo # Test of SET (eg. SELECT only setting an internal variable from
+--echo # the returning value)
+--echo #
+FLUSH STATUS;
+SET @x = (SELECT a FROM t1 WHERE a = 5);
+eval $get_handler_status_counts;
+--echo # 8 locks (1 table + 3 partitions lock/unlock)
+SELECT @x;
+FLUSH STATUS;
+SET @y = (SELECT @x:= b FROM t1 WHERE a = 5);
+eval $get_handler_status_counts;
+--echo # 8 locks (1 table + 3 partitions lock/unlock)
+SELECT @x, @y;
+FLUSH STATUS;
+SET @y = (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, @y;
+
+--echo #
+--echo # LOAD DATA is not supported by WL#4443 !!!
+--echo #
+FLUSH STATUS;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval SELECT * FROM t1 WHERE a IN (1, 4)
+INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1.part1';
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table + 1 partitions lock/unlock)
+DELETE FROM t1 WHERE a IN (1, 4);
+SELECT * FROM t1 ORDER BY a, b;
+FLUSH STATUS;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1;
+eval $get_handler_status_counts;
+--echo # 8 locks (1 table + 3 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a, b;
+DELETE FROM t1 WHERE a IN (1, 4);
+SELECT * FROM t1 ORDER BY a, b;
+--echo # It is possible to avoid locking with explicit partitioning selection!
+FLUSH STATUS;
+--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
+eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1.part1' INTO TABLE t1 PARTITION(p1);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table + 1 partitions lock/unlock)
+SELECT * FROM t1 ORDER BY a, b;
+--remove_file $MYSQLTEST_VARDIR/tmp/t1.part1
+
 DROP TABLE t1;
 
 --echo #
@@ -1561,6 +1621,14 @@ INSERT INTO t2 VALUES ((SELECT a FROM t1
 eval $get_handler_status_counts;
 --echo # 12 locks (3 tables + 3 partitions) x lock/unlock
 --echo # I.e. No lock pruning possible
+FLUSH STATUS;
+INSERT INTO t2 VALUES (1 + (SELECT a FROM t1),
+                       CONCAT("subq: ", (SELECT b FROM t1)));
+eval $get_handler_status_counts;
+--echo # 12 locks (3 tables + 3 partitions) x lock/unlock
+--echo # I.e. No lock pruning possible
+--sorted_result
+SELECT * FROM t2;
 DROP TABLE t1, t2;
 CREATE TABLE t1 (a INT, b INT);
 CREATE TABLE t2 (a INT, b INT) PARTITION BY HASH (a) PARTITIONS 3;

=== modified file 'mysql-test/t/partition_pruning.test'
--- a/mysql-test/t/partition_pruning.test	revid:mattias.jonsson@stripped
+++ b/mysql-test/t/partition_pruning.test	revid:mattias.jonsson@stripped
@@ -954,8 +954,8 @@ show status like 'Handler_read_rnd_next'
 show status like 'Handler_external_lock';
 
 --echo # This will only scan partition p1
---eval explain extended $q
 --let $q = update t1 set a=3 where a=4
+--eval explain extended $q
 --eval explain partitions $q
 flush status;
 --eval $q
@@ -963,8 +963,8 @@ show status like 'Handler_read_rnd_next'
 show status like 'Handler_external_lock';
 
 --echo # This will only scan partition p1 but with a LIMIT
---eval explain extended $q
 --let $q = update t1 set a=4 where a=3 LIMIT 2
+--eval explain extended $q
 --eval explain partitions $q
 flush status;
 --eval $q

=== modified file 'mysys/my_bitmap.c'
--- a/mysys/my_bitmap.c	revid:mattias.jonsson@stripped
+++ b/mysys/my_bitmap.c	revid:mattias.jonsson@stripped
@@ -555,10 +555,20 @@ uint bitmap_get_first_set(const MY_BITMA
 }
 
 
+/**
+  Get the next set bit.
+
+  @param  map         Bitmap
+  @param  bitmap_bit  Bit to start search from
+
+  @return Index to first bit set after bitmap_bit
+*/
+
 uint bitmap_get_next_set(const MY_BITMAP *map, uint bitmap_bit)
 {
-  uint word_pos;
-  uint32 first_word;
+  uint word_pos, byte_to_mask, i;
+  my_bitmap_map first_word;
+  unsigned char *ptr= (unsigned char*) &first_word;
   my_bitmap_map *data_ptr, *end= map->last_word_ptr;
 
   DBUG_ASSERT(map->bitmap);
@@ -569,7 +579,14 @@ uint bitmap_get_next_set(const MY_BITMAP
     return MY_BIT_NONE;
   word_pos= bitmap_bit / 32;
   data_ptr= map->bitmap + word_pos;
-  first_word= *data_ptr & (0xFFFFFFFF << (bitmap_bit % 32));
+  first_word= *data_ptr;
+
+  /* Mask out previous bits */
+  byte_to_mask= (bitmap_bit % 32) / 8;
+  for (i= 0; i < byte_to_mask; i++)
+    ptr[i]= 0;
+  ptr[byte_to_mask]&= 0xFFU << (bitmap_bit & 7);
+
   if (data_ptr == end)
     return get_first_set(first_word & ~map->last_word_mask, word_pos);
    

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	revid:mattias.jonsson@stripped
+++ b/sql/ha_partition.cc	revid:mattias.jonsson@stripped
@@ -2950,15 +2950,24 @@ bool ha_partition::init_partition_bitmap
   DBUG_ENTER("ha_partition::init_partition_bitmaps");
   /* Initialize the bitmap we use to minimize ha_start_bulk_insert calls */
   if (bitmap_init(&m_bulk_insert_started, NULL, m_tot_parts + 1, FALSE))
-    goto err;
+    DBUG_RETURN(true);
   bitmap_clear_all(&m_bulk_insert_started);
+
   /* Initialize the bitmap we use to keep track of locked partitions */
   if (bitmap_init(&m_locked_partitions, NULL, m_tot_parts, FALSE))
-    goto err;
+  {
+    bitmap_free(&m_bulk_insert_started);
+    DBUG_RETURN(true);
+  }
   bitmap_clear_all(&m_locked_partitions);
+
   /* Initialize the bitmap we use to keep track of started partitions */
   if (bitmap_init(&m_started_partitions, NULL, m_tot_parts, FALSE))
-    goto err;
+  {
+    bitmap_free(&m_bulk_insert_started);
+    bitmap_free(&m_locked_partitions);
+    DBUG_RETURN(true);
+  }
   bitmap_clear_all(&m_started_partitions);
 
   /* Initialize the bitmap for read/lock_partitions */
@@ -2966,13 +2975,14 @@ bool ha_partition::init_partition_bitmap
   {
     DBUG_ASSERT(!m_clone_mem_root);
     if (m_part_info->set_partition_bitmaps(NULL))
-      goto err;
+    {
+      bitmap_free(&m_bulk_insert_started);
+      bitmap_free(&m_locked_partitions);
+      bitmap_free(&m_started_partitions);
+      DBUG_RETURN(true);
+    }
   }
   DBUG_RETURN(false);
-
-err:
-  free_partition_bitmaps();
-  DBUG_RETURN(true);
 }
 
 
@@ -3379,7 +3389,6 @@ int ha_partition::external_lock(THD *thd
       leave it as is after unlocking to be able to prune ::reset() calls.
     */
     DBUG_ASSERT(bitmap_is_clear_all(&m_started_partitions));
-    bitmap_clear_all(&m_started_partitions);
     used_partitions= &(m_part_info->lock_partitions);
   }
   first_used_partition= bitmap_get_first_set(used_partitions);

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_base.cc	revid:mattias.jonsson@stripped
@@ -5728,8 +5728,8 @@ err:
                         MYSQL_LOCK_IGNORE_FLUSH - open table even if someone has
                         done a flush on it.
 
-  @retval FALSE - ok
-  @retval TRUE  - error
+  @retval false - ok
+  @retval true  - error
 
   @note
     This is to be used on prepare stage when you don't read any
@@ -9031,7 +9031,7 @@ fill_record_n_invoke_before_triggers(THD
   @param thd           thread handler
   @param ptr           pointer on pointer to record
   @param values        list of fields
-  @param ignore_errors TRUE if we should ignore errors
+  @param ignore_errors True if we should ignore errors
   @param bitmap        Bitmap over fields to fill
 
   @note fill_record() may set table->auto_increment_field_not_null and a

=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_delete.cc	revid:mattias.jonsson@stripped
@@ -560,8 +560,8 @@ extern "C" int refpos_order_cmp(const vo
   @param      thd          Thread context.
   @param[out] table_count  Number of tables to be deleted from.
 
-  @retval FALST - success.
-  @retval TRUE  - error.
+  @retval false - success.
+  @retval true  - error.
 */
 
 int mysql_multi_delete_prepare(THD *thd, uint *table_count)

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_insert.cc	revid:mattias.jonsson@stripped
@@ -822,7 +822,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
         goto exit_without_my_ok;
       }
       /* Also clears all bits. */
-      if (bitmap_init(&used_partitions, bitmap_buf, num_partitions, FALSE))
+      if (bitmap_init(&used_partitions, bitmap_buf, num_partitions, false))
       {
         mem_alloc_error(bitmap_bytes);   /* Cannot happen, due to pre-alloc */
         goto exit_without_my_ok;

=== modified file 'sql/sql_partition_admin.cc'
--- a/sql/sql_partition_admin.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_partition_admin.cc	revid:mattias.jonsson@stripped
@@ -541,12 +541,12 @@ bool Sql_cmd_alter_table_exchange_partit
   partition_name= alter_info->partition_names.head();
   if (table_list->table->part_info->
         set_named_partition_bitmap(partition_name, strlen(partition_name)))
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(true);
 
   if (lock_tables(thd, table_list, table_counter, 0))
   {
     open_and_lock_tables_cleanup(thd, mdl_savepoint);
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(true);
   }
 
   /*
@@ -557,7 +557,7 @@ bool Sql_cmd_alter_table_exchange_partit
   {
     my_error(ER_PARTITION_EXCHANGE_FOREIGN_KEY, MYF(0),
              swap_table->s->table_name.str);
-    DBUG_RETURN(TRUE);
+    DBUG_RETURN(true);
   }
 
   table_hton= swap_table->file->ht;
@@ -780,8 +780,6 @@ bool Sql_cmd_alter_table_truncate_partit
     DBUG_RETURN(TRUE);
 
   mdl_savepoint= thd->mdl_context.mdl_savepoint();
-  //if (open_tables(thd, &first_table, &table_counter, 0,
-                  //&alter_prelocking_strategy))
   if (open_tables(thd, &first_table, &table_counter, 0))
   {
     open_and_lock_tables_cleanup(thd, mdl_savepoint);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_select.cc	revid:mattias.jonsson@stripped
@@ -1027,7 +1027,7 @@ mysql_prepare_select(THD *thd,
                            conds, og_num, order, group, having, proc_param,
                            select_lex, unit);
         if (err)
-          DBUG_RETURN(TRUE);
+          DBUG_RETURN(true);
       }
     }
     *free_join= 0;
@@ -1043,7 +1043,7 @@ mysql_prepare_select(THD *thd,
                        conds, og_num, order, group, having, proc_param,
                        select_lex, unit);
     if (err)
-      DBUG_RETURN(TRUE);
+      DBUG_RETURN(true);
   }
 
   DBUG_RETURN(err);
@@ -1057,10 +1057,9 @@ mysql_prepare_select(THD *thd,
   @param select_lex           the only SELECT_LEX of this query
   @param free_join            if join should be freed
 
-  @retval
-    FALSE  success
-  @retval
-    TRUE   an error
+  @return Operation status
+    @retval false  success
+    @retval true   an error
 
   @note tables must be opened and locked before calling mysql_execute_select.
 */
@@ -1147,9 +1146,9 @@ err:
                               failure
 
   @retval
-    FALSE  success
+    false  success
   @retval
-    TRUE   an error
+    true   an error
 */
 
 bool

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (mattias.jonsson:3799 to 3800) WL#4443Mattias Jonsson7 Jun