List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:January 7 2012 1:28am
Subject:bzr push into mysql-trunk branch (mattias.jonsson:3705 to 3706) WL#4443
View as plain text  
 3706 Mattias Jonsson	2012-01-07
      WL#4443
      
      Fixed the check for insert fields in partitioning
      expressions.
      
      Added copy of default values if needed.
      
      Added checks and test for TIMESTAMP.

    modified:
      mysql-test/r/partition_locking.result
      mysql-test/t/partition_locking.test
      sql/field_conv.cc
      sql/partition_info.cc
      sql/partition_info.h
      sql/sql_base.cc
      sql/sql_insert.cc
 3705 Mattias Jonsson	2012-01-05
      WL#4443
      
      Improved pruning of inserts, now only copying the fields
      belonging to the partitioning expressions when checking
      for used partiitons.
      
      Using bitmap instead of looping through all fields when
      checking if given fields are used in the partitioning
      expressions.

    modified:
      mysql-test/t/partition_locking.test
      sql/partition_info.cc
      sql/sql_base.cc
      sql/sql_base.h
      sql/sql_executor.cc
      sql/sql_insert.cc
      sql/sql_union.cc
      sql/sql_update.cc
=== 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
@@ -67,6 +67,17 @@ HANDLER_WRITE	18
 # Auto increment value not know until write.
 # 28 locks (table + 13 partition lock/unlock)
 # 1 commit
+FLUSH STATUS;
+INSERT INTO t2 (b) VALUES ('Second auto-inc row');
+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	28
+HANDLER_WRITE	18
+# Auto increment value not know until write.
+# 28 locks (table + 13 partition lock/unlock)
+# 1 commit
 #
 # Test of pruning with secondary column auto_inc
 #
@@ -126,6 +137,631 @@ a	b
 1	3
 DROP TABLE t3;
 #
+# More INSERT pruning tests
+#
+CREATE TABLE t3 (a INT, b CHAR(10)) PARTITION BY HASH (a) PARTITIONS 2;
+FLUSH STATUS;
+INSERT INTO t3 VALUES (1, "Test 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
+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	6
+HANDLER_WRITE	20
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (0, "Test 0");
+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 (a, b) VALUES (1, "Test 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
+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	6
+HANDLER_WRITE	20
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (0, "Test 0");
+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 (a) VALUES (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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (2), (3), (4);
+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	6
+HANDLER_WRITE	20
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (6), (8), (10);
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (5), (7), (9);
+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	20
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b) VALUES ("Only b 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b) VALUES ("Only b 2"), ("Only b 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	4
+HANDLER_WRITE	19
+# 4 locks (1 table, 1 partition lock/unlock)
+SELECT * FROM t3 ORDER BY a, b;
+a	b
+NULL	Only b 1
+NULL	Only b 2
+NULL	Only b 3
+0	Test 0
+0	Test 0
+1	NULL
+1	Test 1
+1	Test 1
+2	NULL
+2	Test 2
+2	Test 2
+3	NULL
+3	Test 3
+3	Test 3
+4	NULL
+4	Test 4
+4	Test 4
+5	NULL
+5	Test 5
+5	Test 5
+6	NULL
+6	Test 6
+6	Test 6
+7	NULL
+7	Test 7
+7	Test 7
+8	NULL
+8	Test 8
+8	Test 8
+9	NULL
+9	Test 9
+9	Test 9
+10	NULL
+10	Test 10
+10	Test 10
+DROP TABLE t3;
+#
+# Test of insert pruning with subpartitions
+#
+# I've place the varchar column before the int column for better
+# distribution by LINEAR KEY.
+CREATE TABLE t3
+(a int DEFAULT 10,
+b varchar(64) DEFAULT "Default",
+c varchar(64) DEFAULT "Default",
+d int unsigned DEFAULT 9,
+e varchar(255) DEFAULT "Default-filler.filler.filler.",
+PRIMARY KEY (a,b,c,d))
+PARTITION BY RANGE COLUMNS (a, b)
+SUBPARTITION BY LINEAR KEY (d, c)
+SUBPARTITIONS 4
+(PARTITION pNeg VALUES LESS THAN (-1, MAXVALUE),
+PARTITION `p0-9` VALUES LESS THAN (9, MAXVALUE),
+PARTITION p10 VALUES LESS THAN (10, MAXVALUE),
+PARTITION `p11-100` VALUES LESS THAN (99, MAXVALUE));
+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)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (-1, "ZZZzzzz", "yyyYYY", -1, DEFAULT);
+Warnings:
+Warning	1264	Out of range value for column 'd' at row 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 () VALUES (0, "", "", 0, 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 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (1, "Part expr fulfilled"),
+(10, "Part expr fulfilled");
+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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (d) VALUES (1), (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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (c, d) VALUES ("Subpart expr fulfilled", 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b, d) VALUES (10, "Full part, half subpart", 1),
+(12, "Full part, half subpart", 1),
+(12, "Full part, half subpart", 2),
+(12, "Full part, half subpart", 3),
+(12, "Full part, half subpart", 4),
+(12, "Full part, half subpart", 0);
+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_WRITE	23
+# d = 0 and d = 4 goes to the same subpart!
+# 12 locks (1 table, 5 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b, c) VALUES (1, "Full part", "Half subpart");
+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)
+# Adding 'Default' as padding to see if LINEAR KEY uses different parts.
+FLUSH STATUS;
+INSERT INTO t3 (a, c, d) VALUES (12, "Half part, full subpart", 1),
+(12, "Half part, full subpartDefault", 1),
+(12, "Half part, full subpart Default", 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	6
+HANDLER_WRITE	20
+# First and last row goes to the same subpartition.
+# 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b, c, d) VALUES ("Half part", "Full subpart", 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b, c, d) VALUES (1, "Full part", "Full subpart", 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	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.
+0			0	NULL
+1	Default	Default	9	Default-filler.filler.filler.
+1	Full part	Full subpart	1	Default-filler.filler.filler.
+1	Full part	Half subpart	9	Default-filler.filler.filler.
+1	Part expr fulfilled	Default	9	Default-filler.filler.filler.
+10	Default	Default	1	Default-filler.filler.filler.
+10	Default	Default	2	Default-filler.filler.filler.
+10	Default	Default	9	Default-filler.filler.filler.
+10	Default	Subpart expr fulfilled	1	Default-filler.filler.filler.
+10	Full part, half subpart	Default	1	Default-filler.filler.filler.
+10	Half part	Full subpart	1	Default-filler.filler.filler.
+10	Part expr fulfilled	Default	9	Default-filler.filler.filler.
+12	Default	Half part, full subpart	1	Default-filler.filler.filler.
+12	Default	Half part, full subpart Default	1	Default-filler.filler.filler.
+12	Default	Half part, full subpartDefault	1	Default-filler.filler.filler.
+12	Full part, half subpart	Default	0	Default-filler.filler.filler.
+12	Full part, half subpart	Default	1	Default-filler.filler.filler.
+12	Full part, half subpart	Default	2	Default-filler.filler.filler.
+12	Full part, half subpart	Default	3	Default-filler.filler.filler.
+12	Full part, half subpart	Default	4	Default-filler.filler.filler.
+SELECT d, c FROM t3 PARTITION(`p11-100sp0`);
+d	c
+0	Default
+4	Default
+SELECT d, c FROM t3 PARTITION(`p11-100sp1`);
+d	c
+1	Default
+1	Half part, full subpart
+1	Half part, full subpart Default
+SELECT d, c FROM t3 PARTITION(`p11-100sp2`);
+d	c
+1	Half part, full subpartDefault
+2	Default
+SELECT d, c FROM t3 PARTITION(`p11-100sp3`);
+d	c
+3	Default
+DROP TABLE t3;
+#
+# Test insert with timestamp column
+#
+CREATE TABLE t3
+(a timestamp DEFAULT 0,
+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` (
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `b` char(10) NOT NULL DEFAULT '',
+  PRIMARY KEY (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
+PARTITIONS 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	4
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00');
+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 (a) VALUES ('2011-01-01 00:00:01');
+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 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
+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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
+ON DUPLICATE KEY UPDATE b = CONCAT(b, ", DUP_KEY");
+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_KEY	1
+HANDLER_UPDATE	1
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+5
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+b char(10),
+PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  `b` char(10) NOT NULL DEFAULT '',
+  PRIMARY KEY (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
+PARTITIONS 3 */
+FLUSH STATUS;
+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 (a) VALUES ('2011-01-01 00:00:00');
+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 (a) VALUES ('2011-01-01 00:00:01');
+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 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
+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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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
+# 8 locks (1 table, 3 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+5
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
+b char(10),
+PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
+  `b` char(10) NOT NULL DEFAULT '',
+  PRIMARY KEY (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
+PARTITIONS 3 */
+FLUSH STATUS;
+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 (a) VALUES ('2011-01-01 00:00:00');
+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 (a) VALUES ('2011-01-01 00:00:01');
+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 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
+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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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
+# 8 locks (1 table, 3 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+5
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT CURRENT_TIMESTAMP,
+b char(10),
+PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  `b` char(10) NOT NULL DEFAULT '',
+  PRIMARY KEY (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a))
+PARTITIONS 3 */
+FLUSH STATUS;
+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 (a) VALUES ('2011-01-01 00:00:00');
+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 (a) VALUES ('2011-01-01 00:00:01');
+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 (a) VALUES ('2011-01-01 00:00:02'), ('2011-01-01 00:00:03');
+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	6
+HANDLER_WRITE	19
+# 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:00')
+ON DUPLICATE KEY UPDATE b = CONCAT(b, ", DUP_KEY");
+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_KEY	1
+HANDLER_UPDATE	1
+HANDLER_WRITE	18
+# 4 locks (1 table, 1 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+5
+DROP TABLE t3;
+#
 # Test insert select
 #
 FLUSH STATUS;

=== 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
@@ -58,6 +58,13 @@ eval $get_handler_status_counts;
 --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 # 28 locks (table + 13 partition lock/unlock)
+--echo # 1 commit
+
 --echo #
 --echo # Test of pruning with secondary column auto_inc
 --echo #
@@ -91,6 +98,293 @@ SELECT * FROM t3;
 DROP TABLE t3;
 
 --echo #
+--echo # More INSERT pruning tests
+--echo #
+CREATE TABLE t3 (a INT, b CHAR(10)) PARTITION BY HASH (a) PARTITIONS 2;
+FLUSH STATUS;
+INSERT INTO t3 VALUES (1, "Test 1");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (0, "Test 0");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (1, "Test 1");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (2, "Test 2"), (3, "Test 3"), (4, "Test 4");
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (6, "Test 6"), (8, "Test 8"), (10, "Test 10");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (5, "Test 5"), (7, "Test 7"), (9, "Test 9");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (0, "Test 0");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (1);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (2), (3), (4);
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (6), (8), (10);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (5), (7), (9);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b) VALUES ("Only b 1");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b) VALUES ("Only b 2"), ("Only b 3");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+SELECT * FROM t3 ORDER BY a, b;
+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 # distribution by LINEAR KEY.
+CREATE TABLE t3
+(a int DEFAULT 10,
+ b varchar(64) DEFAULT "Default",
+ c varchar(64) DEFAULT "Default",
+ d int unsigned DEFAULT 9,
+ e varchar(255) DEFAULT "Default-filler.filler.filler.",
+ PRIMARY KEY (a,b,c,d))
+PARTITION BY RANGE COLUMNS (a, b)
+SUBPARTITION BY LINEAR KEY (d, c)
+SUBPARTITIONS 4
+(PARTITION pNeg VALUES LESS THAN (-1, MAXVALUE),
+ PARTITION `p0-9` VALUES LESS THAN (9, MAXVALUE),
+ PARTITION p10 VALUES LESS THAN (10, MAXVALUE),
+ PARTITION `p11-100` VALUES LESS THAN (99, MAXVALUE));
+FLUSH STATUS;
+INSERT INTO t3 () VALUES ();
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 VALUES (-1, "ZZZzzzz", "yyyYYY", -1, DEFAULT);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 () VALUES (0, "", "", 0, NULL);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (1);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b) VALUES (1, "Part expr fulfilled"),
+                             (10, "Part expr fulfilled");
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (d) VALUES (1), (2);
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (c, d) VALUES ("Subpart expr fulfilled", 1);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b, d) VALUES (10, "Full part, half subpart", 1),
+                                (12, "Full part, half subpart", 1),
+                                (12, "Full part, half subpart", 2),
+                                (12, "Full part, half subpart", 3),
+                                (12, "Full part, half subpart", 4),
+                                (12, "Full part, half subpart", 0);
+eval $get_handler_status_counts;
+--echo # d = 0 and d = 4 goes to the same subpart!
+--echo # 12 locks (1 table, 5 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a, b, c) VALUES (1, "Full part", "Half subpart");
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+--echo # Adding 'Default' as padding to see if LINEAR KEY uses different parts.
+FLUSH STATUS;
+INSERT INTO t3 (a, c, d) VALUES (12, "Half part, full subpart", 1),
+                                (12, "Half part, full subpartDefault", 1),
+                                (12, "Half part, full subpart Default", 1);
+eval $get_handler_status_counts;
+--echo # First and last row goes to the same subpartition.
+--echo # 6 locks (1 table, 2 subpartition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (b, c, d) VALUES ("Half part", "Full subpart", 1);
+eval $get_handler_status_counts;
+--echo # 4 locks (1 table, 1 subpartition lock/unlock)
+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)
+
+--sorted_result
+SELECT * FROM t3;
+--sorted_result
+SELECT d, c FROM t3 PARTITION(`p11-100sp0`);
+--sorted_result
+SELECT d, c FROM t3 PARTITION(`p11-100sp1`);
+--sorted_result
+SELECT d, c FROM t3 PARTITION(`p11-100sp2`);
+--sorted_result
+SELECT d, c FROM t3 PARTITION(`p11-100sp3`);
+
+DROP TABLE t3;
+
+--echo #
+--echo # Test insert with timestamp column
+--echo #
+CREATE TABLE t3
+(a timestamp DEFAULT 0,
+ 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;
+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;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
+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:02'), ('2011-01-01 00:00:03');
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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 # 4 locks (1 table, 1 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ b char(10),
+ PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (NULL);
+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;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
+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:02'), ('2011-01-01 00:00:03');
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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';
+--echo # 8 locks (1 table, 3 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
+ b char(10),
+ PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (NULL);
+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;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
+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:02'), ('2011-01-01 00:00:03');
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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';
+--echo # 8 locks (1 table, 3 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+DROP TABLE t3;
+CREATE TABLE t3
+(a timestamp DEFAULT CURRENT_TIMESTAMP,
+ b char(10),
+ PRIMARY KEY (a, b))
+PARTITION BY HASH (UNIX_TIMESTAMP(a)) PARTITIONS 3;
+SHOW CREATE TABLE t3;
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES (NULL);
+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;
+--echo # 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO t3 (a) VALUES ('2011-01-01 00:00:01');
+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:02'), ('2011-01-01 00:00:03');
+eval $get_handler_status_counts;
+--echo # 6 locks (1 table, 2 partition lock/unlock)
+FLUSH STATUS;
+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 # 4 locks (1 table, 1 partition lock/unlock)
+SELECT COUNT(*) FROM t3;
+DROP TABLE t3;
+
+--echo #
 --echo # Test insert select
 --echo #
 FLUSH STATUS;

=== modified file 'sql/field_conv.cc'
--- a/sql/field_conv.cc	revid:mattias.jonsson@stripped
+++ b/sql/field_conv.cc	revid:mattias.jonsson@stripped
@@ -170,7 +170,7 @@ set_field_to_null_with_conversions(Field
     return -1;
 
   /*
-    Check if this is a special type, which will get a special walue
+    Check if this is a special type, which will get a special value
     when set to NULL (TIMESTAMP fields which allow setting to NULL
     are handled by first check).
   */

=== modified file 'sql/partition_info.cc'
--- a/sql/partition_info.cc	revid:mattias.jonsson@stripped
+++ b/sql/partition_info.cc	revid:mattias.jonsson@stripped
@@ -1872,7 +1872,13 @@ void partition_info::report_part_expr_er
 
 
 /**
-  Check if fields are in the partitioning expression
+  Check if fields are in the partitioning expression.
+
+  @param fields  List of Items (fields)
+
+  @return True if any field in the fields list is used by a partitioning expr.
+    @retval true  At least one field in the field list is found.
+    @retval false No field is within any partitioning expression.
 */
 
 bool partition_info::is_field_in_part_expr(List<Item> &fields)
@@ -1880,7 +1886,6 @@ bool partition_info::is_field_in_part_ex
   List_iterator<Item> it(fields);
   Item *item;
   Item_field *field;
-  DBUG_ASSERT(fields.elements);
   DBUG_ENTER("is_fields_in_part_expr");
   while ((item= it++))
   {
@@ -1892,7 +1897,7 @@ bool partition_info::is_field_in_part_ex
     else
     {
       DBUG_ASSERT(field->field->table == table);
-      if (!bitmap_is_set(&full_part_field_set, field->field->field_index))
+      if (bitmap_is_set(&full_part_field_set, field->field->field_index))
         DBUG_RETURN(true);
     }
   }
@@ -1901,6 +1906,51 @@ bool partition_info::is_field_in_part_ex
  
 
 /**
+  Check if all partitioning fields are included.
+*/
+
+bool partition_info::is_full_part_expr_in_fields(List<Item> &fields)
+{
+  Field *part_field= full_part_field_array[0];
+  DBUG_ASSERT(part_field);
+  DBUG_ENTER("is_full_part_expr_in_fields");
+  /*
+    It is very seldom many fields in full_part_field_array, so it is OK
+    to loop over all of them instead of creating a bitmap fields argument
+    to compare with.
+  */
+  do
+  {
+    List_iterator<Item> it(fields);
+    Item *item;
+    Item_field *field;
+    bool found= false;
+  
+    while ((item= it++))
+    {
+      if (!(field= item->filed_for_view_update()))
+      {
+        DBUG_ASSERT(0); // Should already be checked
+        DBUG_RETURN(false);
+      }
+      else
+      {
+        DBUG_ASSERT(field->field->table == table);
+        if (part_field == field->field)
+        {
+          found= true;
+          break;
+        }
+      }
+    }
+    if (!found)
+      DBUG_RETURN(false);
+  } while (++part_field);
+  DBUG_RETURN(true);
+}
+ 
+
+/**
   Create a new column value in current list with maxvalue.
 
   @return Operation status

=== modified file 'sql/partition_info.h'
--- a/sql/partition_info.h	revid:mattias.jonsson@stripped
+++ b/sql/partition_info.h	revid:mattias.jonsson@stripped
@@ -315,6 +315,7 @@ public:
                                    uint32 *part_id);
   void report_part_expr_error(bool use_subpart_expr);
   bool is_field_in_part_expr(List<Item> &fields);
+  bool is_full_part_expr_in_fields(List<Item> &fields);
 private:
   static int list_part_cmp(const void* a, const void* b);
   bool set_up_default_partitions(handler *file, HA_CREATE_INFO *info,

=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_base.cc	revid:mattias.jonsson@stripped
@@ -8920,7 +8920,7 @@ fill_record(THD * thd, List<Item> &field
   Item_field *field;
   TABLE *table= 0;
   DBUG_ENTER("fill_record");
-
+  DBUG_ASSERT(fields.elements == values.elements);
   /*
     Reset the table->auto_increment_field_not_null as it is valid for
     only one row.
@@ -9058,6 +9058,7 @@ fill_record(THD *thd, Field **ptr, List<
     if (value->save_in_field(field, 0) < 0)
       goto err;
   }
+  DBUG_ASSERT(!v++);                         // No extra value!
   DBUG_RETURN(thd->is_error());
 
 err:

=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc	revid:mattias.jonsson@stripped
+++ b/sql/sql_insert.cc	revid:mattias.jonsson@stripped
@@ -663,9 +663,9 @@ create_insert_stmt_from_insert_delayed(T
   @param table  Table the record belongs to
 */
 
-static void default_record(table_map used_tables, TABLE *table)
+static void default_record(bool copy_default_values, TABLE *table)
 {
-  if (used_tables)                           // Column used in values()
+  if (copy_default_values)
     restore_record(table,s->default_values); // Get empty record
   else
   {
@@ -704,15 +704,17 @@ static void default_record(table_map use
 */
 
 static bool set_partition(THD *thd, TABLE *table, List<Item> &fields,
-                          List<Item> &values, MY_BITMAP *used_partitions)
+                          List<Item> &values, bool copy_default_values,
+                          MY_BITMAP *used_partitions)
 {
   uint32 part_id;
   longlong func_value;
   DBUG_ENTER("set_partition");
-  default_record(thd->lex->used_tables, table);
+
+  default_record(copy_default_values, table);
+
   if (fields.elements || !values.elements)
   {
-    DBUG_ASSERT(thd->lex->used_tables);
     if (fill_record(thd, fields, values, false,
         &table->part_info->full_part_field_set))
       DBUG_RETURN(true);
@@ -723,11 +725,11 @@ static bool set_partition(THD *thd, TABL
         &table->part_info->full_part_field_set))
       DBUG_RETURN(true);
   }
+
   if (table->part_info->get_partition_id(table->part_info, &part_id,
                                          &func_value))
-  {
     DBUG_RETURN(true);
-  }
+
   DBUG_PRINT("info", ("Insert into partition %u", part_id));
   bitmap_set_bit(used_partitions, part_id);
   DBUG_RETURN(false);
@@ -782,6 +784,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
   enum enum_can_prune {PRUNE_NO= 0, PRUNE_DEFAULTS, PRUNE_YES};
   enum_can_prune can_prune_partitions= PRUNE_NO;
   MY_BITMAP used_partitions;
+  bool prune_needs_default_values= false;
 #endif /* WITH_PARITITION_STORAGE_ENGINE */
   DBUG_ENTER("mysql_insert");
 
@@ -861,6 +864,7 @@ 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;
@@ -879,38 +883,47 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       few cases (update/auto_inc fields only in part OR subpart fields).
       Cannot be INSERT SELECT.
     */
-#if 0
-    if (can_prune_partitions &&
-        table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
-    {
-      /* TODO: Test if this is a problem, it should use the timestamp which is set when the statement starts? */
-      can_prune_partitions= PRUNE_NO;
-    }
-#endif
-    /* all inserted rows must explicitly set auto increment column */
     if (can_prune_partitions && table->found_next_number_field)
     {
-      Field **field;
       /*
         If the field is used in the partitioning expression, we cannot prune.
-        TODO: Hmm, if all rows have not null values and
+        TODO: If all rows have not null values and
         is not 0 (with NO_AUTO_VALUE_ON_ZERO sql_mode), then pruning is possible!
       */
-      for (field= table->part_info->full_part_field_array; *field; field++)
-      {
-        if (*field == table->found_next_number_field)
-        {
-          can_prune_partitions= PRUNE_NO;
-          break;
-        }
-      }
+      if (bitmap_is_set(&table->part_info->full_part_field_set,
+          table->found_next_number_field->field_index))
+        can_prune_partitions= PRUNE_NO;
     }
     
     /*
       If a updating a field in the partitioning expression, we cannot prune.
+      
+      Note: TIMESTAMP_AUTO_SET_ON_INSERT is handled by converting Item_null
+      to the start time of the statement. Which will be the same as in
+      write_row(). So pruning of TIMESTAMP DEFAULT CURRENT_TIME will work.
+      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.
     */
     if (can_prune_partitions && duplic == DUP_UPDATE)
     {
+      Field *timestamp_field= table->get_timestamp_field();
+      if (timestamp_field &&
+          bitmap_is_set(&table->part_info->full_part_field_set,
+                        timestamp_field->field_index))
+      {
+        /*
+          The timestamp field is part of a partitioning expression.
+          If it is set on update, it is not possible to
+          prune, unless verifying all rows is not set to NULL.
+        */
+        DBUG_PRINT("info", ("timestamp_field_type: %d",
+                            table->timestamp_field_type));
+        if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+          can_prune_partitions= PRUNE_NO;
+      }
+
       /* TODO: add check for static update values, which can be pruned. */
       if (table->part_info->is_field_in_part_expr(update_fields))
         can_prune_partitions= PRUNE_NO;
@@ -928,10 +941,30 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
     /*
       If no partitioning field in set (e.g. defaults) check pruning only once.
     */
-    if (can_prune_partitions && fields.elements)
+    if (can_prune_partitions &&
+        fields.elements &&
+        !table->part_info->is_field_in_part_expr(fields))
+      can_prune_partitions= PRUNE_DEFAULTS;
+
+    /*
+      If not all partitioning fields are given,
+      we also must set all non given partitioning fields
+      to get correct defaults.
+      we do this by copy the full default record in default_record().
+      TODO: If any gain, we could enhance this by only copy the needed default
+      fields by
+        1) check which fields needs to be set.
+        2) only copy those fields from the default record.
+    */
+    if (can_prune_partitions)
     {
-      if (!table->part_info->is_field_in_part_expr(fields))
-        can_prune_partitions= PRUNE_DEFAULTS;
+      if (fields.elements)
+      {
+        if (!table->part_info->is_full_part_expr_in_fields(fields))
+          prune_needs_default_values= true;
+      }
+      else if (!values->elements)
+        prune_needs_default_values= true; // like 'INSERT INTO t () VALUES ()'
     }
 
     if (can_prune_partitions != PRUNE_NO)
@@ -963,7 +996,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
         Do not fail here, since that would break MyISAM behavior of inserting
         all rows before failing row.
       */
-      if (set_partition(thd, table, fields, *values, &used_partitions))
+      if (set_partition(thd, table, fields, *values,
+                        prune_needs_default_values, &used_partitions))
         can_prune_partitions= PRUNE_NO;
     }
   }
@@ -985,11 +1019,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
       TODO: Is it possible to store 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 MyISAM tables.
+      concurrancy on partitioned tables that use table locking, like MyISAM.
     */
     if (can_prune_partitions == PRUNE_YES)
     {
-      if (set_partition(thd, table, fields, *values, &used_partitions))
+      if (set_partition(thd, table, fields, *values,
+                        prune_needs_default_values, &used_partitions))
         can_prune_partitions= PRUNE_NO;
       if (!(counter % num_partitions))
       {

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