2
# Simple test for the erroneos statements using the
3
# partition storage engine
5
-- source include/have_partition.inc
8
drop table if exists t1, t2;
11
let $MYSQLD_DATADIR= `SELECT @@datadir`;
14
--echo # Bug#60039: crash when exchanging a partition on
15
--echo # nonpartitioned table with a view
17
CREATE TABLE t1 (a int);
18
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
19
--error ER_CHECK_NO_SUCH_TABLE
20
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE v1;
25
--echo # Bug#57924: crash when creating partitioned table with
26
--echo # multiple columns in the partition key
28
--error ER_SAME_NAME_PARTITION_FIELD
29
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b))
30
PARTITION BY KEY(a, b, a);
31
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b))
32
PARTITION BY KEY(A, b);
34
--error ER_SAME_NAME_PARTITION_FIELD
35
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a,b))
36
PARTITION BY KEY(a, b, A);
40
--echo # Bug#54483: valgrind errors when making warnings for multiline inserts
41
--echo # into partition
43
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
44
CREATE TABLE t1 (a VARBINARY(10))
45
PARTITION BY RANGE (DAYOFWEEK(a))
46
(PARTITION a1 VALUES LESS THAN (60));
47
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
48
CREATE TABLE t1 (a CHAR(10))
49
PARTITION BY RANGE (DAYOFWEEK(a))
50
(PARTITION a1 VALUES LESS THAN (60));
51
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
52
CREATE TABLE t1 (a VARCHAR(10))
53
PARTITION BY RANGE (DAYOFWEEK(a))
54
(PARTITION a1 VALUES LESS THAN (60));
55
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
56
CREATE TABLE t1 (a TIMESTAMP)
57
PARTITION BY RANGE (DAYOFWEEK(a))
58
(PARTITION a1 VALUES LESS THAN (60));
59
CREATE TABLE t1 (a DATE)
60
PARTITION BY RANGE (DAYOFWEEK(a))
61
(PARTITION a1 VALUES LESS THAN (60));
62
INSERT INTO t1 VALUES ('test'),('a'),('5');
65
CREATE TABLE t1 (a DATETIME)
66
PARTITION BY RANGE (DAYOFWEEK(a))
67
(PARTITION a1 VALUES LESS THAN (60));
68
INSERT INTO t1 VALUES ('test'),('a'),('5');
71
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
72
CREATE TABLE t1 (a TIME)
73
PARTITION BY RANGE (DAYOFWEEK(a))
74
(PARTITION a1 VALUES LESS THAN (60));
77
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
78
CREATE TABLE t1 (a TIME)
79
PARTITION BY HASH (TO_DAYS(a));
80
CREATE TABLE t1 (a DATE)
81
PARTITION BY HASH (TO_DAYS(a));
83
CREATE TABLE t1 (a DATETIME)
84
PARTITION BY HASH (TO_DAYS(a));
86
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
87
CREATE TABLE t1 (a VARCHAR(10))
88
PARTITION BY HASH (TO_DAYS(a));
89
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
90
CREATE TABLE t1 (a INT)
91
PARTITION BY HASH (TO_DAYS(a));
93
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
94
CREATE TABLE t1 (a TIME)
95
PARTITION BY HASH (DAYOFMONTH(a));
96
CREATE TABLE t1 (a DATE)
97
PARTITION BY HASH (DAYOFMONTH(a));
99
CREATE TABLE t1 (a DATETIME)
100
PARTITION BY HASH (DAYOFMONTH(a));
102
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
103
CREATE TABLE t1 (a VARCHAR(10))
104
PARTITION BY HASH (DAYOFMONTH(a));
105
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
106
CREATE TABLE t1 (a INT)
107
PARTITION BY HASH (DAYOFMONTH(a));
109
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
110
CREATE TABLE t1 (a TIME)
111
PARTITION BY HASH (MONTH(a));
112
CREATE TABLE t1 (a DATE)
113
PARTITION BY HASH (MONTH(a));
115
CREATE TABLE t1 (a DATETIME)
116
PARTITION BY HASH (MONTH(a));
118
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
119
CREATE TABLE t1 (a VARCHAR(10))
120
PARTITION BY HASH (MONTH(a));
121
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
122
CREATE TABLE t1 (a INT)
123
PARTITION BY HASH (MONTH(a));
125
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
126
CREATE TABLE t1 (a TIME)
127
PARTITION BY HASH (DAYOFYEAR(a));
128
CREATE TABLE t1 (a DATE)
129
PARTITION BY HASH (DAYOFYEAR(a));
131
CREATE TABLE t1 (a DATETIME)
132
PARTITION BY HASH (DAYOFYEAR(a));
134
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
135
CREATE TABLE t1 (a VARCHAR(10))
136
PARTITION BY HASH (DAYOFYEAR(a));
137
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
138
CREATE TABLE t1 (a INT)
139
PARTITION BY HASH (DAYOFYEAR(a));
141
CREATE TABLE t1 (a TIME)
142
PARTITION BY HASH (HOUR(a));
144
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
145
CREATE TABLE t1 (a DATE)
146
PARTITION BY HASH (HOUR(a));
147
CREATE TABLE t1 (a DATETIME)
148
PARTITION BY HASH (HOUR(a));
150
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
151
CREATE TABLE t1 (a VARCHAR(10))
152
PARTITION BY HASH (HOUR(a));
153
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
154
CREATE TABLE t1 (a INT)
155
PARTITION BY HASH (HOUR(a));
157
CREATE TABLE t1 (a TIME)
158
PARTITION BY HASH (MINUTE(a));
160
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
161
CREATE TABLE t1 (a DATE)
162
PARTITION BY HASH (MINUTE(a));
163
CREATE TABLE t1 (a DATETIME)
164
PARTITION BY HASH (MINUTE(a));
166
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
167
CREATE TABLE t1 (a VARCHAR(10))
168
PARTITION BY HASH (MINUTE(a));
169
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
170
CREATE TABLE t1 (a INT)
171
PARTITION BY HASH (MINUTE(a));
173
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
174
CREATE TABLE t1 (a TIME)
175
PARTITION BY HASH (QUARTER(a));
176
CREATE TABLE t1 (a DATE)
177
PARTITION BY HASH (QUARTER(a));
179
CREATE TABLE t1 (a DATETIME)
180
PARTITION BY HASH (QUARTER(a));
182
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
183
CREATE TABLE t1 (a VARCHAR(10))
184
PARTITION BY HASH (QUARTER(a));
185
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
186
CREATE TABLE t1 (a INT)
187
PARTITION BY HASH (QUARTER(a));
189
CREATE TABLE t1 (a TIME)
190
PARTITION BY HASH (SECOND(a));
192
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
193
CREATE TABLE t1 (a DATE)
194
PARTITION BY HASH (SECOND(a));
195
CREATE TABLE t1 (a DATETIME)
196
PARTITION BY HASH (SECOND(a));
198
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
199
CREATE TABLE t1 (a VARCHAR(10))
200
PARTITION BY HASH (SECOND(a));
201
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
202
CREATE TABLE t1 (a INT)
203
PARTITION BY HASH (SECOND(a));
205
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
206
CREATE TABLE t1 (a TIME)
207
PARTITION BY HASH (YEARWEEK(a));
208
CREATE TABLE t1 (a DATE)
209
PARTITION BY HASH (YEARWEEK(a));
211
CREATE TABLE t1 (a DATETIME)
212
PARTITION BY HASH (YEARWEEK(a));
214
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
215
CREATE TABLE t1 (a VARCHAR(10))
216
PARTITION BY HASH (YEARWEEK(a));
217
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
218
CREATE TABLE t1 (a INT)
219
PARTITION BY HASH (YEARWEEK(a));
221
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
222
CREATE TABLE t1 (a TIME)
223
PARTITION BY HASH (WEEKDAY(a));
224
CREATE TABLE t1 (a DATE)
225
PARTITION BY HASH (WEEKDAY(a));
227
CREATE TABLE t1 (a DATETIME)
228
PARTITION BY HASH (WEEKDAY(a));
230
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
231
CREATE TABLE t1 (a VARCHAR(10))
232
PARTITION BY HASH (WEEKDAY(a));
233
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
234
CREATE TABLE t1 (a INT)
235
PARTITION BY HASH (WEEKDAY(a));
237
--echo # TO_SECONDS() is added in 5.5.
238
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
239
CREATE TABLE t1 (a TIME)
240
PARTITION BY HASH (TO_SECONDS(a));
241
CREATE TABLE t1 (a DATE)
242
PARTITION BY HASH (TO_SECONDS(a));
244
CREATE TABLE t1 (a DATETIME)
245
PARTITION BY HASH (TO_SECONDS(a));
247
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
248
CREATE TABLE t1 (a VARCHAR(10))
249
PARTITION BY HASH (TO_SECONDS(a));
250
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
251
CREATE TABLE t1 (a INT)
252
PARTITION BY HASH (TO_SECONDS(a));
254
CREATE TABLE t1 (a TIME)
255
PARTITION BY HASH (TIME_TO_SEC(a));
257
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
258
CREATE TABLE t1 (a DATE)
259
PARTITION BY HASH (TIME_TO_SEC(a));
260
CREATE TABLE t1 (a DATETIME)
261
PARTITION BY HASH (TIME_TO_SEC(a));
263
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
264
CREATE TABLE t1 (a VARCHAR(10))
265
PARTITION BY HASH (TIME_TO_SEC(a));
266
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
267
CREATE TABLE t1 (a INT)
268
PARTITION BY HASH (TIME_TO_SEC(a));
270
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
271
CREATE TABLE t1 (a TIME)
272
PARTITION BY HASH (FROM_DAYS(a));
273
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
274
CREATE TABLE t1 (a DATE)
275
PARTITION BY HASH (FROM_DAYS(a));
276
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
277
CREATE TABLE t1 (a DATETIME)
278
PARTITION BY HASH (FROM_DAYS(a));
279
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
280
CREATE TABLE t1 (a VARCHAR(10))
281
PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
282
--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
283
CREATE TABLE t1 (a VARCHAR(10))
284
PARTITION BY HASH (FROM_DAYS(a));
285
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
286
CREATE TABLE t1 (a INT)
287
PARTITION BY HASH (TO_DAYS(FROM_DAYS(a)));
288
--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
289
CREATE TABLE t1 (a INT)
290
PARTITION BY HASH (FROM_DAYS(a));
292
CREATE TABLE t1 (a TIME)
293
PARTITION BY HASH (MICROSECOND(a));
295
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
296
CREATE TABLE t1 (a DATE)
297
PARTITION BY HASH (MICROSECOND(a));
298
CREATE TABLE t1 (a DATETIME)
299
PARTITION BY HASH (MICROSECOND(a));
301
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
302
CREATE TABLE t1 (a VARCHAR(10))
303
PARTITION BY HASH (MICROSECOND(a));
304
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
305
CREATE TABLE t1 (a INT)
306
PARTITION BY HASH (MICROSECOND(a));
308
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
311
`extracted_week` int,
314
`default_week_format` int)
315
PARTITION BY LIST (EXTRACT(WEEK FROM date) % 3)
316
(PARTITION p0 VALUES IN (0),
317
PARTITION p1 VALUES IN (1),
318
PARTITION p2 VALUES IN (2));
321
`extracted_week` int,
324
`default_week_format` int);
325
SET @old_default_week_format := @@default_week_format;
326
SET default_week_format = 0;
327
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
328
SET default_week_format = 1;
329
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
330
SET default_week_format = 2;
331
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
332
SET default_week_format = 3;
333
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
334
SET default_week_format = 4;
335
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
336
SET default_week_format = 5;
337
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
338
SET default_week_format = 6;
339
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
340
SET default_week_format = 7;
341
INSERT INTO t1 VALUES ('2000-01-01', EXTRACT(WEEK FROM '2000-01-01'), YEARWEEK('2000-01-01'), WEEK('2000-01-01'), @@default_week_format);
343
SET default_week_format = @old_default_week_format;
346
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
347
CREATE TABLE t1 (a TIME)
348
PARTITION BY HASH (EXTRACT(YEAR FROM a));
349
CREATE TABLE t1 (a DATE)
350
PARTITION BY HASH (EXTRACT(YEAR FROM a));
352
CREATE TABLE t1 (a DATETIME)
353
PARTITION BY HASH (EXTRACT(YEAR FROM a));
355
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
356
CREATE TABLE t1 (a VARCHAR(10))
357
PARTITION BY HASH (EXTRACT(YEAR FROM a));
358
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
359
CREATE TABLE t1 (a INT)
360
PARTITION BY HASH (EXTRACT(YEAR FROM a));
362
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
363
CREATE TABLE t1 (a TIME)
364
PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
365
CREATE TABLE t1 (a DATE)
366
PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
368
CREATE TABLE t1 (a DATETIME)
369
PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
371
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
372
CREATE TABLE t1 (a VARCHAR(10))
373
PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
374
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
375
CREATE TABLE t1 (a INT)
376
PARTITION BY HASH (EXTRACT(YEAR_MONTH FROM a));
378
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
379
CREATE TABLE t1 (a TIME)
380
PARTITION BY HASH (EXTRACT(QUARTER FROM a));
381
CREATE TABLE t1 (a DATE)
382
PARTITION BY HASH (EXTRACT(QUARTER FROM a));
384
CREATE TABLE t1 (a DATETIME)
385
PARTITION BY HASH (EXTRACT(QUARTER FROM a));
387
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
388
CREATE TABLE t1 (a VARCHAR(10))
389
PARTITION BY HASH (EXTRACT(QUARTER FROM a));
390
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
391
CREATE TABLE t1 (a INT)
392
PARTITION BY HASH (EXTRACT(QUARTER FROM a));
394
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
395
CREATE TABLE t1 (a TIME)
396
PARTITION BY HASH (EXTRACT(MONTH FROM a));
397
CREATE TABLE t1 (a DATE)
398
PARTITION BY HASH (EXTRACT(MONTH FROM a));
400
CREATE TABLE t1 (a DATETIME)
401
PARTITION BY HASH (EXTRACT(MONTH FROM a));
403
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
404
CREATE TABLE t1 (a VARCHAR(10))
405
PARTITION BY HASH (EXTRACT(MONTH FROM a));
406
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
407
CREATE TABLE t1 (a INT)
408
PARTITION BY HASH (EXTRACT(MONTH FROM a));
410
--echo # EXTRACT(WEEK...) is disallowed, see bug#57071.
411
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
412
CREATE TABLE t1 (a TIME)
413
PARTITION BY HASH (EXTRACT(WEEK FROM a));
414
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
415
CREATE TABLE t1 (a DATE)
416
PARTITION BY HASH (EXTRACT(WEEK FROM a));
417
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
418
CREATE TABLE t1 (a DATETIME)
419
PARTITION BY HASH (EXTRACT(WEEK FROM a));
420
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
421
CREATE TABLE t1 (a VARCHAR(10))
422
PARTITION BY HASH (EXTRACT(WEEK FROM a));
423
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
424
CREATE TABLE t1 (a INT)
425
PARTITION BY HASH (EXTRACT(WEEK FROM a));
427
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
428
CREATE TABLE t1 (a TIME)
429
PARTITION BY HASH (EXTRACT(DAY FROM a));
430
CREATE TABLE t1 (a DATE)
431
PARTITION BY HASH (EXTRACT(DAY FROM a));
433
CREATE TABLE t1 (a DATETIME)
434
PARTITION BY HASH (EXTRACT(DAY FROM a));
436
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
437
CREATE TABLE t1 (a VARCHAR(10))
438
PARTITION BY HASH (EXTRACT(DAY FROM a));
439
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
440
CREATE TABLE t1 (a INT)
441
PARTITION BY HASH (EXTRACT(DAY FROM a));
443
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
444
CREATE TABLE t1 (a TIME)
445
PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
446
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
447
CREATE TABLE t1 (a DATE)
448
PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
449
CREATE TABLE t1 (a DATETIME)
450
PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
452
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
453
CREATE TABLE t1 (a VARCHAR(10))
454
PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
455
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
456
CREATE TABLE t1 (a INT)
457
PARTITION BY HASH (EXTRACT(DAY_HOUR FROM a));
459
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
460
CREATE TABLE t1 (a TIME)
461
PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
462
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
463
CREATE TABLE t1 (a DATE)
464
PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
465
CREATE TABLE t1 (a DATETIME)
466
PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
468
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
469
CREATE TABLE t1 (a VARCHAR(10))
470
PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
471
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
472
CREATE TABLE t1 (a INT)
473
PARTITION BY HASH (EXTRACT(DAY_MINUTE FROM a));
475
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
476
CREATE TABLE t1 (a TIME)
477
PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
478
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
479
CREATE TABLE t1 (a DATE)
480
PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
481
CREATE TABLE t1 (a DATETIME)
482
PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
484
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
485
CREATE TABLE t1 (a VARCHAR(10))
486
PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
487
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
488
CREATE TABLE t1 (a INT)
489
PARTITION BY HASH (EXTRACT(DAY_SECOND FROM a));
491
CREATE TABLE t1 (a TIME)
492
PARTITION BY HASH (EXTRACT(HOUR FROM a));
494
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
495
CREATE TABLE t1 (a DATE)
496
PARTITION BY HASH (EXTRACT(HOUR FROM a));
497
CREATE TABLE t1 (a DATETIME)
498
PARTITION BY HASH (EXTRACT(HOUR FROM a));
500
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
501
CREATE TABLE t1 (a VARCHAR(10))
502
PARTITION BY HASH (EXTRACT(HOUR FROM a));
503
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
504
CREATE TABLE t1 (a INT)
505
PARTITION BY HASH (EXTRACT(HOUR FROM a));
507
CREATE TABLE t1 (a TIME)
508
PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
510
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
511
CREATE TABLE t1 (a DATE)
512
PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
513
CREATE TABLE t1 (a DATETIME)
514
PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
516
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
517
CREATE TABLE t1 (a VARCHAR(10))
518
PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
519
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
520
CREATE TABLE t1 (a INT)
521
PARTITION BY HASH (EXTRACT(HOUR_MINUTE FROM a));
523
CREATE TABLE t1 (a TIME)
524
PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
526
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
527
CREATE TABLE t1 (a DATE)
528
PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
529
CREATE TABLE t1 (a DATETIME)
530
PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
532
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
533
CREATE TABLE t1 (a VARCHAR(10))
534
PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
535
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
536
CREATE TABLE t1 (a INT)
537
PARTITION BY HASH (EXTRACT(HOUR_SECOND FROM a));
539
CREATE TABLE t1 (a TIME)
540
PARTITION BY HASH (EXTRACT(MINUTE FROM a));
542
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
543
CREATE TABLE t1 (a DATE)
544
PARTITION BY HASH (EXTRACT(MINUTE FROM a));
545
CREATE TABLE t1 (a DATETIME)
546
PARTITION BY HASH (EXTRACT(MINUTE FROM a));
548
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
549
CREATE TABLE t1 (a VARCHAR(10))
550
PARTITION BY HASH (EXTRACT(MINUTE FROM a));
551
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
552
CREATE TABLE t1 (a INT)
553
PARTITION BY HASH (EXTRACT(MINUTE FROM a));
555
CREATE TABLE t1 (a TIME)
556
PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
558
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
559
CREATE TABLE t1 (a DATE)
560
PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
561
CREATE TABLE t1 (a DATETIME)
562
PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
564
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
565
CREATE TABLE t1 (a VARCHAR(10))
566
PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
567
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
568
CREATE TABLE t1 (a INT)
569
PARTITION BY HASH (EXTRACT(MINUTE_SECOND FROM a));
571
CREATE TABLE t1 (a TIME)
572
PARTITION BY HASH (EXTRACT(SECOND FROM a));
574
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
575
CREATE TABLE t1 (a DATE)
576
PARTITION BY HASH (EXTRACT(SECOND FROM a));
577
CREATE TABLE t1 (a DATETIME)
578
PARTITION BY HASH (EXTRACT(SECOND FROM a));
580
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
581
CREATE TABLE t1 (a VARCHAR(10))
582
PARTITION BY HASH (EXTRACT(SECOND FROM a));
583
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
584
CREATE TABLE t1 (a INT)
585
PARTITION BY HASH (EXTRACT(SECOND FROM a));
587
CREATE TABLE t1 (a TIME)
588
PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
590
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
591
CREATE TABLE t1 (a DATE)
592
PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
593
CREATE TABLE t1 (a DATETIME)
594
PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
596
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
597
CREATE TABLE t1 (a VARCHAR(10))
598
PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
599
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
600
CREATE TABLE t1 (a INT)
601
PARTITION BY HASH (EXTRACT(MICROSECOND FROM a));
603
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
604
CREATE TABLE t1 (a TIME)
605
PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
606
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
607
CREATE TABLE t1 (a DATE)
608
PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
609
CREATE TABLE t1 (a DATETIME)
610
PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
612
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
613
CREATE TABLE t1 (a VARCHAR(10))
614
PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
615
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
616
CREATE TABLE t1 (a INT)
617
PARTITION BY HASH (EXTRACT(DAY_MICROSECOND FROM a));
619
CREATE TABLE t1 (a TIME)
620
PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
622
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
623
CREATE TABLE t1 (a DATE)
624
PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
625
CREATE TABLE t1 (a DATETIME)
626
PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
628
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
629
CREATE TABLE t1 (a VARCHAR(10))
630
PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
631
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
632
CREATE TABLE t1 (a INT)
633
PARTITION BY HASH (EXTRACT(HOUR_MICROSECOND FROM a));
635
CREATE TABLE t1 (a TIME)
636
PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
638
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
639
CREATE TABLE t1 (a DATE)
640
PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
641
CREATE TABLE t1 (a DATETIME)
642
PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
644
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
645
CREATE TABLE t1 (a VARCHAR(10))
646
PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
647
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
648
CREATE TABLE t1 (a INT)
649
PARTITION BY HASH (EXTRACT(MINUTE_MICROSECOND FROM a));
651
CREATE TABLE t1 (a TIME)
652
PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
654
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
655
CREATE TABLE t1 (a DATE)
656
PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
657
CREATE TABLE t1 (a DATETIME)
658
PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
660
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
661
CREATE TABLE t1 (a VARCHAR(10))
662
PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
663
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
664
CREATE TABLE t1 (a INT)
665
PARTITION BY HASH (EXTRACT(SECOND_MICROSECOND FROM a));
667
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
668
CREATE TABLE t1 (a TIME, b DATE)
669
PARTITION BY HASH (DATEDIFF(a, b));
670
CREATE TABLE t1 (a DATE, b DATETIME)
671
PARTITION BY HASH (DATEDIFF(a, b));
673
CREATE TABLE t1 (a DATETIME, b DATE)
674
PARTITION BY HASH (DATEDIFF(a, b));
676
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
677
CREATE TABLE t1 (a DATE, b VARCHAR(10))
678
PARTITION BY HASH (DATEDIFF(a, b));
679
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
680
CREATE TABLE t1 (a INT, b DATETIME)
681
PARTITION BY HASH (DATEDIFF(a, b));
683
CREATE TABLE t1 (a TIME)
684
PARTITION BY HASH (TIME_TO_SEC(a));
686
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
687
CREATE TABLE t1 (a DATE)
688
PARTITION BY HASH (TIME_TO_SEC(a));
689
CREATE TABLE t1 (a DATETIME)
690
PARTITION BY HASH (TIME_TO_SEC(a));
692
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
693
CREATE TABLE t1 (a VARCHAR(10))
694
PARTITION BY HASH (TIME_TO_SEC(a));
695
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
696
CREATE TABLE t1 (a INT)
697
PARTITION BY HASH (TIME_TO_SEC(a));
700
--echo # Bug#50036: Inconsistent errors when using TIMESTAMP
701
--echo # columns/expressions
703
--echo # 1. correct and appropriate errors in light of
704
--echo # the fix for BUG#42849:
705
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
706
CREATE TABLE t1 (c TIMESTAMP)
707
PARTITION BY RANGE (TO_DAYS(c))
708
(PARTITION p0 VALUES LESS THAN (10000),
709
PARTITION p1 VALUES LESS THAN (MAXVALUE));
710
CREATE TABLE t2 (c TIMESTAMP);
711
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
713
PARTITION BY RANGE (TO_DAYS(c))
714
(PARTITION p0 VALUES LESS THAN (10000),
715
PARTITION p1 VALUES LESS THAN (MAXVALUE));
717
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
718
CREATE TABLE t1 (c TIMESTAMP)
719
PARTITION BY RANGE COLUMNS(c)
720
(PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
721
PARTITION p1 VALUES LESS THAN (MAXVALUE));
722
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
723
ALTER TABLE t2 PARTITION BY RANGE COLUMNS(c)
724
(PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
725
PARTITION p1 VALUES LESS THAN (MAXVALUE));
728
--echo # 2. These errors where questionable before the fix:
730
--echo # VALUES clause are checked first, clearified the error message.
731
--error ER_VALUES_IS_NOT_INT_TYPE_ERROR
732
CREATE TABLE t1 (c TIMESTAMP)
733
PARTITION BY RANGE (c)
734
(PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
735
PARTITION p1 VALUES LESS THAN (MAXVALUE));
737
--echo # TIMESTAMP is not INT (e.g. UNIX_TIMESTAMP).
738
--error ER_VALUES_IS_NOT_INT_TYPE_ERROR
739
CREATE TABLE t1 (c TIMESTAMP)
740
PARTITION BY RANGE (UNIX_TIMESTAMP(c))
741
(PARTITION p0 VALUES LESS THAN ('2000-01-01 00:00:00'),
742
PARTITION p1 VALUES LESS THAN (MAXVALUE));
744
CREATE TABLE t1 (c TIMESTAMP)
745
PARTITION BY RANGE (UNIX_TIMESTAMP(c))
746
(PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2000-01-01 00:00:00')),
747
PARTITION p1 VALUES LESS THAN (MAXVALUE));
750
--echo # Changed error from ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR
751
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
752
CREATE TABLE t1 (c TIMESTAMP)
753
PARTITION BY HASH (c) PARTITIONS 4;
755
--echo # Moved to partition_myisam, since it was MyISAM specific
756
--echo # Added test with existing TIMESTAMP partitioning (when it was allowed).
759
--echo # Bug#49477: Assertion `0' failed in ha_partition.cc:5530
760
--echo # with temporary table and partitions
762
CREATE TABLE t1 (a INT) PARTITION BY HASH(a);
763
--error ER_PARTITION_NO_TEMPORARY
764
CREATE TEMPORARY TABLE tmp_t1 LIKE t1;
767
--echo # Bug#42954: SQL MODE 'NO_DIR_IN_CREATE' does not work with
768
--echo # subpartitions
769
SET @org_mode=@@sql_mode;
770
SET @@sql_mode='NO_DIR_IN_CREATE';
772
CREATE TABLE t1 (id INT, purchased DATE)
773
PARTITION BY RANGE(YEAR(purchased))
774
SUBPARTITION BY HASH(TO_DAYS(purchased))
775
(PARTITION p0 VALUES LESS THAN MAXVALUE
776
DATA DIRECTORY = '/tmp/not-existing'
777
INDEX DIRECTORY = '/tmp/not-existing');
778
--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
779
SHOW CREATE TABLE t1;
781
CREATE TABLE t1 (id INT, purchased DATE)
782
PARTITION BY RANGE(YEAR(purchased))
783
SUBPARTITION BY HASH(TO_DAYS(purchased)) SUBPARTITIONS 2
784
(PARTITION p0 VALUES LESS THAN MAXVALUE
786
DATA DIRECTORY = '/tmp/not-existing'
787
INDEX DIRECTORY = '/tmp/not-existing',
789
--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
790
SHOW CREATE TABLE t1;
792
CREATE TABLE t1 (id INT, purchased DATE)
793
PARTITION BY RANGE(YEAR(purchased))
794
(PARTITION p0 VALUES LESS THAN MAXVALUE
795
DATA DIRECTORY = '/tmp/not-existing'
796
INDEX DIRECTORY = '/tmp/not-existing');
797
--replace_result MyISAM <curr_engine> InnoDB <curr_engine>
798
SHOW CREATE TABLE t1;
800
SET @@sql_mode= @org_mode;
803
# Bug#38719: Partitioning returns a different error code for a
804
# duplicate key error
805
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a));
806
-- error ER_DUP_ENTRY
807
INSERT INTO t1 VALUES (1),(1);
809
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a))
810
PARTITION BY KEY (a) PARTITIONS 2;
811
-- error ER_DUP_ENTRY
812
INSERT INTO t1 VALUES (1),(1);
817
# Incorrect error, 1467, for syntax error when creating partition
818
--error ER_PARTITION_REQUIRES_VALUES_ERROR
822
PARTITION BY RANGE (a)
824
PARTITION p0 VALUES LESS THAN (1),
825
PARTITION p1 VALU ES LESS THAN (2)
829
# Partition by key stand-alone error
831
--error ER_PARSE_ERROR
832
partition by list (a)
834
(partition x1 values in (1,2,9,4) tablespace ts1,
835
partition x2 values in (3, 11, 5, 7) tablespace ts2,
836
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
839
# Partition by key list, number of partitions defined, no partition defined
841
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
847
partition by list (a)
851
# Partition by key list, wrong result type
853
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
859
partition by list (sin(a))
861
(partition x1 values in (1,2,9,4) tablespace ts1,
862
partition x2 values in (3, 11, 5, 7) tablespace ts2,
863
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
866
# Partition by key, partition function not allowed
868
--error ER_PARSE_ERROR
874
partition by key (a+2)
876
(partition x1 tablespace ts1,
877
partition x2 tablespace ts2,
878
partition x3 tablespace ts3);
881
# Partition by key, no partition name
883
--error ER_PARSE_ERROR
891
(partition tablespace ts1,
892
partition x2 tablespace ts2,
893
partition x3 tablespace ts3);
896
# Partition by range columns, invalid field in field list
898
--error ER_FIELD_NOT_FOUND_PART_ERROR
904
partition by range columns (a,d)
905
(partition x1 VALUES LESS THAN (1,1),
906
partition x2 VALUES LESS THAN (2,2),
907
partition x3 VALUES LESS THAN (3,3));
909
let $MYSQLD_DATADIR= `select @@datadir`;
910
select load_file('$MYSQLD_DATADIR/test/t1.par');
913
# Partition by key, invalid field in field list
915
--error ER_FIELD_NOT_FOUND_PART_ERROR
921
partition by key (a,d)
923
(partition x1 tablespace ts1,
924
partition x2 tablespace ts2,
925
partition x3 tablespace ts3);
927
select load_file('$MYSQLD_DATADIR/test/t1.par');
929
# Partition by hash, invalid field in function
931
--error ER_BAD_FIELD_ERROR
937
partition by hash (a + d)
939
(partition x1 tablespace ts1,
940
partition x2 tablespace ts2,
941
partition x3 tablespace ts3);
944
# Partition by hash, invalid result type
946
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
952
partition by hash (sin(a))
954
(partition x1 tablespace ts1,
955
partition x2 tablespace ts2,
956
partition x3 tablespace ts3);
959
# Partition by key specified 3 partitions but only defined 2 => error
961
--error ER_PARSE_ERROR
969
(partition x1, partition x2);
972
# Partition by hash, random function
974
--error ER_PARSE_ERROR
980
partition by hash (rand(a))
982
(partition x1, partition x2);
985
# Partition by range, random function
987
--error ER_PARSE_ERROR
993
partition by range (rand(a))
995
(partition x1 values less than (0), partition x2 values less than (2));
998
# Partition by list, random function
1000
--error ER_PARSE_ERROR
1006
partition by list (rand(a))
1008
(partition x1 values in (1), partition x2 values in (2));
1011
# Partition by hash, values less than error
1013
--error ER_PARTITION_WRONG_VALUES_ERROR
1019
partition by hash (a)
1021
(partition x1 values less than (4),
1022
partition x2 values less than (5));
1023
select load_file('$MYSQLD_DATADIR/test/t1.par');
1026
# Partition by hash, values in error
1028
--error ER_PARTITION_WRONG_VALUES_ERROR
1034
partition by hash (a)
1036
(partition x1 values in (4),
1037
partition x2 values in (5));
1038
select load_file('$MYSQLD_DATADIR/test/t1.par');
1041
# Partition by hash, values in error
1043
--error ER_PARTITION_WRONG_VALUES_ERROR
1049
partition by hash (a)
1051
(partition x1 values in (4,6),
1052
partition x2 values in (5,7));
1053
select load_file('$MYSQLD_DATADIR/test/t1.par');
1056
# Subpartition by key, no partitions defined, single field
1058
--error ER_SUBPARTITION_ERROR
1064
partition by key (a)
1065
subpartition by key (b);
1066
select load_file('$MYSQLD_DATADIR/test/t1.par');
1069
# Subpartition by key, no partitions defined, list of fields
1071
--error ER_SUBPARTITION_ERROR
1077
partition by key (a)
1078
subpartition by key (a, b);
1079
select load_file('$MYSQLD_DATADIR/test/t1.par');
1082
# Subpartition by hash, no partitions defined
1084
--error ER_SUBPARTITION_ERROR
1090
partition by key (a)
1091
subpartition by hash (a+b);
1092
select load_file('$MYSQLD_DATADIR/test/t1.par');
1095
# Subpartition by key, no partitions defined, single field
1097
--error ER_SUBPARTITION_ERROR
1103
partition by key (a)
1104
subpartition by key (b);
1105
select load_file('$MYSQLD_DATADIR/test/t1.par');
1108
# Subpartition by key, no partitions defined, list of fields
1110
--error ER_SUBPARTITION_ERROR
1116
partition by key (a)
1117
subpartition by key (a, b);
1118
select load_file('$MYSQLD_DATADIR/test/t1.par');
1121
# Subpartition by hash, no partitions defined
1123
--error ER_SUBPARTITION_ERROR
1129
partition by key (a)
1130
subpartition by hash (a+b);
1131
select load_file('$MYSQLD_DATADIR/test/t1.par');
1134
# Subpartition by hash, no partitions defined, wrong subpartition function
1136
--error ER_PARSE_ERROR
1142
partition by key (a)
1143
subpartition by hash (rand(a+b));
1146
# Subpartition by hash, wrong subpartition function
1148
--error ER_SUBPARTITION_ERROR
1154
partition by key (a)
1155
subpartition by hash (sin(a+b))
1156
(partition x1 (subpartition x11, subpartition x12),
1157
partition x2 (subpartition x21, subpartition x22));
1158
select load_file('$MYSQLD_DATADIR/test/t1.par');
1161
# Subpartition by hash, no partitions defined, wrong subpartition function
1163
--error ER_PARSE_ERROR
1169
partition by range (a)
1170
subpartition by key (a+b)
1171
(partition x1 values less than (1) (subpartition x11, subpartition x12),
1172
partition x2 values less than (2) (subpartition x21, subpartition x22));
1175
# Subpartition by hash, no partitions defined, wrong subpartition function
1177
--error ER_FIELD_NOT_FOUND_PART_ERROR
1183
partition by range (a)
1184
subpartition by key (a,d)
1185
(partition x1 values less than (1) (subpartition x11, subpartition x12),
1186
partition x2 values less than (2) (subpartition x21, subpartition x22));
1187
select load_file('$MYSQLD_DATADIR/test/t1.par');
1190
# Subpartition by hash, no partitions defined, wrong subpartition function
1192
--error ER_SUBPARTITION_ERROR
1198
partition by key (a)
1199
subpartition by hash (3+4);
1202
# Subpartition by hash, no partitions defined, wrong subpartition function
1204
--error ER_BAD_FIELD_ERROR
1210
partition by range (a)
1211
subpartition by hash (a+d)
1212
(partition x1 values less than (1) (subpartition x11, subpartition x12),
1213
partition x2 values less than (2) (subpartition x21, subpartition x22));
1216
# Partition by range, no partition => error
1218
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
1224
partition by range (a);
1225
select load_file('$MYSQLD_DATADIR/test/t1.par');
1228
# Partition by range, invalid field in function
1230
--error ER_BAD_FIELD_ERROR
1236
partition by range (a+d)
1238
(partition x1 values less than (4) tablespace ts1,
1239
partition x2 values less than (8) tablespace ts2);
1242
# Partition by range, inconsistent partition function and constants
1244
--error ER_VALUES_IS_NOT_INT_TYPE_ERROR
1250
partition by range (a)
1252
(partition x1 values less than (4.0) tablespace ts1,
1253
partition x2 values less than (8) tablespace ts2);
1256
# Partition by range, constant partition function not allowed
1258
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1264
partition by range (3+4)
1266
(partition x1 values less than (4) tablespace ts1,
1267
partition x2 values less than (8) tablespace ts2);
1270
# Partition by range, no values less than definition
1272
--error ER_PARTITION_REQUIRES_VALUES_ERROR
1278
partition by range (a)
1280
(partition x1 values less than (4),
1284
# Partition by range, no values in definition allowed
1286
--error ER_PARTITION_WRONG_VALUES_ERROR
1292
partition by range (a)
1294
(partition x1 values in (4),
1298
# Partition by range, values in error
1300
--error ER_PARTITION_WRONG_VALUES_ERROR
1306
partition by range (a)
1308
(partition x1 values in (4),
1309
partition x2 values less than (5));
1312
# Partition by range, missing parenthesis
1314
--error ER_PARTITION_WRONG_VALUES_ERROR
1320
partition by list (a)
1322
(partition x1 values less than 4,
1323
partition x2 values less than (5));
1326
# Partition by range, maxvalue in wrong place
1328
--error ER_PARTITION_MAXVALUE_ERROR
1334
partition by range (a)
1336
(partition x1 values less than maxvalue,
1337
partition x2 values less than (5));
1340
# Partition by range, maxvalue in several places
1342
--error ER_PARTITION_MAXVALUE_ERROR
1348
partition by range (a)
1350
(partition x1 values less than maxvalue,
1351
partition x2 values less than maxvalue);
1354
# Partition by range, not increasing ranges
1356
--error ER_RANGE_NOT_INCREASING_ERROR
1362
partition by range (a)
1364
(partition x1 values less than (4),
1365
partition x2 values less than (3));
1368
# Partition by range, wrong result type of partition function
1370
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
1376
partition by range (sin(a))
1378
(partition x1 values less than (4),
1379
partition x2 values less than (5));
1382
# Subpartition by hash, wrong number of subpartitions
1384
--error ER_PARSE_ERROR
1390
partition by list (a)
1391
subpartition by hash (a+b)
1393
( partition x1 values in (1,2,4)
1394
( subpartition x11 nodegroup 0,
1395
subpartition x12 nodegroup 1),
1396
partition x2 values in (3,5,6)
1397
( subpartition x21 nodegroup 0,
1398
subpartition x22 nodegroup 1)
1402
# Subpartition by hash, wrong number of subpartitions
1404
--error ER_PARSE_ERROR
1410
partition by list (a)
1411
subpartition by hash (a+b)
1412
( partition x1 values in (1)
1413
( subpartition x11 nodegroup 0,
1414
subpartition xextra,
1415
subpartition x12 nodegroup 1),
1416
partition x2 values in (2)
1417
( subpartition x21 nodegroup 0,
1418
subpartition x22 nodegroup 1)
1422
# Subpartition by list => error
1424
--error ER_PARSE_ERROR
1430
partition by key (a)
1431
subpartition by list (a+b)
1433
( subpartition x11 engine myisam,
1434
subpartition x12 engine myisam),
1436
( subpartition x21 engine myisam,
1437
subpartition x22 engine myisam)
1441
# Subpartition by list => error
1443
--error ER_PARSE_ERROR
1449
partition by key (a)
1450
subpartition by list (a+b)
1452
( subpartition x11 engine myisam values in (0),
1453
subpartition x12 engine myisam values in (1)),
1455
( subpartition x21 engine myisam values in (0),
1456
subpartition x22 engine myisam values in (1))
1460
# Partition by list, no partition => error
1462
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
1468
partition by list (a);
1471
# Partition by list, constant partition function not allowed
1473
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1479
partition by list (3+4)
1481
(partition x1 values in (4) tablespace ts1,
1482
partition x2 values in (8) tablespace ts2);
1485
# Partition by list, invalid field in function
1487
--error ER_BAD_FIELD_ERROR
1493
partition by list (a+d)
1495
(partition x1 values in (4) tablespace ts1,
1496
partition x2 values in (8) tablespace ts2);
1499
# Partition by list, no values in definition
1501
--error ER_PARTITION_REQUIRES_VALUES_ERROR
1507
partition by list (a)
1509
(partition x1 values in (4),
1513
# Partition by list, values less than error
1515
--error ER_PARTITION_WRONG_VALUES_ERROR
1521
partition by list (a)
1523
(partition x1 values in (4),
1524
partition x2 values less than (5));
1527
# Partition by list, no values in definition
1529
--error ER_PARTITION_REQUIRES_VALUES_ERROR
1535
partition by list (a)
1537
(partition x1 values in (4,6),
1541
# Partition by list, duplicate values
1543
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
1549
partition by list (a)
1551
(partition x1 values in (4, 12+9),
1552
partition x2 values in (3, 21));
1555
# Partition by list, wrong constant result type (not INT)
1557
--error ER_VALUES_IS_NOT_INT_TYPE_ERROR
1563
partition by list (a)
1565
(partition x1 values in (4.0, 12+8),
1566
partition x2 values in (3, 21));
1569
# Partition by list, missing parenthesis
1571
--error ER_PARSE_ERROR
1577
partition by list (a)
1579
(partition x1 values in 4,
1580
partition x2 values in (5));
1583
# Bug #13439: Crash when LESS THAN (non-literal)
1585
--error ER_BAD_FIELD_ERROR
1586
CREATE TABLE t1 (a int)
1587
PARTITION BY RANGE (a)
1588
(PARTITION p0 VALUES LESS THAN (x1));
1591
# No partition for the given value
1593
CREATE TABLE t1(a int)
1594
PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN(5));
1595
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
1596
insert into t1 values (10);
1599
--error ER_PARTITION_CONST_DOMAIN_ERROR
1600
create table t1 (a bigint unsigned)
1601
partition by range (a)
1602
(partition p0 values less than (-1));
1604
# Bug 18198 Partitions: Verify that erroneus partition functions doesn't work
1606
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
1607
create table t1 (v varchar(12))
1608
partition by range (ascii(v))
1609
(partition p0 values less than (10));
1612
-- error ER_PARSE_ERROR
1613
create table t1 (a int)
1614
partition by hash (rand(a));
1615
-- error ER_PARSE_ERROR
1616
create table t1 (a int)
1617
partition by hash(CURTIME() + a);
1618
-- error ER_PARSE_ERROR
1619
create table t1 (a int)
1620
partition by hash (NOW()+a);
1621
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
1622
create table t1 (a int)
1623
partition by hash (extract(hour from convert_tz(a, '+00:00', '+00:00')));
1624
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
1625
create table t1 (a int)
1626
partition by range (a + (select count(*) from t1))
1627
(partition p1 values less than (1));
1628
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
1629
create table t1 (a char(10))
1630
partition by hash (extractvalue(a,'a'));
1633
--echo # Bug #42849: innodb crash with varying time_zone on partitioned
1634
--echo # timestamp primary key
1637
# A correctly partitioned table to test that trying to repartition it using
1638
# timezone-dependent expression will throw an error.
1639
CREATE TABLE old (a TIMESTAMP NOT NULL PRIMARY KEY)
1640
PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (
1641
PARTITION p VALUES LESS THAN (1219089600),
1642
PARTITION pmax VALUES LESS THAN MAXVALUE);
1644
# Check that allowed arithmetic/math functions involving TIMESTAMP values result
1645
# in ER_PARTITION_FUNC_NOT_ALLOWED_ERROR when used as a partitioning function
1647
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
1648
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1649
PARTITION BY RANGE (a) (
1650
PARTITION p VALUES LESS THAN (20080819),
1651
PARTITION pmax VALUES LESS THAN MAXVALUE);
1653
--error ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
1655
PARTITION BY RANGE (a) (
1656
PARTITION p VALUES LESS THAN (20080819),
1657
PARTITION pmax VALUES LESS THAN MAXVALUE);
1659
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1660
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1661
PARTITION BY RANGE (a+0) (
1662
PARTITION p VALUES LESS THAN (20080819),
1663
PARTITION pmax VALUES LESS THAN MAXVALUE);
1665
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1667
PARTITION BY RANGE (a+0) (
1668
PARTITION p VALUES LESS THAN (20080819),
1669
PARTITION pmax VALUES LESS THAN MAXVALUE);
1671
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1672
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1673
PARTITION BY RANGE (a % 2) (
1674
PARTITION p VALUES LESS THAN (20080819),
1675
PARTITION pmax VALUES LESS THAN MAXVALUE);
1677
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1679
PARTITION BY RANGE (a % 2) (
1680
PARTITION p VALUES LESS THAN (20080819),
1681
PARTITION pmax VALUES LESS THAN MAXVALUE);
1683
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1684
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1685
PARTITION BY RANGE (ABS(a)) (
1686
PARTITION p VALUES LESS THAN (20080819),
1687
PARTITION pmax VALUES LESS THAN MAXVALUE);
1689
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1691
PARTITION BY RANGE (ABS(a)) (
1692
PARTITION p VALUES LESS THAN (20080819),
1693
PARTITION pmax VALUES LESS THAN MAXVALUE);
1695
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1696
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1697
PARTITION BY RANGE (CEILING(a)) (
1698
PARTITION p VALUES LESS THAN (20080819),
1699
PARTITION pmax VALUES LESS THAN MAXVALUE);
1701
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1703
PARTITION BY RANGE (CEILING(a)) (
1704
PARTITION p VALUES LESS THAN (20080819),
1705
PARTITION pmax VALUES LESS THAN MAXVALUE);
1707
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1708
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1709
PARTITION BY RANGE (FLOOR(a)) (
1710
PARTITION p VALUES LESS THAN (20080819),
1711
PARTITION pmax VALUES LESS THAN MAXVALUE);
1713
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1715
PARTITION BY RANGE (FLOOR(a)) (
1716
PARTITION p VALUES LESS THAN (20080819),
1717
PARTITION pmax VALUES LESS THAN MAXVALUE);
1719
# Check that allowed date/time functions involving TIMESTAMP values result
1720
# in ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR when used as a partitioning function
1722
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1723
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1724
PARTITION BY RANGE (TO_DAYS(a)) (
1725
PARTITION p VALUES LESS THAN (733638),
1726
PARTITION pmax VALUES LESS THAN MAXVALUE);
1728
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1730
PARTITION BY RANGE (TO_DAYS(a)) (
1731
PARTITION p VALUES LESS THAN (733638),
1732
PARTITION pmax VALUES LESS THAN MAXVALUE);
1734
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1735
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1736
PARTITION BY RANGE (DAYOFYEAR(a)) (
1737
PARTITION p VALUES LESS THAN (231),
1738
PARTITION pmax VALUES LESS THAN MAXVALUE);
1740
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1742
PARTITION BY RANGE (DAYOFYEAR(a)) (
1743
PARTITION p VALUES LESS THAN (231),
1744
PARTITION pmax VALUES LESS THAN MAXVALUE);
1746
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1747
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1748
PARTITION BY RANGE (DAYOFMONTH(a)) (
1749
PARTITION p VALUES LESS THAN (19),
1750
PARTITION pmax VALUES LESS THAN MAXVALUE);
1752
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1754
PARTITION BY RANGE (DAYOFMONTH(a)) (
1755
PARTITION p VALUES LESS THAN (19),
1756
PARTITION pmax VALUES LESS THAN MAXVALUE);
1758
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1759
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1760
PARTITION BY RANGE (DAYOFWEEK(a)) (
1761
PARTITION p VALUES LESS THAN (3),
1762
PARTITION pmax VALUES LESS THAN MAXVALUE);
1764
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1766
PARTITION BY RANGE (DAYOFWEEK(a)) (
1767
PARTITION p VALUES LESS THAN (3),
1768
PARTITION pmax VALUES LESS THAN MAXVALUE);
1770
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1771
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1772
PARTITION BY RANGE (MONTH(a)) (
1773
PARTITION p VALUES LESS THAN (8),
1774
PARTITION pmax VALUES LESS THAN MAXVALUE);
1776
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1778
PARTITION BY RANGE (MONTH(a)) (
1779
PARTITION p VALUES LESS THAN (8),
1780
PARTITION pmax VALUES LESS THAN MAXVALUE);
1782
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1783
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1784
PARTITION BY RANGE (HOUR(a)) (
1785
PARTITION p VALUES LESS THAN (17),
1786
PARTITION pmax VALUES LESS THAN MAXVALUE);
1788
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1790
PARTITION BY RANGE (HOUR(a)) (
1791
PARTITION p VALUES LESS THAN (17),
1792
PARTITION pmax VALUES LESS THAN MAXVALUE);
1794
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1795
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1796
PARTITION BY RANGE (MINUTE(a)) (
1797
PARTITION p VALUES LESS THAN (55),
1798
PARTITION pmax VALUES LESS THAN MAXVALUE);
1800
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1802
PARTITION BY RANGE (MINUTE(a)) (
1803
PARTITION p VALUES LESS THAN (55),
1804
PARTITION pmax VALUES LESS THAN MAXVALUE);
1806
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1807
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1808
PARTITION BY RANGE (QUARTER(a)) (
1809
PARTITION p VALUES LESS THAN (3),
1810
PARTITION pmax VALUES LESS THAN MAXVALUE);
1812
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1814
PARTITION BY RANGE (QUARTER(a)) (
1815
PARTITION p VALUES LESS THAN (3),
1816
PARTITION pmax VALUES LESS THAN MAXVALUE);
1818
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1819
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1820
PARTITION BY RANGE (SECOND(a)) (
1821
PARTITION p VALUES LESS THAN (7),
1822
PARTITION pmax VALUES LESS THAN MAXVALUE);
1824
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1826
PARTITION BY RANGE (SECOND(a)) (
1827
PARTITION p VALUES LESS THAN (7),
1828
PARTITION pmax VALUES LESS THAN MAXVALUE);
1830
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1831
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1832
PARTITION BY RANGE (YEARWEEK(a)) (
1833
PARTITION p VALUES LESS THAN (200833),
1834
PARTITION pmax VALUES LESS THAN MAXVALUE);
1836
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1838
PARTITION BY RANGE (YEARWEEK(a)) (
1839
PARTITION p VALUES LESS THAN (200833),
1840
PARTITION pmax VALUES LESS THAN MAXVALUE);
1842
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1843
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1844
PARTITION BY RANGE (YEAR(a)) (
1845
PARTITION p VALUES LESS THAN (2008),
1846
PARTITION pmax VALUES LESS THAN MAXVALUE);
1848
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1850
PARTITION BY RANGE (YEAR(a)) (
1851
PARTITION p VALUES LESS THAN (2008),
1852
PARTITION pmax VALUES LESS THAN MAXVALUE);
1854
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1855
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1856
PARTITION BY RANGE (WEEKDAY(a)) (
1857
PARTITION p VALUES LESS THAN (3),
1858
PARTITION pmax VALUES LESS THAN MAXVALUE);
1860
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1862
PARTITION BY RANGE (WEEKDAY(a)) (
1863
PARTITION p VALUES LESS THAN (3),
1864
PARTITION pmax VALUES LESS THAN MAXVALUE);
1866
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1867
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1868
PARTITION BY RANGE (TIME_TO_SEC(a)) (
1869
PARTITION p VALUES LESS THAN (64507),
1870
PARTITION pmax VALUES LESS THAN MAXVALUE);
1872
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1874
PARTITION BY RANGE (TIME_TO_SEC(a)) (
1875
PARTITION p VALUES LESS THAN (64507),
1876
PARTITION pmax VALUES LESS THAN MAXVALUE);
1878
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1879
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1880
PARTITION BY RANGE (EXTRACT(DAY FROM a)) (
1881
PARTITION p VALUES LESS THAN (18),
1882
PARTITION pmax VALUES LESS THAN MAXVALUE);
1884
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1886
PARTITION BY RANGE (EXTRACT(DAY FROM a)) (
1887
PARTITION p VALUES LESS THAN (18),
1888
PARTITION pmax VALUES LESS THAN MAXVALUE);
1890
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1891
CREATE TABLE new (a TIMESTAMP NOT NULL, b TIMESTAMP NOT NULL, PRIMARY KEY(a,b))
1892
PARTITION BY RANGE (DATEDIFF(a, a)) (
1893
PARTITION p VALUES LESS THAN (18),
1894
PARTITION pmax VALUES LESS THAN MAXVALUE);
1896
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1898
PARTITION BY RANGE (DATEDIFF(a, a)) (
1899
PARTITION p VALUES LESS THAN (18),
1900
PARTITION pmax VALUES LESS THAN MAXVALUE);
1902
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1903
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1904
PARTITION BY RANGE (YEAR(a + 0)) (
1905
PARTITION p VALUES LESS THAN (2008),
1906
PARTITION pmax VALUES LESS THAN MAXVALUE);
1908
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1910
PARTITION BY RANGE (YEAR(a + 0)) (
1911
PARTITION p VALUES LESS THAN (2008),
1912
PARTITION pmax VALUES LESS THAN MAXVALUE);
1914
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1915
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1916
PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) (
1917
PARTITION p VALUES LESS THAN (733638),
1918
PARTITION pmax VALUES LESS THAN MAXVALUE);
1920
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1922
PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) (
1923
PARTITION p VALUES LESS THAN (733638),
1924
PARTITION pmax VALUES LESS THAN MAXVALUE);
1926
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1927
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1928
PARTITION BY RANGE (YEAR(a + '2008-01-01')) (
1929
PARTITION p VALUES LESS THAN (2008),
1930
PARTITION pmax VALUES LESS THAN MAXVALUE);
1932
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1934
PARTITION BY RANGE (YEAR(a + '2008-01-01')) (
1935
PARTITION p VALUES LESS THAN (2008),
1936
PARTITION pmax VALUES LESS THAN MAXVALUE);
1938
ALTER TABLE old ADD COLUMN b DATE;
1940
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1941
CREATE TABLE new (a TIMESTAMP, b DATE)
1942
PARTITION BY RANGE (YEAR(a + b)) (
1943
PARTITION p VALUES LESS THAN (2008),
1944
PARTITION pmax VALUES LESS THAN MAXVALUE);
1946
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1948
PARTITION BY RANGE (YEAR(a + b)) (
1949
PARTITION p VALUES LESS THAN (2008),
1950
PARTITION pmax VALUES LESS THAN MAXVALUE);
1952
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1953
CREATE TABLE new (a TIMESTAMP, b DATE)
1954
PARTITION BY RANGE (TO_DAYS(a + b)) (
1955
PARTITION p VALUES LESS THAN (733638),
1956
PARTITION pmax VALUES LESS THAN MAXVALUE);
1958
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1960
PARTITION BY RANGE (TO_DAYS(a + b)) (
1961
PARTITION p VALUES LESS THAN (733638),
1962
PARTITION pmax VALUES LESS THAN MAXVALUE);
1964
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1965
CREATE TABLE new (a TIMESTAMP, b date)
1966
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1967
PARTITION p VALUES LESS THAN (1219089600),
1968
PARTITION pmax VALUES LESS THAN MAXVALUE);
1970
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1972
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1973
PARTITION p VALUES LESS THAN (1219089600),
1974
PARTITION pmax VALUES LESS THAN MAXVALUE);
1976
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1977
CREATE TABLE new (a TIMESTAMP, b TIMESTAMP)
1978
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1979
PARTITION p VALUES LESS THAN (1219089600),
1980
PARTITION pmax VALUES LESS THAN MAXVALUE);
1982
ALTER TABLE old MODIFY b TIMESTAMP;
1984
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1986
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1987
PARTITION p VALUES LESS THAN (1219089600),
1988
PARTITION pmax VALUES LESS THAN MAXVALUE);
1993
--echo # Bug #56709: Memory leaks at running the 5.1 test suite
1996
CREATE TABLE t1 (a TIMESTAMP NOT NULL PRIMARY KEY);
1998
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
2000
PARTITION BY RANGE (EXTRACT(DAY FROM a)) (
2001
PARTITION p VALUES LESS THAN (18),
2002
PARTITION pmax VALUES LESS THAN MAXVALUE);
2006
--echo End of 5.1 tests
2008
CREATE TABLE t1 (a INT)
2009
PARTITION BY LIST (a)
2010
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
2011
(PARTITION p1 VALUES IN (1) COMMENT "Comment in p1"
2012
(SUBPARTITION p1spFirst COMMENT "SubPartition comment in p1spFirst",
2013
SUBPARTITION p1spSecond COMMENT "SubPartition comment in p1spSecond"),
2014
PARTITION p2 VALUES IN (2) COMMENT "Comment in p2"
2015
(SUBPARTITION p2spFirst COMMENT "SubPartition comment in p2spFirst",
2016
SUBPARTITION p2spSecond COMMENT "SubPartition comment in p2spSecond"));
2017
SHOW CREATE TABLE t1;
2018
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_COMMENT FROM INFORMATION_SCHEMA.PARTITIONS
2019
WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
2022
CREATE TABLE t1 (a INT)
2023
PARTITION BY LIST (a)
2024
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
2025
(PARTITION p1 VALUES IN (1)
2026
(SUBPARTITION p1spFirst COMMENT "SubPartition comment in p1spFirst",
2027
SUBPARTITION p1spSecond),
2028
PARTITION p2 VALUES IN (2) COMMENT "Comment in p2"
2029
(SUBPARTITION p2spFirst,
2030
SUBPARTITION p2spSecond COMMENT "SubPartition comment in p2spSecond"));
2031
SHOW CREATE TABLE t1;
2032
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_COMMENT FROM INFORMATION_SCHEMA.PARTITIONS
2033
WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
2039
PARTITION BY RANGE (a)
2040
SUBPARTITION BY HASH (a) SUBPARTITIONS 2
2041
(PARTITION pUpTo10 VALUES LESS THAN (10) COMMENT
2042
"This is a long comment (2050 ascii characters) 50 pUpTo10 partition ......80-!.................. 100 ................................................................................................ 200....................................................................................................................................................................................................................................................................................................... 500 ............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... 1000 ..............1024-|.......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... 1500 .............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................. 2000 ......................................2048-|++"
2043
(SUBPARTITION `p-10sp0` ,SUBPARTITION `p-10sp1` ),
2044
PARTITION pMax VALUES LESS THAN MAXVALUE COMMENT
2045
"This is a long comment (2050 ascii characters) 50 pMax partition comment .80-!.................. 100 ................................................................................................ 200....................................................................................................................................................................................................................................................................................................... 500 ............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... 1000 ..............1024-|.......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................... 1500 .............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................. 2000 ......................................2048-|++"
2046
(SUBPARTITION `pMaxsp0` ,SUBPARTITION `pMaxsp1` ));
2048
SHOW CREATE TABLE t1;
2049
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_COMMENT FROM INFORMATION_SCHEMA.PARTITIONS
2050
WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'test';
added
removed
mysql-test/t/partition_error.test
