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;