1--source include/have_innodb.inc 2--source include/have_partition.inc 3 4# Helper statement 5let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 6WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 7 8--echo # 9--echo # Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS 10--echo # 11CREATE TABLE t1 (a int) 12ENGINE = InnoDB 13PARTITION BY HASH (a) PARTITIONS 2; 14INSERT INTO t1 VALUES (0), (1), (2), (3); 15CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0); 16SHOW CREATE VIEW v1; 17FLUSH STATUS; 18--sorted_result 19SELECT * FROM v1; 20eval $get_handler_status_counts; 21--echo # 4 locks (1 table, 1 partition lock/unlock) 22FLUSH STATUS; 23--sorted_result 24SELECT a FROM t1 PARTITION (p0); 25eval $get_handler_status_counts; 26--echo # 4 locks (1 table, 1 partition lock/unlock) 27FLUSH STATUS; 28INSERT INTO v1 VALUES (10); 29eval $get_handler_status_counts; 30--echo # 4 locks (1 table, 1 partition lock/unlock) 31FLUSH STATUS; 32# --error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 33# INSERT INTO v1 VALUES (11); 34eval $get_handler_status_counts; 35--echo # 2 locks (1 table, all partitions pruned) 36FLUSH STATUS; 37--sorted_result 38SELECT * FROM v1; 39eval $get_handler_status_counts; 40--echo # 4 locks (1 table, 1 partition lock/unlock) 41FLUSH STATUS; 42--sorted_result 43SELECT a FROM t1 PARTITION (p0); 44eval $get_handler_status_counts; 45--echo # 4 locks (1 table, 1 partition lock/unlock) 46--sorted_result 47SELECT * FROM t1; 48DROP VIEW v1; 49CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION; 50FLUSH STATUS; 51INSERT INTO v1 VALUES (20); 52eval $get_handler_status_counts; 53--echo # 4 locks (1 table, 1 partition lock/unlock) 54FLUSH STATUS; 55# --error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 56# INSERT INTO v1 VALUES (21); 57eval $get_handler_status_counts; 58--echo # 2 locks (1 table, all partitions pruned) 59--sorted_result 60SELECT * FROM v1; 61--sorted_result 62SELECT * FROM t1; 63DROP VIEW v1; 64CREATE VIEW v1 AS 65SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION; 66FLUSH STATUS; 67INSERT INTO v1 VALUES (30); 68eval $get_handler_status_counts; 69--echo # 4 locks (1 table, 1 partition lock/unlock) 70FLUSH STATUS; 71--error ER_VIEW_CHECK_FAILED 72INSERT INTO v1 VALUES (31); 73eval $get_handler_status_counts; 74--echo # 2 locks (1 table, all partitions pruned) 75FLUSH STATUS; 76--error ER_VIEW_CHECK_FAILED 77INSERT INTO v1 VALUES (32); 78eval $get_handler_status_counts; 79--echo # 4 locks (1 table, 1 partition lock/unlock) 80--sorted_result 81SELECT * FROM v1; 82--sorted_result 83SELECT * FROM t1; 84DROP VIEW v1; 85DROP TABLE t1; 86 87--echo # Original tests for WL#5217 88 89--echo # Must have InnoDB as engine to get the same statistics results. 90--echo # embedded uses MyISAM as default. CREATE SELECT uses the default engine. 91SET @old_default_storage_engine = @@default_storage_engine; 92SET @@default_storage_engine = 'InnoDB'; 93 94--let $MYSQLD_DATADIR= `SELECT @@datadir` 95 96--echo # Test to show if I_S affects HANDLER_ counts 97FLUSH STATUS; 98eval $get_handler_status_counts; 99eval $get_handler_status_counts; 100--echo # OK, seems to add number of variables processed before HANDLER_WRITE 101--echo # and number of variables + 1 evaluated in the previous call in RND_NEXT 102 103CREATE TABLE t1 104(a INT NOT NULL, 105 b varchar (64), 106 INDEX (b,a), 107 PRIMARY KEY (a)) 108ENGINE = InnoDB 109PARTITION BY RANGE (a) 110SUBPARTITION BY HASH (a) SUBPARTITIONS 2 111(PARTITION pNeg VALUES LESS THAN (0) 112 (SUBPARTITION subp0, 113 SUBPARTITION subp1), 114 PARTITION `p0-9` VALUES LESS THAN (10) 115 (SUBPARTITION subp2, 116 SUBPARTITION subp3), 117 PARTITION `p10-99` VALUES LESS THAN (100) 118 (SUBPARTITION subp4, 119 SUBPARTITION subp5), 120 PARTITION `p100-99999` VALUES LESS THAN (100000) 121 (SUBPARTITION subp6, 122 SUBPARTITION subp7)); 123 124SHOW CREATE TABLE t1; 125--echo # First test that the syntax is OK 126--error ER_PARSE_ERROR 127SHOW CREATE TABLE t1 PARTITION (subp0); 128--echo # Not a correct partition list 129--error ER_PARSE_ERROR 130INSERT INTO t1 PARTITION () VALUES (1, "error"); 131--error ER_UNKNOWN_PARTITION 132INSERT INTO t1 PARTITION (pNonExisting) VALUES (1, "error"); 133--error ER_UNKNOWN_PARTITION 134INSERT INTO t1 PARTITION (pNeg, pNonExisting) VALUES (1, "error"); 135--echo # Duplicate partitions and overlapping partitions and subpartitios is OK 136FLUSH STATUS; 137INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)"); 138eval $get_handler_status_counts; 139--echo # Should be 1 commit 140--echo # 4 external locks (due to pruning of locks) 141--echo # (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 142--echo # and 18 write (1 ha_innobase + 17 internal I_S write) 143INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)"); 144INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0"); 145 146--echo # should be correct 147INSERT INTO t1 PARTITION (`p100-99999`) VALUES (100, "`p100-99999`(-subp6)"), (101, "`p100-99999`(-subp7)"), (1000, "`p100-99999`(-subp6)"); 148INSERT INTO t1 PARTITION(`p10-99`,subp3) VALUES (1, "subp3"), (10, "p10-99"); 149FLUSH STATUS; 150INSERT INTO t1 PARTITION(subp3) VALUES (3, "subp3"); 151eval $get_handler_status_counts; 152--echo # Should be 1 commit 153--echo # 4 external locks 154--echo # (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 155--echo # and 18 write (1 ha_innobase + 17 internal I_S write) 156FLUSH STATUS; 157LOCK TABLE t1 WRITE; 158eval $get_handler_status_counts; 159--echo # should be 1 commit 160--echo # 9 locks (1 ha_partition + 8 ha_innobase) 161--echo # 17 writes (internal I_S) 162INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3"); 163eval $get_handler_status_counts; 164--echo # + 1 commit 165--echo # + 19 rnd next (internal I_S) 166--echo # + 19 write (18 internal I_S + 1 insert) 167UNLOCK TABLES; 168eval $get_handler_status_counts; 169--echo # + 9 locks (unlocks) 170--echo # + 19 rnd next (internal I_S) 171--echo # + 18 write (internal I_S) 172 173--echo # Not matching partitions with inserted value 174--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 175INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (1, "error"); 176--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 177INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (1, "error"); 178--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 179INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1, "error"), (10, "error"); 180--error ER_NO_PARTITION_FOR_GIVEN_VALUE 181INSERT INTO t1 VALUES (1000000, "error"), (9999999, "error"); 182--error ER_NO_PARTITION_FOR_GIVEN_VALUE 183INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1000000, "error"), (9999999, "error"); 184--error ER_DUP_ENTRY,ER_DUP_KEY 185INSERT INTO t1 PARTITION (pNeg, subp4) VALUES (-7, "pNeg(-subp1)"), (-10, "pNeg(-subp0)"), (-1, "pNeg(-subp1)"), (-99, "pNeg(-subp1)"); 186 187SELECT * FROM t1 ORDER BY a; 188ANALYZE TABLE t1; 189 190SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; 191SET @@global.innodb_stats_on_metadata=ON; 192SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS 193FROM INFORMATION_SCHEMA.PARTITIONS 194WHERE TABLE_SCHEMA = 'test' 195AND TABLE_NAME = 't1' ORDER BY SUBPARTITION_NAME; 196SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; 197 198FLUSH STATUS; 199--error ER_UNKNOWN_PARTITION 200SELECT * FROM t1 PARTITION (pNonexistent); 201eval $get_handler_status_counts; 202--echo # should have failed before locking (only 17 internal I_S writes) 203FLUSH STATUS; 204SELECT * FROM t1 PARTITION (subp2); 205eval $get_handler_status_counts; 206--echo # Should be 1 commit 207--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 208--echo # 1 read first (also calls index_read) 209--echo # 2 read key (first from innobase_get_index and second from index first) 210--echo # 17 writes (internal I_S) 211FLUSH STATUS; 212SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias; 213eval $get_handler_status_counts; 214--echo # Should be 1 commit 215--echo # 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2 216--echo # 3 read first (one for each partition) 217--echo # 6 read key (3 from read first and 3 from innobase_get_index) 218--echo # 3 read next (one next call after each read row) 219--echo # 17 writes (internal I_S) 220FLUSH STATUS; 221LOCK TABLE t1 READ, t1 as TableAlias READ; 222eval $get_handler_status_counts; 223--echo # 1 commit 224--echo # 18 locks 225--echo # 18 READ KEY from opening a new partition table instance, 226--echo # (1 innobase_get_index for each index, per partition, 1 x 2 x 8 = 16 227--echo # + info(HA_STATUS_CONST) call on the partition with the most number 228--echo # of rows, 2 innobase_get_index for updating both index statistics) 229--echo # 17 writes (internal I_S) 230SELECT * FROM t1 PARTITION (subp3) AS TableAlias; 231eval $get_handler_status_counts; 232--echo # + 1 commit 233--echo # + 1 read first (read first key from index in one partition) 234--echo # + 2 read key (innobase_get_index from index_init + from index_first) 235--echo # + 3 read next (one after each row) 236--echo # + 19 rnd next (from the last I_S query) 237--echo # + 18 write (internal I_S) 238SELECT COUNT(*) FROM t1 PARTITION (`p10-99`); 239eval $get_handler_status_counts; 240--echo # + 1 commit 241--echo # + 2 read first (one for each subpart) 242--echo # + 4 read key (innobase_get_index from index_init + from index_first) 243--echo # + 1 read next (one after each row) 244--echo # + 19 rnd next (from the last I_S query) 245--echo # + 18 write (internal I_S) 246SELECT * FROM t1 WHERE a = 1000000; 247eval $get_handler_status_counts; 248--echo # No matching partition, only internal I_S. 249SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100; 250UNLOCK TABLES; 251eval $get_handler_status_counts; 252--echo # + 18 for unlock (same as lock above) (100 is not in pNeg, no match) 253 254--echo # Test that EXPLAIN PARTITION works 255--error ER_UNKNOWN_PARTITION 256EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent); 257EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2); 258FLUSH STATUS; 259EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias; 260eval $get_handler_status_counts; 261--echo # 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock) 262EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias; 263EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 PARTITION (`p10-99`); 264EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1000000; 265EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100; 266 267--echo # Test how it changes the alias/keywords/reserved words 268--error ER_PARSE_ERROR 269SELECT * FROM t1 PARTITION; 270SELECT * FROM t1 `PARTITION`; 271--error ER_PARSE_ERROR 272SELECT * FROM t1 AS PARTITION; 273SELECT * FROM t1 AS `PARTITION`; 274 275--echo # 276--echo # Test REPLACE 277--echo # 278FLUSH STATUS; 279--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 280REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!'); 281eval $get_handler_status_counts; 282--echo # 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443 283--echo # explicit pruning says part_id 0 and implicit pruning says part_id 1 284--echo # so no partition will be locked! 285--echo # 0 rollback (since no locked partition) 286--echo # 17 writes (I_S internal) 287FLUSH STATUS; 288REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'Insert by REPLACE'); 289eval $get_handler_status_counts; 290--echo # 1 commit 291--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 292--echo # 18 writes (17 I_S internal, 1 ha_innobase) 293SELECT * FROM t1 PARTITION (pNeg); 294FLUSH STATUS; 295REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE'); 296eval $get_handler_status_counts; 297--echo # 1 commit 298--echo # 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 299--echo # 2 read key (1 innobase_get_index when init the index + 1 index read 300--echo # to get the position to update) 301--echo # 1 update (updated one row, since there is no delete trigger, update 302--echo # is used instead of delete+insert) 303--echo # 18 write (17 from I_S, 1 for the failed insert) 304SELECT * FROM t1 PARTITION (pNeg); 305FLUSH STATUS; 306LOCK TABLE t1 WRITE; 307eval $get_handler_status_counts; 308--echo # 1 commit 309--echo # 9 locks 310--echo # 17 write (internal I_S) 311DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE"; 312eval $get_handler_status_counts; 313--echo # + 1 commit 314--echo # + 1 delete (one row deleted) 315--echo # + 3 read key (1 innodb_get_index in records_in_range, 316--echo # 1 innodb_get_index in index_init, 1 index_read in index_read_first) 317--echo # + 1 read next (search for another row in secondary index) 318--echo # + 19 rnd next (internal I_S) 319--echo # + 18 write (internal I_S) 320--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 321REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!'); 322eval $get_handler_status_counts; 323--echo # Failed before start_stmt/execution. 324--echo # + 19 rnd next (internal I_S) 325--echo # 0 rollback (No partition had called start_stmt, all parts pruned) 326--echo # + 18 write (internal I_S) 327REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE'); 328eval $get_handler_status_counts; 329--echo # + 1 commit 330--echo # + 19 rnd next (internal I_S) 331--echo # + 19 write (18 internal I_S + 1 real write) 332REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE'); 333eval $get_handler_status_counts; 334--echo # + 1 commit 335--echo # + 2 read key (see non locked query) 336--echo # + 19 rnd next (internal I_S) 337--echo # + 1 update (see non locked query) 338--echo # + 19 write (18 internal I_S + 1 failed write) 339SELECT * FROM t1 PARTITION (subp1); 340eval $get_handler_status_counts; 341--echo # + 1 commit 342--echo # + 1 read first 343--echo # + 2 read key 344--echo # + 3 read next 345--echo # + 19 rnd next (internal I_S) 346--echo # + 18 write (internal I_S) 347UNLOCK TABLES; 348eval $get_handler_status_counts; 349--echo # + 9 locks 350--echo # + 19 rnd next (internal I_S) 351--echo # + 18 write (internal I_S) 352 353--echo # 354--echo # Test LOAD 355--echo # 356SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 357FLUSH STATUS; 358SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt'; 359eval $get_handler_status_counts; 360--echo # 1 commit 361--echo # 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock) 362--echo # 4 read first (for reading the first row in 4 partitions) 363--echo # 8 read key (4 from read first + 4 for index init) 364--echo # 5 read next (one after each row) 365--echo # 17 write (internal I_S) 366FLUSH STATUS; 367ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`; 368eval $get_handler_status_counts; 369--echo # 10 locks (table + 4 partition) x (lock + unlock) 370SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 371FLUSH STATUS; 372--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 373LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg); 374eval $get_handler_status_counts; 375--echo # 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock) 376--echo # 1 rollback 377SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 378FLUSH STATUS; 379LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, subp4, subp5); 380eval $get_handler_status_counts; 381--echo # 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock) 382ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`; 383FLUSH STATUS; 384LOCK TABLE t1 WRITE; 385eval $get_handler_status_counts; 386--echo # 9 locks 387--echo # 18 read key (ALTER forces table to be closed, see above for open) 388LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, `p10-99`); 389eval $get_handler_status_counts; 390--echo # + 23 write (18 internal I_S + 5 rows) 391UNLOCK TABLES; 392eval $get_handler_status_counts; 393--echo # + 9 locks 394--remove_file $MYSQLD_DATADIR/test/loadtest.txt 395 396--echo # 397--echo # Test UPDATE 398--echo # 399FLUSH STATUS; 400UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated'); 401eval $get_handler_status_counts; 402--echo # 1 commit 403--echo # 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 404--echo # 1 read first (read first row, called from first rnd_next) 405--echo # 2 read key (innobase_get_index from rnd_init + 406--echo # read next row from second rnd_next) 407--echo # 1 update (update the row) 408# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated'); 409SELECT * FROM t1 PARTITION (subp0) ORDER BY a; 410FLUSH STATUS; 411UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2; 412eval $get_handler_status_counts; 413--echo # 1 commit 414--echo # 4 lock 415--echo # 1 read key 416--echo # 1 update 417# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2; 418FLUSH STATUS; 419UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2') 420WHERE a = -2; 421eval $get_handler_status_counts; 422--echo # 1 commit 423--echo # 4 lock 424--echo # 2 read key - (2 index read) 425--echo # 1 read rnd - rnd_pos 426--echo # 1 update 427# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2'); 428FLUSH STATUS; 429UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100; 430eval $get_handler_status_counts; 431--echo # Nothing, since impossible PARTITION+WHERE clause. 432# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100; 433FLUSH STATUS; 434UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100') 435WHERE a = 100; 436eval $get_handler_status_counts; 437--echo # Nothing, since impossible PARTITION+WHERE clause. 438# EXPLAIN PARTITIONS UPDATE t1 PARTITION(subp0) 439# SET a = -2, b = concat(b, ', Updated from a = 100') WHERE a = 100; 440FLUSH STATUS; 441--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 442UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100') 443WHERE a = 100; 444eval $get_handler_status_counts; 445--echo # 6 lock 446--echo # 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos) 447--echo # 1 read rnd (rnd pos) 448--echo # 1 rollback 449FLUSH STATUS; 450--error ER_DUP_ENTRY,ER_DUP_KEY 451UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -4, b = concat(b, ', Updated from a = 100') 452WHERE a = 100; 453eval $get_handler_status_counts; 454--echo # 10 locks 455--echo # 4 read key 456--echo # 1 read rnd 457--echo # 1 rollback 458--echo # 18 write (17 internal I_S + 1 failed insert) 459FLUSH STATUS; 460UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -222, b = concat(b, ', Updated from a = 100') 461WHERE a = 100; 462eval $get_handler_status_counts; 463--echo # 1 commit 464--echo # 1 delete 465--echo # 4 read key 466--echo # 1 read rnd 467--echo # 18 write (17 internal I_S + 1 insert) 468 469SELECT * FROM t1 ORDER BY a; 470--echo # Test of non matching partition (i.e ER_NO_PARTITION_FOUND) 471FLUSH STATUS; 472UPDATE t1 SET b = concat(b, ', Updated2') WHERE a = 1000000; 473eval $get_handler_status_counts; 474--echo # Nothing (no matching partition found) 475FLUSH STATUS; 476UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000; 477eval $get_handler_status_counts; 478--echo # Nothing (no matching partition found) 479FLUSH STATUS; 480LOCK TABLE t1 WRITE; 481eval $get_handler_status_counts; 482--echo # 9 locks 483UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101; 484eval $get_handler_status_counts; 485--echo # + 4 read key 486--echo # + 1 read rnd 487--echo # + 1 update 488UPDATE t1 PARTITION (`p100-99999`) SET b = concat(b, ', Updated to 110'), a = 110 WHERE a = 103; 489eval $get_handler_status_counts; 490--echo # + 1 delete 491--echo # + 4 read key 492--echo # + 1 read rnd 493--echo # + 19 write (18 internal I_S + 1 insert) 494UNLOCK TABLES; 495eval $get_handler_status_counts; 496--echo + 9 locks 497 498--echo # 499--echo # Test DELETE 500--echo # 501SELECT * FROM t1 ORDER BY b, a; 502FLUSH STATUS; 503DELETE FROM t1 PARTITION (pNeg) WHERE a = -1; 504eval $get_handler_status_counts; 505--echo # 1 delete 506--echo # 4 locks (pruning works!). 507--echo # 1 read key (index read) 508FLUSH STATUS; 509DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%'; 510eval $get_handler_status_counts; 511--echo # 1 delete 512--echo # 4 locks 513--echo # 1 read first 514--echo # 2 read key 515--echo # 3 read rnd 516FLUSH STATUS; 517LOCK TABLE t1 WRITE; 518eval $get_handler_status_counts; 519--echo # 9 locks 520DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3'; 521eval $get_handler_status_counts; 522--echo # + 3 read key (1 innodb_get_index in records_in_range 523--echo # + 1 innobase_get_index in index_init + 1 index read) 524DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3'; 525eval $get_handler_status_counts; 526--echo # + 1 delete 527--echo # + 6 read key (same as above, but for two subpartitions) 528--echo # + 1 read next (read next after found row) 529UNLOCK TABLES; 530eval $get_handler_status_counts; 531--echo # + 9 locks 532 533--echo # Test multi-table DELETE 534--echo # Can be expressed in two different ways. 535CREATE TABLE t2 LIKE t1; 536FLUSH STATUS; 537INSERT INTO t2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 538eval $get_handler_status_counts; 539--echo # 24 locks (2 table, 5 + 5 subpartitions lock/unlock) 540FLUSH STATUS; 541ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`; 542eval $get_handler_status_counts; 543--echo # 14 locks (1 table, 6 subpartitions lock/unlock) 544FLUSH STATUS; 545--error ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET 546INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 547eval $get_handler_status_counts; 548--echo # 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) 549FLUSH STATUS; 550INSERT IGNORE INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 551eval $get_handler_status_counts; 552--echo # 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) 553TRUNCATE TABLE t2; 554FLUSH STATUS; 555INSERT INTO t2 SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 556eval $get_handler_status_counts; 557--echo # 30 locks (2 table, 8 + 5 subpartitions lock/unlock) 558FLUSH STATUS; 559CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`); 560eval $get_handler_status_counts; 561--echo # 14 locks (2 table, 5 subpartitions lock/unlock) 562SHOW CREATE TABLE t1; 563SELECT * FROM t1; 564SHOW CREATE TABLE t2; 565SELECT * FROM t2; 566SHOW CREATE TABLE t3; 567SELECT * FROM t3; 568FLUSH STATUS; 569--error ER_PARSE_ERROR 570DELETE t1 PARTITION (pNeg), t3 FROM t1, t3 571WHERE t1.a = t3.a AND t3.b = 'subp3'; 572eval $get_handler_status_counts; 573--echo # Multi table delete without any matching rows 574FLUSH STATUS; 575DELETE t1, t2 FROM t1 PARTITION (pNeg), t3, t2 PARTITION (subp3) 576WHERE t1.a = t3.a AND t3.b = 'subp3' AND t3.a = t2.a; 577eval $get_handler_status_counts; 578--echo # 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock) 579--echo # 1 read first (first rnd_next in t2) 580--echo # 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2 581--echo # + 2 innodb_get_index in index_init in t1) 582--echo # 3 read rnd next (3 rnd next in t2, 2 rows + 1 empty) 583--echo # Multi table delete matching all rows in subp3 (2 rows in per table) 584FLUSH STATUS; 585DELETE FROM t2, t3 USING t2 PARTITION (`p0-9`), t3, t1 PARTITION (subp3) 586WHERE t1.a = t3.a AND t3.b = 'subp3' AND t2.a = t1.a; 587eval $get_handler_status_counts; 588--echo # 4 delete (2 in t2 + 2 in t3) 589--echo # 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock) 590--echo # 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1) 591--echo # 17 read key (1 index_init in t1 + 1 read first in t1 + 592--echo # 2 index_init in t2 + 1 index read in t2 + 593--echo # 1 index_init in t3 + 1 index read in t3 + 594--echo # 1 index read in t2 + 595--echo # 1 index_init in t3 + 1 index read in t3 + 596--echo # 2 index_init in t2 + 2 index read in t2 (from rnd_pos) 597--echo # 1 index_init in t3 + 2 index read in t3 (from rnd_pos)) 598--echo # 2 read next (1 in t1 + 1 in t1, second row) 599--echo # 4 read rnd (position on 4 found rows to delete) 600--echo # 16 rnd next (8 in t3 + 8 in t3, for second row) 601SELECT * FROM t1 ORDER BY a; 602SELECT * FROM t2 ORDER BY a; 603SELECT * FROM t3 ORDER BY a; 604 605 606--echo # Test TRUNCATE TABLE (should fail, since one should use 607--echo # ALTER TABLE ... TRUNCATE PARTITION instead) 608--error ER_PARSE_ERROR 609TRUNCATE TABLE t1 PARTITION(`p10-99`); 610 611--echo # Test of locking in TRUNCATE PARTITION 612--echo # Note that it does not support truncating subpartitions 613FLUSH STATUS; 614ALTER TABLE t1 TRUNCATE PARTITION pNeg; 615eval $get_handler_status_counts; 616--echo # 6 locks (lock/unlock two subpartitions + table) 617 618--echo # Test on non partitioned table 619--error ER_PARTITION_CLAUSE_ON_NONPARTITIONED 620SELECT * FROM t3 PARTITION (pNeg); 621 622DROP TABLE t1, t2, t3; 623# 624--echo # Test from superseeded WL# 2682 625# Partition select tests. 626# 627--disable_warnings 628drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; 629--enable_warnings 630 631CREATE TABLE `t1` ( 632`id` int(11) default NULL 633) ENGINE=MyISAM DEFAULT CHARSET=latin1 634PARTITION BY RANGE (id) ( 635PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM, 636PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM, 637PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM, 638PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM); 639 640INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), 641(11), (12), (13), (14), (15), (16), (17), (18), (19), (20); 642 643SELECT * FROM t1; 644SELECT * FROM t1 PARTITION (p0); 645SELECT * FROM t1 PARTITION (p1); 646SELECT * FROM t1 PARTITION (p2); 647SELECT * FROM t1 PARTITION (p3); 648SELECT * FROM t1 PARTITION (p3) WHERE id = 2; 649--error ER_UNKNOWN_PARTITION 650SELECT * FROM t1 PARTITION (foo); 651 652# now try indexes 653CREATE TABLE `t2` ( 654`id` int(11) NOT NULL DEFAULT 0, 655PRIMARY KEY (`id`) 656) ENGINE=MyISAM DEFAULT CHARSET=latin1 657PARTITION BY RANGE (id) ( 658PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM, 659PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM, 660PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM, 661PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM); 662 663INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), 664(11), (12), (13), (14), (15), (16), (17), (18), (19), (20); 665 666SELECT * FROM t2; 667SELECT * FROM t2 PARTITION (p0); 668SELECT * FROM t2 PARTITION (p1); 669SELECT * FROM t2 PARTITION (p2); 670SELECT * FROM t2 PARTITION (p3); 671SELECT * FROM t2 PARTITION (p3) ORDER BY id; 672SELECT * FROM t2 PARTITION (p3) WHERE id = 2; 673--error ER_UNKNOWN_PARTITION 674SELECT * FROM t2 PARTITION (foo); 675 676 677CREATE TABLE `t3` ( 678 `id` int(32) default NULL, 679 `name` varchar(32) default NULL 680) ENGINE=MyISAM DEFAULT CHARSET=latin1 681PARTITION BY LIST (id) ( 682 PARTITION p0 VALUES IN (1,3,5,7), 683 PARTITION p1 VALUES IN (0,2,4,6,8), 684 PARTITION p2 VALUES IN (9,10,11,12,13) 685); 686 687INSERT 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'); 688 689SELECT * FROM `t3`; 690SELECT * FROM `t3` PARTITION (p0); 691SELECT * FROM `t3` PARTITION (p1); 692SELECT * FROM `t3` PARTITION (p2); 693SELECT * FROM `t3` PARTITION (p2) ORDER BY id; 694 695DROP TABLE IF EXISTS `t4`; 696CREATE TABLE `t4` ( 697 `id` int(32) default NULL 698) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; 699 700INSERT INTO `t4` SELECT * FROM `t2`; 701INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id; 702# not sure how to do this, since names could be anything 703#SELECT * FROM `t4` PARTITION (p0); 704#SELECT * FROM `t4` PARTITION (p1); 705#SELECT * FROM `t4` PARTITION (p2); 706#SELECT * FROM `t4` PARTITION (p3); 707#SELECT * FROM `t4` PARTITION (p3) ORDER BY id; 708 709CREATE TABLE `t5` ( 710 id int(32), 711 name varchar(64), 712 purchased date) 713PARTITION BY RANGE( YEAR(purchased) ) 714 SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 715 PARTITION p0 VALUES LESS THAN (1990) ( 716 SUBPARTITION s0, 717 SUBPARTITION s1 718 ), 719 PARTITION p1 VALUES LESS THAN (2000) ( 720 SUBPARTITION s2, 721 SUBPARTITION s3 722 ), 723 PARTITION p2 VALUES LESS THAN MAXVALUE ( 724 SUBPARTITION s4, 725 SUBPARTITION s5 726 ) 727 ); 728 729INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00'); 730INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00'); 731INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00'); 732INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00'); 733INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00'); 734INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00'); 735INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00'); 736INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00'); 737INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00'); 738INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00'); 739INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00'); 740INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00'); 741INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00'); 742INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00'); 743INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00'); 744INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00'); 745INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00'); 746INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00'); 747INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00'); 748INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00'); 749 750SELECT * FROM `t5`; 751SELECT * FROM `t5` PARTITION(p0) ORDER BY id; 752SELECT * FROM `t5` PARTITION(s0) ORDER BY id; 753SELECT * FROM `t5` PARTITION(s1) ORDER BY id; 754SELECT * FROM `t5` PARTITION(p1) ORDER BY id; 755SELECT * FROM `t5` PARTITION(s2) ORDER BY id; 756SELECT * FROM `t5` PARTITION(s3) ORDER BY id; 757SELECT * FROM `t5` PARTITION(p2) ORDER BY id; 758SELECT * FROM `t5` PARTITION(s4) ORDER BY id; 759SELECT * FROM `t5` PARTITION(s5) ORDER BY id; 760 761--disable_warnings 762drop table t1,t2,t3,t4,t5; 763--enable_warnings 764 765# Tests for working together with partition pruning. 766create table t1 (a int) partition by hash(a) partitions 3; 767insert into t1 values(1),(2),(3); 768explain partitions select * from t1 where a=1; 769explain partitions select * from t1 partition (p1) where a=1; 770explain partitions select * from t1 partition (p1) where a=1 or a=2; 771explain partitions select * from t1 partition (p2) where a=1; 772 773drop table t1; 774 775--echo # 776--echo # Bug#59864: Crash if table empty: DELETE FROM t2 PARTITION (subp3). 777--echo # 778CREATE TABLE t1 779(a INT NOT NULL, 780 b varchar (64), 781 INDEX (b,a), 782 PRIMARY KEY (a)) 783PARTITION BY RANGE (a) 784SUBPARTITION BY HASH (a) SUBPARTITIONS 3 785(PARTITION pNeg VALUES LESS THAN (0) 786 (SUBPARTITION subp0, 787 SUBPARTITION subp1, 788 SUBPARTITION subp2), 789 PARTITION `p0-29` VALUES LESS THAN (30) 790 (SUBPARTITION subp3, 791 SUBPARTITION subp4, 792 SUBPARTITION subp5), 793 PARTITION `p30-299` VALUES LESS THAN (300) 794 (SUBPARTITION subp6, 795 SUBPARTITION subp7, 796 SUBPARTITION subp8), 797 PARTITION `p300-2999` VALUES LESS THAN (3000) 798 (SUBPARTITION subp9, 799 SUBPARTITION subp10, 800 SUBPARTITION subp11), 801 PARTITION `p3000-299999` VALUES LESS THAN (300000) 802 (SUBPARTITION subp12, 803 SUBPARTITION subp13, 804 SUBPARTITION subp14)); 805 806eval SHOW CREATE TABLE t1; 807 808INSERT INTO t1 VALUES (-9, "negative nine"), (-8, "-8"), (-7, "-7"), (-6, "-6"), (-5, "-5"), (-4, "-4"), (-3, "-3"), (-2, "-2"), (-1, "-1"); 809INSERT INTO t1 VALUES (9, "nine"), (8, "8"), (7, "7"), (6, "6"), (5, "5"), (4, "4"), (3, "3"), (2, "2"), (1, "1"); 810INSERT INTO t1 VALUES (39, "Thirty nine"), (38, "38"), (37, "37"), (36, "36"), (35, "35"), (34, "34"), (33, "33"), (32, "32"), (31, "31"); 811INSERT INTO t1 VALUES (339, "Three hundred thirty nine"), (338, "338"), (337, "337"), (336, "336"), (335, "335"), (334, "334"), (333, "333"), (332, "332"), (331, "331"); 812INSERT 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"); 813 814--sorted_result 815SELECT * FROM t1; 816--sorted_result 817SELECT * FROM t1 PARTITION (subp3); 818 819DELETE FROM t1 PARTITION (subp3); 820 821--sorted_result 822SELECT * FROM t1; 823--sorted_result 824SELECT * FROM t1 PARTITION (subp3); 825 826DELETE FROM t1 PARTITION (`p0-29`); 827 828--sorted_result 829SELECT * FROM t1; 830--sorted_result 831SELECT * FROM t1 PARTITION (`p0-29`); 832 833ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 3; 834 835DELETE FROM t1 PARTITION (p2); 836 837--sorted_result 838SELECT * FROM t1; 839--sorted_result 840SELECT * FROM t1 PARTITION (p2); 841 842DROP TABLE t1; 843 844--echo # 845--echo # Test explicit partition selection on a non partitioned temp table 846--echo # 847CREATE TEMPORARY TABLE t1 (a INT); 848--error ER_PARTITION_CLAUSE_ON_NONPARTITIONED 849SELECT * FROM t1 PARTITION(pNonexisting); 850DROP TEMPORARY TABLE t1; 851 852--echo # 853--echo # Test CREATE LIKE does not take PARTITION clause 854--echo # 855CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3; 856--error ER_PARSE_ERROR 857CREATE TABLE t2 LIKE t1 PARTITION (p0, p2); 858DROP TABLE t1; 859 860SET @@default_storage_engine = @old_default_storage_engine; 861 862 863--echo # 864--echo # MDEV-14815 - Server crash or AddressSanitizer errors or valgrind warnings in thr_lock / has_old_lock upon FLUSH TABLES 865--echo # 866CREATE TABLE t1 (i INT) ENGINE=MEMORY PARTITION BY RANGE (i) (PARTITION p0 VALUES LESS THAN (4), PARTITION pm VALUES LESS THAN MAXVALUE); 867CREATE TABLE t2 (i INT) ENGINE=MEMORY; 868LOCK TABLE t1 WRITE, t2 WRITE; 869SELECT * FROM t1 PARTITION (p0); 870FLUSH TABLES; 871SELECT * FROM t1 PARTITION (p0); 872ALTER TABLE t1 TRUNCATE PARTITION p0; 873SELECT * FROM t1 PARTITION (p0); 874ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; 875SELECT * FROM t1 PARTITION (p0); 876UNLOCK TABLES; 877DROP TABLE t1; 878 879--echo # 880--echo # MDEV-18371 Server crashes in ha_innobase::cmp_ref upon UPDATE with PARTITION clause. 881--echo # 882 883CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=InnoDB PARTITION BY KEY(b) PARTITIONS 4; 884INSERT INTO t1 VALUES (3,0),(8,2),(7,8),(3,4),(2,4),(0,7),(4,3),(3,6); 885FLUSH TABLES; 886UPDATE t1 PARTITION (p3,p1) SET a = 2 WHERE a = 3; 887SELECT * FROM t1; 888 889 890# Cleanup 891DROP TABLE t1, t2; 892 893--echo # 894--echo # MDEV-18982: INSERT using explicit patition pruning with column list 895--echo # 896 897create table t1 (a int) partition by hash(a); 898insert into t1 partition (p0) (a) values (1); 899select * from t1; 900drop table t1; 901