1# 2# Tests for the new column list partitioning introduced in second 3# version for partitioning. 4# 5--source include/have_partition.inc 6 7--disable_warnings 8drop table if exists t1; 9--enable_warnings 10 11--echo # 12--echo # Bug#52815: LIST COLUMNS doesn't insert rows in correct partition 13--echo # if muliple columns used 14CREATE TABLE t1 ( 15id INT NOT NULL, 16name VARCHAR(255), 17department VARCHAR(10), 18country VARCHAR(255) 19) PARTITION BY LIST COLUMNS (department, country) ( 20PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')), 21PARTITION second_office VALUES IN (('dep2', 'Russia')) 22); 23 24INSERT INTO t1 VALUES(1, 'Ann', 'dep1', 'Russia'); 25INSERT INTO t1 VALUES(2, 'Bob', 'dep1', 'Croatia'); 26INSERT INTO t1 VALUES(3, 'Cecil', 'dep2', 'Russia'); 27--error ER_NO_PARTITION_FOR_GIVEN_VALUE 28INSERT INTO t1 VALUES(3, 'Dan', 'dep2', 'Croatia'); 29SELECT PARTITION_NAME,TABLE_ROWS 30FROM INFORMATION_SCHEMA.PARTITIONS 31WHERE TABLE_NAME = 't1'; 32SHOW CREATE TABLE t1; 33SELECT * FROM t1 WHERE department = 'dep2' and country = 'Croatia'; 34SELECT * FROM t1 WHERE department = 'dep1' and country = 'Croatia'; 35DROP TABLE t1; 36 37# 38# Bug#51347: assertion with show create table + partition by columns 39# on decimal column 40# 41--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 42CREATE TABLE t1 (a DECIMAL) 43PARTITION BY RANGE COLUMNS (a) 44(PARTITION p0 VALUES LESS THAN (0)); 45 46--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR 47CREATE TABLE t1 (a BLOB) 48PARTITION BY RANGE COLUMNS (a) 49(PARTITION p0 VALUES LESS THAN ("X")); 50 51--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR 52CREATE TABLE t1 (a TEXT) 53PARTITION BY RANGE COLUMNS (a) 54(PARTITION p0 VALUES LESS THAN ("X")); 55 56--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 57CREATE TABLE t1 (a FLOAT) 58PARTITION BY RANGE COLUMNS (a) 59(PARTITION p0 VALUES LESS THAN (0.0)); 60 61--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 62CREATE TABLE t1 (a DOUBLE) 63PARTITION BY RANGE COLUMNS (a) 64(PARTITION p0 VALUES LESS THAN (0.0)); 65 66--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 67CREATE TABLE t1 (d TIMESTAMP) 68PARTITION BY RANGE COLUMNS(d) 69(PARTITION p0 VALUES LESS THAN ('2000-01-01'), 70 PARTITION p1 VALUES LESS THAN ('2040-01-01')); 71 72--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 73CREATE TABLE t1 (d BIT(1)) 74PARTITION BY RANGE COLUMNS(d) 75(PARTITION p0 VALUES LESS THAN (0), 76 PARTITION p1 VALUES LESS THAN (1)); 77 78--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 79CREATE TABLE t1 (d ENUM("YES","NO")) 80PARTITION BY RANGE COLUMNS(d) 81(PARTITION p0 VALUES LESS THAN ("NO"), 82 PARTITION p1 VALUES LESS THAN (MAXVALUE)); 83 84--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD 85CREATE TABLE t1 (d SET("Car","MC")) 86PARTITION BY RANGE COLUMNS(d) 87(PARTITION p0 VALUES LESS THAN ("MC"), 88 PARTITION p1 VALUES LESS THAN (MAXVALUE)); 89 90# 91# BUG#49180, Possible to define empty intervals for column list partitioning 92# 93--error ER_RANGE_NOT_INCREASING_ERROR 94create table t1 (a int, b int) 95partition by range columns (a,b) 96( partition p0 values less than (maxvalue, 10), 97 partition p1 values less than (maxvalue, maxvalue)); 98 99--error ER_RANGE_NOT_INCREASING_ERROR 100create table t1 (a int, b int, c int) 101partition by range columns (a,b,c) 102( partition p0 values less than (1, maxvalue, 10), 103 partition p1 values less than (1, maxvalue, maxvalue)); 104 105# 106# BUG#48737, Search fails with ucs2 107# 108create table t1 (a varchar(5) character set ucs2 collate ucs2_bin) 109partition by range columns (a) 110(partition p0 values less than (0x0041)); 111insert into t1 values (0x00410000); 112select hex(a) from t1 where a like 'A_'; 113analyze table t1; 114explain partitions select hex(a) from t1 where a like 'A_'; 115alter table t1 remove partitioning; 116select hex(a) from t1 where a like 'A_'; 117create index a on t1 (a); 118select hex(a) from t1 where a like 'A_'; 119insert into t1 values ('A_'); 120select hex(a) from t1; 121drop table t1; 122 123# 124# BUG#48161, Delivering too few records using collate syntax with partitions 125# 126# Test case from BUG#48447 with some extension 127create table t1 (a varchar(1) character set latin1 collate latin1_general_ci) 128partition by range columns(a) 129( partition p0 values less than ('a'), 130 partition p1 values less than ('b'), 131 partition p2 values less than ('c'), 132 partition p3 values less than ('d')); 133insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c'); 134select * from t1 where a > 'B' collate latin1_bin; 135select * from t1 where a <> 'B' collate latin1_bin; 136alter table t1 remove partitioning; 137select * from t1 where a > 'B' collate latin1_bin; 138select * from t1 where a <> 'B' collate latin1_bin; 139drop table t1; 140 141# Test case from BUG#48161 142create table t1 (a varchar(2) character set latin1, 143 b varchar(2) character set latin1) 144partition by list columns(a,b) 145(partition p0 values in (('a','a'))); 146insert into t1 values ('A','A'); 147select * from t1 where b <> 'a' collate latin1_bin AND 148 a = 'A' collate latin1_bin; 149alter table t1 remove partitioning; 150select * from t1 where b <> 'a' collate latin1_bin AND 151 a = 'A' collate latin1_bin; 152drop table t1; 153 154create table t1 (a varchar(5)) 155partition by list columns(a) 156( partition p0 values in ('\''), 157 partition p1 values in ('\\'), 158 partition p2 values in ('\0')); 159show create table t1; 160drop table t1; 161 162# 163# BUG#48165, sql_mode gives error 164# 165set @@sql_mode=allow_invalid_dates; 166--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 167create table t1 (a char, b char, c date) 168partition by range columns (a,b,c) 169( partition p0 values less than (0,0,to_days('3000-11-31'))); 170 171--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 172create table t1 (a char, b char, c date) 173partition by range columns (a,b,c) 174( partition p0 values less than (0,0,'3000-11-31')); 175set @@sql_mode=''; 176 177--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 178create table t1 (a int, b char(10), c varchar(25), d datetime) 179partition by range columns(a,b,c,d) 180subpartition by hash (to_seconds(d)) 181subpartitions 4 182( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'), 183 partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), 184 partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE), 185 partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); 186 187create table t1 (a int, b char(10), c varchar(25), d datetime) 188partition by range columns(a,b,c,d) 189subpartition by hash (to_seconds(d)) 190subpartitions 4 191( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'), 192 partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'), 193 partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE), 194 partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE)); 195select partition_method, partition_expression, partition_description 196 from information_schema.partitions where table_name = "t1"; 197show create table t1; 198drop table t1; 199 200--error ER_NULL_IN_VALUES_LESS_THAN 201create table t1 (a int, b int) 202partition by range columns (a,b) 203(partition p0 values less than (NULL, maxvalue)); 204 205--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR 206create table t1 (a int, b int) 207partition by list columns(a,b) 208( partition p0 values in ((maxvalue, 0))); 209 210create table t1 (a int, b int) 211partition by list columns (a,b) 212( partition p0 values in ((0,0))); 213--error ER_MAXVALUE_IN_VALUES_IN, ER_PARSE_ERROR 214alter table t1 add partition 215(partition p1 values in (maxvalue, maxvalue)); 216drop table t1; 217# 218# BUG#47837, Crash when two same fields in column list processing 219# 220--error ER_SAME_NAME_PARTITION_FIELD 221create table t1 (a int, b int) 222partition by key (a,a); 223--error ER_SAME_NAME_PARTITION_FIELD 224create table t1 (a int, b int) 225partition by list columns(a,a) 226( partition p values in ((1,1))); 227 228# 229# BUG#47838, List partitioning have problems with <= and >= 230# 231create table t1 (a int signed) 232partition by list (a) 233( partition p0 values in (1, 3, 5, 7, 9, NULL), 234 partition p1 values in (2, 4, 6, 8, 0)); 235insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); 236select * from t1 where NULL <= a; 237select * from t1 where a is null; 238analyze table t1; 239explain partitions select * from t1 where a is null; 240select * from t1 where a <= 1; 241drop table t1; 242 243create table t1 (a int signed) 244partition by list columns(a) 245( partition p0 values in (1, 3, 5, 7, 9, NULL), 246 partition p1 values in (2, 4, 6, 8, 0)); 247insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8); 248select * from t1 where a <= NULL; 249select * from t1 where a is null; 250analyze table t1; 251explain partitions select * from t1 where a is null; 252select * from t1 where a <= 1; 253drop table t1; 254 255create table t1 (a int, b int) 256partition by list columns(a,b) 257( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)), 258 partition p1 values in ((1,1), (2,2)), 259 partition p2 values in ((3, NULL), (NULL, 1))); 260select partition_method, partition_expression, partition_description 261 from information_schema.partitions where table_name = "t1"; 262show create table t1; 263# 264# BUG#47754 Crash when selecting using NOT BETWEEN for column list partitioning 265# 266insert into t1 values (3, NULL); 267insert into t1 values (NULL, 1); 268insert into t1 values (NULL, NULL); 269insert into t1 values (1, NULL); 270insert into t1 values (2, NULL); 271insert into t1 values (1,1); 272insert into t1 values (2,2); 273select * from t1 where a = 1; 274select * from t1 where a = 2; 275select * from t1 where a > 8; 276select * from t1 where a not between 8 and 8; 277show create table t1; 278drop table t1; 279 280--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR 281create table t1 (a int) 282partition by list (a) 283( partition p0 values in (1), 284 partition p1 values in (1)); 285 286create table t1 (a int) 287partition by list (a) 288( partition p0 values in (2, 1), 289 partition p1 values in (4, NULL, 3)); 290select partition_method, partition_expression, partition_description 291 from information_schema.partitions where table_name = "t1"; 292show create table t1; 293insert into t1 values (1); 294insert into t1 values (2); 295insert into t1 values (3); 296insert into t1 values (4); 297insert into t1 values (NULL); 298--error ER_NO_PARTITION_FOR_GIVEN_VALUE 299insert into t1 values (5); 300drop table t1; 301 302--error ER_PARSE_ERROR 303create table t1 (a int) 304partition by list columns(a) 305( partition p0 values in (2, 1), 306 partition p1 values in ((4), (NULL), (3))); 307 308create table t1 (a int) 309partition by list columns(a) 310( partition p0 values in (2, 1), 311 partition p1 values in (4, NULL, 3)); 312select partition_method, partition_expression, partition_description 313 from information_schema.partitions where table_name = "t1"; 314show create table t1; 315insert into t1 values (1); 316insert into t1 values (2); 317insert into t1 values (3); 318insert into t1 values (4); 319insert into t1 values (NULL); 320--error ER_NO_PARTITION_FOR_GIVEN_VALUE 321insert into t1 values (5); 322show create table t1; 323drop table t1; 324 325create table t1 (a int, b char(10), c varchar(5), d int) 326partition by range columns(a,b,c) 327subpartition by key (c,d) 328subpartitions 3 329( partition p0 values less than (1,'abc','abc'), 330 partition p1 values less than (2,'abc','abc'), 331 partition p2 values less than (3,'abc','abc'), 332 partition p3 values less than (4,'abc','abc')); 333select partition_method, partition_expression, partition_description 334 from information_schema.partitions where table_name = "t1"; 335show create table t1; 336 337insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3); 338insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3); 339insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3); 340insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3); 341select * from t1 where (a = 1 AND b < 'd' AND (c = 'b' OR (c = 'c' AND d = 1)) OR 342 (a = 1 AND b >= 'a' AND (c = 'c' OR (c = 'd' AND d = 2)))); 343drop table t1; 344 345create table t1 (a int, b varchar(2), c int) 346partition by range columns (a, b, c) 347(partition p0 values less than (1, 'A', 1), 348 partition p1 values less than (1, 'B', 1)); 349select partition_method, partition_expression, partition_description 350 from information_schema.partitions where table_name = "t1"; 351show create table t1; 352insert into t1 values (1, 'A', 1); 353analyze table t1; 354explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1; 355select * from t1 where a = 1 AND b <= 'A' and c = 1; 356drop table t1; 357 358create table t1 (a char, b char, c char) 359partition by list columns(a) 360( partition p0 values in ('a')); 361insert into t1 (a) values ('a'); 362select * from t1 where a = 'a'; 363drop table t1; 364 365--error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 366create table t1 (d time) 367partition by range columns(d) 368( partition p0 values less than ('2000-01-01'), 369 partition p1 values less than ('2040-01-01')); 370 371create table t1 (a int, b int) 372partition by range columns(a,b) 373(partition p0 values less than (maxvalue, 10)); 374drop table t1; 375 376create table t1 (d date) 377partition by range columns(d) 378( partition p0 values less than ('2000-01-01'), 379 partition p1 values less than ('2009-01-01')); 380drop table t1; 381 382create table t1 (d date) 383partition by range columns(d) 384( partition p0 values less than ('1999-01-01'), 385 partition p1 values less than ('2000-01-01')); 386drop table t1; 387 388create table t1 (d date) 389partition by range columns(d) 390( partition p0 values less than ('2000-01-01'), 391 partition p1 values less than ('3000-01-01')); 392drop table t1; 393 394create table t1 (a int, b int) 395partition by range columns(a,b) 396(partition p2 values less than (99,99), 397 partition p1 values less than (99,999)); 398 399insert into t1 values (99,998); 400select * from t1 where b = 998; 401drop table t1; 402 403create table t1 as select to_seconds(null) as to_seconds; 404select data_type from information_schema.columns 405where column_name='to_seconds'; 406drop table t1; 407 408--error ER_PARSE_ERROR 409create table t1 (a int, b int) 410partition by list columns(a,b) 411(partition p0 values in ((maxvalue,maxvalue))); 412create table t1 (a int, b int) 413partition by range columns(a,b) 414(partition p0 values less than (maxvalue,maxvalue)); 415drop table t1; 416 417create table t1 (a int) 418partition by list columns(a) 419(partition p0 values in (0)); 420select partition_method from information_schema.partitions where table_name='t1'; 421drop table t1; 422 423create table t1 (a char(6)) 424partition by range columns(a) 425(partition p0 values less than ('H23456'), 426 partition p1 values less than ('M23456')); 427insert into t1 values ('F23456'); 428select * from t1; 429drop table t1; 430 431-- error 1054 432create table t1 (a char(6)) 433partition by range columns(a) 434(partition p0 values less than (H23456), 435 partition p1 values less than (M23456)); 436 437-- error ER_WRONG_TYPE_COLUMN_VALUE_ERROR 438create table t1 (a char(6)) 439partition by range columns(a) 440(partition p0 values less than (23456), 441 partition p1 values less than (23456)); 442 443-- error 1064 444create table t1 (a int, b int) 445partition by range columns(a,b) 446(partition p0 values less than (10)); 447 448-- error ER_PARTITION_COLUMN_LIST_ERROR 449create table t1 (a int, b int) 450partition by range columns(a,b) 451(partition p0 values less than (1,1,1); 452 453create table t1 (a int, b int) 454partition by range columns(a,b) 455(partition p0 values less than (1, 0), 456 partition p1 values less than (2, maxvalue), 457 partition p2 values less than (3, 3), 458 partition p3 values less than (10, maxvalue)); 459 460-- error ER_NO_PARTITION_FOR_GIVEN_VALUE 461insert into t1 values (11,0); 462insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1); 463select * from t1; 464 465alter table t1 466partition by range columns(b,a) 467(partition p0 values less than (1,2), 468 partition p1 values less than (3,3), 469 partition p2 values less than (9,5)); 470analyze table t1; 471explain partitions select * from t1 where b < 2; 472select * from t1 where b < 2; 473explain partitions select * from t1 where b < 4; 474select * from t1 where b < 4; 475 476alter table t1 reorganize partition p1 into 477(partition p11 values less than (2,2), 478 partition p12 values less than (3,3)); 479 480-- error ER_REORG_OUTSIDE_RANGE 481alter table t1 reorganize partition p0 into 482(partition p01 values less than (0,3), 483 partition p02 values less than (1,1)); 484 485-- error ER_PARTITION_COLUMN_LIST_ERROR 486alter table t1 reorganize partition p2 into 487(partition p2 values less than(9,6,1)); 488 489-- error ER_PARTITION_COLUMN_LIST_ERROR 490alter table t1 reorganize partition p2 into 491(partition p2 values less than (10)); 492 493alter table t1 reorganize partition p2 into 494(partition p21 values less than (4,7), 495 partition p22 values less than (9,5)); 496analyze table t1; 497explain partitions select * from t1 where b < 4; 498select * from t1 where b < 4; 499drop table t1; 500 501create table t1 (a int, b int) 502partition by list columns(a,b) 503subpartition by hash (b) 504subpartitions 2 505(partition p0 values in ((0,0), (1,1)), 506 partition p1 values in ((1000,1000))); 507insert into t1 values (1000,1000); 508#select * from t1 where a = 0 and b = 0; 509drop table t1; 510 511create table t1 (a char, b char, c char) 512partition by range columns(a,b,c) 513( partition p0 values less than ('a','b','c')); 514alter table t1 add partition 515(partition p1 values less than ('b','c','d')); 516drop table t1; 517