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