summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/partition_explicit_prune.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/partition_explicit_prune.result')
-rw-r--r--mysql-test/main/partition_explicit_prune.result2010
1 files changed, 2010 insertions, 0 deletions
diff --git a/mysql-test/main/partition_explicit_prune.result b/mysql-test/main/partition_explicit_prune.result
new file mode 100644
index 00000000..8b49210d
--- /dev/null
+++ b/mysql-test/main/partition_explicit_prune.result
@@ -0,0 +1,2010 @@
+#
+# 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`) latin1 latin1_swedish_ci
+FLUSH STATUS;
+SELECT * FROM v1;
+a
+0
+2
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_RND_NEXT 3
+HANDLER_TMP_WRITE 24
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+SELECT a FROM t1 PARTITION (p0);
+a
+0
+2
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_RND_NEXT 3
+HANDLER_TMP_WRITE 24
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO v1 VALUES (10);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_TMP_WRITE 24
+# 2 locks (1 table, all partitions pruned)
+FLUSH STATUS;
+SELECT * FROM v1;
+a
+0
+10
+2
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_RND_NEXT 4
+HANDLER_TMP_WRITE 24
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+SELECT a FROM t1 PARTITION (p0);
+a
+0
+10
+2
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_RND_NEXT 4
+HANDLER_TMP_WRITE 24
+# 4 locks (1 table, 1 partition lock/unlock)
+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;
+FLUSH STATUS;
+INSERT INTO v1 VALUES (20);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_TMP_WRITE 24
+# 2 locks (1 table, all partitions pruned)
+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;
+FLUSH STATUS;
+INSERT INTO v1 VALUES (30);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# 4 locks (1 table, 1 partition lock/unlock)
+FLUSH STATUS;
+INSERT INTO v1 VALUES (31);
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+# 2 locks (1 table, all partitions pruned)
+FLUSH STATUS;
+INSERT INTO v1 VALUES (32);
+ERROR 44000: CHECK OPTION failed `test`.`v1`
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+# 4 locks (1 table, 1 partition lock/unlock)
+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.
+# embedded uses MyISAM as default. 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_TMP_WRITE 24
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+# 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
+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 COLLATE=latin1_swedish_ci
+ 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 MariaDB 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 MariaDB 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)");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_KEY 6
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# Should be 1 commit
+# 4 external locks (due to pruning of locks)
+# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
+# and 18 write (1 ha_innobase + 17 internal I_S write)
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)");
+INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0");
+# 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");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# Should be 1 commit
+# 4 external locks
+# (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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# should be 1 commit
+# 9 locks (1 ha_partition + 8 ha_innobase)
+# 17 writes (internal I_S)
+INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3");
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+HANDLER_WRITE 2
+# + 1 commit
+# + 19 rnd next (internal I_S)
+# + 19 write (18 internal I_S + 1 insert)
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_RND_NEXT 56
+HANDLER_TMP_WRITE 78
+HANDLER_WRITE 2
+# + 9 locks (unlocks)
+# + 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 Engine-independent statistics collected
+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'
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_TMP_WRITE 24
+# should have failed before locking (only 17 internal I_S writes)
+FLUSH STATUS;
+SELECT * FROM t1 PARTITION (subp2);
+a b
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 1
+HANDLER_TMP_WRITE 24
+# Should be 1 commit
+# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
+# 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)
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 3
+HANDLER_READ_NEXT 3
+HANDLER_TMP_WRITE 24
+# Should be 1 commit
+# 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2
+# 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 1 commit
+# 18 locks
+# 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_FIRST 1
+HANDLER_READ_NEXT 3
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+# + 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_READ_FIRST 3
+HANDLER_READ_NEXT 4
+HANDLER_READ_RND_NEXT 56
+HANDLER_TMP_WRITE 78
+# + 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 4
+HANDLER_READ_FIRST 3
+HANDLER_READ_NEXT 4
+HANDLER_READ_RND_NEXT 84
+HANDLER_TMP_WRITE 105
+# No matching partition, only internal I_S.
+SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
+a b
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 5
+HANDLER_READ_FIRST 3
+HANDLER_READ_NEXT 4
+HANDLER_READ_RND_NEXT 112
+HANDLER_TMP_WRITE 132
+# + 18 for unlock (same as lock above) (100 is not in pNeg, no match)
+# Test that EXPLAIN PARTITION works
+EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent);
+ERROR HY000: Unknown partition 'pNonexistent' in table 't1'
+EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p0-9_subp2 index NULL b 71 NULL 2 Using index
+FLUSH STATUS;
+EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE TableAlias pNeg_subp0,pNeg_subp1,p0-9_subp2 index NULL b 71 NULL 4 Using index
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock)
+EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE TableAlias p0-9_subp3 index NULL b 71 NULL 3 Using index
+EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p10-99_subp4,p10-99_subp5 index NULL PRIMARY 4 NULL 2 Using index
+EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1000000;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+# 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 MariaDB server version for the right syntax to use near '' at line 1
+SELECT * FROM t1 `PARTITION`;
+a b
+-2 (pNeg-)subp0
+5 p0-9:subp3
+10 p10-99
+-3 pNeg(-subp1)
+-1 pNeg(-subp1)
+1 subp3
+3 subp3
+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 MariaDB server version for the right syntax to use near 'PARTITION' at line 1
+SELECT * FROM t1 AS `PARTITION`;
+a b
+-2 (pNeg-)subp0
+5 p0-9:subp3
+10 p10-99
+-3 pNeg(-subp1)
+-1 pNeg(-subp1)
+1 subp3
+3 subp3
+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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 1
+# 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443
+# 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');
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 2
+# 1 commit
+# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
+# 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');
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_KEY 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 2
+HANDLER_WRITE 2
+# 1 commit
+# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
+# 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 1 commit
+# 9 locks
+# 17 write (internal I_S)
+DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE";
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_DELETE 2
+HANDLER_READ_KEY 1
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+# + 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_DELETE 2
+HANDLER_READ_KEY 1
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 56
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 78
+HANDLER_WRITE 1
+# 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');
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_DELETE 2
+HANDLER_READ_KEY 1
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 84
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 105
+HANDLER_WRITE 3
+# + 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');
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 4
+HANDLER_DELETE 2
+HANDLER_READ_KEY 2
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 112
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 132
+HANDLER_UPDATE 2
+HANDLER_WRITE 5
+# + 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 5
+HANDLER_DELETE 2
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_NEXT 4
+HANDLER_READ_RND_NEXT 140
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 159
+HANDLER_UPDATE 2
+HANDLER_WRITE 5
+# + 1 commit
+# + 1 read first
+# + 2 read key
+# + 3 read next
+# + 19 rnd next (internal I_S)
+# + 18 write (internal I_S)
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 5
+HANDLER_DELETE 2
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_NEXT 4
+HANDLER_READ_RND_NEXT 168
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 186
+HANDLER_UPDATE 2
+HANDLER_WRITE 5
+# + 9 locks
+# + 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
+10 p10-99
+-3 pNeg(-subp1)
+-1 pNeg(-subp1)
+-21 REPLACEd by REPLACE
+FLUSH STATUS;
+SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt';
+Warnings:
+Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 4
+HANDLER_READ_NEXT 5
+HANDLER_TMP_WRITE 24
+# 1 commit
+# 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock)
+# 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`;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 10 locks (table + 4 partition) x (lock + unlock)
+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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 3
+# 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock)
+# 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);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 10
+# 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock)
+ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
+FLUSH STATUS;
+LOCK TABLE t1 WRITE;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 9 locks
+# 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`);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+HANDLER_WRITE 10
+# + 23 write (18 internal I_S + 5 rows)
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_RND_NEXT 56
+HANDLER_TMP_WRITE 78
+HANDLER_WRITE 10
+# + 9 locks
+#
+# Test UPDATE
+#
+FLUSH STATUS;
+UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_KEY 6
+HANDLER_READ_RND_NEXT 2
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 2
+# 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)
+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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_KEY 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 2
+# 1 commit
+# 4 lock
+# 1 read key
+# 1 update
+FLUSH STATUS;
+UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2')
+WHERE a = -2;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_KEY 1
+HANDLER_READ_RND 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 2
+# 1 commit
+# 4 lock
+# 2 read key - (2 index read)
+# 1 read rnd - rnd_pos
+# 1 update
+FLUSH STATUS;
+UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# Nothing, since impossible PARTITION+WHERE clause.
+FLUSH STATUS;
+UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100')
+WHERE a = 100;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# Nothing, since impossible PARTITION+WHERE clause.
+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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_READ_KEY 1
+HANDLER_READ_RND 1
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_READ_KEY 1
+HANDLER_READ_RND 1
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 1
+HANDLER_WRITE 1
+# 10 locks
+# 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 1
+HANDLER_READ_KEY 1
+HANDLER_READ_RND 1
+HANDLER_TMP_WRITE 24
+HANDLER_UPDATE 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# Nothing (no matching partition found)
+FLUSH STATUS;
+UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# Nothing (no matching partition found)
+FLUSH STATUS;
+LOCK TABLE t1 WRITE;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 9 locks
+UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_KEY 1
+HANDLER_READ_RND 1
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+HANDLER_UPDATE 2
+# + 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_DELETE 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND 2
+HANDLER_READ_RND_NEXT 56
+HANDLER_TMP_WRITE 78
+HANDLER_UPDATE 3
+HANDLER_WRITE 1
+# + 1 delete
+# + 4 read key
+# + 1 read rnd
+# + 19 write (18 internal I_S + 1 insert)
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_DELETE 1
+HANDLER_READ_KEY 2
+HANDLER_READ_RND 2
+HANDLER_READ_RND_NEXT 84
+HANDLER_TMP_WRITE 105
+HANDLER_UPDATE 3
+HANDLER_WRITE 1
++ 9 locks
+#
+# 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 2
+HANDLER_READ_KEY 1
+HANDLER_TMP_WRITE 24
+# 1 delete
+# 4 locks (pruning works!).
+# 1 read key (index read)
+FLUSH STATUS;
+DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%';
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 2
+HANDLER_READ_RND_NEXT 3
+HANDLER_TMP_WRITE 24
+# 1 delete
+# 4 locks
+# 1 read first
+# 2 read key
+# 3 read rnd
+FLUSH STATUS;
+LOCK TABLE t1 WRITE;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 9 locks
+DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3';
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 2
+HANDLER_READ_KEY 1
+HANDLER_READ_RND_NEXT 28
+HANDLER_TMP_WRITE 51
+# + 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';
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_DELETE 2
+HANDLER_READ_KEY 3
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 56
+HANDLER_TMP_WRITE 78
+# + 1 delete
+# + 6 read key (same as above, but for two subpartitions)
+# + 1 read next (read next after found row)
+UNLOCK TABLES;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 3
+HANDLER_DELETE 2
+HANDLER_READ_KEY 3
+HANDLER_READ_NEXT 1
+HANDLER_READ_RND_NEXT 84
+HANDLER_TMP_WRITE 105
+# + 9 locks
+# Test multi-table DELETE
+# Can be expressed in two different ways.
+CREATE TABLE t2 LIKE t1;
+FLUSH STATUS;
+INSERT INTO t2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 5
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 5
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 10
+# 24 locks (2 table, 5 + 5 subpartitions lock/unlock)
+FLUSH STATUS;
+ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 14 locks (1 table, 6 subpartitions lock/unlock)
+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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_READ_FIRST 5
+HANDLER_READ_KEY 6
+HANDLER_ROLLBACK 1
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 1
+# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock)
+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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 5
+HANDLER_READ_NEXT 5
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 7
+# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock)
+TRUNCATE TABLE t2;
+FLUSH STATUS;
+INSERT INTO t2 SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 5
+HANDLER_READ_KEY 6
+HANDLER_READ_NEXT 5
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 10
+# 30 locks (2 table, 8 + 5 subpartitions lock/unlock)
+FLUSH STATUS;
+CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`);
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_FIRST 5
+HANDLER_READ_NEXT 7
+HANDLER_TMP_WRITE 24
+HANDLER_WRITE 7
+# 14 locks (2 table, 5 subpartitions lock/unlock)
+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 COLLATE=latin1_swedish_ci
+ 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
+10 p10-99
+-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
+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 COLLATE=latin1_swedish_ci
+ 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
+10 p10-99
+1 subp3
+3 subp3
+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) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SELECT * FROM t3;
+a b
+-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2
+-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 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 MariaDB 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
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_TMP_WRITE 24
+# 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_READ_RND_NEXT 3
+HANDLER_TMP_WRITE 24
+# 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock)
+# 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)
+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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_DELETE 6
+HANDLER_READ_FIRST 1
+HANDLER_READ_KEY 2
+HANDLER_READ_NEXT 2
+HANDLER_READ_RND 4
+HANDLER_READ_RND_NEXT 16
+HANDLER_TMP_WRITE 24
+# 4 delete (2 in t2 + 2 in t3)
+# 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock)
+# 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 MariaDB 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;
+SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
+WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;
+VARIABLE_NAME VARIABLE_VALUE
+HANDLER_COMMIT 1
+HANDLER_TMP_WRITE 24
+# 6 locks (lock/unlock two subpartitions + table)
+# 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
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (id) (
+PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
+PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
+PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
+PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
+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`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+PARTITION BY RANGE (id) (
+PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM,
+PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM,
+PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM,
+PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM);
+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
+) ENGINE=MyISAM 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)
+);
+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
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ;
+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
+)
+);
+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 partitions select * from t1 where a=1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 partition (p1) where a=1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 partition (p1) where a=1 or a=2;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where
+explain partitions select * from t1 partition (p2) where a=1;
+id select_type table partitions type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+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=latin1 COLLATE=latin1_swedish_ci
+ 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 MariaDB server version for the right syntax to use near 'PARTITION (p0, p2)' at line 1
+DROP TABLE t1;
+SET @@default_storage_engine = @old_default_storage_engine;
+#
+# MDEV-14815 - Server crash or AddressSanitizer errors or valgrind warnings in thr_lock / has_old_lock upon FLUSH TABLES
+#
+CREATE TABLE t1 (i INT) ENGINE=MEMORY PARTITION BY RANGE (i) (PARTITION p0 VALUES LESS THAN (4), PARTITION pm VALUES LESS THAN MAXVALUE);
+CREATE TABLE t2 (i INT) ENGINE=MEMORY;
+LOCK TABLE t1 WRITE, t2 WRITE;
+SELECT * FROM t1 PARTITION (p0);
+i
+FLUSH TABLES;
+SELECT * FROM t1 PARTITION (p0);
+i
+ALTER TABLE t1 TRUNCATE PARTITION p0;
+SELECT * FROM t1 PARTITION (p0);
+i
+ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
+SELECT * FROM t1 PARTITION (p0);
+i
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+#
+# MDEV-18371 Server crashes in ha_innobase::cmp_ref upon UPDATE with PARTITION clause.
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=InnoDB PARTITION BY KEY(b) PARTITIONS 4;
+INSERT INTO t1 VALUES (3,0),(8,2),(7,8),(3,4),(2,4),(0,7),(4,3),(3,6);
+FLUSH TABLES;
+UPDATE t1 PARTITION (p3,p1) SET a = 2 WHERE a = 3;
+SELECT * FROM t1;
+a b
+2 0
+7 8
+2 4
+2 4
+0 7
+4 3
+8 2
+2 6
+DROP TABLE t1;
+#
+# MDEV-21134 Crash with partitioned table, PARTITION syntax, and index_merge.
+#
+create table t1 (
+pk int primary key,
+a int,
+b int,
+filler char(32),
+key (a),
+key (b)
+) engine=myisam partition by range(pk) (
+partition p0 values less than (10),
+partition p1 values less than MAXVALUE
+) ;
+insert into t1 select
+seq,
+MOD(seq, 100),
+MOD(seq, 100),
+'filler-data-filler-data'
+ from
+seq_1_to_5000;
+explain select * from t1 partition (p1) where a=10 and b=10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
+flush tables;
+select * from t1 partition (p1)where a=10 and b=10;
+pk a b filler
+10 10 10 filler-data-filler-data
+110 10 10 filler-data-filler-data
+210 10 10 filler-data-filler-data
+310 10 10 filler-data-filler-data
+410 10 10 filler-data-filler-data
+510 10 10 filler-data-filler-data
+610 10 10 filler-data-filler-data
+710 10 10 filler-data-filler-data
+810 10 10 filler-data-filler-data
+910 10 10 filler-data-filler-data
+1010 10 10 filler-data-filler-data
+1110 10 10 filler-data-filler-data
+1210 10 10 filler-data-filler-data
+1310 10 10 filler-data-filler-data
+1410 10 10 filler-data-filler-data
+1510 10 10 filler-data-filler-data
+1610 10 10 filler-data-filler-data
+1710 10 10 filler-data-filler-data
+1810 10 10 filler-data-filler-data
+1910 10 10 filler-data-filler-data
+2010 10 10 filler-data-filler-data
+2110 10 10 filler-data-filler-data
+2210 10 10 filler-data-filler-data
+2310 10 10 filler-data-filler-data
+2410 10 10 filler-data-filler-data
+2510 10 10 filler-data-filler-data
+2610 10 10 filler-data-filler-data
+2710 10 10 filler-data-filler-data
+2810 10 10 filler-data-filler-data
+2910 10 10 filler-data-filler-data
+3010 10 10 filler-data-filler-data
+3110 10 10 filler-data-filler-data
+3210 10 10 filler-data-filler-data
+3310 10 10 filler-data-filler-data
+3410 10 10 filler-data-filler-data
+3510 10 10 filler-data-filler-data
+3610 10 10 filler-data-filler-data
+3710 10 10 filler-data-filler-data
+3810 10 10 filler-data-filler-data
+3910 10 10 filler-data-filler-data
+4010 10 10 filler-data-filler-data
+4110 10 10 filler-data-filler-data
+4210 10 10 filler-data-filler-data
+4310 10 10 filler-data-filler-data
+4410 10 10 filler-data-filler-data
+4510 10 10 filler-data-filler-data
+4610 10 10 filler-data-filler-data
+4710 10 10 filler-data-filler-data
+4810 10 10 filler-data-filler-data
+4910 10 10 filler-data-filler-data
+DROP TABLE t1;
+#
+# End of 10.3 tests
+#
+#
+# MDEV-18982: INSERT using explicit patition pruning with column list
+#
+create table t1 (a int) partition by hash(a);
+insert into t1 partition (p0) (a) values (1);
+select * from t1;
+a
+1
+drop table t1;
+#
+# End of 10.4 tests
+#
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-23 12:54:15 +0000