EVOLUTION-MANAGER

Edit File: 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;