1856 lines
51 KiB
Plaintext
1856 lines
51 KiB
Plaintext
create table thread_to_monitor(thread_id int);
|
|
insert into thread_to_monitor(thread_id)
|
|
SELECT THREAD_ID FROM performance_schema.threads
|
|
WHERE PROCESSLIST_ID=CONNECTION_ID();
|
|
#
|
|
# Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS
|
|
#
|
|
CREATE TABLE t1 (a int)
|
|
ENGINE = InnoDB
|
|
PARTITION BY HASH (a) PARTITIONS 2;
|
|
INSERT INTO t1 VALUES (0), (1), (2), (3);
|
|
CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0);
|
|
SHOW CREATE VIEW v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) utf8mb4 utf8mb4_0900_ai_ci
|
|
FLUSH STATUS;
|
|
SELECT * FROM v1;
|
|
a
|
|
0
|
|
2
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 2
|
|
FLUSH STATUS;
|
|
SELECT a FROM t1 PARTITION (p0);
|
|
a
|
|
0
|
|
2
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 2
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (10);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (11);
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
FLUSH STATUS;
|
|
SELECT * FROM v1;
|
|
a
|
|
0
|
|
10
|
|
2
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 3
|
|
FLUSH STATUS;
|
|
SELECT a FROM t1 PARTITION (p0);
|
|
a
|
|
0
|
|
10
|
|
2
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 3
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
10
|
|
2
|
|
3
|
|
DROP VIEW v1;
|
|
CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION;
|
|
SHOW CREATE VIEW v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) WITH CASCADED CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (20);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (21);
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
SELECT * FROM v1;
|
|
a
|
|
0
|
|
10
|
|
2
|
|
20
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
10
|
|
2
|
|
20
|
|
3
|
|
DROP VIEW v1;
|
|
CREATE VIEW v1 AS
|
|
SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION;
|
|
SHOW CREATE VIEW v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) where (`t1`.`a` = 30) WITH CASCADED CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (30);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (31);
|
|
ERROR HY000: CHECK OPTION failed 'test.v1'
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
FLUSH STATUS;
|
|
INSERT INTO v1 VALUES (32);
|
|
ERROR HY000: CHECK OPTION failed 'test.v1'
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
Handler_rollback 1
|
|
SELECT * FROM v1;
|
|
a
|
|
30
|
|
SELECT * FROM t1;
|
|
a
|
|
0
|
|
1
|
|
10
|
|
2
|
|
20
|
|
3
|
|
30
|
|
DROP VIEW v1;
|
|
DROP TABLE t1;
|
|
# Original tests for WL#5217
|
|
# Must have InnoDB as engine to get the same statistics results.
|
|
# CREATE SELECT uses the default engine.
|
|
SET @old_default_storage_engine = @@default_storage_engine;
|
|
SET @@default_storage_engine = 'InnoDB';
|
|
# Test to show if I_S affects HANDLER_ counts
|
|
FLUSH STATUS;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# OK, seems to add number of variables processed before HANDLER_WRITE
|
|
# and number of variables + 1 evaluated in the previous call in RND_NEXT
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL,
|
|
b varchar (64),
|
|
INDEX (b,a),
|
|
PRIMARY KEY (a))
|
|
ENGINE = InnoDB
|
|
charset latin1
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0,
|
|
SUBPARTITION subp1),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2,
|
|
SUBPARTITION subp3),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4,
|
|
SUBPARTITION subp5),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6,
|
|
SUBPARTITION subp7));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2 ENGINE = InnoDB,
|
|
SUBPARTITION subp3 ENGINE = InnoDB),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB)) */
|
|
# First test that the syntax is OK
|
|
SHOW CREATE TABLE t1 PARTITION (subp0);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (subp0)' at line 1
|
|
# Not a correct partition list
|
|
INSERT INTO t1 PARTITION () VALUES (1, "error");
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') VALUES (1, "error")' at line 1
|
|
INSERT INTO t1 PARTITION (pNonExisting) VALUES (1, "error");
|
|
ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
|
|
INSERT INTO t1 PARTITION (pNeg, pNonExisting) VALUES (1, "error");
|
|
ERROR HY000: Unknown partition 'pNonExisting' in table 't1'
|
|
# Duplicate partitions and overlapping partitions and subpartitios is OK
|
|
FLUSH STATUS;
|
|
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)");
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
# Should be 1 commit
|
|
# 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");
|
|
# should be correct
|
|
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (100, "`p100-99999`(-subp6)"), (101, "`p100-99999`(-subp7)"), (1000, "`p100-99999`(-subp6)");
|
|
INSERT INTO t1 PARTITION(`p10-99`,subp3) VALUES (1, "subp3"), (10, "p10-99");
|
|
FLUSH STATUS;
|
|
INSERT INTO t1 PARTITION(subp3) VALUES (3, "subp3");
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
# Should be 1 commit
|
|
# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
|
|
# and 18 write (1 ha_innobase + 17 internal I_S write)
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 WRITE;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 17
|
|
Handler_external_lock 65
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
# should be 1 commit
|
|
# 17 writes (internal I_S)
|
|
INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3");
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
Handler_write 1
|
|
# + 1 commit
|
|
# + 19 rnd next (internal I_S)
|
|
# + 19 write (18 internal I_S + 1 insert)
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_external_lock 66
|
|
Handler_prepare 2
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
Handler_write 1
|
|
# + 19 rnd next (internal I_S)
|
|
# + 18 write (internal I_S)
|
|
# Not matching partitions with inserted value
|
|
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (1, "error");
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (1, "error");
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1, "error"), (10, "error");
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
INSERT INTO t1 VALUES (1000000, "error"), (9999999, "error");
|
|
ERROR HY000: Table has no partition for value 1000000
|
|
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1000000, "error"), (9999999, "error");
|
|
ERROR HY000: Table has no partition for value 1000000
|
|
INSERT INTO t1 PARTITION (pNeg, subp4) VALUES (-7, "pNeg(-subp1)"), (-10, "pNeg(-subp0)"), (-1, "pNeg(-subp1)"), (-99, "pNeg(-subp1)");
|
|
Got one of the listed errors
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
-3 pNeg(-subp1)
|
|
-2 (pNeg-)subp0
|
|
-1 pNeg(-subp1)
|
|
1 subp3
|
|
3 subp3
|
|
5 p0-9:subp3
|
|
10 p10-99
|
|
100 `p100-99999`(-subp6)
|
|
101 `p100-99999`(-subp7)
|
|
1000 `p100-99999`(-subp6)
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata;
|
|
SET @@global.innodb_stats_on_metadata=ON;
|
|
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
|
|
FROM INFORMATION_SCHEMA.PARTITIONS
|
|
WHERE TABLE_SCHEMA = 'test'
|
|
AND TABLE_NAME = 't1' ORDER BY SUBPARTITION_NAME;
|
|
PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS
|
|
pNeg subp0 1
|
|
pNeg subp1 2
|
|
p0-9 subp2 0
|
|
p0-9 subp3 3
|
|
p10-99 subp4 1
|
|
p10-99 subp5 0
|
|
p100-99999 subp6 2
|
|
p100-99999 subp7 1
|
|
SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 PARTITION (pNonexistent);
|
|
ERROR HY000: Unknown partition 'pNonexistent' in table 't1'
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# should have failed before locking (only 17 internal I_S writes)
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 PARTITION (subp2);
|
|
a b
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
# Should be 1 commit
|
|
# 1 read first (also calls index_read)
|
|
# 2 read key (first from innobase_get_index and second from index first)
|
|
# 17 writes (internal I_S)
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 3
|
|
Handler_read_key 3
|
|
Handler_read_next 3
|
|
# Should be 1 commit
|
|
# 3 read first (one for each partition)
|
|
# 6 read key (3 from read first and 3 from innobase_get_index)
|
|
# 3 read next (one next call after each read row)
|
|
# 17 writes (internal I_S)
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 READ, t1 as TableAlias READ;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
# 1 commit
|
|
# 18 READ KEY from opening a new partition table instance,
|
|
# (1 innobase_get_index for each index, per partition, 1 x 2 x 8 = 16
|
|
# + info(HA_STATUS_CONST) call on the partition with the most number
|
|
# of rows, 2 innobase_get_index for updating both index statistics)
|
|
# 17 writes (internal I_S)
|
|
SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
|
|
a b
|
|
5 p0-9:subp3
|
|
1 subp3
|
|
3 subp3
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_next 3
|
|
# + 1 commit
|
|
# + 1 read first (read first key from index in one partition)
|
|
# + 2 read key (innobase_get_index from index_init + from index_first)
|
|
# + 3 read next (one after each row)
|
|
# + 19 rnd next (from the last I_S query)
|
|
# + 18 write (internal I_S)
|
|
SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
|
|
COUNT(*)
|
|
1
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 3
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_next 3
|
|
# + 1 commit
|
|
# + 2 read first (one for each subpart)
|
|
# + 4 read key (innobase_get_index from index_init + from index_first)
|
|
# + 1 read next (one after each row)
|
|
# + 19 rnd next (from the last I_S query)
|
|
# + 18 write (internal I_S)
|
|
SELECT * FROM t1 WHERE a = 1000000;
|
|
a b
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 3
|
|
Handler_external_lock 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_next 3
|
|
# No matching partition, only internal I_S.
|
|
SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
|
|
a b
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 3
|
|
Handler_external_lock 4
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_next 3
|
|
# + 18 for unlock (same as lock above) (100 is not in pNeg, no match)
|
|
# Test that EXPLAIN PARTITION works
|
|
EXPLAIN SELECT * FROM t1 PARTITION (pNonexistent);
|
|
ERROR HY000: Unknown partition 'pNonexistent' in table 't1'
|
|
EXPLAIN SELECT * FROM t1 PARTITION (subp2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p0-9_subp2 index NULL b 71 NULL 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` PARTITION (`subp2`)
|
|
FLUSH STATUS;
|
|
EXPLAIN SELECT * FROM t1 PARTITION (subp2,pNeg) AS tablealias;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE tablealias pNeg_subp0,pNeg_subp1,p0-9_subp2 index NULL b 71 NULL 3 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`tablealias`.`a` AS `a`,`test`.`tablealias`.`b` AS `b` from `test`.`t1` PARTITION (`subp2`,`pNeg`) `tablealias`
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
EXPLAIN SELECT * FROM t1 PARTITION (subp3) AS tablealias;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE tablealias p0-9_subp3 index NULL b 71 NULL 3 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`tablealias`.`a` AS `a`,`test`.`tablealias`.`b` AS `b` from `test`.`t1` PARTITION (`subp3`) `tablealias`
|
|
EXPLAIN SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p10-99_subp4,p10-99_subp5 index NULL PRIMARY 4 NULL 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` PARTITION (`p10-99`)
|
|
EXPLAIN SELECT * FROM t1 WHERE a = 1000000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b` from `test`.`t1` where multiple equal(1000000, NULL)
|
|
EXPLAIN SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select NULL AS `a`,NULL AS `b` from `test`.`t1` PARTITION (`pNeg`) where multiple equal(100, NULL)
|
|
# Test how it changes the alias/keywords/reserved words
|
|
SELECT * FROM t1 PARTITION;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
|
|
SELECT * FROM t1 `PARTITION`;
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
5 p0-9:subp3
|
|
1 subp3
|
|
3 subp3
|
|
10 p10-99
|
|
100 `p100-99999`(-subp6)
|
|
1000 `p100-99999`(-subp6)
|
|
101 `p100-99999`(-subp7)
|
|
SELECT * FROM t1 AS PARTITION;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION' at line 1
|
|
SELECT * FROM t1 AS `PARTITION`;
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
5 p0-9:subp3
|
|
1 subp3
|
|
3 subp3
|
|
10 p10-99
|
|
100 `p100-99999`(-subp6)
|
|
1000 `p100-99999`(-subp6)
|
|
101 `p100-99999`(-subp7)
|
|
#
|
|
# Test REPLACE
|
|
#
|
|
FLUSH STATUS;
|
|
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
# explicit pruning says part_id 0 and implicit pruning says part_id 1
|
|
# so no partition will be locked!
|
|
# 0 rollback (since no locked partition)
|
|
# 17 writes (I_S internal)
|
|
FLUSH STATUS;
|
|
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'Insert by REPLACE');
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 1
|
|
# 1 commit
|
|
# 18 writes (17 I_S internal, 1 ha_innobase)
|
|
SELECT * FROM t1 PARTITION (pNeg);
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-21 Insert by REPLACE
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
FLUSH STATUS;
|
|
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_key 1
|
|
Handler_update 1
|
|
Handler_write 1
|
|
# 1 commit
|
|
# 2 read key (1 innobase_get_index when init the index + 1 index read
|
|
# to get the position to update)
|
|
# 1 update (updated one row, since there is no delete trigger, update
|
|
# is used instead of delete+insert)
|
|
# 18 write (17 from I_S, 1 for the failed insert)
|
|
SELECT * FROM t1 PARTITION (pNeg);
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
-21 REPLACEd by REPLACE
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 WRITE;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 17
|
|
Handler_external_lock 65
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
# 1 commit
|
|
# 17 write (internal I_S)
|
|
DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE";
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 33
|
|
Handler_read_next 17
|
|
# + 1 commit
|
|
# + 1 delete (one row deleted)
|
|
# + 3 read key (1 innodb_get_index in records_in_range,
|
|
# 1 innodb_get_index in index_init, 1 index_read in index_read_first)
|
|
# + 1 read next (search for another row in secondary index)
|
|
# + 19 rnd next (internal I_S)
|
|
# + 18 write (internal I_S)
|
|
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 33
|
|
Handler_read_next 17
|
|
# Failed before start_stmt/execution.
|
|
# + 19 rnd next (internal I_S)
|
|
# 0 rollback (No partition had called start_stmt, all parts pruned)
|
|
# + 18 write (internal I_S)
|
|
REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE');
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 21
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 4
|
|
Handler_read_key 33
|
|
Handler_read_next 17
|
|
Handler_write 1
|
|
# + 1 commit
|
|
# + 19 rnd next (internal I_S)
|
|
# + 19 write (18 internal I_S + 1 real write)
|
|
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 23
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 6
|
|
Handler_read_key 34
|
|
Handler_read_next 17
|
|
Handler_update 1
|
|
Handler_write 2
|
|
# + 1 commit
|
|
# + 2 read key (see non locked query)
|
|
# + 19 rnd next (internal I_S)
|
|
# + 1 update (see non locked query)
|
|
# + 19 write (18 internal I_S + 1 failed write)
|
|
SELECT * FROM t1 PARTITION (subp1);
|
|
a b
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
-21 REPLACEd by REPLACE
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 24
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 6
|
|
Handler_read_first 1
|
|
Handler_read_key 35
|
|
Handler_read_next 20
|
|
Handler_update 1
|
|
Handler_write 2
|
|
# + 1 commit
|
|
# + 1 read first
|
|
# + 2 read key
|
|
# + 3 read next
|
|
# + 19 rnd next (internal I_S)
|
|
# + 18 write (internal I_S)
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 24
|
|
Handler_delete 1
|
|
Handler_external_lock 66
|
|
Handler_prepare 6
|
|
Handler_read_first 1
|
|
Handler_read_key 35
|
|
Handler_read_next 20
|
|
Handler_update 1
|
|
Handler_write 2
|
|
# + 19 rnd next (internal I_S)
|
|
# + 18 write (internal I_S)
|
|
#
|
|
# Test LOAD
|
|
#
|
|
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
|
|
a b
|
|
-2 (pNeg-)subp0
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
-21 REPLACEd by REPLACE
|
|
10 p10-99
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 2
|
|
Handler_read_first 4
|
|
Handler_read_key 4
|
|
Handler_read_next 5
|
|
# 2 commit (1 because ALTER TABLE temporarily disables auto-commit)
|
|
# 4 read first (for reading the first row in 4 partitions)
|
|
# 8 read key (4 from read first + 4 for index init)
|
|
# 5 read next (one after each row)
|
|
# 17 write (internal I_S)
|
|
FLUSH STATUS;
|
|
ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit
|
|
Handler_delete
|
|
Handler_external_lock
|
|
Handler_prepare
|
|
Handler_read_key
|
|
Handler_read_next
|
|
Handler_update
|
|
Handler_write
|
|
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
|
|
a b
|
|
FLUSH STATUS;
|
|
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg);
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
Handler_rollback 2
|
|
Handler_write 4
|
|
# 1 rollback
|
|
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
|
|
a b
|
|
FLUSH STATUS;
|
|
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, subp4, subp5);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_write 5
|
|
# 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock)
|
|
ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2 ENGINE = InnoDB,
|
|
SUBPARTITION subp3 ENGINE = InnoDB),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB)) */
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 WRITE;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 17
|
|
Handler_external_lock 65
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
# 18 read key (ALTER forces table to be closed, see above for open)
|
|
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, `p10-99`) CHARACTER SET latin1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
Handler_write 5
|
|
# + 23 write (18 internal I_S + 5 rows)
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_external_lock 66
|
|
Handler_prepare 2
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
Handler_write 5
|
|
#
|
|
# Test UPDATE
|
|
#
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 1
|
|
Handler_update 1
|
|
# 1 commit
|
|
# 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
|
|
# 1 read first (read first row, called from first rnd_next)
|
|
# 2 read key (innobase_get_index from rnd_init +
|
|
# read next row from second rnd_next)
|
|
# 1 update (update the row)
|
|
EXPLAIN UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 pNeg_subp0 index NULL PRIMARY 4 NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` PARTITION (`subp0`) set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', Updated')
|
|
SELECT * FROM t1 PARTITION (subp0) ORDER BY a;
|
|
a b
|
|
-2 (pNeg-)subp0, Updated
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_key 1
|
|
Handler_update 1
|
|
# 1 commit
|
|
# 4 lock
|
|
# 1 read key
|
|
# 1 update
|
|
EXPLAIN UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 pNeg_subp0 range PRIMARY PRIMARY 4 const 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` PARTITION (`subp0`) set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', Updated2') where (`test`.`t1`.`a` = -(2))
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2')
|
|
WHERE a = -2;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_key 2
|
|
Handler_read_rnd 1
|
|
Handler_update 1
|
|
# 1 commit
|
|
# 4 lock
|
|
# 2 read key - (2 index read)
|
|
# 1 read rnd - rnd_pos
|
|
# 1 update
|
|
EXPLAIN UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t1 pNeg_subp0 index NULL PRIMARY 4 NULL 1 100.00 Using temporary
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` PARTITION (`subp0`) set `test`.`t1`.`a` = -(4),`test`.`t1`.`b` = concat(`test`.`t1`.`b`,', Updated from a = -2')
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# Nothing, since impossible PARTITION+WHERE clause.
|
|
EXPLAIN UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE NULL NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` PARTITION (`subp0`) set `test`.`t1`.`b` = concat(`test`.`t1`.`b`,', Updated2') where (`test`.`t1`.`a` = 100)
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100')
|
|
WHERE a = 100;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# Nothing, since impossible PARTITION+WHERE clause.
|
|
EXPLAIN UPDATE t1 PARTITION(subp0)
|
|
SET a = -2, b = concat(b, ', Updated from a = 100') WHERE a = 100;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE NULL NULL NULL NULL NULL NULL NULL NULL NULL No matching rows after partition pruning
|
|
Warnings:
|
|
Note 1003 update `test`.`t1` PARTITION (`subp0`) set `test`.`t1`.`a` = -(2),`test`.`t1`.`b` = concat(`test`.`t1`.`b`,', Updated from a = 100') where (`test`.`t1`.`a` = 100)
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100')
|
|
WHERE a = 100;
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
Handler_read_key 2
|
|
Handler_read_rnd 1
|
|
Handler_rollback 1
|
|
# 6 lock
|
|
# 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos)
|
|
# 1 read rnd (rnd pos)
|
|
# 1 rollback
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -4, b = concat(b, ', Updated from a = 100')
|
|
WHERE a = 100;
|
|
Got one of the listed errors
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 2
|
|
Handler_read_key 2
|
|
Handler_read_rnd 1
|
|
Handler_rollback 1
|
|
Handler_write 1
|
|
# 4 read key
|
|
# 1 read rnd
|
|
# 1 rollback
|
|
# 18 write (17 internal I_S + 1 failed insert)
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -222, b = concat(b, ', Updated from a = 100')
|
|
WHERE a = 100;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_delete 1
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_key 2
|
|
Handler_read_rnd 1
|
|
Handler_write 1
|
|
# 1 commit
|
|
# 1 delete
|
|
# 4 read key
|
|
# 1 read rnd
|
|
# 18 write (17 internal I_S + 1 insert)
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
-21 REPLACEd by REPLACE
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
1 subp3
|
|
3 subp3
|
|
5 p0-9:subp3
|
|
10 p10-99
|
|
101 `p100-99999`(-subp7)
|
|
1000 `p100-99999`(-subp6)
|
|
# Test of non matching partition (i.e ER_NO_PARTITION_FOUND)
|
|
FLUSH STATUS;
|
|
UPDATE t1 SET b = concat(b, ', Updated2') WHERE a = 1000000;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# Nothing (no matching partition found)
|
|
FLUSH STATUS;
|
|
UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# Nothing (no matching partition found)
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 WRITE;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 17
|
|
Handler_external_lock 65
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 19
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 34
|
|
Handler_read_next 16
|
|
Handler_read_rnd 1
|
|
Handler_update 1
|
|
# + 4 read key
|
|
# + 1 read rnd
|
|
# + 1 update
|
|
UPDATE t1 PARTITION (`p100-99999`) SET b = concat(b, ', Updated to 110'), a = 110 WHERE a = 103;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 21
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 4
|
|
Handler_read_key 36
|
|
Handler_read_next 16
|
|
Handler_read_rnd 2
|
|
Handler_update 1
|
|
Handler_write 1
|
|
# + 1 delete
|
|
# + 4 read key
|
|
# + 1 read rnd
|
|
# + 19 write (18 internal I_S + 1 insert)
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 21
|
|
Handler_delete 1
|
|
Handler_external_lock 66
|
|
Handler_prepare 4
|
|
Handler_read_key 36
|
|
Handler_read_next 16
|
|
Handler_read_rnd 2
|
|
Handler_update 1
|
|
Handler_write 1
|
|
#
|
|
# Test DELETE
|
|
#
|
|
SELECT * FROM t1 ORDER BY b, a;
|
|
a b
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
5 p0-9:subp3
|
|
10 p10-99
|
|
-3 pNeg(-subp1)
|
|
-1 pNeg(-subp1)
|
|
-21 REPLACEd by REPLACE
|
|
1 subp3
|
|
3 subp3
|
|
1000 `p100-99999`(-subp6)
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
FLUSH STATUS;
|
|
DELETE FROM t1 PARTITION (pNeg) WHERE a = -1;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_delete 1
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_key 1
|
|
# 1 delete
|
|
# 1 read key (index read)
|
|
FLUSH STATUS;
|
|
DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_delete 1
|
|
Handler_external_lock 2
|
|
Handler_prepare 2
|
|
Handler_read_first 1
|
|
Handler_read_key 1
|
|
Handler_read_rnd_next 2
|
|
# 1 delete
|
|
# 1 read first
|
|
# 2 read key
|
|
# 3 read rnd
|
|
FLUSH STATUS;
|
|
LOCK TABLE t1 WRITE;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 17
|
|
Handler_external_lock 65
|
|
Handler_read_key 32
|
|
Handler_read_next 16
|
|
DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 18
|
|
Handler_external_lock 65
|
|
Handler_read_key 33
|
|
Handler_read_next 16
|
|
# + 3 read key (1 innodb_get_index in records_in_range
|
|
# + 1 innobase_get_index in index_init + 1 index read)
|
|
DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3';
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 20
|
|
Handler_delete 1
|
|
Handler_external_lock 65
|
|
Handler_prepare 2
|
|
Handler_read_key 35
|
|
Handler_read_next 17
|
|
# + 1 delete
|
|
# + 6 read key (same as above, but for two subpartitions)
|
|
# + 1 read next (read next after found row)
|
|
UNLOCK TABLES;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 20
|
|
Handler_delete 1
|
|
Handler_external_lock 66
|
|
Handler_prepare 2
|
|
Handler_read_key 35
|
|
Handler_read_next 17
|
|
# Test multi-table DELETE
|
|
# Can be expressed in two different ways.
|
|
CREATE TABLE t2 LIKE t1;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2 ENGINE = InnoDB,
|
|
SUBPARTITION subp3 ENGINE = InnoDB),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB)) */
|
|
FLUSH STATUS;
|
|
INSERT INTO t2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 4
|
|
Handler_prepare 2
|
|
Handler_read_first 5
|
|
Handler_read_key 5
|
|
Handler_read_next 5
|
|
Handler_write 5
|
|
FLUSH STATUS;
|
|
ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit
|
|
Handler_delete
|
|
Handler_external_lock
|
|
Handler_prepare
|
|
Handler_read_key
|
|
Handler_read_next
|
|
Handler_update
|
|
Handler_write
|
|
# Warm-up data-dictionary cache
|
|
FLUSH STATUS;
|
|
INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
|
|
ERROR HY000: Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_external_lock 4
|
|
Handler_read_first 2
|
|
Handler_read_key 2
|
|
Handler_read_next 2
|
|
Handler_rollback 2
|
|
Handler_write 2
|
|
FLUSH STATUS;
|
|
INSERT IGNORE INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
|
|
Warnings:
|
|
Warning 1748 Found a row not matching the given partition set
|
|
Warning 1748 Found a row not matching the given partition set
|
|
Warning 1748 Found a row not matching the given partition set
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 4
|
|
Handler_prepare 2
|
|
Handler_read_first 5
|
|
Handler_read_key 5
|
|
Handler_read_next 5
|
|
Handler_write 2
|
|
TRUNCATE TABLE t2;
|
|
# Warm-up data-dictionary cache
|
|
FLUSH STATUS;
|
|
INSERT INTO t2 SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_external_lock 4
|
|
Handler_prepare 2
|
|
Handler_read_first 5
|
|
Handler_read_key 5
|
|
Handler_read_next 5
|
|
Handler_write 5
|
|
FLUSH STATUS;
|
|
CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`);
|
|
SELECT VARIABLE_NAME, VARIABLE_VALUE
|
|
FROM performance_schema.status_by_thread
|
|
WHERE VARIABLE_NAME IN ('Handler_read_first', 'Handler_read_next', 'Handler_write')
|
|
AND VARIABLE_VALUE > 0
|
|
AND THREAD_ID IN (SELECT thread_id from test.thread_to_monitor);
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_read_first 5
|
|
Handler_read_next 7
|
|
Handler_write 25
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2 ENGINE = InnoDB,
|
|
SUBPARTITION subp3 ENGINE = InnoDB),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB)) */
|
|
SELECT * FROM t1;
|
|
a b
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
-21 REPLACEd by REPLACE
|
|
1 subp3
|
|
3 subp3
|
|
10 p10-99
|
|
1000 `p100-99999`(-subp6)
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB),
|
|
PARTITION `p0-9` VALUES LESS THAN (10)
|
|
(SUBPARTITION subp2 ENGINE = InnoDB,
|
|
SUBPARTITION subp3 ENGINE = InnoDB),
|
|
PARTITION `p10-99` VALUES LESS THAN (100)
|
|
(SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p100-99999` VALUES LESS THAN (100000)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB)) */
|
|
SELECT * FROM t2;
|
|
a b
|
|
1 subp3
|
|
3 subp3
|
|
10 p10-99
|
|
1000 `p100-99999`(-subp6)
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) CHARACTER SET latin1 DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SELECT * FROM t3;
|
|
a b
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
-21 REPLACEd by REPLACE
|
|
1 subp3
|
|
3 subp3
|
|
1000 `p100-99999`(-subp6)
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
FLUSH STATUS;
|
|
DELETE t1 PARTITION (pNeg), t3 FROM t1, t3
|
|
WHERE t1.a = t3.a AND t3.b = 'subp3';
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (pNeg), t3 FROM t1, t3
|
|
WHERE t1.a = t3.a AND t3.b = 'subp3'' at line 1
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
# Multi table delete without any matching rows
|
|
FLUSH STATUS;
|
|
DELETE t1, t2 FROM t1 PARTITION (pNeg), t3, t2 PARTITION (subp3)
|
|
WHERE t1.a = t3.a AND t3.b = 'subp3' AND t3.a = t2.a;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 1
|
|
Handler_external_lock 6
|
|
Handler_read_first 1
|
|
Handler_read_key 3
|
|
Handler_read_rnd_next 8
|
|
# 1 read first (first rnd_next in t2)
|
|
# 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2
|
|
# + 2 innodb_get_index in index_init in t1)
|
|
# 3 read rnd next (3 rnd next in t2, 2 rows + 1 empty)
|
|
# Multi table delete matching all rows in subp3 (2 rows in per table)
|
|
# Pull-in all objects in Table and DD caches first.
|
|
FLUSH STATUS;
|
|
DELETE FROM t2, t3 USING t2 PARTITION (`p0-9`), t3, t1 PARTITION (subp3)
|
|
WHERE t1.a = t3.a AND t3.b = 'subp3' AND t2.a = t1.a;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit 2
|
|
Handler_delete 4
|
|
Handler_external_lock 6
|
|
Handler_prepare 2
|
|
Handler_read_first 1
|
|
Handler_read_key 7
|
|
Handler_read_rnd 2
|
|
Handler_read_rnd_next 8
|
|
# 4 delete (2 in t2 + 2 in t3)
|
|
# 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1)
|
|
# 17 read key (1 index_init in t1 + 1 read first in t1 +
|
|
# 2 index_init in t2 + 1 index read in t2 +
|
|
# 1 index_init in t3 + 1 index read in t3 +
|
|
# 1 index read in t2 +
|
|
# 1 index_init in t3 + 1 index read in t3 +
|
|
# 2 index_init in t2 + 2 index read in t2 (from rnd_pos)
|
|
# 1 index_init in t3 + 2 index read in t3 (from rnd_pos))
|
|
# 2 read next (1 in t1 + 1 in t1, second row)
|
|
# 4 read rnd (position on 4 found rows to delete)
|
|
# 16 rnd next (8 in t3 + 8 in t3, for second row)
|
|
SELECT * FROM t1 ORDER BY a;
|
|
a b
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
-21 REPLACEd by REPLACE
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
1 subp3
|
|
3 subp3
|
|
10 p10-99
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
1000 `p100-99999`(-subp6)
|
|
SELECT * FROM t2 ORDER BY a;
|
|
a b
|
|
10 p10-99
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
1000 `p100-99999`(-subp6)
|
|
SELECT * FROM t3 ORDER BY a;
|
|
a b
|
|
-222 `p100-99999`(-subp6), Updated from a = 100
|
|
-21 REPLACEd by REPLACE
|
|
-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
|
|
110 `p100-99999`(-subp7), Updated to 103, Updated to 110
|
|
1000 `p100-99999`(-subp6)
|
|
# Test TRUNCATE TABLE (should fail, since one should use
|
|
# ALTER TABLE ... TRUNCATE PARTITION instead)
|
|
TRUNCATE TABLE t1 PARTITION(`p10-99`);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION(`p10-99`)' at line 1
|
|
# Test of locking in TRUNCATE PARTITION
|
|
# Note that it does not support truncating subpartitions
|
|
FLUSH STATUS;
|
|
ALTER TABLE t1 TRUNCATE PARTITION pNeg;
|
|
VARIABLE_NAME VARIABLE_VALUE
|
|
Handler_commit
|
|
Handler_delete
|
|
Handler_external_lock
|
|
Handler_prepare
|
|
Handler_read_key
|
|
Handler_read_next
|
|
Handler_update
|
|
Handler_write
|
|
# Lots of extra lock acquisitions, reads and etc due to
|
|
# data-dictionary update.
|
|
# Test on non partitioned table
|
|
SELECT * FROM t3 PARTITION (pNeg);
|
|
ERROR HY000: PARTITION () clause on non partitioned table
|
|
DROP TABLE t1, t2, t3;
|
|
# Test from superseeded WL# 2682
|
|
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
|
|
CREATE TABLE `t1` (
|
|
`id` int(11) default NULL
|
|
) DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE (id) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
|
|
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
|
|
SELECT * FROM t1;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
SELECT * FROM t1 PARTITION (p0);
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT * FROM t1 PARTITION (p1);
|
|
id
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
SELECT * FROM t1 PARTITION (p2);
|
|
id
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
SELECT * FROM t1 PARTITION (p3);
|
|
id
|
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
SELECT * FROM t1 PARTITION (p3) WHERE id = 2;
|
|
id
|
|
SELECT * FROM t1 PARTITION (foo);
|
|
ERROR HY000: Unknown partition 'foo' in table 't1'
|
|
CREATE TABLE `t2` (
|
|
`id` int(11) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARSET=latin1
|
|
PARTITION BY RANGE (id) (
|
|
PARTITION p0 VALUES LESS THAN (6),
|
|
PARTITION p1 VALUES LESS THAN (11),
|
|
PARTITION p2 VALUES LESS THAN (16),
|
|
PARTITION p3 VALUES LESS THAN (21));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
|
|
(11), (12), (13), (14), (15), (16), (17), (18), (19), (20);
|
|
SELECT * FROM t2;
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
SELECT * FROM t2 PARTITION (p0);
|
|
id
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
SELECT * FROM t2 PARTITION (p1);
|
|
id
|
|
6
|
|
7
|
|
8
|
|
9
|
|
10
|
|
SELECT * FROM t2 PARTITION (p2);
|
|
id
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
SELECT * FROM t2 PARTITION (p3);
|
|
id
|
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
SELECT * FROM t2 PARTITION (p3) ORDER BY id;
|
|
id
|
|
16
|
|
17
|
|
18
|
|
19
|
|
20
|
|
SELECT * FROM t2 PARTITION (p3) WHERE id = 2;
|
|
id
|
|
SELECT * FROM t2 PARTITION (foo);
|
|
ERROR HY000: Unknown partition 'foo' in table 't2'
|
|
CREATE TABLE `t3` (
|
|
`id` int(32) default NULL,
|
|
`name` varchar(32) default NULL
|
|
) DEFAULT CHARSET=latin1
|
|
PARTITION BY LIST (id) (
|
|
PARTITION p0 VALUES IN (1,3,5,7),
|
|
PARTITION p1 VALUES IN (0,2,4,6,8),
|
|
PARTITION p2 VALUES IN (9,10,11,12,13)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t3` VALUES (1,'first'), (3,'third'),(5,'fifth'),(7,'seventh'),(0,'zilch'),(2,'second'),(4,'fourth'),(6,'sixth'),(8,'eighth'),(9,'ninth'),(10,'tenth'),(11,'eleventh'),(12,'twelfth'),(13,'thirteenth');
|
|
SELECT * FROM `t3`;
|
|
id name
|
|
1 first
|
|
3 third
|
|
5 fifth
|
|
7 seventh
|
|
0 zilch
|
|
2 second
|
|
4 fourth
|
|
6 sixth
|
|
8 eighth
|
|
9 ninth
|
|
10 tenth
|
|
11 eleventh
|
|
12 twelfth
|
|
13 thirteenth
|
|
SELECT * FROM `t3` PARTITION (p0);
|
|
id name
|
|
1 first
|
|
3 third
|
|
5 fifth
|
|
7 seventh
|
|
SELECT * FROM `t3` PARTITION (p1);
|
|
id name
|
|
0 zilch
|
|
2 second
|
|
4 fourth
|
|
6 sixth
|
|
8 eighth
|
|
SELECT * FROM `t3` PARTITION (p2);
|
|
id name
|
|
9 ninth
|
|
10 tenth
|
|
11 eleventh
|
|
12 twelfth
|
|
13 thirteenth
|
|
SELECT * FROM `t3` PARTITION (p2) ORDER BY id;
|
|
id name
|
|
9 ninth
|
|
10 tenth
|
|
11 eleventh
|
|
12 twelfth
|
|
13 thirteenth
|
|
DROP TABLE IF EXISTS `t4`;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t4'
|
|
CREATE TABLE `t4` (
|
|
`id` int(32) default NULL
|
|
) DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t4` SELECT * FROM `t2`;
|
|
INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id;
|
|
CREATE TABLE `t5` (
|
|
id int(32),
|
|
name varchar(64),
|
|
purchased date)
|
|
PARTITION BY RANGE( YEAR(purchased) )
|
|
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
|
|
PARTITION p0 VALUES LESS THAN (1990) (
|
|
SUBPARTITION s0,
|
|
SUBPARTITION s1
|
|
),
|
|
PARTITION p1 VALUES LESS THAN (2000) (
|
|
SUBPARTITION s2,
|
|
SUBPARTITION s3
|
|
),
|
|
PARTITION p2 VALUES LESS THAN MAXVALUE (
|
|
SUBPARTITION s4,
|
|
SUBPARTITION s5
|
|
)
|
|
);
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00');
|
|
INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00');
|
|
INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00');
|
|
INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00');
|
|
INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00');
|
|
INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00');
|
|
INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00');
|
|
INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00');
|
|
INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00');
|
|
INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00');
|
|
SELECT * FROM `t5`;
|
|
id name purchased
|
|
8 hhhhhhh 1978-01-05
|
|
13 nnnnnnn 1989-01-05
|
|
14 ooooooo 1983-12-05
|
|
18 sssssss 1950-09-23
|
|
3 ccccccc 1985-08-07
|
|
9 iiiiiii 1979-01-05
|
|
15 ppppppp 1986-06-05
|
|
16 qqqqqqq 1974-04-11
|
|
17 qqqqqqq 1960-03-15
|
|
5 eeeeeee 1999-12-01
|
|
12 mmmmmmm 1994-01-05
|
|
7 ggggggg 1990-01-05
|
|
10 jjjjjjj 1992-01-05
|
|
11 kkkkkkk 1993-01-05
|
|
19 ttttttt 1999-08-02
|
|
20 uuuuuuu 1994-05-28
|
|
2 bbbbbbb 2005-08-05
|
|
6 fffffff 2003-11-12
|
|
1 aaaaaaa 2006-01-05
|
|
4 ddddddd 2000-01-01
|
|
SELECT * FROM `t5` PARTITION(p0) ORDER BY id;
|
|
id name purchased
|
|
3 ccccccc 1985-08-07
|
|
8 hhhhhhh 1978-01-05
|
|
9 iiiiiii 1979-01-05
|
|
13 nnnnnnn 1989-01-05
|
|
14 ooooooo 1983-12-05
|
|
15 ppppppp 1986-06-05
|
|
16 qqqqqqq 1974-04-11
|
|
17 qqqqqqq 1960-03-15
|
|
18 sssssss 1950-09-23
|
|
SELECT * FROM `t5` PARTITION(s0) ORDER BY id;
|
|
id name purchased
|
|
8 hhhhhhh 1978-01-05
|
|
13 nnnnnnn 1989-01-05
|
|
14 ooooooo 1983-12-05
|
|
18 sssssss 1950-09-23
|
|
SELECT * FROM `t5` PARTITION(s1) ORDER BY id;
|
|
id name purchased
|
|
3 ccccccc 1985-08-07
|
|
9 iiiiiii 1979-01-05
|
|
15 ppppppp 1986-06-05
|
|
16 qqqqqqq 1974-04-11
|
|
17 qqqqqqq 1960-03-15
|
|
SELECT * FROM `t5` PARTITION(p1) ORDER BY id;
|
|
id name purchased
|
|
5 eeeeeee 1999-12-01
|
|
7 ggggggg 1990-01-05
|
|
10 jjjjjjj 1992-01-05
|
|
11 kkkkkkk 1993-01-05
|
|
12 mmmmmmm 1994-01-05
|
|
19 ttttttt 1999-08-02
|
|
20 uuuuuuu 1994-05-28
|
|
SELECT * FROM `t5` PARTITION(s2) ORDER BY id;
|
|
id name purchased
|
|
5 eeeeeee 1999-12-01
|
|
12 mmmmmmm 1994-01-05
|
|
SELECT * FROM `t5` PARTITION(s3) ORDER BY id;
|
|
id name purchased
|
|
7 ggggggg 1990-01-05
|
|
10 jjjjjjj 1992-01-05
|
|
11 kkkkkkk 1993-01-05
|
|
19 ttttttt 1999-08-02
|
|
20 uuuuuuu 1994-05-28
|
|
SELECT * FROM `t5` PARTITION(p2) ORDER BY id;
|
|
id name purchased
|
|
1 aaaaaaa 2006-01-05
|
|
2 bbbbbbb 2005-08-05
|
|
4 ddddddd 2000-01-01
|
|
6 fffffff 2003-11-12
|
|
SELECT * FROM `t5` PARTITION(s4) ORDER BY id;
|
|
id name purchased
|
|
2 bbbbbbb 2005-08-05
|
|
6 fffffff 2003-11-12
|
|
SELECT * FROM `t5` PARTITION(s5) ORDER BY id;
|
|
id name purchased
|
|
1 aaaaaaa 2006-01-05
|
|
4 ddddddd 2000-01-01
|
|
drop table t1,t2,t3,t4,t5;
|
|
create table t1 (a int) partition by hash(a) partitions 3;
|
|
insert into t1 values(1),(2),(3);
|
|
explain select * from t1 where a=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1)
|
|
explain select * from t1 partition (p1) where a=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` PARTITION (`p1`) where (`test`.`t1`.`a` = 1)
|
|
explain select * from t1 partition (p1) where a=1 or a=2;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` PARTITION (`p1`) where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`a` = 2))
|
|
explain select * from t1 partition (p2) where a=1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` PARTITION (`p2`) where (`test`.`t1`.`a` = 1)
|
|
drop table t1;
|
|
#
|
|
# Bug#59864: Crash if table empty: DELETE FROM t2 PARTITION (subp3).
|
|
#
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL,
|
|
b varchar (64),
|
|
INDEX (b,a),
|
|
PRIMARY KEY (a))
|
|
PARTITION BY RANGE (a)
|
|
SUBPARTITION BY HASH (a) SUBPARTITIONS 3
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0,
|
|
SUBPARTITION subp1,
|
|
SUBPARTITION subp2),
|
|
PARTITION `p0-29` VALUES LESS THAN (30)
|
|
(SUBPARTITION subp3,
|
|
SUBPARTITION subp4,
|
|
SUBPARTITION subp5),
|
|
PARTITION `p30-299` VALUES LESS THAN (300)
|
|
(SUBPARTITION subp6,
|
|
SUBPARTITION subp7,
|
|
SUBPARTITION subp8),
|
|
PARTITION `p300-2999` VALUES LESS THAN (3000)
|
|
(SUBPARTITION subp9,
|
|
SUBPARTITION subp10,
|
|
SUBPARTITION subp11),
|
|
PARTITION `p3000-299999` VALUES LESS THAN (300000)
|
|
(SUBPARTITION subp12,
|
|
SUBPARTITION subp13,
|
|
SUBPARTITION subp14));
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(64) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`,`a`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
SUBPARTITION BY HASH (`a`)
|
|
(PARTITION pNeg VALUES LESS THAN (0)
|
|
(SUBPARTITION subp0 ENGINE = InnoDB,
|
|
SUBPARTITION subp1 ENGINE = InnoDB,
|
|
SUBPARTITION subp2 ENGINE = InnoDB),
|
|
PARTITION `p0-29` VALUES LESS THAN (30)
|
|
(SUBPARTITION subp3 ENGINE = InnoDB,
|
|
SUBPARTITION subp4 ENGINE = InnoDB,
|
|
SUBPARTITION subp5 ENGINE = InnoDB),
|
|
PARTITION `p30-299` VALUES LESS THAN (300)
|
|
(SUBPARTITION subp6 ENGINE = InnoDB,
|
|
SUBPARTITION subp7 ENGINE = InnoDB,
|
|
SUBPARTITION subp8 ENGINE = InnoDB),
|
|
PARTITION `p300-2999` VALUES LESS THAN (3000)
|
|
(SUBPARTITION subp9 ENGINE = InnoDB,
|
|
SUBPARTITION subp10 ENGINE = InnoDB,
|
|
SUBPARTITION subp11 ENGINE = InnoDB),
|
|
PARTITION `p3000-299999` VALUES LESS THAN (300000)
|
|
(SUBPARTITION subp12 ENGINE = InnoDB,
|
|
SUBPARTITION subp13 ENGINE = InnoDB,
|
|
SUBPARTITION subp14 ENGINE = InnoDB)) */
|
|
INSERT INTO t1 VALUES (-9, "negative nine"), (-8, "-8"), (-7, "-7"), (-6, "-6"), (-5, "-5"), (-4, "-4"), (-3, "-3"), (-2, "-2"), (-1, "-1");
|
|
INSERT INTO t1 VALUES (9, "nine"), (8, "8"), (7, "7"), (6, "6"), (5, "5"), (4, "4"), (3, "3"), (2, "2"), (1, "1");
|
|
INSERT INTO t1 VALUES (39, "Thirty nine"), (38, "38"), (37, "37"), (36, "36"), (35, "35"), (34, "34"), (33, "33"), (32, "32"), (31, "31");
|
|
INSERT INTO t1 VALUES (339, "Three hundred thirty nine"), (338, "338"), (337, "337"), (336, "336"), (335, "335"), (334, "334"), (333, "333"), (332, "332"), (331, "331");
|
|
INSERT INTO t1 VALUES (3339, "Three thousand three hundred thirty nine"), (3338, "3338"), (3337, "3337"), (3336, "3336"), (3335, "3335"), (3334, "3334"), (3333, "3333"), (3332, "3332"), (3331, "3331");
|
|
SELECT * FROM t1;
|
|
a b
|
|
-1 -1
|
|
-2 -2
|
|
-3 -3
|
|
-4 -4
|
|
-5 -5
|
|
-6 -6
|
|
-7 -7
|
|
-8 -8
|
|
-9 negative nine
|
|
1 1
|
|
2 2
|
|
3 3
|
|
31 31
|
|
32 32
|
|
33 33
|
|
331 331
|
|
332 332
|
|
333 333
|
|
3331 3331
|
|
3332 3332
|
|
3333 3333
|
|
3334 3334
|
|
3335 3335
|
|
3336 3336
|
|
3337 3337
|
|
3338 3338
|
|
3339 Three thousand three hundred thirty nine
|
|
334 334
|
|
335 335
|
|
336 336
|
|
337 337
|
|
338 338
|
|
339 Three hundred thirty nine
|
|
34 34
|
|
35 35
|
|
36 36
|
|
37 37
|
|
38 38
|
|
39 Thirty nine
|
|
4 4
|
|
5 5
|
|
6 6
|
|
7 7
|
|
8 8
|
|
9 nine
|
|
SELECT * FROM t1 PARTITION (subp3);
|
|
a b
|
|
3 3
|
|
6 6
|
|
9 nine
|
|
DELETE FROM t1 PARTITION (subp3);
|
|
SELECT * FROM t1;
|
|
a b
|
|
-1 -1
|
|
-2 -2
|
|
-3 -3
|
|
-4 -4
|
|
-5 -5
|
|
-6 -6
|
|
-7 -7
|
|
-8 -8
|
|
-9 negative nine
|
|
1 1
|
|
2 2
|
|
31 31
|
|
32 32
|
|
33 33
|
|
331 331
|
|
332 332
|
|
333 333
|
|
3331 3331
|
|
3332 3332
|
|
3333 3333
|
|
3334 3334
|
|
3335 3335
|
|
3336 3336
|
|
3337 3337
|
|
3338 3338
|
|
3339 Three thousand three hundred thirty nine
|
|
334 334
|
|
335 335
|
|
336 336
|
|
337 337
|
|
338 338
|
|
339 Three hundred thirty nine
|
|
34 34
|
|
35 35
|
|
36 36
|
|
37 37
|
|
38 38
|
|
39 Thirty nine
|
|
4 4
|
|
5 5
|
|
7 7
|
|
8 8
|
|
SELECT * FROM t1 PARTITION (subp3);
|
|
a b
|
|
DELETE FROM t1 PARTITION (`p0-29`);
|
|
SELECT * FROM t1;
|
|
a b
|
|
-1 -1
|
|
-2 -2
|
|
-3 -3
|
|
-4 -4
|
|
-5 -5
|
|
-6 -6
|
|
-7 -7
|
|
-8 -8
|
|
-9 negative nine
|
|
31 31
|
|
32 32
|
|
33 33
|
|
331 331
|
|
332 332
|
|
333 333
|
|
3331 3331
|
|
3332 3332
|
|
3333 3333
|
|
3334 3334
|
|
3335 3335
|
|
3336 3336
|
|
3337 3337
|
|
3338 3338
|
|
3339 Three thousand three hundred thirty nine
|
|
334 334
|
|
335 335
|
|
336 336
|
|
337 337
|
|
338 338
|
|
339 Three hundred thirty nine
|
|
34 34
|
|
35 35
|
|
36 36
|
|
37 37
|
|
38 38
|
|
39 Thirty nine
|
|
SELECT * FROM t1 PARTITION (`p0-29`);
|
|
a b
|
|
ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 3;
|
|
DELETE FROM t1 PARTITION (p2);
|
|
SELECT * FROM t1;
|
|
a b
|
|
-1 -1
|
|
-3 -3
|
|
-4 -4
|
|
-6 -6
|
|
-7 -7
|
|
-9 negative nine
|
|
31 31
|
|
33 33
|
|
331 331
|
|
333 333
|
|
3331 3331
|
|
3333 3333
|
|
3334 3334
|
|
3336 3336
|
|
3337 3337
|
|
3339 Three thousand three hundred thirty nine
|
|
334 334
|
|
336 336
|
|
337 337
|
|
339 Three hundred thirty nine
|
|
34 34
|
|
36 36
|
|
37 37
|
|
39 Thirty nine
|
|
SELECT * FROM t1 PARTITION (p2);
|
|
a b
|
|
DROP TABLE t1;
|
|
#
|
|
# Test explicit partition selection on a non partitioned temp table
|
|
#
|
|
CREATE TEMPORARY TABLE t1 (a INT);
|
|
SELECT * FROM t1 PARTITION(pNonexisting);
|
|
ERROR HY000: PARTITION () clause on non partitioned table
|
|
DROP TEMPORARY TABLE t1;
|
|
#
|
|
# Test CREATE LIKE does not take PARTITION clause
|
|
#
|
|
CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3;
|
|
CREATE TABLE t2 LIKE t1 PARTITION (p0, p2);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION (p0, p2)' at line 1
|
|
DROP TABLE t1;
|
|
DROP TABLE test.thread_to_monitor;
|
|
SET @@default_storage_engine = @old_default_storage_engine;
|