Debian Codegraph @

mysql-test/suite/sql_sequence/setval.result

raw
1 drop table if exists t1;
2 Warnings:
3 Note 1051 Unknown table 'test.t1'
4 #
5 # Test setval function
6 #
7 CREATE SEQUENCE t1 cache 10 engine=myisam;
8 select next_not_cached_value,cycle_count from t1;
9 next_not_cached_value cycle_count
10 1 0
11 do setval(t1,10);
12 select next_not_cached_value,cycle_count from t1;
13 next_not_cached_value cycle_count
14 11 0
15 select next value for t1;
16 next value for t1
17 11
18 do setval(t1,12,1);
19 select next_not_cached_value,cycle_count from t1;
20 next_not_cached_value cycle_count
21 21 0
22 select next value for t1;
23 next value for t1
24 13
25 do setval(t1,15,0);
26 select next_not_cached_value,cycle_count from t1;
27 next_not_cached_value cycle_count
28 21 0
29 select next value for t1;
30 next value for t1
31 15
32 select setval(t1,16,0);
33 setval(t1,16,0)
34 16
35 select next value for t1;
36 next value for t1
37 16
38 do setval(t1,1000,0);
39 select next value for t1;
40 next value for t1
41 1000
42 select next_not_cached_value,cycle_count from t1;
43 next_not_cached_value cycle_count
44 1010 0
45 do setval(t1,2000,0);
46 select next value for t1;
47 next value for t1
48 2000
49 select next_not_cached_value,cycle_count from t1;
50 next_not_cached_value cycle_count
51 2010 0
52 select setval(t1,1000,0);
53 setval(t1,1000,0)
54 NULL
55 select next value for t1;
56 next value for t1
57 2001
58 select setval(t1,1000,TRUE);
59 setval(t1,1000,TRUE)
60 NULL
61 select next value for t1;
62 next value for t1
63 2002
64 select next_not_cached_value,cycle_count from t1;
65 next_not_cached_value cycle_count
66 2010 0
67 select setval(t1,2002,0);
68 setval(t1,2002,0)
69 NULL
70 select next value for t1;
71 next value for t1
72 2003
73 select setval(t1,2010,0);
74 setval(t1,2010,0)
75 2010
76 select next value for t1;
77 next value for t1
78 2010
79 select next_not_cached_value,cycle_count from t1;
80 next_not_cached_value cycle_count
81 2020 0
82 drop sequence t1;
83 #
84 # Testing with cycle
85 #
86 CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
87 select next_not_cached_value,cycle_count from t1;
88 next_not_cached_value cycle_count
89 1 0
90 select setval(t1,100,0);
91 setval(t1,100,0)
92 100
93 select next_not_cached_value,cycle_count from t1;
94 next_not_cached_value cycle_count
95 100 0
96 select next value for t1;
97 next value for t1
98 100
99 select next_not_cached_value,cycle_count from t1;
100 next_not_cached_value cycle_count
101 101 0
102 select setval(t1,100,0);
103 setval(t1,100,0)
104 NULL
105 select next_not_cached_value,cycle_count from t1;
106 next_not_cached_value cycle_count
107 101 0
108 select next value for t1;
109 next value for t1
110 1
111 select next_not_cached_value,cycle_count from t1;
112 next_not_cached_value cycle_count
113 11 1
114 select next value for t1;
115 next value for t1
116 2
117 select setval(t1,100,0,1);
118 setval(t1,100,0,1)
119 100
120 select next_not_cached_value,cycle_count from t1;
121 next_not_cached_value cycle_count
122 100 1
123 select next value for t1;
124 next value for t1
125 100
126 select setval(t1,100,1,2);
127 setval(t1,100,1,2)
128 100
129 select next_not_cached_value,cycle_count from t1;
130 next_not_cached_value cycle_count
131 101 2
132 select next value for t1;
133 next value for t1
134 1
135 select setval(t1,100,0,3);
136 setval(t1,100,0,3)
137 100
138 select next_not_cached_value,cycle_count from t1;
139 next_not_cached_value cycle_count
140 100 3
141 select next value for t1;
142 next value for t1
143 100
144 drop sequence t1;
145 #
146 # Testing extreme values
147 #
148 CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb;
149 select next_not_cached_value,cycle_count from t1;
150 next_not_cached_value cycle_count
151 1 0
152 select setval(t1,200);
153 setval(t1,200)
154 200
155 select next_not_cached_value,cycle_count from t1;
156 next_not_cached_value cycle_count
157 101 0
158 select next value for t1;
159 ERROR HY000: Sequence 'test.t1' has run out
160 drop sequence t1;
161 CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
162 select next_not_cached_value,cycle_count from t1;
163 next_not_cached_value cycle_count
164 1 0
165 select setval(t1,200);
166 setval(t1,200)
167 200
168 select next_not_cached_value,cycle_count from t1;
169 next_not_cached_value cycle_count
170 101 0
171 select next value for t1;
172 next value for t1
173 1
174 drop sequence t1;
175 CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
176 select setval(t1,-10);
177 setval(t1,-10)
178 -10
179 select next_not_cached_value,cycle_count from t1;
180 next_not_cached_value cycle_count
181 -20 0
182 select next value for t1;
183 next value for t1
184 -20
185 select setval(t1,-15);
186 setval(t1,-15)
187 NULL
188 select next_not_cached_value,cycle_count from t1;
189 next_not_cached_value cycle_count
190 -120 0
191 select next value for t1;
192 next value for t1
193 -30
194 select setval(t1,-500,FALSE);
195 setval(t1,-500,FALSE)
196 -500
197 select next value for t1;
198 next value for t1
199 -500
200 select next value for t1;
201 next value for t1
202 -510
203 select setval(t1,-525,0);
204 setval(t1,-525,0)
205 -525
206 select next value for t1;
207 next value for t1
208 -525
209 select next value for t1;
210 next value for t1
211 -535
212 drop sequence t1;
213 CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
214 select setval(t1,-10,0);
215 setval(t1,-10,0)
216 -10
217 select next_not_cached_value,cycle_count from t1;
218 next_not_cached_value cycle_count
219 -10 0
220 select next value for t1;
221 next value for t1
222 -10
223 drop sequence t1;
224 #
225 # Other testing
226 #
227 CREATE SEQUENCE t1;
228 select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1);
229 setval(t1,10,0) setval(t1,15,1) setval(t1,5,1)
230 10 15 NULL
231 select next value for t1;
232 next value for t1
233 16
234 select next_not_cached_value,cycle_count from t1;
235 next_not_cached_value cycle_count
236 1016 0
237 explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50);
238 id select_type table type possible_keys key key_len ref rows filtered Extra
239 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
240 Warnings:
241 Note 1003 select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)`
242 drop sequence t1;
243 create table t1 (a int);
244 select setval(t1,10);
245 ERROR 42S02: 'test.t1' is not a SEQUENCE
246 drop table t1;
247 #
248 # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
249 #
250 CREATE SEQUENCE s1;
251 SELECT SETVAL(s1,10);
252 Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
253 def SETVAL(s1,10) 8 20 2 Y 32896 0 63
254 SETVAL(s1,10)
255 10
256 DROP SEQUENCE s1;
257 #
258 # MDEV-15732: Assertion `next_free_value % real_increment == offset &&
259 # next_free_value >= reserved_until' failed in
260 # sequence_definition::adjust_values upon SETVAL for sequence with
261 # INCREMENT 0
262 #
263 CREATE SEQUENCE s INCREMENT 0;
264 SELECT NEXTVAL(s);
265 NEXTVAL(s)
266 1
267 SELECT SETVAL(s, 10);
268 SETVAL(s, 10)
269 10
270 DROP SEQUENCE s;
271 # End of 10.3 tests
272 #
273 # MDEV-28152 Features for sequence
274 #
275 create sequence s;
276 select setval(s, 12345678901234567890);
277 setval(s, 12345678901234567890)
278 NULL
279 select nextval(s);
280 nextval(s)
281 1
282 drop sequence s;
283 create sequence s increment -1;
284 select setval(s, 12345678901234567890);
285 setval(s, 12345678901234567890)
286 NULL
287 select nextval(s);
288 nextval(s)
289 -1
290 drop sequence s;
291 create sequence s as smallint;
292 select setval(s, 55555);
293 setval(s, 55555)
294 NULL
295 select setval(s, -55555);
296 setval(s, -55555)
297 NULL
298 select nextval(s);
299 nextval(s)
300 1
301 drop sequence s;
302 create sequence s as smallint increment -1;
303 select setval(s, 55555);
304 setval(s, 55555)
305 NULL
306 select setval(s, -55555);
307 setval(s, -55555)
308 NULL
309 select nextval(s);
310 nextval(s)
311 -1
312 drop sequence s;
313 create sequence s as bigint unsigned;
314 select setval(s, -123);
315 setval(s, -123)
316 NULL
317 select nextval(s);
318 nextval(s)
319 1
320 drop sequence s;
321 create sequence s as bigint unsigned increment -1;
322 select setval(s, -123);
323 setval(s, -123)
324 NULL
325 select nextval(s);
326 nextval(s)
327 18446744073709551614
328 drop sequence s;
329 create sequence s as bigint unsigned;
330 select setval(s, -123);
331 setval(s, -123)
332 NULL
333 select setval(s, 0);
334 setval(s, 0)
335 0
336 select nextval(s);
337 nextval(s)
338 1
339 select setval(s, 12345678901234567890);
340 setval(s, 12345678901234567890)
341 12345678901234567890
342 select nextval(s);
343 nextval(s)
344 12345678901234567891
345 select setval(s, 12345678901234567890);
346 setval(s, 12345678901234567890)
347 NULL
348 select nextval(s);
349 nextval(s)
350 12345678901234567892
351 select setval(s, 18446744073709551614, 0);
352 setval(s, 18446744073709551614, 0)
353 18446744073709551614
354 select nextval(s);
355 nextval(s)
356 18446744073709551614
357 select nextval(s);
358 ERROR HY000: Sequence 'test.s' has run out
359 select setval(s, 12345678901234567890, 0, 1);
360 ERROR HY000: Sequence 'test.s' has run out
361 alter sequence s cycle;
362 select setval(s, 12345678901234567890, 0, 1);
363 setval(s, 12345678901234567890, 0, 1)
364 12345678901234567890
365 select nextval(s);
366 nextval(s)
367 12345678901234567890
368 drop sequence s;
369 create sequence s as bigint unsigned;
370 select setval(s, 18446744073709551616);
371 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '18446744073709551616)' at line 1
372 select setval(s, 18446744073709551615);
373 setval(s, 18446744073709551615)
374 18446744073709551615
375 select nextval(s);
376 ERROR HY000: Sequence 'test.s' has run out
377 drop sequence s;
378 create sequence s as bigint unsigned;
379 select setval(s, 18446744073709551615, 0);
380 setval(s, 18446744073709551615, 0)
381 18446744073709551615
382 select nextval(s);
383 ERROR HY000: Sequence 'test.s' has run out
384 drop sequence s;
385 create sequence s as bigint unsigned cycle;
386 select setval(s, 18446744073709551615);
387 setval(s, 18446744073709551615)
388 18446744073709551615
389 select nextval(s);
390 nextval(s)
391 1
392 drop sequence s;
393 create sequence s as bigint unsigned cycle;
394 select setval(s, 18446744073709551615, 0);
395 setval(s, 18446744073709551615, 0)
396 18446744073709551615
397 select nextval(s);
398 nextval(s)
399 1
400 drop sequence s;
401 create sequence s as bigint unsigned minvalue 123 maxvalue 456;
402 select setval(s, 100);
403 setval(s, 100)
404 NULL
405 select nextval(s);
406 nextval(s)
407 123
408 select setval(s, 500);
409 setval(s, 500)
410 500
411 select nextval(s);
412 ERROR HY000: Sequence 'test.s' has run out
413 drop sequence s;
414 create sequence s as smallint;
415 select setval(s, 32767);
416 setval(s, 32767)
417 32767
418 select nextval(s);
419 ERROR HY000: Sequence 'test.s' has run out
420 drop sequence s;
421 #
422 # MDEV-33836 Assertion `(ulonglong) next_free_value % real_increment == (ulonglong) offset' failed in void sequence_definition::adjust_values(longlong)
423 #
424 CREATE SEQUENCE s AS BIGINT UNSIGNED START WITH 9223372036854775800 INCREMENT 0;
425 set @old_AUTO_INCREMENT_INCREMENT=@@global.AUTO_INCREMENT_INCREMENT;
426 set global AUTO_INCREMENT_INCREMENT=100;
427 SELECT SETVAL (s,12345678901234567890);
428 SETVAL (s,12345678901234567890)
429 12345678901234567890
430 drop sequence s;
431 set global AUTO_INCREMENT_INCREMENT=@old_AUTO_INCREMENT_INCREMENT;
432 # End of 11.5 tests