1#--disable_abort_on_error 2# 3# Simple test for the partition storage engine 4# testing list partitioning 5# 6-- source include/have_partition.inc 7 8--disable_warnings 9drop table if exists t1; 10--enable_warnings 11 12# 13# Bug 20733: Zerofill columns gives wrong result with partitioned tables 14# 15create table t1 (a int unsigned) 16partition by list (a) 17(partition p0 values in (0), 18 partition p1 values in (1), 19 partition pnull values in (null), 20 partition p2 values in (2)); 21 22insert into t1 values (null),(0),(1),(2); 23select * from t1 where a < 2; 24select * from t1 where a <= 0; 25select * from t1 where a < 1; 26select * from t1 where a > 0; 27select * from t1 where a > 1; 28select * from t1 where a >= 0; 29select * from t1 where a >= 1; 30select * from t1 where a is null; 31select * from t1 where a is not null; 32select * from t1 where a is null or a > 0; 33drop table t1; 34 35create table t1 (a int unsigned, b int) 36partition by list (a) 37subpartition by hash (b) 38subpartitions 2 39(partition p0 values in (0), 40 partition p1 values in (1), 41 partition pnull values in (null, 2), 42 partition p3 values in (3)); 43select partition_method, partition_expression, partition_description 44 from information_schema.partitions where table_name = "t1"; 45insert into t1 values (0,0),(0,1),(1,0),(1,1),(null,0),(null,1); 46insert into t1 values (2,0),(2,1),(3,0),(3,1); 47 48explain partitions select * from t1 where a is null; 49select * from t1 where a is null; 50explain partitions select * from t1 where a = 2; 51select * from t1 where a = 2; 52select * from t1 where a <= 0; 53select * from t1 where a < 3; 54select * from t1 where a >= 1 or a is null; 55drop table t1; 56 57# 58# Test ordinary list partitioning that it works ok 59# 60CREATE TABLE t1 ( 61a int not null, 62b int not null, 63c int not null) 64partition by list(a) 65partitions 2 66(partition x123 values in (1,5,6), 67 partition x234 values in (4,7,8)); 68 69INSERT into t1 VALUES (1,1,1); 70--error ER_NO_PARTITION_FOR_GIVEN_VALUE 71INSERT into t1 VALUES (2,1,1); 72--error ER_NO_PARTITION_FOR_GIVEN_VALUE 73INSERT into t1 VALUES (3,1,1); 74INSERT into t1 VALUES (4,1,1); 75INSERT into t1 VALUES (5,1,1); 76INSERT into t1 VALUES (6,1,1); 77INSERT into t1 VALUES (7,1,1); 78INSERT into t1 VALUES (8,1,1); 79--error ER_NO_PARTITION_FOR_GIVEN_VALUE 80INSERT into t1 VALUES (9,1,1); 81INSERT into t1 VALUES (1,2,1); 82INSERT into t1 VALUES (1,3,1); 83INSERT into t1 VALUES (1,4,1); 84INSERT into t1 VALUES (7,2,1); 85INSERT into t1 VALUES (7,3,1); 86INSERT into t1 VALUES (7,4,1); 87 88SELECT * from t1; 89SELECT * from t1 WHERE a=1; 90SELECT * from t1 WHERE a=7; 91SELECT * from t1 WHERE b=2; 92 93UPDATE t1 SET a=8 WHERE a=7 AND b=3; 94SELECT * from t1; 95UPDATE t1 SET a=8 WHERE a=5 AND b=1; 96SELECT * from t1; 97 98DELETE from t1 WHERE a=8; 99SELECT * from t1; 100DELETE from t1 WHERE a=2; 101SELECT * from t1; 102DELETE from t1 WHERE a=5 OR a=6; 103SELECT * from t1; 104 105ALTER TABLE t1 106partition by list(a) 107partitions 2 108(partition x123 values in (1,5,6), 109 partition x234 values in (4,7,8)); 110SELECT * from t1; 111INSERT into t1 VALUES (6,2,1); 112--error ER_NO_PARTITION_FOR_GIVEN_VALUE 113INSERT into t1 VALUES (2,2,1); 114 115drop table t1; 116# 117# Subpartition by hash, two partitions and two subpartitions 118# Defined node group 119# 120CREATE TABLE t1 ( 121a int not null, 122b int not null, 123c int not null, 124primary key (a,b)) 125partition by list (a) 126subpartition by hash (a+b) 127( partition x1 values in (1,2,3) 128 ( subpartition x11 nodegroup 0, 129 subpartition x12 nodegroup 1), 130 partition x2 values in (4,5,6) 131 ( subpartition x21 nodegroup 0, 132 subpartition x22 nodegroup 1) 133); 134 135INSERT into t1 VALUES (1,1,1); 136INSERT into t1 VALUES (4,1,1); 137--error ER_NO_PARTITION_FOR_GIVEN_VALUE 138INSERT into t1 VALUES (7,1,1); 139UPDATE t1 SET a=5 WHERE a=1; 140SELECT * from t1; 141UPDATE t1 SET a=6 WHERE a=4; 142SELECT * from t1; 143DELETE from t1 WHERE a=6; 144SELECT * from t1; 145 146drop table t1; 147 148# 149CREATE TABLE t1 ( 150a int not null, 151b int not null, 152c int not null, 153primary key(a,b)) 154partition by list (a) 155(partition x1 values in (1,2,9,4) tablespace ts1); 156 157drop table t1; 158 159# 160#Bug #17173 Partitions: less-than search fails 161# 162CREATE TABLE t1 (s1 int) PARTITION BY LIST (s1) 163(PARTITION p1 VALUES IN (1), 164PARTITION p2 VALUES IN (2), 165PARTITION p3 VALUES IN (3), 166PARTITION p4 VALUES IN (4), 167PARTITION p5 VALUES IN (5)); 168INSERT INTO t1 VALUES (1), (2), (3), (4), (5); 169SELECT COUNT(*) FROM t1 WHERE s1 < 3; 170DROP TABLE t1; 171 172# 173# Bug 19281 Partitions: Auto-increment value lost 174# 175create table t1 (a int auto_increment primary key) 176auto_increment=100 177partition by list (a) 178(partition p0 values in (1, 100)); 179create index inx on t1 (a); 180insert into t1 values (null); 181select * from t1; 182drop table t1; 183 184--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED 185create table t1 (a char(1)) 186partition by list (ascii(ucase(a))) 187(partition p1 values in (2)); 188 189--echo # 190--echo # MDEV-11681: PARTITION BY LIST COLUMNS with default partition: 191--echo # Assertion `part_info->num_list_values' failed in 192--echo # get_part_iter_for_interval_cols_via_map 193--echo # 194CREATE TABLE t1 (f int) PARTITION BY LIST COLUMNS (f) (PARTITION pdef DEFAULT); 195insert into t1 values (1),(2); 196select * from t1 where f = 1; 197 198drop table t1; 199 200CREATE TABLE t1 (f int, d int) PARTITION BY LIST COLUMNS (f,d) (PARTITION pdef DEFAULT); 201insert into t1 values (1,1),(2,2); 202select * from t1 where f = 1 and d = 1 ; 203 204drop table t1; 205CREATE TABLE t1 (f int) PARTITION BY LIST (f) (PARTITION pdef DEFAULT); 206insert into t1 values (1),(2); 207select * from t1 where f = 1; 208 209drop table t1; 210 211--echo #end of 10.2 tests 212 213--echo # 214--echo # Bug MDEV-16101: More than MAX_REF_PARTS values in a list on ALTER TABLE. 215--echo # Currently MAX_REF_PARTS = 32. 216CREATE TABLE ts1 (a INT, PRIMARY KEY (`a`)) 217PARTITION BY LIST (`a`) 218(PARTITION `p ts_0` VALUES IN (101,102,103,104,105,106,107,108,109,110, 219 111,112,113,114,115,116,117,118,119,120, 220 121,122,123,124,125,126,127,128,129,130, 221 131,132,133)); 222INSERT INTO ts1 223VALUES (101), (102), (103), (104), (105), (106), (107), (108), (109), (110), 224 (111), (112), (113), (114), (115), (116), (117), (118), (119), (120), 225 (121), (122), (123), (124), (125), (126), (127), (128), (129), (130), 226 (131), (132), (133); 227--error ER_NO_PARTITION_FOR_GIVEN_VALUE 228INSERT INTO ts1 VALUES (134); 229SELECT * FROM ts1; 230ALTER TABLE ts1 ADD PARTITION 231(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10, 232 11,12,13,14,15,16,17,18,19,20, 233 21,22,23,24,25,26,27,28,29,30, 234 31,32,33)); 235INSERT INTO ts1 236VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), 237 (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), 238 (21), (22), (23), (24), (25), (26), (27), (28), (29), (30), 239 (31), (32), (33); 240--error ER_NO_PARTITION_FOR_GIVEN_VALUE 241INSERT INTO ts1 VALUES(34); 242SELECT * FROM ts1; 243ALTER TABLE ts1 REORGANIZE PARTITION `p ts_1` INTO 244(PARTITION `p ts_1` VALUES IN (1,2,3,4,5,6,7,8,9,10, 245 11,12,13,14,15,16,17,18,19,20, 246 21,22,23,24,25,26,27,28,29,30, 247 31,32,33,34,35)); 248INSERT INTO ts1 VALUES (34), (35); 249--error ER_NO_PARTITION_FOR_GIVEN_VALUE 250INSERT INTO ts1 VALUES (36); 251SELECT * FROM ts1; 252DROP TABLE ts1; 253