1drop table if exists t1; 2CREATE TABLE t1 ( 3a int not null, 4b int not null, 5primary key(a), 6index (b)) 7partition by range (a) 8partitions 2 9(partition x1 values less than (25), 10partition x2 values less than (100)); 11INSERT into t1 values (1, 1); 12INSERT into t1 values (2, 5); 13INSERT into t1 values (30, 4); 14INSERT into t1 values (35, 2); 15select * from t1 order by b; 16a b 171 1 1835 2 1930 4 202 5 21select * from t1 force index (b) where b > 0 order by b; 22a b 231 1 2435 2 2530 4 262 5 27drop table t1; 28CREATE TABLE t1 ( 29a int not null, 30b int unsigned not null, 31primary key(a), 32index (b)) 33partition by range (a) 34partitions 2 35(partition x1 values less than (25), 36partition x2 values less than (100)); 37INSERT into t1 values (1, 1); 38INSERT into t1 values (2, 5); 39INSERT into t1 values (30, 4); 40INSERT into t1 values (35, 2); 41select * from t1 force index (b) where b > 0 order by b; 42a b 431 1 4435 2 4530 4 462 5 47drop table t1; 48CREATE TABLE t1 ( 49a int not null, 50b tinyint not null, 51primary key(a), 52index (b)) 53partition by range (a) 54partitions 2 55(partition x1 values less than (25), 56partition x2 values less than (100)); 57INSERT into t1 values (1, 1); 58INSERT into t1 values (2, 5); 59INSERT into t1 values (30, 4); 60INSERT into t1 values (35, 2); 61select * from t1 force index (b) where b > 0 order by b; 62a b 631 1 6435 2 6530 4 662 5 67drop table t1; 68CREATE TABLE t1 ( 69a int not null, 70b tinyint unsigned not null, 71primary key(a), 72index (b)) 73partition by range (a) 74partitions 2 75(partition x1 values less than (25), 76partition x2 values less than (100)); 77INSERT into t1 values (1, 1); 78INSERT into t1 values (2, 5); 79INSERT into t1 values (30, 4); 80INSERT into t1 values (35, 2); 81select * from t1 force index (b) where b > 0 order by b; 82a b 831 1 8435 2 8530 4 862 5 87drop table t1; 88CREATE TABLE t1 ( 89a int not null, 90b smallint not null, 91primary key(a), 92index (b)) 93partition by range (a) 94partitions 2 95(partition x1 values less than (25), 96partition x2 values less than (100)); 97INSERT into t1 values (1, 1); 98INSERT into t1 values (2, 5); 99INSERT into t1 values (30, 4); 100INSERT into t1 values (35, 2); 101select * from t1 force index (b) where b > 0 order by b; 102a b 1031 1 10435 2 10530 4 1062 5 107drop table t1; 108CREATE TABLE t1 ( 109a int not null, 110b smallint unsigned not null, 111primary key(a), 112index (b)) 113partition by range (a) 114partitions 2 115(partition x1 values less than (25), 116partition x2 values less than (100)); 117INSERT into t1 values (1, 1); 118INSERT into t1 values (2, 5); 119INSERT into t1 values (30, 4); 120INSERT into t1 values (35, 2); 121select * from t1 force index (b) where b > 0 order by b; 122a b 1231 1 12435 2 12530 4 1262 5 127drop table t1; 128CREATE TABLE t1 ( 129a int not null, 130b mediumint not null, 131primary key(a), 132index (b)) 133partition by range (a) 134partitions 2 135(partition x1 values less than (25), 136partition x2 values less than (100)); 137INSERT into t1 values (1, 1); 138INSERT into t1 values (2, 5); 139INSERT into t1 values (30, 4); 140INSERT into t1 values (35, 2); 141select * from t1 force index (b) where b > 0 order by b; 142a b 1431 1 14435 2 14530 4 1462 5 147drop table t1; 148CREATE TABLE t1 ( 149a int not null, 150b mediumint unsigned not null, 151primary key(a), 152index (b)) 153partition by range (a) 154partitions 2 155(partition x1 values less than (25), 156partition x2 values less than (100)); 157INSERT into t1 values (1, 1); 158INSERT into t1 values (2, 5); 159INSERT into t1 values (30, 4); 160INSERT into t1 values (35, 2); 161select * from t1 force index (b) where b > 0 order by b; 162a b 1631 1 16435 2 16530 4 1662 5 167drop table t1; 168CREATE TABLE t1 ( 169a int not null, 170b bigint unsigned not null, 171primary key(a), 172index (b)) 173partition by range (a) 174partitions 2 175(partition x1 values less than (25), 176partition x2 values less than (100)); 177INSERT into t1 values (1, 1); 178INSERT into t1 values (2, 5); 179INSERT into t1 values (30, 4); 180INSERT into t1 values (35, 2); 181select * from t1 force index (b) where b > 0 order by b; 182a b 1831 1 18435 2 18530 4 1862 5 187drop table t1; 188CREATE TABLE t1 ( 189a int not null, 190b bigint not null, 191primary key(a), 192index (b)) 193partition by range (a) 194partitions 2 195(partition x1 values less than (25), 196partition x2 values less than (100)); 197INSERT into t1 values (1, 1); 198INSERT into t1 values (2, 5); 199INSERT into t1 values (30, 4); 200INSERT into t1 values (35, 2); 201select * from t1 force index (b) where b > 0 order by b; 202a b 2031 1 20435 2 20530 4 2062 5 207drop table t1; 208CREATE TABLE t1 ( 209a int not null, 210b bigint not null, 211primary key(a), 212index (b)) 213partition by range (a) 214partitions 2 215(partition x1 values less than (25), 216partition x2 values less than (100)); 217INSERT into t1 values (1, 1); 218INSERT into t1 values (2, 5); 219INSERT into t1 values (30, 4); 220INSERT into t1 values (35, 2); 221select * from t1 force index (b) where b > 0 order by b; 222a b 2231 1 22435 2 22530 4 2262 5 227drop table t1; 228CREATE TABLE t1 ( 229a int not null, 230b float not null, 231primary key(a), 232index (b)) 233partition by range (a) 234partitions 2 235(partition x1 values less than (25), 236partition x2 values less than (100)); 237INSERT into t1 values (1, 1); 238INSERT into t1 values (2, 5); 239INSERT into t1 values (30, 4); 240INSERT into t1 values (35, 2); 241select * from t1 force index (b) where b > 0 order by b; 242a b 2431 1 24435 2 24530 4 2462 5 247drop table t1; 248CREATE TABLE t1 ( 249a int not null, 250b double not null, 251primary key(a), 252index (b)) 253partition by range (a) 254partitions 2 255(partition x1 values less than (25), 256partition x2 values less than (100)); 257INSERT into t1 values (1, 1); 258INSERT into t1 values (2, 5); 259INSERT into t1 values (30, 4); 260INSERT into t1 values (35, 2); 261select * from t1 force index (b) where b > 0 order by b; 262a b 2631 1 26435 2 26530 4 2662 5 267drop table t1; 268CREATE TABLE t1 ( 269a int not null, 270b double unsigned not null, 271primary key(a), 272index (b)) 273partition by range (a) 274partitions 2 275(partition x1 values less than (25), 276partition x2 values less than (100)); 277INSERT into t1 values (1, 1); 278INSERT into t1 values (2, 5); 279INSERT into t1 values (30, 4); 280INSERT into t1 values (35, 2); 281select * from t1 force index (b) where b > 0 order by b; 282a b 2831 1 28435 2 28530 4 2862 5 287drop table t1; 288CREATE TABLE t1 ( 289a int not null, 290b float unsigned not null, 291primary key(a), 292index (b)) 293partition by range (a) 294partitions 2 295(partition x1 values less than (25), 296partition x2 values less than (100)); 297INSERT into t1 values (1, 1); 298INSERT into t1 values (2, 5); 299INSERT into t1 values (30, 4); 300INSERT into t1 values (35, 2); 301select * from t1 force index (b) where b > 0 order by b; 302a b 3031 1 30435 2 30530 4 3062 5 307drop table t1; 308CREATE TABLE t1 ( 309a int not null, 310b double precision not null, 311primary key(a), 312index (b)) 313partition by range (a) 314partitions 2 315(partition x1 values less than (25), 316partition x2 values less than (100)); 317INSERT into t1 values (1, 1); 318INSERT into t1 values (2, 5); 319INSERT into t1 values (30, 4); 320INSERT into t1 values (35, 2); 321select * from t1 force index (b) where b > 0 order by b; 322a b 3231 1 32435 2 32530 4 3262 5 327drop table t1; 328CREATE TABLE t1 ( 329a int not null, 330b double precision unsigned not null, 331primary key(a), 332index (b)) 333partition by range (a) 334partitions 2 335(partition x1 values less than (25), 336partition x2 values less than (100)); 337INSERT into t1 values (1, 1); 338INSERT into t1 values (2, 5); 339INSERT into t1 values (30, 4); 340INSERT into t1 values (35, 2); 341select * from t1 force index (b) where b > 0 order by b; 342a b 3431 1 34435 2 34530 4 3462 5 347drop table t1; 348CREATE TABLE t1 ( 349a int not null, 350b decimal not null, 351primary key(a), 352index (b)) 353partition by range (a) 354partitions 2 355(partition x1 values less than (25), 356partition x2 values less than (100)); 357INSERT into t1 values (1, 1); 358INSERT into t1 values (2, 5); 359INSERT into t1 values (30, 4); 360INSERT into t1 values (35, 2); 361select * from t1 force index (b) where b > 0 order by b; 362a b 3631 1 36435 2 36530 4 3662 5 367drop table t1; 368CREATE TABLE t1 ( 369a int not null, 370b char(10) not null, 371primary key(a), 372index (b)) 373partition by range (a) 374partitions 2 375(partition x1 values less than (25), 376partition x2 values less than (100)); 377INSERT into t1 values (1, '1'); 378INSERT into t1 values (2, '5'); 379INSERT into t1 values (30, '4'); 380INSERT into t1 values (35, '2'); 381select * from t1 force index (b) where b > 0 order by b; 382a b 3831 1 38435 2 38530 4 3862 5 387drop table t1; 388CREATE TABLE t1 ( 389a int not null, 390b varchar(10) not null, 391primary key(a), 392index (b)) 393partition by range (a) 394partitions 2 395(partition x1 values less than (25), 396partition x2 values less than (100)); 397INSERT into t1 values (1, '1'); 398INSERT into t1 values (2, '5'); 399INSERT into t1 values (30, '4'); 400INSERT into t1 values (35, '2'); 401select * from t1 force index (b) where b > '0' order by b; 402a b 4031 1 40435 2 40530 4 4062 5 407drop table t1; 408CREATE TABLE t1 ( 409a int not null, 410b varchar(10) not null, 411primary key(a), 412index (b(5))) 413partition by range (a) 414partitions 2 415(partition x1 values less than (25), 416partition x2 values less than (100)); 417INSERT into t1 values (1, '1'); 418INSERT into t1 values (2, '5'); 419INSERT into t1 values (30, '4'); 420INSERT into t1 values (35, '2'); 421select * from t1 force index (b) where b > '0' order by b; 422a b 4231 1 42435 2 42530 4 4262 5 427drop table t1; 428CREATE TABLE t1 ( 429a int not null, 430b varchar(10) binary not null, 431primary key(a), 432index (b)) 433partition by range (a) 434partitions 2 435(partition x1 values less than (25), 436partition x2 values less than (100)); 437INSERT into t1 values (1, '1'); 438INSERT into t1 values (2, '5'); 439INSERT into t1 values (30, '4'); 440INSERT into t1 values (35, '2'); 441select * from t1 force index (b) where b > '0' order by b; 442a b 4431 1 44435 2 44530 4 4462 5 447drop table t1; 448CREATE TABLE t1 ( 449a int not null, 450b tinytext not null, 451primary key(a), 452index (b(10))) 453partition by range (a) 454partitions 2 455(partition x1 values less than (25), 456partition x2 values less than (100)); 457INSERT into t1 values (1, '1'); 458INSERT into t1 values (2, '5'); 459INSERT into t1 values (30, '4'); 460INSERT into t1 values (35, '2'); 461select * from t1 force index (b) where b > '0' order by b; 462a b 4631 1 46435 2 46530 4 4662 5 467drop table t1; 468CREATE TABLE t1 ( 469a int not null, 470b text not null, 471primary key(a), 472index (b(10))) 473partition by range (a) 474partitions 2 475(partition x1 values less than (25), 476partition x2 values less than (100)); 477INSERT into t1 values (1, '1'); 478INSERT into t1 values (2, '5'); 479INSERT into t1 values (30, '4'); 480INSERT into t1 values (35, '2'); 481select * from t1 force index (b) where b > '0' order by b; 482a b 4831 1 48435 2 48530 4 4862 5 487drop table t1; 488CREATE TABLE t1 ( 489a int not null, 490b mediumtext not null, 491primary key(a), 492index (b(10))) 493partition by range (a) 494partitions 2 495(partition x1 values less than (25), 496partition x2 values less than (100)); 497INSERT into t1 values (1, '1'); 498INSERT into t1 values (2, '5'); 499INSERT into t1 values (30, '4'); 500INSERT into t1 values (35, '2'); 501select * from t1 force index (b) where b > '0' order by b; 502a b 5031 1 50435 2 50530 4 5062 5 507drop table t1; 508CREATE TABLE t1 ( 509a int not null, 510b longtext not null, 511primary key(a), 512index (b(10))) 513partition by range (a) 514partitions 2 515(partition x1 values less than (25), 516partition x2 values less than (100)); 517INSERT into t1 values (1, '1'); 518INSERT into t1 values (2, '5'); 519INSERT into t1 values (30, '4'); 520INSERT into t1 values (35, '2'); 521select * from t1 force index (b) where b > '0' order by b; 522a b 5231 1 52435 2 52530 4 5262 5 527drop table t1; 528CREATE TABLE t1 ( 529a int not null, 530b enum('1','2', '4', '5') not null, 531primary key(a), 532index (b)) 533partition by range (a) 534partitions 2 535(partition x1 values less than (25), 536partition x2 values less than (100)); 537INSERT into t1 values (1, '1'); 538INSERT into t1 values (2, '5'); 539INSERT into t1 values (30, '4'); 540INSERT into t1 values (35, '2'); 541select * from t1 force index (b) where b >= '1' order by b; 542a b 5431 1 54435 2 54530 4 5462 5 547drop table t1; 548CREATE TABLE t1 ( 549a int not null, 550b set('1','2', '4', '5') not null, 551primary key(a), 552index (b)) 553partition by range (a) 554partitions 2 555(partition x1 values less than (25), 556partition x2 values less than (100)); 557INSERT into t1 values (1, '1'); 558INSERT into t1 values (2, '5'); 559INSERT into t1 values (30, '4'); 560INSERT into t1 values (35, '2'); 561select * from t1 force index (b) where b >= '1' order by b; 562a b 5631 1 56435 2 56530 4 5662 5 567drop table t1; 568CREATE TABLE t1 ( 569a int not null, 570b date not null, 571primary key(a), 572index (b)) 573partition by range (a) 574partitions 2 575(partition x1 values less than (25), 576partition x2 values less than (100)); 577INSERT into t1 values (1, '2001-01-01'); 578INSERT into t1 values (2, '2005-01-01'); 579INSERT into t1 values (30, '2004-01-01'); 580INSERT into t1 values (35, '2002-01-01'); 581select * from t1 force index (b) where b > '2000-01-01' order by b; 582a b 5831 2001-01-01 58435 2002-01-01 58530 2004-01-01 5862 2005-01-01 587drop table t1; 588CREATE TABLE t1 ( 589a int not null, 590b datetime not null, 591primary key(a), 592index (b)) 593partition by range (a) 594partitions 2 595(partition x1 values less than (25), 596partition x2 values less than (100)); 597INSERT into t1 values (1, '2001-01-01 00:00:00'); 598INSERT into t1 values (2, '2005-01-01 00:00:00'); 599INSERT into t1 values (30, '2004-01-01 00:00:00'); 600INSERT into t1 values (35, '2002-01-01 00:00:00'); 601select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b; 602a b 6031 2001-01-01 00:00:00 60435 2002-01-01 00:00:00 60530 2004-01-01 00:00:00 6062 2005-01-01 00:00:00 607drop table t1; 608CREATE TABLE t1 ( 609a int not null, 610b timestamp not null, 611primary key(a), 612index (b)) 613partition by range (a) 614partitions 2 615(partition x1 values less than (25), 616partition x2 values less than (100)); 617INSERT into t1 values (1, '2001-01-01 00:00:00'); 618INSERT into t1 values (2, '2005-01-01 00:00:00'); 619INSERT into t1 values (30, '2004-01-01 00:00:00'); 620INSERT into t1 values (35, '2002-01-01 00:00:00'); 621select * from t1 force index (b) where b > '2000-01-01 00:00:00' order by b; 622a b 6231 2001-01-01 00:00:00 62435 2002-01-01 00:00:00 62530 2004-01-01 00:00:00 6262 2005-01-01 00:00:00 627drop table t1; 628CREATE TABLE t1 ( 629a int not null, 630b time not null, 631primary key(a), 632index (b)) 633partition by range (a) 634partitions 2 635(partition x1 values less than (25), 636partition x2 values less than (100)); 637INSERT into t1 values (1, '01:00:00'); 638INSERT into t1 values (2, '05:00:00'); 639INSERT into t1 values (30, '04:00:00'); 640INSERT into t1 values (35, '02:00:00'); 641select * from t1 force index (b) where b > '00:00:00' order by b; 642a b 6431 01:00:00 64435 02:00:00 64530 04:00:00 6462 05:00:00 647drop table t1; 648CREATE TABLE t1 ( 649a int not null, 650b year not null, 651primary key(a), 652index (b)) 653partition by range (a) 654partitions 2 655(partition x1 values less than (25), 656partition x2 values less than (100)); 657INSERT into t1 values (1, 2001); 658INSERT into t1 values (2, 2005); 659INSERT into t1 values (30, 2004); 660INSERT into t1 values (35, 2002); 661select * from t1 force index (b) where b > 2000 order by b; 662a b 6631 2001 66435 2002 66530 2004 6662 2005 667drop table t1; 668CREATE TABLE t1 ( 669a int not null, 670b bit(5) not null, 671c int, 672primary key(a), 673index (b)) 674partition by range (a) 675partitions 2 676(partition x1 values less than (25), 677partition x2 values less than (100)); 678INSERT into t1 values (1, b'00001', NULL); 679INSERT into t1 values (2, b'00101', 2); 680INSERT into t1 values (30, b'00100', 2); 681INSERT into t1 values (35, b'00010', NULL); 682select a from t1 force index (b) where b > b'00000' order by b; 683a 6841 68535 68630 6872 688drop table t1; 689CREATE TABLE t1 ( 690a int not null, 691b bit(15) not null, 692c int, 693primary key(a), 694index (b)) 695partition by range (a) 696partitions 2 697(partition x1 values less than (25), 698partition x2 values less than (100)); 699INSERT into t1 values (1, b'000000000000001', NULL); 700INSERT into t1 values (2, b'001010000000101', 2); 701INSERT into t1 values (30, b'001000000000100', 2); 702INSERT into t1 values (35, b'000100000000010', NULL); 703select a from t1 force index (b) where b > b'000000000000000' order by b; 704a 7051 70635 70730 7082 709drop table t1; 710CREATE TABLE t1 ( 711a int not null, 712b int, 713primary key(a), 714index (b)) 715partition by range (a) 716partitions 2 717(partition x1 values less than (25), 718partition x2 values less than (100)); 719INSERT into t1 values (1, 1); 720INSERT into t1 values (5, NULL); 721INSERT into t1 values (2, 4); 722INSERT into t1 values (3, 3); 723INSERT into t1 values (4, 5); 724INSERT into t1 values (7, 1); 725INSERT into t1 values (6, 6); 726INSERT into t1 values (30, 4); 727INSERT into t1 values (35, 2); 728INSERT into t1 values (40, NULL); 729select * from t1 force index (b) where b < 10 OR b IS NULL order by b; 730a b 7315 NULL 73240 NULL 7331 1 7347 1 73535 2 7363 3 73730 4 7382 4 7394 5 7406 6 741select * from t1 force index (b) where b < 10 ORDER BY b; 742a b 7431 1 7447 1 74535 2 7463 3 74730 4 7482 4 7494 5 7506 6 751select * from t1 force index (b) where b < 10 ORDER BY b DESC; 752a b 7536 6 7544 5 7552 4 75630 4 7573 3 75835 2 7597 1 7601 1 761drop table t1; 762create table t1 (a int not null, b int, c varchar(20), key (a,b,c)) 763partition by range (b) 764(partition p0 values less than (5), 765partition p1 values less than (10)); 766INSERT into t1 values (1,1,'1'),(2,2,'2'),(1,3,'3'),(2,4,'4'),(1,5,'5'); 767INSERT into t1 values (2,6,'6'),(1,7,'7'),(2,8,'8'),(1,9,'9'); 768INSERT into t1 values (1, NULL, NULL), (2, NULL, '10'); 769select * from t1 where a = 1 order by a desc, b desc; 770a b c 7711 9 9 7721 7 7 7731 5 5 7741 3 3 7751 1 1 7761 NULL NULL 777select * from t1 where a = 1 order by b desc; 778a b c 7791 9 9 7801 7 7 7811 5 5 7821 3 3 7831 1 1 7841 NULL NULL 785drop table t1; 786