Debian Codegraph @

mysql-test/suite/sql_sequence/setval.test

raw
1 --source include/have_sequence.inc
2 --source include/have_innodb.inc
3
4 drop table if exists t1;
5
6 --echo #
7 --echo # Test setval function
8 --echo #
9
10 --disable_ps2_protocol
11 CREATE SEQUENCE t1 cache 10 engine=myisam;
12 select next_not_cached_value,cycle_count from t1;
13 do setval(t1,10);
14 select next_not_cached_value,cycle_count from t1;
15 select next value for t1;
16 do setval(t1,12,1);
17 select next_not_cached_value,cycle_count from t1;
18 select next value for t1;
19 do setval(t1,15,0);
20 select next_not_cached_value,cycle_count from t1;
21 select next value for t1;
22 select setval(t1,16,0);
23 select next value for t1;
24 do setval(t1,1000,0);
25 select next value for t1;
26 select next_not_cached_value,cycle_count from t1;
27 do setval(t1,2000,0);
28 select next value for t1;
29 select next_not_cached_value,cycle_count from t1;
30 # Set smaller value
31 select setval(t1,1000,0);
32 select next value for t1;
33 select setval(t1,1000,TRUE);
34 select next value for t1;
35 select next_not_cached_value,cycle_count from t1;
36 select setval(t1,2002,0);
37 select next value for t1;
38 select setval(t1,2010,0);
39 select next value for t1;
40 select next_not_cached_value,cycle_count from t1;
41 drop sequence t1;
42
43 --echo #
44 --echo # Testing with cycle
45 --echo #
46
47 CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
48 select next_not_cached_value,cycle_count from t1;
49 select setval(t1,100,0);
50 select next_not_cached_value,cycle_count from t1;
51 select next value for t1;
52 select next_not_cached_value,cycle_count from t1;
53 select setval(t1,100,0);
54 select next_not_cached_value,cycle_count from t1;
55 select next value for t1;
56 select next_not_cached_value,cycle_count from t1;
57 select next value for t1;
58 select setval(t1,100,0,1);
59 select next_not_cached_value,cycle_count from t1;
60 select next value for t1;
61 select setval(t1,100,1,2);
62 select next_not_cached_value,cycle_count from t1;
63 select next value for t1;
64 select setval(t1,100,0,3);
65 select next_not_cached_value,cycle_count from t1;
66 select next value for t1;
67 drop sequence t1;
68
69 --echo #
70 --echo # Testing extreme values
71 --echo #
72
73 CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb;
74 select next_not_cached_value,cycle_count from t1;
75 select setval(t1,200);
76 select next_not_cached_value,cycle_count from t1;
77 --error ER_SEQUENCE_RUN_OUT
78 select next value for t1;
79 drop sequence t1;
80
81 CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
82 select next_not_cached_value,cycle_count from t1;
83 select setval(t1,200);
84 select next_not_cached_value,cycle_count from t1;
85 select next value for t1;
86 drop sequence t1;
87
88 CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
89 select setval(t1,-10);
90 select next_not_cached_value,cycle_count from t1;
91 select next value for t1;
92 select setval(t1,-15);
93 select next_not_cached_value,cycle_count from t1;
94 select next value for t1;
95 select setval(t1,-500,FALSE);
96 select next value for t1;
97 select next value for t1;
98 select setval(t1,-525,0);
99 select next value for t1;
100 select next value for t1;
101 drop sequence t1;
102
103 CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
104 select setval(t1,-10,0);
105 select next_not_cached_value,cycle_count from t1;
106 select next value for t1;
107 drop sequence t1;
108
109 --echo #
110 --echo # Other testing
111 --echo #
112
113 CREATE SEQUENCE t1;
114 select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1);
115 select next value for t1;
116 select next_not_cached_value,cycle_count from t1;
117 explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50);
118 drop sequence t1;
119
120 #
121 # Some error testing
122 #
123
124 create table t1 (a int);
125 --error ER_NOT_SEQUENCE
126 select setval(t1,10);
127 drop table t1;
128
129
130 --echo #
131 --echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
132 --echo #
133
134 --enable_metadata
135 --disable_ps_protocol
136 CREATE SEQUENCE s1;
137 SELECT SETVAL(s1,10);
138 DROP SEQUENCE s1;
139 --enable_ps_protocol
140 --disable_metadata
141
142 --echo #
143 --echo # MDEV-15732: Assertion `next_free_value % real_increment == offset &&
144 --echo # next_free_value >= reserved_until' failed in
145 --echo # sequence_definition::adjust_values upon SETVAL for sequence with
146 --echo # INCREMENT 0
147 --echo #
148
149 CREATE SEQUENCE s INCREMENT 0;
150 SELECT NEXTVAL(s);
151 SELECT SETVAL(s, 10);
152
153 # Cleanup
154 DROP SEQUENCE s;
155
156 --echo # End of 10.3 tests
157
158 --echo #
159 --echo # MDEV-28152 Features for sequence
160 --echo #
161
162 # out of bounds for the type: returns NULL
163 create sequence s;
164 select setval(s, 12345678901234567890);
165 select nextval(s);
166 drop sequence s;
167
168 create sequence s increment -1;
169 select setval(s, 12345678901234567890);
170 select nextval(s);
171 drop sequence s;
172
173 create sequence s as smallint;
174 select setval(s, 55555);
175 select setval(s, -55555);
176 select nextval(s);
177 drop sequence s;
178
179 create sequence s as smallint increment -1;
180 select setval(s, 55555);
181 select setval(s, -55555);
182 select nextval(s);
183 drop sequence s;
184
185 create sequence s as bigint unsigned;
186 select setval(s, -123);
187 select nextval(s);
188 drop sequence s;
189
190 create sequence s as bigint unsigned increment -1;
191 select setval(s, -123);
192 select nextval(s);
193 drop sequence s;
194
195 # large setval values for bigint unsigned
196 create sequence s as bigint unsigned;
197 select setval(s, -123);
198 select setval(s, 0);
199 select nextval(s);
200 select setval(s, 12345678901234567890);
201 select nextval(s);
202 select setval(s, 12345678901234567890);
203 select nextval(s);
204 select setval(s, 18446744073709551614, 0);
205 select nextval(s);
206 --error ER_SEQUENCE_RUN_OUT
207 select nextval(s);
208 --error ER_SEQUENCE_RUN_OUT
209 select setval(s, 12345678901234567890, 0, 1);
210 alter sequence s cycle;
211 select setval(s, 12345678901234567890, 0, 1);
212 select nextval(s);
213 drop sequence s;
214
215 # extreme setval values for bigint unsigned
216 create sequence s as bigint unsigned;
217 --error ER_PARSE_ERROR
218 select setval(s, 18446744073709551616);
219 select setval(s, 18446744073709551615);
220 --error ER_SEQUENCE_RUN_OUT
221 select nextval(s);
222 drop sequence s;
223
224 create sequence s as bigint unsigned;
225 select setval(s, 18446744073709551615, 0);
226 --error ER_SEQUENCE_RUN_OUT
227 select nextval(s);
228 drop sequence s;
229
230 create sequence s as bigint unsigned cycle;
231 select setval(s, 18446744073709551615);
232 select nextval(s);
233 drop sequence s;
234
235 create sequence s as bigint unsigned cycle;
236 select setval(s, 18446744073709551615, 0);
237 select nextval(s);
238 drop sequence s;
239
240 # a small range for bigint unsigned
241 create sequence s as bigint unsigned minvalue 123 maxvalue 456;
242 select setval(s, 100);
243 select nextval(s);
244 select setval(s, 500);
245 --error ER_SEQUENCE_RUN_OUT
246 select nextval(s);
247 drop sequence s;
248
249 create sequence s as smallint;
250 select setval(s, 32767);
251 --error ER_SEQUENCE_RUN_OUT
252 select nextval(s);
253 drop sequence s;
254 --enable_ps2_protocol
255
256 --echo #
257 --echo # MDEV-33836 Assertion `(ulonglong) next_free_value % real_increment == (ulonglong) offset' failed in void sequence_definition::adjust_values(longlong)
258 --echo #
259
260 CREATE SEQUENCE s AS BIGINT UNSIGNED START WITH 9223372036854775800 INCREMENT 0;
261 set @old_AUTO_INCREMENT_INCREMENT=@@global.AUTO_INCREMENT_INCREMENT;
262 set global AUTO_INCREMENT_INCREMENT=100;
263 SELECT SETVAL (s,12345678901234567890);
264 drop sequence s;
265 set global AUTO_INCREMENT_INCREMENT=@old_AUTO_INCREMENT_INCREMENT;
266
267 --echo # End of 11.5 tests