Debian Codegraph @

mysql-test/suite/sql_sequence/create.test

raw
1 #
2 # Test create options with sequences
3 #
4 --source include/have_innodb.inc
5
6 drop table if exists t1;
7
8 #
9 # Check some sample engines
10 #
11
12 create or replace sequence t1 engine=myisam;
13 show create sequence t1;
14 show create table t1;
15 select * from t1;
16 create or replace sequence t1 engine=innodb;
17 show create sequence t1;
18 show create table t1;
19 select * from t1;
20 create or replace sequence t1 engine=maria;
21 show create sequence t1;
22 show create table t1;
23 select * from t1;
24
25 # Check start values
26 create or replace sequence t1 start with 10;
27 show create sequence t1;
28 select * from t1;
29 create or replace sequence t1 minvalue=11;
30 show create sequence t1;
31 select * from t1;
32 create or replace sequence t1 maxvalue=13 increment by -1;
33 show create sequence t1;
34 select * from t1;
35
36 create or replace sequence t1 increment by -1 cache 100;
37 show create sequence t1;
38 select * from t1;
39 create or replace sequence t1 cycle;
40 show create sequence t1;
41 select * from t1;
42 create or replace sequence t1 nocycle;
43 show create sequence t1;
44 select * from t1;
45 show create sequence t1;
46 create or replace sequence t1 cycle minvalue= 14;
47 show create sequence t1;
48 select * from t1;
49 create or replace sequence t1 cycle increment by -1;
50 show create sequence t1;
51
52 drop sequence t1;
53 create sequence if not exists t1;
54 create sequence if not exists t1 start with 10;
55 select * from t1;
56 show create sequence t1;
57
58 create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle;
59 show create sequence t1;
60 select * from t1;
61 create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10;
62 show create sequence t1;
63 select * from t1;
64
65 # NO MINVALUE, NO MAXVALUE
66 create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE;
67
68 # Some edge cases
69 create or replace sequence t1 start with 10 maxvalue 10;
70 create or replace sequence t1 start with 10 minvalue 10;
71 create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle;
72 create or replace sequence t1 start with 10 maxvalue=9223372036854775806;
73 create or replace sequence t1 start with 10 minvalue=-9223372036854775807;
74 drop sequence if exists t1;
75
76 create sequence t1 increment by 0;
77 show create sequence t1;
78 select * from t1;
79 drop sequence t1;
80
81 #
82 # Wrong usage and arguments to create sequence
83 #
84
85 create table t1 (a int);
86 --error ER_NOT_SEQUENCE
87 show create sequence t1;
88 --error ER_NOT_SEQUENCE2
89 drop sequence t1;
90 drop sequence if exists t1;
91
92 --error ER_SEQUENCE_INVALID_DATA
93 create sequence t1 start with 10 maxvalue=9;
94 --error ER_SEQUENCE_INVALID_DATA
95 create sequence t1 minvalue= 100 maxvalue=10;
96 --error ER_SEQUENCE_INVALID_DATA
97 create sequence t1 start with 9 minvalue=10;
98 --error ER_PARSE_ERROR
99 create or replace sequence t1 maxvalue=13, increment by -1;
100 --error ER_PARSE_ERROR
101 create or replace sequence t1 start with= 10 maxvalue=13;
102 --error ER_PARSE_ERROR
103 create or replace sequence t1 maxvalue=13, increment= -1;
104 --error ER_PARSE_ERROR
105 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
106 --error ER_PARSE_ERROR
107 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
108 --error ER_PARSE_ERROR
109 create sequence t1 RESTART WITH 10;
110 --error ER_DUP_ARGUMENT
111 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
112 # hidden constraints cache < (LONGLONG_MAX - max_increment) / max_increment
113 --error ER_SEQUENCE_INVALID_DATA
114 create sequence s increment by 3234567890123456789;
115
116 #
117 # Test with LIST COLUMNS as first command
118 #
119 create or replace sequence t1;
120 show fields from t1;
121 flush tables;
122 show fields from t1;
123 create or replace sequence t1 engine=aria;
124 show fields from t1;
125 show fields from t1;
126 flush tables;
127
128 #
129 # Test with other create options
130 #
131
132 create or replace sequence t1 comment= "test 1";
133 show create sequence t1;
134 show create table t1;
135 create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2;
136 show create sequence t1;
137 show create table t1;
138 create or replace sequence t1 start=1 increment= 2;
139 create or replace sequence t1 start 1 increment 2;
140 create or replace sequence t1 cache +1;
141 drop sequence t1;
142
143 #
144 # test with create table
145 #
146
147 CREATE TABLE t1 (
148 `next_not_cached_value` bigint(21) NOT NULL,
149 `minimum_value` bigint(21) NOT NULL,
150 `maximum_value` bigint(21) NOT NULL,
151 `start_value` bigint(21) NOT NULL,
152 `increment` bigint(21) NOT NULL,
153 `cache_size` bigint(21) unsigned NOT NULL,
154 `cycle_option` tinyint(1) unsigned NOT NULL,
155 `cycle_count` bigint(21) NOT NULL
156 ) sequence=1;
157
158 show create sequence t1;
159 show create table t1;
160 drop sequence t1;
161
162 # Wrong column name
163
164 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
165 CREATE OR REPLACE TABLE t1 (
166 `next_not_cached_value` bigint(21) NOT NULL,
167 `minimum_value` bigint(21) NOT NULL,
168 `maximum_value` bigint(21) NOT NULL,
169 `start_value` bigint(21) NOT NULL,
170 `increment` bigint(21) NOT NULL,
171 `cache_size` bigint(21) unsigned NOT NULL,
172 `cycle_option` tinyint(1) unsigned NOT NULL,
173 `cycle_count_not_exists` bigint(21) NOT NULL
174 ) sequence=1;
175
176 # Wrong type
177
178 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
179 CREATE OR REPLACE TABLE t1 (
180 `next_not_cached_value` int(21) NOT NULL,
181 `minimum_value` bigint(21) NOT NULL,
182 `maximum_value` bigint(21) NOT NULL,
183 `start_value` bigint(21) NOT NULL,
184 `increment` bigint(21) NOT NULL,
185 `cache_size` bigint(21) unsigned NOT NULL,
186 `cycle_option` tinyint(1) unsigned NOT NULL,
187 `cycle_count` bigint(21) NOT NULL
188 ) sequence=1;
189
190 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
191 CREATE OR REPLACE TABLE t1 (
192 `next_not_cached_value` bigint(21) NOT NULL,
193 `minimum_value` bigint(21) NOT NULL,
194 `maximum_value` bigint(21) NOT NULL,
195 `start_value` bigint(21) NOT NULL,
196 `increment` bigint(21) NOT NULL,
197 `cache_size` bigint(21) unsigned NOT NULL,
198 `cycle` bigint(21) unsigned NOT NULL, /* error */
199 `cycle_count` bigint(21) NOT NULL
200 ) sequence=1;
201
202
203 # Missing NOT NULL
204
205 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
206 CREATE OR REPLACE TABLE t1 (
207 `next_not_cached_value` bigint(21), /* error */
208 `minimum_value` bigint(21) NOT NULL,
209 `maximum_value` bigint(21) NOT NULL,
210 `start_value` bigint(21) NOT NULL,
211 `increment` bigint(21) NOT NULL,
212 `cache_size` bigint(21) unsigned NOT NULL,
213 `cycle_option` tinyint(1) unsigned NOT NULL,
214 `cycle_count` bigint(21) NOT NULL
215 ) sequence=1;
216
217 # Extra field
218
219 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
220 CREATE OR REPLACE TABLE t1 (
221 `next_not_cached_value` bigint(21) NOT NULL,
222 `minimum_value` bigint(21) NOT NULL,
223 `maximum_value` bigint(21) NOT NULL,
224 `start_value` bigint(21) NOT NULL,
225 `increment` bigint(21) NOT NULL,
226 `cache_size` bigint(21) unsigned NOT NULL,
227 `cycle_option` tinyint(1) unsigned NOT NULL,
228 `cycle_count` bigint(21) NOT NULL,
229 extra_field bigint(21)
230 ) sequence=1;
231
232 # Wrong field order
233
234 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
235 CREATE OR REPLACE TABLE t1 (
236 `minimum_value` bigint(21) NOT NULL,
237 `next_not_cached_value` bigint(21) NOT NULL,
238 `maximum_value` bigint(21) NOT NULL,
239 `start_value` bigint(21) NOT NULL,
240 `increment` bigint(21) NOT NULL,
241 `cache_size` bigint(21) unsigned NOT NULL,
242 `cycle_option` tinyint(1) unsigned NOT NULL,
243 `cycle_count` bigint(21) NOT NULL
244 ) sequence=1;
245
246 # key
247
248 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
249 CREATE OR REPLACE TABLE t1 (
250 `next_not_cached_value` bigint(21) NOT NULL,
251 `minimum_value` bigint(21) NOT NULL,
252 `maximum_value` bigint(21) NOT NULL,
253 `start_value` bigint(21) NOT NULL,
254 `increment` bigint(21) NOT NULL,
255 `cache_size` bigint(21) unsigned NOT NULL,
256 `cycle_option` tinyint(1) unsigned NOT NULL,
257 `cycle_count` bigint(21) NOT NULL,
258 key key1 (next_not_cached_value)
259 ) sequence=1;
260
261 # Check constraint
262
263 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
264 CREATE TABLE t1 (
265 `next_not_cached_value` bigint(21) NOT NULL,
266 `minimum_value` bigint(21) NOT NULL,
267 `maximum_value` bigint(21) NOT NULL,
268 `start_value` bigint(21) NOT NULL,
269 `increment` bigint(21) NOT NULL,
270 `cache_size` bigint(21) unsigned NOT NULL,
271 `cycle_option` tinyint(1) unsigned NOT NULL,
272 `cycle_count` bigint(21) NOT NULL,
273 CHECK (start_value < minimum_value)
274 ) sequence=1;
275
276 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
277 CREATE TABLE t1 (
278 `next_not_cached_value` bigint(21) NOT NULL,
279 `minimum_value` bigint(21) NOT NULL,
280 `maximum_value` bigint(21) NOT NULL,
281 `start_value` bigint(21) NOT NULL CHECK (start_value < minimum_value),
282 `increment` bigint(21) NOT NULL,
283 `cache_size` bigint(21) unsigned NOT NULL,
284 `cycle_option` tinyint(1) unsigned NOT NULL,
285 `cycle_count` bigint(21) NOT NULL
286 ) sequence=1;
287
288
289 # Virtual field
290
291 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
292 CREATE TABLE t1 (
293 `next_not_cached_value` bigint(21) NOT NULL,
294 `minimum_value` bigint(21) NOT NULL,
295 `maximum_value` bigint(21) NOT NULL,
296 `start_value` bigint(21) NOT NULL,
297 `increment` bigint(21) NOT NULL,
298 `cache_size` bigint(21) unsigned NOT NULL,
299 `cycle_option` tinyint(1) unsigned NOT NULL,
300 `cycle_count` bigint(21) generated always as (1) virtual
301 ) sequence=1;
302
303 drop sequence if exists t1;
304
305 #
306 # DROP SEQUENCE
307 #
308
309 create sequence t1;
310 create sequence t2;
311 create table t3 (a int) engine=myisam;
312 select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test";
313
314 CREATE SEQUENCE s1;
315 drop sequence s1;
316
317 drop sequence if exists t1,t2,t3,t4;
318 drop table if exists t1,t2,t3;
319
320 CREATE TABLE t1 (a int);
321 CREATE TABLE t2 (a int);
322 CREATE SEQUENCE s1;
323 drop table t1,t2,s1;
324
325 CREATE TABLE t1 (a int);
326 CREATE TABLE t2 (a int);
327 CREATE SEQUENCE s1;
328 drop table if exists t1,t2,s1,s2;
329
330 CREATE TABLE t1 (a int);
331 CREATE TABLE t2 (a int);
332 CREATE SEQUENCE s1;
333 --error ER_NOT_SEQUENCE2
334 drop sequence t1,t2,s1,s2;
335 drop table if exists t1,t2;
336
337 CREATE TABLE t1 (a int);
338 CREATE TABLE t2 (a int);
339 CREATE SEQUENCE s1;
340 drop sequence if exists t1,t2,s1,s2;
341 drop table if exists t1,t2;
342
343 CREATE TEMPORARY SEQUENCE s1;
344 DROP SEQUENCE s1;
345 --error ER_UNKNOWN_SEQUENCES
346 DROP TEMPORARY SEQUENCE s1;
347
348 CREATE TEMPORARY SEQUENCE s1;
349 CREATE SEQUENCE s2;
350 CREATE TEMPORARY TABLE t1 (a int);
351 CREATE TABLE t2 (a int);
352 --error ER_UNKNOWN_SEQUENCES
353 DROP TEMPORARY SEQUENCE t1,t2,s1,s2;
354 --error ER_UNKNOWN_SEQUENCES
355 DROP TEMPORARY SEQUENCE s1;
356 DROP TEMPORARY TABLE t1;
357 --error ER_BAD_TABLE_ERROR
358 DROP TABLE t1,t2,s1,s2;
359
360 create view v1 as (select 1);
361 CREATE SEQUENCE s1;
362 --error ER_IT_IS_A_VIEW
363 DROP SEQUENCE s1,v1;
364 drop view v1;
365
366 #
367 # CREATE TEMPORARY SEQUENCE
368 #
369
370 --disable_ps2_protocol
371 CREATE TEMPORARY SEQUENCE t1;
372 select next value for t1;
373 drop temporary table t1;
374 --error ER_UNKNOWN_SEQUENCES
375 select previous value for t1;
376 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10;
377 select next value for t1;
378 select previous value for t1;
379 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10;
380 select previous value for t1;
381 select next value for t1;
382 select previous value for t1;
383 drop temporary sequence t1;
384 select previous value for t1;
385 drop sequence t1;
386
387 CREATE TEMPORARY SEQUENCE t1 engine=innodb;
388 select next value for t1;
389 drop temporary table t1;
390 --error ER_UNKNOWN_SEQUENCES
391 select previous value for t1;
392 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb;
393 select next value for t1;
394 select previous value for t1;
395 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb;
396 START TRANSACTION WITH CONSISTENT SNAPSHOT;
397 select previous value for t1;
398 select next value for t1;
399 select previous value for t1;
400 drop temporary sequence t1;
401 select previous value for t1;
402 drop sequence t1;
403 --enable_ps2_protocol
404
405 #
406 # Check that we can't create anything with the sequence engine
407 #
408
409 --error ER_UNKNOWN_STORAGE_ENGINE
410 create table t1 (a int) engine=sql_sequence;
411
412 --echo #
413 --echo # MDEV-13711 assertion on CREATE LIKE fix
414 --echo #
415
416 create sequence s;
417 create table t like s;
418 show create table t;
419 show create sequence t;
420 drop tables t, s;
421
422 --echo #
423 --echo # MDEV-13714 SEQUENCE option fix
424 --echo #
425
426 create or replace table s (
427 `next_value` bigint(21) not null,
428 `min_value` bigint(21) not null,
429 `max_value` bigint(21) not null,
430 `start` bigint(21) not null,
431 `increment` bigint(21) not null,
432 `cache` bigint(21) not null,
433 `cycle` tinyint(1) unsigned not null,
434 `round` bigint(21) not null)
435 sequence=0;
436
437 create or replace table s2 (
438 `next_value` bigint(21) not null,
439 `min_value` bigint(21) not null,
440 `max_value` bigint(21) not null,
441 `start` bigint(21) not null,
442 `increment` bigint(21) not null,
443 `cache` bigint(21) not null,
444 `cycle` tinyint(1) unsigned not null,
445 `round` bigint(21) not null)
446 sequence=default;
447
448 show create table s;
449 show create table s2;
450 --error ER_NOT_SEQUENCE
451 show create sequence s;
452 --error ER_NOT_SEQUENCE
453 show create sequence s2;
454 drop table s,s2;
455
456 --echo #
457 --echo # MDEV-13721 Assertion is_lock_owner() failed in mysql_rm_table_no_locks
458 --echo #
459
460 create or replace sequence s;
461 create temporary table s (i int);
462 drop sequence s;
463 show create table s;
464 drop table s;
465
466 create or replace sequence s;
467 create temporary sequence s;
468 show create table s;
469 drop sequence s;
470 show create table s;
471 drop table s;
472
473 create or replace sequence s;
474 create temporary sequence s;
475 drop temporary sequence s;
476 show create table s;
477 drop table s;
478
479 create temporary sequence s;
480 drop temporary table s;
481 create temporary table s (i int);
482 --error ER_UNKNOWN_SEQUENCES
483 drop temporary sequence s;
484 drop table s;
485
486 --echo #
487 --echo # MDEV-15115 Assertion failure in CREATE SEQUENCE...ROW_FORMAT=REDUNDANT
488 --echo #
489 CREATE SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
490
491 --replace_column 1 # 5 #
492 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/seq1';
493
494 DROP SEQUENCE seq1;
495 CREATE TEMPORARY SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
496 DROP TEMPORARY SEQUENCE seq1;
497
498 --echo #
499 --echo # MDEV-17503 CREATE SEQUENCE failed with innodb_force_primary_key =1
500 --echo #
501
502 set global innodb_force_primary_key =1;
503 CREATE SEQUENCE s1 START WITH 100 INCREMENT BY 10 ENGINE=innodb;
504 set global innodb_force_primary_key=default;
505 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
506 ALTER TABLE s1 ADD PRIMARY KEY (next_not_cached_value);
507 DROP SEQUENCE s1;
508
509 --echo #
510 --echo # Beginning of 10.4 Test
511 --echo #
512 --echo # MDEV-13005: Fixing bugs in SEQUENCE, part 3
513 --echo #
514
515 --echo # Task 1:
516 --error ER_UNKNOWN_SEQUENCES
517 SET @x = PREVIOUS VALUE FOR x;
518
519 --echo # Task 2:
520 --error ER_SEQUENCE_INVALID_DATA
521 CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678;
522
523 --echo # Task 3:
524 --error ER_SEQUENCE_INVALID_DATA
525 CREATE SEQUENCE seq1 START WITH 1 cache -1;
526
527 --echo # Task 4:
528 CREATE TEMPORARY TABLE s1 (s1 INT);
529 --error ER_UNKNOWN_SEQUENCES
530 DROP TEMPORARY SEQUENCE s1;
531 DROP TEMPORARY TABLE s1;
532
533 --echo # Task 5:
534 CREATE TEMPORARY TABLE s1 (s1 INT);
535 --error ER_PARSE_ERROR
536 CREATE TEMPORARY SEQUENCE s1 (s1 INT);
537 DROP TEMPORARY TABLE s1;
538
539 --echo # Task 6:
540 CREATE SEQUENCE seq1 START WITH 2;
541 --error ER_TRG_ON_VIEW_OR_TEMP_TABLE
542 CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5;
543 DROP SEQUENCE seq1;
544
545 --echo #
546 --echo # MDEV-29771: Server crashes in check_sequence_fields upon
547 --echo # CREATE TABLE .. SEQUENCE=1 AS SELECT ..
548 --echo #
549
550 --error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
551 create table s sequence=1 as select 1;
552
553 --echo #
554 --echo # End of 10.4 test
555 --echo #
556
557 --echo #
558 --echo # Ensure that SHOW CREATE TABLE shows used table options
559 --echo #
560
561 SET @@innodb_compression_default=ON;
562 CREATE TABLE seq (
563 `next_not_cached_value` bigint(21) NOT NULL,
564 `minimum_value` bigint(21) NOT NULL,
565 `maximum_value` bigint(21) NOT NULL,
566 `start_value` bigint(21) NOT NULL,
567 `increment` bigint(21) NOT NULL,
568 `cache_size` bigint(21) unsigned NOT NULL,
569 `cycle_option` tinyint(1) unsigned NOT NULL,
570 `cycle_count` bigint(21) NOT NULL
571 ) engine=innodb,sequence=1;
572 show create sequence seq;
573 show create table seq;
574 drop sequence seq;
575 SET @@innodb_compression_default=DEFAULT;
576
577 --echo ######
578 --echo # MDEV-28152 Features for sequence
579 --echo ######
580
581 --echo # -----
582 --echo # Truncating out-of-bound numbers for minvalue and maxvalue
583 --echo # -----
584 --disable_ps_protocol
585 create or replace sequence t1 minvalue -999999999999999999999;
586 show create sequence t1;
587 create or replace sequence t1 minvalue -9223372036854775808;
588 show create sequence t1;
589 create or replace sequence t1 minvalue -9223372036854775807;
590 show create sequence t1;
591 create or replace sequence t1 minvalue 9223372036854775805;
592 show create sequence t1;
593 --error ER_SEQUENCE_INVALID_DATA
594 create or replace sequence t1 minvalue 9223372036854775806;
595 --error ER_SEQUENCE_INVALID_DATA
596 create or replace sequence t1 minvalue 9223372036854775807;
597 --error ER_SEQUENCE_INVALID_DATA
598 create or replace sequence t1 minvalue 9223372036854775808;
599 --error ER_SEQUENCE_INVALID_DATA
600 create or replace sequence t1 minvalue 9999999999999999999999;
601 --error ER_SEQUENCE_INVALID_DATA
602 create or replace sequence t1 maxvalue -999999999999999999999 increment by -1;
603 --error ER_SEQUENCE_INVALID_DATA
604 create or replace sequence t1 maxvalue -9223372036854775808 increment by -1;
605 --error ER_SEQUENCE_INVALID_DATA
606 create or replace sequence t1 maxvalue -9223372036854775807 increment by -1;
607 create or replace sequence t1 maxvalue -9223372036854775806 increment by -1;
608 show create sequence t1;
609 create or replace sequence t1 maxvalue 9223372036854775806;
610 show create sequence t1;
611 create or replace sequence t1 maxvalue 9223372036854775807;
612 show create sequence t1;
613 create or replace sequence t1 maxvalue 9223372036854775808;
614 show create sequence t1;
615 create or replace sequence t1 maxvalue 9999999999999999999999;
616 show create sequence t1;
617 --enable_ps_protocol
618
619 --echo # -----
620 --echo # Create with value types
621 --echo # -----
622 create or replace sequence t1 as tinyint;
623 show create sequence t1;
624 show create table t1;
625 create or replace sequence t1 as smallint;
626 show create sequence t1;
627 show create table t1;
628 create or replace sequence t1 as mediumint;
629 show create sequence t1;
630 show create table t1;
631 create or replace sequence t1 as int;
632 show create sequence t1;
633 show create table t1;
634 create or replace sequence t1 as bigint;
635 show create sequence t1;
636 show create table t1;
637 create or replace sequence t1 as tinyint unsigned;
638 show create sequence t1;
639 show create table t1;
640 create or replace sequence t1 as smallint unsigned;
641 show create sequence t1;
642 show create table t1;
643 create or replace sequence t1 as mediumint unsigned;
644 show create sequence t1;
645 show create table t1;
646 create or replace sequence t1 as int unsigned;
647 show create sequence t1;
648 show create table t1;
649 create or replace sequence t1 as bigint unsigned;
650 show create sequence t1;
651 show create table t1;
652 create or replace sequence t1 increment -1 as tinyint;
653 show create sequence t1;
654 show create table t1;
655 create or replace sequence t1 increment -1 as smallint;
656 show create sequence t1;
657 show create table t1;
658 create or replace sequence t1 increment -1 as mediumint;
659 show create sequence t1;
660 show create table t1;
661 create or replace sequence t1 increment -1 as int;
662 show create sequence t1;
663 show create table t1;
664 create or replace sequence t1 increment -1 as bigint;
665 show create sequence t1;
666 show create table t1;
667 create or replace sequence t1 increment -1 as tinyint unsigned;
668 show create sequence t1;
669 show create table t1;
670 create or replace sequence t1 increment -1 as smallint unsigned;
671 show create sequence t1;
672 show create table t1;
673 create or replace sequence t1 increment -1 as mediumint unsigned;
674 show create sequence t1;
675 show create table t1;
676 create or replace sequence t1 increment -1 as int unsigned;
677 show create sequence t1;
678 show create table t1;
679 create or replace sequence t1 increment -1 as bigint unsigned;
680 show create sequence t1;
681 show create table t1;
682
683 #zerofill is not supported
684 --error ER_BAD_OPTION_VALUE
685 create or replace sequence t1 as tinyint zerofill;
686
687 #start with a number between longlong_max and ulonglong_max
688 create or replace sequence t1 as bigint unsigned start with 12345678901234567890;
689 show create sequence t1;
690 show create table t1;
691
692 # hidden constraints cache < (LONGLONG_MAX - max_increment) / max_increment
693 --error ER_SEQUENCE_INVALID_DATA
694 create or replace sequence t1 as bigint unsigned increment 12345678901234567;
695
696 --echo # -----
697 --echo # value types + truncating
698 --echo # -----
699 --disable_ps_protocol
700 create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned;
701 show create sequence t1;
702 show create table t1;
703 create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint;
704 show create sequence t1;
705 show create table t1;
706 create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned;
707 show create sequence t1;
708 show create table t1;
709 create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234;
710 show create sequence t1;
711 show create table t1;
712 create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234;
713 show create sequence t1;
714 show create table t1;
715 --enable_ps_protocol
716
717 --echo # -----
718 --echo # indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser.
719 --echo # -----
720 #signed, -1: no truncation. Note that we need a negative increment because this is a nagative sequence
721 create or replace sequence t1 as bigint maxvalue -1 increment by -1;
722 show create sequence t1;
723 show create table t1;
724 --disable_ps_protocol
725 #signed, ulonglong_max: turncating to longlong_max-1
726 create or replace sequence t1 as bigint maxvalue 18446744073709551615;
727 show create sequence t1;
728 show create table t1;
729 #unsigned, -1: truncation and invalid data (max_value truncated to 1 which is equal to min_value)
730 --error ER_SEQUENCE_INVALID_DATA
731 create or replace sequence t1 as bigint unsigned maxvalue -1;
732 #unsigned, ulonglong_max: truncating to ulonglong_max-1
733 create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615;
734 show create sequence t1;
735 show create table t1;
736 --enable_ps_protocol
737
738 --echo # -----
739 --echo # value types + out of range start
740 --echo # -----
741 --error ER_PARSE_ERROR
742 create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned;
743 --error ER_SEQUENCE_INVALID_DATA
744 create or replace sequence t1 start with -1 as tinyint unsigned;
745 --error ER_SEQUENCE_INVALID_DATA
746 create or replace sequence t1 start with 0 as tinyint unsigned;
747 show create sequence t1;
748 create or replace sequence t1 start with 1 as tinyint unsigned;
749 show create sequence t1;
750 create or replace sequence t1 start with 254 as tinyint unsigned;
751 --error ER_SEQUENCE_INVALID_DATA
752 create or replace sequence t1 start with 255 as tinyint unsigned;
753 --error ER_SEQUENCE_INVALID_DATA
754 create or replace sequence t1 start with 256 as tinyint unsigned;
755 --error ER_PARSE_ERROR
756 create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned;
757
758 drop sequence t1;
759
760 --echo # -----
761 --echo # information_schema.sequences
762 --echo # -----
763 create sequence s1 as tinyint unsigned increment by 23;
764 create sequence s2 start with 42 minvalue -9223372036854775807;
765 create sequence s3 as bigint unsigned start with 12345678901234567890 cycle;
766 #test support of all types in information schema
767 create sequence s4 as tinyint;
768 create sequence s5 as smallint;
769 create sequence s6 as mediumint;
770 create sequence s7 as int;
771 create sequence s8 as bigint;
772 create sequence s9 as tinyint unsigned;
773 create sequence s10 as smallint unsigned;
774 create sequence s11 as mediumint unsigned;
775 create sequence s12 as int unsigned;
776 create sequence s13 as bigint unsigned;
777 select * from information_schema.sequences order by sequence_name;
778 drop sequence s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13;
779
780 --echo # End of 11.5 tests