1drop table if exists t1, t2; 2# 3# Bug#48229: group by performance issue of partitioned table 4# 5CREATE TABLE t1 ( 6a INT, 7b INT, 8KEY a (a,b) 9) 10PARTITION BY HASH (a) PARTITIONS 1; 11INSERT INTO t1 VALUES (0, 580092), (3, 894076), (4, 805483), (4, 913540), (6, 611137), (8, 171602), (9, 599495), (9, 746305), (10, 272829), (10, 847519), (12, 258869), (12, 929028), (13, 288970), (15, 20971), (15, 105839), (16, 788272), (17, 76914), (18, 827274), (19, 802258), (20, 123677), (20, 587729), (22, 701449), (25, 31565), (25, 230782), (25, 442887), (25, 733139), (25, 851020); 12EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; 13id select_type table type possible_keys key key_len ref rows Extra 141 SIMPLE t1 range a a 5 NULL 4 Using where; Using index 15DROP TABLE t1; 16create table t1 (a DATETIME) 17partition by range (TO_DAYS(a)) 18subpartition by hash(to_seconds(a)) 19(partition p0 values less than (1)); 20show create table t1; 21Table Create Table 22t1 CREATE TABLE `t1` ( 23 `a` datetime DEFAULT NULL 24) ENGINE=MyISAM DEFAULT CHARSET=latin1 25/*!50500 PARTITION BY RANGE (TO_DAYS(a)) 26SUBPARTITION BY HASH (to_seconds(a)) 27(PARTITION p0 VALUES LESS THAN (1) ENGINE = MyISAM) */ 28drop table t1; 29create table t1 (a int) 30partition by range (a) 31( partition p0 values less than (NULL), 32partition p1 values less than (MAXVALUE)); 33ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN 34create table t1 (a datetime not null) 35partition by range (TO_SECONDS(a)) 36( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')), 37partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00'))); 38select partition_method, partition_expression, partition_description 39from information_schema.partitions where table_name = "t1"; 40partition_method partition_expression partition_description 41RANGE TO_SECONDS(a) 63340531200 42RANGE TO_SECONDS(a) 63342604800 43INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00'); 44INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00'); 45explain partitions select * from t1 where a < '2007-03-08 00:00:00'; 46id select_type table partitions type possible_keys key key_len ref rows Extra 471 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 48explain partitions select * from t1 where a < '2007-03-08 00:00:01'; 49id select_type table partitions type possible_keys key key_len ref rows Extra 501 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where 51explain partitions select * from t1 where a <= '2007-03-08 00:00:00'; 52id select_type table partitions type possible_keys key key_len ref rows Extra 531 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 4 Using where 54explain partitions select * from t1 where a <= '2007-03-07 23:59:59'; 55id select_type table partitions type possible_keys key key_len ref rows Extra 561 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 57explain partitions select * from t1 where a < '2007-03-07 23:59:59'; 58id select_type table partitions type possible_keys key key_len ref rows Extra 591 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 60show create table t1; 61Table Create Table 62t1 CREATE TABLE `t1` ( 63 `a` datetime NOT NULL 64) ENGINE=MyISAM DEFAULT CHARSET=latin1 65/*!50500 PARTITION BY RANGE (TO_SECONDS(a)) 66(PARTITION p0 VALUES LESS THAN (63340531200) ENGINE = MyISAM, 67 PARTITION p1 VALUES LESS THAN (63342604800) ENGINE = MyISAM) */ 68drop table t1; 69create table t1 (a date) 70partition by range(to_seconds(a)) 71(partition p0 values less than (to_seconds('2004-01-01')), 72partition p1 values less than (to_seconds('2005-01-01'))); 73insert into t1 values ('2003-12-30'),('2004-12-31'); 74select * from t1; 75a 762003-12-30 772004-12-31 78explain partitions select * from t1 where a <= '2003-12-31'; 79id select_type table partitions type possible_keys key key_len ref rows Extra 801 SIMPLE t1 p0 system NULL NULL NULL NULL 1 NULL 81select * from t1 where a <= '2003-12-31'; 82a 832003-12-30 84explain partitions select * from t1 where a <= '2005-01-01'; 85id select_type table partitions type possible_keys key key_len ref rows Extra 861 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where 87select * from t1 where a <= '2005-01-01'; 88a 892003-12-30 902004-12-31 91show create table t1; 92Table Create Table 93t1 CREATE TABLE `t1` ( 94 `a` date DEFAULT NULL 95) ENGINE=MyISAM DEFAULT CHARSET=latin1 96/*!50500 PARTITION BY RANGE (to_seconds(a)) 97(PARTITION p0 VALUES LESS THAN (63240134400) ENGINE = MyISAM, 98 PARTITION p1 VALUES LESS THAN (63271756800) ENGINE = MyISAM) */ 99drop table t1; 100create table t1 (a datetime) 101partition by range(to_seconds(a)) 102(partition p0 values less than (to_seconds('2004-01-01 12:00:00')), 103partition p1 values less than (to_seconds('2005-01-01 12:00:00'))); 104insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59'); 105select * from t1; 106a 1072004-01-01 11:59:29 1082005-01-01 11:59:59 109explain partitions select * from t1 where a <= '2004-01-01 11:59.59'; 110id select_type table partitions type possible_keys key key_len ref rows Extra 1111 SIMPLE t1 p0 system NULL NULL NULL NULL 1 NULL 112select * from t1 where a <= '2004-01-01 11:59:59'; 113a 1142004-01-01 11:59:29 115explain partitions select * from t1 where a <= '2005-01-01'; 116id select_type table partitions type possible_keys key key_len ref rows Extra 1171 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where 118select * from t1 where a <= '2005-01-01'; 119a 1202004-01-01 11:59:29 121show create table t1; 122Table Create Table 123t1 CREATE TABLE `t1` ( 124 `a` datetime DEFAULT NULL 125) ENGINE=MyISAM DEFAULT CHARSET=latin1 126/*!50500 PARTITION BY RANGE (to_seconds(a)) 127(PARTITION p0 VALUES LESS THAN (63240177600) ENGINE = MyISAM, 128 PARTITION p1 VALUES LESS THAN (63271800000) ENGINE = MyISAM) */ 129drop table t1; 130create table t1 (a int, b char(20)) 131partition by range columns(a,b) 132(partition p0 values less than (1)); 133ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3 134create table t1 (a int, b char(20)) 135partition by range(a) 136(partition p0 values less than (1,"b")); 137ERROR HY000: Cannot have more than one value for this type of RANGE partitioning 138create table t1 (a int, b char(20)) 139partition by range(a) 140(partition p0 values less than (1,"b")); 141ERROR HY000: Cannot have more than one value for this type of RANGE partitioning 142create table t1 (a int, b char(20)) 143partition by range columns(b) 144(partition p0 values less than ("b")); 145drop table t1; 146create table t1 (a int) 147partition by range (a) 148( partition p0 values less than (maxvalue)); 149alter table t1 add partition (partition p1 values less than (100000)); 150ERROR HY000: MAXVALUE can only be used in last partition definition 151show create table t1; 152Table Create Table 153t1 CREATE TABLE `t1` ( 154 `a` int(11) DEFAULT NULL 155) ENGINE=MyISAM DEFAULT CHARSET=latin1 156/*!50100 PARTITION BY RANGE (a) 157(PARTITION p0 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 158drop table t1; 159create table t1 (a integer) 160partition by range (a) 161( partition p0 values less than (4), 162partition p1 values less than (100)); 163create trigger tr1 before insert on t1 164for each row begin 165set @a = 1; 166end| 167alter table t1 drop partition p0; 168drop table t1; 169create table t1 (a integer) 170partition by range (a) 171( partition p0 values less than (4), 172partition p1 values less than (100)); 173LOCK TABLES t1 WRITE; 174alter table t1 drop partition p0; 175alter table t1 reorganize partition p1 into 176( partition p0 values less than (4), 177partition p1 values less than (100)); 178alter table t1 add partition ( partition p2 values less than (200)); 179UNLOCK TABLES; 180drop table t1; 181create table t1 (a int unsigned) 182partition by range (a) 183(partition pnull values less than (0), 184partition p0 values less than (1), 185partition p1 values less than(2)); 186insert into t1 values (null),(0),(1); 187select * from t1 where a is null; 188a 189NULL 190select * from t1 where a >= 0; 191a 1920 1931 194select * from t1 where a < 0; 195a 196select * from t1 where a <= 0; 197a 1980 199select * from t1 where a > 1; 200a 201explain partitions select * from t1 where a is null; 202id select_type table partitions type possible_keys key key_len ref rows Extra 2031 SIMPLE t1 pnull system NULL NULL NULL NULL 1 NULL 204explain partitions select * from t1 where a >= 0; 205id select_type table partitions type possible_keys key key_len ref rows Extra 2061 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 2 Using where 207explain partitions select * from t1 where a < 0; 208id select_type table partitions type possible_keys key key_len ref rows Extra 2091 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 210explain partitions select * from t1 where a <= 0; 211id select_type table partitions type possible_keys key key_len ref rows Extra 2121 SIMPLE t1 pnull,p0 ALL NULL NULL NULL NULL 2 Using where 213explain partitions select * from t1 where a > 1; 214id select_type table partitions type possible_keys key key_len ref rows Extra 2151 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 216drop table t1; 217create table t1 (a int unsigned, b int unsigned) 218partition by range (a) 219subpartition by hash (b) 220subpartitions 2 221(partition pnull values less than (0), 222partition p0 values less than (1), 223partition p1 values less than(2)); 224insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1); 225select * from t1 where a is null; 226a b 227NULL 0 228NULL 1 229select * from t1 where a >= 0; 230a b 2310 0 2320 1 2331 0 2341 1 235select * from t1 where a < 0; 236a b 237select * from t1 where a <= 0; 238a b 2390 0 2400 1 241select * from t1 where a > 1; 242a b 243explain partitions select * from t1 where a is null; 244id select_type table partitions type possible_keys key key_len ref rows Extra 2451 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where 246explain partitions select * from t1 where a >= 0; 247id select_type table partitions type possible_keys key key_len ref rows Extra 2481 SIMPLE t1 p0_p0sp0,p0_p0sp1,p1_p1sp0,p1_p1sp1 ALL NULL NULL NULL NULL 4 Using where 249explain partitions select * from t1 where a < 0; 250id select_type table partitions type possible_keys key key_len ref rows Extra 2511 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1 ALL NULL NULL NULL NULL 2 Using where 252explain partitions select * from t1 where a <= 0; 253id select_type table partitions type possible_keys key key_len ref rows Extra 2541 SIMPLE t1 pnull_pnullsp0,pnull_pnullsp1,p0_p0sp0,p0_p0sp1 ALL NULL NULL NULL NULL 4 Using where 255explain partitions select * from t1 where a > 1; 256id select_type table partitions type possible_keys key key_len ref rows Extra 2571 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 258drop table t1; 259CREATE TABLE t1 ( 260a int not null, 261b int not null, 262c int not null, 263primary key(a,b)) 264partition by range (a) 265partitions 3 266(partition x1 values less than (5) tablespace ts1, 267partition x2 values less than (10) tablespace ts2, 268partition x3 values less than maxvalue tablespace ts3); 269INSERT into t1 values (1, 1, 1); 270INSERT into t1 values (6, 1, 1); 271INSERT into t1 values (10, 1, 1); 272INSERT into t1 values (15, 1, 1); 273select * from t1; 274a b c 2751 1 1 2766 1 1 27710 1 1 27815 1 1 279show create table t1; 280Table Create Table 281t1 CREATE TABLE `t1` ( 282 `a` int(11) NOT NULL, 283 `b` int(11) NOT NULL, 284 `c` int(11) NOT NULL, 285 PRIMARY KEY (`a`,`b`) 286) ENGINE=MyISAM DEFAULT CHARSET=latin1 287/*!50100 PARTITION BY RANGE (a) 288(PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, 289 PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, 290 PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ 291ALTER TABLE t1 292partition by range (a) 293partitions 3 294(partition x1 values less than (5) tablespace ts1, 295partition x2 values less than (10) tablespace ts2, 296partition x3 values less than maxvalue tablespace ts3); 297select * from t1; 298a b c 2991 1 1 3006 1 1 30110 1 1 30215 1 1 303show create table t1; 304Table Create Table 305t1 CREATE TABLE `t1` ( 306 `a` int(11) NOT NULL, 307 `b` int(11) NOT NULL, 308 `c` int(11) NOT NULL, 309 PRIMARY KEY (`a`,`b`) 310) ENGINE=MyISAM DEFAULT CHARSET=latin1 311/*!50100 PARTITION BY RANGE (a) 312(PARTITION x1 VALUES LESS THAN (5) TABLESPACE = ts1 ENGINE = MyISAM, 313 PARTITION x2 VALUES LESS THAN (10) TABLESPACE = ts2 ENGINE = MyISAM, 314 PARTITION x3 VALUES LESS THAN MAXVALUE TABLESPACE = ts3 ENGINE = MyISAM) */ 315drop table if exists t1; 316CREATE TABLE t1 ( 317a int not null, 318b int not null, 319c int not null) 320partition by range (a) 321partitions 3 322(partition x1 values less than (5) tablespace ts1, 323partition x2 values less than (10) tablespace ts2, 324partition x3 values less than maxvalue tablespace ts3); 325INSERT into t1 values (1, 1, 1); 326INSERT into t1 values (6, 1, 1); 327INSERT into t1 values (10, 1, 1); 328INSERT into t1 values (15, 1, 1); 329select * from t1; 330a b c 3311 1 1 3326 1 1 33310 1 1 33415 1 1 335ALTER TABLE t1 336partition by range (a) 337partitions 3 338(partition x1 values less than (5) tablespace ts1, 339partition x2 values less than (10) tablespace ts2, 340partition x3 values less than maxvalue tablespace ts3); 341select * from t1; 342a b c 3431 1 1 3446 1 1 34510 1 1 34615 1 1 347drop table if exists t1; 348CREATE TABLE t1 ( 349a int not null, 350b int not null, 351c int not null, 352primary key(a,b)) 353partition by range (a) 354partitions 3 355(partition x1 values less than (5) tablespace ts1, 356partition x2 values less than (10) tablespace ts2, 357partition x3 values less than (15) tablespace ts3); 358INSERT into t1 values (1, 1, 1); 359INSERT into t1 values (6, 1, 1); 360INSERT into t1 values (10, 1, 1); 361INSERT into t1 values (15, 1, 1); 362ERROR HY000: Table has no partition for value 15 363select * from t1; 364a b c 3651 1 1 3666 1 1 36710 1 1 368ALTER TABLE t1 369partition by range (a) 370partitions 3 371(partition x1 values less than (5) tablespace ts1, 372partition x2 values less than (10) tablespace ts2, 373partition x3 values less than (15) tablespace ts3); 374select * from t1; 375a b c 3761 1 1 3776 1 1 37810 1 1 379drop table t1; 380CREATE TABLE t1 ( 381a int not null, 382b int not null, 383c int not null, 384primary key(a,b)) 385partition by range (a) 386(partition x1 values less than (1)); 387drop table t1; 388CREATE TABLE t1 ( 389a int not null, 390b int not null, 391c int not null, 392primary key (a,b)) 393partition by range (a) 394subpartition by hash (a+b) 395( partition x1 values less than (1) 396( subpartition x11, 397subpartition x12), 398partition x2 values less than (5) 399( subpartition x21, 400subpartition x22) 401); 402SELECT * from t1; 403a b c 404show create table t1; 405Table Create Table 406t1 CREATE TABLE `t1` ( 407 `a` int(11) NOT NULL, 408 `b` int(11) NOT NULL, 409 `c` int(11) NOT NULL, 410 PRIMARY KEY (`a`,`b`) 411) ENGINE=MyISAM DEFAULT CHARSET=latin1 412/*!50100 PARTITION BY RANGE (a) 413SUBPARTITION BY HASH (a+b) 414(PARTITION x1 VALUES LESS THAN (1) 415 (SUBPARTITION x11 ENGINE = MyISAM, 416 SUBPARTITION x12 ENGINE = MyISAM), 417 PARTITION x2 VALUES LESS THAN (5) 418 (SUBPARTITION x21 ENGINE = MyISAM, 419 SUBPARTITION x22 ENGINE = MyISAM)) */ 420ALTER TABLE t1 ADD COLUMN d int; 421show create table t1; 422Table Create Table 423t1 CREATE TABLE `t1` ( 424 `a` int(11) NOT NULL, 425 `b` int(11) NOT NULL, 426 `c` int(11) NOT NULL, 427 `d` int(11) DEFAULT NULL, 428 PRIMARY KEY (`a`,`b`) 429) ENGINE=MyISAM DEFAULT CHARSET=latin1 430/*!50100 PARTITION BY RANGE (a) 431SUBPARTITION BY HASH (a+b) 432(PARTITION x1 VALUES LESS THAN (1) 433 (SUBPARTITION x11 ENGINE = MyISAM, 434 SUBPARTITION x12 ENGINE = MyISAM), 435 PARTITION x2 VALUES LESS THAN (5) 436 (SUBPARTITION x21 ENGINE = MyISAM, 437 SUBPARTITION x22 ENGINE = MyISAM)) */ 438drop table t1; 439CREATE TABLE t1 ( 440a int not null, 441b int not null, 442c int not null, 443primary key (a,b)) 444partition by range (a) 445subpartition by hash (a+b) 446( partition x1 values less than (1) 447( subpartition x11 tablespace t1 engine myisam nodegroup 0, 448subpartition x12 tablespace t2 engine myisam nodegroup 1), 449partition x2 values less than (5) 450( subpartition x21 tablespace t1 engine myisam nodegroup 0, 451subpartition x22 tablespace t2 engine myisam nodegroup 1) 452); 453SELECT * from t1; 454a b c 455drop table t1; 456CREATE TABLE t1 ( 457a int not null, 458b int not null, 459c int not null, 460primary key (a,b)) 461partition by range (a) 462subpartition by hash (a+b) 463( partition x1 values less than (1) 464( subpartition x11 tablespace t1 nodegroup 0, 465subpartition x12 tablespace t2 nodegroup 1), 466partition x2 values less than (5) 467( subpartition x21 tablespace t1 nodegroup 0, 468subpartition x22 tablespace t2 nodegroup 1) 469); 470SELECT * from t1; 471a b c 472drop table t1; 473CREATE TABLE t1 ( 474a int not null, 475b int not null, 476c int not null, 477primary key (a,b)) 478partition by range (a) 479subpartition by hash (a+b) 480( partition x1 values less than (1) 481( subpartition x11 engine myisam nodegroup 0, 482subpartition x12 engine myisam nodegroup 1), 483partition x2 values less than (5) 484( subpartition x21 engine myisam nodegroup 0, 485subpartition x22 engine myisam nodegroup 1) 486); 487INSERT into t1 VALUES (1,1,1); 488INSERT into t1 VALUES (4,1,1); 489INSERT into t1 VALUES (5,1,1); 490ERROR HY000: Table has no partition for value 5 491SELECT * from t1; 492a b c 4931 1 1 4944 1 1 495ALTER TABLE t1 496partition by range (a) 497subpartition by hash (a+b) 498( partition x1 values less than (1) 499( subpartition x11 engine myisam nodegroup 0, 500subpartition x12 engine myisam nodegroup 1), 501partition x2 values less than (5) 502( subpartition x21 engine myisam nodegroup 0, 503subpartition x22 engine myisam nodegroup 1) 504); 505SELECT * from t1; 506a b c 5071 1 1 5084 1 1 509drop table t1; 510CREATE TABLE t1 ( 511a int not null, 512b int not null, 513c int not null, 514primary key (a,b)) 515partition by range (a) 516subpartition by hash (a+b) 517( partition x1 values less than (1) 518( subpartition x11 tablespace t1 engine myisam, 519subpartition x12 tablespace t2 engine myisam), 520partition x2 values less than (5) 521( subpartition x21 tablespace t1 engine myisam, 522subpartition x22 tablespace t2 engine myisam) 523); 524INSERT into t1 VALUES (1,1,1); 525INSERT into t1 VALUES (4,1,1); 526INSERT into t1 VALUES (5,1,1); 527ERROR HY000: Table has no partition for value 5 528SELECT * from t1; 529a b c 5301 1 1 5314 1 1 532ALTER TABLE t1 533partition by range (a) 534subpartition by hash (a+b) 535( partition x1 values less than (1) 536( subpartition x11 tablespace t1 engine myisam, 537subpartition x12 tablespace t2 engine myisam), 538partition x2 values less than (5) 539( subpartition x21 tablespace t1 engine myisam, 540subpartition x22 tablespace t2 engine myisam) 541); 542SELECT * from t1; 543a b c 5441 1 1 5454 1 1 546drop table t1; 547CREATE TABLE t1 ( 548a int not null, 549b int not null, 550c int not null, 551primary key (a,b)) 552partition by range (a) 553subpartition by hash (a+b) 554( partition x1 values less than (1) 555( subpartition x11 tablespace t1, 556subpartition x12 tablespace t2), 557partition x2 values less than (5) 558( subpartition x21 tablespace t1, 559subpartition x22 tablespace t2) 560); 561INSERT into t1 VALUES (1,1,1); 562INSERT into t1 VALUES (4,1,1); 563INSERT into t1 VALUES (5,1,1); 564ERROR HY000: Table has no partition for value 5 565SELECT * from t1; 566a b c 5671 1 1 5684 1 1 569ALTER TABLE t1 570partition by range (a) 571subpartition by hash (a+b) 572( partition x1 values less than (1) 573( subpartition x11 tablespace t1 engine myisam, 574subpartition x12 tablespace t2 engine myisam), 575partition x2 values less than (5) 576( subpartition x21 tablespace t1 engine myisam, 577subpartition x22 tablespace t2 engine myisam) 578); 579SELECT * from t1; 580a b c 5811 1 1 5824 1 1 583drop table t1; 584CREATE TABLE t1 ( 585a int not null, 586b int not null, 587c int not null, 588primary key (a,b)) 589partition by range (a) 590subpartition by hash (a+b) 591( partition x1 values less than (1) 592( subpartition x11 engine myisam, 593subpartition x12 engine myisam), 594partition x2 values less than (5) 595( subpartition x21 engine myisam, 596subpartition x22 engine myisam) 597); 598INSERT into t1 VALUES (1,1,1); 599INSERT into t1 VALUES (4,1,1); 600INSERT into t1 VALUES (5,1,1); 601ERROR HY000: Table has no partition for value 5 602SELECT * from t1; 603a b c 6041 1 1 6054 1 1 606ALTER TABLE t1 607partition by range (a) 608subpartition by hash (a+b) 609( partition x1 values less than (1) 610( subpartition x11 engine myisam, 611subpartition x12 engine myisam), 612partition x2 values less than (5) 613( subpartition x21 engine myisam, 614subpartition x22 engine myisam) 615); 616SELECT * from t1; 617a b c 6181 1 1 6194 1 1 620drop table t1; 621CREATE TABLE t1 (c1 int default NULL, c2 varchar(30) default NULL, 622c3 date default NULL) engine=myisam 623PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), 624PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , 625PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , 626PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , 627PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , 628PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), 629PARTITION p11 VALUES LESS THAN MAXVALUE ); 630INSERT INTO t1 VALUES (1, 'testing partitions', '1995-07-17'), 631(3, 'testing partitions','1995-07-31'), 632(5, 'testing partitions','1995-08-13'), 633(7, 'testing partitions','1995-08-26'), 634(9, 'testing partitions','1995-09-09'), 635(0, 'testing partitions','2000-07-10'), 636(2, 'testing partitions','2000-07-23'), 637(4, 'testing partitions','2000-08-05'), 638(6, 'testing partitions','2000-08-19'), 639(8, 'testing partitions','2000-09-01'); 640SELECT COUNT(*) FROM t1 WHERE c3 BETWEEN '1996-12-31' AND '2000-12-31'; 641COUNT(*) 6425 643SELECT COUNT(*) FROM t1 WHERE c3 < '2000-12-31'; 644COUNT(*) 64510 646DROP TABLE t1; 647create table t1 (a bigint unsigned) 648partition by range (a) 649(partition p0 values less than (10), 650partition p1 values less than (0)); 651ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 652create table t1 (a bigint unsigned) 653partition by range (a) 654(partition p0 values less than (0), 655partition p1 values less than (10)); 656show create table t1; 657Table Create Table 658t1 CREATE TABLE `t1` ( 659 `a` bigint(20) unsigned DEFAULT NULL 660) ENGINE=MyISAM DEFAULT CHARSET=latin1 661/*!50100 PARTITION BY RANGE (a) 662(PARTITION p0 VALUES LESS THAN (0) ENGINE = MyISAM, 663 PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ 664drop table t1; 665create table t1 (a bigint unsigned) 666partition by range (a) 667(partition p0 values less than (2), 668partition p1 values less than (10)); 669show create table t1; 670Table Create Table 671t1 CREATE TABLE `t1` ( 672 `a` bigint(20) unsigned DEFAULT NULL 673) ENGINE=MyISAM DEFAULT CHARSET=latin1 674/*!50100 PARTITION BY RANGE (a) 675(PARTITION p0 VALUES LESS THAN (2) ENGINE = MyISAM, 676 PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM) */ 677insert into t1 values (0xFFFFFFFFFFFFFFFF); 678ERROR HY000: Table has no partition for value 18446744073709551615 679drop table t1; 680create table t1 (a int) 681partition by range (MOD(a,3)) 682subpartition by hash(a) 683subpartitions 2 684(partition p0 values less than (1), 685partition p1 values less than (2), 686partition p2 values less than (3), 687partition p3 values less than (4)); 688ALTER TABLE t1 DROP PARTITION p3; 689ALTER TABLE t1 DROP PARTITION p1; 690ALTER TABLE t1 DROP PARTITION p2; 691drop table t1; 692create table t1 (a int) 693partition by range (MOD(a,3)) 694subpartition by hash(a) 695subpartitions 2 696(partition p0 values less than (1), 697partition p1 values less than (2), 698partition p2 values less than (3), 699partition p3 values less than (4)); 700ALTER TABLE t1 DROP PARTITION p0; 701ALTER TABLE t1 DROP PARTITION p1; 702ALTER TABLE t1 DROP PARTITION p2; 703drop table t1; 704create table t1 (a int DEFAULT NULL, 705b varchar(30) DEFAULT NULL, 706c date DEFAULT NULL) 707ENGINE=MYISAM DEFAULT CHARSET=latin1; 708insert into t1 values (1, 'abc', '1995-01-01'); 709insert into t1 values (1, 'abc', '1995-01-02'); 710insert into t1 values (1, 'abc', '1995-01-03'); 711insert into t1 values (1, 'abc', '1995-01-04'); 712insert into t1 values (1, 'abc', '1995-01-05'); 713insert into t1 values (1, 'abc', '1995-01-06'); 714insert into t1 values (1, 'abc', '1995-01-07'); 715insert into t1 values (1, 'abc', '1995-01-08'); 716insert into t1 values (1, 'abc', '1995-01-09'); 717insert into t1 values (1, 'abc', '1995-01-10'); 718insert into t1 values (1, 'abc', '1995-01-11'); 719insert into t1 values (1, 'abc', '1995-01-12'); 720insert into t1 values (1, 'abc', '1995-01-13'); 721insert into t1 values (1, 'abc', '1995-01-14'); 722insert into t1 values (1, 'abc', '1995-01-15'); 723insert into t1 values (1, 'abc', '1997-01-01'); 724insert into t1 values (1, 'abc', '1997-01-02'); 725insert into t1 values (1, 'abc', '1997-01-03'); 726insert into t1 values (1, 'abc', '1997-01-04'); 727insert into t1 values (1, 'abc', '1997-01-05'); 728insert into t1 values (1, 'abc', '1997-01-06'); 729insert into t1 values (1, 'abc', '1997-01-07'); 730insert into t1 values (1, 'abc', '1997-01-08'); 731insert into t1 values (1, 'abc', '1997-01-09'); 732insert into t1 values (1, 'abc', '1997-01-10'); 733insert into t1 values (1, 'abc', '1997-01-11'); 734insert into t1 values (1, 'abc', '1997-01-12'); 735insert into t1 values (1, 'abc', '1997-01-13'); 736insert into t1 values (1, 'abc', '1997-01-14'); 737insert into t1 values (1, 'abc', '1997-01-15'); 738insert into t1 values (1, 'abc', '1998-01-01'); 739insert into t1 values (1, 'abc', '1998-01-02'); 740insert into t1 values (1, 'abc', '1998-01-03'); 741insert into t1 values (1, 'abc', '1998-01-04'); 742insert into t1 values (1, 'abc', '1998-01-05'); 743insert into t1 values (1, 'abc', '1998-01-06'); 744insert into t1 values (1, 'abc', '1998-01-07'); 745insert into t1 values (1, 'abc', '1998-01-08'); 746insert into t1 values (1, 'abc', '1998-01-09'); 747insert into t1 values (1, 'abc', '1998-01-10'); 748insert into t1 values (1, 'abc', '1998-01-11'); 749insert into t1 values (1, 'abc', '1998-01-12'); 750insert into t1 values (1, 'abc', '1998-01-13'); 751insert into t1 values (1, 'abc', '1998-01-14'); 752insert into t1 values (1, 'abc', '1998-01-15'); 753insert into t1 values (1, 'abc', '1999-01-01'); 754insert into t1 values (1, 'abc', '1999-01-02'); 755insert into t1 values (1, 'abc', '1999-01-03'); 756insert into t1 values (1, 'abc', '1999-01-04'); 757insert into t1 values (1, 'abc', '1999-01-05'); 758insert into t1 values (1, 'abc', '1999-01-06'); 759insert into t1 values (1, 'abc', '1999-01-07'); 760insert into t1 values (1, 'abc', '1999-01-08'); 761insert into t1 values (1, 'abc', '1999-01-09'); 762insert into t1 values (1, 'abc', '1999-01-10'); 763insert into t1 values (1, 'abc', '1999-01-11'); 764insert into t1 values (1, 'abc', '1999-01-12'); 765insert into t1 values (1, 'abc', '1999-01-13'); 766insert into t1 values (1, 'abc', '1999-01-14'); 767insert into t1 values (1, 'abc', '1999-01-15'); 768insert into t1 values (1, 'abc', '2000-01-01'); 769insert into t1 values (1, 'abc', '2000-01-02'); 770insert into t1 values (1, 'abc', '2000-01-03'); 771insert into t1 values (1, 'abc', '2000-01-04'); 772insert into t1 values (1, 'abc', '2000-01-05'); 773insert into t1 values (1, 'abc', '2000-01-06'); 774insert into t1 values (1, 'abc', '2000-01-07'); 775insert into t1 values (1, 'abc', '2000-01-08'); 776insert into t1 values (1, 'abc', '2000-01-09'); 777insert into t1 values (1, 'abc', '2000-01-15'); 778insert into t1 values (1, 'abc', '2000-01-11'); 779insert into t1 values (1, 'abc', '2000-01-12'); 780insert into t1 values (1, 'abc', '2000-01-13'); 781insert into t1 values (1, 'abc', '2000-01-14'); 782insert into t1 values (1, 'abc', '2000-01-15'); 783insert into t1 values (1, 'abc', '2001-01-01'); 784insert into t1 values (1, 'abc', '2001-01-02'); 785insert into t1 values (1, 'abc', '2001-01-03'); 786insert into t1 values (1, 'abc', '2001-01-04'); 787insert into t1 values (1, 'abc', '2001-01-05'); 788insert into t1 values (1, 'abc', '2001-01-06'); 789insert into t1 values (1, 'abc', '2001-01-07'); 790insert into t1 values (1, 'abc', '2001-01-08'); 791insert into t1 values (1, 'abc', '2001-01-09'); 792insert into t1 values (1, 'abc', '2001-01-15'); 793insert into t1 values (1, 'abc', '2001-01-11'); 794insert into t1 values (1, 'abc', '2001-01-12'); 795insert into t1 values (1, 'abc', '2001-01-13'); 796insert into t1 values (1, 'abc', '2001-01-14'); 797insert into t1 values (1, 'abc', '2001-01-15'); 798alter table t1 799partition by range (year(c)) 800(partition p5 values less than (2000), partition p10 values less than (2010)); 801alter table t1 802reorganize partition p5 into 803(partition p1 values less than (1996), 804partition p2 values less than (1997), 805partition p3 values less than (1998), 806partition p4 values less than (1999), 807partition p5 values less than (2000)); 808drop table t1; 809CREATE TABLE t1 (a date) 810PARTITION BY RANGE (TO_DAYS(a)) 811(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')), 812PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')), 813PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')), 814PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')), 815PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')), 816PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')), 817PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')), 818PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')), 819PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')), 820PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')), 821PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')), 822PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')), 823PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')), 824PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')), 825PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')), 826PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')), 827PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')), 828PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')), 829PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')), 830PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')), 831PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')), 832PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')), 833PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')), 834PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')), 835PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')), 836PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')), 837PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')), 838PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')), 839PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')), 840PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')), 841PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')), 842PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'))); 843INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30'); 844INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30'); 845INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30'); 846INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30'); 847INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28'); 848INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30'); 849INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30'); 850INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30'); 851INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30'); 852INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30'); 853INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30'); 854INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30'); 855INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30'); 856INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30'); 857INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30'); 858INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30'); 859INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28'); 860INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30'); 861INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30'); 862INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30'); 863INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30'); 864INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30'); 865INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30'); 866INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30'); 867INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30'); 868INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30'); 869INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30'); 870INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30'); 871INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28'); 872INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30'); 873INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30'); 874INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30'); 875INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30'); 876INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30'); 877SELECT * FROM t1 878WHERE a >= '2004-07-01' AND a <= '2004-09-30'; 879a 8802004-07-13 8812004-07-20 8822004-07-30 8832004-08-13 8842004-08-20 8852004-08-30 8862004-09-13 8872004-09-20 8882004-09-30 889EXPLAIN PARTITIONS SELECT * FROM t1 890WHERE a >= '2004-07-01' AND a <= '2004-09-30'; 891id select_type table partitions type possible_keys key key_len ref rows Extra 8921 SIMPLE t1 p3xx,p407,p408,p409 ALL NULL NULL NULL NULL 18 Using where 893SELECT * from t1 894WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR 895(a >= '2005-07-01' AND a <= '2005-09-30'); 896a 8972004-07-13 8982004-07-20 8992004-07-30 9002004-08-13 9012004-08-20 9022004-08-30 9032004-09-13 9042004-09-20 9052004-09-30 9062005-07-13 9072005-07-20 9082005-07-30 9092005-08-13 9102005-08-20 9112005-08-30 9122005-09-13 9132005-09-20 9142005-09-30 915EXPLAIN PARTITIONS SELECT * from t1 916WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR 917(a >= '2005-07-01' AND a <= '2005-09-30'); 918id select_type table partitions type possible_keys key key_len ref rows Extra 9191 SIMPLE t1 p3xx,p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 27 Using where 920DROP TABLE t1; 921create table t1 (a int); 922insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 923CREATE TABLE t2 ( 924defid int(10) unsigned NOT NULL, 925day int(10) unsigned NOT NULL, 926count int(10) unsigned NOT NULL, 927filler char(200), 928KEY (defid,day) 929) 930PARTITION BY RANGE (day) ( 931PARTITION p7 VALUES LESS THAN (20070401) , 932PARTITION p8 VALUES LESS THAN (20070501)); 933insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; 934insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; 935insert into t2 values(52, 20070321, 123, 'filler') ; 936insert into t2 values(52, 20070322, 456, 'filler') ; 937select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; 938sum(count) 939579 940drop table t1, t2; 941# 942# Bug#50939: Loose Index Scan unduly relies on engine to remember range 943# endpoints 944# 945CREATE TABLE t1 ( 946a INT, 947b INT, 948KEY ( a, b ) 949) PARTITION BY HASH (a) PARTITIONS 1; 950CREATE TABLE t2 ( 951a INT, 952b INT, 953KEY ( a, b ) 954); 955INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 956INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; 957INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; 958INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; 959INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; 960INSERT INTO t2 SELECT * FROM t1; 961# plans should be identical 962EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; 963id select_type table type possible_keys key key_len ref rows Extra 9641 SIMPLE t1 range a a 5 NULL 1 Using where; Using index for group-by 965EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; 966id select_type table type possible_keys key key_len ref rows Extra 9671 SIMPLE t2 range a a 5 NULL 2 Using where; Using index for group-by 968FLUSH status; 969SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; 970a MAX(b) 97110 10 972# Should be no more than 4 reads. 973SHOW status LIKE 'handler_read_key'; 974Variable_name Value 975Handler_read_key 4 976FLUSH status; 977SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; 978a MAX(b) 97910 10 980# Should be no more than 4 reads. 981SHOW status LIKE 'handler_read_key'; 982Variable_name Value 983Handler_read_key 4 984DROP TABLE t1, t2; 985