path: root/mysql-test/main/partition_explicit_prune.test

--source include/have_innodb.inc
--source include/have_partition.inc

# Helper statement
let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0;

--echo #
--echo # Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS
--echo #
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;
FLUSH STATUS;
--sorted_result
SELECT * FROM v1;
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
--sorted_result
SELECT a FROM t1 PARTITION (p0);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
INSERT INTO v1 VALUES (10);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
# --error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
# INSERT INTO v1 VALUES (11);
eval $get_handler_status_counts;
--echo # 2 locks (1 table, all partitions pruned)
FLUSH STATUS;
--sorted_result
SELECT * FROM v1;
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
--sorted_result
SELECT a FROM t1 PARTITION (p0);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
--sorted_result
SELECT * FROM t1;
DROP VIEW v1;
CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION;
FLUSH STATUS;
INSERT INTO v1 VALUES (20);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
# --error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
# INSERT INTO v1 VALUES (21);
eval $get_handler_status_counts;
--echo # 2 locks (1 table, all partitions pruned)
--sorted_result
SELECT * FROM v1;
--sorted_result
SELECT * FROM t1;
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);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
FLUSH STATUS;
--error ER_VIEW_CHECK_FAILED
INSERT INTO v1 VALUES (31);
eval $get_handler_status_counts;
--echo # 2 locks (1 table, all partitions pruned)
FLUSH STATUS;
--error ER_VIEW_CHECK_FAILED
INSERT INTO v1 VALUES (32);
eval $get_handler_status_counts;
--echo # 4 locks (1 table, 1 partition lock/unlock)
--sorted_result
SELECT * FROM v1;
--sorted_result
SELECT * FROM t1;
DROP VIEW v1;
DROP TABLE t1;

--echo # Original tests for WL#5217

--echo # Must have InnoDB as engine to get the same statistics results.
--echo # embedded uses MyISAM as default. CREATE SELECT uses the default engine.
SET @old_default_storage_engine = @@default_storage_engine;
SET @@default_storage_engine = 'InnoDB';

--let $MYSQLD_DATADIR= `SELECT @@datadir`

--echo # Test to show if I_S affects HANDLER_ counts
FLUSH STATUS;
eval $get_handler_status_counts;
eval $get_handler_status_counts;
--echo # OK, seems to add number of variables processed before HANDLER_WRITE
--echo # 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;
--echo # First test that the syntax is OK
--error ER_PARSE_ERROR
SHOW CREATE TABLE t1 PARTITION (subp0);
--echo # Not a correct partition list
--error ER_PARSE_ERROR
INSERT INTO t1 PARTITION () VALUES (1, "error");
--error ER_UNKNOWN_PARTITION
INSERT INTO t1 PARTITION (pNonExisting) VALUES (1, "error");
--error ER_UNKNOWN_PARTITION
INSERT INTO t1 PARTITION (pNeg, pNonExisting) VALUES (1, "error");
--echo # Duplicate partitions and overlapping partitions and subpartitios is OK
FLUSH STATUS;
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)");
eval $get_handler_status_counts;
--echo # Should be 1 commit
--echo # 4 external locks (due to pruning of locks)
--echo # (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
--echo # 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");

--echo # 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");
eval $get_handler_status_counts;
--echo # Should be 1 commit
--echo # 4 external locks
--echo # (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
--echo # and 18 write (1 ha_innobase + 17 internal I_S write)
FLUSH STATUS;
LOCK TABLE t1 WRITE;
eval $get_handler_status_counts;
--echo # should be 1 commit
--echo # 9 locks (1 ha_partition + 8 ha_innobase)
--echo # 17 writes (internal I_S)
INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3");
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 19 rnd next (internal I_S)
--echo # + 19 write (18 internal I_S + 1 insert)
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo # + 9 locks (unlocks)
--echo # + 19 rnd next (internal I_S)
--echo # + 18 write (internal I_S)

--echo # Not matching partitions with inserted value
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (1, "error");
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (1, "error");
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1, "error"), (10, "error");
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
INSERT INTO t1 VALUES (1000000, "error"), (9999999, "error");
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1000000, "error"), (9999999, "error");
--error ER_DUP_ENTRY,ER_DUP_KEY
INSERT INTO t1 PARTITION (pNeg, subp4) VALUES (-7, "pNeg(-subp1)"), (-10, "pNeg(-subp0)"), (-1, "pNeg(-subp1)"), (-99, "pNeg(-subp1)");

SELECT * FROM t1 ORDER BY a;
ANALYZE TABLE t1;

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;
SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata;

FLUSH STATUS;
--error ER_UNKNOWN_PARTITION
SELECT * FROM t1 PARTITION (pNonexistent);
eval $get_handler_status_counts;
--echo # should have failed before locking (only 17 internal I_S writes)
FLUSH STATUS;
SELECT * FROM t1 PARTITION (subp2);
eval $get_handler_status_counts;
--echo # Should be 1 commit
--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
--echo # 1 read first (also calls index_read)
--echo # 2 read key (first from innobase_get_index and second from index first)
--echo # 17 writes (internal I_S)
FLUSH STATUS;
SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
eval $get_handler_status_counts;
--echo # Should be 1 commit
--echo # 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2
--echo # 3 read first (one for each partition)
--echo # 6 read key (3 from read first and 3 from innobase_get_index)
--echo # 3 read next (one next call after each read row)
--echo # 17 writes (internal I_S)
FLUSH STATUS;
LOCK TABLE t1 READ, t1 as TableAlias READ;
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 18 locks
--echo # 18 READ KEY from opening a new partition table instance,
--echo # (1 innobase_get_index for each index, per partition, 1 x 2 x 8 = 16
--echo #  + info(HA_STATUS_CONST) call on the partition with the most number
--echo #  of rows, 2 innobase_get_index for updating both index statistics)
--echo # 17 writes (internal I_S)
SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 1 read first (read first key from index in one partition)
--echo # + 2 read key (innobase_get_index from index_init + from index_first)
--echo # + 3 read next (one after each row)
--echo # + 19 rnd next (from the last I_S query)
--echo # + 18 write (internal I_S)
SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 2 read first (one for each subpart)
--echo # + 4 read key (innobase_get_index from index_init + from index_first)
--echo # + 1 read next (one after each row)
--echo # + 19 rnd next (from the last I_S query)
--echo # + 18 write (internal I_S)
SELECT * FROM t1 WHERE a = 1000000;
eval $get_handler_status_counts;
--echo # No matching partition, only internal I_S.
SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo # + 18 for unlock (same as lock above) (100 is not in pNeg, no match)

--echo # Test that EXPLAIN PARTITION works
--error ER_UNKNOWN_PARTITION
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent);
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2);
FLUSH STATUS;
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias;
eval $get_handler_status_counts;
--echo # 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock)
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias;
EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 PARTITION (`p10-99`);
EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1000000;
EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100;

--echo # Test how it changes the alias/keywords/reserved words
--error ER_PARSE_ERROR
SELECT * FROM t1 PARTITION;
SELECT * FROM t1 `PARTITION`;
--error ER_PARSE_ERROR
SELECT * FROM t1 AS PARTITION;
SELECT * FROM t1 AS `PARTITION`;

--echo #
--echo # Test REPLACE
--echo #
FLUSH STATUS;
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
eval $get_handler_status_counts;
--echo # 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443
--echo # explicit pruning says part_id 0 and implicit pruning says part_id 1
--echo # so no partition will be locked!
--echo # 0 rollback (since no locked partition)
--echo # 17 writes (I_S internal)
FLUSH STATUS;
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'Insert by REPLACE');
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
--echo # 18 writes (17 I_S internal, 1 ha_innobase)
SELECT * FROM t1 PARTITION (pNeg);
FLUSH STATUS;
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock)
--echo # 2 read key (1 innobase_get_index when init the index + 1 index read
--echo # to get the position to update)
--echo # 1 update (updated one row, since there is no delete trigger, update
--echo # is used instead of delete+insert)
--echo # 18 write (17 from I_S, 1 for the failed insert)
SELECT * FROM t1 PARTITION (pNeg);
FLUSH STATUS;
LOCK TABLE t1 WRITE;
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 9 locks
--echo # 17 write (internal I_S)
DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE";
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 1 delete (one row deleted)
--echo # + 3 read key (1 innodb_get_index in records_in_range,
--echo #   1 innodb_get_index in index_init, 1 index_read in index_read_first)
--echo # + 1 read next (search for another row in secondary index)
--echo # + 19 rnd next (internal I_S)
--echo # + 18 write (internal I_S)
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!');
eval $get_handler_status_counts;
--echo # Failed before start_stmt/execution.
--echo # + 19 rnd next (internal I_S)
--echo #   0 rollback (No partition had called start_stmt, all parts pruned)
--echo # + 18 write (internal I_S)
REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE');
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 19 rnd next (internal I_S)
--echo # + 19 write (18 internal I_S + 1 real write)
REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE');
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 2 read key (see non locked query)
--echo # + 19 rnd next (internal I_S)
--echo # + 1 update (see non locked query)
--echo # + 19 write (18 internal I_S + 1 failed write)
SELECT * FROM t1 PARTITION (subp1);
eval $get_handler_status_counts;
--echo # + 1 commit
--echo # + 1 read first 
--echo # + 2 read key 
--echo # + 3 read next
--echo # + 19 rnd next (internal I_S)
--echo # + 18 write (internal I_S)
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo # + 9 locks
--echo # + 19 rnd next (internal I_S)
--echo # + 18 write (internal I_S)

--echo #
--echo # Test LOAD
--echo #
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
FLUSH STATUS;
SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt';
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock)
--echo # 4 read first (for reading the first row in 4 partitions)
--echo # 8 read key (4 from read first + 4 for index init)
--echo # 5 read next (one after each row)
--echo # 17 write (internal I_S)
FLUSH STATUS;
ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`;
eval $get_handler_status_counts;
--echo # 10 locks (table + 4 partition) x (lock + unlock)
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
FLUSH STATUS;
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg);
eval $get_handler_status_counts;
--echo # 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock)
--echo # 1 rollback
SELECT * FROM t1 PARTITION (pNeg, `p10-99`);
FLUSH STATUS;
LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, subp4, subp5);
eval $get_handler_status_counts;
--echo # 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;
eval $get_handler_status_counts;
--echo # 9 locks
--echo # 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`);
eval $get_handler_status_counts;
--echo # + 23 write (18 internal I_S + 5 rows)
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo # + 9 locks
--remove_file $MYSQLD_DATADIR/test/loadtest.txt

--echo #
--echo # Test UPDATE
--echo #
FLUSH STATUS;
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock)
--echo # 1 read first (read first row, called from first rnd_next)
--echo # 2 read key (innobase_get_index from rnd_init +
--echo #             read next row from second rnd_next)
--echo # 1 update (update the row)
# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated');
SELECT * FROM t1 PARTITION (subp0) ORDER BY a;
FLUSH STATUS;
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 4 lock
--echo # 1 read key
--echo # 1 update
# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2;
FLUSH STATUS;
UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2')
WHERE a = -2;
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 4 lock
--echo # 2 read key - (2 index read)
--echo # 1 read rnd - rnd_pos
--echo # 1 update
# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2');
FLUSH STATUS;
UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
eval $get_handler_status_counts;
--echo # Nothing, since impossible PARTITION+WHERE clause.
# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100;
FLUSH STATUS;
UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100')
WHERE a = 100;
eval $get_handler_status_counts;
--echo # Nothing, since impossible PARTITION+WHERE clause.
# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0)
# SET a = -2, b = concat(b, ', Updated from a = 100') WHERE a = 100;
FLUSH STATUS;
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100')
WHERE a = 100;
eval $get_handler_status_counts;
--echo # 6 lock
--echo # 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos)
--echo # 1 read rnd (rnd pos)
--echo # 1 rollback
FLUSH STATUS;
--error ER_DUP_ENTRY,ER_DUP_KEY
UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -4, b = concat(b, ', Updated from a = 100')
WHERE a = 100;
eval $get_handler_status_counts;
--echo # 10 locks
--echo # 4 read key
--echo # 1 read rnd
--echo # 1 rollback
--echo # 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;
eval $get_handler_status_counts;
--echo # 1 commit
--echo # 1 delete
--echo # 4 read key
--echo # 1 read rnd
--echo # 18 write (17 internal I_S + 1 insert)

SELECT * FROM t1 ORDER BY a;
--echo # Test of non matching partition (i.e ER_NO_PARTITION_FOUND)
FLUSH STATUS;
UPDATE t1 SET b = concat(b, ', Updated2') WHERE a = 1000000;
eval $get_handler_status_counts;
--echo # Nothing (no matching partition found)
FLUSH STATUS;
UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000;
eval $get_handler_status_counts;
--echo # Nothing (no matching partition found)
FLUSH STATUS;
LOCK TABLE t1 WRITE;
eval $get_handler_status_counts;
--echo # 9 locks
UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101;
eval $get_handler_status_counts;
--echo # + 4 read key
--echo # + 1 read rnd
--echo # + 1 update
UPDATE t1 PARTITION (`p100-99999`) SET b = concat(b, ', Updated to 110'), a = 110 WHERE a = 103;
eval $get_handler_status_counts;
--echo # + 1 delete
--echo # + 4 read key
--echo # + 1 read rnd
--echo # + 19 write (18 internal I_S + 1 insert)
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo + 9 locks

--echo #
--echo # Test DELETE
--echo #
SELECT * FROM t1 ORDER BY b, a;
FLUSH STATUS;
DELETE FROM t1 PARTITION (pNeg) WHERE a = -1;
eval $get_handler_status_counts;
--echo # 1 delete
--echo # 4 locks (pruning works!).
--echo # 1 read key (index read)
FLUSH STATUS;
DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%';
eval $get_handler_status_counts;
--echo # 1 delete
--echo # 4 locks
--echo # 1 read first
--echo # 2 read key
--echo # 3 read rnd
FLUSH STATUS;
LOCK TABLE t1 WRITE;
eval $get_handler_status_counts;
--echo # 9 locks
DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3';
eval $get_handler_status_counts;
--echo # + 3 read key (1 innodb_get_index in records_in_range
--echo #               + 1 innobase_get_index in index_init + 1 index read)
DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3';
eval $get_handler_status_counts;
--echo # + 1 delete
--echo # + 6 read key (same as above, but for two subpartitions)
--echo # + 1 read next (read next after found row)
UNLOCK TABLES;
eval $get_handler_status_counts;
--echo # + 9 locks

--echo # Test multi-table DELETE
--echo # 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`);
eval $get_handler_status_counts;
--echo # 24 locks (2 table, 5 + 5 subpartitions lock/unlock)
FLUSH STATUS;
ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`;
eval $get_handler_status_counts;
--echo # 14 locks (1 table, 6 subpartitions lock/unlock)
FLUSH STATUS;
--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`);
eval $get_handler_status_counts;
--echo # 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`);
eval $get_handler_status_counts;
--echo # 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`);
eval $get_handler_status_counts;
--echo # 30 locks (2 table, 8 + 5 subpartitions lock/unlock)
FLUSH STATUS;
CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`);
eval $get_handler_status_counts;
--echo # 14 locks (2 table, 5 subpartitions lock/unlock)
SHOW CREATE TABLE t1;
SELECT * FROM t1;
SHOW CREATE TABLE t2;
SELECT * FROM t2;
SHOW CREATE TABLE t3;
SELECT * FROM t3;
FLUSH STATUS;
--error ER_PARSE_ERROR
DELETE t1 PARTITION (pNeg), t3 FROM t1, t3
WHERE t1.a = t3.a AND t3.b = 'subp3';
eval $get_handler_status_counts;
--echo # 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;
eval $get_handler_status_counts;
--echo # 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock)
--echo # 1 read first (first rnd_next in t2)
--echo # 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2
--echo #             + 2 innodb_get_index in index_init in t1)
--echo # 3 read rnd next (3 rnd next in t2, 2 rows + 1 empty) 
--echo # 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;
eval $get_handler_status_counts;
--echo # 4 delete (2 in t2 + 2 in t3)
--echo # 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock)
--echo # 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1)
--echo # 17 read key (1 index_init in t1 + 1 read first in t1 + 
--echo #              2 index_init in t2 + 1 index read in t2 +
--echo #              1 index_init in t3 + 1 index read in t3 +
--echo #              1 index read in t2 +
--echo #              1 index_init in t3 + 1 index read in t3 +
--echo #              2 index_init in t2 + 2 index read in t2 (from rnd_pos)
--echo #              1 index_init in t3 + 2 index read in t3 (from rnd_pos))
--echo # 2 read next (1 in t1 + 1 in t1, second row)
--echo # 4 read rnd (position on 4 found rows to delete)
--echo # 16 rnd next (8 in t3 + 8 in t3, for second row)
SELECT * FROM t1 ORDER BY a;
SELECT * FROM t2 ORDER BY a;
SELECT * FROM t3 ORDER BY a;


--echo # Test TRUNCATE TABLE (should fail, since one should use
--echo # ALTER TABLE ... TRUNCATE PARTITION instead)
--error ER_PARSE_ERROR
TRUNCATE TABLE t1 PARTITION(`p10-99`);

--echo # Test of locking in TRUNCATE PARTITION
--echo # Note that it does not support truncating subpartitions
FLUSH STATUS;
ALTER TABLE t1 TRUNCATE PARTITION pNeg;
eval $get_handler_status_counts;
--echo # 6 locks (lock/unlock two subpartitions + table)

--echo # Test on non partitioned table
--error ER_PARTITION_CLAUSE_ON_NONPARTITIONED
SELECT * FROM t3 PARTITION (pNeg);

DROP TABLE t1, t2, t3;
#
--echo # Test from superseeded WL# 2682
# Partition select tests.
#
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--enable_warnings

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;
SELECT * FROM t1 PARTITION (p0);
SELECT * FROM t1 PARTITION (p1);
SELECT * FROM t1 PARTITION (p2);
SELECT * FROM t1 PARTITION (p3);
SELECT * FROM t1 PARTITION (p3) WHERE id = 2;
--error ER_UNKNOWN_PARTITION
SELECT * FROM t1 PARTITION (foo);

# now try indexes
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;
SELECT * FROM t2 PARTITION (p0);
SELECT * FROM t2 PARTITION (p1);
SELECT * FROM t2 PARTITION (p2);
SELECT * FROM t2 PARTITION (p3);
SELECT * FROM t2 PARTITION (p3) ORDER BY id;
SELECT * FROM t2 PARTITION (p3) WHERE id = 2;
--error ER_UNKNOWN_PARTITION
SELECT * FROM t2 PARTITION (foo);


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`;
SELECT * FROM `t3` PARTITION (p0);
SELECT * FROM `t3` PARTITION (p1);
SELECT * FROM `t3` PARTITION (p2);
SELECT * FROM `t3` PARTITION (p2) ORDER BY id;

DROP TABLE IF EXISTS `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;
# not sure how to do this, since names could be anything
#SELECT * FROM `t4` PARTITION (p0);
#SELECT * FROM `t4` PARTITION (p1);
#SELECT * FROM `t4` PARTITION (p2);
#SELECT * FROM `t4` PARTITION (p3);
#SELECT * FROM `t4` PARTITION (p3) 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`;
SELECT * FROM `t5` PARTITION(p0) ORDER BY id;
SELECT * FROM `t5` PARTITION(s0) ORDER BY id;
SELECT * FROM `t5` PARTITION(s1) ORDER BY id;
SELECT * FROM `t5` PARTITION(p1) ORDER BY id;
SELECT * FROM `t5` PARTITION(s2) ORDER BY id;
SELECT * FROM `t5` PARTITION(s3) ORDER BY id;
SELECT * FROM `t5` PARTITION(p2) ORDER BY id;
SELECT * FROM `t5` PARTITION(s4) ORDER BY id;
SELECT * FROM `t5` PARTITION(s5) ORDER BY id;

--disable_warnings
drop table t1,t2,t3,t4,t5;
--enable_warnings

# Tests for working together with partition pruning.
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;
explain partitions select * from t1 partition (p1) where a=1;
explain partitions select * from t1 partition (p1) where a=1 or a=2;
explain partitions select * from t1 partition (p2) where a=1;

drop table t1;

--echo #
--echo # Bug#59864: Crash if table empty: DELETE FROM t2 PARTITION (subp3).
--echo #
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));

eval SHOW CREATE TABLE t1;

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");

--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t1 PARTITION (subp3);

DELETE FROM t1 PARTITION (subp3);

--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t1 PARTITION (subp3);

DELETE FROM t1 PARTITION (`p0-29`);

--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t1 PARTITION (`p0-29`);

ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 3;

DELETE FROM t1 PARTITION (p2);

--sorted_result
SELECT * FROM t1;
--sorted_result
SELECT * FROM t1 PARTITION (p2);

DROP TABLE t1;

--echo #
--echo # Test explicit partition selection on a non partitioned temp table
--echo #
CREATE TEMPORARY TABLE t1 (a INT);
--error ER_PARTITION_CLAUSE_ON_NONPARTITIONED
SELECT * FROM t1 PARTITION(pNonexisting);
DROP TEMPORARY TABLE t1;

--echo #
--echo # Test CREATE LIKE does not take PARTITION clause
--echo #
CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3;
--error ER_PARSE_ERROR
CREATE TABLE t2 LIKE t1 PARTITION (p0, p2);
DROP TABLE t1;

SET @@default_storage_engine = @old_default_storage_engine;


--echo #
--echo # MDEV-14815 - Server crash or AddressSanitizer errors or valgrind warnings in thr_lock / has_old_lock upon FLUSH TABLES
--echo #
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);
FLUSH TABLES;
SELECT * FROM t1 PARTITION (p0);
ALTER TABLE t1 TRUNCATE PARTITION p0;
SELECT * FROM t1 PARTITION (p0);
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2;
SELECT * FROM t1 PARTITION (p0);
UNLOCK TABLES;

# Cleanup
DROP TABLE t1, t2;