diff options
Diffstat (limited to 'mysql-test/main/partition_explicit_prune.test')
| -rw-r--r-- | mysql-test/main/partition_explicit_prune.test | 860 |
1 files changed, 860 insertions, 0 deletions
diff --git a/mysql-test/main/partition_explicit_prune.test b/mysql-test/main/partition_explicit_prune.test new file mode 100644 index 00000000000..68b829fbcc3 --- /dev/null +++ b/mysql-test/main/partition_explicit_prune.test @@ -0,0 +1,860 @@ +--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; |