Debian Codegraph @

mysql-test/suite/sql_sequence/other.result

raw
1 #
2 # Create and check
3 #
4 create sequence s1 engine=innodb;
5 check table s1;
6 Table Op Msg_type Msg_text
7 test.s1 check note The storage engine for the table doesn't support check
8 select next value for s1;
9 next value for s1
10 1
11 flush tables;
12 check table s1;
13 Table Op Msg_type Msg_text
14 test.s1 check note The storage engine for the table doesn't support check
15 select next value for s1;
16 next value for s1
17 1001
18 flush tables;
19 repair table s1;
20 Table Op Msg_type Msg_text
21 test.s1 repair status OK
22 select next value for s1;
23 next value for s1
24 2001
25 drop sequence s1;
26 create or replace sequence s1 engine=innodb;
27 select next value for s1;
28 next value for s1
29 1
30 repair table s1;
31 Table Op Msg_type Msg_text
32 test.s1 repair status OK
33 check table s1;
34 Table Op Msg_type Msg_text
35 test.s1 check note The storage engine for the table doesn't support check
36 select next value for s1;
37 next value for s1
38 1001
39 select * from s1;
40 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
41 2001 1 9223372036854775806 1 1 1000 0 0
42 drop sequence s1;
43 #
44 # INSERT
45 #
46 create sequence s1;
47 create sequence s2;
48 insert into s1 (next_not_cached_value, minimum_value) values (100,1000);
49 ERROR HY000: Field 'maximum_value' doesn't have a default value
50 insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
51 insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
52 ERROR HY000: Sequence 'test.s1' has out of range value for options
53 insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0);
54 ERROR HY000: Sequence 'test.s1' has out of range value for options
55 select * from s1;
56 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
57 1 1 9223372036854775806 1 1 1000 0 0
58 insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0);
59 select * from s1;
60 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
61 1000 1 9223372036854775806 1 1 1000 0 0
62 select next value for s1;
63 next value for s1
64 1000
65 select * from s1;
66 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
67 2000 1 9223372036854775806 1 1 1000 0 0
68 insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
69 ERROR HY000: Sequence 'test.s2' has out of range value for options
70 select * from s1;
71 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
72 2000 1 9223372036854775806 1 1 1000 0 0
73 insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
74 select * from s1;
75 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
76 1 1 9223372036854775806 1 1 1000 0 0
77 select * from s2;
78 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
79 1001 1 9223372036854775806 1 1 1000 0 0
80 insert into s1 select * from s2;
81 select * from s1;
82 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
83 1001 1 9223372036854775806 1 1 1000 0 0
84 drop sequence s1,s2;
85 #
86 # UPDATE and DELETE
87 #
88 create sequence s1;
89 update s1 set next_not_cached_value=100;
90 ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option
91 delete from s1 where next_not_cached_value > 0;
92 ERROR HY000: Storage engine SEQUENCE of the table `test`.`s1` doesn't have this option
93 drop sequence s1;
94 #
95 # SHOW TABLES
96 #
97 create sequence s1;
98 create table t1 (a int);
99 create view v1 as select * from s1;
100 show full tables;
101 Tables_in_test Table_type
102 s1 SEQUENCE
103 t1 BASE TABLE
104 v1 VIEW
105 SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME;
106 TABLE_TYPE ENGINE
107 SEQUENCE MyISAM
108 BASE TABLE MyISAM
109 VIEW NULL
110 drop table t1,s1;
111 drop view v1;
112 #
113 # LOCK TABLES (as in mysqldump)
114 #
115 create sequence s1 engine=innodb;
116 LOCK TABLES s1 READ;
117 SELECT * from s1;
118 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
119 1 1 9223372036854775806 1 1 1000 0 0
120 UNLOCK TABLES;
121 LOCK TABLES s1 WRITE;
122 insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0);
123 UNLOCK TABLES;
124 drop table s1;
125 #
126 # Many sequence calls with innodb
127 #
128 create sequence s1 cache=1000 engine=innodb;
129 start transaction;
130 select count(nextval(s1)) from seq_1_to_2000;
131 count(nextval(s1))
132 2000
133 commit;
134 select * from s1;
135 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
136 2001 1 9223372036854775806 1 1 1000 0 0
137 drop sequence s1;
138 create sequence s1 cache=1000 engine=innodb;
139 start transaction;
140 select count(nextval(s1)) from seq_1_to_2000;
141 count(nextval(s1))
142 2000
143 connect addconroot, localhost, root,,;
144 connection addconroot;
145 start transaction;
146 select count(nextval(s1)) from seq_1_to_2000;
147 count(nextval(s1))
148 2000
149 select * from s1;
150 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
151 4001 1 9223372036854775806 1 1 1000 0 0
152 commit;
153 disconnect addconroot;
154 connection default;
155 select * from s1;
156 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
157 4001 1 9223372036854775806 1 1 1000 0 0
158 commit;
159 drop sequence s1;
160 #
161 # Flush tables with read lock
162 #
163 create sequence s1;
164 select next value for s1;
165 next value for s1
166 1
167 flush tables with read lock;
168 create sequence s2;
169 ERROR HY000: Can't execute the query because you have a conflicting read lock
170 select next value for s1;
171 ERROR HY000: Can't execute the query because you have a conflicting read lock
172 unlock tables;
173 drop sequence s1;
174 #
175 # MDEV-14761
176 # Assertion `!mysql_parse_status || thd->is_error() ||
177 # thd->get_internal_handler()' failed in parse_sql
178 #
179 CREATE SEQUENCE s1;
180 ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE;
181 ERROR HY000: Option 'MAXVALUE' used twice in statement
182 DROP SEQUENCE s1;
183 #
184 # Don't allow SEQUENCE to be used with CHECK or virtual fields
185 #
186 CREATE SEQUENCE s1 nocache engine=myisam;
187 CREATE table t1 (a int check (next value for s1 > 0));
188 ERROR 42000: CHECK does not support subqueries or stored functions
189 CREATE table t1 (a int check (previous value for s1 > 0));
190 ERROR 42000: CHECK does not support subqueries or stored functions
191 CREATE table t1 (a int check (setval(s1,10)));
192 ERROR 42000: CHECK does not support subqueries or stored functions
193 CREATE TABLE t1 (a int, b int as (next value for s1 > 0));
194 ERROR HY000: Function or expression 'nextval()' cannot be used in the GENERATED ALWAYS AS clause of `b`
195 drop sequence s1;
196 #
197 # MDEV-13024: Server crashes in my_store_ptr upon DELETE from
198 # sequence in multi-table format
199 #
200 CREATE SEQUENCE s;
201 CREATE table t1 (a int);
202 insert into t1 values (1),(2);
203 DELETE s FROM s;
204 delete t1,s from s,t1;
205 delete s,t1 from t1,s;
206 DROP SEQUENCE s;
207 DROP TABLE t1;
208 #
209 # MDEV-20074: Lost connection on update trigger
210 #
211 # INSERT & table
212 create sequence s1 increment by 1 start with 1;
213 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
214 create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
215 insert into t1 values
216 (1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
217 CREATE TRIGGER tr_upd
218 BEFORE UPDATE on t1
219 FOR EACH ROW
220 BEGIN
221 INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
222 END;
223 $$
224 update t1 set p_first_name='Yunxi' where p_id=1;
225 drop sequence s1;
226 drop table t1,t2;
227 # INSERT & view
228 create sequence s1 increment by 1 start with 1;
229 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
230 create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
231 create view v2 as select * from t2;
232 insert into t1 values
233 (1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
234 CREATE TRIGGER tr_upd
235 BEFORE UPDATE on t1
236 FOR EACH ROW
237 BEGIN
238 INSERT INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
239 END;
240 $$
241 update t1 set p_first_name='Yunxi' where p_id=1;
242 drop view v2;
243 drop table t1,t2;
244 drop sequence s1;
245 # INSERT SELECT & view
246 create sequence s1 increment by 1 start with 1;
247 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
248 create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
249 create view v2 as select * from t2;
250 insert into t1 values
251 (1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
252 CREATE TRIGGER tr_upd
253 BEFORE UPDATE on t1
254 FOR EACH ROW
255 BEGIN
256 INSERT INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name;
257 END;
258 $$
259 update t1 set p_first_name='Yunxi' where p_id=1;
260 drop view v2;
261 drop table t1,t2;
262 drop sequence s1;
263 # REPLACE & view
264 create sequence s1 increment by 1 start with 1;
265 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
266 create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
267 create view v2 as select * from t2;
268 insert into t1 values
269 (1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
270 CREATE TRIGGER tr_upd
271 BEFORE UPDATE on t1
272 FOR EACH ROW
273 BEGIN
274 REPLACE INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
275 END;
276 $$
277 update t1 set p_first_name='Yunxi' where p_id=1;
278 drop view v2;
279 drop table t1,t2;
280 drop sequence s1;
281 # REPLACE SELECT & view
282 create sequence s1 increment by 1 start with 1;
283 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
284 create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp);
285 create view v2 as select * from t2;
286 insert into t1 values
287 (1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa');
288 CREATE TRIGGER tr_upd
289 BEFORE UPDATE on t1
290 FOR EACH ROW
291 BEGIN
292 REPLACE INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name;
293 END;
294 $$
295 update t1 set p_first_name='Yunxi' where p_id=1;
296 drop view v2;
297 drop table t1,t2;
298 drop sequence s1;
299 #
300 # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
301 # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
302 # table->db.str, table->table_name.str, MDL_SHARED)' failed
303 # in mysql_rm_table_no_locks
304 #
305 CREATE TABLE t1 (a INT);
306 CREATE TEMPORARY TABLE tmp (b INT);
307 LOCK TABLE t1 READ;
308 DROP SEQUENCE tmp;
309 ERROR 42S02: Unknown SEQUENCE: 'test.tmp'
310 DROP TEMPORARY SEQUENCE tmp;
311 ERROR 42S02: Unknown SEQUENCE: 'test.tmp'
312 DROP SEQUENCE t1;
313 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
314 DROP TEMPORARY SEQUENCE t1;
315 ERROR 42S02: Unknown SEQUENCE: 'test.t1'
316 UNLOCK TABLES;
317 DROP SEQUENCE t1;
318 ERROR 42S02: 'test.t1' is not a SEQUENCE
319 DROP TEMPORARY SEQUENCE t1;
320 ERROR 42S02: Unknown SEQUENCE: 'test.t1'
321 DROP TABLE t1;
322 CREATE TABLE t (a INT);
323 CREATE SEQUENCE s;
324 LOCK TABLE t WRITE;
325 DROP SEQUENCE s;
326 ERROR HY000: Table 's' was not locked with LOCK TABLES
327 DROP TEMPORARY SEQUENCE s;
328 ERROR 42S02: Unknown SEQUENCE: 'test.s'
329 UNLOCK TABLES;
330 CREATE TEMPORARY SEQUENCE s;
331 LOCK TABLE t WRITE;
332 DROP TEMPORARY SEQUENCE s;
333 UNLOCK TABLES;
334 DROP SEQUENCE s;
335 create table s(a INT);
336 CREATE TEMPORARY TABLE s (f INT);
337 LOCK TABLE t WRITE;
338 DROP TEMPORARY TABLE s;
339 CREATE TEMPORARY TABLE s (f INT);
340 DROP TABLE s;
341 DROP TABLE s;
342 ERROR HY000: Table 's' was not locked with LOCK TABLES
343 UNLOCK TABLES;
344 DROP TABLE s;
345 CREATE VIEW v1 as SELECT * FROM t;
346 CREATE SEQUENCE s;
347 DROP SEQUENCE IF EXISTS v1;
348 Warnings:
349 Note 1965 'test.v1' is a view
350 DROP VIEW IF EXISTS s;
351 Warnings:
352 Warning 1347 'test.s' is not of type 'VIEW'
353 Note 4092 Unknown VIEW: 'test.s'
354 DROP VIEW v1;
355 DROP SEQUENCE s;
356 DROP TABLE t;
357 #
358 # End of 10.3 tests
359 #
360 #
361 # MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks
362 #
363 create sequence s1;
364 create sequence s2;
365 connect con1,localhost,root,,;
366 set session transaction read only;
367 start transaction;
368 connection default;
369 start transaction;
370 insert into s2 values (1, 1, 10000, 100, 1, 1000, 0, 0);
371 connection con1;
372 select lastval(s1);
373 lastval(s1)
374 NULL
375 select lastval(s2);;
376 connection default;
377 set lock_wait_timeout= 1;
378 insert into s1 values (1, 1, 10000, 100, 1, 1000, 0, 0);
379 connection con1;
380 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
381 disconnect con1;
382 connection default;
383 drop sequence s1;
384 drop sequence s2;
385 # End of 10.4 tests
386 #
387 # MDEV-37906 Server crash or UBSAN errors in Item_func_nextval::update_table upon INSERT DELAYED
388 #
389 create sequence s engine=myisam;
390 create table t (id bigint default(nextval(s))) engine=myisam;
391 insert delayed into t () values();
392 drop table t;
393 drop sequence s;
394 #
395 # MDEV-37345 Item_func_nextval::val_int() crash on INSERT...SELECT with subqueries
396 #
397 create sequence s;
398 create table t1 (a int, b int default(nextval(s)));
399 insert into t1 () values ();
400 create table t2 (c int);
401 create procedure p() update t1 set a = 0;
402 create trigger tr after insert on t2 for each row
403 begin
404 insert into t1 () values ();
405 call p();
406 end $
407 insert into t2 values ();
408 drop table t1, t2, s;
409 drop procedure p;
410 create sequence s;
411 create temporary table t (f int);
412 alter table t modify f int default(nextval(s));
413 create or replace sequence s;
414 insert into t values (default);
415 drop sequence s;
416 # End of 10.11 tests