1#--disable_abort_on_error 2# 3# Simple test for the partition storage engine 4# Focuses on range partitioning tests 5# 6-- source include/have_partition.inc 7# This test needs MyISAM as it has testcases for defined engine MyISAM 8--source include/have_myisam.inc 9 10--disable_warnings 11drop table if exists t1, t2; 12--enable_warnings 13 14--echo # 15--echo # Bug#48229: group by performance issue of partitioned table 16--echo # 17CREATE TABLE t1 ( 18 a INT, 19 b INT, 20 KEY a (a,b) 21) 22PARTITION BY HASH (a) PARTITIONS 1; 23 24# insert some rows (i.e. so that rows/blocks > 1) 25INSERT 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); 26 27# Before the fix the 'Extra' column showed 'Using index for group-by' 28analyze table t1; 29EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100, 3) GROUP BY a; 30 31DROP TABLE t1; 32 33# 34#BUG#49591, Add proper version number to SHOW CREATE TABLE 35# 36create table t1 (a DATETIME) 37partition by range (TO_DAYS(a)) 38subpartition by hash(to_seconds(a)) 39(partition p0 values less than (1)); 40show create table t1; 41drop table t1; 42 43--error ER_NULL_IN_VALUES_LESS_THAN 44create table t1 (a int) 45partition by range (a) 46( partition p0 values less than (NULL), 47 partition p1 values less than (MAXVALUE)); 48# 49# Merge fix of bug#27927 for TO_SECONDS function 50# 51create table t1 (a datetime not null) 52partition by range (TO_SECONDS(a)) 53( partition p0 VALUES LESS THAN (TO_SECONDS('2007-03-08 00:00:00')), 54 partition p1 VALUES LESS THAN (TO_SECONDS('2007-04-01 00:00:00'))); 55select partition_method, partition_expression, partition_description 56 from information_schema.partitions where table_name = "t1"; 57INSERT INTO t1 VALUES ('2007-03-01 12:00:00'), ('2007-03-07 12:00:00'); 58INSERT INTO t1 VALUES ('2007-03-08 12:00:00'), ('2007-03-15 12:00:00'); 59analyze table t1; 60explain partitions select * from t1 where a < '2007-03-08 00:00:00'; 61explain partitions select * from t1 where a < '2007-03-08 00:00:01'; 62explain partitions select * from t1 where a <= '2007-03-08 00:00:00'; 63explain partitions select * from t1 where a <= '2007-03-07 23:59:59'; 64explain partitions select * from t1 where a < '2007-03-07 23:59:59'; 65show create table t1; 66drop table t1; 67# 68# New test cases for new function to_seconds 69# 70create table t1 (a date) 71partition by range(to_seconds(a)) 72(partition p0 values less than (to_seconds('2004-01-01')), 73 partition p1 values less than (to_seconds('2005-01-01'))); 74insert into t1 values ('2003-12-30'),('2004-12-31'); 75select * from t1; 76analyze table t1; 77explain partitions select * from t1 where a <= '2003-12-31'; 78select * from t1 where a <= '2003-12-31'; 79explain partitions select * from t1 where a <= '2005-01-01'; 80select * from t1 where a <= '2005-01-01'; 81show create table t1; 82drop table t1; 83 84create table t1 (a datetime) 85partition by range(to_seconds(a)) 86(partition p0 values less than (to_seconds('2004-01-01 12:00:00')), 87 partition p1 values less than (to_seconds('2005-01-01 12:00:00'))); 88insert into t1 values ('2004-01-01 11:59:29'),('2005-01-01 11:59:59'); 89select * from t1; 90analyze table t1; 91explain partitions select * from t1 where a <= '2004-01-01 11:59.59'; 92select * from t1 where a <= '2004-01-01 11:59:59'; 93explain partitions select * from t1 where a <= '2005-01-01'; 94select * from t1 where a <= '2005-01-01'; 95show create table t1; 96drop table t1; 97 98# 99# Adding new test cases for column list variant for partitioning 100# 101--error 1064 102create table t1 (a int, b char(20)) 103partition by range columns(a,b) 104(partition p0 values less than (1)); 105 106--error ER_TOO_MANY_VALUES_ERROR 107create table t1 (a int, b char(20)) 108partition by range(a) 109(partition p0 values less than (1,"b")); 110 111--error ER_TOO_MANY_VALUES_ERROR 112create table t1 (a int, b char(20)) 113partition by range(a) 114(partition p0 values less than (1,"b")); 115 116create table t1 (a int, b char(20)) 117partition by range columns(b) 118(partition p0 values less than ("b")); 119drop table t1; 120 121# 122# BUG 33429: Succeeds in adding partition when maxvalue on last partition 123# 124create table t1 (a int) 125partition by range (a) 126( partition p0 values less than (maxvalue)); 127--error ER_PARTITION_MAXVALUE_ERROR 128alter table t1 add partition (partition p1 values less than (100000)); 129show create table t1; 130drop table t1; 131 132# BUG 32943: 133# Locking problems in relation to partitioning and triggers 134# Also fixes and test cases of generic lock issues with 135# partition change code. 136# 137create table t1 (a integer) 138partition by range (a) 139( partition p0 values less than (4), 140 partition p1 values less than (100)); 141 142delimiter |; 143create trigger tr1 before insert on t1 144for each row begin 145 set @a = 1; 146end| 147 148delimiter ;| 149alter table t1 drop partition p0; 150 151drop table t1; 152 153create table t1 (a integer) 154partition by range (a) 155( partition p0 values less than (4), 156 partition p1 values less than (100)); 157LOCK TABLES t1 WRITE; 158alter table t1 drop partition p0; 159alter table t1 reorganize partition p1 into 160( partition p0 values less than (4), 161 partition p1 values less than (100)); 162alter table t1 add partition ( partition p2 values less than (200)); 163UNLOCK TABLES; 164drop table t1; 165 166# 167# BUG 18198: Various tests for partition functions 168# 169#create table t1 (a varchar(10) charset latin1 collate latin1_bin, b int) 170#partition by range (ascii(a) * b) 171#(partition p0 values less than (2), partition p1 values less than (4000)); 172#insert into t1 values ('a ', 2),('a',3); 173#drop table t1; 174 175#create table t1 (a varchar(10) charset latin1 collate latin1_bin, b int) 176#partition by range (b* ascii(a) * b) 177#(partition p0 values less than (2), partition p1 values less than (4000)); 178#insert into t1 values ('a ', 2),('a',3); 179#drop table t1; 180 181#create table t1 (a varchar(10) charset latin1 collate latin1_bin, 182# b varchar(10) charset latin1 collate latin1_bin) 183#partition by range (ascii(b) * ascii(a)) 184#(partition p0 values less than (2), partition p1 values less than (40000)); 185#insert into t1 values ('a ', 'b '),('a','b'); 186#drop table t1; 187 188#create table t1 (a varchar(10) charset latin1 collate latin1_bin, 189# b varchar(10) charset latin1 collate latin1_bin) 190#partition by range (ascii(a) * ascii(b)) 191#(partition p0 values less than (2), partition p1 values less than (40000)); 192#insert into t1 values ('a ', 'b '),('a','b'); 193#drop table t1; 194 195#create table t1 (a varchar(10) charset latin1 collate latin1_bin, 196# b varchar(10) charset latin1 collate latin1_bin, c int) 197#partition by range (ascii(a) * c) 198#(partition p0 values less than (2), partition p1 values less than (4000)); 199#insert into t1 values ('a ', 'b ', 2),('a','b', 3); 200#drop table t1; 201 202#create table t1 (a varchar(10) charset latin1 collate latin1_bin, 203# b varchar(10) charset latin1 collate latin1_bin, c int) 204#partition by range (c * ascii(a)) 205#(partition p0 values less than (2), partition p1 values less than (4000)); 206#insert into t1 values ('a ', 'b ', 2),('a','b', 3); 207#drop table t1; 208 209# 210# More checks for partition pruning 211# 212create table t1 (a int unsigned) 213partition by range (a) 214(partition pnull values less than (0), 215 partition p0 values less than (1), 216 partition p1 values less than(2)); 217insert into t1 values (null),(0),(1); 218 219select * from t1 where a is null; 220select * from t1 where a >= 0; 221select * from t1 where a < 0; 222select * from t1 where a <= 0; 223select * from t1 where a > 1; 224analyze table t1; 225explain partitions select * from t1 where a is null; 226explain partitions select * from t1 where a >= 0; 227explain partitions select * from t1 where a < 0; 228explain partitions select * from t1 where a <= 0; 229explain partitions select * from t1 where a > 1; 230drop table t1; 231 232create table t1 (a int unsigned, b int unsigned) 233partition by range (a) 234subpartition by hash (b) 235subpartitions 2 236(partition pnull values less than (0), 237 partition p0 values less than (1), 238 partition p1 values less than(2)); 239insert into t1 values (null,0),(null,1),(0,0),(0,1),(1,0),(1,1); 240 241select * from t1 where a is null; 242select * from t1 where a >= 0; 243select * from t1 where a < 0; 244select * from t1 where a <= 0; 245select * from t1 where a > 1; 246analyze table t1; 247explain partitions select * from t1 where a is null; 248explain partitions select * from t1 where a >= 0; 249explain partitions select * from t1 where a < 0; 250explain partitions select * from t1 where a <= 0; 251explain partitions select * from t1 where a > 1; 252 253drop table t1; 254 255# 256# Partition by range, basic 257# 258CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd'; 259CREATE TABLESPACE ts2 ADD DATAFILE 'ts2.ibd'; 260CREATE TABLESPACE ts3 ADD DATAFILE 'ts3.ibd'; 261CREATE TABLE t1 ( 262a int not null, 263b int not null, 264c int not null, 265primary key(a,b)) 266partition by range (a) 267partitions 3 268(partition x1 values less than (5) tablespace ts1, 269 partition x2 values less than (10) tablespace ts2, 270 partition x3 values less than maxvalue tablespace ts3); 271 272# Simple insert and verify test 273INSERT into t1 values (1, 1, 1); 274INSERT into t1 values (6, 1, 1); 275INSERT into t1 values (10, 1, 1); 276INSERT into t1 values (15, 1, 1); 277 278select * from t1; 279show create table t1; 280 281ALTER TABLE t1 282partition by range (a) 283partitions 3 284(partition x1 values less than (5) tablespace ts1, 285 partition x2 values less than (10) tablespace ts2, 286 partition x3 values less than maxvalue tablespace ts3); 287 288select * from t1; 289show create table t1; 290 291drop table if exists t1; 292 293# 294# Partition by range, basic 295# No primary key 296# 297CREATE TABLE t1 ( 298a int not null, 299b int not null, 300c int not null) 301partition by range (a) 302partitions 3 303(partition x1 values less than (5) tablespace ts1, 304 partition x2 values less than (10) tablespace ts2, 305 partition x3 values less than maxvalue tablespace ts3); 306 307# Simple insert and verify test 308INSERT into t1 values (1, 1, 1); 309INSERT into t1 values (6, 1, 1); 310INSERT into t1 values (10, 1, 1); 311INSERT into t1 values (15, 1, 1); 312 313select * from t1; 314SHOW CREATE TABLE t1; 315 316ALTER TABLE t1 317partition by range (a) 318partitions 3 319(partition x1 values less than (5) tablespace ts1, 320 partition x2 values less than (10) tablespace ts2, 321 partition x3 values less than maxvalue tablespace ts3); 322 323select * from t1; 324 325SHOW CREATE TABLE t1; 326drop table if exists t1; 327 328# 329# Partition by range, basic 330# No max value used 331# 332CREATE TABLE t1 ( 333a int not null, 334b int not null, 335c int not null, 336primary key(a,b)) 337partition by range (a) 338partitions 3 339(partition x1 values less than (5) tablespace ts1, 340 partition x2 values less than (10) tablespace ts2, 341 partition x3 values less than (15) tablespace ts3); 342 343 344# Simple insert and verify test 345INSERT into t1 values (1, 1, 1); 346INSERT into t1 values (6, 1, 1); 347INSERT into t1 values (10, 1, 1); 348--error ER_NO_PARTITION_FOR_GIVEN_VALUE 349INSERT into t1 values (15, 1, 1); 350 351select * from t1; 352SHOW CREATE TABLE t1; 353 354ALTER TABLE t1 355partition by range (a) 356partitions 3 357(partition x1 values less than (5) tablespace ts1, 358 partition x2 values less than (10) tablespace ts2, 359 partition x3 values less than (15) tablespace ts3); 360 361select * from t1; 362 363SHOW CREATE TABLE t1; 364drop table t1; 365 366# 367# Partition by range, only one partition 368# 369CREATE TABLE t1 ( 370a int not null, 371b int not null, 372c int not null, 373primary key(a,b)) 374partition by range (a) 375(partition x1 values less than (1)); 376 377drop table t1; 378 379# 380# Subpartition by hash, two partitions and two subpartitions 381# 382CREATE TABLE t1 ( 383a int not null, 384b int not null, 385c int not null, 386primary key (a,b)) 387partition by range (a) 388subpartition by hash (a+b) 389( partition x1 values less than (1) 390 ( subpartition x11, 391 subpartition x12), 392 partition x2 values less than (5) 393 ( subpartition x21, 394 subpartition x22) 395); 396 397SELECT * from t1; 398show create table t1; 399 400ALTER TABLE t1 ADD COLUMN d int; 401show create table t1; 402 403drop table t1; 404 405# 406# Subpartition by hash, two partitions and two subpartitions 407# Defined tablespace, engine and node group 408# 409CREATE TABLE t1 ( 410a int not null, 411b int not null, 412c int not null, 413primary key (a,b)) 414partition by range (a) 415subpartition by hash (a+b) 416( partition x1 values less than (1) 417 ( subpartition x11 tablespace t1 engine myisam nodegroup 0, 418 subpartition x12 tablespace t2 engine myisam nodegroup 1), 419 partition x2 values less than (5) 420 ( subpartition x21 tablespace t1 engine myisam nodegroup 0, 421 subpartition x22 tablespace t2 engine myisam nodegroup 1) 422); 423 424SELECT * from t1; 425 426drop table t1; 427 428# 429# Subpartition by hash, two partitions and two subpartitions 430# Defined tablespace, node group 431# 432CREATE TABLE t1 ( 433a int not null, 434b int not null, 435c int not null, 436primary key (a,b)) 437partition by range (a) 438subpartition by hash (a+b) 439( partition x1 values less than (1) 440 ( subpartition x11 tablespace ts1 nodegroup 0, 441 subpartition x12 tablespace ts2 nodegroup 1), 442 partition x2 values less than (5) 443 ( subpartition x21 tablespace ts1 nodegroup 0, 444 subpartition x22 tablespace ts2 nodegroup 1) 445); 446 447SELECT * from t1; 448SHOW CREATE TABLE t1; 449 450drop table t1; 451DROP TABLESPACE ts1; 452DROP TABLESPACE ts2; 453DROP TABLESPACE ts3; 454 455# 456# Subpartition by hash, two partitions and two subpartitions 457# Defined engine and node group 458# 459CREATE TABLE t1 ( 460a int not null, 461b int not null, 462c int not null, 463primary key (a,b)) 464partition by range (a) 465subpartition by hash (a+b) 466( partition x1 values less than (1) 467 ( subpartition x11 engine myisam nodegroup 0, 468 subpartition x12 engine myisam nodegroup 1), 469 partition x2 values less than (5) 470 ( subpartition x21 engine myisam nodegroup 0, 471 subpartition x22 engine myisam nodegroup 1) 472); 473 474INSERT into t1 VALUES (1,1,1); 475INSERT into t1 VALUES (4,1,1); 476--error ER_NO_PARTITION_FOR_GIVEN_VALUE 477INSERT into t1 VALUES (5,1,1); 478 479SELECT * from t1; 480 481ALTER TABLE t1 482partition by range (a) 483subpartition by hash (a+b) 484( partition x1 values less than (1) 485 ( subpartition x11 engine myisam nodegroup 0, 486 subpartition x12 engine myisam nodegroup 1), 487 partition x2 values less than (5) 488 ( subpartition x21 engine myisam nodegroup 0, 489 subpartition x22 engine myisam nodegroup 1) 490); 491 492SELECT * from t1; 493 494drop table t1; 495 496# 497# Subpartition by hash, two partitions and two subpartitions 498# Defined tablespace, engine 499# 500CREATE TABLE t1 ( 501a int not null, 502b int not null, 503c int not null, 504primary key (a,b)) 505partition by range (a) 506subpartition by hash (a+b) 507( partition x1 values less than (1) 508 ( subpartition x11 tablespace t1 engine myisam, 509 subpartition x12 tablespace t2 engine myisam), 510 partition x2 values less than (5) 511 ( subpartition x21 tablespace t1 engine myisam, 512 subpartition x22 tablespace t2 engine myisam) 513); 514 515INSERT into t1 VALUES (1,1,1); 516INSERT into t1 VALUES (4,1,1); 517--error ER_NO_PARTITION_FOR_GIVEN_VALUE 518INSERT into t1 VALUES (5,1,1); 519 520SELECT * from t1; 521 522ALTER TABLE t1 523partition by range (a) 524subpartition by hash (a+b) 525( partition x1 values less than (1) 526 ( subpartition x11 tablespace t1 engine myisam, 527 subpartition x12 tablespace t2 engine myisam), 528 partition x2 values less than (5) 529 ( subpartition x21 tablespace t1 engine myisam, 530 subpartition x22 tablespace t2 engine myisam) 531); 532 533SELECT * from t1; 534 535drop table t1; 536 537# 538# Subpartition by hash, two partitions and two subpartitions 539# Defined tablespace 540# 541CREATE TABLE t1 ( 542a int not null, 543b int not null, 544c int not null, 545primary key (a,b)) 546partition by range (a) 547subpartition by hash (a+b) 548( partition x1 values less than (1) 549 ( subpartition x11 tablespace t1 engine myisam, 550 subpartition x12 tablespace t2 engine myisam), 551 partition x2 values less than (5) 552 ( subpartition x21 tablespace t1 engine myisam, 553 subpartition x22 tablespace t2 engine myisam) 554); 555 556INSERT into t1 VALUES (1,1,1); 557INSERT into t1 VALUES (4,1,1); 558--error ER_NO_PARTITION_FOR_GIVEN_VALUE 559INSERT into t1 VALUES (5,1,1); 560 561SELECT * from t1; 562 563ALTER TABLE t1 564partition by range (a) 565subpartition by hash (a+b) 566( partition x1 values less than (1) 567 ( subpartition x11 tablespace t1 engine myisam, 568 subpartition x12 tablespace t2 engine myisam), 569 partition x2 values less than (5) 570 ( subpartition x21 tablespace t1 engine myisam, 571 subpartition x22 tablespace t2 engine myisam) 572); 573 574SELECT * from t1; 575 576drop table t1; 577 578# 579# Subpartition by hash, two partitions and two subpartitions 580# Defined engine 581# 582CREATE TABLE t1 ( 583a int not null, 584b int not null, 585c int not null, 586primary key (a,b)) 587partition by range (a) 588subpartition by hash (a+b) 589( partition x1 values less than (1) 590 ( subpartition x11 engine myisam, 591 subpartition x12 engine myisam), 592 partition x2 values less than (5) 593 ( subpartition x21 engine myisam, 594 subpartition x22 engine myisam) 595); 596 597INSERT into t1 VALUES (1,1,1); 598INSERT into t1 VALUES (4,1,1); 599--error ER_NO_PARTITION_FOR_GIVEN_VALUE 600INSERT into t1 VALUES (5,1,1); 601 602SELECT * from t1; 603 604ALTER TABLE t1 605partition by range (a) 606subpartition by hash (a+b) 607( partition x1 values less than (1) 608 ( subpartition x11 engine myisam, 609 subpartition x12 engine myisam), 610 partition x2 values less than (5) 611 ( subpartition x21 engine myisam, 612 subpartition x22 engine myisam) 613); 614 615SELECT * from t1; 616 617drop table t1; 618 619# 620# Bug #17894 Comparison with "less than" operator fails with Range partition 621# 622CREATE TABLE t1 (c1 int default NULL, c2 varchar(30) default NULL, 623c3 date default NULL) engine=myisam 624PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), 625PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , 626PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , 627PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , 628PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , 629PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), 630PARTITION p11 VALUES LESS THAN MAXVALUE ); 631INSERT INTO t1 VALUES (1, 'testing partitions', '1995-07-17'), 632(3, 'testing partitions','1995-07-31'), 633(5, 'testing partitions','1995-08-13'), 634(7, 'testing partitions','1995-08-26'), 635(9, 'testing partitions','1995-09-09'), 636(0, 'testing partitions','2000-07-10'), 637(2, 'testing partitions','2000-07-23'), 638(4, 'testing partitions','2000-08-05'), 639(6, 'testing partitions','2000-08-19'), 640(8, 'testing partitions','2000-09-01'); 641SELECT COUNT(*) FROM t1 WHERE c3 BETWEEN '1996-12-31' AND '2000-12-31'; 642SELECT COUNT(*) FROM t1 WHERE c3 < '2000-12-31'; 643DROP TABLE t1; 644 645# 646# BUG 16002: Unsigned partition functions not handled correctly 647# 648--error ER_RANGE_NOT_INCREASING_ERROR 649create table t1 (a bigint unsigned) 650partition by range (a) 651(partition p0 values less than (10), 652 partition p1 values less than (0)); 653 654create table t1 (a bigint unsigned) 655partition by range (a) 656(partition p0 values less than (0), 657 partition p1 values less than (10)); 658show create table t1; 659drop table t1; 660 661create table t1 (a bigint unsigned) 662partition by range (a) 663(partition p0 values less than (2), 664 partition p1 values less than (10)); 665show create table t1; 666--error ER_NO_PARTITION_FOR_GIVEN_VALUE 667insert into t1 values (0xFFFFFFFFFFFFFFFF); 668drop table t1; 669 670# 671# BUG 18962 Errors in DROP PARTITION 672# 673create table t1 (a int) 674partition by range (MOD(a,3)) 675subpartition by hash(a) 676subpartitions 2 677(partition p0 values less than (1), 678 partition p1 values less than (2), 679 partition p2 values less than (3), 680 partition p3 values less than (4)); 681ALTER TABLE t1 DROP PARTITION p3; 682ALTER TABLE t1 DROP PARTITION p1; 683ALTER TABLE t1 DROP PARTITION p2; 684drop table t1; 685 686create table t1 (a int) 687partition by range (MOD(a,3)) 688subpartition by hash(a) 689subpartitions 2 690(partition p0 values less than (1), 691 partition p1 values less than (2), 692 partition p2 values less than (3), 693 partition p3 values less than (4)); 694ALTER TABLE t1 DROP PARTITION p0; 695ALTER TABLE t1 DROP PARTITION p1; 696ALTER TABLE t1 DROP PARTITION p2; 697drop table t1; 698 699# 700# Bug 19830: ALTER TABLE t1 REORGANIZE PARTITION crashes 701# 702create table t1 (a int DEFAULT NULL, 703 b varchar(30) DEFAULT NULL, 704 c date DEFAULT NULL) 705ENGINE=MYISAM DEFAULT CHARSET=latin1; 706 707insert into t1 values (1, 'abc', '1995-01-01'); 708insert into t1 values (1, 'abc', '1995-01-02'); 709insert into t1 values (1, 'abc', '1995-01-03'); 710insert into t1 values (1, 'abc', '1995-01-04'); 711insert into t1 values (1, 'abc', '1995-01-05'); 712insert into t1 values (1, 'abc', '1995-01-06'); 713insert into t1 values (1, 'abc', '1995-01-07'); 714insert into t1 values (1, 'abc', '1995-01-08'); 715insert into t1 values (1, 'abc', '1995-01-09'); 716insert into t1 values (1, 'abc', '1995-01-10'); 717insert into t1 values (1, 'abc', '1995-01-11'); 718insert into t1 values (1, 'abc', '1995-01-12'); 719insert into t1 values (1, 'abc', '1995-01-13'); 720insert into t1 values (1, 'abc', '1995-01-14'); 721insert into t1 values (1, 'abc', '1995-01-15'); 722insert into t1 values (1, 'abc', '1997-01-01'); 723insert into t1 values (1, 'abc', '1997-01-02'); 724insert into t1 values (1, 'abc', '1997-01-03'); 725insert into t1 values (1, 'abc', '1997-01-04'); 726insert into t1 values (1, 'abc', '1997-01-05'); 727insert into t1 values (1, 'abc', '1997-01-06'); 728insert into t1 values (1, 'abc', '1997-01-07'); 729insert into t1 values (1, 'abc', '1997-01-08'); 730insert into t1 values (1, 'abc', '1997-01-09'); 731insert into t1 values (1, 'abc', '1997-01-10'); 732insert into t1 values (1, 'abc', '1997-01-11'); 733insert into t1 values (1, 'abc', '1997-01-12'); 734insert into t1 values (1, 'abc', '1997-01-13'); 735insert into t1 values (1, 'abc', '1997-01-14'); 736insert into t1 values (1, 'abc', '1997-01-15'); 737insert into t1 values (1, 'abc', '1998-01-01'); 738insert into t1 values (1, 'abc', '1998-01-02'); 739insert into t1 values (1, 'abc', '1998-01-03'); 740insert into t1 values (1, 'abc', '1998-01-04'); 741insert into t1 values (1, 'abc', '1998-01-05'); 742insert into t1 values (1, 'abc', '1998-01-06'); 743insert into t1 values (1, 'abc', '1998-01-07'); 744insert into t1 values (1, 'abc', '1998-01-08'); 745insert into t1 values (1, 'abc', '1998-01-09'); 746insert into t1 values (1, 'abc', '1998-01-10'); 747insert into t1 values (1, 'abc', '1998-01-11'); 748insert into t1 values (1, 'abc', '1998-01-12'); 749insert into t1 values (1, 'abc', '1998-01-13'); 750insert into t1 values (1, 'abc', '1998-01-14'); 751insert into t1 values (1, 'abc', '1998-01-15'); 752insert into t1 values (1, 'abc', '1999-01-01'); 753insert into t1 values (1, 'abc', '1999-01-02'); 754insert into t1 values (1, 'abc', '1999-01-03'); 755insert into t1 values (1, 'abc', '1999-01-04'); 756insert into t1 values (1, 'abc', '1999-01-05'); 757insert into t1 values (1, 'abc', '1999-01-06'); 758insert into t1 values (1, 'abc', '1999-01-07'); 759insert into t1 values (1, 'abc', '1999-01-08'); 760insert into t1 values (1, 'abc', '1999-01-09'); 761insert into t1 values (1, 'abc', '1999-01-10'); 762insert into t1 values (1, 'abc', '1999-01-11'); 763insert into t1 values (1, 'abc', '1999-01-12'); 764insert into t1 values (1, 'abc', '1999-01-13'); 765insert into t1 values (1, 'abc', '1999-01-14'); 766insert into t1 values (1, 'abc', '1999-01-15'); 767insert into t1 values (1, 'abc', '2000-01-01'); 768insert into t1 values (1, 'abc', '2000-01-02'); 769insert into t1 values (1, 'abc', '2000-01-03'); 770insert into t1 values (1, 'abc', '2000-01-04'); 771insert into t1 values (1, 'abc', '2000-01-05'); 772insert into t1 values (1, 'abc', '2000-01-06'); 773insert into t1 values (1, 'abc', '2000-01-07'); 774insert into t1 values (1, 'abc', '2000-01-08'); 775insert into t1 values (1, 'abc', '2000-01-09'); 776insert into t1 values (1, 'abc', '2000-01-15'); 777insert into t1 values (1, 'abc', '2000-01-11'); 778insert into t1 values (1, 'abc', '2000-01-12'); 779insert into t1 values (1, 'abc', '2000-01-13'); 780insert into t1 values (1, 'abc', '2000-01-14'); 781insert into t1 values (1, 'abc', '2000-01-15'); 782insert into t1 values (1, 'abc', '2001-01-01'); 783insert into t1 values (1, 'abc', '2001-01-02'); 784insert into t1 values (1, 'abc', '2001-01-03'); 785insert into t1 values (1, 'abc', '2001-01-04'); 786insert into t1 values (1, 'abc', '2001-01-05'); 787insert into t1 values (1, 'abc', '2001-01-06'); 788insert into t1 values (1, 'abc', '2001-01-07'); 789insert into t1 values (1, 'abc', '2001-01-08'); 790insert into t1 values (1, 'abc', '2001-01-09'); 791insert into t1 values (1, 'abc', '2001-01-15'); 792insert into t1 values (1, 'abc', '2001-01-11'); 793insert into t1 values (1, 'abc', '2001-01-12'); 794insert into t1 values (1, 'abc', '2001-01-13'); 795insert into t1 values (1, 'abc', '2001-01-14'); 796insert into t1 values (1, 'abc', '2001-01-15'); 797 798alter table t1 799partition by range (year(c)) 800(partition p5 values less than (2000), partition p10 values less than (2010)); 801 802alter table t1 803reorganize partition p5 into 804(partition p1 values less than (1996), 805 partition p2 values less than (1997), 806 partition p3 values less than (1998), 807 partition p4 values less than (1999), 808 partition p5 values less than (2000)); 809 810drop table t1; 811 812# 813# New test cases for date based partitioning 814# 815CREATE TABLE t1 (a date) 816PARTITION BY RANGE (TO_DAYS(a)) 817(PARTITION p3xx VALUES LESS THAN (TO_DAYS('2004-01-01')), 818 PARTITION p401 VALUES LESS THAN (TO_DAYS('2004-02-01')), 819 PARTITION p402 VALUES LESS THAN (TO_DAYS('2004-03-01')), 820 PARTITION p403 VALUES LESS THAN (TO_DAYS('2004-04-01')), 821 PARTITION p404 VALUES LESS THAN (TO_DAYS('2004-05-01')), 822 PARTITION p405 VALUES LESS THAN (TO_DAYS('2004-06-01')), 823 PARTITION p406 VALUES LESS THAN (TO_DAYS('2004-07-01')), 824 PARTITION p407 VALUES LESS THAN (TO_DAYS('2004-08-01')), 825 PARTITION p408 VALUES LESS THAN (TO_DAYS('2004-09-01')), 826 PARTITION p409 VALUES LESS THAN (TO_DAYS('2004-10-01')), 827 PARTITION p410 VALUES LESS THAN (TO_DAYS('2004-11-01')), 828 PARTITION p411 VALUES LESS THAN (TO_DAYS('2004-12-01')), 829 PARTITION p412 VALUES LESS THAN (TO_DAYS('2005-01-01')), 830 PARTITION p501 VALUES LESS THAN (TO_DAYS('2005-02-01')), 831 PARTITION p502 VALUES LESS THAN (TO_DAYS('2005-03-01')), 832 PARTITION p503 VALUES LESS THAN (TO_DAYS('2005-04-01')), 833 PARTITION p504 VALUES LESS THAN (TO_DAYS('2005-05-01')), 834 PARTITION p505 VALUES LESS THAN (TO_DAYS('2005-06-01')), 835 PARTITION p506 VALUES LESS THAN (TO_DAYS('2005-07-01')), 836 PARTITION p507 VALUES LESS THAN (TO_DAYS('2005-08-01')), 837 PARTITION p508 VALUES LESS THAN (TO_DAYS('2005-09-01')), 838 PARTITION p509 VALUES LESS THAN (TO_DAYS('2005-10-01')), 839 PARTITION p510 VALUES LESS THAN (TO_DAYS('2005-11-01')), 840 PARTITION p511 VALUES LESS THAN (TO_DAYS('2005-12-01')), 841 PARTITION p512 VALUES LESS THAN (TO_DAYS('2006-01-01')), 842 PARTITION p601 VALUES LESS THAN (TO_DAYS('2006-02-01')), 843 PARTITION p602 VALUES LESS THAN (TO_DAYS('2006-03-01')), 844 PARTITION p603 VALUES LESS THAN (TO_DAYS('2006-04-01')), 845 PARTITION p604 VALUES LESS THAN (TO_DAYS('2006-05-01')), 846 PARTITION p605 VALUES LESS THAN (TO_DAYS('2006-06-01')), 847 PARTITION p606 VALUES LESS THAN (TO_DAYS('2006-07-01')), 848 PARTITION p607 VALUES LESS THAN (TO_DAYS('2006-08-01'))); 849 850INSERT INTO t1 VALUES ('2003-01-13'),('2003-06-20'),('2003-08-30'); 851INSERT INTO t1 VALUES ('2003-04-13'),('2003-07-20'),('2003-10-30'); 852INSERT INTO t1 VALUES ('2003-05-13'),('2003-11-20'),('2003-12-30'); 853 854INSERT INTO t1 VALUES ('2004-01-13'),('2004-01-20'),('2004-01-30'); 855INSERT INTO t1 VALUES ('2004-02-13'),('2004-02-20'),('2004-02-28'); 856INSERT INTO t1 VALUES ('2004-03-13'),('2004-03-20'),('2004-03-30'); 857INSERT INTO t1 VALUES ('2004-04-13'),('2004-04-20'),('2004-04-30'); 858INSERT INTO t1 VALUES ('2004-05-13'),('2004-05-20'),('2004-05-30'); 859INSERT INTO t1 VALUES ('2004-06-13'),('2004-06-20'),('2004-06-30'); 860INSERT INTO t1 VALUES ('2004-07-13'),('2004-07-20'),('2004-07-30'); 861INSERT INTO t1 VALUES ('2004-08-13'),('2004-08-20'),('2004-08-30'); 862INSERT INTO t1 VALUES ('2004-09-13'),('2004-09-20'),('2004-09-30'); 863INSERT INTO t1 VALUES ('2004-10-13'),('2004-10-20'),('2004-10-30'); 864INSERT INTO t1 VALUES ('2004-11-13'),('2004-11-20'),('2004-11-30'); 865INSERT INTO t1 VALUES ('2004-12-13'),('2004-12-20'),('2004-12-30'); 866 867INSERT INTO t1 VALUES ('2005-01-13'),('2005-01-20'),('2005-01-30'); 868INSERT INTO t1 VALUES ('2005-02-13'),('2005-02-20'),('2005-02-28'); 869INSERT INTO t1 VALUES ('2005-03-13'),('2005-03-20'),('2005-03-30'); 870INSERT INTO t1 VALUES ('2005-04-13'),('2005-04-20'),('2005-04-30'); 871INSERT INTO t1 VALUES ('2005-05-13'),('2005-05-20'),('2005-05-30'); 872INSERT INTO t1 VALUES ('2005-06-13'),('2005-06-20'),('2005-06-30'); 873INSERT INTO t1 VALUES ('2005-07-13'),('2005-07-20'),('2005-07-30'); 874INSERT INTO t1 VALUES ('2005-08-13'),('2005-08-20'),('2005-08-30'); 875INSERT INTO t1 VALUES ('2005-09-13'),('2005-09-20'),('2005-09-30'); 876INSERT INTO t1 VALUES ('2005-10-13'),('2005-10-20'),('2005-10-30'); 877INSERT INTO t1 VALUES ('2005-11-13'),('2005-11-20'),('2005-11-30'); 878INSERT INTO t1 VALUES ('2005-12-13'),('2005-12-20'),('2005-12-30'); 879 880INSERT INTO t1 VALUES ('2006-01-13'),('2006-01-20'),('2006-01-30'); 881INSERT INTO t1 VALUES ('2006-02-13'),('2006-02-20'),('2006-02-28'); 882INSERT INTO t1 VALUES ('2006-03-13'),('2006-03-20'),('2006-03-30'); 883INSERT INTO t1 VALUES ('2006-04-13'),('2006-04-20'),('2006-04-30'); 884INSERT INTO t1 VALUES ('2006-05-13'),('2006-05-20'),('2006-05-30'); 885INSERT INTO t1 VALUES ('2006-06-13'),('2006-06-20'),('2006-06-30'); 886INSERT INTO t1 VALUES ('2006-07-13'),('2006-07-20'),('2006-07-30'); 887 888SELECT * FROM t1 889WHERE a >= '2004-07-01' AND a <= '2004-09-30'; 890analyze table t1; 891EXPLAIN PARTITIONS SELECT * FROM t1 892WHERE a >= '2004-07-01' AND a <= '2004-09-30'; 893SELECT * from t1 894WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR 895 (a >= '2005-07-01' AND a <= '2005-09-30'); 896EXPLAIN PARTITIONS SELECT * from t1 897WHERE (a >= '2004-07-01' AND a <= '2004-09-30') OR 898 (a >= '2005-07-01' AND a <= '2005-09-30'); 899DROP TABLE t1; 900 901# 902# Bug 18198: Try with a couple of cases using VARCHAR fields in 903# partition function. 904#create table t1 (a varchar(20)) 905#partition by range (ascii(a)) 906#(partition p0 values less than (100), 907# partition p1 values less than maxvalue); 908 909#insert into t1 values ("12345678901234567890"); 910#insert into t1 values ("A2345678901234567890"); 911#insert into t1 values ("B2345678901234567890"); 912#insert into t1 values ("1234567890123456789"); 913#insert into t1 values ("1234567890123456"); 914#select * from t1; 915#explain partitions select * from t1 where a = "12345678901234567890"; 916#explain partitions select * from t1 where a = "12345678901234567890" OR 917# a = "A2345678901234567890" OR 918# a = "B2345678901234567890" OR 919# a = "C2345678901234567890"; 920#explain partitions select * from t1 where a = "01234567890123456"; 921#select * from t1 where a = "01234567890123456"; 922#select * from t1 where a = "12345678901234567890" OR 923# a = "A2345678901234567890" OR 924# a = "B2345678901234567890" OR 925# a = "C2345678901234567890"; 926#select * from t1 where a = "12345678901234567890"; 927#drop table t1; 928 929 930# 931# BUG#30573: get wrong result with "group by" on PARTITIONed table 932# 933create table t1 (a int); 934insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 935CREATE TABLE t2 ( 936 defid int(10) unsigned NOT NULL, 937 day int(10) unsigned NOT NULL, 938 count int(10) unsigned NOT NULL, 939 filler char(200), 940 KEY (defid,day) 941) 942PARTITION BY RANGE (day) ( 943 PARTITION p7 VALUES LESS THAN (20070401) , 944 PARTITION p8 VALUES LESS THAN (20070501)); 945 946insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; 947insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; 948insert into t2 values(52, 20070321, 123, 'filler') ; 949insert into t2 values(52, 20070322, 456, 'filler') ; 950 951select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; 952drop table t1, t2; 953 954 955--echo # 956--echo # Bug#50939: Loose Index Scan unduly relies on engine to remember range 957--echo # endpoints 958--echo # 959CREATE TABLE t1 ( 960 a INT, 961 b INT, 962 KEY ( a, b ) 963) PARTITION BY HASH (a) PARTITIONS 1; 964 965CREATE TABLE t2 ( 966 a INT, 967 b INT, 968 KEY ( a, b ) 969); 970 971INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); 972 973INSERT INTO t1 SELECT a + 5, b + 5 FROM t1; 974INSERT INTO t1 SELECT a + 10, b + 10 FROM t1; 975INSERT INTO t1 SELECT a + 20, b + 20 FROM t1; 976INSERT INTO t1 SELECT a + 40, b + 40 FROM t1; 977 978INSERT INTO t2 SELECT * FROM t1; 979 980--echo # plans should be identical 981analyze table t1; 982analyze table t2; 983EXPLAIN SELECT a, MAX(b) FROM t1 WHERE a IN (10,100) GROUP BY a; 984EXPLAIN SELECT a, MAX(b) FROM t2 WHERE a IN (10,100) GROUP BY a; 985 986FLUSH status; 987SELECT a, MAX(b) FROM t1 WHERE a IN (10, 100) GROUP BY a; 988--echo # Should be no more than 2 reads. 989SHOW status LIKE 'handler_read_key'; 990 991FLUSH status; 992SELECT a, MAX(b) FROM t2 WHERE a IN (10, 100) GROUP BY a; 993--echo # Should be no more than 2 reads. 994SHOW status LIKE 'handler_read_key'; 995 996DROP TABLE t1, t2; 997