Debian Codegraph @

mysql-test/suite/sql_sequence/other.test

raw
1 --source include/have_sequence.inc
2 --source include/have_innodb.inc
3
4 #
5 # Test various combinations of operations on sequence
6 #
7
8 --echo #
9 --echo # Create and check
10 --echo #
11
12 --disable_ps2_protocol
13 create sequence s1 engine=innodb;
14 check table s1;
15 select next value for s1;
16 flush tables;
17 check table s1;
18 select next value for s1;
19 flush tables;
20 repair table s1;
21 select next value for s1;
22 drop sequence s1;
23
24 create or replace sequence s1 engine=innodb;
25 select next value for s1;
26 repair table s1;
27 check table s1;
28 select next value for s1;
29 select * from s1;
30 drop sequence s1;
31 --enable_ps2_protocol
32
33 --echo #
34 --echo # INSERT
35 --echo #
36
37 create sequence s1;
38 create sequence s2;
39 --error ER_NO_DEFAULT_FOR_FIELD
40 insert into s1 (next_not_cached_value, minimum_value) values (100,1000);
41 insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
42 --error ER_SEQUENCE_INVALID_DATA
43 insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
44 --error ER_SEQUENCE_INVALID_DATA
45 insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0);
46 select * from s1;
47 insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0);
48 select * from s1;
49 --disable_ps2_protocol
50 select next value for s1;
51 --enable_ps2_protocol
52 select * from s1;
53 --error ER_SEQUENCE_INVALID_DATA
54 insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
55
56 select * from s1;
57 insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
58 select * from s1;
59 select * from s2;
60 insert into s1 select * from s2;
61 select * from s1;
62 drop sequence s1,s2;
63
64 --echo #
65 --echo # UPDATE and DELETE
66 --echo #
67
68 create sequence s1;
69 --error ER_ILLEGAL_HA
70 update s1 set next_not_cached_value=100;
71 --error ER_ILLEGAL_HA
72 delete from s1 where next_not_cached_value > 0;
73 drop sequence s1;
74
75 --echo #
76 --echo # SHOW TABLES
77 --echo #
78
79 create sequence s1;
80 create table t1 (a int);
81 create view v1 as select * from s1;
82 show full tables;
83 SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test" ORDER BY TABLE_NAME;
84 drop table t1,s1;
85 drop view v1;
86
87 --echo #
88 --echo # LOCK TABLES (as in mysqldump)
89 --echo #
90
91 create sequence s1 engine=innodb;
92 LOCK TABLES s1 READ;
93 SELECT * from s1;
94 UNLOCK TABLES;
95 LOCK TABLES s1 WRITE;
96 insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0);
97 UNLOCK TABLES;
98 drop table s1;
99
100 --echo #
101 --echo # Many sequence calls with innodb
102 --echo #
103
104 --disable_ps2_protocol
105 create sequence s1 cache=1000 engine=innodb;
106 start transaction;
107 select count(nextval(s1)) from seq_1_to_2000;
108 commit;
109 select * from s1;
110 drop sequence s1;
111
112 create sequence s1 cache=1000 engine=innodb;
113 start transaction;
114 select count(nextval(s1)) from seq_1_to_2000;
115
116 connect (addconroot, localhost, root,,);
117 connection addconroot;
118 start transaction;
119 select count(nextval(s1)) from seq_1_to_2000;
120 select * from s1;
121 commit;
122 disconnect addconroot;
123 connection default;
124 select * from s1;
125 commit;
126 drop sequence s1;
127 --enable_ps2_protocol
128
129 --echo #
130 --echo # Flush tables with read lock
131 --echo #
132
133 --disable_ps2_protocol
134 create sequence s1;
135 select next value for s1;
136 flush tables with read lock;
137 --error 1223
138 create sequence s2;
139 --error 1223
140 select next value for s1;
141 unlock tables;
142 drop sequence s1;
143 --enable_ps2_protocol
144
145 --echo #
146 --echo # MDEV-14761
147 --echo # Assertion `!mysql_parse_status || thd->is_error() ||
148 --echo # thd->get_internal_handler()' failed in parse_sql
149 --echo #
150
151 CREATE SEQUENCE s1;
152 --error ER_DUP_ARGUMENT
153 ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE;
154 DROP SEQUENCE s1;
155
156 --echo #
157 --echo # Don't allow SEQUENCE to be used with CHECK or virtual fields
158 --echo #
159
160 CREATE SEQUENCE s1 nocache engine=myisam;
161 --error ER_SUBQUERIES_NOT_SUPPORTED
162 CREATE table t1 (a int check (next value for s1 > 0));
163 --error ER_SUBQUERIES_NOT_SUPPORTED
164 CREATE table t1 (a int check (previous value for s1 > 0));
165 --error ER_SUBQUERIES_NOT_SUPPORTED
166 CREATE table t1 (a int check (setval(s1,10)));
167 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
168 CREATE TABLE t1 (a int, b int as (next value for s1 > 0));
169 drop sequence s1;
170
171
172 --echo #
173 --echo # MDEV-13024: Server crashes in my_store_ptr upon DELETE from
174 --echo # sequence in multi-table format
175 --echo #
176 CREATE SEQUENCE s;
177 CREATE table t1 (a int);
178 insert into t1 values (1),(2);
179 DELETE s FROM s;
180 delete t1,s from s,t1;
181 delete s,t1 from t1,s;
182 DROP SEQUENCE s;
183 DROP TABLE t1;
184
185
186 --echo #
187 --echo # MDEV-20074: Lost connection on update trigger
188 --echo #
189
190 --echo # INSERT & table
191 create sequence s1 increment by 1 start with 1;
192 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
193 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);
194
195 insert into t1 values
196 (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');
197
198 DELIMITER $$;
199
200 CREATE TRIGGER tr_upd
201 BEFORE UPDATE on t1
202 FOR EACH ROW
203 BEGIN
204 INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
205 END;
206 $$
207 DELIMITER ;$$
208
209 update t1 set p_first_name='Yunxi' where p_id=1;
210
211 drop sequence s1;
212 drop table t1,t2;
213
214
215 --echo # INSERT & view
216 create sequence s1 increment by 1 start with 1;
217 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
218 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);
219 create view v2 as select * from t2;
220
221 insert into t1 values
222 (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');
223
224 DELIMITER $$;
225
226 CREATE TRIGGER tr_upd
227 BEFORE UPDATE on t1
228 FOR EACH ROW
229 BEGIN
230 INSERT INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
231 END;
232 $$
233 DELIMITER ;$$
234
235 update t1 set p_first_name='Yunxi' where p_id=1;
236
237 drop view v2;
238 drop table t1,t2;
239 drop sequence s1;
240
241
242 --echo # INSERT SELECT & view
243 create sequence s1 increment by 1 start with 1;
244 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
245 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);
246 create view v2 as select * from t2;
247
248 insert into t1 values
249 (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');
250
251 DELIMITER $$;
252
253 CREATE TRIGGER tr_upd
254 BEFORE UPDATE on t1
255 FOR EACH ROW
256 BEGIN
257 INSERT INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name;
258 END;
259 $$
260 DELIMITER ;$$
261
262 update t1 set p_first_name='Yunxi' where p_id=1;
263
264 drop view v2;
265 drop table t1,t2;
266 drop sequence s1;
267
268
269 --echo # REPLACE & view
270 create sequence s1 increment by 1 start with 1;
271 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
272 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);
273 create view v2 as select * from t2;
274
275 insert into t1 values
276 (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');
277
278 DELIMITER $$;
279
280 CREATE TRIGGER tr_upd
281 BEFORE UPDATE on t1
282 FOR EACH ROW
283 BEGIN
284 REPLACE INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name);
285 END;
286 $$
287 DELIMITER ;$$
288
289 update t1 set p_first_name='Yunxi' where p_id=1;
290
291 drop view v2;
292 drop table t1,t2;
293 drop sequence s1;
294
295
296 --echo # REPLACE SELECT & view
297 create sequence s1 increment by 1 start with 1;
298 create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128));
299 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);
300 create view v2 as select * from t2;
301
302 insert into t1 values
303 (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');
304
305 DELIMITER $$;
306
307 CREATE TRIGGER tr_upd
308 BEFORE UPDATE on t1
309 FOR EACH ROW
310 BEGIN
311 REPLACE INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name;
312 END;
313 $$
314 DELIMITER ;$$
315
316 update t1 set p_first_name='Yunxi' where p_id=1;
317
318 drop view v2;
319 drop table t1,t2;
320 drop sequence s1;
321
322 --echo #
323 --echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or
324 --echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE,
325 --echo # table->db.str, table->table_name.str, MDL_SHARED)' failed
326 --echo # in mysql_rm_table_no_locks
327 --echo #
328
329 CREATE TABLE t1 (a INT);
330 CREATE TEMPORARY TABLE tmp (b INT);
331 LOCK TABLE t1 READ;
332 --error ER_UNKNOWN_SEQUENCES
333 DROP SEQUENCE tmp;
334 --error ER_UNKNOWN_SEQUENCES
335 DROP TEMPORARY SEQUENCE tmp;
336 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
337 DROP SEQUENCE t1;
338 --error ER_UNKNOWN_SEQUENCES
339 DROP TEMPORARY SEQUENCE t1;
340 UNLOCK TABLES;
341 --error ER_NOT_SEQUENCE2
342 DROP SEQUENCE t1;
343 --error ER_UNKNOWN_SEQUENCES
344 DROP TEMPORARY SEQUENCE t1;
345
346 # Cleanup
347 DROP TABLE t1;
348
349
350 CREATE TABLE t (a INT);
351 CREATE SEQUENCE s;
352 LOCK TABLE t WRITE;
353 --error ER_TABLE_NOT_LOCKED
354 DROP SEQUENCE s;
355 --error ER_UNKNOWN_SEQUENCES
356 DROP TEMPORARY SEQUENCE s;
357 UNLOCK TABLES;
358 CREATE TEMPORARY SEQUENCE s;
359 LOCK TABLE t WRITE;
360 DROP TEMPORARY SEQUENCE s;
361 UNLOCK TABLES;
362 DROP SEQUENCE s;
363
364 create table s(a INT);
365 CREATE TEMPORARY TABLE s (f INT);
366 LOCK TABLE t WRITE;
367 DROP TEMPORARY TABLE s;
368 CREATE TEMPORARY TABLE s (f INT);
369 DROP TABLE s;
370 --error ER_TABLE_NOT_LOCKED
371 DROP TABLE s;
372 UNLOCK TABLES;
373 DROP TABLE s;
374
375 CREATE VIEW v1 as SELECT * FROM t;
376 CREATE SEQUENCE s;
377
378 DROP SEQUENCE IF EXISTS v1;
379 DROP VIEW IF EXISTS s;
380
381 DROP VIEW v1;
382 DROP SEQUENCE s;
383 DROP TABLE t;
384 --echo #
385 --echo # End of 10.3 tests
386 --echo #
387
388 --echo #
389 --echo # MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks
390 --echo #
391 create sequence s1;
392 create sequence s2;
393 --connect (con1,localhost,root,,)
394 set session transaction read only;
395 start transaction;
396
397 --connection default
398 start transaction;
399 insert into s2 values (1, 1, 10000, 100, 1, 1000, 0, 0);
400
401 --connection con1
402 select lastval(s1);
403 --send select lastval(s2);
404
405 --connection default
406 set lock_wait_timeout= 1;
407 insert into s1 values (1, 1, 10000, 100, 1, 1000, 0, 0);
408
409 --connection con1
410 --error ER_LOCK_DEADLOCK
411 --reap
412 --disconnect con1
413 --connection default
414 drop sequence s1;
415 drop sequence s2;
416
417 --echo # End of 10.4 tests
418
419 --echo #
420 --echo # MDEV-37906 Server crash or UBSAN errors in Item_func_nextval::update_table upon INSERT DELAYED
421 --echo #
422 create sequence s engine=myisam;
423 create table t (id bigint default(nextval(s))) engine=myisam;
424 insert delayed into t () values();
425 drop table t;
426 drop sequence s;
427
428 --echo #
429 --echo # MDEV-37345 Item_func_nextval::val_int() crash on INSERT...SELECT with subqueries
430 --echo #
431 # sequence and prelocking.
432 create sequence s;
433 create table t1 (a int, b int default(nextval(s)));
434 insert into t1 () values ();
435 create table t2 (c int);
436 create procedure p() update t1 set a = 0;
437 --delimiter $
438 create trigger tr after insert on t2 for each row
439 begin
440 insert into t1 () values ();
441 call p();
442 end $
443 --delimiter ;
444 insert into t2 values ();
445 drop table t1, t2, s;
446 drop procedure p;
447
448 # another one, temporary table, ALTER, recreate sequence
449 create sequence s;
450 create temporary table t (f int);
451 alter table t modify f int default(nextval(s));
452 create or replace sequence s;
453 insert into t values (default);
454 drop sequence s;
455
456 --echo # End of 10.11 tests