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;
12
# Bug#38719: Partitioning returns a different error code for a
14
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a));
16
INSERT INTO t1 VALUES (1),(1);
18
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a))
19
PARTITION BY KEY (a) PARTITIONS 2;
21
INSERT INTO t1 VALUES (1),(1);
25
# Bug#31931: Mix of handlers error message
27
--error ER_MIX_HANDLER_ERROR
28
CREATE TABLE t1 (a INT)
30
( PARTITION p0 ENGINE=MyISAM,
32
--error ER_MIX_HANDLER_ERROR
33
CREATE TABLE t1 (a INT)
35
SUBPARTITION BY HASH (a)
36
( PARTITION p0 VALUES IN (0)
37
( SUBPARTITION s0, SUBPARTITION s1 ENGINE=MyISAM, SUBPARTITION s2),
38
PARTITION p1 VALUES IN (1)
39
( SUBPARTITION s3 ENGINE=MyISAM, SUBPARTITION s4, SUBPARTITION s5 ENGINE=MyISAM));
44
# Incorrect error, 1467, for syntax error when creating partition
45
--error ER_PARTITION_REQUIRES_VALUES_ERROR
49
PARTITION BY RANGE (a)
51
PARTITION p0 VALUES LESS THAN (1),
52
PARTITION p1 VALU ES LESS THAN (2)
56
# Partition by key stand-alone error
61
(partition x1 values in (1,2,9,4) tablespace ts1,
62
partition x2 values in (3, 11, 5, 7) tablespace ts2,
63
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
66
# Partition by key list, number of partitions defined, no partition defined
68
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
78
# Partition by key list, wrong result type
80
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
86
partition by list (sin(a))
88
(partition x1 values in (1,2,9,4) tablespace ts1,
89
partition x2 values in (3, 11, 5, 7) tablespace ts2,
90
partition x3 values in (16, 8, 5+19, 70-43) tablespace ts3);
93
# Partition by key, partition function not allowed
101
partition by key (a+2)
103
(partition x1 tablespace ts1,
104
partition x2 tablespace ts2,
105
partition x3 tablespace ts3);
108
# Partition by key, no partition name
118
(partition tablespace ts1,
119
partition x2 tablespace ts2,
120
partition x3 tablespace ts3);
123
# Partition by key, invalid field in field list
125
--error ER_FIELD_NOT_FOUND_PART_ERROR
131
partition by key (a,d)
133
(partition x1 tablespace ts1,
134
partition x2 tablespace ts2,
135
partition x3 tablespace ts3);
137
let $MYSQLD_DATADIR= `select @@datadir`;
138
select load_file('$MYSQLD_DATADIR/test/t1.par');
140
# Partition by hash, invalid field in function
148
partition by hash (a + d)
150
(partition x1 tablespace ts1,
151
partition x2 tablespace ts2,
152
partition x3 tablespace ts3);
155
# Partition by hash, invalid result type
157
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
163
partition by hash (sin(a))
165
(partition x1 tablespace ts1,
166
partition x2 tablespace ts2,
167
partition x3 tablespace ts3);
170
# Partition by key specified 3 partitions but only defined 2 => error
180
(partition x1, partition x2);
183
# Partition by key specified 3 partitions but only defined 2 => error
191
partition by hash (rand(a))
193
(partition x1, partition x2);
196
# Partition by key specified 3 partitions but only defined 2 => error
204
partition by range (rand(a))
206
(partition x1 values less than (0), partition x2 values less than (2));
209
# Partition by key specified 3 partitions but only defined 2 => error
217
partition by list (rand(a))
219
(partition x1 values in (1), partition x2 values in (2));
222
# Partition by hash, values less than error
224
--error ER_PARTITION_WRONG_VALUES_ERROR
230
partition by hash (a)
232
(partition x1 values less than (4),
233
partition x2 values less than (5));
234
select load_file('$MYSQLD_DATADIR/test/t1.par');
237
# Partition by hash, values in error
239
--error ER_PARTITION_WRONG_VALUES_ERROR
245
partition by hash (a)
247
(partition x1 values in (4),
248
partition x2 values in (5));
249
select load_file('$MYSQLD_DATADIR/test/t1.par');
252
# Partition by hash, values in error
254
--error ER_PARTITION_WRONG_VALUES_ERROR
260
partition by hash (a)
262
(partition x1 values in (4,6),
263
partition x2 values in (5,7));
264
select load_file('$MYSQLD_DATADIR/test/t1.par');
267
# Subpartition by key, no partitions defined, single field
269
--error ER_SUBPARTITION_ERROR
276
subpartition by key (b);
277
select load_file('$MYSQLD_DATADIR/test/t1.par');
280
# Subpartition by key, no partitions defined, list of fields
282
--error ER_SUBPARTITION_ERROR
289
subpartition by key (a, b);
290
select load_file('$MYSQLD_DATADIR/test/t1.par');
293
# Subpartition by hash, no partitions defined
295
--error ER_SUBPARTITION_ERROR
302
subpartition by hash (a+b);
303
select load_file('$MYSQLD_DATADIR/test/t1.par');
306
# Subpartition by key, no partitions defined, single field
308
--error ER_SUBPARTITION_ERROR
315
subpartition by key (b);
316
select load_file('$MYSQLD_DATADIR/test/t1.par');
319
# Subpartition by key, no partitions defined, list of fields
321
--error ER_SUBPARTITION_ERROR
328
subpartition by key (a, b);
329
select load_file('$MYSQLD_DATADIR/test/t1.par');
332
# Subpartition by hash, no partitions defined
334
--error ER_SUBPARTITION_ERROR
341
subpartition by hash (a+b);
342
select load_file('$MYSQLD_DATADIR/test/t1.par');
345
# Subpartition by hash, no partitions defined, wrong subpartition function
354
subpartition by hash (rand(a+b));
357
# Subpartition by hash, wrong subpartition function
359
--error ER_SUBPARTITION_ERROR
366
subpartition by hash (sin(a+b))
367
(partition x1 (subpartition x11, subpartition x12),
368
partition x2 (subpartition x21, subpartition x22));
369
select load_file('$MYSQLD_DATADIR/test/t1.par');
372
# Subpartition by hash, no partitions defined, wrong subpartition function
380
partition by range (a)
381
subpartition by key (a+b)
382
(partition x1 values less than (1) (subpartition x11, subpartition x12),
383
partition x2 values less than (2) (subpartition x21, subpartition x22));
386
# Subpartition by hash, no partitions defined, wrong subpartition function
388
--error ER_FIELD_NOT_FOUND_PART_ERROR
394
partition by range (a)
395
subpartition by key (a,d)
396
(partition x1 values less than (1) (subpartition x11, subpartition x12),
397
partition x2 values less than (2) (subpartition x21, subpartition x22));
398
select load_file('$MYSQLD_DATADIR/test/t1.par');
401
# Subpartition by hash, no partitions defined, wrong subpartition function
403
--error ER_SUBPARTITION_ERROR
410
subpartition by hash (3+4);
413
# Subpartition by hash, no partitions defined, wrong subpartition function
421
partition by range (a)
422
subpartition by hash (a+d)
423
(partition x1 values less than (1) (subpartition x11, subpartition x12),
424
partition x2 values less than (2) (subpartition x21, subpartition x22));
427
# Partition by range, no partition => error
429
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
435
partition by range (a);
436
select load_file('$MYSQLD_DATADIR/test/t1.par');
439
# Partition by range, invalid field in function
447
partition by range (a+d)
449
(partition x1 values less than (4) tablespace ts1,
450
partition x2 values less than (8) tablespace ts2);
453
# Partition by range, inconsistent partition function and constants
461
partition by range (a)
463
(partition x1 values less than (4.0) tablespace ts1,
464
partition x2 values less than (8) tablespace ts2);
467
# Partition by range, constant partition function not allowed
469
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
475
partition by range (3+4)
477
(partition x1 values less than (4) tablespace ts1,
478
partition x2 values less than (8) tablespace ts2);
481
# Partition by range, no values less than definition
483
--error ER_PARTITION_REQUIRES_VALUES_ERROR
489
partition by range (a)
491
(partition x1 values less than (4),
495
# Partition by range, no values in definition allowed
497
--error ER_PARTITION_WRONG_VALUES_ERROR
503
partition by range (a)
505
(partition x1 values in (4),
509
# Partition by range, values in error
511
--error ER_PARTITION_WRONG_VALUES_ERROR
517
partition by range (a)
519
(partition x1 values in (4),
520
partition x2 values less than (5));
523
# Partition by range, missing parenthesis
531
partition by list (a)
533
(partition x1 values less than 4,
534
partition x2 values less than (5));
537
# Partition by range, maxvalue in wrong place
545
partition by range (a)
547
(partition x1 values less than maxvalue,
548
partition x2 values less than (5));
551
# Partition by range, maxvalue in several places
559
partition by range (a)
561
(partition x1 values less than maxvalue,
562
partition x2 values less than maxvalue);
565
# Partition by range, not increasing ranges
567
--error ER_RANGE_NOT_INCREASING_ERROR
573
partition by range (a)
575
(partition x1 values less than (4),
576
partition x2 values less than (3));
579
# Partition by range, wrong result type of partition function
581
--error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
587
partition by range (sin(a))
589
(partition x1 values less than (4),
590
partition x2 values less than (5));
593
# Subpartition by hash, wrong number of subpartitions
601
partition by list (a)
602
subpartition by hash (a+b)
604
( partition x1 values in (1,2,4)
605
( subpartition x11 nodegroup 0,
606
subpartition x12 nodegroup 1),
607
partition x2 values in (3,5,6)
608
( subpartition x21 nodegroup 0,
609
subpartition x22 nodegroup 1)
613
# Subpartition by hash, wrong number of subpartitions
621
partition by list (a)
622
subpartition by hash (a+b)
623
( partition x1 values in (1)
624
( subpartition x11 nodegroup 0,
626
subpartition x12 nodegroup 1),
627
partition x2 values in (2)
628
( subpartition x21 nodegroup 0,
629
subpartition x22 nodegroup 1)
633
# Subpartition by list => error
642
subpartition by list (a+b)
644
( subpartition x11 engine myisam,
645
subpartition x12 engine myisam),
647
( subpartition x21 engine myisam,
648
subpartition x22 engine myisam)
652
# Subpartition by list => error
661
subpartition by list (a+b)
663
( subpartition x11 engine myisam values in (0),
664
subpartition x12 engine myisam values in (1)),
666
( subpartition x21 engine myisam values in (0),
667
subpartition x22 engine myisam values in (1))
671
# Partition by list, no partition => error
673
--error ER_PARTITIONS_MUST_BE_DEFINED_ERROR
679
partition by list (a);
682
# Partition by list, constant partition function not allowed
684
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
690
partition by list (3+4)
692
(partition x1 values in (4) tablespace ts1,
693
partition x2 values in (8) tablespace ts2);
696
# Partition by list, invalid field in function
704
partition by list (a+d)
706
(partition x1 values in (4) tablespace ts1,
707
partition x2 values in (8) tablespace ts2);
710
# Partition by list, no values in definition
712
--error ER_PARTITION_REQUIRES_VALUES_ERROR
718
partition by list (a)
720
(partition x1 values in (4),
724
# Partition by list, values less than error
726
--error ER_PARTITION_WRONG_VALUES_ERROR
732
partition by list (a)
734
(partition x1 values in (4),
735
partition x2 values less than (5));
738
# Partition by list, no values in definition
740
--error ER_PARTITION_REQUIRES_VALUES_ERROR
746
partition by list (a)
748
(partition x1 values in (4,6),
752
# Partition by list, duplicate values
754
--error ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR
760
partition by list (a)
762
(partition x1 values in (4, 12+9),
763
partition x2 values in (3, 21));
766
# Partition by list, wrong constant result type (not INT)
774
partition by list (a)
776
(partition x1 values in (4.0, 12+8),
777
partition x2 values in (3, 21));
780
# Partition by list, missing parenthesis
788
partition by list (a)
790
(partition x1 values in 4,
791
partition x2 values in (5));
794
# Bug #13439: Crash when LESS THAN (non-literal)
797
CREATE TABLE t1 (a int)
798
PARTITION BY RANGE (a)
799
(PARTITION p0 VALUES LESS THAN (x1));
802
# No partition for the given value
804
CREATE TABLE t1(a int)
805
PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN(5));
806
--error ER_NO_PARTITION_FOR_GIVEN_VALUE
807
insert into t1 values (10);
810
--error ER_PARTITION_CONST_DOMAIN_ERROR
811
create table t1 (a bigint unsigned)
812
partition by range (a)
813
(partition p0 values less than (-1));
815
# Bug 18198 Partitions: Verify that erroneus partition functions doesn't work
817
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
818
create table t1 (v varchar(12))
819
partition by range (ascii(v))
820
(partition p0 values less than (10));
824
create table t1 (a int)
825
partition by hash (rand(a));
827
create table t1 (a int)
828
partition by hash(CURTIME() + a);
830
create table t1 (a int)
831
partition by hash (NOW()+a);
832
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
833
create table t1 (a int)
834
partition by hash (extract(hour from convert_tz(a, '+00:00', '+00:00')));
835
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
836
create table t1 (a int)
837
partition by range (a + (select count(*) from t1))
838
(partition p1 values less than (1));
839
-- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
840
create table t1 (a char(10))
841
partition by hash (extractvalue(a,'a'));
844
--echo # Bug #42849: innodb crash with varying time_zone on partitioned
845
--echo # timestamp primary key
848
# A correctly partitioned table to test that trying to repartition it using
849
# timezone-dependent expression will throw an error.
850
CREATE TABLE old (a TIMESTAMP NOT NULL PRIMARY KEY)
851
PARTITION BY RANGE (UNIX_TIMESTAMP(a)) (
852
PARTITION p VALUES LESS THAN (1219089600),
853
PARTITION pmax VALUES LESS THAN MAXVALUE);
855
# Check that allowed arithmetic/math functions involving TIMESTAMP values result
856
# in ER_PARTITION_FUNC_NOT_ALLOWED_ERROR when used as a partitioning function
858
--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
859
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
860
PARTITION BY RANGE (a) (
861
PARTITION p VALUES LESS THAN (20080819),
862
PARTITION pmax VALUES LESS THAN MAXVALUE);
864
--error ER_PARTITION_FUNC_NOT_ALLOWED_ERROR
866
PARTITION BY RANGE (a) (
867
PARTITION p VALUES LESS THAN (20080819),
868
PARTITION pmax VALUES LESS THAN MAXVALUE);
870
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
871
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
872
PARTITION BY RANGE (a+0) (
873
PARTITION p VALUES LESS THAN (20080819),
874
PARTITION pmax VALUES LESS THAN MAXVALUE);
876
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
878
PARTITION BY RANGE (a+0) (
879
PARTITION p VALUES LESS THAN (20080819),
880
PARTITION pmax VALUES LESS THAN MAXVALUE);
882
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
883
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
884
PARTITION BY RANGE (a % 2) (
885
PARTITION p VALUES LESS THAN (20080819),
886
PARTITION pmax VALUES LESS THAN MAXVALUE);
888
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
890
PARTITION BY RANGE (a % 2) (
891
PARTITION p VALUES LESS THAN (20080819),
892
PARTITION pmax VALUES LESS THAN MAXVALUE);
894
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
895
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
896
PARTITION BY RANGE (ABS(a)) (
897
PARTITION p VALUES LESS THAN (20080819),
898
PARTITION pmax VALUES LESS THAN MAXVALUE);
900
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
902
PARTITION BY RANGE (ABS(a)) (
903
PARTITION p VALUES LESS THAN (20080819),
904
PARTITION pmax VALUES LESS THAN MAXVALUE);
906
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
907
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
908
PARTITION BY RANGE (CEILING(a)) (
909
PARTITION p VALUES LESS THAN (20080819),
910
PARTITION pmax VALUES LESS THAN MAXVALUE);
912
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
914
PARTITION BY RANGE (CEILING(a)) (
915
PARTITION p VALUES LESS THAN (20080819),
916
PARTITION pmax VALUES LESS THAN MAXVALUE);
918
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
919
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
920
PARTITION BY RANGE (FLOOR(a)) (
921
PARTITION p VALUES LESS THAN (20080819),
922
PARTITION pmax VALUES LESS THAN MAXVALUE);
924
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
926
PARTITION BY RANGE (FLOOR(a)) (
927
PARTITION p VALUES LESS THAN (20080819),
928
PARTITION pmax VALUES LESS THAN MAXVALUE);
930
# Check that allowed date/time functions involving TIMESTAMP values result
931
# in ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR when used as a partitioning function
933
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
934
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
935
PARTITION BY RANGE (TO_DAYS(a)) (
936
PARTITION p VALUES LESS THAN (733638),
937
PARTITION pmax VALUES LESS THAN MAXVALUE);
939
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
941
PARTITION BY RANGE (TO_DAYS(a)) (
942
PARTITION p VALUES LESS THAN (733638),
943
PARTITION pmax VALUES LESS THAN MAXVALUE);
945
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
946
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
947
PARTITION BY RANGE (DAYOFYEAR(a)) (
948
PARTITION p VALUES LESS THAN (231),
949
PARTITION pmax VALUES LESS THAN MAXVALUE);
951
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
953
PARTITION BY RANGE (DAYOFYEAR(a)) (
954
PARTITION p VALUES LESS THAN (231),
955
PARTITION pmax VALUES LESS THAN MAXVALUE);
957
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
958
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
959
PARTITION BY RANGE (DAYOFMONTH(a)) (
960
PARTITION p VALUES LESS THAN (19),
961
PARTITION pmax VALUES LESS THAN MAXVALUE);
963
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
965
PARTITION BY RANGE (DAYOFMONTH(a)) (
966
PARTITION p VALUES LESS THAN (19),
967
PARTITION pmax VALUES LESS THAN MAXVALUE);
969
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
970
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
971
PARTITION BY RANGE (DAYOFWEEK(a)) (
972
PARTITION p VALUES LESS THAN (3),
973
PARTITION pmax VALUES LESS THAN MAXVALUE);
975
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
977
PARTITION BY RANGE (DAYOFWEEK(a)) (
978
PARTITION p VALUES LESS THAN (3),
979
PARTITION pmax VALUES LESS THAN MAXVALUE);
981
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
982
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
983
PARTITION BY RANGE (MONTH(a)) (
984
PARTITION p VALUES LESS THAN (8),
985
PARTITION pmax VALUES LESS THAN MAXVALUE);
987
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
989
PARTITION BY RANGE (MONTH(a)) (
990
PARTITION p VALUES LESS THAN (8),
991
PARTITION pmax VALUES LESS THAN MAXVALUE);
993
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
994
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
995
PARTITION BY RANGE (HOUR(a)) (
996
PARTITION p VALUES LESS THAN (17),
997
PARTITION pmax VALUES LESS THAN MAXVALUE);
999
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1001
PARTITION BY RANGE (HOUR(a)) (
1002
PARTITION p VALUES LESS THAN (17),
1003
PARTITION pmax VALUES LESS THAN MAXVALUE);
1005
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1006
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1007
PARTITION BY RANGE (MINUTE(a)) (
1008
PARTITION p VALUES LESS THAN (55),
1009
PARTITION pmax VALUES LESS THAN MAXVALUE);
1011
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1013
PARTITION BY RANGE (MINUTE(a)) (
1014
PARTITION p VALUES LESS THAN (55),
1015
PARTITION pmax VALUES LESS THAN MAXVALUE);
1017
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1018
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1019
PARTITION BY RANGE (QUARTER(a)) (
1020
PARTITION p VALUES LESS THAN (3),
1021
PARTITION pmax VALUES LESS THAN MAXVALUE);
1023
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1025
PARTITION BY RANGE (QUARTER(a)) (
1026
PARTITION p VALUES LESS THAN (3),
1027
PARTITION pmax VALUES LESS THAN MAXVALUE);
1029
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1030
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1031
PARTITION BY RANGE (SECOND(a)) (
1032
PARTITION p VALUES LESS THAN (7),
1033
PARTITION pmax VALUES LESS THAN MAXVALUE);
1035
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1037
PARTITION BY RANGE (SECOND(a)) (
1038
PARTITION p VALUES LESS THAN (7),
1039
PARTITION pmax VALUES LESS THAN MAXVALUE);
1041
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1042
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1043
PARTITION BY RANGE (YEARWEEK(a)) (
1044
PARTITION p VALUES LESS THAN (200833),
1045
PARTITION pmax VALUES LESS THAN MAXVALUE);
1047
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1049
PARTITION BY RANGE (YEARWEEK(a)) (
1050
PARTITION p VALUES LESS THAN (200833),
1051
PARTITION pmax VALUES LESS THAN MAXVALUE);
1053
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1054
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1055
PARTITION BY RANGE (YEAR(a)) (
1056
PARTITION p VALUES LESS THAN (2008),
1057
PARTITION pmax VALUES LESS THAN MAXVALUE);
1059
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1061
PARTITION BY RANGE (YEAR(a)) (
1062
PARTITION p VALUES LESS THAN (2008),
1063
PARTITION pmax VALUES LESS THAN MAXVALUE);
1065
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1066
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1067
PARTITION BY RANGE (WEEKDAY(a)) (
1068
PARTITION p VALUES LESS THAN (3),
1069
PARTITION pmax VALUES LESS THAN MAXVALUE);
1071
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1073
PARTITION BY RANGE (WEEKDAY(a)) (
1074
PARTITION p VALUES LESS THAN (3),
1075
PARTITION pmax VALUES LESS THAN MAXVALUE);
1077
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1078
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1079
PARTITION BY RANGE (TIME_TO_SEC(a)) (
1080
PARTITION p VALUES LESS THAN (64507),
1081
PARTITION pmax VALUES LESS THAN MAXVALUE);
1083
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1085
PARTITION BY RANGE (TIME_TO_SEC(a)) (
1086
PARTITION p VALUES LESS THAN (64507),
1087
PARTITION pmax VALUES LESS THAN MAXVALUE);
1089
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1090
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1091
PARTITION BY RANGE (EXTRACT(DAY FROM a)) (
1092
PARTITION p VALUES LESS THAN (18),
1093
PARTITION pmax VALUES LESS THAN MAXVALUE);
1095
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1097
PARTITION BY RANGE (EXTRACT(DAY FROM a)) (
1098
PARTITION p VALUES LESS THAN (18),
1099
PARTITION pmax VALUES LESS THAN MAXVALUE);
1101
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1102
CREATE TABLE new (a TIMESTAMP NOT NULL, b TIMESTAMP NOT NULL, PRIMARY KEY(a,b))
1103
PARTITION BY RANGE (DATEDIFF(a, a)) (
1104
PARTITION p VALUES LESS THAN (18),
1105
PARTITION pmax VALUES LESS THAN MAXVALUE);
1107
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1109
PARTITION BY RANGE (DATEDIFF(a, a)) (
1110
PARTITION p VALUES LESS THAN (18),
1111
PARTITION pmax VALUES LESS THAN MAXVALUE);
1113
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1114
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1115
PARTITION BY RANGE (YEAR(a + 0)) (
1116
PARTITION p VALUES LESS THAN (2008),
1117
PARTITION pmax VALUES LESS THAN MAXVALUE);
1119
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1121
PARTITION BY RANGE (YEAR(a + 0)) (
1122
PARTITION p VALUES LESS THAN (2008),
1123
PARTITION pmax VALUES LESS THAN MAXVALUE);
1125
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1126
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1127
PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) (
1128
PARTITION p VALUES LESS THAN (733638),
1129
PARTITION pmax VALUES LESS THAN MAXVALUE);
1131
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1133
PARTITION BY RANGE (TO_DAYS(a + '2008-01-01')) (
1134
PARTITION p VALUES LESS THAN (733638),
1135
PARTITION pmax VALUES LESS THAN MAXVALUE);
1137
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1138
CREATE TABLE new (a TIMESTAMP NOT NULL PRIMARY KEY)
1139
PARTITION BY RANGE (YEAR(a + '2008-01-01')) (
1140
PARTITION p VALUES LESS THAN (2008),
1141
PARTITION pmax VALUES LESS THAN MAXVALUE);
1143
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1145
PARTITION BY RANGE (YEAR(a + '2008-01-01')) (
1146
PARTITION p VALUES LESS THAN (2008),
1147
PARTITION pmax VALUES LESS THAN MAXVALUE);
1149
ALTER TABLE old ADD COLUMN b DATE;
1151
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1152
CREATE TABLE new (a TIMESTAMP, b DATE)
1153
PARTITION BY RANGE (YEAR(a + b)) (
1154
PARTITION p VALUES LESS THAN (2008),
1155
PARTITION pmax VALUES LESS THAN MAXVALUE);
1157
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1159
PARTITION BY RANGE (YEAR(a + b)) (
1160
PARTITION p VALUES LESS THAN (2008),
1161
PARTITION pmax VALUES LESS THAN MAXVALUE);
1163
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1164
CREATE TABLE new (a TIMESTAMP, b DATE)
1165
PARTITION BY RANGE (TO_DAYS(a + b)) (
1166
PARTITION p VALUES LESS THAN (733638),
1167
PARTITION pmax VALUES LESS THAN MAXVALUE);
1169
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1171
PARTITION BY RANGE (TO_DAYS(a + b)) (
1172
PARTITION p VALUES LESS THAN (733638),
1173
PARTITION pmax VALUES LESS THAN MAXVALUE);
1175
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1176
CREATE TABLE new (a TIMESTAMP, b date)
1177
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1178
PARTITION p VALUES LESS THAN (1219089600),
1179
PARTITION pmax VALUES LESS THAN MAXVALUE);
1181
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1183
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1184
PARTITION p VALUES LESS THAN (1219089600),
1185
PARTITION pmax VALUES LESS THAN MAXVALUE);
1187
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1188
CREATE TABLE new (a TIMESTAMP, b TIMESTAMP)
1189
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1190
PARTITION p VALUES LESS THAN (1219089600),
1191
PARTITION pmax VALUES LESS THAN MAXVALUE);
1193
ALTER TABLE old MODIFY b TIMESTAMP;
1195
--error ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR
1197
PARTITION BY RANGE (UNIX_TIMESTAMP(a + b)) (
1198
PARTITION p VALUES LESS THAN (1219089600),
1199
PARTITION pmax VALUES LESS THAN MAXVALUE);
1203
--echo End of 5.1 tests