1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
--source include/have_sequence.inc
--source include/have_innodb.inc
drop table if exists t1;
--echo #
--echo # Test alter sequence
--echo #
--disable_ps2_protocol
CREATE SEQUENCE t1 nocache engine=myisam;
select * from t1;
select next value for t1;
alter sequence t1 start=50;
show create sequence t1;
select * from t1;
select next value for t1;
alter sequence t1 minvalue=-100;
show create sequence t1;
select * from t1;
--error ER_SEQUENCE_INVALID_DATA
alter sequence t1 minvalue=100 start=100;
alter sequence t1 minvalue=100 start=100 restart=100;
show create sequence t1;
select * from t1;
alter sequence t1 maxvalue=500;
show create sequence t1;
select * from t1;
drop sequence t1;
CREATE SEQUENCE t1 engine=myisam;
alter sequence t1 nocache;
show create sequence t1;
alter sequence t1 cache=100;
flush tables;
show create sequence t1;
alter sequence t1 nocache;
show create sequence t1;
flush tables;
show create sequence t1;
select * from t1;
select next value for t1;
select next value for t1;
select next value for t1;
select next_not_cached_value, cycle_count from t1;
drop sequence t1;
CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
alter sequence t1 no maxvalue;
show create sequence t1;
select * from t1;
alter sequence t1 cycle;
show create sequence t1;
alter sequence t1 nocycle;
alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
show create sequence t1;
select * from t1;
select NEXT VALUE for t1 from seq_1_to_10;
alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
select NEXT VALUE for t1 from seq_1_to_10;
drop sequence t1;
CREATE SEQUENCE t1 maxvalue=100;
alter sequence t1 increment=-2 start with 50 minvalue=-100;
show create sequence t1;
select * from t1;
select NEXT VALUE for t1 from seq_1_to_10;
drop sequence t1;
--echo #
--echo # InnoDB (some things work different with InnoDB)
--echo
CREATE SEQUENCE t1 cache 10 engine=innodb;
select * from t1;
select next value for t1;
alter sequence t1 start=100;
show create sequence t1;
select * from t1;
select next value for t1;
drop sequence t1;
--echo #
--echo # ALTER TABLE
--echo #
CREATE SEQUENCE t1 engine=innodb;
select next value for t1;
alter table t1 rename t2;
select next value for t2;
rename table t2 to t1;
select next value for t1;
alter table t1 comment="foo";
show create sequence t1;
alter table t1 engine=myisam;
show create sequence t1;
alter table t1 engine=innodb;
show create sequence t1;
select * from t1;
drop sequence t1;
#
# Some error testing
#
CREATE SEQUENCE t1 engine=myisam;
--error ER_SEQUENCE_INVALID_DATA
alter sequence t1 minvalue=100;
drop sequence t1;
CREATE SEQUENCE t1 engine=myisam;
--error ER_SEQUENCE_INVALID_DATA
alter sequence t1 minvalue=25 maxvalue=20;
drop sequence t1;
create table t1 (a int);
--error ER_NOT_SEQUENCE
alter sequence t1 minvalue=100;
drop table t1;
--error ER_UNKNOWN_SEQUENCES
alter sequence if exists t1 minvalue=100;
--error ER_UNKNOWN_SEQUENCES
alter sequence t1 minvalue=100;
create sequence t1;
--error ER_PARSE_ERROR
alter sequence t1;
drop sequence t1;
CREATE SEQUENCE t1 maxvalue=100;
alter sequence t1 increment=-2 start with 50;
select next value for t1;
--error ER_SEQUENCE_RUN_OUT
select next value for t1;
select * from t1;
alter sequence t1 restart;
select next value for t1;
alter sequence t1 restart with 90;
select next value for t1;
drop sequence t1;
#
# MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X'
# failed in lock_rec_lock
#
CREATE SEQUENCE t1 engine=innodb;
--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value);
DROP SEQUENCE t1;
#
# MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't
# find record) after ALTER .. ORDER BY
#
CREATE SEQUENCE s;
--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
ALTER TABLE s ORDER BY cache_size;
SELECT NEXTVAL(s);
DROP SEQUENCE s;
--enable_ps2_protocol
|
