1
drop table if exists t1;
3
# Bug#52815: LIST COLUMNS doesn't insert rows in correct partition
4
# if muliple columns used
8
department VARCHAR(10),
10
) PARTITION BY LIST COLUMNS (department, country) (
11
PARTITION first_office VALUES IN (('dep1', 'Russia'), ('dep1', 'Croatia')),
12
PARTITION second_office VALUES IN (('dep2', 'Russia'))
14
INSERT INTO t1 VALUES(1, 'Ann', 'dep1', 'Russia');
15
INSERT INTO t1 VALUES(2, 'Bob', 'dep1', 'Croatia');
16
INSERT INTO t1 VALUES(3, 'Cecil', 'dep2', 'Russia');
17
INSERT INTO t1 VALUES(3, 'Dan', 'dep2', 'Croatia');
18
ERROR HY000: Table has no partition for value from column_list
19
SELECT PARTITION_NAME,TABLE_ROWS
20
FROM INFORMATION_SCHEMA.PARTITIONS
21
WHERE TABLE_NAME = 't1';
22
PARTITION_NAME TABLE_ROWS
27
t1 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
/*!50500 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) */
36
SELECT * FROM t1 WHERE department = 'dep2' and country = 'Croatia';
37
id name department country
38
SELECT * FROM t1 WHERE department = 'dep1' and country = 'Croatia';
39
id name department country
42
CREATE TABLE t1 (a DECIMAL)
43
PARTITION BY RANGE COLUMNS (a)
44
(PARTITION p0 VALUES LESS THAN (0));
45
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
46
CREATE TABLE t1 (a BLOB)
47
PARTITION BY RANGE COLUMNS (a)
48
(PARTITION p0 VALUES LESS THAN ("X"));
49
ERROR HY000: A BLOB field is not allowed in partition function
50
CREATE TABLE t1 (a TEXT)
51
PARTITION BY RANGE COLUMNS (a)
52
(PARTITION p0 VALUES LESS THAN ("X"));
53
ERROR HY000: A BLOB field is not allowed in partition function
54
CREATE TABLE t1 (a FLOAT)
55
PARTITION BY RANGE COLUMNS (a)
56
(PARTITION p0 VALUES LESS THAN (0.0));
57
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
58
CREATE TABLE t1 (a DOUBLE)
59
PARTITION BY RANGE COLUMNS (a)
60
(PARTITION p0 VALUES LESS THAN (0.0));
61
ERROR HY000: Field 'a' is of a not allowed type for this type of partitioning
62
CREATE TABLE t1 (d TIMESTAMP)
63
PARTITION BY RANGE COLUMNS(d)
64
(PARTITION p0 VALUES LESS THAN ('2000-01-01'),
65
PARTITION p1 VALUES LESS THAN ('2040-01-01'));
66
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
67
CREATE TABLE t1 (d BIT(1))
68
PARTITION BY RANGE COLUMNS(d)
69
(PARTITION p0 VALUES LESS THAN (0),
70
PARTITION p1 VALUES LESS THAN (1));
71
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
72
CREATE TABLE t1 (d ENUM("YES","NO"))
73
PARTITION BY RANGE COLUMNS(d)
74
(PARTITION p0 VALUES LESS THAN ("NO"),
75
PARTITION p1 VALUES LESS THAN (MAXVALUE));
76
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
77
CREATE TABLE t1 (d SET("Car","MC"))
78
PARTITION BY RANGE COLUMNS(d)
79
(PARTITION p0 VALUES LESS THAN ("MC"),
80
PARTITION p1 VALUES LESS THAN (MAXVALUE));
81
ERROR HY000: Field 'd' is of a not allowed type for this type of partitioning
82
create table t1 (a int, b int)
83
partition by range columns (a,b)
84
( partition p0 values less than (maxvalue, 10),
85
partition p1 values less than (maxvalue, maxvalue));
86
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
87
create table t1 (a int, b int, c int)
88
partition by range columns (a,b,c)
89
( partition p0 values less than (1, maxvalue, 10),
90
partition p1 values less than (1, maxvalue, maxvalue));
91
ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
92
create table t1 (a varchar(5) character set ucs2 collate ucs2_bin)
93
partition by range columns (a)
94
(partition p0 values less than (0x0041));
95
insert into t1 values (0x00410000);
96
select hex(a) from t1 where a like 'A_';
99
explain partitions select hex(a) from t1 where a like 'A_';
100
id select_type table partitions type possible_keys key key_len ref rows Extra
101
1 SIMPLE t1 p0 system NULL NULL NULL NULL 1 NULL
102
alter table t1 remove partitioning;
103
select hex(a) from t1 where a like 'A_';
106
create index a on t1 (a);
107
select hex(a) from t1 where a like 'A_';
110
insert into t1 values ('A_');
111
select hex(a) from t1;
116
create table t1 (a varchar(1) character set latin1 collate latin1_general_ci)
117
partition by range columns(a)
118
( partition p0 values less than ('a'),
119
partition p1 values less than ('b'),
120
partition p2 values less than ('c'),
121
partition p3 values less than ('d'));
122
insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c');
123
select * from t1 where a > 'B' collate latin1_bin;
129
select * from t1 where a <> 'B' collate latin1_bin;
136
alter table t1 remove partitioning;
137
select * from t1 where a > 'B' collate latin1_bin;
143
select * from t1 where a <> 'B' collate latin1_bin;
151
create table t1 (a varchar(2) character set latin1,
152
b varchar(2) character set latin1)
153
partition by list columns(a,b)
154
(partition p0 values in (('a','a')));
155
insert into t1 values ('A','A');
156
select * from t1 where b <> 'a' collate latin1_bin AND
157
a = 'A' collate latin1_bin;
160
alter table t1 remove partitioning;
161
select * from t1 where b <> 'a' collate latin1_bin AND
162
a = 'A' collate latin1_bin;
166
create table t1 (a varchar(5))
167
partition by list columns(a)
168
( partition p0 values in ('\''),
169
partition p1 values in ('\\'),
170
partition p2 values in ('\0'));
171
show create table t1;
173
t1 CREATE TABLE `t1` (
174
`a` varchar(5) DEFAULT NULL
175
) ENGINE=MyISAM DEFAULT CHARSET=latin1
176
/*!50500 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) */
181
set @@sql_mode=allow_invalid_dates;
182
create table t1 (a char, b char, c date)
183
partition by range columns (a,b,c)
184
( partition p0 values less than (0,0,to_days('3000-11-31')));
185
ERROR HY000: Partition column values of incorrect type
186
create table t1 (a char, b char, c date)
187
partition by range columns (a,b,c)
188
( partition p0 values less than (0,0,'3000-11-31'));
189
ERROR HY000: Partition column values of incorrect type
191
create table t1 (a int, b char(10), c varchar(25), d datetime)
192
partition by range columns(a,b,c,d)
193
subpartition by hash (to_seconds(d))
195
( partition p0 values less than (1, 0, MAXVALUE, '1900-01-01'),
196
partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
197
partition p2 values less than (1, 'a', MAXVALUE, MAXVALUE),
198
partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));
199
ERROR HY000: Partition column values of incorrect type
200
create table t1 (a int, b char(10), c varchar(25), d datetime)
201
partition by range columns(a,b,c,d)
202
subpartition by hash (to_seconds(d))
204
( partition p0 values less than (1, '0', MAXVALUE, '1900-01-01'),
205
partition p1 values less than (1, 'a', MAXVALUE, '1999-01-01'),
206
partition p2 values less than (1, 'b', MAXVALUE, MAXVALUE),
207
partition p3 values less than (1, MAXVALUE, MAXVALUE, MAXVALUE));
208
select partition_method, partition_expression, partition_description
209
from information_schema.partitions where table_name = "t1";
210
partition_method partition_expression partition_description
211
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01'
212
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01'
213
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01'
214
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'0',MAXVALUE,'1900-01-01'
215
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01'
216
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01'
217
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01'
218
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'a',MAXVALUE,'1999-01-01'
219
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE
220
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE
221
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE
222
RANGE COLUMNS `a`,`b`,`c`,`d` 1,'b',MAXVALUE,MAXVALUE
223
RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE
224
RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE
225
RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE
226
RANGE COLUMNS `a`,`b`,`c`,`d` 1,MAXVALUE,MAXVALUE,MAXVALUE
227
show create table t1;
229
t1 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
/*!50500 PARTITION BY RANGE COLUMNS(a,b,c,d)
236
SUBPARTITION BY HASH (to_seconds(d))
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) */
243
create table t1 (a int, b int)
244
partition by range columns (a,b)
245
(partition p0 values less than (NULL, maxvalue));
246
ERROR HY000: Not allowed to use NULL value in VALUES LESS THAN
247
create table t1 (a int, b int)
248
partition by list columns(a,b)
249
( partition p0 values in ((maxvalue, 0)));
250
Got one of the listed errors
251
create table t1 (a int, b int)
252
partition by list columns (a,b)
253
( partition p0 values in ((0,0)));
254
alter table t1 add partition
255
(partition p1 values in (maxvalue, maxvalue));
256
Got one of the listed errors
258
create table t1 (a int, b int)
259
partition by key (a,a);
260
ERROR HY000: Duplicate partition field name 'a'
261
create table t1 (a int, b int)
262
partition by list columns(a,a)
263
( partition p values in ((1,1)));
264
ERROR HY000: Duplicate partition field name 'a'
265
create table t1 (a int signed)
266
partition by list (a)
267
( partition p0 values in (1, 3, 5, 7, 9, NULL),
268
partition p1 values in (2, 4, 6, 8, 0));
269
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
270
select * from t1 where NULL <= a;
272
select * from t1 where a is null;
275
explain partitions select * from t1 where a is null;
276
id select_type table partitions type possible_keys key key_len ref rows Extra
277
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
278
select * from t1 where a <= 1;
283
create table t1 (a int signed)
284
partition by list columns(a)
285
( partition p0 values in (1, 3, 5, 7, 9, NULL),
286
partition p1 values in (2, 4, 6, 8, 0));
287
insert into t1 values (NULL),(0),(1),(2),(2),(4),(4),(4),(8),(8);
288
select * from t1 where a <= NULL;
290
select * from t1 where a is null;
293
explain partitions select * from t1 where a is null;
294
id select_type table partitions type possible_keys key key_len ref rows Extra
295
1 SIMPLE t1 p0 ALL NULL NULL NULL NULL 2 Using where
296
select * from t1 where a <= 1;
301
create table t1 (a int, b int)
302
partition by list columns(a,b)
303
( partition p0 values in ((1, NULL), (2, NULL), (NULL, NULL)),
304
partition p1 values in ((1,1), (2,2)),
305
partition p2 values in ((3, NULL), (NULL, 1)));
306
select partition_method, partition_expression, partition_description
307
from information_schema.partitions where table_name = "t1";
308
partition_method partition_expression partition_description
309
LIST COLUMNS `a`,`b` (1,NULL),(2,NULL),(NULL,NULL)
310
LIST COLUMNS `a`,`b` (1,1),(2,2)
311
LIST COLUMNS `a`,`b` (3,NULL),(NULL,1)
312
show create table t1;
314
t1 CREATE TABLE `t1` (
315
`a` int(11) DEFAULT NULL,
316
`b` int(11) DEFAULT NULL
317
) ENGINE=MyISAM DEFAULT CHARSET=latin1
318
/*!50500 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) */
322
insert into t1 values (3, NULL);
323
insert into t1 values (NULL, 1);
324
insert into t1 values (NULL, NULL);
325
insert into t1 values (1, NULL);
326
insert into t1 values (2, NULL);
327
insert into t1 values (1,1);
328
insert into t1 values (2,2);
329
select * from t1 where a = 1;
333
select * from t1 where a = 2;
337
select * from t1 where a > 8;
339
select * from t1 where a not between 8 and 8;
346
show create table t1;
348
t1 CREATE TABLE `t1` (
349
`a` int(11) DEFAULT NULL,
350
`b` int(11) DEFAULT NULL
351
) ENGINE=MyISAM DEFAULT CHARSET=latin1
352
/*!50500 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) */
357
create table t1 (a int)
358
partition by list (a)
359
( partition p0 values in (1),
360
partition p1 values in (1));
361
ERROR HY000: Multiple definition of same constant in list partitioning
362
create table t1 (a int)
363
partition by list (a)
364
( partition p0 values in (2, 1),
365
partition p1 values in (4, NULL, 3));
366
select partition_method, partition_expression, partition_description
367
from information_schema.partitions where table_name = "t1";
368
partition_method partition_expression partition_description
371
show create table t1;
373
t1 CREATE TABLE `t1` (
374
`a` int(11) DEFAULT NULL
375
) ENGINE=MyISAM DEFAULT CHARSET=latin1
376
/*!50100 PARTITION BY LIST (a)
377
(PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM,
378
PARTITION p1 VALUES IN (NULL,4,3) ENGINE = MyISAM) */
379
insert into t1 values (1);
380
insert into t1 values (2);
381
insert into t1 values (3);
382
insert into t1 values (4);
383
insert into t1 values (NULL);
384
insert into t1 values (5);
385
ERROR HY000: Table has no partition for value 5
387
create table t1 (a int)
388
partition by list columns(a)
389
( partition p0 values in (2, 1),
390
partition p1 values in ((4), (NULL), (3)));
391
ERROR 42000: Row expressions in VALUES IN only allowed for multi-field column partitioning near '))' at line 4
392
create table t1 (a int)
393
partition by list columns(a)
394
( partition p0 values in (2, 1),
395
partition p1 values in (4, NULL, 3));
396
select partition_method, partition_expression, partition_description
397
from information_schema.partitions where table_name = "t1";
398
partition_method partition_expression partition_description
400
LIST COLUMNS `a` 4,NULL,3
401
show create table t1;
403
t1 CREATE TABLE `t1` (
404
`a` int(11) DEFAULT NULL
405
) ENGINE=MyISAM DEFAULT CHARSET=latin1
406
/*!50500 PARTITION BY LIST COLUMNS(a)
407
(PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM,
408
PARTITION p1 VALUES IN (4,NULL,3) ENGINE = MyISAM) */
409
insert into t1 values (1);
410
insert into t1 values (2);
411
insert into t1 values (3);
412
insert into t1 values (4);
413
insert into t1 values (NULL);
414
insert into t1 values (5);
415
ERROR HY000: Table has no partition for value from column_list
416
show create table t1;
418
t1 CREATE TABLE `t1` (
419
`a` int(11) DEFAULT NULL
420
) ENGINE=MyISAM DEFAULT CHARSET=latin1
421
/*!50500 PARTITION BY LIST COLUMNS(a)
422
(PARTITION p0 VALUES IN (2,1) ENGINE = MyISAM,
423
PARTITION p1 VALUES IN (4,NULL,3) ENGINE = MyISAM) */
425
create table t1 (a int, b char(10), c varchar(5), d int)
426
partition by range columns(a,b,c)
427
subpartition by key (c,d)
429
( partition p0 values less than (1,'abc','abc'),
430
partition p1 values less than (2,'abc','abc'),
431
partition p2 values less than (3,'abc','abc'),
432
partition p3 values less than (4,'abc','abc'));
433
select partition_method, partition_expression, partition_description
434
from information_schema.partitions where table_name = "t1";
435
partition_method partition_expression partition_description
436
RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc'
437
RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc'
438
RANGE COLUMNS `a`,`b`,`c` 1,'abc','abc'
439
RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc'
440
RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc'
441
RANGE COLUMNS `a`,`b`,`c` 2,'abc','abc'
442
RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc'
443
RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc'
444
RANGE COLUMNS `a`,`b`,`c` 3,'abc','abc'
445
RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc'
446
RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc'
447
RANGE COLUMNS `a`,`b`,`c` 4,'abc','abc'
448
show create table t1;
450
t1 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
/*!50500 PARTITION BY RANGE COLUMNS(a,b,c)
457
SUBPARTITION BY KEY (c,d)
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) */
463
insert into t1 values (1,'a','b',1),(2,'a','b',2),(3,'a','b',3);
464
insert into t1 values (1,'b','c',1),(2,'b','c',2),(3,'b','c',3);
465
insert into t1 values (1,'c','d',1),(2,'c','d',2),(3,'c','d',3);
466
insert into t1 values (1,'d','e',1),(2,'d','e',2),(3,'d','e',3);
467
select * 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))));
473
create table t1 (a int, b varchar(2), c int)
474
partition by range columns (a, b, c)
475
(partition p0 values less than (1, 'A', 1),
476
partition p1 values less than (1, 'B', 1));
477
select partition_method, partition_expression, partition_description
478
from information_schema.partitions where table_name = "t1";
479
partition_method partition_expression partition_description
480
RANGE COLUMNS `a`,`b`,`c` 1,'A',1
481
RANGE COLUMNS `a`,`b`,`c` 1,'B',1
482
show create table t1;
484
t1 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
/*!50500 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) */
492
insert into t1 values (1, 'A', 1);
493
explain partitions select * from t1 where a = 1 AND b <= 'A' and c = 1;
494
id select_type table partitions type possible_keys key key_len ref rows Extra
495
1 SIMPLE t1 p0,p1 system NULL NULL NULL NULL 1 NULL
496
select * from t1 where a = 1 AND b <= 'A' and c = 1;
500
create table t1 (a char, b char, c char)
501
partition by list columns(a)
502
( partition p0 values in ('a'));
503
insert into t1 (a) values ('a');
504
select * from t1 where a = 'a';
508
create table t1 (d time)
509
partition by range columns(d)
510
( partition p0 values less than ('2000-01-01'),
511
partition p1 values less than ('2040-01-01'));
512
ERROR HY000: Partition column values of incorrect type
513
create table t1 (a int, b int)
514
partition by range columns(a,b)
515
(partition p0 values less than (maxvalue, 10));
517
create table t1 (d date)
518
partition by range columns(d)
519
( partition p0 values less than ('2000-01-01'),
520
partition p1 values less than ('2009-01-01'));
522
create table t1 (d date)
523
partition by range columns(d)
524
( partition p0 values less than ('1999-01-01'),
525
partition p1 values less than ('2000-01-01'));
527
create table t1 (d date)
528
partition by range columns(d)
529
( partition p0 values less than ('2000-01-01'),
530
partition p1 values less than ('3000-01-01'));
532
create table t1 (a int, b int)
533
partition by range columns(a,b)
534
(partition p2 values less than (99,99),
535
partition p1 values less than (99,999));
536
insert into t1 values (99,998);
537
select * from t1 where b = 998;
541
create table t1 as select to_seconds(null) as to_seconds;
542
select data_type from information_schema.columns
543
where column_name='to_seconds';
547
create table t1 (a int, b int)
548
partition by list columns(a,b)
549
(partition p0 values in ((maxvalue,maxvalue)));
550
ERROR 42000: Cannot use MAXVALUE as value in VALUES IN near 'maxvalue,maxvalue)))' at line 3
551
create table t1 (a int, b int)
552
partition by range columns(a,b)
553
(partition p0 values less than (maxvalue,maxvalue));
555
create table t1 (a int)
556
partition by list columns(a)
557
(partition p0 values in (0));
558
select partition_method from information_schema.partitions where table_name='t1';
562
create table t1 (a char(6))
563
partition by range columns(a)
564
(partition p0 values less than ('H23456'),
565
partition p1 values less than ('M23456'));
566
insert into t1 values ('F23456');
571
create table t1 (a char(6))
572
partition by range columns(a)
573
(partition p0 values less than (H23456),
574
partition p1 values less than (M23456));
575
ERROR 42S22: Unknown column 'H23456' in 'field list'
576
create table t1 (a char(6))
577
partition by range columns(a)
578
(partition p0 values less than (23456),
579
partition p1 values less than (23456));
580
ERROR HY000: Partition column values of incorrect type
581
create table t1 (a int, b int)
582
partition by range columns(a,b)
583
(partition p0 values less than (10));
584
ERROR 42000: Inconsistency in usage of column lists for partitioning near '))' at line 3
585
create table t1 (a int, b int)
586
partition by range columns(a,b)
587
(partition p0 values less than (1,1,1);
588
ERROR HY000: Inconsistency in usage of column lists for partitioning
589
create table t1 (a int, b int)
590
partition by range columns(a,b)
591
(partition p0 values less than (1, 0),
592
partition p1 values less than (2, maxvalue),
593
partition p2 values less than (3, 3),
594
partition p3 values less than (10, maxvalue));
595
insert into t1 values (11,0);
596
ERROR HY000: Table has no partition for value from column_list
597
insert into t1 values (0,1),(1,1),(2,1),(3,1),(3,4),(4,9),(9,1);
608
partition by range columns(b,a)
609
(partition p0 values less than (1,2),
610
partition p1 values less than (3,3),
611
partition p2 values less than (9,5));
612
explain partitions select * from t1 where b < 2;
613
id select_type table partitions type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 p0,p1 ALL NULL NULL NULL NULL 5 Using where
615
select * from t1 where b < 2;
622
explain partitions select * from t1 where b < 4;
623
id select_type table partitions type possible_keys key key_len ref rows Extra
624
1 SIMPLE t1 p0,p1,p2 ALL NULL NULL NULL NULL 7 Using where
625
select * from t1 where b < 4;
632
alter table t1 reorganize partition p1 into
633
(partition p11 values less than (2,2),
634
partition p12 values less than (3,3));
635
alter table t1 reorganize partition p0 into
636
(partition p01 values less than (0,3),
637
partition p02 values less than (1,1));
638
ERROR HY000: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
639
alter table t1 reorganize partition p2 into
640
(partition p2 values less than(9,6,1));
641
ERROR HY000: Inconsistency in usage of column lists for partitioning
642
alter table t1 reorganize partition p2 into
643
(partition p2 values less than (10));
644
ERROR HY000: Inconsistency in usage of column lists for partitioning
645
alter table t1 reorganize partition p2 into
646
(partition p21 values less than (4,7),
647
partition p22 values less than (9,5));
648
explain partitions select * from t1 where b < 4;
649
id select_type table partitions type possible_keys key key_len ref rows Extra
650
1 SIMPLE t1 p0,p11,p12,p21 ALL NULL NULL NULL NULL 6 Using where
651
select * from t1 where b < 4;
659
create table t1 (a int, b int)
660
partition by list columns(a,b)
661
subpartition by hash (b)
663
(partition p0 values in ((0,0), (1,1)),
664
partition p1 values in ((1000,1000)));
665
insert into t1 values (1000,1000);
667
create table t1 (a char, b char, c char)
668
partition by range columns(a,b,c)
669
( partition p0 values less than ('a','b','c'));
670
alter table t1 add partition
671
(partition p1 values less than ('b','c','d'));