Debian Codegraph @

mysql-test/suite/sql_sequence/next.test

raw
1 --source include/have_sequence.inc
2 --source include/have_innodb.inc
3
4 #
5 # Test sequence generation
6 #
7
8 --disable_ps2_protocol
9 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle;
10 show create table t1;
11 select next value for t1;
12 select next_not_cached_value,cycle_count from t1;
13 select next value for t1;
14 select next_not_cached_value,cycle_count from t1;
15 select next value for t1;
16 select next_not_cached_value,cycle_count from t1;
17 select next value for t1;
18 select next_not_cached_value,cycle_count from t1;
19 select next value for t1;
20 select next_not_cached_value,cycle_count from t1;
21 select next value for t1;
22 select next_not_cached_value,cycle_count from t1;
23 select next value for t1;
24 select next_not_cached_value,cycle_count from t1;
25 select next value for t1;
26 select next_not_cached_value,cycle_count from t1;
27 select next value for t1;
28 select next_not_cached_value,cycle_count from t1;
29 select next value for t1;
30 select next_not_cached_value,cycle_count from t1;
31 select next value for t1;
32 select next_not_cached_value,cycle_count from t1;
33
34 select NEXT VALUE for t1,seq from seq_1_to_20;
35
36 drop sequence t1;
37
38 CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria;
39 select next value for t1;
40 select next_not_cached_value,cycle_count from t1;
41 select next value for t1;
42 select next_not_cached_value,cycle_count from t1;
43 select next value for t1;
44 select next_not_cached_value,cycle_count from t1;
45 select next value for t1;
46 select next_not_cached_value,cycle_count from t1;
47 select next value for t1;
48 select next_not_cached_value,cycle_count from t1;
49 select next value for t1;
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 next value for t1;
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 next_not_cached_value,cycle_count from t1;
59 select next value for t1;
60 select next_not_cached_value,cycle_count from t1;
61
62 select NEXT VALUE for t1,seq from seq_1_to_20;
63
64 drop sequence t1;
65
66 CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle;
67 select next value for t1;
68 select next value for t1;
69 select next value for t1;
70 select previous value for t1;
71 --error ER_SEQUENCE_RUN_OUT
72 select next value for t1;
73 select previous value for t1;
74 --error ER_SEQUENCE_RUN_OUT
75 select next value for t1;
76 drop sequence t1;
77
78 create sequence s1 start with 1 cache 2 maxvalue 5;
79 select next value for s1;
80 select next value for s1;
81 select next value for s1;
82 select next value for s1;
83 select next value for s1;
84 --error ER_SEQUENCE_RUN_OUT
85 select next value for s1;
86 drop sequence s1;
87
88 #
89 # Test that flush tables jumps to next next_not_cached_value
90 #
91
92 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10;
93 select next value for t1;
94 select * from t1;
95 flush tables;
96 select next value for t1;
97 select nextval(t1);
98 drop sequence t1;
99
100 #
101 # Test currval/previous
102 #
103
104 CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
105 select previous value for t9;
106 select next value for t9;
107 select previous value for t9, lastval(t9);
108 select next value for t9;
109 select previous value for t9, lastval(t9);
110 select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20;
111 select * from t9;
112 drop sequence t9;
113
114 #
115 # CACHE = 0 should be same as CACHE = 1
116 #
117 CREATE SEQUENCE s1 cache=0;
118 select * from s1;
119 select next value for s1;
120 select next_not_cached_value from s1;
121 select next value for s1;
122 select next_not_cached_value from s1;
123 DROP SEQUENCE s1;
124 CREATE SEQUENCE s1 cache=1;
125 select next_not_cached_value from s1;
126 select next value for s1;
127 select next_not_cached_value from s1;
128 select next value for s1;
129 select next_not_cached_value from s1;
130 DROP SEQUENCE s1;
131 CREATE SEQUENCE s1 cache=2;
132 select next_not_cached_value from s1;
133 select next value for s1;
134 select next_not_cached_value from s1;
135 select next value for s1;
136 select next_not_cached_value from s1;
137 DROP SEQUENCE s1;
138
139 #
140 # Negative increment for sequence
141 #
142
143 CREATE SEQUENCE s1;
144 select next value for s1;
145 select next value for s1;
146 select next value for s1;
147 select next value for s1;
148 alter sequence s1 increment -2;
149 select * from s1;
150 select next value for s1;
151 select next value for s1;
152 alter sequence s1 restart 6;
153 select next value for s1;
154 select next value for s1;
155 select next value for s1;
156 --error ER_SEQUENCE_RUN_OUT
157 select next value for s1;
158 DROP SEQUENCE s1;
159
160 #
161 # Check what happens when one refers to a sequence that has been closed/deleted
162 #
163
164 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
165 select next value for t1;
166 select previous value for t1;
167 flush tables;
168 select previous value for t1;
169 drop sequence t1;
170 --error ER_UNKNOWN_SEQUENCES
171 select previous value for t1;
172 CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
173 select previous value for t1;
174 select next value for t1;
175 select previous value for t1;
176 drop sequence t1;
177
178 # This failed in an early build
179
180 CREATE or replace SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999
181 INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb;
182 show create table s1;
183 select * from s1;
184 select NEXT VALUE FOR s1;
185 select NEXT VALUE FOR s1;
186 select NEXT VALUE FOR s1;
187 select * from s1;
188 FLUSH TABLES;
189 select * from s1;
190 select NEXT VALUE FOR s1;
191 select * from s1;
192 drop sequence s1;
193
194 #
195 # Explain
196 #
197
198 CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
199 explain select next value for t1;
200 explain select next value for t1, minimum_value from t1;
201 drop table t1;
202
203 #
204 # Using insert with NEXT VALUE
205 #
206
207 CREATE SEQUENCE s1;
208 CREATE TABLE t1 (a int);
209 insert into t1 values (next value for s1);
210 insert into t1 values (next value for s1);
211 select * from t1;
212 drop table t1,s1;
213
214 #
215 # Using update with NEXT VALUE
216 #
217
218 CREATE SEQUENCE s1;
219 CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam;
220 insert into t1 values (),(),(),(),(),(),();
221 update t1 set b= next value for s1 where a <= 3;
222 select * from t1;
223 drop table t1,s1;
224
225 #
226 # NO CACHE and InnoDB
227 #
228
229 CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
230 select * from s1;
231 select next value for s1;
232 explain extended select next value for s1;
233 explain extended select previous value for s1;
234 drop sequence s1;
235
236 #
237 # Some error testing
238 #
239
240 create table t1 (a int);
241 --error ER_NOT_SEQUENCE
242 select next value for t1;
243 drop table t1;
244
245 create sequence t1;
246 select next value for t1;
247 --error ER_BAD_FIELD_ERROR
248 select next value for t1, minimum_value;
249 drop sequence t1;
250
251 --echo #
252 --echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
253 --echo #
254
255 --enable_metadata
256 --disable_ps_protocol
257 CREATE SEQUENCE s1;
258 SELECT
259 NEXT VALUE FOR s1,
260 PREVIOUS VALUE FOR s1;
261 DROP SEQUENCE s1;
262 --enable_ps_protocol
263 --disable_metadata
264
265 --echo #
266 --echo # MDEV-13720 ER_NOT_SEQUENCE for temporary table
267 --echo #
268
269 create temporary table tmp (i int);
270 --error ER_NOT_SEQUENCE
271 select next value for tmp;
272 drop table tmp;
273
274 --echo #
275 --echo # Test negative numbers
276 --echo #
277
278 create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1;
279 select next value for s;
280 select next value for s;
281 flush tables;
282 select next value for s;
283 drop sequence s;
284
285 --echo #
286 --echo # MDEV-23823 NEXT VALUE crash on locked view
287 --echo #
288 CREATE VIEW v AS SELECT 1;
289 LOCK TABLE v READ;
290 --error ER_NOT_SEQUENCE
291 SELECT NEXT VALUE FOR v;
292
293 --echo #
294 --echo # MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL
295 --echo #
296 --error ER_NOT_SEQUENCE
297 SELECT SETVAL (v,0);
298
299 UNLOCK TABLES;
300 DROP VIEW v;
301
302 --echo #
303 --echo # MDEV-28650 Server crashes in Item_func_nextval::val_int after select from view
304 --echo #
305 create sequence s;
306 create table t1 (a int not null default nextval(s));
307 insert into t1 values(),();
308 flush tables;
309 select default(a) from t1;
310
311 create algorithm=merge view v1 as select default(a) from t1;
312 select * from v1;
313 Select * from v1;
314 prepare ps from 'SELECT * from v1';
315 execute ps; Execute ps;
316 drop prepare ps;
317
318 create or replace algorithm=temptable view v1 as select default(a) from t1;
319 select * from v1;
320 Select * from v1;
321 prepare ps from 'SELECT * from v1';
322 execute ps; Execute ps;
323 drop prepare ps;
324
325 # cleanup
326 drop view v1;
327 drop table t1;
328 drop sequence s;
329
330 --echo #
331 --echo # MDEV-33985 Server crashes at Item_func_nextval::val_int
332 --echo #
333 --error ER_NO_SUCH_TABLE
334 select * from (select 1 as a) x where (nextval(x));
335 --error ER_NOT_SEQUENCE2
336 select (with x(x) as (select 1) select * from x where (nextval(x)));
337 create sequence s;
338 --error ER_NO_SUCH_TABLE
339 select next value for s1 from s as s1;
340 drop sequence s;
341
342 --echo #
343 --echo # MDEV-38815 Server crashes in Item_func_nextval::val_int upon update on a view
344 --echo #
345 create sequence s;
346 create table t (a int default(nextval(s)));
347 create view v as select * from t;
348 insert into t values (1);
349 update v set a= default;
350
351 drop view v;
352 drop table t;
353 drop sequence s;
354
355 --echo # End of 10.11 tests
356
357 --echo #
358 --echo # MDEV-28152 Features for sequence
359 --echo #
360
361 create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2;
362 show create sequence t1;
363 show create table t1;
364 select * from t1;
365 select next value for t1;
366 select next value for t1;
367 --error ER_SEQUENCE_RUN_OUT
368 select next value for t1;
369
370 create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2 cycle;
371 show create sequence t1;
372 show create table t1;
373 select * from t1;
374 select next value for t1;
375 select next value for t1;
376 select next value for t1;
377
378 --disable_ps_protocol
379 create or replace sequence t1 minvalue -23 maxvalue 99999 as tinyint;
380 --enable_ps_protocol
381 show create sequence t1;
382 show create table t1;
383 select * from t1;
384 select next value for t1;
385 select next value for t1;
386 select next value for t1;
387
388 #test previous value for bigint unsigned
389 create or replace sequence t1 as bigint unsigned start with 18446744073709551614;
390 select previous value for t1;
391 select next value for t1;
392 select previous value for t1;
393
394 create or replace sequence t1 as tinyint start with 126;
395 select next value for t1;
396 --error ER_SEQUENCE_RUN_OUT
397 select next value for t1;
398 alter sequence t1 cycle;
399 select next value for t1;
400
401 create or replace sequence t1 as tinyint unsigned start with 254;
402 select next value for t1;
403 --error ER_SEQUENCE_RUN_OUT
404 select next value for t1;
405 alter sequence t1 cycle;
406 select next value for t1;
407
408 create or replace sequence t1 as smallint start with 32766;
409 select next value for t1;
410 --error ER_SEQUENCE_RUN_OUT
411 select next value for t1;
412 alter sequence t1 cycle;
413 select next value for t1;
414
415 create or replace sequence t1 as smallint unsigned start with 65534;
416 select next value for t1;
417 --error ER_SEQUENCE_RUN_OUT
418 select next value for t1;
419 alter sequence t1 cycle;
420 select next value for t1;
421
422 create or replace sequence t1 as mediumint start with 8388606;
423 select next value for t1;
424 --error ER_SEQUENCE_RUN_OUT
425 select next value for t1;
426 alter sequence t1 cycle;
427 select next value for t1;
428
429 create or replace sequence t1 as mediumint unsigned start with 16777214;
430 select next value for t1;
431 --error ER_SEQUENCE_RUN_OUT
432 select next value for t1;
433 alter sequence t1 cycle;
434 select next value for t1;
435
436 create or replace sequence t1 as int start with 2147483646;
437 select next value for t1;
438 --error ER_SEQUENCE_RUN_OUT
439 select next value for t1;
440 alter sequence t1 cycle;
441 select next value for t1;
442
443 create or replace sequence t1 as int unsigned start with 4294967294;
444 select next value for t1;
445 --error ER_SEQUENCE_RUN_OUT
446 select next value for t1;
447 alter sequence t1 cycle;
448 select next value for t1;
449
450 create or replace sequence t1 as bigint start with 9223372036854775806;
451 select next value for t1;
452 --error ER_SEQUENCE_RUN_OUT
453 select next value for t1;
454 alter sequence t1 cycle;
455 select next value for t1;
456
457 create or replace sequence t1 as bigint unsigned start with 18446744073709551614;
458 select next value for t1;
459 --error ER_SEQUENCE_RUN_OUT
460 select next value for t1;
461 alter sequence t1 cycle;
462 select next value for t1;
463
464 create or replace sequence t1 as tinyint start with -127 increment -1;
465 select next value for t1;
466 --error ER_SEQUENCE_RUN_OUT
467 select next value for t1;
468 alter sequence t1 cycle;
469 select next value for t1;
470
471 create or replace sequence t1 as tinyint unsigned start with 1 increment -1;
472 select next value for t1;
473 --error ER_SEQUENCE_RUN_OUT
474 select next value for t1;
475 alter sequence t1 cycle;
476 select next value for t1;
477
478 create or replace sequence t1 as smallint start with -32767 increment -1;
479 select next value for t1;
480 --error ER_SEQUENCE_RUN_OUT
481 select next value for t1;
482 alter sequence t1 cycle;
483 select next value for t1;
484
485 create or replace sequence t1 as smallint unsigned start with 1 increment -1;
486 select next value for t1;
487 --error ER_SEQUENCE_RUN_OUT
488 select next value for t1;
489 alter sequence t1 cycle;
490 select next value for t1;
491
492 create or replace sequence t1 as mediumint start with -8388607 increment -1;
493 select next value for t1;
494 --error ER_SEQUENCE_RUN_OUT
495 select next value for t1;
496 alter sequence t1 cycle;
497 select next value for t1;
498
499 create or replace sequence t1 as mediumint unsigned start with 1 increment -1;
500 select next value for t1;
501 --error ER_SEQUENCE_RUN_OUT
502 select next value for t1;
503 alter sequence t1 cycle;
504 select next value for t1;
505
506 create or replace sequence t1 as int start with -2147483647 increment -1;
507 select next value for t1;
508 --error ER_SEQUENCE_RUN_OUT
509 select next value for t1;
510 alter sequence t1 cycle;
511 select next value for t1;
512
513 create or replace sequence t1 as int unsigned start with 1 increment -1;
514 select next value for t1;
515 --error ER_SEQUENCE_RUN_OUT
516 select next value for t1;
517 alter sequence t1 cycle;
518 select next value for t1;
519
520 create or replace sequence t1 as bigint start with -9223372036854775807 increment -1;
521
522 select next value for t1;
523 --error ER_SEQUENCE_RUN_OUT
524 select next value for t1;
525 alter sequence t1 cycle;
526 select next value for t1;
527
528 create or replace sequence t1 as bigint unsigned start with 1 increment -1;
529 select next value for t1;
530 --error ER_SEQUENCE_RUN_OUT
531 select next value for t1;
532 alter sequence t1 cycle;
533 select next value for t1;
534
535 drop sequence t1;
536
537 --echo # End of 11.5 tests