Debian Codegraph @

mysql-test/suite/sql_sequence/create.result

raw
1 drop table if exists t1;
2 Warnings:
3 Note 1051 Unknown table 'test.t1'
4 create or replace sequence t1 engine=myisam;
5 show create sequence t1;
6 Table Create Table
7 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
8 show create table t1;
9 Table Create Table
10 t1 CREATE TABLE `t1` (
11 `next_not_cached_value` bigint(21) NOT NULL,
12 `minimum_value` bigint(21) NOT NULL,
13 `maximum_value` bigint(21) NOT NULL,
14 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
15 `increment` bigint(21) NOT NULL COMMENT 'increment value',
16 `cache_size` bigint(21) unsigned NOT NULL,
17 `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',
18 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
19 ) ENGINE=MyISAM SEQUENCE=1
20 select * from t1;
21 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
22 1 1 9223372036854775806 1 1 1000 0 0
23 create or replace sequence t1 engine=innodb;
24 show create sequence t1;
25 Table Create Table
26 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
27 show create table t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `next_not_cached_value` bigint(21) NOT NULL,
31 `minimum_value` bigint(21) NOT NULL,
32 `maximum_value` bigint(21) NOT NULL,
33 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
34 `increment` bigint(21) NOT NULL COMMENT 'increment value',
35 `cache_size` bigint(21) unsigned NOT NULL,
36 `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',
37 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
38 ) ENGINE=InnoDB SEQUENCE=1
39 select * from t1;
40 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
41 1 1 9223372036854775806 1 1 1000 0 0
42 create or replace sequence t1 engine=maria;
43 show create sequence t1;
44 Table Create Table
45 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria
46 show create table t1;
47 Table Create Table
48 t1 CREATE TABLE `t1` (
49 `next_not_cached_value` bigint(21) NOT NULL,
50 `minimum_value` bigint(21) NOT NULL,
51 `maximum_value` bigint(21) NOT NULL,
52 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
53 `increment` bigint(21) NOT NULL COMMENT 'increment value',
54 `cache_size` bigint(21) unsigned NOT NULL,
55 `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',
56 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
57 ) ENGINE=Aria SEQUENCE=1
58 select * from t1;
59 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
60 1 1 9223372036854775806 1 1 1000 0 0
61 create or replace sequence t1 start with 10;
62 show create sequence t1;
63 Table Create Table
64 t1 CREATE SEQUENCE `t1` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
65 select * from t1;
66 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
67 10 1 9223372036854775806 10 1 1000 0 0
68 create or replace sequence t1 minvalue=11;
69 show create sequence t1;
70 Table Create Table
71 t1 CREATE SEQUENCE `t1` start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
72 select * from t1;
73 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
74 11 11 9223372036854775806 11 1 1000 0 0
75 create or replace sequence t1 maxvalue=13 increment by -1;
76 show create sequence t1;
77 Table Create Table
78 t1 CREATE SEQUENCE `t1` start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM
79 select * from t1;
80 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
81 13 -9223372036854775807 13 13 -1 1000 0 0
82 create or replace sequence t1 increment by -1 cache 100;
83 show create sequence t1;
84 Table Create Table
85 t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM
86 select * from t1;
87 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
88 -1 -9223372036854775807 -1 -1 -1 100 0 0
89 create or replace sequence t1 cycle;
90 show create sequence t1;
91 Table Create Table
92 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
93 select * from t1;
94 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
95 1 1 9223372036854775806 1 1 1000 1 0
96 create or replace sequence t1 nocycle;
97 show create sequence t1;
98 Table Create Table
99 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
100 select * from t1;
101 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
102 1 1 9223372036854775806 1 1 1000 0 0
103 show create sequence t1;
104 Table Create Table
105 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
106 create or replace sequence t1 cycle minvalue= 14;
107 show create sequence t1;
108 Table Create Table
109 t1 CREATE SEQUENCE `t1` start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
110 select * from t1;
111 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
112 14 14 9223372036854775806 14 1 1000 1 0
113 create or replace sequence t1 cycle increment by -1;
114 show create sequence t1;
115 Table Create Table
116 t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM
117 drop sequence t1;
118 create sequence if not exists t1;
119 create sequence if not exists t1 start with 10;
120 Warnings:
121 Note 1050 Table 't1' already exists
122 select * from t1;
123 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
124 1 1 9223372036854775806 1 1 1000 0 0
125 show create sequence t1;
126 Table Create Table
127 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
128 create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle;
129 show create sequence t1;
130 Table Create Table
131 t1 CREATE SEQUENCE `t1` start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM
132 select * from t1;
133 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
134 10 10 11 10 1 0 1 0
135 create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10;
136 show create sequence t1;
137 Table Create Table
138 t1 CREATE SEQUENCE `t1` start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM
139 select * from t1;
140 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
141 10 -10 11 10 10 10 1 0
142 create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE;
143 create or replace sequence t1 start with 10 maxvalue 10;
144 create or replace sequence t1 start with 10 minvalue 10;
145 create or replace sequence t1 start with 10 minvalue 10 maxvalue 11 cycle;
146 create or replace sequence t1 start with 10 maxvalue=9223372036854775806;
147 create or replace sequence t1 start with 10 minvalue=-9223372036854775807;
148 drop sequence if exists t1;
149 create sequence t1 increment by 0;
150 show create sequence t1;
151 Table Create Table
152 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM
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 0 1000 0 0
156 drop sequence t1;
157 create table t1 (a int);
158 show create sequence t1;
159 ERROR 42S02: 'test.t1' is not a SEQUENCE
160 drop sequence t1;
161 ERROR 42S02: 'test.t1' is not a SEQUENCE
162 drop sequence if exists t1;
163 Warnings:
164 Note 4090 'test.t1' is not a SEQUENCE
165 create sequence t1 start with 10 maxvalue=9;
166 ERROR HY000: Sequence 'test.t1' has out of range value for options
167 create sequence t1 minvalue= 100 maxvalue=10;
168 ERROR HY000: Sequence 'test.t1' has out of range value for options
169 create sequence t1 start with 9 minvalue=10;
170 ERROR HY000: Sequence 'test.t1' has out of range value for options
171 create or replace sequence t1 maxvalue=13, increment by -1;
172 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 ' increment by -1' at line 1
173 create or replace sequence t1 start with= 10 maxvalue=13;
174 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 '= 10 maxvalue=13' at line 1
175 create or replace sequence t1 maxvalue=13, increment= -1;
176 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 ' increment= -1' at line 1
177 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
178 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 'NO MINVALUE' at line 1
179 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
180 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 'NO MINVALUE' at line 1
181 create sequence t1 RESTART WITH 10;
182 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 'RESTART' at line 1
183 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
184 ERROR HY000: Option 'MINVALUE' used twice in statement
185 create sequence s increment by 3234567890123456789;
186 ERROR HY000: Sequence 'test.s' has out of range value for options
187 create or replace sequence t1;
188 show fields from t1;
189 Field Type Null Key Default Extra
190 next_not_cached_value bigint(21) NO NULL
191 minimum_value bigint(21) NO NULL
192 maximum_value bigint(21) NO NULL
193 start_value bigint(21) NO NULL
194 increment bigint(21) NO NULL
195 cache_size bigint(21) unsigned NO NULL
196 cycle_option tinyint(1) unsigned NO NULL
197 cycle_count bigint(21) NO NULL
198 flush tables;
199 show fields from t1;
200 Field Type Null Key Default Extra
201 next_not_cached_value bigint(21) NO NULL
202 minimum_value bigint(21) NO NULL
203 maximum_value bigint(21) NO NULL
204 start_value bigint(21) NO NULL
205 increment bigint(21) NO NULL
206 cache_size bigint(21) unsigned NO NULL
207 cycle_option tinyint(1) unsigned NO NULL
208 cycle_count bigint(21) NO NULL
209 create or replace sequence t1 engine=aria;
210 show fields from t1;
211 Field Type Null Key Default Extra
212 next_not_cached_value bigint(21) NO NULL
213 minimum_value bigint(21) NO NULL
214 maximum_value bigint(21) NO NULL
215 start_value bigint(21) NO NULL
216 increment bigint(21) NO NULL
217 cache_size bigint(21) unsigned NO NULL
218 cycle_option tinyint(1) unsigned NO NULL
219 cycle_count bigint(21) NO NULL
220 show fields from t1;
221 Field Type Null Key Default Extra
222 next_not_cached_value bigint(21) NO NULL
223 minimum_value bigint(21) NO NULL
224 maximum_value bigint(21) NO NULL
225 start_value bigint(21) NO NULL
226 increment bigint(21) NO NULL
227 cache_size bigint(21) unsigned NO NULL
228 cycle_option tinyint(1) unsigned NO NULL
229 cycle_count bigint(21) NO NULL
230 flush tables;
231 create or replace sequence t1 comment= "test 1";
232 show create sequence t1;
233 Table Create Table
234 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1'
235 show create table t1;
236 Table Create Table
237 t1 CREATE TABLE `t1` (
238 `next_not_cached_value` bigint(21) NOT NULL,
239 `minimum_value` bigint(21) NOT NULL,
240 `maximum_value` bigint(21) NOT NULL,
241 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
242 `increment` bigint(21) NOT NULL COMMENT 'increment value',
243 `cache_size` bigint(21) unsigned NOT NULL,
244 `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',
245 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
246 ) ENGINE=MyISAM SEQUENCE=1 COMMENT='test 1'
247 create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2;
248 show create sequence t1;
249 Table Create Table
250 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2'
251 show create table t1;
252 Table Create Table
253 t1 CREATE TABLE `t1` (
254 `next_not_cached_value` bigint(21) NOT NULL,
255 `minimum_value` bigint(21) NOT NULL,
256 `maximum_value` bigint(21) NOT NULL,
257 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
258 `increment` bigint(21) NOT NULL COMMENT 'increment value',
259 `cache_size` bigint(21) unsigned NOT NULL,
260 `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',
261 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
262 ) ENGINE=MyISAM MIN_ROWS=1 MAX_ROWS=2 SEQUENCE=1 COMMENT='test 2'
263 create or replace sequence t1 start=1 increment= 2;
264 create or replace sequence t1 start 1 increment 2;
265 create or replace sequence t1 cache +1;
266 drop sequence t1;
267 CREATE TABLE t1 (
268 `next_not_cached_value` bigint(21) NOT NULL,
269 `minimum_value` bigint(21) NOT NULL,
270 `maximum_value` bigint(21) NOT NULL,
271 `start_value` bigint(21) NOT NULL,
272 `increment` bigint(21) NOT NULL,
273 `cache_size` bigint(21) unsigned NOT NULL,
274 `cycle_option` tinyint(1) unsigned NOT NULL,
275 `cycle_count` bigint(21) NOT NULL
276 ) sequence=1;
277 show create sequence t1;
278 Table Create Table
279 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
280 show create table t1;
281 Table Create Table
282 t1 CREATE TABLE `t1` (
283 `next_not_cached_value` bigint(21) NOT NULL,
284 `minimum_value` bigint(21) NOT NULL,
285 `maximum_value` bigint(21) NOT NULL,
286 `start_value` bigint(21) NOT NULL,
287 `increment` bigint(21) NOT NULL,
288 `cache_size` bigint(21) unsigned NOT NULL,
289 `cycle_option` tinyint(1) unsigned NOT NULL,
290 `cycle_count` bigint(21) NOT NULL
291 ) ENGINE=MyISAM SEQUENCE=1
292 drop sequence t1;
293 CREATE OR REPLACE TABLE t1 (
294 `next_not_cached_value` bigint(21) NOT NULL,
295 `minimum_value` bigint(21) NOT NULL,
296 `maximum_value` bigint(21) NOT NULL,
297 `start_value` bigint(21) NOT NULL,
298 `increment` bigint(21) NOT NULL,
299 `cache_size` bigint(21) unsigned NOT NULL,
300 `cycle_option` tinyint(1) unsigned NOT NULL,
301 `cycle_count_not_exists` bigint(21) NOT NULL
302 ) sequence=1;
303 ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle_count_not_exists)
304 CREATE OR REPLACE TABLE t1 (
305 `next_not_cached_value` int(21) NOT NULL,
306 `minimum_value` bigint(21) NOT NULL,
307 `maximum_value` bigint(21) NOT NULL,
308 `start_value` bigint(21) NOT NULL,
309 `increment` bigint(21) NOT NULL,
310 `cache_size` bigint(21) unsigned NOT NULL,
311 `cycle_option` tinyint(1) unsigned NOT NULL,
312 `cycle_count` bigint(21) NOT NULL
313 ) sequence=1;
314 ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value)
315 CREATE OR REPLACE TABLE t1 (
316 `next_not_cached_value` bigint(21) NOT NULL,
317 `minimum_value` bigint(21) NOT NULL,
318 `maximum_value` bigint(21) NOT NULL,
319 `start_value` bigint(21) NOT NULL,
320 `increment` bigint(21) NOT NULL,
321 `cache_size` bigint(21) unsigned NOT NULL,
322 `cycle` bigint(21) unsigned NOT NULL, /* error */
323 `cycle_count` bigint(21) NOT NULL
324 ) sequence=1;
325 ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle)
326 CREATE OR REPLACE TABLE t1 (
327 `next_not_cached_value` bigint(21), /* error */
328 `minimum_value` bigint(21) NOT NULL,
329 `maximum_value` bigint(21) NOT NULL,
330 `start_value` bigint(21) NOT NULL,
331 `increment` bigint(21) NOT NULL,
332 `cache_size` bigint(21) unsigned NOT NULL,
333 `cycle_option` tinyint(1) unsigned NOT NULL,
334 `cycle_count` bigint(21) NOT NULL
335 ) sequence=1;
336 ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value)
337 CREATE OR REPLACE TABLE t1 (
338 `next_not_cached_value` bigint(21) NOT NULL,
339 `minimum_value` bigint(21) NOT NULL,
340 `maximum_value` bigint(21) NOT NULL,
341 `start_value` bigint(21) NOT NULL,
342 `increment` bigint(21) NOT NULL,
343 `cache_size` bigint(21) unsigned NOT NULL,
344 `cycle_option` tinyint(1) unsigned NOT NULL,
345 `cycle_count` bigint(21) NOT NULL,
346 extra_field bigint(21)
347 ) sequence=1;
348 ERROR HY000: Sequence 'test.t1' table structure is invalid (Wrong number of columns)
349 CREATE OR REPLACE TABLE t1 (
350 `minimum_value` bigint(21) NOT NULL,
351 `next_not_cached_value` bigint(21) NOT NULL,
352 `maximum_value` bigint(21) NOT NULL,
353 `start_value` bigint(21) NOT NULL,
354 `increment` bigint(21) NOT NULL,
355 `cache_size` bigint(21) unsigned NOT NULL,
356 `cycle_option` tinyint(1) unsigned NOT NULL,
357 `cycle_count` bigint(21) NOT NULL
358 ) sequence=1;
359 ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value)
360 CREATE OR REPLACE TABLE t1 (
361 `next_not_cached_value` bigint(21) NOT NULL,
362 `minimum_value` bigint(21) NOT NULL,
363 `maximum_value` bigint(21) NOT NULL,
364 `start_value` bigint(21) NOT NULL,
365 `increment` bigint(21) NOT NULL,
366 `cache_size` bigint(21) unsigned NOT NULL,
367 `cycle_option` tinyint(1) unsigned NOT NULL,
368 `cycle_count` bigint(21) NOT NULL,
369 key key1 (next_not_cached_value)
370 ) sequence=1;
371 ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys)
372 CREATE TABLE t1 (
373 `next_not_cached_value` bigint(21) NOT NULL,
374 `minimum_value` bigint(21) NOT NULL,
375 `maximum_value` bigint(21) NOT NULL,
376 `start_value` bigint(21) NOT NULL,
377 `increment` bigint(21) NOT NULL,
378 `cache_size` bigint(21) unsigned NOT NULL,
379 `cycle_option` tinyint(1) unsigned NOT NULL,
380 `cycle_count` bigint(21) NOT NULL,
381 CHECK (start_value < minimum_value)
382 ) sequence=1;
383 ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints)
384 CREATE TABLE t1 (
385 `next_not_cached_value` bigint(21) NOT NULL,
386 `minimum_value` bigint(21) NOT NULL,
387 `maximum_value` bigint(21) NOT NULL,
388 `start_value` bigint(21) NOT NULL CHECK (start_value < minimum_value),
389 `increment` bigint(21) NOT NULL,
390 `cache_size` bigint(21) unsigned NOT NULL,
391 `cycle_option` tinyint(1) unsigned NOT NULL,
392 `cycle_count` bigint(21) NOT NULL
393 ) sequence=1;
394 ERROR HY000: Sequence 'test.t1' table structure is invalid (start_value)
395 CREATE TABLE t1 (
396 `next_not_cached_value` bigint(21) NOT NULL,
397 `minimum_value` bigint(21) NOT NULL,
398 `maximum_value` bigint(21) NOT NULL,
399 `start_value` bigint(21) NOT NULL,
400 `increment` bigint(21) NOT NULL,
401 `cache_size` bigint(21) unsigned NOT NULL,
402 `cycle_option` tinyint(1) unsigned NOT NULL,
403 `cycle_count` bigint(21) generated always as (1) virtual
404 ) sequence=1;
405 ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle_count)
406 drop sequence if exists t1;
407 Warnings:
408 Note 4091 Unknown SEQUENCE: 'test.t1'
409 create sequence t1;
410 create sequence t2;
411 create table t3 (a int) engine=myisam;
412 select table_catalog, table_schema, table_name, table_type from information_schema.tables where table_catalog="test";
413 table_catalog table_schema table_name table_type
414 CREATE SEQUENCE s1;
415 drop sequence s1;
416 drop sequence if exists t1,t2,t3,t4;
417 Warnings:
418 Note 4090 'test.t3' is not a SEQUENCE
419 Note 4091 Unknown SEQUENCE: 'test.t4'
420 drop table if exists t1,t2,t3;
421 Warnings:
422 Note 1051 Unknown table 'test.t1,test.t2'
423 CREATE TABLE t1 (a int);
424 CREATE TABLE t2 (a int);
425 CREATE SEQUENCE s1;
426 drop table t1,t2,s1;
427 CREATE TABLE t1 (a int);
428 CREATE TABLE t2 (a int);
429 CREATE SEQUENCE s1;
430 drop table if exists t1,t2,s1,s2;
431 Warnings:
432 Note 1051 Unknown table 'test.s2'
433 CREATE TABLE t1 (a int);
434 CREATE TABLE t2 (a int);
435 CREATE SEQUENCE s1;
436 drop sequence t1,t2,s1,s2;
437 ERROR 42S02: 'test.t1' is not a SEQUENCE
438 drop table if exists t1,t2;
439 CREATE TABLE t1 (a int);
440 CREATE TABLE t2 (a int);
441 CREATE SEQUENCE s1;
442 drop sequence if exists t1,t2,s1,s2;
443 Warnings:
444 Note 4090 'test.t1' is not a SEQUENCE
445 Note 4090 'test.t2' is not a SEQUENCE
446 Note 4091 Unknown SEQUENCE: 'test.s2'
447 drop table if exists t1,t2;
448 CREATE TEMPORARY SEQUENCE s1;
449 DROP SEQUENCE s1;
450 DROP TEMPORARY SEQUENCE s1;
451 ERROR 42S02: Unknown SEQUENCE: 'test.s1'
452 CREATE TEMPORARY SEQUENCE s1;
453 CREATE SEQUENCE s2;
454 CREATE TEMPORARY TABLE t1 (a int);
455 CREATE TABLE t2 (a int);
456 DROP TEMPORARY SEQUENCE t1,t2,s1,s2;
457 ERROR 42S02: Unknown SEQUENCE: 'test.t1,test.t2,test.s2'
458 DROP TEMPORARY SEQUENCE s1;
459 ERROR 42S02: Unknown SEQUENCE: 'test.s1'
460 DROP TEMPORARY TABLE t1;
461 DROP TABLE t1,t2,s1,s2;
462 ERROR 42S02: Unknown table 'test.t1,test.s1'
463 create view v1 as (select 1);
464 CREATE SEQUENCE s1;
465 DROP SEQUENCE s1,v1;
466 ERROR 42S02: 'test.v1' is a view
467 drop view v1;
468 CREATE TEMPORARY SEQUENCE t1;
469 select next value for t1;
470 next value for t1
471 1
472 drop temporary table t1;
473 select previous value for t1;
474 ERROR 42S02: Unknown SEQUENCE: 't1'
475 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10;
476 select next value for t1;
477 next value for t1
478 1
479 select previous value for t1;
480 previous value for t1
481 1
482 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10;
483 select previous value for t1;
484 previous value for t1
485 NULL
486 select next value for t1;
487 next value for t1
488 100
489 select previous value for t1;
490 previous value for t1
491 100
492 drop temporary sequence t1;
493 select previous value for t1;
494 previous value for t1
495 1
496 drop sequence t1;
497 CREATE TEMPORARY SEQUENCE t1 engine=innodb;
498 select next value for t1;
499 next value for t1
500 1
501 drop temporary table t1;
502 select previous value for t1;
503 ERROR 42S02: Unknown SEQUENCE: 't1'
504 CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 10 engine=innodb;
505 select next value for t1;
506 next value for t1
507 1
508 select previous value for t1;
509 previous value for t1
510 1
511 CREATE TEMPORARY SEQUENCE t1 start with 100 minvalue 100 maxvalue 200 increment by 1 cache 10 engine=innodb;
512 START TRANSACTION WITH CONSISTENT SNAPSHOT;
513 select previous value for t1;
514 previous value for t1
515 NULL
516 select next value for t1;
517 next value for t1
518 100
519 select previous value for t1;
520 previous value for t1
521 100
522 drop temporary sequence t1;
523 select previous value for t1;
524 previous value for t1
525 1
526 drop sequence t1;
527 create table t1 (a int) engine=sql_sequence;
528 ERROR 42000: Unknown storage engine 'sql_sequence'
529 #
530 # MDEV-13711 assertion on CREATE LIKE fix
531 #
532 create sequence s;
533 create table t like s;
534 show create table t;
535 Table Create Table
536 t CREATE TABLE `t` (
537 `next_not_cached_value` bigint(21) NOT NULL,
538 `minimum_value` bigint(21) NOT NULL,
539 `maximum_value` bigint(21) NOT NULL,
540 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
541 `increment` bigint(21) NOT NULL COMMENT 'increment value',
542 `cache_size` bigint(21) unsigned NOT NULL,
543 `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',
544 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
545 ) ENGINE=MyISAM SEQUENCE=1
546 show create sequence t;
547 Table Create Table
548 t CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
549 drop tables t, s;
550 #
551 # MDEV-13714 SEQUENCE option fix
552 #
553 create or replace table s (
554 `next_value` bigint(21) not null,
555 `min_value` bigint(21) not null,
556 `max_value` bigint(21) not null,
557 `start` bigint(21) not null,
558 `increment` bigint(21) not null,
559 `cache` bigint(21) not null,
560 `cycle` tinyint(1) unsigned not null,
561 `round` bigint(21) not null)
562 sequence=0;
563 create or replace table s2 (
564 `next_value` bigint(21) not null,
565 `min_value` bigint(21) not null,
566 `max_value` bigint(21) not null,
567 `start` bigint(21) not null,
568 `increment` bigint(21) not null,
569 `cache` bigint(21) not null,
570 `cycle` tinyint(1) unsigned not null,
571 `round` bigint(21) not null)
572 sequence=default;
573 show create table s;
574 Table Create Table
575 s CREATE TABLE `s` (
576 `next_value` bigint(21) NOT NULL,
577 `min_value` bigint(21) NOT NULL,
578 `max_value` bigint(21) NOT NULL,
579 `start` bigint(21) NOT NULL,
580 `increment` bigint(21) NOT NULL,
581 `cache` bigint(21) NOT NULL,
582 `cycle` tinyint(1) unsigned NOT NULL,
583 `round` bigint(21) NOT NULL
584 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
585 show create table s2;
586 Table Create Table
587 s2 CREATE TABLE `s2` (
588 `next_value` bigint(21) NOT NULL,
589 `min_value` bigint(21) NOT NULL,
590 `max_value` bigint(21) NOT NULL,
591 `start` bigint(21) NOT NULL,
592 `increment` bigint(21) NOT NULL,
593 `cache` bigint(21) NOT NULL,
594 `cycle` tinyint(1) unsigned NOT NULL,
595 `round` bigint(21) NOT NULL
596 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
597 show create sequence s;
598 ERROR 42S02: 'test.s' is not a SEQUENCE
599 show create sequence s2;
600 ERROR 42S02: 'test.s2' is not a SEQUENCE
601 drop table s,s2;
602 #
603 # MDEV-13721 Assertion is_lock_owner() failed in mysql_rm_table_no_locks
604 #
605 create or replace sequence s;
606 create temporary table s (i int);
607 drop sequence s;
608 show create table s;
609 Table Create Table
610 s CREATE TEMPORARY TABLE `s` (
611 `i` int(11) DEFAULT NULL
612 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
613 drop table s;
614 create or replace sequence s;
615 create temporary sequence s;
616 show create table s;
617 Table Create Table
618 s CREATE TEMPORARY TABLE `s` (
619 `next_not_cached_value` bigint(21) NOT NULL,
620 `minimum_value` bigint(21) NOT NULL,
621 `maximum_value` bigint(21) NOT NULL,
622 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
623 `increment` bigint(21) NOT NULL COMMENT 'increment value',
624 `cache_size` bigint(21) unsigned NOT NULL,
625 `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',
626 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
627 ) ENGINE=MyISAM SEQUENCE=1
628 drop sequence s;
629 show create table s;
630 Table Create Table
631 s CREATE TABLE `s` (
632 `next_not_cached_value` bigint(21) NOT NULL,
633 `minimum_value` bigint(21) NOT NULL,
634 `maximum_value` bigint(21) NOT NULL,
635 `start_value` bigint(21) 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 drop table s;
642 create or replace sequence s;
643 create temporary sequence s;
644 drop temporary sequence s;
645 show create table s;
646 Table Create Table
647 s CREATE TABLE `s` (
648 `next_not_cached_value` bigint(21) NOT NULL,
649 `minimum_value` bigint(21) NOT NULL,
650 `maximum_value` bigint(21) NOT NULL,
651 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
652 `increment` bigint(21) NOT NULL COMMENT 'increment value',
653 `cache_size` bigint(21) unsigned NOT NULL,
654 `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',
655 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
656 ) ENGINE=MyISAM SEQUENCE=1
657 drop table s;
658 create temporary sequence s;
659 drop temporary table s;
660 create temporary table s (i int);
661 drop temporary sequence s;
662 ERROR 42S02: Unknown SEQUENCE: 'test.s'
663 drop table s;
664 #
665 # MDEV-15115 Assertion failure in CREATE SEQUENCE...ROW_FORMAT=REDUNDANT
666 #
667 CREATE SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
668 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/seq1';
669 TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
670 # test/seq1 12288 11 # Redundant 0 Single
671 DROP SEQUENCE seq1;
672 CREATE TEMPORARY SEQUENCE seq1 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
673 DROP TEMPORARY SEQUENCE seq1;
674 #
675 # MDEV-17503 CREATE SEQUENCE failed with innodb_force_primary_key =1
676 #
677 set global innodb_force_primary_key =1;
678 CREATE SEQUENCE s1 START WITH 100 INCREMENT BY 10 ENGINE=innodb;
679 set global innodb_force_primary_key=default;
680 ALTER TABLE s1 ADD PRIMARY KEY (next_not_cached_value);
681 ERROR HY000: Sequence 'test.s1' table structure is invalid (Sequence tables cannot have any keys)
682 DROP SEQUENCE s1;
683 #
684 # Beginning of 10.4 Test
685 #
686 # MDEV-13005: Fixing bugs in SEQUENCE, part 3
687 #
688 # Task 1:
689 SET @x = PREVIOUS VALUE FOR x;
690 ERROR 42S02: Unknown SEQUENCE: 'x'
691 # Task 2:
692 CREATE SEQUENCE x START WITH 1 INCREMENT BY 123456789012345678;
693 ERROR HY000: Sequence 'test.x' has out of range value for options
694 # Task 3:
695 CREATE SEQUENCE seq1 START WITH 1 cache -1;
696 ERROR HY000: Sequence 'test.seq1' has out of range value for options
697 # Task 4:
698 CREATE TEMPORARY TABLE s1 (s1 INT);
699 DROP TEMPORARY SEQUENCE s1;
700 ERROR 42S02: Unknown SEQUENCE: 'test.s1'
701 DROP TEMPORARY TABLE s1;
702 # Task 5:
703 CREATE TEMPORARY TABLE s1 (s1 INT);
704 CREATE TEMPORARY SEQUENCE s1 (s1 INT);
705 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 '(s1 INT)' at line 1
706 DROP TEMPORARY TABLE s1;
707 # Task 6:
708 CREATE SEQUENCE seq1 START WITH 2;
709 CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5;
710 ERROR HY000: Trigger's 'seq1' is a view, temporary table or sequence
711 DROP SEQUENCE seq1;
712 #
713 # MDEV-29771: Server crashes in check_sequence_fields upon
714 # CREATE TABLE .. SEQUENCE=1 AS SELECT ..
715 #
716 create table s sequence=1 as select 1;
717 ERROR HY000: Sequence 'test.s' table structure is invalid (Wrong number of columns)
718 #
719 # End of 10.4 test
720 #
721 #
722 # Ensure that SHOW CREATE TABLE shows used table options
723 #
724 SET @@innodb_compression_default=ON;
725 CREATE TABLE seq (
726 `next_not_cached_value` bigint(21) NOT NULL,
727 `minimum_value` bigint(21) NOT NULL,
728 `maximum_value` bigint(21) NOT NULL,
729 `start_value` bigint(21) NOT NULL,
730 `increment` bigint(21) NOT NULL,
731 `cache_size` bigint(21) unsigned NOT NULL,
732 `cycle_option` tinyint(1) unsigned NOT NULL,
733 `cycle_count` bigint(21) NOT NULL
734 ) engine=innodb,sequence=1;
735 show create sequence seq;
736 Table Create Table
737 seq CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB `PAGE_COMPRESSED`='ON'
738 show create table seq;
739 Table Create Table
740 seq CREATE TABLE `seq` (
741 `next_not_cached_value` bigint(21) NOT NULL,
742 `minimum_value` bigint(21) NOT NULL,
743 `maximum_value` bigint(21) NOT NULL,
744 `start_value` bigint(21) NOT NULL,
745 `increment` bigint(21) NOT NULL,
746 `cache_size` bigint(21) unsigned NOT NULL,
747 `cycle_option` tinyint(1) unsigned NOT NULL,
748 `cycle_count` bigint(21) NOT NULL
749 ) ENGINE=InnoDB SEQUENCE=1 `PAGE_COMPRESSED`='ON'
750 drop sequence seq;
751 SET @@innodb_compression_default=DEFAULT;
752 ######
753 # MDEV-28152 Features for sequence
754 ######
755 # -----
756 # Truncating out-of-bound numbers for minvalue and maxvalue
757 # -----
758 create or replace sequence t1 minvalue -999999999999999999999;
759 Warnings:
760 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
761 show create sequence t1;
762 Table Create Table
763 t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
764 create or replace sequence t1 minvalue -9223372036854775808;
765 Warnings:
766 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
767 show create sequence t1;
768 Table Create Table
769 t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
770 create or replace sequence t1 minvalue -9223372036854775807;
771 show create sequence t1;
772 Table Create Table
773 t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
774 create or replace sequence t1 minvalue 9223372036854775805;
775 show create sequence t1;
776 Table Create Table
777 t1 CREATE SEQUENCE `t1` start with 9223372036854775805 minvalue 9223372036854775805 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
778 create or replace sequence t1 minvalue 9223372036854775806;
779 ERROR HY000: Sequence 'test.t1' has out of range value for options
780 create or replace sequence t1 minvalue 9223372036854775807;
781 ERROR HY000: Sequence 'test.t1' has out of range value for options
782 create or replace sequence t1 minvalue 9223372036854775808;
783 ERROR HY000: Sequence 'test.t1' has out of range value for options
784 create or replace sequence t1 minvalue 9999999999999999999999;
785 ERROR HY000: Sequence 'test.t1' has out of range value for options
786 create or replace sequence t1 maxvalue -999999999999999999999 increment by -1;
787 ERROR HY000: Sequence 'test.t1' has out of range value for options
788 create or replace sequence t1 maxvalue -9223372036854775808 increment by -1;
789 ERROR HY000: Sequence 'test.t1' has out of range value for options
790 create or replace sequence t1 maxvalue -9223372036854775807 increment by -1;
791 ERROR HY000: Sequence 'test.t1' has out of range value for options
792 create or replace sequence t1 maxvalue -9223372036854775806 increment by -1;
793 show create sequence t1;
794 Table Create Table
795 t1 CREATE SEQUENCE `t1` start with -9223372036854775806 minvalue -9223372036854775807 maxvalue -9223372036854775806 increment by -1 cache 1000 nocycle ENGINE=MyISAM
796 create or replace sequence t1 maxvalue 9223372036854775806;
797 show create sequence t1;
798 Table Create Table
799 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
800 create or replace sequence t1 maxvalue 9223372036854775807;
801 Warnings:
802 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
803 show create sequence t1;
804 Table Create Table
805 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
806 create or replace sequence t1 maxvalue 9223372036854775808;
807 Warnings:
808 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
809 show create sequence t1;
810 Table Create Table
811 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
812 create or replace sequence t1 maxvalue 9999999999999999999999;
813 Warnings:
814 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
815 show create sequence t1;
816 Table Create Table
817 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
818 # -----
819 # Create with value types
820 # -----
821 create or replace sequence t1 as tinyint;
822 show create sequence t1;
823 Table Create Table
824 t1 CREATE SEQUENCE `t1` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
825 show create table t1;
826 Table Create Table
827 t1 CREATE TABLE `t1` (
828 `next_not_cached_value` tinyint(5) NOT NULL,
829 `minimum_value` tinyint(5) NOT NULL,
830 `maximum_value` tinyint(5) NOT NULL,
831 `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
832 `increment` bigint(21) NOT NULL COMMENT 'increment value',
833 `cache_size` bigint(21) unsigned NOT NULL,
834 `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',
835 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
836 ) ENGINE=MyISAM SEQUENCE=1
837 create or replace sequence t1 as smallint;
838 show create sequence t1;
839 Table Create Table
840 t1 CREATE SEQUENCE `t1` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM
841 show create table t1;
842 Table Create Table
843 t1 CREATE TABLE `t1` (
844 `next_not_cached_value` smallint(7) NOT NULL,
845 `minimum_value` smallint(7) NOT NULL,
846 `maximum_value` smallint(7) NOT NULL,
847 `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
848 `increment` bigint(21) NOT NULL COMMENT 'increment value',
849 `cache_size` bigint(21) unsigned NOT NULL,
850 `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',
851 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
852 ) ENGINE=MyISAM SEQUENCE=1
853 create or replace sequence t1 as mediumint;
854 show create sequence t1;
855 Table Create Table
856 t1 CREATE SEQUENCE `t1` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM
857 show create table t1;
858 Table Create Table
859 t1 CREATE TABLE `t1` (
860 `next_not_cached_value` mediumint(10) NOT NULL,
861 `minimum_value` mediumint(10) NOT NULL,
862 `maximum_value` mediumint(10) NOT NULL,
863 `start_value` mediumint(10) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
864 `increment` bigint(21) NOT NULL COMMENT 'increment value',
865 `cache_size` bigint(21) unsigned NOT NULL,
866 `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',
867 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
868 ) ENGINE=MyISAM SEQUENCE=1
869 create or replace sequence t1 as int;
870 show create sequence t1;
871 Table Create Table
872 t1 CREATE SEQUENCE `t1` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM
873 show create table t1;
874 Table Create Table
875 t1 CREATE TABLE `t1` (
876 `next_not_cached_value` int(12) NOT NULL,
877 `minimum_value` int(12) NOT NULL,
878 `maximum_value` int(12) NOT NULL,
879 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
880 `increment` bigint(21) NOT NULL COMMENT 'increment value',
881 `cache_size` bigint(21) unsigned NOT NULL,
882 `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',
883 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
884 ) ENGINE=MyISAM SEQUENCE=1
885 create or replace sequence t1 as bigint;
886 show create sequence t1;
887 Table Create Table
888 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
889 show create table t1;
890 Table Create Table
891 t1 CREATE TABLE `t1` (
892 `next_not_cached_value` bigint(21) NOT NULL,
893 `minimum_value` bigint(21) NOT NULL,
894 `maximum_value` bigint(21) NOT NULL,
895 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
896 `increment` bigint(21) NOT NULL COMMENT 'increment value',
897 `cache_size` bigint(21) unsigned NOT NULL,
898 `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',
899 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
900 ) ENGINE=MyISAM SEQUENCE=1
901 create or replace sequence t1 as tinyint unsigned;
902 show create sequence t1;
903 Table Create Table
904 t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM
905 show create table t1;
906 Table Create Table
907 t1 CREATE TABLE `t1` (
908 `next_not_cached_value` tinyint(5) unsigned NOT NULL,
909 `minimum_value` tinyint(5) unsigned NOT NULL,
910 `maximum_value` tinyint(5) unsigned NOT NULL,
911 `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
912 `increment` bigint(21) NOT NULL COMMENT 'increment value',
913 `cache_size` bigint(21) unsigned NOT NULL,
914 `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',
915 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
916 ) ENGINE=MyISAM SEQUENCE=1
917 create or replace sequence t1 as smallint unsigned;
918 show create sequence t1;
919 Table Create Table
920 t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM
921 show create table t1;
922 Table Create Table
923 t1 CREATE TABLE `t1` (
924 `next_not_cached_value` smallint(7) unsigned NOT NULL,
925 `minimum_value` smallint(7) unsigned NOT NULL,
926 `maximum_value` smallint(7) unsigned NOT NULL,
927 `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
928 `increment` bigint(21) NOT NULL COMMENT 'increment value',
929 `cache_size` bigint(21) unsigned NOT NULL,
930 `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',
931 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
932 ) ENGINE=MyISAM SEQUENCE=1
933 create or replace sequence t1 as mediumint unsigned;
934 show create sequence t1;
935 Table Create Table
936 t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM
937 show create table t1;
938 Table Create Table
939 t1 CREATE TABLE `t1` (
940 `next_not_cached_value` mediumint(10) unsigned NOT NULL,
941 `minimum_value` mediumint(10) unsigned NOT NULL,
942 `maximum_value` mediumint(10) unsigned NOT NULL,
943 `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
944 `increment` bigint(21) NOT NULL COMMENT 'increment value',
945 `cache_size` bigint(21) unsigned NOT NULL,
946 `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',
947 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
948 ) ENGINE=MyISAM SEQUENCE=1
949 create or replace sequence t1 as int unsigned;
950 show create sequence t1;
951 Table Create Table
952 t1 CREATE SEQUENCE `t1` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM
953 show create table t1;
954 Table Create Table
955 t1 CREATE TABLE `t1` (
956 `next_not_cached_value` int(12) unsigned NOT NULL,
957 `minimum_value` int(12) unsigned NOT NULL,
958 `maximum_value` int(12) unsigned NOT NULL,
959 `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
960 `increment` bigint(21) NOT NULL COMMENT 'increment value',
961 `cache_size` bigint(21) unsigned NOT NULL,
962 `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',
963 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
964 ) ENGINE=MyISAM SEQUENCE=1
965 create or replace sequence t1 as bigint unsigned;
966 show create sequence t1;
967 Table Create Table
968 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
969 show create table t1;
970 Table Create Table
971 t1 CREATE TABLE `t1` (
972 `next_not_cached_value` bigint(21) unsigned NOT NULL,
973 `minimum_value` bigint(21) unsigned NOT NULL,
974 `maximum_value` bigint(21) unsigned NOT NULL,
975 `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
976 `increment` bigint(21) NOT NULL COMMENT 'increment value',
977 `cache_size` bigint(21) unsigned NOT NULL,
978 `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',
979 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
980 ) ENGINE=MyISAM SEQUENCE=1
981 create or replace sequence t1 increment -1 as tinyint;
982 show create sequence t1;
983 Table Create Table
984 t1 CREATE SEQUENCE `t1` as tinyint start with -1 minvalue -127 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
985 show create table t1;
986 Table Create Table
987 t1 CREATE TABLE `t1` (
988 `next_not_cached_value` tinyint(5) NOT NULL,
989 `minimum_value` tinyint(5) NOT NULL,
990 `maximum_value` tinyint(5) NOT NULL,
991 `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
992 `increment` bigint(21) NOT NULL COMMENT 'increment value',
993 `cache_size` bigint(21) unsigned NOT NULL,
994 `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',
995 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
996 ) ENGINE=MyISAM SEQUENCE=1
997 create or replace sequence t1 increment -1 as smallint;
998 show create sequence t1;
999 Table Create Table
1000 t1 CREATE SEQUENCE `t1` as smallint start with -1 minvalue -32767 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1001 show create table t1;
1002 Table Create Table
1003 t1 CREATE TABLE `t1` (
1004 `next_not_cached_value` smallint(7) NOT NULL,
1005 `minimum_value` smallint(7) NOT NULL,
1006 `maximum_value` smallint(7) NOT NULL,
1007 `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1008 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1009 `cache_size` bigint(21) unsigned NOT NULL,
1010 `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',
1011 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1012 ) ENGINE=MyISAM SEQUENCE=1
1013 create or replace sequence t1 increment -1 as mediumint;
1014 show create sequence t1;
1015 Table Create Table
1016 t1 CREATE SEQUENCE `t1` as mediumint start with -1 minvalue -8388607 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1017 show create table t1;
1018 Table Create Table
1019 t1 CREATE TABLE `t1` (
1020 `next_not_cached_value` mediumint(10) NOT NULL,
1021 `minimum_value` mediumint(10) NOT NULL,
1022 `maximum_value` mediumint(10) NOT NULL,
1023 `start_value` mediumint(10) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1024 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1025 `cache_size` bigint(21) unsigned NOT NULL,
1026 `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',
1027 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1028 ) ENGINE=MyISAM SEQUENCE=1
1029 create or replace sequence t1 increment -1 as int;
1030 show create sequence t1;
1031 Table Create Table
1032 t1 CREATE SEQUENCE `t1` as int start with -1 minvalue -2147483647 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1033 show create table t1;
1034 Table Create Table
1035 t1 CREATE TABLE `t1` (
1036 `next_not_cached_value` int(12) NOT NULL,
1037 `minimum_value` int(12) NOT NULL,
1038 `maximum_value` int(12) NOT NULL,
1039 `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1040 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1041 `cache_size` bigint(21) unsigned NOT NULL,
1042 `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',
1043 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1044 ) ENGINE=MyISAM SEQUENCE=1
1045 create or replace sequence t1 increment -1 as bigint;
1046 show create sequence t1;
1047 Table Create Table
1048 t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1049 show create table t1;
1050 Table Create Table
1051 t1 CREATE TABLE `t1` (
1052 `next_not_cached_value` bigint(21) NOT NULL,
1053 `minimum_value` bigint(21) NOT NULL,
1054 `maximum_value` bigint(21) NOT NULL,
1055 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1056 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1057 `cache_size` bigint(21) unsigned NOT NULL,
1058 `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',
1059 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1060 ) ENGINE=MyISAM SEQUENCE=1
1061 create or replace sequence t1 increment -1 as tinyint unsigned;
1062 show create sequence t1;
1063 Table Create Table
1064 t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 254 minvalue 1 maxvalue 254 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1065 show create table t1;
1066 Table Create Table
1067 t1 CREATE TABLE `t1` (
1068 `next_not_cached_value` tinyint(5) unsigned NOT NULL,
1069 `minimum_value` tinyint(5) unsigned NOT NULL,
1070 `maximum_value` tinyint(5) unsigned NOT NULL,
1071 `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1072 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1073 `cache_size` bigint(21) unsigned NOT NULL,
1074 `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',
1075 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1076 ) ENGINE=MyISAM SEQUENCE=1
1077 create or replace sequence t1 increment -1 as smallint unsigned;
1078 show create sequence t1;
1079 Table Create Table
1080 t1 CREATE SEQUENCE `t1` as smallint unsigned start with 65534 minvalue 1 maxvalue 65534 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1081 show create table t1;
1082 Table Create Table
1083 t1 CREATE TABLE `t1` (
1084 `next_not_cached_value` smallint(7) unsigned NOT NULL,
1085 `minimum_value` smallint(7) unsigned NOT NULL,
1086 `maximum_value` smallint(7) unsigned NOT NULL,
1087 `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1088 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1089 `cache_size` bigint(21) unsigned NOT NULL,
1090 `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',
1091 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1092 ) ENGINE=MyISAM SEQUENCE=1
1093 create or replace sequence t1 increment -1 as mediumint unsigned;
1094 show create sequence t1;
1095 Table Create Table
1096 t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 16777214 minvalue 1 maxvalue 16777214 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1097 show create table t1;
1098 Table Create Table
1099 t1 CREATE TABLE `t1` (
1100 `next_not_cached_value` mediumint(10) unsigned NOT NULL,
1101 `minimum_value` mediumint(10) unsigned NOT NULL,
1102 `maximum_value` mediumint(10) unsigned NOT NULL,
1103 `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1104 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1105 `cache_size` bigint(21) unsigned NOT NULL,
1106 `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',
1107 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1108 ) ENGINE=MyISAM SEQUENCE=1
1109 create or replace sequence t1 increment -1 as int unsigned;
1110 show create sequence t1;
1111 Table Create Table
1112 t1 CREATE SEQUENCE `t1` as int unsigned start with 4294967294 minvalue 1 maxvalue 4294967294 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1113 show create table t1;
1114 Table Create Table
1115 t1 CREATE TABLE `t1` (
1116 `next_not_cached_value` int(12) unsigned NOT NULL,
1117 `minimum_value` int(12) unsigned NOT NULL,
1118 `maximum_value` int(12) unsigned NOT NULL,
1119 `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1120 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1121 `cache_size` bigint(21) unsigned NOT NULL,
1122 `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',
1123 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1124 ) ENGINE=MyISAM SEQUENCE=1
1125 create or replace sequence t1 increment -1 as bigint unsigned;
1126 show create sequence t1;
1127 Table Create Table
1128 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 18446744073709551614 minvalue 1 maxvalue 18446744073709551614 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1129 show create table t1;
1130 Table Create Table
1131 t1 CREATE TABLE `t1` (
1132 `next_not_cached_value` bigint(21) unsigned NOT NULL,
1133 `minimum_value` bigint(21) unsigned NOT NULL,
1134 `maximum_value` bigint(21) unsigned NOT NULL,
1135 `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1136 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1137 `cache_size` bigint(21) unsigned NOT NULL,
1138 `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',
1139 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1140 ) ENGINE=MyISAM SEQUENCE=1
1141 create or replace sequence t1 as tinyint zerofill;
1142 ERROR HY000: Incorrect value 'ZEROFILL' for option 'AS'
1143 create or replace sequence t1 as bigint unsigned start with 12345678901234567890;
1144 show create sequence t1;
1145 Table Create Table
1146 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 12345678901234567890 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1147 show create table t1;
1148 Table Create Table
1149 t1 CREATE TABLE `t1` (
1150 `next_not_cached_value` bigint(21) unsigned NOT NULL,
1151 `minimum_value` bigint(21) unsigned NOT NULL,
1152 `maximum_value` bigint(21) unsigned NOT NULL,
1153 `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1154 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1155 `cache_size` bigint(21) unsigned NOT NULL,
1156 `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',
1157 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1158 ) ENGINE=MyISAM SEQUENCE=1
1159 create or replace sequence t1 as bigint unsigned increment 12345678901234567;
1160 ERROR HY000: Sequence 'test.t1' has out of range value for options
1161 # -----
1162 # value types + truncating
1163 # -----
1164 create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned;
1165 Warnings:
1166 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
1167 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1168 show create sequence t1;
1169 Table Create Table
1170 t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1171 show create table t1;
1172 Table Create Table
1173 t1 CREATE TABLE `t1` (
1174 `next_not_cached_value` tinyint(5) unsigned NOT NULL,
1175 `minimum_value` tinyint(5) unsigned NOT NULL,
1176 `maximum_value` tinyint(5) unsigned NOT NULL,
1177 `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1178 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1179 `cache_size` bigint(21) unsigned NOT NULL,
1180 `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',
1181 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1182 ) ENGINE=MyISAM SEQUENCE=1
1183 create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint;
1184 Warnings:
1185 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
1186 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1187 show create sequence t1;
1188 Table Create Table
1189 t1 CREATE SEQUENCE `t1` as smallint start with -32767 minvalue -32767 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1190 show create table t1;
1191 Table Create Table
1192 t1 CREATE TABLE `t1` (
1193 `next_not_cached_value` smallint(7) NOT NULL,
1194 `minimum_value` smallint(7) NOT NULL,
1195 `maximum_value` smallint(7) NOT NULL,
1196 `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1197 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1198 `cache_size` bigint(21) unsigned NOT NULL,
1199 `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',
1200 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1201 ) ENGINE=MyISAM SEQUENCE=1
1202 create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned;
1203 Warnings:
1204 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
1205 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1206 show create sequence t1;
1207 Table Create Table
1208 t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1209 show create table t1;
1210 Table Create Table
1211 t1 CREATE TABLE `t1` (
1212 `next_not_cached_value` smallint(7) unsigned NOT NULL,
1213 `minimum_value` smallint(7) unsigned NOT NULL,
1214 `maximum_value` smallint(7) unsigned NOT NULL,
1215 `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1216 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1217 `cache_size` bigint(21) unsigned NOT NULL,
1218 `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',
1219 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1220 ) ENGINE=MyISAM SEQUENCE=1
1221 create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234;
1222 Warnings:
1223 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
1224 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1225 show create sequence t1;
1226 Table Create Table
1227 t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1228 show create table t1;
1229 Table Create Table
1230 t1 CREATE TABLE `t1` (
1231 `next_not_cached_value` mediumint(10) unsigned NOT NULL,
1232 `minimum_value` mediumint(10) unsigned NOT NULL,
1233 `maximum_value` mediumint(10) unsigned NOT NULL,
1234 `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1235 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1236 `cache_size` bigint(21) unsigned NOT NULL,
1237 `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',
1238 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1239 ) ENGINE=MyISAM SEQUENCE=1
1240 create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234;
1241 Warnings:
1242 Note 1292 Truncated incorrect INTEGER value: 'MINVALUE'
1243 show create sequence t1;
1244 Table Create Table
1245 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 12345678901234 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1246 show create table t1;
1247 Table Create Table
1248 t1 CREATE TABLE `t1` (
1249 `next_not_cached_value` bigint(21) unsigned NOT NULL,
1250 `minimum_value` bigint(21) unsigned NOT NULL,
1251 `maximum_value` bigint(21) unsigned NOT NULL,
1252 `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1253 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1254 `cache_size` bigint(21) unsigned NOT NULL,
1255 `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',
1256 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1257 ) ENGINE=MyISAM SEQUENCE=1
1258 # -----
1259 # indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser.
1260 # -----
1261 create or replace sequence t1 as bigint maxvalue -1 increment by -1;
1262 show create sequence t1;
1263 Table Create Table
1264 t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM
1265 show create table t1;
1266 Table Create Table
1267 t1 CREATE TABLE `t1` (
1268 `next_not_cached_value` bigint(21) NOT NULL,
1269 `minimum_value` bigint(21) NOT NULL,
1270 `maximum_value` bigint(21) NOT NULL,
1271 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1272 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1273 `cache_size` bigint(21) unsigned NOT NULL,
1274 `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',
1275 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1276 ) ENGINE=MyISAM SEQUENCE=1
1277 create or replace sequence t1 as bigint maxvalue 18446744073709551615;
1278 Warnings:
1279 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1280 show create sequence t1;
1281 Table Create Table
1282 t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1283 show create table t1;
1284 Table Create Table
1285 t1 CREATE TABLE `t1` (
1286 `next_not_cached_value` bigint(21) NOT NULL,
1287 `minimum_value` bigint(21) NOT NULL,
1288 `maximum_value` bigint(21) NOT NULL,
1289 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1290 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1291 `cache_size` bigint(21) unsigned NOT NULL,
1292 `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',
1293 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1294 ) ENGINE=MyISAM SEQUENCE=1
1295 create or replace sequence t1 as bigint unsigned maxvalue -1;
1296 ERROR HY000: Sequence 'test.t1' has out of range value for options
1297 create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615;
1298 Warnings:
1299 Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
1300 show create sequence t1;
1301 Table Create Table
1302 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1303 show create table t1;
1304 Table Create Table
1305 t1 CREATE TABLE `t1` (
1306 `next_not_cached_value` bigint(21) unsigned NOT NULL,
1307 `minimum_value` bigint(21) unsigned NOT NULL,
1308 `maximum_value` bigint(21) unsigned NOT NULL,
1309 `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
1310 `increment` bigint(21) NOT NULL COMMENT 'increment value',
1311 `cache_size` bigint(21) unsigned NOT NULL,
1312 `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',
1313 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
1314 ) ENGINE=MyISAM SEQUENCE=1
1315 # -----
1316 # value types + out of range start
1317 # -----
1318 create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned;
1319 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 '123456789012345678901 as tinyint unsigned' at line 1
1320 create or replace sequence t1 start with -1 as tinyint unsigned;
1321 ERROR HY000: Sequence 'test.t1' has out of range value for options
1322 create or replace sequence t1 start with 0 as tinyint unsigned;
1323 ERROR HY000: Sequence 'test.t1' has out of range value for options
1324 show create sequence t1;
1325 Table Create Table
1326 t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1327 create or replace sequence t1 start with 1 as tinyint unsigned;
1328 show create sequence t1;
1329 Table Create Table
1330 t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM
1331 create or replace sequence t1 start with 254 as tinyint unsigned;
1332 create or replace sequence t1 start with 255 as tinyint unsigned;
1333 ERROR HY000: Sequence 'test.t1' has out of range value for options
1334 create or replace sequence t1 start with 256 as tinyint unsigned;
1335 ERROR HY000: Sequence 'test.t1' has out of range value for options
1336 create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned;
1337 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 '123456789012345678901 as tinyint unsigned' at line 1
1338 drop sequence t1;
1339 # -----
1340 # information_schema.sequences
1341 # -----
1342 create sequence s1 as tinyint unsigned increment by 23;
1343 create sequence s2 start with 42 minvalue -9223372036854775807;
1344 create sequence s3 as bigint unsigned start with 12345678901234567890 cycle;
1345 create sequence s4 as tinyint;
1346 create sequence s5 as smallint;
1347 create sequence s6 as mediumint;
1348 create sequence s7 as int;
1349 create sequence s8 as bigint;
1350 create sequence s9 as tinyint unsigned;
1351 create sequence s10 as smallint unsigned;
1352 create sequence s11 as mediumint unsigned;
1353 create sequence s12 as int unsigned;
1354 create sequence s13 as bigint unsigned;
1355 select * from information_schema.sequences order by sequence_name;
1356 SEQUENCE_CATALOG SEQUENCE_SCHEMA SEQUENCE_NAME DATA_TYPE NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE START_VALUE MINIMUM_VALUE MAXIMUM_VALUE INCREMENT CYCLE_OPTION
1357 def test s1 tinyint unsigned 8 2 0 1 1 254 23 0
1358 def test s10 smallint unsigned 16 2 0 1 1 65534 1 0
1359 def test s11 mediumint unsigned 24 2 0 1 1 16777214 1 0
1360 def test s12 int unsigned 32 2 0 1 1 4294967294 1 0
1361 def test s13 bigint unsigned 64 2 0 1 1 18446744073709551614 1 0
1362 def test s2 bigint 64 2 0 42 -9223372036854775807 9223372036854775806 1 0
1363 def test s3 bigint unsigned 64 2 0 12345678901234567890 1 18446744073709551614 1 1
1364 def test s4 tinyint 8 2 0 1 1 126 1 0
1365 def test s5 smallint 16 2 0 1 1 32766 1 0
1366 def test s6 mediumint 24 2 0 1 1 8388606 1 0
1367 def test s7 int 32 2 0 1 1 2147483646 1 0
1368 def test s8 bigint 64 2 0 1 1 9223372036854775806 1 0
1369 def test s9 tinyint unsigned 8 2 0 1 1 254 1 0
1370 drop sequence s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13;
1371 # End of 11.5 tests