1drop table if exists t1; 2# 3# Bug#52815: LIST COLUMNS doesn't insert rows in correct partition 4# if muliple columns used 5CREATE TABLE t1 ( 6id INT NOT NULL, 7name VARCHAR(255), 8department VARCHAR(10), 9country VARCHAR(255) 10) PARTITION BY LIST COLUMNS (department, country) ( 11PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')), 12PARTITION second_office VALUES IN (('dep2', 'Russia')) 13); 14INSERT INTO t1 VALUES(1, 'Ann', 'dep1', 'Russia'); 15INSERT INTO t1 VALUES(2, 'Bob', 'dep1', 'Croatia'); 16INSERT INTO t1 VALUES(3, 'Cecil', 'dep2', 'Russia'); 17INSERT INTO t1 VALUES(3, 'Dan', 'dep2', 'Croatia'); 18ERROR HY000: Table has no partition for value from column_list 19SELECT PARTITION_NAME,TABLE_ROWS 20FROM INFORMATION_SCHEMA.PARTITIONS 21WHERE TABLE_NAME = 't1'; 22PARTITION_NAME TABLE_ROWS 23first_office 2 24second_office 1 25SHOW CREATE TABLE t1; 26Table Create Table 27t1 CREATE TABLE `t1` ( 28 `id` int(11) NOT NULL, 29 `name` varchar(255) DEFAULT NULL, 30 `department` varchar(10) DEFAULT NULL, 31 `country` varchar(255) DEFAULT NULL 32) ENGINE=MyISAM DEFAULT CHARSET=latin1 33 PARTITION BY LIST COLUMNS(`department`,`country`) 34(PARTITION `first_office` VALUES IN (('dep1','Russia'),('dep1','Croatia')) ENGINE = MyISAM, 35 PARTITION `second_office` VALUES IN (('dep2','Russia')) ENGINE = MyISAM) 36SELECT * FROM t1 WHERE department = 'dep2' and country = 'Croatia'; 37id name department country 38SELECT * FROM t1 WHERE department = 'dep1' and country = 'Croatia'; 39id name department country 402 Bob dep1 Croatia 41DROP TABLE t1; 42CREATE TABLE t1 (a DECIMAL) 43PARTITION BY RANGE COLUMNS (a) 44(PARTITION p0 VALUES LESS THAN (0)); 45ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning 46CREATE TABLE t1 (a BLOB) 47PARTITION BY RANGE COLUMNS (a) 48(PARTITION p0 VALUES LESS THAN ("X")); 49ERROR HY000: A BLOB field is not allowed in partition function 50CREATE TABLE t1 (a TEXT) 51PARTITION BY RANGE COLUMNS (a) 52(PARTITION p0 VALUES LESS THAN ("X")); 53ERROR HY000: A BLOB field is not allowed in partition function 54CREATE TABLE t1 (a FLOAT) 55PARTITION BY RANGE COLUMNS (a) 56(PARTITION p0 VALUES LESS THAN (0.0)); 57ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning 58CREATE TABLE t1 (a DOUBLE) 59PARTITION BY RANGE COLUMNS (a) 60(PARTITION p0 VALUES LESS THAN (0.0)); 61ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning 62CREATE TABLE t1 (d TIMESTAMP) 63PARTITION BY RANGE COLUMNS(d) 64(PARTITION p0 VALUES LESS THAN ('2000-01-01'), 65PARTITION p1 VALUES LESS THAN ('2040-01-01')); 66ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning 67CREATE TABLE t1 (d BIT(1)) 68PARTITION BY RANGE COLUMNS(d) 69(PARTITION p0 VALUES LESS THAN (0), 70PARTITION p1 VALUES LESS THAN (1)); 71ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning 72CREATE TABLE t1 (d ENUM("YES","NO")) 73PARTITION BY RANGE COLUMNS(d) 74(PARTITION p0 VALUES LESS THAN ("NO"), 75PARTITION p1 VALUES LESS THAN (MAXVALUE)); 76ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning 77CREATE TABLE t1 (d SET("Car","MC")) 78PARTITION BY RANGE COLUMNS(d) 79(PARTITION p0 VALUES LESS THAN ("MC"), 80PARTITION p1 VALUES LESS THAN (MAXVALUE)); 81ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning 82create table t1 (a int, b int) 83partition by range columns (a,b) 84( partition p0 values less than (maxvalue, 10), 85partition p1 values less than (maxvalue, maxvalue)); 86ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 87create table t1 (a int, b int, c int) 88partition by range columns (a,b,c) 89( partition p0 values less than (1, maxvalue, 10), 90partition p1 values less than (1, maxvalue, maxvalue)); 91ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition 92create table t1 (a varchar(5) character set ucs2 collate ucs2_bin) 93partition by range columns (a) 94(partition p0 values less than (0x0041)); 95insert into t1 values (0x00410000); 96select hex(a) from t1 where a like 'A_'; 97hex(a) 9800410000 99explain partitions select hex(a) from t1 where a like 'A_'; 100id select_type table partitions type possible_keys key key_len ref rows Extra 1011 SIMPLE t1 p0 system NULL NULL NULL NULL 1 102alter table t1 remove partitioning; 103select hex(a) from t1 where a like 'A_'; 104hex(a) 10500410000 106create index a on t1 (a); 107select hex(a) from t1 where a like 'A_'; 108hex(a) 10900410000 110insert into t1 values ('A_'); 111select hex(a) from t1; 112hex(a) 11300410000 1140041005F 115drop table t1; 116create table t1 (a varchar(1) character set latin1 collate latin1_general_ci) 117partition by range columns(a) 118( partition p0 values less than ('a'), 119partition p1 values less than ('b'), 120partition p2 values less than ('c'), 121partition p3 values less than ('d')); 122insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c'); 123select * from t1 where a > 'B' collate latin1_bin; 124a 125a 126b 127C 128c 129select * from t1 where a <> 'B' collate latin1_bin; 130a 131A 132a 133b 134C 135c 136alter table t1 remove partitioning; 137select * from t1 where a > 'B' collate latin1_bin; 138a 139a 140b 141C 142c 143select * from t1 where a <> 'B' collate latin1_bin; 144a 145A 146a 147b 148C 149c 150drop table t1; 151create table t1 (a varchar(2) character set latin1, 152b varchar(2) character set latin1) 153partition by list columns(a,b) 154(partition p0 values in (('a','a'))); 155insert into t1 values ('A','A'); 156select * from t1 where b <> 'a' collate latin1_bin AND 157a = 'A' collate latin1_bin; 158a b 159A A 160alter table t1 remove partitioning; 161select * from t1 where b <> 'a' collate latin1_bin AND 162a = 'A' collate latin1_bin; 163a b 164A A 165drop table t1; 166create table t1 (a varchar(5)) 167partition by list columns(a) 168( partition p0 values in ('\''), 169 partition p1 values in ('\\'), 170 partition p2 values in ('\0')); 171show create table t1; 172Table Create Table 173t1 CREATE TABLE `t1` ( 174 `a` varchar(5) DEFAULT NULL 175) ENGINE=MyISAM DEFAULT CHARSET=latin1 176 PARTITION BY LIST COLUMNS(`a`) 177(PARTITION `p0` VALUES IN ('''') ENGINE = MyISAM, 178 PARTITION `p1` VALUES IN ('\\') ENGINE = MyISAM, 179 PARTITION `p2` VALUES IN ('\0') ENGINE = MyISAM) 180drop table t1; 181set @@sql_mode=allow_invalid_dates; 182create table t1 (a char, b char, c date) 183partition by range columns (a,b,c) 184( partition p0 values less than (0,0,to_days('3000-11-31'))); 185ERROR HY000: Partition column values of incorrect type 186create table t1 (a char, b char, c date) 187partition by range columns (a,b,c) 188( partition p0 values less than (0,0,'3000-11-31')); 189ERROR HY000: Partition column values of incorrect type 190set @@sql_mode=''; 191create table t1 (a int, b char(10), c varchar(25), d datetime) 192partition by range columns(a,b,c,d) 193subpartition by hash (to_seconds(d)) 194subpartitions 4 195( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'), 196partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), 197partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE), 198partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); 199ERROR HY000: Partition column values of incorrect type 200create table t1 (a int, b char(10), c varchar(25), d datetime) 201partition by range columns(a,b,c,d) 202subpartition by hash (to_seconds(d)) 203subpartitions 4 204( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'), 205partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), 206partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE), 207partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); 208select partition_method, partition_expression, partition_description 209from information_schema.partitions where table_name = "t1"; 210partition_method partition_expression partition_description 211RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01' 212RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01' 213RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01' 214RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01' 215RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01' 216RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01' 217RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01' 218RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01' 219RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE 220RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE 221RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE 222RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE 223RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE 224RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE 225RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE 226RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE 227show create table t1; 228Table Create Table 229t1 CREATE TABLE `t1` ( 230 `a` int(11) DEFAULT NULL, 231 `b` char(10) DEFAULT NULL, 232 `c` varchar(25) DEFAULT NULL, 233 `d` datetime DEFAULT NULL 234) ENGINE=MyISAM DEFAULT CHARSET=latin1 235 PARTITION BY RANGE COLUMNS(`a`,`b`,`c`,`d`) 236SUBPARTITION BY HASH (to_seconds(`d`)) 237SUBPARTITIONS 4 238(PARTITION `p0` VALUES LESS THAN (1,'0',MAXVALUE,'1900-01-01') ENGINE = MyISAM, 239 PARTITION `p1` VALUES LESS THAN (1,'a',MAXVALUE,'1999-01-01') ENGINE = MyISAM, 240 PARTITION `p2` VALUES LESS THAN (1,'b',MAXVALUE,MAXVALUE) ENGINE = MyISAM, 241 PARTITION `p3` VALUES LESS THAN (1,MAXVALUE,MAXVALUE,MAXVALUE) ENGINE = MyISAM) 242drop table t1; 243create table t1 (a int, b int) 244partition by range columns (a,b) 245(partition p0 values less than (NULL, maxvalue)); 246ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN 247create table t1 (a int, b int) 248partition by list columns(a,b) 249( partition p0 values in ((maxvalue, 0))); 250Got one of the listed errors 251create table t1 (a int, b int) 252partition by list columns (a,b) 253( partition p0 values in ((0,0))); 254alter table t1 add partition 255(partition p1 values in (maxvalue, maxvalue)); 256Got one of the listed errors 257drop table t1; 258create table t1 (a int, b int) 259partition by key (a,a); 260ERROR HY000: Duplicate partition field name 'a' 261create table t1 (a int, b int) 262partition by list columns(a,a) 263( partition p values in ((1,1))); 264ERROR HY000: Duplicate partition field name 'a' 265create table t1 (a int signed) 266partition by list (a) 267( partition p0 values in (1, 3, 5, 7, 9, NULL), 268partition p1 values in (2, 4, 6, 8, 0)); 269insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); 270select * from t1 where NULL <= a; 271a 272select * from t1 where a is null; 273a 274NULL 275explain partitions select * from t1 where a is null; 276id select_type table partitions type possible_keys key key_len ref rows Extra 2771 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 278select * from t1 where a <= 1; 279a 2801 2810 282drop table t1; 283create table t1 (a int signed) 284partition by list columns(a) 285( partition p0 values in (1, 3, 5, 7, 9, NULL), 286partition p1 values in (2, 4, 6, 8, 0)); 287insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); 288select * from t1 where a <= NULL; 289a 290select * from t1 where a is null; 291a 292NULL 293explain partitions select * from t1 where a is null; 294id select_type table partitions type possible_keys key key_len ref rows Extra 2951 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where 296select * from t1 where a <= 1; 297a 2981 2990 300drop table t1; 301create table t1 (a int, b int) 302partition by list columns(a,b) 303( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)), 304partition p1 values in ((1,1), (2,2)), 305partition p2 values in ((3, NULL), (NULL, 1))); 306select partition_method, partition_expression, partition_description 307from information_schema.partitions where table_name = "t1"; 308partition_method partition_expression partition_description 309LIST COLUMNS `a`,`b` (1,NULL),(2,NULL),(NULL,NULL) 310LIST COLUMNS `a`,`b` (1,1),(2,2) 311LIST COLUMNS `a`,`b` (3,NULL),(NULL,1) 312show create table t1; 313Table Create Table 314t1 CREATE TABLE `t1` ( 315 `a` int(11) DEFAULT NULL, 316 `b` int(11) DEFAULT NULL 317) ENGINE=MyISAM DEFAULT CHARSET=latin1 318 PARTITION BY LIST COLUMNS(`a`,`b`) 319(PARTITION `p0` VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM, 320 PARTITION `p1` VALUES IN ((1,1),(2,2)) ENGINE = MyISAM, 321 PARTITION `p2` VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM) 322insert into t1 values (3, NULL); 323insert into t1 values (NULL, 1); 324insert into t1 values (NULL, NULL); 325insert into t1 values (1, NULL); 326insert into t1 values (2, NULL); 327insert into t1 values (1,1); 328insert into t1 values (2,2); 329select * from t1 where a = 1; 330a b 3311 NULL 3321 1 333select * from t1 where a = 2; 334a b 3352 NULL 3362 2 337select * from t1 where a > 8; 338a b 339select * from t1 where a not between 8 and 8; 340a b 3411 NULL 3422 NULL 3431 1 3442 2 3453 NULL 346show create table t1; 347Table Create Table 348t1 CREATE TABLE `t1` ( 349 `a` int(11) DEFAULT NULL, 350 `b` int(11) DEFAULT NULL 351) ENGINE=MyISAM DEFAULT CHARSET=latin1 352 PARTITION BY LIST COLUMNS(`a`,`b`) 353(PARTITION `p0` VALUES IN ((1,NULL),(2,NULL),(NULL,NULL)) ENGINE = MyISAM, 354 PARTITION `p1` VALUES IN ((1,1),(2,2)) ENGINE = MyISAM, 355 PARTITION `p2` VALUES IN ((3,NULL),(NULL,1)) ENGINE = MyISAM) 356drop table t1; 357create table t1 (a int) 358partition by list (a) 359( partition p0 values in (1), 360partition p1 values in (1)); 361ERROR HY000: Multiple definition of same constant in list partitioning 362create table t1 (a int) 363partition by list (a) 364( partition p0 values in (2, 1), 365partition p1 values in (4, NULL, 3)); 366select partition_method, partition_expression, partition_description 367from information_schema.partitions where table_name = "t1"; 368partition_method partition_expression partition_description 369LIST `a` 2,1 370LIST `a` NULL,4,3 371show create table t1; 372Table Create Table 373t1 CREATE TABLE `t1` ( 374 `a` int(11) DEFAULT NULL 375) ENGINE=MyISAM DEFAULT CHARSET=latin1 376 PARTITION BY LIST (`a`) 377(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM, 378 PARTITION `p1` VALUES IN (NULL,4,3) ENGINE = MyISAM) 379insert into t1 values (1); 380insert into t1 values (2); 381insert into t1 values (3); 382insert into t1 values (4); 383insert into t1 values (NULL); 384insert into t1 values (5); 385ERROR HY000: Table has no partition for value 5 386drop table t1; 387create table t1 (a int) 388partition by list columns(a) 389( partition p0 values in (2, 1), 390partition p1 values in ((4), (NULL), (3))); 391ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 4 392create table t1 (a int) 393partition by list columns(a) 394( partition p0 values in (2, 1), 395partition p1 values in (4, NULL, 3)); 396select partition_method, partition_expression, partition_description 397from information_schema.partitions where table_name = "t1"; 398partition_method partition_expression partition_description 399LIST COLUMNS `a` 2,1 400LIST COLUMNS `a` 4,NULL,3 401show create table t1; 402Table Create Table 403t1 CREATE TABLE `t1` ( 404 `a` int(11) DEFAULT NULL 405) ENGINE=MyISAM DEFAULT CHARSET=latin1 406 PARTITION BY LIST COLUMNS(`a`) 407(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM, 408 PARTITION `p1` VALUES IN (4,NULL,3) ENGINE = MyISAM) 409insert into t1 values (1); 410insert into t1 values (2); 411insert into t1 values (3); 412insert into t1 values (4); 413insert into t1 values (NULL); 414insert into t1 values (5); 415ERROR HY000: Table has no partition for value from column_list 416show create table t1; 417Table Create Table 418t1 CREATE TABLE `t1` ( 419 `a` int(11) DEFAULT NULL 420) ENGINE=MyISAM DEFAULT CHARSET=latin1 421 PARTITION BY LIST COLUMNS(`a`) 422(PARTITION `p0` VALUES IN (2,1) ENGINE = MyISAM, 423 PARTITION `p1` VALUES IN (4,NULL,3) ENGINE = MyISAM) 424drop table t1; 425create table t1 (a int, b char(10), c varchar(5), d int) 426partition by range columns(a,b,c) 427subpartition by key (c,d) 428subpartitions 3 429( partition p0 values less than (1,'abc','abc'), 430partition p1 values less than (2,'abc','abc'), 431partition p2 values less than (3,'abc','abc'), 432partition p3 values less than (4,'abc','abc')); 433select partition_method, partition_expression, partition_description 434from information_schema.partitions where table_name = "t1"; 435partition_method partition_expression partition_description 436RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc' 437RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc' 438RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc' 439RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc' 440RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc' 441RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc' 442RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc' 443RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc' 444RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc' 445RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc' 446RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc' 447RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc' 448show create table t1; 449Table Create Table 450t1 CREATE TABLE `t1` ( 451 `a` int(11) DEFAULT NULL, 452 `b` char(10) DEFAULT NULL, 453 `c` varchar(5) DEFAULT NULL, 454 `d` int(11) DEFAULT NULL 455) ENGINE=MyISAM DEFAULT CHARSET=latin1 456 PARTITION BY RANGE COLUMNS(`a`,`b`,`c`) 457SUBPARTITION BY KEY (`c`,`d`) 458SUBPARTITIONS 3 459(PARTITION `p0` VALUES LESS THAN (1,'abc','abc') ENGINE = MyISAM, 460 PARTITION `p1` VALUES LESS THAN (2,'abc','abc') ENGINE = MyISAM, 461 PARTITION `p2` VALUES LESS THAN (3,'abc','abc') ENGINE = MyISAM, 462 PARTITION `p3` VALUES LESS THAN (4,'abc','abc') ENGINE = MyISAM) 463insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); 464insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3); 465insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3); 466insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3); 467select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR 468(a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); 469a b c d 4701 a b 1 4711 b c 1 472drop table t1; 473create table t1 (a int, b varchar(2), c int) 474partition by range columns (a, b, c) 475(partition p0 values less than (1, 'A', 1), 476partition p1 values less than (1, 'B', 1)); 477select partition_method, partition_expression, partition_description 478from information_schema.partitions where table_name = "t1"; 479partition_method partition_expression partition_description 480RANGE COLUMNS `a`,`b`,`c` 1,'A',1 481RANGE COLUMNS `a`,`b`,`c` 1,'B',1 482show create table t1; 483Table Create Table 484t1 CREATE TABLE `t1` ( 485 `a` int(11) DEFAULT NULL, 486 `b` varchar(2) DEFAULT NULL, 487 `c` int(11) DEFAULT NULL 488) ENGINE=MyISAM DEFAULT CHARSET=latin1 489 PARTITION BY RANGE COLUMNS(`a`,`b`,`c`) 490(PARTITION `p0` VALUES LESS THAN (1,'A',1) ENGINE = MyISAM, 491 PARTITION `p1` VALUES LESS THAN (1,'B',1) ENGINE = MyISAM) 492insert into t1 values (1, 'A', 1); 493explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; 494id select_type table partitions type possible_keys key key_len ref rows Extra 4951 SIMPLE t1 p0,p1 system NULL NULL NULL NULL 1 496select * from t1 where a = 1 AND b <= 'A' and c = 1; 497a b c 4981 A 1 499drop table t1; 500create table t1 (a char, b char, c char) 501partition by list columns(a) 502( partition p0 values in ('a')); 503insert into t1 (a) values ('a'); 504select * from t1 where a = 'a'; 505a b c 506a NULL NULL 507drop table t1; 508create table t1 (d time) 509partition by range columns(d) 510( partition p0 values less than ('2000-01-01'), 511partition p1 values less than ('2040-01-01')); 512ERROR HY000: Partition column values of incorrect type 513create table t1 (a int, b int) 514partition by range columns(a,b) 515(partition p0 values less than (maxvalue, 10)); 516drop table t1; 517create table t1 (d date) 518partition by range columns(d) 519( partition p0 values less than ('2000-01-01'), 520partition p1 values less than ('2009-01-01')); 521drop table t1; 522create table t1 (d date) 523partition by range columns(d) 524( partition p0 values less than ('1999-01-01'), 525partition p1 values less than ('2000-01-01')); 526drop table t1; 527create table t1 (d date) 528partition by range columns(d) 529( partition p0 values less than ('2000-01-01'), 530partition p1 values less than ('3000-01-01')); 531drop table t1; 532create table t1 (a int, b int) 533partition by range columns(a,b) 534(partition p2 values less than (99,99), 535partition p1 values less than (99,999)); 536insert into t1 values (99,998); 537select * from t1 where b = 998; 538a b 53999 998 540drop table t1; 541create table t1 as select to_seconds(null) as to_seconds; 542select data_type from information_schema.columns 543where table_schema='test' and column_name='to_seconds'; 544data_type 545bigint 546drop table t1; 547create table t1 (a int, b int) 548partition by list columns(a,b) 549(partition p0 values in ((maxvalue,maxvalue))); 550ERROR 42000: Cannot use MAXVALUE as value in VALUES IN near 'maxvalue,maxvalue)))' at line 3 551create table t1 (a int, b int) 552partition by range columns(a,b) 553(partition p0 values less than (maxvalue,maxvalue)); 554drop table t1; 555create table t1 (a int) 556partition by list columns(a) 557(partition p0 values in (0)); 558select partition_method from information_schema.partitions where table_name='t1'; 559partition_method 560LIST COLUMNS 561drop table t1; 562create table t1 (a char(6)) 563partition by range columns(a) 564(partition p0 values less than ('H23456'), 565partition p1 values less than ('M23456')); 566insert into t1 values ('F23456'); 567select * from t1; 568a 569F23456 570drop table t1; 571create table t1 (a char(6)) 572partition by range columns(a) 573(partition p0 values less than (H23456), 574partition p1 values less than (M23456)); 575ERROR 42S22: Unknown column 'H23456' in 'field list' 576create table t1 (a char(6)) 577partition by range columns(a) 578(partition p0 values less than (23456), 579partition p1 values less than (23456)); 580ERROR HY000: Partition column values of incorrect type 581create table t1 (a int, b int) 582partition by range columns(a,b) 583(partition p0 values less than (10)); 584ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3 585create table t1 (a int, b int) 586partition by range columns(a,b) 587(partition p0 values less than (1,1,1); 588ERROR HY000: Inconsistency in usage of column lists for partitioning 589create table t1 (a int, b int) 590partition by range columns(a,b) 591(partition p0 values less than (1, 0), 592partition p1 values less than (2, maxvalue), 593partition p2 values less than (3, 3), 594partition p3 values less than (10, maxvalue)); 595insert into t1 values (11,0); 596ERROR HY000: Table has no partition for value from column_list 597insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); 598select * from t1; 599a b 6000 1 6011 1 6022 1 6033 1 6043 4 6054 9 6069 1 607alter table t1 608partition by range columns(b,a) 609(partition p0 values less than (1,2), 610partition p1 values less than (3,3), 611partition p2 values less than (9,5)); 612explain partitions select * from t1 where b < 2; 613id select_type table partitions type possible_keys key key_len ref rows Extra 6141 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where 615select * from t1 where b < 2; 616a b 6170 1 6181 1 6192 1 6203 1 6219 1 622explain partitions select * from t1 where b < 4; 623id select_type table partitions type possible_keys key key_len ref rows Extra 6241 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where 625select * from t1 where b < 4; 626a b 6270 1 6281 1 6292 1 6303 1 6319 1 632alter table t1 reorganize partition p1 into 633(partition p11 values less than (2,2), 634partition p12 values less than (3,3)); 635alter table t1 reorganize partition p0 into 636(partition p01 values less than (0,3), 637partition p02 values less than (1,1)); 638ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range 639alter table t1 reorganize partition p2 into 640(partition p2 values less than(9,6,1)); 641ERROR HY000: Inconsistency in usage of column lists for partitioning 642alter table t1 reorganize partition p2 into 643(partition p2 values less than (10)); 644ERROR HY000: Inconsistency in usage of column lists for partitioning 645alter table t1 reorganize partition p2 into 646(partition p21 values less than (4,7), 647partition p22 values less than (9,5)); 648explain partitions select * from t1 where b < 4; 649id select_type table partitions type possible_keys key key_len ref rows Extra 6501 SIMPLE t1 p0,p11,p12,p21 ALL NULL NULL NULL NULL 6 Using where 651select * from t1 where b < 4; 652a b 6530 1 6541 1 6552 1 6563 1 6579 1 658drop table t1; 659create table t1 (a int, b int) 660partition by list columns(a,b) 661subpartition by hash (b) 662subpartitions 2 663(partition p0 values in ((0,0), (1,1)), 664partition p1 values in ((1000,1000))); 665insert into t1 values (1000,1000); 666drop table t1; 667create table t1 (a char, b char, c char) 668partition by range columns(a,b,c) 669( partition p0 values less than ('a','b','c')); 670alter table t1 add partition 671(partition p1 values less than ('b','c','d')); 672drop table t1; 673