Debian Codegraph @

mysql-test/suite/sql_sequence/alter.result

raw
1 drop table if exists t1;
2 Warnings:
3 Note 1051 Unknown table 'test.t1'
4 #
5 # Test alter sequence
6 #
7 CREATE SEQUENCE t1 nocache engine=myisam;
8 select * from t1;
9 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
10 1 1 9223372036854775806 1 1 0 0 0
11 select next value for t1;
12 next value for t1
13 1
14 alter sequence t1 start=50;
15 show create sequence t1;
16 Table Create Table
17 t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
18 select * from t1;
19 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
20 2 1 9223372036854775806 50 1 0 0 0
21 select next value for t1;
22 next value for t1
23 2
24 alter sequence t1 minvalue=-100;
25 show create sequence t1;
26 Table Create Table
27 t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
28 select * from t1;
29 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
30 3 -100 9223372036854775806 50 1 0 0 0
31 alter sequence t1 minvalue=100 start=100;
32 ERROR HY000: Sequence 'test.t1' has out of range value for options
33 alter sequence t1 minvalue=100 start=100 restart=100;
34 show create sequence t1;
35 Table Create Table
36 t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
37 select * from t1;
38 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
39 100 100 9223372036854775806 100 1 0 0 0
40 alter sequence t1 maxvalue=500;
41 show create sequence t1;
42 Table Create Table
43 t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM
44 select * from t1;
45 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
46 100 100 500 100 1 0 0 0
47 drop sequence t1;
48 CREATE SEQUENCE t1 engine=myisam;
49 alter sequence t1 nocache;
50 show create sequence t1;
51 Table Create Table
52 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
53 alter sequence t1 cache=100;
54 flush tables;
55 show create sequence t1;
56 Table Create Table
57 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM
58 alter sequence t1 nocache;
59 show create sequence t1;
60 Table Create Table
61 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
62 flush tables;
63 show create sequence t1;
64 Table Create Table
65 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
66 select * from t1;
67 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
68 1 1 9223372036854775806 1 1 0 0 0
69 select next value for t1;
70 next value for t1
71 1
72 select next value for t1;
73 next value for t1
74 2
75 select next value for t1;
76 next value for t1
77 3
78 select next_not_cached_value, cycle_count from t1;
79 next_not_cached_value cycle_count
80 4 0
81 drop sequence t1;
82 CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
83 alter sequence t1 no maxvalue;
84 show create sequence t1;
85 Table Create Table
86 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
87 select * from t1;
88 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
89 1 1 9223372036854775806 1 1 1000 0 0
90 alter sequence t1 cycle;
91 show create sequence t1;
92 Table Create Table
93 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
94 alter sequence t1 nocycle;
95 alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
96 show create sequence t1;
97 Table Create Table
98 t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM
99 select * from t1;
100 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
101 15 10 20 15 1 1000 1 0
102 select NEXT VALUE for t1 from seq_1_to_10;
103 NEXT VALUE for t1
104 15
105 16
106 17
107 18
108 19
109 20
110 10
111 11
112 12
113 13
114 alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
115 select NEXT VALUE for t1 from seq_1_to_10;
116 NEXT VALUE for t1
117 17
118 18
119 19
120 20
121 10
122 11
123 12
124 13
125 14
126 15
127 drop sequence t1;
128 CREATE SEQUENCE t1 maxvalue=100;
129 alter sequence t1 increment=-2 start with 50 minvalue=-100;
130 show create sequence t1;
131 Table Create Table
132 t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM
133 select * from t1;
134 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
135 1 -100 100 50 -2 1000 0 0
136 select NEXT VALUE for t1 from seq_1_to_10;
137 NEXT VALUE for t1
138 1
139 -1
140 -3
141 -5
142 -7
143 -9
144 -11
145 -13
146 -15
147 -17
148 drop sequence t1;
149 #
150 # InnoDB (some things work different with InnoDB)
151
152 CREATE SEQUENCE t1 cache 10 engine=innodb;
153 select * from t1;
154 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
155 1 1 9223372036854775806 1 1 10 0 0
156 select next value for t1;
157 next value for t1
158 1
159 alter sequence t1 start=100;
160 show create sequence t1;
161 Table Create Table
162 t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB
163 select * from t1;
164 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
165 11 1 9223372036854775806 100 1 10 0 0
166 select next value for t1;
167 next value for t1
168 11
169 $check_innodb_flags;
170 is_sequence
171 12288
172 alter table t1 sequence=0;
173 begin;
174 delete from t1;
175 rollback;
176 $check_innodb_flags;
177 is_sequence
178 0
179 alter table t1 sequence=1;
180 $check_innodb_flags;
181 is_sequence
182 12288
183 alter table t1 sequence=0, algorithm=copy;
184 $check_innodb_flags;
185 is_sequence
186 0
187 alter table t1 sequence=1, algorithm=inplace;
188 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: SEQUENCE. Try ALGORITHM=COPY
189 alter table t1 sequence=1, algorithm=copy;
190 $check_innodb_flags;
191 is_sequence
192 12288
193 alter table t1 sequence=0, algorithm=inplace;
194 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: SEQUENCE. Try ALGORITHM=COPY
195 drop sequence t1;
196 #
197 # ALTER TABLE
198 #
199 CREATE SEQUENCE t1 engine=innodb;
200 select next value for t1;
201 next value for t1
202 1
203 alter table t1 rename t2;
204 select next value for t2;
205 next value for t2
206 1001
207 rename table t2 to t1;
208 select next value for t1;
209 next value for t1
210 2001
211 alter table t1 comment="foo";
212 show create sequence t1;
213 Table Create Table
214 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo'
215 alter table t1 engine=myisam;
216 show create sequence t1;
217 Table Create Table
218 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo'
219 alter table t1 engine=innodb;
220 show create sequence t1;
221 Table Create Table
222 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo'
223 select * from t1;
224 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
225 3001 1 9223372036854775806 1 1 1000 0 0
226 drop sequence t1;
227 CREATE SEQUENCE t1 engine=myisam;
228 alter sequence t1 minvalue=100;
229 ERROR HY000: Sequence 'test.t1' has out of range value for options
230 drop sequence t1;
231 CREATE SEQUENCE t1 engine=myisam;
232 alter sequence t1 minvalue=25 maxvalue=20;
233 ERROR HY000: Sequence 'test.t1' has out of range value for options
234 drop sequence t1;
235 create table t1 (a int);
236 alter sequence t1 minvalue=100;
237 ERROR 42S02: 'test.t1' is not a SEQUENCE
238 drop table t1;
239 #
240 # MDEV-32795: ALTER SEQUENCE IF NOT EXISTS non_existing_seq Errors rather than note
241 #
242 alter sequence if exists t1 minvalue=100;
243 Warnings:
244 Note 4091 Unknown SEQUENCE: 'test.t1'
245 alter sequence t1 minvalue=100;
246 ERROR 42S02: Unknown SEQUENCE: 't1'
247 create sequence t1;
248 alter sequence t1;
249 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 '' at line 1
250 drop sequence t1;
251 CREATE SEQUENCE t1 maxvalue=100;
252 alter sequence t1 increment=-2 start with 50;
253 select next value for t1;
254 next value for t1
255 1
256 select next value for t1;
257 ERROR HY000: Sequence 'test.t1' has run out
258 select * from t1;
259 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
260 0 1 100 50 -2 1000 0 0
261 alter sequence t1 restart;
262 select next value for t1;
263 next value for t1
264 50
265 alter sequence t1 restart with 90;
266 select next value for t1;
267 next value for t1
268 90
269 drop sequence t1;
270 CREATE SEQUENCE t1 engine=innodb;
271 ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value);
272 ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints)
273 DROP SEQUENCE t1;
274 CREATE SEQUENCE s;
275 ALTER TABLE s ORDER BY cache_size;
276 ERROR HY000: Sequence 'test.s' table structure is invalid (ORDER BY)
277 SELECT NEXTVAL(s);
278 NEXTVAL(s)
279 1
280 DROP SEQUENCE s;
281 #
282 # MDEV-33169 Alter sequence 2nd ps fails while alter sequence 2nd time (no ps) succeeds
283 #
284 create sequence s;
285 show create sequence s;
286 Table Create Table
287 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
288 alter sequence s maxvalue 123;
289 show create sequence s;
290 Table Create Table
291 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
292 alter sequence s maxvalue 123;
293 show create sequence s;
294 Table Create Table
295 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
296 drop sequence s;
297 create sequence s;
298 show create sequence s;
299 Table Create Table
300 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
301 prepare stmt from 'alter sequence s maxvalue 123';
302 execute stmt;
303 show create sequence s;
304 Table Create Table
305 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
306 execute stmt;
307 show create sequence s;
308 Table Create Table
309 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
310 deallocate prepare stmt;
311 drop sequence s;
312 create sequence s;
313 show create sequence s;
314 Table Create Table
315 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
316 create procedure p() alter sequence s maxvalue 123;
317 call p;
318 show create sequence s;
319 Table Create Table
320 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
321 call p;
322 show create sequence s;
323 Table Create Table
324 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
325 drop procedure p;
326 drop sequence s;
327 #
328 # End of 10.4 tests
329 #
330 #
331 # MDEV-32350 Can't selectively restore sequences using innodb tables from
332 # backup
333 #
334 create sequence s2 engine=innodb;
335 alter table s2 discard tablespace;
336 SELECT NEXTVAL(s2);
337 ERROR HY000: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
338 create sequence s1 engine=innodb;
339 select * from s1;
340 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
341 1 1 9223372036854775806 1 1 1000 0 0
342 flush tables s1 for export;
343 unlock tables;
344 select * from s2;
345 ERROR HY000: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
346 SELECT NEXTVAL(s2);
347 ERROR HY000: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
348 alter sequence s2 restart;
349 ERROR HY000: Got error 194 "Tablespace is missing for a table" from storage engine InnoDB
350 alter table s2 import tablespace;
351 select * from s2;
352 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
353 1 1 9223372036854775806 1 1 1000 0 0
354 SELECT NEXTVAL(s2);
355 NEXTVAL(s2)
356 1
357 select NEXTVAL(s1);
358 NEXTVAL(s1)
359 1
360 flush table s1,s2;
361 select * from s1;
362 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
363 1001 1 9223372036854775806 1 1 1000 0 0
364 select * from s2;
365 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
366 1001 1 9223372036854775806 1 1 1000 0 0
367 drop sequence s1,s2;
368 #
369 # MDEV-35144 CREATE TABLE ... LIKE uses current innodb_compression_default instead of the create value
370 #
371 set @@innodb_compression_default= off;
372 create or replace sequence s engine=innodb;
373 set @@innodb_compression_default= on;
374 create or replace table s_import like s;
375 show create table s;
376 Table Create Table
377 s CREATE TABLE `s` (
378 `next_not_cached_value` bigint(21) NOT NULL,
379 `minimum_value` bigint(21) NOT NULL,
380 `maximum_value` bigint(21) NOT NULL,
381 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
382 `increment` bigint(21) NOT NULL COMMENT 'increment value',
383 `cache_size` bigint(21) unsigned NOT NULL,
384 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
385 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
386 ) ENGINE=InnoDB SEQUENCE=1
387 show create table s_import;
388 Table Create Table
389 s_import CREATE TABLE `s_import` (
390 `next_not_cached_value` bigint(21) NOT NULL,
391 `minimum_value` bigint(21) NOT NULL,
392 `maximum_value` bigint(21) NOT NULL,
393 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
394 `increment` bigint(21) NOT NULL COMMENT 'increment value',
395 `cache_size` bigint(21) unsigned NOT NULL,
396 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
397 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
398 ) ENGINE=InnoDB SEQUENCE=1
399 alter table s_import discard tablespace;
400 flush table s for export;
401 UNLOCK TABLES;
402 alter table s_import import tablespace;
403 drop table s,s_import;
404 # End of 10.5 tests
405 #
406 # MDEV-31607 ER_DUP_KEY in mysql.table_stats upon REANME on sequence
407 #
408 CREATE SEQUENCE s1 ENGINE=InnoDB;
409 CREATE SEQUENCE s2 ENGINE=InnoDB;
410 SHOW CREATE SEQUENCE s1;
411 Table Create Table
412 s1 CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB `PAGE_COMPRESSED`='ON'
413 SHOW CREATE SEQUENCE s2;
414 Table Create Table
415 s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB `PAGE_COMPRESSED`='ON'
416 DROP SEQUENCE s2;
417 RENAME TABLE s1 TO s2;
418 DROP SEQUENCE s2;
419 #
420 # End of 10.6 tests
421 #
422 #
423 # MDEV-36032 Check when doing ALTER TABLE table_name sequence=1 that table can be a sequence
424 #
425 create sequence s;
426 alter table s sequence=0;
427 insert into s values (3,1,9223372036854775806,1,1,1000,0,0);
428 alter table s sequence=1;
429 ERROR HY000: Internal error: More than one row in the table
430 drop table s;
431 create sequence s;
432 alter table s sequence=0;
433 delete from s;
434 insert into s values (2,500,200,1,1,1000,0,0);
435 select * from s;
436 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
437 2 500 200 1 1 1000 0 0
438 alter table s sequence=1;
439 ERROR HY000: Sequence 'test.s' has out of range value for options
440 check table s;
441 Table Op Msg_type Msg_text
442 test.s check status OK
443 select * from s;
444 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
445 2 500 200 1 1 1000 0 0
446 check table s;
447 Table Op Msg_type Msg_text
448 test.s check status OK
449 drop table s;
450 CREATE TABLE `s` (
451 # `next_not_cached_value` bigint(21) NOT NULL,
452 `minimum_value` bigint(21) NOT NULL,
453 `maximum_value` bigint(21) NOT NULL,
454 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
455 `increment` bigint(21) NOT NULL COMMENT 'increment value',
456 `cache_size` bigint(21) unsigned NOT NULL,
457 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
458 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
459 ) ENGINE=innodb;
460 alter table s sequence=1;
461 ERROR HY000: Sequence 'test.s' table structure is invalid (Wrong number of columns)
462 drop table s;
463 create sequence s;
464 alter table s drop column next_not_cached_value;
465 ERROR HY000: Sequence 'test.s' table structure is invalid (Wrong number of columns)
466 drop sequence s;
467 CREATE TABLE `s1` (
468 `next_not_cached_value` bigint(21) NOT NULL,
469 `minimum_value` bigint(21) NOT NULL,
470 `maximum_value` bigint(21) NOT NULL,
471 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
472 `increment` bigint(21) NOT NULL COMMENT 'increment value',
473 `cache_size` bigint(21) unsigned NOT NULL,
474 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
475 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
476 ) ENGINE=innodb;
477 alter table s1 sequence=1;
478 ERROR HY000: Internal error: Fewer than one row in the table
479 alter table s1 sequence=0;
480 insert into s1 values (1,1,9223372036854775806,1,1,1000,0,0);
481 alter table s1 sequence=1;
482 alter table s1 sequence=0;
483 insert into s1 values (2,1,9223372036854775806,1,1,1000,0,0);
484 alter table s1 sequence=1;
485 ERROR HY000: Internal error: More than one row in the table
486 alter table s1 sequence=0;
487 insert into s1 values (3,1,9223372036854775806,1,1,1000,0,0);
488 alter table s1 sequence=1;
489 ERROR HY000: Internal error: More than one row in the table
490 drop table s1;
491 # End of 10.11 tests
492 #
493 # MDEV-28152 Features for sequence
494 #
495 create sequence s maxvalue 12345;
496 show create sequence s;
497 Table Create Table
498 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM
499 alter sequence s maxvalue 123456789012345678901234;
500 Warnings:
501 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
502 show create sequence s;
503 Table Create Table
504 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
505 drop sequence s;
506 create sequence s as tinyint;
507 show create sequence s;
508 Table Create Table
509 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
510 show create table s;
511 Table Create Table
512 s CREATE TABLE `s` (
513 `next_not_cached_value` tinyint(5) NOT NULL,
514 `minimum_value` tinyint(5) NOT NULL,
515 `maximum_value` tinyint(5) NOT NULL,
516 `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
517 `increment` bigint(21) NOT NULL COMMENT 'increment value',
518 `cache_size` bigint(21) unsigned NOT NULL,
519 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
520 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
521 ) ENGINE=MyISAM SEQUENCE=1
522 alter sequence s as int;
523 show create sequence s;
524 Table Create Table
525 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
526 show create table s;
527 Table Create Table
528 s CREATE TABLE `s` (
529 `next_not_cached_value` int(12) NOT NULL,
530 `minimum_value` int(12) NOT NULL,
531 `maximum_value` int(12) NOT NULL,
532 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
533 `increment` bigint(21) NOT NULL COMMENT 'increment value',
534 `cache_size` bigint(21) unsigned NOT NULL,
535 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
536 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
537 ) ENGINE=MyISAM SEQUENCE=1
538 alter sequence s maxvalue 12345;
539 show create sequence s;
540 Table Create Table
541 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM
542 show create table s;
543 Table Create Table
544 s CREATE TABLE `s` (
545 `next_not_cached_value` int(12) NOT NULL,
546 `minimum_value` int(12) NOT NULL,
547 `maximum_value` int(12) NOT NULL,
548 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
549 `increment` bigint(21) NOT NULL COMMENT 'increment value',
550 `cache_size` bigint(21) unsigned NOT NULL,
551 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
552 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
553 ) ENGINE=MyISAM SEQUENCE=1
554 drop sequence s;
555 create sequence s;
556 show create sequence s;
557 Table Create Table
558 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
559 show create table s;
560 Table Create Table
561 s CREATE TABLE `s` (
562 `next_not_cached_value` bigint(21) NOT NULL,
563 `minimum_value` bigint(21) NOT NULL,
564 `maximum_value` bigint(21) NOT NULL,
565 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
566 `increment` bigint(21) NOT NULL COMMENT 'increment value',
567 `cache_size` bigint(21) unsigned NOT NULL,
568 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
569 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
570 ) ENGINE=MyISAM SEQUENCE=1
571 alter sequence s maxvalue 123;
572 show create sequence s;
573 Table Create Table
574 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
575 show create table s;
576 Table Create Table
577 s CREATE TABLE `s` (
578 `next_not_cached_value` bigint(21) NOT NULL,
579 `minimum_value` bigint(21) NOT NULL,
580 `maximum_value` bigint(21) NOT NULL,
581 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
582 `increment` bigint(21) NOT NULL COMMENT 'increment value',
583 `cache_size` bigint(21) unsigned NOT NULL,
584 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
585 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
586 ) ENGINE=MyISAM SEQUENCE=1
587 alter sequence s as tinyint;
588 show create sequence s;
589 Table Create Table
590 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM
591 show create table s;
592 Table Create Table
593 s CREATE TABLE `s` (
594 `next_not_cached_value` tinyint(5) NOT NULL,
595 `minimum_value` tinyint(5) NOT NULL,
596 `maximum_value` tinyint(5) NOT NULL,
597 `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
598 `increment` bigint(21) NOT NULL COMMENT 'increment value',
599 `cache_size` bigint(21) unsigned NOT NULL,
600 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
601 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
602 ) ENGINE=MyISAM SEQUENCE=1
603 drop sequence s;
604 create sequence s as int;
605 show create sequence s;
606 Table Create Table
607 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
608 show create table s;
609 Table Create Table
610 s CREATE TABLE `s` (
611 `next_not_cached_value` int(12) NOT NULL,
612 `minimum_value` int(12) NOT NULL,
613 `maximum_value` int(12) NOT NULL,
614 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
615 `increment` bigint(21) NOT NULL COMMENT 'increment value',
616 `cache_size` bigint(21) unsigned NOT NULL,
617 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
618 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
619 ) ENGINE=MyISAM SEQUENCE=1
620 alter sequence s as tinyint;
621 ERROR 22003: Out of range value for column 'maximum_value' at row 1
622 alter sequence s maxvalue 126;
623 alter sequence s as tinyint;
624 drop sequence s;
625 create sequence s as tinyint;
626 show create sequence s;
627 Table Create Table
628 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
629 show create table s;
630 Table Create Table
631 s CREATE TABLE `s` (
632 `next_not_cached_value` tinyint(5) NOT NULL,
633 `minimum_value` tinyint(5) NOT NULL,
634 `maximum_value` tinyint(5) NOT NULL,
635 `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
636 `increment` bigint(21) NOT NULL COMMENT 'increment value',
637 `cache_size` bigint(21) unsigned NOT NULL,
638 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
639 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
640 ) ENGINE=MyISAM SEQUENCE=1
641 alter sequence s as int maxvalue 123;
642 ERROR 42000: This version of MariaDB doesn't yet support 'ALTER SEQUENCE with both AS <type> and something else.'
643 drop sequence s;
644 create sequence s as smallint;
645 select next value for s;
646 next value for s
647 1
648 alter sequence s as int;
649 select next value for s;
650 next value for s
651 1001
652 drop sequence s;
653 create sequence s as tinyint;
654 select next value for s;
655 next value for s
656 1
657 alter sequence s cache 100;
658 select next value for s;
659 ERROR HY000: Sequence 'test.s' has run out
660 drop sequence s;
661 create sequence s as int;
662 show create sequence s;
663 Table Create Table
664 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
665 show create table s;
666 Table Create Table
667 s CREATE TABLE `s` (
668 `next_not_cached_value` int(12) NOT NULL,
669 `minimum_value` int(12) NOT NULL,
670 `maximum_value` int(12) NOT NULL,
671 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
672 `increment` bigint(21) NOT NULL COMMENT 'increment value',
673 `cache_size` bigint(21) unsigned NOT NULL,
674 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
675 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
676 ) ENGINE=MyISAM SEQUENCE=1
677 alter sequence s as int unsigned;
678 show create sequence s;
679 Table Create Table
680 s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
681 show create table s;
682 Table Create Table
683 s CREATE TABLE `s` (
684 `next_not_cached_value` int(12) unsigned NOT NULL,
685 `minimum_value` int(12) unsigned NOT NULL,
686 `maximum_value` int(12) unsigned NOT NULL,
687 `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
688 `increment` bigint(21) NOT NULL COMMENT 'increment value',
689 `cache_size` bigint(21) unsigned NOT NULL,
690 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
691 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
692 ) ENGINE=MyISAM SEQUENCE=1
693 drop sequence s;
694 create sequence s as tinyint;
695 alter sequence s maxvalue 123456789012345678901;
696 Warnings:
697 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
698 show create sequence s;
699 Table Create Table
700 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
701 alter sequence s maxvalue 126;
702 show create sequence s;
703 Table Create Table
704 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
705 select next value for s;
706 next value for s
707 1
708 alter sequence s maxvalue 63;
709 show create sequence s;
710 Table Create Table
711 s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 63 increment by 1 cache 1000 nocycle ENGINE=MyISAM
712 select next value for s;
713 ERROR HY000: Sequence 'test.s' has run out
714 drop sequence s;
715 create sequence s as tinyint unsigned;
716 alter sequence s maxvalue 123456789012345678901;
717 Warnings:
718 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
719 show create sequence s;
720 Table Create Table
721 s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM
722 alter sequence s maxvalue 254;
723 show create sequence s;
724 Table Create Table
725 s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM
726 select next value for s;
727 next value for s
728 1
729 alter sequence s maxvalue 120;
730 show create sequence s;
731 Table Create Table
732 s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 120 increment by 1 cache 1000 nocycle ENGINE=MyISAM
733 select next value for s;
734 ERROR HY000: Sequence 'test.s' has run out
735 drop sequence s;
736 create sequence s as smallint;
737 alter sequence s maxvalue 123456789012345678901;
738 Warnings:
739 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
740 show create sequence s;
741 Table Create Table
742 s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM
743 alter sequence s maxvalue 32766;
744 show create sequence s;
745 Table Create Table
746 s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM
747 select next value for s;
748 next value for s
749 1
750 alter sequence s maxvalue 16030;
751 show create sequence s;
752 Table Create Table
753 s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 16030 increment by 1 cache 1000 nocycle ENGINE=MyISAM
754 select next value for s;
755 next value for s
756 1001
757 drop sequence s;
758 create sequence s as smallint unsigned;
759 alter sequence s maxvalue 123456789012345678901;
760 Warnings:
761 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
762 show create sequence s;
763 Table Create Table
764 s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM
765 alter sequence s maxvalue 65534;
766 show create sequence s;
767 Table Create Table
768 s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM
769 select next value for s;
770 next value for s
771 1
772 alter sequence s maxvalue 32000;
773 show create sequence s;
774 Table Create Table
775 s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 32000 increment by 1 cache 1000 nocycle ENGINE=MyISAM
776 select next value for s;
777 next value for s
778 1001
779 drop sequence s;
780 create sequence s as mediumint;
781 alter sequence s maxvalue 123456789012345678901;
782 Warnings:
783 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
784 show create sequence s;
785 Table Create Table
786 s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM
787 alter sequence s maxvalue 8388606;
788 show create sequence s;
789 Table Create Table
790 s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM
791 select next value for s;
792 next value for s
793 1
794 alter sequence s maxvalue 4223212;
795 show create sequence s;
796 Table Create Table
797 s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 4223212 increment by 1 cache 1000 nocycle ENGINE=MyISAM
798 select next value for s;
799 next value for s
800 1001
801 drop sequence s;
802 create sequence s as mediumint unsigned;
803 alter sequence s maxvalue 123456789012345678901;
804 Warnings:
805 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
806 show create sequence s;
807 Table Create Table
808 s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM
809 alter sequence s maxvalue 16777214;
810 show create sequence s;
811 Table Create Table
812 s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM
813 select next value for s;
814 next value for s
815 1
816 alter sequence s maxvalue 8389231;
817 show create sequence s;
818 Table Create Table
819 s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 8389231 increment by 1 cache 1000 nocycle ENGINE=MyISAM
820 select next value for s;
821 next value for s
822 1001
823 drop sequence s;
824 create sequence s as int;
825 alter sequence s maxvalue 123456789012345678901;
826 Warnings:
827 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
828 show create sequence s;
829 Table Create Table
830 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
831 alter sequence s maxvalue 2147483646;
832 show create sequence s;
833 Table Create Table
834 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
835 select next value for s;
836 next value for s
837 1
838 alter sequence s maxvalue 1234567890;
839 show create sequence s;
840 Table Create Table
841 s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 1234567890 increment by 1 cache 1000 nocycle ENGINE=MyISAM
842 select next value for s;
843 next value for s
844 1001
845 drop sequence s;
846 create sequence s as int unsigned;
847 alter sequence s maxvalue 123456789012345678901;
848 Warnings:
849 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
850 show create sequence s;
851 Table Create Table
852 s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM
853 alter sequence s maxvalue 4294967294;
854 show create sequence s;
855 Table Create Table
856 s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM
857 select next value for s;
858 next value for s
859 1
860 alter sequence s maxvalue 2123834923;
861 show create sequence s;
862 Table Create Table
863 s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 2123834923 increment by 1 cache 1000 nocycle ENGINE=MyISAM
864 select next value for s;
865 next value for s
866 1001
867 drop sequence s;
868 create sequence s as bigint;
869 alter sequence s maxvalue 123456789012345678901;
870 Warnings:
871 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
872 show create sequence s;
873 Table Create Table
874 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
875 alter sequence s maxvalue 9223372036854775806;
876 show create sequence s;
877 Table Create Table
878 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
879 select next value for s;
880 next value for s
881 1
882 alter sequence s maxvalue 4683883928492758294;
883 show create sequence s;
884 Table Create Table
885 s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 4683883928492758294 increment by 1 cache 1000 nocycle ENGINE=MyISAM
886 select next value for s;
887 next value for s
888 1001
889 drop sequence s;
890 create sequence s as bigint unsigned;
891 alter sequence s maxvalue 123456789012345678901;
892 Warnings:
893 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
894 show create sequence s;
895 Table Create Table
896 s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
897 alter sequence s maxvalue 18446744073709551614;
898 show create sequence s;
899 Table Create Table
900 s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
901 select next value for s;
902 next value for s
903 1
904 alter sequence s maxvalue 9432738420582397432;
905 show create sequence s;
906 Table Create Table
907 s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 9432738420582397432 increment by 1 cache 1000 nocycle ENGINE=MyISAM
908 select next value for s;
909 next value for s
910 1001
911 drop sequence s;
912 #
913 # MDEV-33739 Assertion `0' failed in Type_handler_typelib::max_display_length_for_field
914 #
915 CREATE SEQUENCE s1 ;
916 ALTER table s1 CHANGE `next_not_cached_value` next_not_cached_value SET('1','2','3','4','5','6','7','8','9','10');
917 ERROR HY000: Sequence 'test.s1' table structure is invalid (next_not_cached_value)
918 ALTER table s1 CHANGE `next_not_cached_value` next_not_cached_value bool;
919 ERROR HY000: Sequence 'test.s1' table structure is invalid (next_not_cached_value)
920 drop sequence s1;
921 #
922 # End of 11.5 test
923 #