1# 2# Bug#13559657: PARTITION SELECTION DOES NOT WORK WITH VIEWS 3# 4CREATE TABLE t1 (a int) 5ENGINE = InnoDB 6PARTITION BY HASH (a) PARTITIONS 2; 7INSERT INTO t1 VALUES (0), (1), (2), (3); 8CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0); 9SHOW CREATE VIEW v1; 10View Create View character_set_client collation_connection 11v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` PARTITION (`p0`) latin1 latin1_swedish_ci 12FLUSH STATUS; 13SELECT * FROM v1; 14a 150 162 17SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 18WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 19VARIABLE_NAME VARIABLE_VALUE 20HANDLER_COMMIT 1 21HANDLER_READ_RND_NEXT 3 22HANDLER_TMP_WRITE 24 23# 4 locks (1 table, 1 partition lock/unlock) 24FLUSH STATUS; 25SELECT a FROM t1 PARTITION (p0); 26a 270 282 29SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 30WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 31VARIABLE_NAME VARIABLE_VALUE 32HANDLER_COMMIT 1 33HANDLER_READ_RND_NEXT 3 34HANDLER_TMP_WRITE 24 35# 4 locks (1 table, 1 partition lock/unlock) 36FLUSH STATUS; 37INSERT INTO v1 VALUES (10); 38SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 39WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 40VARIABLE_NAME VARIABLE_VALUE 41HANDLER_COMMIT 1 42HANDLER_TMP_WRITE 24 43HANDLER_WRITE 2 44# 4 locks (1 table, 1 partition lock/unlock) 45FLUSH STATUS; 46SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 47WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 48VARIABLE_NAME VARIABLE_VALUE 49HANDLER_TMP_WRITE 24 50# 2 locks (1 table, all partitions pruned) 51FLUSH STATUS; 52SELECT * FROM v1; 53a 540 5510 562 57SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 58WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 59VARIABLE_NAME VARIABLE_VALUE 60HANDLER_COMMIT 1 61HANDLER_READ_RND_NEXT 4 62HANDLER_TMP_WRITE 24 63# 4 locks (1 table, 1 partition lock/unlock) 64FLUSH STATUS; 65SELECT a FROM t1 PARTITION (p0); 66a 670 6810 692 70SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 71WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 72VARIABLE_NAME VARIABLE_VALUE 73HANDLER_COMMIT 1 74HANDLER_READ_RND_NEXT 4 75HANDLER_TMP_WRITE 24 76# 4 locks (1 table, 1 partition lock/unlock) 77SELECT * FROM t1; 78a 790 801 8110 822 833 84DROP VIEW v1; 85CREATE VIEW v1 AS SELECT a FROM t1 PARTITION (p0) WITH CHECK OPTION; 86FLUSH STATUS; 87INSERT INTO v1 VALUES (20); 88SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 89WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 90VARIABLE_NAME VARIABLE_VALUE 91HANDLER_COMMIT 1 92HANDLER_TMP_WRITE 24 93HANDLER_WRITE 2 94# 4 locks (1 table, 1 partition lock/unlock) 95FLUSH STATUS; 96SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 97WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 98VARIABLE_NAME VARIABLE_VALUE 99HANDLER_TMP_WRITE 24 100# 2 locks (1 table, all partitions pruned) 101SELECT * FROM v1; 102a 1030 10410 1052 10620 107SELECT * FROM t1; 108a 1090 1101 11110 1122 11320 1143 115DROP VIEW v1; 116CREATE VIEW v1 AS 117SELECT a FROM t1 PARTITION (p0) WHERE a = 30 WITH CHECK OPTION; 118FLUSH STATUS; 119INSERT INTO v1 VALUES (30); 120SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 121WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 122VARIABLE_NAME VARIABLE_VALUE 123HANDLER_COMMIT 1 124HANDLER_TMP_WRITE 24 125HANDLER_WRITE 2 126# 4 locks (1 table, 1 partition lock/unlock) 127FLUSH STATUS; 128INSERT INTO v1 VALUES (31); 129ERROR 44000: CHECK OPTION failed `test`.`v1` 130SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 131WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 132VARIABLE_NAME VARIABLE_VALUE 133HANDLER_ROLLBACK 1 134HANDLER_TMP_WRITE 24 135# 2 locks (1 table, all partitions pruned) 136FLUSH STATUS; 137INSERT INTO v1 VALUES (32); 138ERROR 44000: CHECK OPTION failed `test`.`v1` 139SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 140WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 141VARIABLE_NAME VARIABLE_VALUE 142HANDLER_ROLLBACK 1 143HANDLER_TMP_WRITE 24 144# 4 locks (1 table, 1 partition lock/unlock) 145SELECT * FROM v1; 146a 14730 148SELECT * FROM t1; 149a 1500 1511 15210 1532 15420 1553 15630 157DROP VIEW v1; 158DROP TABLE t1; 159# Original tests for WL#5217 160# Must have InnoDB as engine to get the same statistics results. 161# embedded uses MyISAM as default. CREATE SELECT uses the default engine. 162SET @old_default_storage_engine = @@default_storage_engine; 163SET @@default_storage_engine = 'InnoDB'; 164# Test to show if I_S affects HANDLER_ counts 165FLUSH STATUS; 166SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 167WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 168VARIABLE_NAME VARIABLE_VALUE 169HANDLER_TMP_WRITE 24 170SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 171WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 172VARIABLE_NAME VARIABLE_VALUE 173HANDLER_READ_RND_NEXT 28 174HANDLER_TMP_WRITE 51 175# OK, seems to add number of variables processed before HANDLER_WRITE 176# and number of variables + 1 evaluated in the previous call in RND_NEXT 177CREATE TABLE t1 178(a INT NOT NULL, 179b varchar (64), 180INDEX (b,a), 181PRIMARY KEY (a)) 182ENGINE = InnoDB 183PARTITION BY RANGE (a) 184SUBPARTITION BY HASH (a) SUBPARTITIONS 2 185(PARTITION pNeg VALUES LESS THAN (0) 186(SUBPARTITION subp0, 187SUBPARTITION subp1), 188PARTITION `p0-9` VALUES LESS THAN (10) 189(SUBPARTITION subp2, 190SUBPARTITION subp3), 191PARTITION `p10-99` VALUES LESS THAN (100) 192(SUBPARTITION subp4, 193SUBPARTITION subp5), 194PARTITION `p100-99999` VALUES LESS THAN (100000) 195(SUBPARTITION subp6, 196SUBPARTITION subp7)); 197SHOW CREATE TABLE t1; 198Table Create Table 199t1 CREATE TABLE `t1` ( 200 `a` int(11) NOT NULL, 201 `b` varchar(64) DEFAULT NULL, 202 PRIMARY KEY (`a`), 203 KEY `b` (`b`,`a`) 204) ENGINE=InnoDB DEFAULT CHARSET=latin1 205 PARTITION BY RANGE (`a`) 206SUBPARTITION BY HASH (`a`) 207(PARTITION `pNeg` VALUES LESS THAN (0) 208 (SUBPARTITION `subp0` ENGINE = InnoDB, 209 SUBPARTITION `subp1` ENGINE = InnoDB), 210 PARTITION `p0-9` VALUES LESS THAN (10) 211 (SUBPARTITION `subp2` ENGINE = InnoDB, 212 SUBPARTITION `subp3` ENGINE = InnoDB), 213 PARTITION `p10-99` VALUES LESS THAN (100) 214 (SUBPARTITION `subp4` ENGINE = InnoDB, 215 SUBPARTITION `subp5` ENGINE = InnoDB), 216 PARTITION `p100-99999` VALUES LESS THAN (100000) 217 (SUBPARTITION `subp6` ENGINE = InnoDB, 218 SUBPARTITION `subp7` ENGINE = InnoDB)) 219# First test that the syntax is OK 220SHOW CREATE TABLE t1 PARTITION (subp0); 221ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION (subp0)' at line 1 222# Not a correct partition list 223INSERT INTO t1 PARTITION () VALUES (1, "error"); 224ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') VALUES (1, "error")' at line 1 225INSERT INTO t1 PARTITION (pNonExisting) VALUES (1, "error"); 226ERROR HY000: Unknown partition 'pNonExisting' in table 't1' 227INSERT INTO t1 PARTITION (pNeg, pNonExisting) VALUES (1, "error"); 228ERROR HY000: Unknown partition 'pNonExisting' in table 't1' 229# Duplicate partitions and overlapping partitions and subpartitios is OK 230FLUSH STATUS; 231INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (-1, "pNeg(-subp1)"); 232SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 233WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 234VARIABLE_NAME VARIABLE_VALUE 235HANDLER_COMMIT 1 236HANDLER_TMP_WRITE 24 237HANDLER_WRITE 2 238# Should be 1 commit 239# 4 external locks (due to pruning of locks) 240# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 241# and 18 write (1 ha_innobase + 17 internal I_S write) 242INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-3, "pNeg(-subp1)"); 243INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (-2, "(pNeg-)subp0"); 244# should be correct 245INSERT INTO t1 PARTITION (`p100-99999`) VALUES (100, "`p100-99999`(-subp6)"), (101, "`p100-99999`(-subp7)"), (1000, "`p100-99999`(-subp6)"); 246INSERT INTO t1 PARTITION(`p10-99`,subp3) VALUES (1, "subp3"), (10, "p10-99"); 247FLUSH STATUS; 248INSERT INTO t1 PARTITION(subp3) VALUES (3, "subp3"); 249SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 250WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 251VARIABLE_NAME VARIABLE_VALUE 252HANDLER_COMMIT 1 253HANDLER_TMP_WRITE 24 254HANDLER_WRITE 2 255# Should be 1 commit 256# 4 external locks 257# (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 258# and 18 write (1 ha_innobase + 17 internal I_S write) 259FLUSH STATUS; 260LOCK TABLE t1 WRITE; 261SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 262WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 263VARIABLE_NAME VARIABLE_VALUE 264HANDLER_COMMIT 1 265HANDLER_TMP_WRITE 24 266# should be 1 commit 267# 9 locks (1 ha_partition + 8 ha_innobase) 268# 17 writes (internal I_S) 269INSERT INTO t1 PARTITION(`p0-9`) VALUES (5, "p0-9:subp3"); 270SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 271WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 272VARIABLE_NAME VARIABLE_VALUE 273HANDLER_COMMIT 2 274HANDLER_READ_RND_NEXT 28 275HANDLER_TMP_WRITE 51 276HANDLER_WRITE 2 277# + 1 commit 278# + 19 rnd next (internal I_S) 279# + 19 write (18 internal I_S + 1 insert) 280UNLOCK TABLES; 281SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 282WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 283VARIABLE_NAME VARIABLE_VALUE 284HANDLER_COMMIT 2 285HANDLER_READ_RND_NEXT 56 286HANDLER_TMP_WRITE 78 287HANDLER_WRITE 2 288# + 9 locks (unlocks) 289# + 19 rnd next (internal I_S) 290# + 18 write (internal I_S) 291# Not matching partitions with inserted value 292INSERT INTO t1 PARTITION (pNeg, pNeg) VALUES (1, "error"); 293ERROR HY000: Found a row not matching the given partition set 294INSERT INTO t1 PARTITION (pNeg, subp0) VALUES (1, "error"); 295ERROR HY000: Found a row not matching the given partition set 296INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1, "error"), (10, "error"); 297ERROR HY000: Found a row not matching the given partition set 298INSERT INTO t1 VALUES (1000000, "error"), (9999999, "error"); 299ERROR HY000: Table has no partition for value 1000000 300INSERT INTO t1 PARTITION (`p100-99999`) VALUES (1000000, "error"), (9999999, "error"); 301ERROR HY000: Table has no partition for value 1000000 302INSERT INTO t1 PARTITION (pNeg, subp4) VALUES (-7, "pNeg(-subp1)"), (-10, "pNeg(-subp0)"), (-1, "pNeg(-subp1)"), (-99, "pNeg(-subp1)"); 303Got one of the listed errors 304SELECT * FROM t1 ORDER BY a; 305a b 306-3 pNeg(-subp1) 307-2 (pNeg-)subp0 308-1 pNeg(-subp1) 3091 subp3 3103 subp3 3115 p0-9:subp3 31210 p10-99 313100 `p100-99999`(-subp6) 314101 `p100-99999`(-subp7) 3151000 `p100-99999`(-subp6) 316ANALYZE TABLE t1; 317Table Op Msg_type Msg_text 318test.t1 analyze status OK 319SET @save_innodb_stats_on_metadata=@@global.innodb_stats_on_metadata; 320SET @@global.innodb_stats_on_metadata=ON; 321SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS 322FROM INFORMATION_SCHEMA.PARTITIONS 323WHERE TABLE_SCHEMA = 'test' 324AND TABLE_NAME = 't1' ORDER BY SUBPARTITION_NAME; 325PARTITION_NAME SUBPARTITION_NAME TABLE_ROWS 326pNeg subp0 1 327pNeg subp1 2 328p0-9 subp2 0 329p0-9 subp3 3 330p10-99 subp4 1 331p10-99 subp5 0 332p100-99999 subp6 2 333p100-99999 subp7 1 334SET @@global.innodb_stats_on_metadata=@save_innodb_stats_on_metadata; 335FLUSH STATUS; 336SELECT * FROM t1 PARTITION (pNonexistent); 337ERROR HY000: Unknown partition 'pNonexistent' in table 't1' 338SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 339WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 340VARIABLE_NAME VARIABLE_VALUE 341HANDLER_TMP_WRITE 24 342# should have failed before locking (only 17 internal I_S writes) 343FLUSH STATUS; 344SELECT * FROM t1 PARTITION (subp2); 345a b 346SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 347WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 348VARIABLE_NAME VARIABLE_VALUE 349HANDLER_COMMIT 1 350HANDLER_READ_FIRST 1 351HANDLER_TMP_WRITE 24 352# Should be 1 commit 353# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 354# 1 read first (also calls index_read) 355# 2 read key (first from innobase_get_index and second from index first) 356# 17 writes (internal I_S) 357FLUSH STATUS; 358SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias; 359a b 360-2 (pNeg-)subp0 361-3 pNeg(-subp1) 362-1 pNeg(-subp1) 363SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 364WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 365VARIABLE_NAME VARIABLE_VALUE 366HANDLER_COMMIT 1 367HANDLER_READ_FIRST 3 368HANDLER_READ_NEXT 3 369HANDLER_TMP_WRITE 24 370# Should be 1 commit 371# 8 locks (1 ha_partition + 2 + 1 ha_innobase) x 2 372# 3 read first (one for each partition) 373# 6 read key (3 from read first and 3 from innobase_get_index) 374# 3 read next (one next call after each read row) 375# 17 writes (internal I_S) 376FLUSH STATUS; 377LOCK TABLE t1 READ, t1 as TableAlias READ; 378SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 379WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 380VARIABLE_NAME VARIABLE_VALUE 381HANDLER_COMMIT 1 382HANDLER_TMP_WRITE 24 383# 1 commit 384# 18 locks 385# 18 READ KEY from opening a new partition table instance, 386# (1 innobase_get_index for each index, per partition, 1 x 2 x 8 = 16 387# + info(HA_STATUS_CONST) call on the partition with the most number 388# of rows, 2 innobase_get_index for updating both index statistics) 389# 17 writes (internal I_S) 390SELECT * FROM t1 PARTITION (subp3) AS TableAlias; 391a b 3925 p0-9:subp3 3931 subp3 3943 subp3 395SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 396WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 397VARIABLE_NAME VARIABLE_VALUE 398HANDLER_COMMIT 2 399HANDLER_READ_FIRST 1 400HANDLER_READ_NEXT 3 401HANDLER_READ_RND_NEXT 28 402HANDLER_TMP_WRITE 51 403# + 1 commit 404# + 1 read first (read first key from index in one partition) 405# + 2 read key (innobase_get_index from index_init + from index_first) 406# + 3 read next (one after each row) 407# + 19 rnd next (from the last I_S query) 408# + 18 write (internal I_S) 409SELECT COUNT(*) FROM t1 PARTITION (`p10-99`); 410COUNT(*) 4111 412SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 413WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 414VARIABLE_NAME VARIABLE_VALUE 415HANDLER_COMMIT 3 416HANDLER_READ_FIRST 3 417HANDLER_READ_NEXT 4 418HANDLER_READ_RND_NEXT 56 419HANDLER_TMP_WRITE 78 420# + 1 commit 421# + 2 read first (one for each subpart) 422# + 4 read key (innobase_get_index from index_init + from index_first) 423# + 1 read next (one after each row) 424# + 19 rnd next (from the last I_S query) 425# + 18 write (internal I_S) 426SELECT * FROM t1 WHERE a = 1000000; 427a b 428SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 429WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 430VARIABLE_NAME VARIABLE_VALUE 431HANDLER_COMMIT 4 432HANDLER_READ_FIRST 3 433HANDLER_READ_NEXT 4 434HANDLER_READ_RND_NEXT 84 435HANDLER_TMP_WRITE 105 436# No matching partition, only internal I_S. 437SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100; 438a b 439UNLOCK TABLES; 440SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 441WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 442VARIABLE_NAME VARIABLE_VALUE 443HANDLER_COMMIT 5 444HANDLER_READ_FIRST 3 445HANDLER_READ_NEXT 4 446HANDLER_READ_RND_NEXT 112 447HANDLER_TMP_WRITE 132 448# + 18 for unlock (same as lock above) (100 is not in pNeg, no match) 449# Test that EXPLAIN PARTITION works 450EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNonexistent); 451ERROR HY000: Unknown partition 'pNonexistent' in table 't1' 452EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2); 453id select_type table partitions type possible_keys key key_len ref rows Extra 4541 SIMPLE t1 p0-9_subp2 index NULL b 71 NULL 2 Using index 455FLUSH STATUS; 456EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp2,pNeg) AS TableAlias; 457id select_type table partitions type possible_keys key key_len ref rows Extra 4581 SIMPLE TableAlias pNeg_subp0,pNeg_subp1,p0-9_subp2 index NULL b 71 NULL 4 Using index 459SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 460WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 461VARIABLE_NAME VARIABLE_VALUE 462HANDLER_COMMIT 1 463HANDLER_TMP_WRITE 24 464# 8 locks (1 ha_partition + 3 ha_innobase) x 2 (lock/unlock) 465EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (subp3) AS TableAlias; 466id select_type table partitions type possible_keys key key_len ref rows Extra 4671 SIMPLE TableAlias p0-9_subp3 index NULL b 71 NULL 3 Using index 468EXPLAIN PARTITIONS SELECT COUNT(*) FROM t1 PARTITION (`p10-99`); 469id select_type table partitions type possible_keys key key_len ref rows Extra 4701 SIMPLE t1 p10-99_subp4,p10-99_subp5 index NULL PRIMARY 4 NULL 2 Using index 471EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = 1000000; 472id select_type table partitions type possible_keys key key_len ref rows Extra 4731 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 474EXPLAIN PARTITIONS SELECT * FROM t1 PARTITION (pNeg) WHERE a = 100; 475id select_type table partitions type possible_keys key key_len ref rows Extra 4761 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 477# Test how it changes the alias/keywords/reserved words 478SELECT * FROM t1 PARTITION; 479ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 480SELECT * FROM t1 `PARTITION`; 481a b 482-2 (pNeg-)subp0 4835 p0-9:subp3 48410 p10-99 485-3 pNeg(-subp1) 486-1 pNeg(-subp1) 4871 subp3 4883 subp3 489100 `p100-99999`(-subp6) 4901000 `p100-99999`(-subp6) 491101 `p100-99999`(-subp7) 492SELECT * FROM t1 AS PARTITION; 493ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION' at line 1 494SELECT * FROM t1 AS `PARTITION`; 495a b 496-2 (pNeg-)subp0 4975 p0-9:subp3 49810 p10-99 499-3 pNeg(-subp1) 500-1 pNeg(-subp1) 5011 subp3 5023 subp3 503100 `p100-99999`(-subp6) 5041000 `p100-99999`(-subp6) 505101 `p100-99999`(-subp7) 506# 507# Test REPLACE 508# 509FLUSH STATUS; 510REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!'); 511ERROR HY000: Found a row not matching the given partition set 512SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 513WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 514VARIABLE_NAME VARIABLE_VALUE 515HANDLER_ROLLBACK 1 516HANDLER_TMP_WRITE 24 517HANDLER_WRITE 1 518# 2 locks (1 ha_partition) x 2 (lock/unlock), Was 4 locks before WL4443 519# explicit pruning says part_id 0 and implicit pruning says part_id 1 520# so no partition will be locked! 521# 0 rollback (since no locked partition) 522# 17 writes (I_S internal) 523FLUSH STATUS; 524REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'Insert by REPLACE'); 525SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 526WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 527VARIABLE_NAME VARIABLE_VALUE 528HANDLER_COMMIT 1 529HANDLER_TMP_WRITE 24 530HANDLER_WRITE 2 531# 1 commit 532# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 533# 18 writes (17 I_S internal, 1 ha_innobase) 534SELECT * FROM t1 PARTITION (pNeg); 535a b 536-2 (pNeg-)subp0 537-21 Insert by REPLACE 538-3 pNeg(-subp1) 539-1 pNeg(-subp1) 540FLUSH STATUS; 541REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE'); 542SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 543WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 544VARIABLE_NAME VARIABLE_VALUE 545HANDLER_COMMIT 1 546HANDLER_READ_KEY 2 547HANDLER_TMP_WRITE 24 548HANDLER_UPDATE 2 549HANDLER_WRITE 2 550# 1 commit 551# 4 locks (1 ha_partition + 1 ha_innobase) x 2 (lock/unlock) 552# 2 read key (1 innobase_get_index when init the index + 1 index read 553# to get the position to update) 554# 1 update (updated one row, since there is no delete trigger, update 555# is used instead of delete+insert) 556# 18 write (17 from I_S, 1 for the failed insert) 557SELECT * FROM t1 PARTITION (pNeg); 558a b 559-2 (pNeg-)subp0 560-3 pNeg(-subp1) 561-1 pNeg(-subp1) 562-21 REPLACEd by REPLACE 563FLUSH STATUS; 564LOCK TABLE t1 WRITE; 565SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 566WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 567VARIABLE_NAME VARIABLE_VALUE 568HANDLER_COMMIT 1 569HANDLER_TMP_WRITE 24 570# 1 commit 571# 9 locks 572# 17 write (internal I_S) 573DELETE FROM t1 PARTITION(subp1) WHERE b = "REPLACEd by REPLACE"; 574SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 575WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 576VARIABLE_NAME VARIABLE_VALUE 577HANDLER_COMMIT 2 578HANDLER_DELETE 2 579HANDLER_READ_KEY 1 580HANDLER_READ_NEXT 1 581HANDLER_READ_RND_NEXT 28 582HANDLER_TMP_WRITE 51 583# + 1 commit 584# + 1 delete (one row deleted) 585# + 3 read key (1 innodb_get_index in records_in_range, 586# 1 innodb_get_index in index_init, 1 index_read in index_read_first) 587# + 1 read next (search for another row in secondary index) 588# + 19 rnd next (internal I_S) 589# + 18 write (internal I_S) 590REPLACE INTO t1 PARTITION (subp0) VALUES (-21, 'Should fail!'); 591ERROR HY000: Found a row not matching the given partition set 592SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 593WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 594VARIABLE_NAME VARIABLE_VALUE 595HANDLER_COMMIT 2 596HANDLER_DELETE 2 597HANDLER_READ_KEY 1 598HANDLER_READ_NEXT 1 599HANDLER_READ_RND_NEXT 56 600HANDLER_ROLLBACK 1 601HANDLER_TMP_WRITE 78 602HANDLER_WRITE 1 603# Failed before start_stmt/execution. 604# + 19 rnd next (internal I_S) 605# 0 rollback (No partition had called start_stmt, all parts pruned) 606# + 18 write (internal I_S) 607REPLACE INTO t1 PARTITION (pNeg) VALUES (-21, 'Insert by REPLACE'); 608SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 609WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 610VARIABLE_NAME VARIABLE_VALUE 611HANDLER_COMMIT 3 612HANDLER_DELETE 2 613HANDLER_READ_KEY 1 614HANDLER_READ_NEXT 1 615HANDLER_READ_RND_NEXT 84 616HANDLER_ROLLBACK 1 617HANDLER_TMP_WRITE 105 618HANDLER_WRITE 3 619# + 1 commit 620# + 19 rnd next (internal I_S) 621# + 19 write (18 internal I_S + 1 real write) 622REPLACE INTO t1 PARTITION (subp1) VALUES (-21, 'REPLACEd by REPLACE'); 623SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 624WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 625VARIABLE_NAME VARIABLE_VALUE 626HANDLER_COMMIT 4 627HANDLER_DELETE 2 628HANDLER_READ_KEY 3 629HANDLER_READ_NEXT 1 630HANDLER_READ_RND_NEXT 112 631HANDLER_ROLLBACK 1 632HANDLER_TMP_WRITE 132 633HANDLER_UPDATE 2 634HANDLER_WRITE 5 635# + 1 commit 636# + 2 read key (see non locked query) 637# + 19 rnd next (internal I_S) 638# + 1 update (see non locked query) 639# + 19 write (18 internal I_S + 1 failed write) 640SELECT * FROM t1 PARTITION (subp1); 641a b 642-3 pNeg(-subp1) 643-1 pNeg(-subp1) 644-21 REPLACEd by REPLACE 645SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 646WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 647VARIABLE_NAME VARIABLE_VALUE 648HANDLER_COMMIT 5 649HANDLER_DELETE 2 650HANDLER_READ_FIRST 1 651HANDLER_READ_KEY 3 652HANDLER_READ_NEXT 4 653HANDLER_READ_RND_NEXT 140 654HANDLER_ROLLBACK 1 655HANDLER_TMP_WRITE 159 656HANDLER_UPDATE 2 657HANDLER_WRITE 5 658# + 1 commit 659# + 1 read first 660# + 2 read key 661# + 3 read next 662# + 19 rnd next (internal I_S) 663# + 18 write (internal I_S) 664UNLOCK TABLES; 665SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 666WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 667VARIABLE_NAME VARIABLE_VALUE 668HANDLER_COMMIT 5 669HANDLER_DELETE 2 670HANDLER_READ_FIRST 1 671HANDLER_READ_KEY 3 672HANDLER_READ_NEXT 4 673HANDLER_READ_RND_NEXT 168 674HANDLER_ROLLBACK 1 675HANDLER_TMP_WRITE 186 676HANDLER_UPDATE 2 677HANDLER_WRITE 5 678# + 9 locks 679# + 19 rnd next (internal I_S) 680# + 18 write (internal I_S) 681# 682# Test LOAD 683# 684SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 685a b 686-2 (pNeg-)subp0 68710 p10-99 688-3 pNeg(-subp1) 689-1 pNeg(-subp1) 690-21 REPLACEd by REPLACE 691FLUSH STATUS; 692SELECT * FROM t1 PARTITION (pNeg, `p10-99`) INTO OUTFILE 'loadtest.txt'; 693SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 694WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 695VARIABLE_NAME VARIABLE_VALUE 696HANDLER_COMMIT 1 697HANDLER_READ_FIRST 4 698HANDLER_READ_NEXT 5 699HANDLER_TMP_WRITE 24 700# 1 commit 701# 10 locks (1 ha_partition + 4 ha_innobase) x 2 (lock/unlock) 702# 4 read first (for reading the first row in 4 partitions) 703# 8 read key (4 from read first + 4 for index init) 704# 5 read next (one after each row) 705# 17 write (internal I_S) 706FLUSH STATUS; 707ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`; 708SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 709WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 710VARIABLE_NAME VARIABLE_VALUE 711HANDLER_COMMIT 1 712HANDLER_TMP_WRITE 24 713# 10 locks (table + 4 partition) x (lock + unlock) 714SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 715a b 716FLUSH STATUS; 717LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg); 718ERROR HY000: Found a row not matching the given partition set 719SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 720WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 721VARIABLE_NAME VARIABLE_VALUE 722HANDLER_ROLLBACK 1 723HANDLER_TMP_WRITE 24 724HANDLER_WRITE 3 725# 6 locks (1 ha_partition + 2 ha_innobase) x 2 (lock+unlock) 726# 1 rollback 727SELECT * FROM t1 PARTITION (pNeg, `p10-99`); 728a b 729FLUSH STATUS; 730LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, subp4, subp5); 731SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 732WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 733VARIABLE_NAME VARIABLE_VALUE 734HANDLER_COMMIT 1 735HANDLER_TMP_WRITE 24 736HANDLER_WRITE 10 737# 10 lock (1 ha_partition + 4 ha_innobase) x 2 (lock + unlock) 738ALTER TABLE t1 TRUNCATE PARTITION pNeg, `p10-99`; 739FLUSH STATUS; 740LOCK TABLE t1 WRITE; 741SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 742WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 743VARIABLE_NAME VARIABLE_VALUE 744HANDLER_COMMIT 1 745HANDLER_TMP_WRITE 24 746# 9 locks 747# 18 read key (ALTER forces table to be closed, see above for open) 748LOAD DATA INFILE 'loadtest.txt' INTO TABLE t1 PARTITION (pNeg, `p10-99`); 749SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 750WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 751VARIABLE_NAME VARIABLE_VALUE 752HANDLER_COMMIT 2 753HANDLER_READ_RND_NEXT 28 754HANDLER_TMP_WRITE 51 755HANDLER_WRITE 10 756# + 23 write (18 internal I_S + 5 rows) 757UNLOCK TABLES; 758SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 759WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 760VARIABLE_NAME VARIABLE_VALUE 761HANDLER_COMMIT 2 762HANDLER_READ_RND_NEXT 56 763HANDLER_TMP_WRITE 78 764HANDLER_WRITE 10 765# + 9 locks 766# 767# Test UPDATE 768# 769FLUSH STATUS; 770UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated'); 771SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 772WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 773VARIABLE_NAME VARIABLE_VALUE 774HANDLER_COMMIT 1 775HANDLER_READ_RND_NEXT 2 776HANDLER_TMP_WRITE 24 777HANDLER_UPDATE 2 778# 1 commit 779# 4 lock (1 ha_partition + 1 ha_innobase) x 2 (lock + unlock) 780# 1 read first (read first row, called from first rnd_next) 781# 2 read key (innobase_get_index from rnd_init + 782# read next row from second rnd_next) 783# 1 update (update the row) 784SELECT * FROM t1 PARTITION (subp0) ORDER BY a; 785a b 786-2 (pNeg-)subp0, Updated 787FLUSH STATUS; 788UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = -2; 789SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 790WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 791VARIABLE_NAME VARIABLE_VALUE 792HANDLER_COMMIT 1 793HANDLER_READ_KEY 1 794HANDLER_TMP_WRITE 24 795HANDLER_UPDATE 2 796# 1 commit 797# 4 lock 798# 1 read key 799# 1 update 800FLUSH STATUS; 801UPDATE t1 PARTITION(subp0) SET a = -4, b = concat(b, ', Updated from a = -2') 802WHERE a = -2; 803SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 804WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 805VARIABLE_NAME VARIABLE_VALUE 806HANDLER_COMMIT 1 807HANDLER_READ_KEY 1 808HANDLER_READ_RND 1 809HANDLER_TMP_WRITE 24 810HANDLER_UPDATE 2 811# 1 commit 812# 4 lock 813# 2 read key - (2 index read) 814# 1 read rnd - rnd_pos 815# 1 update 816FLUSH STATUS; 817UPDATE t1 PARTITION(subp0) SET b = concat(b, ', Updated2') WHERE a = 100; 818SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 819WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 820VARIABLE_NAME VARIABLE_VALUE 821HANDLER_COMMIT 1 822HANDLER_TMP_WRITE 24 823# Nothing, since impossible PARTITION+WHERE clause. 824FLUSH STATUS; 825UPDATE t1 PARTITION(subp0) SET a = -2, b = concat(b, ', Updated from a = 100') 826WHERE a = 100; 827SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 828WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 829VARIABLE_NAME VARIABLE_VALUE 830HANDLER_COMMIT 1 831HANDLER_TMP_WRITE 24 832# Nothing, since impossible PARTITION+WHERE clause. 833FLUSH STATUS; 834UPDATE t1 PARTITION(`p100-99999`) SET a = -2, b = concat(b, ', Updated from a = 100') 835WHERE a = 100; 836ERROR HY000: Found a row not matching the given partition set 837SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 838WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 839VARIABLE_NAME VARIABLE_VALUE 840HANDLER_READ_KEY 1 841HANDLER_READ_RND 1 842HANDLER_ROLLBACK 1 843HANDLER_TMP_WRITE 24 844HANDLER_UPDATE 1 845# 6 lock 846# 4 read key (1 index init + 1 index read + 1 rnd init + 1 rnd pos) 847# 1 read rnd (rnd pos) 848# 1 rollback 849FLUSH STATUS; 850UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -4, b = concat(b, ', Updated from a = 100') 851WHERE a = 100; 852Got one of the listed errors 853SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 854WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 855VARIABLE_NAME VARIABLE_VALUE 856HANDLER_READ_KEY 1 857HANDLER_READ_RND 1 858HANDLER_ROLLBACK 1 859HANDLER_TMP_WRITE 24 860HANDLER_UPDATE 1 861HANDLER_WRITE 1 862# 10 locks 863# 4 read key 864# 1 read rnd 865# 1 rollback 866# 18 write (17 internal I_S + 1 failed insert) 867FLUSH STATUS; 868UPDATE t1 PARTITION(`p100-99999`, pNeg) SET a = -222, b = concat(b, ', Updated from a = 100') 869WHERE a = 100; 870SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 871WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 872VARIABLE_NAME VARIABLE_VALUE 873HANDLER_COMMIT 1 874HANDLER_DELETE 1 875HANDLER_READ_KEY 1 876HANDLER_READ_RND 1 877HANDLER_TMP_WRITE 24 878HANDLER_UPDATE 1 879HANDLER_WRITE 1 880# 1 commit 881# 1 delete 882# 4 read key 883# 1 read rnd 884# 18 write (17 internal I_S + 1 insert) 885SELECT * FROM t1 ORDER BY a; 886a b 887-222 `p100-99999`(-subp6), Updated from a = 100 888-21 REPLACEd by REPLACE 889-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 890-3 pNeg(-subp1) 891-1 pNeg(-subp1) 8921 subp3 8933 subp3 8945 p0-9:subp3 89510 p10-99 896101 `p100-99999`(-subp7) 8971000 `p100-99999`(-subp6) 898# Test of non matching partition (i.e ER_NO_PARTITION_FOUND) 899FLUSH STATUS; 900UPDATE t1 SET b = concat(b, ', Updated2') WHERE a = 1000000; 901SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 902WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 903VARIABLE_NAME VARIABLE_VALUE 904HANDLER_COMMIT 1 905HANDLER_TMP_WRITE 24 906# Nothing (no matching partition found) 907FLUSH STATUS; 908UPDATE t1 PARTITION (pNeg) SET b = concat(b, ', Updated2') WHERE a = 1000000; 909SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 910WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 911VARIABLE_NAME VARIABLE_VALUE 912HANDLER_COMMIT 1 913HANDLER_TMP_WRITE 24 914# Nothing (no matching partition found) 915FLUSH STATUS; 916LOCK TABLE t1 WRITE; 917SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 918WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 919VARIABLE_NAME VARIABLE_VALUE 920HANDLER_COMMIT 1 921HANDLER_TMP_WRITE 24 922# 9 locks 923UPDATE t1 PARTITION (subp7) SET b = concat(b, ', Updated to 103'), a = 103 WHERE a = 101; 924SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 925WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 926VARIABLE_NAME VARIABLE_VALUE 927HANDLER_COMMIT 2 928HANDLER_READ_KEY 1 929HANDLER_READ_RND 1 930HANDLER_READ_RND_NEXT 28 931HANDLER_TMP_WRITE 51 932HANDLER_UPDATE 2 933# + 4 read key 934# + 1 read rnd 935# + 1 update 936UPDATE t1 PARTITION (`p100-99999`) SET b = concat(b, ', Updated to 110'), a = 110 WHERE a = 103; 937SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 938WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 939VARIABLE_NAME VARIABLE_VALUE 940HANDLER_COMMIT 3 941HANDLER_DELETE 1 942HANDLER_READ_KEY 2 943HANDLER_READ_RND 2 944HANDLER_READ_RND_NEXT 56 945HANDLER_TMP_WRITE 78 946HANDLER_UPDATE 3 947HANDLER_WRITE 1 948# + 1 delete 949# + 4 read key 950# + 1 read rnd 951# + 19 write (18 internal I_S + 1 insert) 952UNLOCK TABLES; 953SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 954WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 955VARIABLE_NAME VARIABLE_VALUE 956HANDLER_COMMIT 3 957HANDLER_DELETE 1 958HANDLER_READ_KEY 2 959HANDLER_READ_RND 2 960HANDLER_READ_RND_NEXT 84 961HANDLER_TMP_WRITE 105 962HANDLER_UPDATE 3 963HANDLER_WRITE 1 964+ 9 locks 965# 966# Test DELETE 967# 968SELECT * FROM t1 ORDER BY b, a; 969a b 970-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 9715 p0-9:subp3 97210 p10-99 973-3 pNeg(-subp1) 974-1 pNeg(-subp1) 975-21 REPLACEd by REPLACE 9761 subp3 9773 subp3 9781000 `p100-99999`(-subp6) 979-222 `p100-99999`(-subp6), Updated from a = 100 980110 `p100-99999`(-subp7), Updated to 103, Updated to 110 981FLUSH STATUS; 982DELETE FROM t1 PARTITION (pNeg) WHERE a = -1; 983SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 984WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 985VARIABLE_NAME VARIABLE_VALUE 986HANDLER_COMMIT 1 987HANDLER_DELETE 2 988HANDLER_READ_KEY 1 989HANDLER_TMP_WRITE 24 990# 1 delete 991# 4 locks (pruning works!). 992# 1 read key (index read) 993FLUSH STATUS; 994DELETE FROM t1 PARTITION (subp1) WHERE b like '%subp1%'; 995SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 996WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 997VARIABLE_NAME VARIABLE_VALUE 998HANDLER_COMMIT 1 999HANDLER_DELETE 2 1000HANDLER_READ_RND_NEXT 3 1001HANDLER_TMP_WRITE 24 1002# 1 delete 1003# 4 locks 1004# 1 read first 1005# 2 read key 1006# 3 read rnd 1007FLUSH STATUS; 1008LOCK TABLE t1 WRITE; 1009SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1010WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1011VARIABLE_NAME VARIABLE_VALUE 1012HANDLER_COMMIT 1 1013HANDLER_TMP_WRITE 24 1014# 9 locks 1015DELETE FROM t1 PARTITION (subp1) WHERE b = 'p0-9:subp3'; 1016SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1017WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1018VARIABLE_NAME VARIABLE_VALUE 1019HANDLER_COMMIT 2 1020HANDLER_READ_KEY 1 1021HANDLER_READ_RND_NEXT 28 1022HANDLER_TMP_WRITE 51 1023# + 3 read key (1 innodb_get_index in records_in_range 1024# + 1 innobase_get_index in index_init + 1 index read) 1025DELETE FROM t1 PARTITION (`p0-9`) WHERE b = 'p0-9:subp3'; 1026SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1027WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1028VARIABLE_NAME VARIABLE_VALUE 1029HANDLER_COMMIT 3 1030HANDLER_DELETE 2 1031HANDLER_READ_KEY 3 1032HANDLER_READ_NEXT 1 1033HANDLER_READ_RND_NEXT 56 1034HANDLER_TMP_WRITE 78 1035# + 1 delete 1036# + 6 read key (same as above, but for two subpartitions) 1037# + 1 read next (read next after found row) 1038UNLOCK TABLES; 1039SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1040WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1041VARIABLE_NAME VARIABLE_VALUE 1042HANDLER_COMMIT 3 1043HANDLER_DELETE 2 1044HANDLER_READ_KEY 3 1045HANDLER_READ_NEXT 1 1046HANDLER_READ_RND_NEXT 84 1047HANDLER_TMP_WRITE 105 1048# + 9 locks 1049# Test multi-table DELETE 1050# Can be expressed in two different ways. 1051CREATE TABLE t2 LIKE t1; 1052FLUSH STATUS; 1053INSERT INTO t2 PARTITION (`p10-99`, subp3, `p100-99999`) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 1054SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1055WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1056VARIABLE_NAME VARIABLE_VALUE 1057HANDLER_COMMIT 1 1058HANDLER_READ_FIRST 5 1059HANDLER_READ_NEXT 5 1060HANDLER_TMP_WRITE 24 1061HANDLER_WRITE 10 1062# 24 locks (2 table, 5 + 5 subpartitions lock/unlock) 1063FLUSH STATUS; 1064ALTER TABLE t2 TRUNCATE PARTITION `p10-99`, `p0-9`, `p100-99999`; 1065SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1066WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1067VARIABLE_NAME VARIABLE_VALUE 1068HANDLER_COMMIT 1 1069HANDLER_TMP_WRITE 24 1070# 14 locks (1 table, 6 subpartitions lock/unlock) 1071FLUSH STATUS; 1072INSERT INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 1073ERROR HY000: Found a row not matching the given partition set 1074SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1075WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1076VARIABLE_NAME VARIABLE_VALUE 1077HANDLER_READ_FIRST 5 1078HANDLER_ROLLBACK 1 1079HANDLER_TMP_WRITE 24 1080HANDLER_WRITE 1 1081# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) 1082FLUSH STATUS; 1083INSERT IGNORE INTO t2 PARTITION (subp3) SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 1084Warnings: 1085Warning 1748 Found a row not matching the given partition set 1086Warning 1748 Found a row not matching the given partition set 1087Warning 1748 Found a row not matching the given partition set 1088SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1089WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1090VARIABLE_NAME VARIABLE_VALUE 1091HANDLER_COMMIT 1 1092HANDLER_READ_FIRST 5 1093HANDLER_READ_NEXT 5 1094HANDLER_TMP_WRITE 24 1095HANDLER_WRITE 7 1096# 16 locks (2 tables, 1 + 5 subpartitions lock/unlock) 1097TRUNCATE TABLE t2; 1098FLUSH STATUS; 1099INSERT INTO t2 SELECT * FROM t1 PARTITION (subp3, `p10-99`, `p100-99999`); 1100SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1101WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1102VARIABLE_NAME VARIABLE_VALUE 1103HANDLER_COMMIT 1 1104HANDLER_READ_FIRST 5 1105HANDLER_READ_NEXT 5 1106HANDLER_TMP_WRITE 24 1107HANDLER_WRITE 10 1108# 30 locks (2 table, 8 + 5 subpartitions lock/unlock) 1109FLUSH STATUS; 1110CREATE TABLE t3 SELECT * FROM t1 PARTITION (pNeg,subp3,`p100-99999`); 1111SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1112WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1113VARIABLE_NAME VARIABLE_VALUE 1114HANDLER_COMMIT 1 1115HANDLER_READ_FIRST 5 1116HANDLER_READ_NEXT 7 1117HANDLER_TMP_WRITE 24 1118HANDLER_WRITE 7 1119# 14 locks (2 table, 5 subpartitions lock/unlock) 1120SHOW CREATE TABLE t1; 1121Table Create Table 1122t1 CREATE TABLE `t1` ( 1123 `a` int(11) NOT NULL, 1124 `b` varchar(64) DEFAULT NULL, 1125 PRIMARY KEY (`a`), 1126 KEY `b` (`b`,`a`) 1127) ENGINE=InnoDB DEFAULT CHARSET=latin1 1128 PARTITION BY RANGE (`a`) 1129SUBPARTITION BY HASH (`a`) 1130(PARTITION `pNeg` VALUES LESS THAN (0) 1131 (SUBPARTITION `subp0` ENGINE = InnoDB, 1132 SUBPARTITION `subp1` ENGINE = InnoDB), 1133 PARTITION `p0-9` VALUES LESS THAN (10) 1134 (SUBPARTITION `subp2` ENGINE = InnoDB, 1135 SUBPARTITION `subp3` ENGINE = InnoDB), 1136 PARTITION `p10-99` VALUES LESS THAN (100) 1137 (SUBPARTITION `subp4` ENGINE = InnoDB, 1138 SUBPARTITION `subp5` ENGINE = InnoDB), 1139 PARTITION `p100-99999` VALUES LESS THAN (100000) 1140 (SUBPARTITION `subp6` ENGINE = InnoDB, 1141 SUBPARTITION `subp7` ENGINE = InnoDB)) 1142SELECT * FROM t1; 1143a b 1144-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 114510 p10-99 1146-21 REPLACEd by REPLACE 11471 subp3 11483 subp3 11491000 `p100-99999`(-subp6) 1150-222 `p100-99999`(-subp6), Updated from a = 100 1151110 `p100-99999`(-subp7), Updated to 103, Updated to 110 1152SHOW CREATE TABLE t2; 1153Table Create Table 1154t2 CREATE TABLE `t2` ( 1155 `a` int(11) NOT NULL, 1156 `b` varchar(64) DEFAULT NULL, 1157 PRIMARY KEY (`a`), 1158 KEY `b` (`b`,`a`) 1159) ENGINE=InnoDB DEFAULT CHARSET=latin1 1160 PARTITION BY RANGE (`a`) 1161SUBPARTITION BY HASH (`a`) 1162(PARTITION `pNeg` VALUES LESS THAN (0) 1163 (SUBPARTITION `subp0` ENGINE = InnoDB, 1164 SUBPARTITION `subp1` ENGINE = InnoDB), 1165 PARTITION `p0-9` VALUES LESS THAN (10) 1166 (SUBPARTITION `subp2` ENGINE = InnoDB, 1167 SUBPARTITION `subp3` ENGINE = InnoDB), 1168 PARTITION `p10-99` VALUES LESS THAN (100) 1169 (SUBPARTITION `subp4` ENGINE = InnoDB, 1170 SUBPARTITION `subp5` ENGINE = InnoDB), 1171 PARTITION `p100-99999` VALUES LESS THAN (100000) 1172 (SUBPARTITION `subp6` ENGINE = InnoDB, 1173 SUBPARTITION `subp7` ENGINE = InnoDB)) 1174SELECT * FROM t2; 1175a b 117610 p10-99 11771 subp3 11783 subp3 11791000 `p100-99999`(-subp6) 1180110 `p100-99999`(-subp7), Updated to 103, Updated to 110 1181SHOW CREATE TABLE t3; 1182Table Create Table 1183t3 CREATE TABLE `t3` ( 1184 `a` int(11) NOT NULL, 1185 `b` varchar(64) DEFAULT NULL 1186) ENGINE=InnoDB DEFAULT CHARSET=latin1 1187SELECT * FROM t3; 1188a b 1189-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 1190-21 REPLACEd by REPLACE 11911 subp3 11923 subp3 11931000 `p100-99999`(-subp6) 1194-222 `p100-99999`(-subp6), Updated from a = 100 1195110 `p100-99999`(-subp7), Updated to 103, Updated to 110 1196FLUSH STATUS; 1197DELETE t1 PARTITION (pNeg), t3 FROM t1, t3 1198WHERE t1.a = t3.a AND t3.b = 'subp3'; 1199ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION (pNeg), t3 FROM t1, t3 1200WHERE t1.a = t3.a AND t3.b = 'subp3'' at line 1 1201SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1202WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1203VARIABLE_NAME VARIABLE_VALUE 1204HANDLER_TMP_WRITE 24 1205# Multi table delete without any matching rows 1206FLUSH STATUS; 1207DELETE t1, t2 FROM t1 PARTITION (pNeg), t3, t2 PARTITION (subp3) 1208WHERE t1.a = t3.a AND t3.b = 'subp3' AND t3.a = t2.a; 1209SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1210WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1211VARIABLE_NAME VARIABLE_VALUE 1212HANDLER_COMMIT 1 1213HANDLER_READ_RND_NEXT 3 1214HANDLER_TMP_WRITE 24 1215# 12 locks (3 in t1, 1 in t3, 2 in t2) x 2 (lock + unlock) 1216# 1 read first (first rnd_next in t2) 1217# 4 read key (1 innodb_get_index in rnd_init in t2 + index read in t2 1218# + 2 innodb_get_index in index_init in t1) 1219# 3 read rnd next (3 rnd next in t2, 2 rows + 1 empty) 1220# Multi table delete matching all rows in subp3 (2 rows in per table) 1221FLUSH STATUS; 1222DELETE FROM t2, t3 USING t2 PARTITION (`p0-9`), t3, t1 PARTITION (subp3) 1223WHERE t1.a = t3.a AND t3.b = 'subp3' AND t2.a = t1.a; 1224SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1225WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1226VARIABLE_NAME VARIABLE_VALUE 1227HANDLER_COMMIT 1 1228HANDLER_DELETE 6 1229HANDLER_READ_FIRST 1 1230HANDLER_READ_KEY 2 1231HANDLER_READ_NEXT 2 1232HANDLER_READ_RND 4 1233HANDLER_READ_RND_NEXT 16 1234HANDLER_TMP_WRITE 24 1235# 4 delete (2 in t2 + 2 in t3) 1236# 12 locks (3 in t2, 1 in t3, 2 in t1) x 2 (lock + unlock) 1237# 3 read first (1 in t1 + 1 in t3 + 1 in t3, for second row in t1) 1238# 17 read key (1 index_init in t1 + 1 read first in t1 + 1239# 2 index_init in t2 + 1 index read in t2 + 1240# 1 index_init in t3 + 1 index read in t3 + 1241# 1 index read in t2 + 1242# 1 index_init in t3 + 1 index read in t3 + 1243# 2 index_init in t2 + 2 index read in t2 (from rnd_pos) 1244# 1 index_init in t3 + 2 index read in t3 (from rnd_pos)) 1245# 2 read next (1 in t1 + 1 in t1, second row) 1246# 4 read rnd (position on 4 found rows to delete) 1247# 16 rnd next (8 in t3 + 8 in t3, for second row) 1248SELECT * FROM t1 ORDER BY a; 1249a b 1250-222 `p100-99999`(-subp6), Updated from a = 100 1251-21 REPLACEd by REPLACE 1252-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 12531 subp3 12543 subp3 125510 p10-99 1256110 `p100-99999`(-subp7), Updated to 103, Updated to 110 12571000 `p100-99999`(-subp6) 1258SELECT * FROM t2 ORDER BY a; 1259a b 126010 p10-99 1261110 `p100-99999`(-subp7), Updated to 103, Updated to 110 12621000 `p100-99999`(-subp6) 1263SELECT * FROM t3 ORDER BY a; 1264a b 1265-222 `p100-99999`(-subp6), Updated from a = 100 1266-21 REPLACEd by REPLACE 1267-4 (pNeg-)subp0, Updated, Updated2, Updated from a = -2 1268110 `p100-99999`(-subp7), Updated to 103, Updated to 110 12691000 `p100-99999`(-subp6) 1270# Test TRUNCATE TABLE (should fail, since one should use 1271# ALTER TABLE ... TRUNCATE PARTITION instead) 1272TRUNCATE TABLE t1 PARTITION(`p10-99`); 1273ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION(`p10-99`)' at line 1 1274# Test of locking in TRUNCATE PARTITION 1275# Note that it does not support truncating subpartitions 1276FLUSH STATUS; 1277ALTER TABLE t1 TRUNCATE PARTITION pNeg; 1278SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS 1279WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; 1280VARIABLE_NAME VARIABLE_VALUE 1281HANDLER_COMMIT 1 1282HANDLER_TMP_WRITE 24 1283# 6 locks (lock/unlock two subpartitions + table) 1284# Test on non partitioned table 1285SELECT * FROM t3 PARTITION (pNeg); 1286ERROR HY000: PARTITION () clause on non partitioned table 1287DROP TABLE t1, t2, t3; 1288# Test from superseeded WL# 2682 1289drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; 1290CREATE TABLE `t1` ( 1291`id` int(11) default NULL 1292) ENGINE=MyISAM DEFAULT CHARSET=latin1 1293PARTITION BY RANGE (id) ( 1294PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM, 1295PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM, 1296PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM, 1297PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM); 1298INSERT INTO `t1` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), 1299(11), (12), (13), (14), (15), (16), (17), (18), (19), (20); 1300SELECT * FROM t1; 1301id 13021 13032 13043 13054 13065 13076 13087 13098 13109 131110 131211 131312 131413 131514 131615 131716 131817 131918 132019 132120 1322SELECT * FROM t1 PARTITION (p0); 1323id 13241 13252 13263 13274 13285 1329SELECT * FROM t1 PARTITION (p1); 1330id 13316 13327 13338 13349 133510 1336SELECT * FROM t1 PARTITION (p2); 1337id 133811 133912 134013 134114 134215 1343SELECT * FROM t1 PARTITION (p3); 1344id 134516 134617 134718 134819 134920 1350SELECT * FROM t1 PARTITION (p3) WHERE id = 2; 1351id 1352SELECT * FROM t1 PARTITION (foo); 1353ERROR HY000: Unknown partition 'foo' in table 't1' 1354CREATE TABLE `t2` ( 1355`id` int(11) NOT NULL DEFAULT 0, 1356PRIMARY KEY (`id`) 1357) ENGINE=MyISAM DEFAULT CHARSET=latin1 1358PARTITION BY RANGE (id) ( 1359PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM, 1360PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM, 1361PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM, 1362PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM); 1363INSERT INTO `t2` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), 1364(11), (12), (13), (14), (15), (16), (17), (18), (19), (20); 1365SELECT * FROM t2; 1366id 13671 13682 13693 13704 13715 13726 13737 13748 13759 137610 137711 137812 137913 138014 138115 138216 138317 138418 138519 138620 1387SELECT * FROM t2 PARTITION (p0); 1388id 13891 13902 13913 13924 13935 1394SELECT * FROM t2 PARTITION (p1); 1395id 13966 13977 13988 13999 140010 1401SELECT * FROM t2 PARTITION (p2); 1402id 140311 140412 140513 140614 140715 1408SELECT * FROM t2 PARTITION (p3); 1409id 141016 141117 141218 141319 141420 1415SELECT * FROM t2 PARTITION (p3) ORDER BY id; 1416id 141716 141817 141918 142019 142120 1422SELECT * FROM t2 PARTITION (p3) WHERE id = 2; 1423id 1424SELECT * FROM t2 PARTITION (foo); 1425ERROR HY000: Unknown partition 'foo' in table 't2' 1426CREATE TABLE `t3` ( 1427`id` int(32) default NULL, 1428`name` varchar(32) default NULL 1429) ENGINE=MyISAM DEFAULT CHARSET=latin1 1430PARTITION BY LIST (id) ( 1431PARTITION p0 VALUES IN (1,3,5,7), 1432PARTITION p1 VALUES IN (0,2,4,6,8), 1433PARTITION p2 VALUES IN (9,10,11,12,13) 1434); 1435INSERT 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'); 1436SELECT * FROM `t3`; 1437id name 14381 first 14393 third 14405 fifth 14417 seventh 14420 zilch 14432 second 14444 fourth 14456 sixth 14468 eighth 14479 ninth 144810 tenth 144911 eleventh 145012 twelfth 145113 thirteenth 1452SELECT * FROM `t3` PARTITION (p0); 1453id name 14541 first 14553 third 14565 fifth 14577 seventh 1458SELECT * FROM `t3` PARTITION (p1); 1459id name 14600 zilch 14612 second 14624 fourth 14636 sixth 14648 eighth 1465SELECT * FROM `t3` PARTITION (p2); 1466id name 14679 ninth 146810 tenth 146911 eleventh 147012 twelfth 147113 thirteenth 1472SELECT * FROM `t3` PARTITION (p2) ORDER BY id; 1473id name 14749 ninth 147510 tenth 147611 eleventh 147712 twelfth 147813 thirteenth 1479DROP TABLE IF EXISTS `t4`; 1480Warnings: 1481Note 1051 Unknown table 'test.t4' 1482CREATE TABLE `t4` ( 1483`id` int(32) default NULL 1484) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH (id) ; 1485INSERT INTO `t4` SELECT * FROM `t2`; 1486INSERT INTO `t4` SELECT * FROM `t2` ORDER BY id; 1487CREATE TABLE `t5` ( 1488id int(32), 1489name varchar(64), 1490purchased date) 1491PARTITION BY RANGE( YEAR(purchased) ) 1492SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( 1493PARTITION p0 VALUES LESS THAN (1990) ( 1494SUBPARTITION s0, 1495SUBPARTITION s1 1496), 1497PARTITION p1 VALUES LESS THAN (2000) ( 1498SUBPARTITION s2, 1499SUBPARTITION s3 1500), 1501PARTITION p2 VALUES LESS THAN MAXVALUE ( 1502SUBPARTITION s4, 1503SUBPARTITION s5 1504) 1505); 1506INSERT INTO `t5` VALUES (1, 'aaaaaaa', '2006-01-05 00:00:00'); 1507INSERT INTO `t5` VALUES (2, 'bbbbbbb', '2005-08-05 00:00:00'); 1508INSERT INTO `t5` VALUES (3, 'ccccccc', '1985-08-07 00:00:00'); 1509INSERT INTO `t5` VALUES (4, 'ddddddd', '2000-01-01 00:00:00'); 1510INSERT INTO `t5` VALUES (5, 'eeeeeee', '1999-12-01 00:00:00'); 1511INSERT INTO `t5` VALUES (6, 'fffffff', '2003-11-12 00:00:00'); 1512INSERT INTO `t5` VALUES (7, 'ggggggg', '1990-01-05 00:00:00'); 1513INSERT INTO `t5` VALUES (8, 'hhhhhhh', '1978-01-05 00:00:00'); 1514INSERT INTO `t5` VALUES (9, 'iiiiiii', '1979-01-05 00:00:00'); 1515INSERT INTO `t5` VALUES (10, 'jjjjjjj', '1992-01-05 00:00:00'); 1516INSERT INTO `t5` VALUES (11, 'kkkkkkk', '1993-01-05 00:00:00'); 1517INSERT INTO `t5` VALUES (12, 'mmmmmmm', '1994-01-05 00:00:00'); 1518INSERT INTO `t5` VALUES (13, 'nnnnnnn', '1989-01-05 00:00:00'); 1519INSERT INTO `t5` VALUES (14, 'ooooooo', '1983-12-05 00:00:00'); 1520INSERT INTO `t5` VALUES (15, 'ppppppp', '1986-06-05 00:00:00'); 1521INSERT INTO `t5` VALUES (16, 'qqqqqqq', '1974-04-11 00:00:00'); 1522INSERT INTO `t5` VALUES (17, 'qqqqqqq', '1960-03-15 00:00:00'); 1523INSERT INTO `t5` VALUES (18, 'sssssss', '1950-09-23 00:00:00'); 1524INSERT INTO `t5` VALUES (19, 'ttttttt', '1999-08-02 00:00:00'); 1525INSERT INTO `t5` VALUES (20, 'uuuuuuu', '1994-05-28 00:00:00'); 1526SELECT * FROM `t5`; 1527id name purchased 15288 hhhhhhh 1978-01-05 152913 nnnnnnn 1989-01-05 153014 ooooooo 1983-12-05 153118 sssssss 1950-09-23 15323 ccccccc 1985-08-07 15339 iiiiiii 1979-01-05 153415 ppppppp 1986-06-05 153516 qqqqqqq 1974-04-11 153617 qqqqqqq 1960-03-15 15375 eeeeeee 1999-12-01 153812 mmmmmmm 1994-01-05 15397 ggggggg 1990-01-05 154010 jjjjjjj 1992-01-05 154111 kkkkkkk 1993-01-05 154219 ttttttt 1999-08-02 154320 uuuuuuu 1994-05-28 15442 bbbbbbb 2005-08-05 15456 fffffff 2003-11-12 15461 aaaaaaa 2006-01-05 15474 ddddddd 2000-01-01 1548SELECT * FROM `t5` PARTITION(p0) ORDER BY id; 1549id name purchased 15503 ccccccc 1985-08-07 15518 hhhhhhh 1978-01-05 15529 iiiiiii 1979-01-05 155313 nnnnnnn 1989-01-05 155414 ooooooo 1983-12-05 155515 ppppppp 1986-06-05 155616 qqqqqqq 1974-04-11 155717 qqqqqqq 1960-03-15 155818 sssssss 1950-09-23 1559SELECT * FROM `t5` PARTITION(s0) ORDER BY id; 1560id name purchased 15618 hhhhhhh 1978-01-05 156213 nnnnnnn 1989-01-05 156314 ooooooo 1983-12-05 156418 sssssss 1950-09-23 1565SELECT * FROM `t5` PARTITION(s1) ORDER BY id; 1566id name purchased 15673 ccccccc 1985-08-07 15689 iiiiiii 1979-01-05 156915 ppppppp 1986-06-05 157016 qqqqqqq 1974-04-11 157117 qqqqqqq 1960-03-15 1572SELECT * FROM `t5` PARTITION(p1) ORDER BY id; 1573id name purchased 15745 eeeeeee 1999-12-01 15757 ggggggg 1990-01-05 157610 jjjjjjj 1992-01-05 157711 kkkkkkk 1993-01-05 157812 mmmmmmm 1994-01-05 157919 ttttttt 1999-08-02 158020 uuuuuuu 1994-05-28 1581SELECT * FROM `t5` PARTITION(s2) ORDER BY id; 1582id name purchased 15835 eeeeeee 1999-12-01 158412 mmmmmmm 1994-01-05 1585SELECT * FROM `t5` PARTITION(s3) ORDER BY id; 1586id name purchased 15877 ggggggg 1990-01-05 158810 jjjjjjj 1992-01-05 158911 kkkkkkk 1993-01-05 159019 ttttttt 1999-08-02 159120 uuuuuuu 1994-05-28 1592SELECT * FROM `t5` PARTITION(p2) ORDER BY id; 1593id name purchased 15941 aaaaaaa 2006-01-05 15952 bbbbbbb 2005-08-05 15964 ddddddd 2000-01-01 15976 fffffff 2003-11-12 1598SELECT * FROM `t5` PARTITION(s4) ORDER BY id; 1599id name purchased 16002 bbbbbbb 2005-08-05 16016 fffffff 2003-11-12 1602SELECT * FROM `t5` PARTITION(s5) ORDER BY id; 1603id name purchased 16041 aaaaaaa 2006-01-05 16054 ddddddd 2000-01-01 1606drop table t1,t2,t3,t4,t5; 1607create table t1 (a int) partition by hash(a) partitions 3; 1608insert into t1 values(1),(2),(3); 1609explain partitions select * from t1 where a=1; 1610id select_type table partitions type possible_keys key key_len ref rows Extra 16111 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where 1612explain partitions select * from t1 partition (p1) where a=1; 1613id select_type table partitions type possible_keys key key_len ref rows Extra 16141 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where 1615explain partitions select * from t1 partition (p1) where a=1 or a=2; 1616id select_type table partitions type possible_keys key key_len ref rows Extra 16171 SIMPLE t1 p1 ALL NULL NULL NULL NULL 2 Using where 1618explain partitions select * from t1 partition (p2) where a=1; 1619id select_type table partitions type possible_keys key key_len ref rows Extra 16201 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1621drop table t1; 1622# 1623# Bug#59864: Crash if table empty: DELETE FROM t2 PARTITION (subp3). 1624# 1625CREATE TABLE t1 1626(a INT NOT NULL, 1627b varchar (64), 1628INDEX (b,a), 1629PRIMARY KEY (a)) 1630PARTITION BY RANGE (a) 1631SUBPARTITION BY HASH (a) SUBPARTITIONS 3 1632(PARTITION pNeg VALUES LESS THAN (0) 1633(SUBPARTITION subp0, 1634SUBPARTITION subp1, 1635SUBPARTITION subp2), 1636PARTITION `p0-29` VALUES LESS THAN (30) 1637(SUBPARTITION subp3, 1638SUBPARTITION subp4, 1639SUBPARTITION subp5), 1640PARTITION `p30-299` VALUES LESS THAN (300) 1641(SUBPARTITION subp6, 1642SUBPARTITION subp7, 1643SUBPARTITION subp8), 1644PARTITION `p300-2999` VALUES LESS THAN (3000) 1645(SUBPARTITION subp9, 1646SUBPARTITION subp10, 1647SUBPARTITION subp11), 1648PARTITION `p3000-299999` VALUES LESS THAN (300000) 1649(SUBPARTITION subp12, 1650SUBPARTITION subp13, 1651SUBPARTITION subp14)); 1652SHOW CREATE TABLE t1; 1653Table Create Table 1654t1 CREATE TABLE `t1` ( 1655 `a` int(11) NOT NULL, 1656 `b` varchar(64) DEFAULT NULL, 1657 PRIMARY KEY (`a`), 1658 KEY `b` (`b`,`a`) 1659) ENGINE=InnoDB DEFAULT CHARSET=latin1 1660 PARTITION BY RANGE (`a`) 1661SUBPARTITION BY HASH (`a`) 1662(PARTITION `pNeg` VALUES LESS THAN (0) 1663 (SUBPARTITION `subp0` ENGINE = InnoDB, 1664 SUBPARTITION `subp1` ENGINE = InnoDB, 1665 SUBPARTITION `subp2` ENGINE = InnoDB), 1666 PARTITION `p0-29` VALUES LESS THAN (30) 1667 (SUBPARTITION `subp3` ENGINE = InnoDB, 1668 SUBPARTITION `subp4` ENGINE = InnoDB, 1669 SUBPARTITION `subp5` ENGINE = InnoDB), 1670 PARTITION `p30-299` VALUES LESS THAN (300) 1671 (SUBPARTITION `subp6` ENGINE = InnoDB, 1672 SUBPARTITION `subp7` ENGINE = InnoDB, 1673 SUBPARTITION `subp8` ENGINE = InnoDB), 1674 PARTITION `p300-2999` VALUES LESS THAN (3000) 1675 (SUBPARTITION `subp9` ENGINE = InnoDB, 1676 SUBPARTITION `subp10` ENGINE = InnoDB, 1677 SUBPARTITION `subp11` ENGINE = InnoDB), 1678 PARTITION `p3000-299999` VALUES LESS THAN (300000) 1679 (SUBPARTITION `subp12` ENGINE = InnoDB, 1680 SUBPARTITION `subp13` ENGINE = InnoDB, 1681 SUBPARTITION `subp14` ENGINE = InnoDB)) 1682INSERT INTO t1 VALUES (-9, "negative nine"), (-8, "-8"), (-7, "-7"), (-6, "-6"), (-5, "-5"), (-4, "-4"), (-3, "-3"), (-2, "-2"), (-1, "-1"); 1683INSERT INTO t1 VALUES (9, "nine"), (8, "8"), (7, "7"), (6, "6"), (5, "5"), (4, "4"), (3, "3"), (2, "2"), (1, "1"); 1684INSERT INTO t1 VALUES (39, "Thirty nine"), (38, "38"), (37, "37"), (36, "36"), (35, "35"), (34, "34"), (33, "33"), (32, "32"), (31, "31"); 1685INSERT INTO t1 VALUES (339, "Three hundred thirty nine"), (338, "338"), (337, "337"), (336, "336"), (335, "335"), (334, "334"), (333, "333"), (332, "332"), (331, "331"); 1686INSERT 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"); 1687SELECT * FROM t1; 1688a b 1689-1 -1 1690-2 -2 1691-3 -3 1692-4 -4 1693-5 -5 1694-6 -6 1695-7 -7 1696-8 -8 1697-9 negative nine 16981 1 16992 2 17003 3 170131 31 170232 32 170333 33 1704331 331 1705332 332 1706333 333 17073331 3331 17083332 3332 17093333 3333 17103334 3334 17113335 3335 17123336 3336 17133337 3337 17143338 3338 17153339 Three thousand three hundred thirty nine 1716334 334 1717335 335 1718336 336 1719337 337 1720338 338 1721339 Three hundred thirty nine 172234 34 172335 35 172436 36 172537 37 172638 38 172739 Thirty nine 17284 4 17295 5 17306 6 17317 7 17328 8 17339 nine 1734SELECT * FROM t1 PARTITION (subp3); 1735a b 17363 3 17376 6 17389 nine 1739DELETE FROM t1 PARTITION (subp3); 1740SELECT * FROM t1; 1741a b 1742-1 -1 1743-2 -2 1744-3 -3 1745-4 -4 1746-5 -5 1747-6 -6 1748-7 -7 1749-8 -8 1750-9 negative nine 17511 1 17522 2 175331 31 175432 32 175533 33 1756331 331 1757332 332 1758333 333 17593331 3331 17603332 3332 17613333 3333 17623334 3334 17633335 3335 17643336 3336 17653337 3337 17663338 3338 17673339 Three thousand three hundred thirty nine 1768334 334 1769335 335 1770336 336 1771337 337 1772338 338 1773339 Three hundred thirty nine 177434 34 177535 35 177636 36 177737 37 177838 38 177939 Thirty nine 17804 4 17815 5 17827 7 17838 8 1784SELECT * FROM t1 PARTITION (subp3); 1785a b 1786DELETE FROM t1 PARTITION (`p0-29`); 1787SELECT * FROM t1; 1788a b 1789-1 -1 1790-2 -2 1791-3 -3 1792-4 -4 1793-5 -5 1794-6 -6 1795-7 -7 1796-8 -8 1797-9 negative nine 179831 31 179932 32 180033 33 1801331 331 1802332 332 1803333 333 18043331 3331 18053332 3332 18063333 3333 18073334 3334 18083335 3335 18093336 3336 18103337 3337 18113338 3338 18123339 Three thousand three hundred thirty nine 1813334 334 1814335 335 1815336 336 1816337 337 1817338 338 1818339 Three hundred thirty nine 181934 34 182035 35 182136 36 182237 37 182338 38 182439 Thirty nine 1825SELECT * FROM t1 PARTITION (`p0-29`); 1826a b 1827ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 3; 1828DELETE FROM t1 PARTITION (p2); 1829SELECT * FROM t1; 1830a b 1831-1 -1 1832-3 -3 1833-4 -4 1834-6 -6 1835-7 -7 1836-9 negative nine 183731 31 183833 33 1839331 331 1840333 333 18413331 3331 18423333 3333 18433334 3334 18443336 3336 18453337 3337 18463339 Three thousand three hundred thirty nine 1847334 334 1848336 336 1849337 337 1850339 Three hundred thirty nine 185134 34 185236 36 185337 37 185439 Thirty nine 1855SELECT * FROM t1 PARTITION (p2); 1856a b 1857DROP TABLE t1; 1858# 1859# Test explicit partition selection on a non partitioned temp table 1860# 1861CREATE TEMPORARY TABLE t1 (a INT); 1862SELECT * FROM t1 PARTITION(pNonexisting); 1863ERROR HY000: PARTITION () clause on non partitioned table 1864DROP TEMPORARY TABLE t1; 1865# 1866# Test CREATE LIKE does not take PARTITION clause 1867# 1868CREATE TABLE t1 (a INT) PARTITION BY HASH (a) PARTITIONS 3; 1869CREATE TABLE t2 LIKE t1 PARTITION (p0, p2); 1870ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PARTITION (p0, p2)' at line 1 1871DROP TABLE t1; 1872SET @@default_storage_engine = @old_default_storage_engine; 1873# 1874# MDEV-14815 - Server crash or AddressSanitizer errors or valgrind warnings in thr_lock / has_old_lock upon FLUSH TABLES 1875# 1876CREATE TABLE t1 (i INT) ENGINE=MEMORY PARTITION BY RANGE (i) (PARTITION p0 VALUES LESS THAN (4), PARTITION pm VALUES LESS THAN MAXVALUE); 1877CREATE TABLE t2 (i INT) ENGINE=MEMORY; 1878LOCK TABLE t1 WRITE, t2 WRITE; 1879SELECT * FROM t1 PARTITION (p0); 1880i 1881FLUSH TABLES; 1882SELECT * FROM t1 PARTITION (p0); 1883i 1884ALTER TABLE t1 TRUNCATE PARTITION p0; 1885SELECT * FROM t1 PARTITION (p0); 1886i 1887ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE t2; 1888SELECT * FROM t1 PARTITION (p0); 1889i 1890UNLOCK TABLES; 1891DROP TABLE t1; 1892# 1893# MDEV-18371 Server crashes in ha_innobase::cmp_ref upon UPDATE with PARTITION clause. 1894# 1895CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE=InnoDB PARTITION BY KEY(b) PARTITIONS 4; 1896INSERT INTO t1 VALUES (3,0),(8,2),(7,8),(3,4),(2,4),(0,7),(4,3),(3,6); 1897FLUSH TABLES; 1898UPDATE t1 PARTITION (p3,p1) SET a = 2 WHERE a = 3; 1899SELECT * FROM t1; 1900a b 19012 0 19027 8 19032 4 19042 4 19050 7 19064 3 19078 2 19082 6 1909DROP TABLE t1, t2; 1910