diff options
author | Michael Widenius <monty@mariadb.org> | 2018-03-09 14:05:35 +0200 |
---|---|---|
committer | Monty <monty@mariadb.org> | 2018-03-29 13:59:44 +0300 |
commit | a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 (patch) | |
tree | 70eb743fa965a17380bbc0ac88ae79ca1075b896 /mysql-test/t/partition_explicit_prune.test | |
parent | ab1941266c59a19703a74b5593cf3f508a5752d7 (diff) | |
download | mariadb-git-a7abddeffa6a760ce948c2dfb007cdf3f1a369d5.tar.gz |
Create 'main' test directory and move 't' and 'r' there
Diffstat (limited to 'mysql-test/t/partition_explicit_prune.test')
-rw-r--r-- | mysql-test/t/partition_explicit_prune.test | 860 |
1 files changed, 0 insertions, 860 deletions
diff --git a/mysql-test/t/partition_explicit_prune.test b/mysql-test/t/partition_explicit_prune.test deleted file mode 100644 index 68b829fbcc3..00000000000 --- a/mysql-test/t/partition_explicit_prune.test +++ /dev/null @@ -1,860 +0,0 @@ ---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; |