1 2--source include/have_partition.inc 3 4# 5# expression lists 6# 7create table t1 (a int, b int) 8 PARTITION BY LIST (a) 9 ( 10 PARTITION p2 VALUES IN (4,5,6), 11 PARTITION p1 VALUES IN (1) 12 ) 13; 14--error ER_NO_PARTITION_FOR_GIVEN_VALUE 15insert into t1 values (10,10); 16drop table t1; 17create table t1 (a int, b int) 18 PARTITION BY LIST (a) 19 ( 20 PARTITION p2 VALUES IN (4,5,6), 21 PARTITION p1 VALUES IN (1), 22 PARTITION p0 DEFAULT 23 ) 24; 25show create table t1; 26insert into t1 values (10,10); 27insert into t1 values (4,4); 28select * from t1; 29--sorted_result 30select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 31drop table t1; 32 33--error ER_PARTITION_DEFAULT_ERROR 34create table t1 (a int, b int) 35 PARTITION BY LIST (a) 36 ( 37 PARTITION p2 VALUES IN (4,5,6), 38 PARTITION p1 VALUES IN (1), 39 PARTITION p0 DEFAULT, 40 PARTITION p3 DEFAULT 41 ) 42; 43--error ER_PARTITION_DEFAULT_ERROR 44create table t1 (a int, b int) 45 PARTITION BY LIST (a) 46 ( 47 PARTITION p0 DEFAULT, 48 PARTITION p2 VALUES IN (4,5,6), 49 PARTITION p1 VALUES IN (1), 50 PARTITION p3 DEFAULT 51 ) 52; 53 54create table t1 (a int, b int) 55 PARTITION BY LIST (a) 56 ( 57 PARTITION p0 DEFAULT, 58 PARTITION p2 VALUES IN (4,5,6), 59 PARTITION p1 VALUES IN (1) 60 ) 61; 62show create table t1; 63insert into t1 values (10,10); 64select * from t1; 65--sorted_result 66select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 67drop table t1; 68 69# 70# Default has its value as 0 check that they are not clash. 71# 72create table t1 (a int, b int) 73 PARTITION BY LIST (a) 74 ( 75 PARTITION p0 DEFAULT, 76 PARTITION p2 VALUES IN (4,5,6), 77 PARTITION p1 VALUES IN (1, 0) 78 ) 79; 80show create table t1; 81insert into t1 values (10,10); 82select * from t1; 83--sorted_result 84select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 85drop table t1; 86 87# 88# columns lists 89# 90create table t1 (a int, b int) 91 PARTITION BY LIST COLUMNS(a,b) 92 ( 93 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 94 PARTITION p1 VALUES IN ((1,1),(0,0)), 95 PARTITION p0 DEFAULT 96 ) 97; 98show create table t1; 99insert into t1 values (10,10); 100select * from t1; 101--sorted_result 102select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 103drop table t1; 104 105--error ER_PARTITION_DEFAULT_ERROR 106create table t1 (a int, b int) 107 PARTITION BY LIST COLUMNS(a,b) 108 ( 109 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 110 PARTITION p1 VALUES IN ((1,1),(0,0)), 111 PARTITION p0 DEFAULT, 112 PARTITION p3 DEFAULT 113 ) 114; 115 116--error ER_PARTITION_DEFAULT_ERROR 117create table t1 (a int, b int) 118 PARTITION BY LIST COLUMNS(a,b) 119 ( 120 PARTITION p0 DEFAULT, 121 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 122 PARTITION p1 VALUES IN ((1,1),(0,0)), 123 PARTITION p3 DEFAULT 124 ) 125; 126 127# 128# partititon prunning test 129# 130 131create table t1 (a int, b int) 132 PARTITION BY LIST (a) 133 ( 134 PARTITION p2 VALUES IN (4,5,6), 135 PARTITION p1 VALUES IN (1,20), 136 PARTITION p0 default 137 ) 138; 139show create table t1; 140insert into t1 values (10,10); 141--sorted_result 142select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 143select * from t1 where a=10; 144select * from t1 where a<=10; 145select * from t1 where a<=20; 146select * from t1 where a>=10; 147select * from t1 where a>=5; 148insert into t1 values (20,20),(5,5); 149--sorted_result 150select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 151select * from t1 where a=10; 152select * from t1 where a<=10; 153select * from t1 where a<=20; 154select * from t1 where a>=10; 155select * from t1 where a>=5; 156explain partitions select * from t1 where a=10; 157explain partitions select * from t1 where a=5; 158select * from t1 where a=10 or a=5; 159explain partitions select * from t1 where a=10 or a=5; 160 161drop table t1; 162 163create table t1 (a int, b int) 164 PARTITION BY LIST COLUMNS(a,b) 165 ( 166 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6),(5,5)), 167 PARTITION p1 VALUES IN ((1,1),(20,20)), 168 PARTITION p0 DEFAULT 169 ) 170; 171show create table t1; 172insert into t1 values (10,10); 173--sorted_result 174select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 175select * from t1 where a=10 and b=10; 176explain partitions select * from t1 where a=10 and b=10; 177select * from t1 where a=10; 178explain partitions select * from t1 where a=10; 179select * from t1 where a<=10; 180select * from t1 where a>=10; 181insert into t1 values (20,20),(5,5); 182--sorted_result 183select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 184select * from t1 where a=10 and b=10; 185explain partitions select * from t1 where a=10 and b=10; 186select * from t1 where a=10 and b=10 or a=20 and b=20; 187explain partitions select * from t1 where a=10 and b=10 or a=20 and b=20; 188drop table t1; 189 190# 191# partition pruning with expressions 192# 193create table t1 (a int, b int); 194 195insert into t1 values (10,10),(2,5),(0,0); 196 197select * from t1; 198 199alter table t1 200 PARTITION BY LIST (a+b) 201 ( 202 PARTITION p2 VALUES IN (1,2,3,7), 203 PARTITION p1 VALUES IN (21,0), 204 PARTITION p0 DEFAULT 205 ) 206; 207show create table t1; 208select * from t1; 209explain partitions select * from t1 where a=2 and b=5; 210explain partitions select * from t1 where a=10 and b=10; 211drop table t1; 212 213create table t1 (a int, b int); 214 215insert into t1 values (10,10),(2,5),(0,0); 216 217select * from t1; 218 219alter table t1 220 PARTITION BY LIST (a+5) 221 ( 222 PARTITION p2 VALUES IN (1,2,3,7), 223 PARTITION p1 VALUES IN (0), 224 PARTITION p0 DEFAULT 225 ) 226; 227show create table t1; 228select * from t1; 229--sorted_result 230select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 231explain partitions select * from t1 where a>=2; 232explain partitions select * from t1 where a>=2 and a<=3; 233explain partitions select * from t1 where a=10; 234drop table t1; 235 236 237 238CREATE TABLE t1 (a DATE, KEY(a)) 239PARTITION BY LIST (TO_DAYS(a)) 240(PARTITION `pDEF` DEFAULT, 241 PARTITION `p2001-01-01` VALUES IN (TO_DAYS('2001-01-01')), 242 PARTITION `pNULL` VALUES IN (NULL), 243 PARTITION `p0000-01-02` VALUES IN (TO_DAYS('0000-01-02')), 244 PARTITION `p1001-01-01` VALUES IN (TO_DAYS('1001-01-01'))); 245if ($verify_without_partitions) 246{ 247ALTER TABLE t1 REMOVE PARTITIONING; 248} 249INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 250 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 251--source include/partition_date_range.inc 252--echo # test without index 253ALTER TABLE t1 DROP KEY a; 254--source include/partition_date_range.inc 255DROP TABLE t1; 256--echo # TO_SECONDS, test of LIST and index 257CREATE TABLE t1 (a DATE, KEY(a)) 258PARTITION BY LIST (TO_SECONDS(a)) 259(PARTITION `pDEF` DEFAULT, 260 PARTITION `p2001-01-01` VALUES IN (TO_SECONDS('2001-01-01')), 261 PARTITION `pNULL` VALUES IN (NULL), 262 PARTITION `p0000-01-02` VALUES IN (TO_SECONDS('0000-01-02')), 263 PARTITION `p1001-01-01` VALUES IN (TO_SECONDS('1001-01-01'))); 264if ($verify_without_partitions) 265{ 266ALTER TABLE t1 REMOVE PARTITIONING; 267} 268INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), 269 ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); 270--source include/partition_date_range.inc 271--echo # test without index 272ALTER TABLE t1 DROP KEY a; 273--source include/partition_date_range.inc 274DROP TABLE t1; 275 276# 277# ALTER TABLE test 278# 279 280create table t1 (a int, b int); 281 282insert into t1 values (10,10),(2,5),(0,0); 283 284select * from t1; 285 286alter table t1 287 PARTITION BY LIST (a) 288 ( 289 PARTITION p2 VALUES IN (1,2,3), 290 PARTITION p1 VALUES IN (20,0), 291 PARTITION p0 DEFAULT 292 ) 293; 294show create table t1; 295select * from t1; 296--sorted_result 297select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 298explain partitions select * from t1 where a=2 and b=5; 299explain partitions select * from t1 where a=10 and b=10; 300alter table t1 301 PARTITION BY LIST (a) 302 ( 303 PARTITION p2 VALUES IN (1,2,3), 304 PARTITION p1 VALUES IN (20,0), 305 PARTITION p0 VALUES IN (10) 306 ) 307; 308show create table t1; 309select * from t1; 310--sorted_result 311select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 312explain partitions select * from t1 where a=2 and b=5; 313explain partitions select * from t1 where a=10 and b=10; 314alter table t1 315 PARTITION BY LIST (a) 316 ( 317 PARTITION p2 DEFAULT, 318 PARTITION p1 VALUES IN (20,0), 319 PARTITION p0 VALUES IN (10) 320 ) 321; 322show create table t1; 323select * from t1; 324--sorted_result 325select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 326explain partitions select * from t1 where a=2 and b=5; 327explain partitions select * from t1 where a=10 and b=10; 328 329alter table t1 drop partition p2; 330show create table t1; 331select * from t1; 332--sorted_result 333select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 334 335alter table t1 add partition (PARTITION pd DEFAULT); 336show create table t1; 337--error ER_PARTITION_DEFAULT_ERROR 338alter table t1 add partition (PARTITION pdd DEFAULT); 339alter table t1 drop partition pd; 340--error ER_PARTITION_DEFAULT_ERROR 341alter table t1 add partition (PARTITION pdd DEFAULT, 342 PARTITION pd DEFAULT); 343 344drop table t1; 345 346create table t1 (a int, b int); 347 348insert into t1 values (10,10),(2,5),(0,0); 349 350select * from t1; 351 352alter table t1 353 PARTITION BY LIST COLUMNS(a,b) 354 ( 355 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 356 PARTITION p1 VALUES IN ((1,1),(0,0)), 357 PARTITION p0 DEFAULT 358 ) 359; 360show create table t1; 361select * from t1; 362--sorted_result 363select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 364explain partitions select * from t1 where a=2 and b=5; 365explain partitions select * from t1 where a=10 and b=10; 366alter table t1 367 PARTITION BY LIST COLUMNS(a,b) 368 ( 369 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 370 PARTITION p1 VALUES IN ((1,1),(0,0)), 371 PARTITION p0 VALUES IN ((10,10)) 372 ) 373; 374show create table t1; 375select * from t1; 376--sorted_result 377select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 378explain partitions select * from t1 where a=2 and b=5; 379explain partitions select * from t1 where a=10 and b=10; 380alter table t1 381 PARTITION BY LIST COLUMNS(a,b) 382 ( 383 PARTITION p2 DEFAULT, 384 PARTITION p1 VALUES IN ((1,1),(0,0)), 385 PARTITION p0 VALUES IN ((10,10)) 386 ) 387; 388show create table t1; 389select * from t1; 390--sorted_result 391select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 392explain partitions select * from t1 where a=2 and b=5; 393explain partitions select * from t1 where a=10 and b=10; 394 395alter table t1 drop partition p2; 396show create table t1; 397select * from t1; 398--sorted_result 399select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 400 401alter table t1 add partition (PARTITION pd DEFAULT); 402show create table t1; 403--error ER_PARTITION_DEFAULT_ERROR 404alter table t1 add partition (PARTITION pdd DEFAULT); 405alter table t1 drop partition pd; 406--error ER_PARTITION_DEFAULT_ERROR 407alter table t1 add partition (PARTITION pdd DEFAULT, 408 PARTITION pd DEFAULT); 409drop table t1; 410 411 412# 413# Problem of reorganizing DEFAULT partition 414# 415create table t1 (a int) 416 PARTITION BY LIST (a) 417 ( 418 PARTITION p2 VALUES IN (4,5,6), 419 PARTITION p1 VALUES IN (1), 420 PARTITION pd DEFAULT 421 ) 422; 423insert into t1 values (1),(2),(3),(4); 424--sorted_result 425select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 426 427alter table t1 add partition 428 (partition p0 VALUES IN (2,3)); 429 430--sorted_result 431select partition_name, table_rows from INFORMATION_SCHEMA.PARTITIONS where table_name='t1'; 432 433drop table t1; 434 435# 436# Check that first DEFAULT works in LIST COLUMN 437# 438create table t1 (a int, b int) 439 PARTITION BY LIST COLUMNS(a,b) 440 ( 441 PARTITION p0 DEFAULT, 442 PARTITION p2 VALUES IN ((1,4),(2,5),(3,6)), 443 PARTITION p1 VALUES IN ((1,1),(0,0)) 444 ) 445; 446 447show create table t1; 448 449drop table t1; 450 451--echo # 452--echo # MDEV-10765: Wrong result - query does not retrieve values from 453--echo # default partition on a table partitioned by list columns 454--echo # 455 456create table t1 (i int, j int) partition by list columns(i,j) (partition p1 values in ((10,10)), partition p2 default); 457insert into t1 values (10,1); 458select * from t1 where i = 10; 459explain partitions 460select * from t1 where i = 10; 461select * from t1 where i = 10 and j=1; 462explain partitions 463select * from t1 where i = 10 and j=1; 464insert into t1 values (10,10); 465select * from t1 where i = 10 and j=10; 466explain partitions 467select * from t1 where i = 10 and j=10; 468drop table t1; 469 470create table t1 471( 472 a int not null, 473 b int not null, 474 c int 475) 476partition by list columns(a,b) 477( 478 partition p1 values in ((10,10)), 479 partition p2 values in ((10,20)), 480 partition p3 values in ((10,30)), 481 partition p4 values in ((10,40)), 482 partition p5 values in ((10,50)) 483); 484 485insert into t1 values 486 (10,10,1234), 487 (10,20,1234), 488 (10,30,1234), 489 (10,40,1234), 490 (10,50,1234); 491 492explain partitions 493select * from t1 494where a>=10 and (a <=10 and b <=30); 495drop table t1; 496 497--echo # 498--echo # MDEV-10763: Wrong result - server does not return NULL values 499--echo # from default list partition after ALTER table 500--echo # 501create table t1 (i int) partition by list (i) ( partition p1 default); 502insert into t1 values (null); 503select * from t1 where i is null; 504alter table t1 partition by list (i) ( partition p1 values in (1), partition p2 default); 505select * from t1 where i is null; 506explain partitions 507select * from t1 where i is null; 508alter table t1 partition by list (i) ( 509 partition p0 values in (NULL), 510 partition p1 values in (1), 511 partition p2 default); 512select * from t1 where i is null; 513explain partitions 514select * from t1 where i is null; 515 516drop table t1; 517 518 519--echo # 520--echo # MDEV-12395: DROP PARTITION does not work as expected when 521--echo # table has DEFAULT LIST partition 522--echo # 523 524CREATE TABLE t1 (i INT) 525 PARTITION BY LIST (i) 526 (PARTITION p VALUES IN (1,2,3,4), 527 PARTITION pdef DEFAULT); 528INSERT INTO t1 VALUES (1),(10); 529ALTER TABLE t1 DROP PARTITION p; 530SELECT * FROM t1; 531SHOW CREATE TABLE t1; 532DROP TABLE t1; 533 534CREATE TABLE t1 (i INT) 535 PARTITION BY LIST (i) 536 (PARTITION p VALUES IN (1,2,3,4), 537 PARTITION pdef DEFAULT); 538INSERT INTO t1 VALUES (1),(10); 539ALTER TABLE t1 DROP PARTITION pdef; 540SELECT * FROM t1; 541SHOW CREATE TABLE t1; 542DROP TABLE t1; 543