Debian Codegraph @

mysql-test/suite/sql_sequence/default.result

raw
1 drop table if exists t1,s1,s2;
2 Warnings:
3 Note 1051 Unknown table 'test.t1,test.s1,test.s2'
4 drop view if exists v1;
5 Warnings:
6 Note 4092 Unknown VIEW: 'test.v1'
7 #
8 # Test DEFAULT
9 #
10 CREATE SEQUENCE s1 nocache engine=myisam;
11 CREATE table t1 (a int default next value for s1, b int);
12 show create table t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` int(11) DEFAULT nextval(`test`.`s1`),
16 `b` int(11) DEFAULT NULL
17 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
18 insert into t1 SET b=1;
19 insert into t1 SET b=2;
20 insert into t1 (b) values (3),(4);
21 select * from t1;
22 a b
23 1 1
24 2 2
25 3 3
26 4 4
27 update t1 set b=5 where a=1;
28 delete from t1 where b=1;
29 select * from t1;
30 a b
31 1 5
32 2 2
33 3 3
34 4 4
35 #
36 # Executing DEFAULT function
37 #
38 INSERT into t1 values(default(a),10);
39 INSERT into t1 values(default(a),default(a));
40 update t1 set a=default(a), b=12 where b=2;
41 select * from t1;
42 a b
43 1 5
44 8 12
45 3 3
46 4 4
47 5 10
48 6 7
49 select default(a), a, b from t1;
50 default(a) a b
51 9 1 5
52 10 8 12
53 11 3 3
54 12 4 4
55 13 5 10
56 14 6 7
57 select * from s1;
58 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
59 15 1 9223372036854775806 1 1 0 0 0
60 select * from t1 where default(a) > 0;
61 a b
62 1 5
63 8 12
64 3 3
65 4 4
66 5 10
67 6 7
68 select * from s1;
69 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
70 16 1 9223372036854775806 1 1 0 0 0
71 #
72 # View
73 #
74 create view v1 as select * from t1;
75 insert into v1 set b=20;
76 select * from v1;
77 a b
78 1 5
79 8 12
80 3 3
81 4 4
82 5 10
83 6 7
84 16 20
85 drop view v1;
86 #
87 # Alter table
88 #
89 CREATE SEQUENCE s2 nocache engine=myisam;
90 alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
91 show create table t1;
92 Table Create Table
93 t1 CREATE TABLE `t1` (
94 `a` int(11) DEFAULT nextval(`test`.`s1`),
95 `b` int(11) DEFAULT NULL,
96 `c` int(11) DEFAULT nextval(`test`.`s2`),
97 `d` int(11) DEFAULT lastval(`test`.`s2`)
98 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
99 select * from t1;
100 a b c d
101 1 5 1 1
102 8 12 2 2
103 3 3 3 3
104 4 4 4 4
105 5 10 5 5
106 6 7 6 6
107 16 20 7 7
108 drop sequence s2;
109 show create table t1;
110 Table Create Table
111 t1 CREATE TABLE `t1` (
112 `a` int(11) DEFAULT nextval(`test`.`s1`),
113 `b` int(11) DEFAULT NULL,
114 `c` int(11) DEFAULT nextval(`test`.`s2`),
115 `d` int(11) DEFAULT lastval(`test`.`s2`)
116 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
117 drop table t1;
118 drop sequence s1;
119 #
120 # LOCK TABLES
121 #
122 CREATE SEQUENCE s1 nocache engine=myisam;
123 CREATE table t1 (a int default next value for s1, b int);
124 insert into t1 (b) values (3),(4);
125 LOCK TABLE t1 WRITE;
126 insert into t1 (b) values (5),(6);
127 ERROR HY000: Table 's1' was not locked with LOCK TABLES
128 UNLOCK TABLES;
129 LOCK TABLE t1 WRITE, s1 WRITE;
130 insert into t1 (b) values (5),(6);
131 select default(a) from t1;
132 default(a)
133 5
134 6
135 7
136 8
137 UNLOCK TABLES;
138 LOCK TABLE t1 READ;
139 insert into t1 (b) values (5),(6);
140 ERROR HY000: Table 's1' was not locked with LOCK TABLES
141 select default(a) from t1;
142 ERROR HY000: Table 's1' was not locked with LOCK TABLES
143 UNLOCK TABLES;
144 LOCK TABLE t1 READ, s1 read;
145 insert into t1 (b) values (5),(6);
146 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
147 select default(a) from t1;
148 ERROR HY000: Table 's1' was locked with a READ lock and can't be updated
149 UNLOCK TABLES;
150 drop table t1;
151 drop sequence s1;
152 #
153 # Testing prepared statements
154 #
155 CREATE or replace SEQUENCE s1 nocache engine=myisam;
156 CREATE or replace table t1 (a int default next value for s1, b int);
157 PREPARE stmt FROM "insert into t1 (b) values(?)";
158 execute stmt using 1;
159 execute stmt using 2;
160 execute stmt using 3;
161 select * from t1;
162 a b
163 1 1
164 2 2
165 3 3
166 drop table t1,s1;
167 deallocate prepare stmt;
168 #
169 # Wrong usage of default
170 #
171 CREATE table t1 (a int default next value for s1, b int);
172 ERROR 42S02: Table 'test.s1' doesn't exist
173 CREATE SEQUENCE s1 nocache engine=myisam;
174 CREATE table t1 (a int default next value for s1, b int);
175 DROP SEQUENCE s1;
176 insert into t1 (b) values (5),(6);
177 ERROR 42S02: Table 'test.s1' doesn't exist
178 ALTER TABLE t1 add column c int;
179 ERROR 42S02: Table 'test.s1' doesn't exist
180 CREATE SEQUENCE s1 nocache engine=myisam;
181 ALTER TABLE t1 add column c int;
182 ALTER TABLE t1 add column d int default next value for s_not_exits;
183 ERROR 42S02: Table 'test.s_not_exits' doesn't exist
184 drop table t1;
185 drop sequence s1;
186 #
187 # MDEV 22785 Crash with prepared statements and NEXTVAL()
188 #
189 CREATE SEQUENCE s;
190 CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id));
191 PREPARE stmt FROM " INSERT INTO t1 () values ()";
192 INSERT INTO t1 () values ();
193 EXECUTE stmt;
194 DROP TABLE t1;
195 DROP SEQUENCE s;
196 #
197 # MDEV-29540 Incorrect sequence values in INSERT SELECT
198 #
199 CREATE SEQUENCE s1;
200 CREATE TABLE t1 (
201 a BIGINT UNSIGNED NOT NULL PRIMARY KEY
202 DEFAULT (NEXT VALUE FOR s1),
203 b CHAR(1) NOT NULL
204 );
205 INSERT INTO t1 (b) VALUES ('a');
206 INSERT INTO t1 (b) VALUES ('b'), ('c');
207 INSERT INTO t1 (b) VALUES ('d');
208 INSERT INTO t1 (b) SELECT c FROM (
209 SELECT 'e' as c
210 UNION
211 SELECT 'f'
212 UNION
213 SELECT 'g'
214 ) der;
215 SELECT a, b FROM t1;
216 a b
217 1 a
218 2 b
219 3 c
220 4 d
221 5 e
222 6 f
223 7 g
224 ALTER SEQUENCE s1 RESTART;
225 INSERT INTO t1 (b) SELECT c FROM (
226 SELECT 'a' as c
227 UNION
228 SELECT 'b'
229 UNION
230 SELECT 'c'
231 UNION
232 SELECT 'd'
233 UNION
234 SELECT 'e'
235 UNION
236 SELECT 'f'
237 UNION
238 SELECT 'g'
239 ) der;
240 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
241 ALTER SEQUENCE s1 RESTART;
242 INSERT IGNORE INTO t1 (b) SELECT c FROM (
243 SELECT 'a' as c
244 UNION
245 SELECT 'b'
246 UNION
247 SELECT 'c'
248 UNION
249 SELECT 'd'
250 UNION
251 SELECT 'e'
252 UNION
253 SELECT 'f'
254 UNION
255 SELECT 'g'
256 ) der;
257 Warnings:
258 Warning 1062 Duplicate entry '1' for key 'PRIMARY'
259 Warning 1062 Duplicate entry '2' for key 'PRIMARY'
260 Warning 1062 Duplicate entry '3' for key 'PRIMARY'
261 Warning 1062 Duplicate entry '4' for key 'PRIMARY'
262 Warning 1062 Duplicate entry '5' for key 'PRIMARY'
263 Warning 1062 Duplicate entry '6' for key 'PRIMARY'
264 Warning 1062 Duplicate entry '7' for key 'PRIMARY'
265 SELECT a, b FROM t1;
266 a b
267 1 a
268 2 b
269 3 c
270 4 d
271 5 e
272 6 f
273 7 g
274 INSERT IGNORE INTO t1 (b) SELECT c FROM (
275 SELECT 'h' as c
276 UNION
277 SELECT 'i'
278 UNION
279 SELECT 'j'
280 ) der;
281 SELECT a, b FROM t1;
282 a b
283 1 a
284 2 b
285 3 c
286 4 d
287 5 e
288 6 f
289 7 g
290 8 h
291 9 i
292 10 j
293 DROP TABLE t1;
294 DROP SEQUENCE s1;
295 # End of 10.3 tests
296 # in UPDATE
297 create sequence s1 cache 0;
298 create table t1 (id int unsigned default nextval(s1));
299 insert t1 values ();
300 update t1 set id=default;
301 prepare stmt from "update t1 set id=?";
302 execute stmt using default;
303 deallocate prepare stmt;
304 drop table t1;
305 drop sequence s1;
306 #
307 # MDEV-37302 Assertion failure in Table_triggers_list::add_tables_and_routines_for_triggers upon attempt to insert DEFAULT into non-insertable view
308 #
309 create table t1 (f int);
310 create algorithm=temptable view v1 as select * from t1;
311 create trigger tr before update on t1 for each row set @a=1;
312 insert v1 values (default);
313 ERROR HY000: The target table v1 of the INSERT is not insertable-into
314 drop view v1;
315 drop table t1;
316 #
317 # MDEV-37172 Server crashes in Item_func_nextval::update_table after
318 # INSERT to the table, that uses expression with nextval() as default
319 #
320 create sequence s1;
321 create sequence s2;
322 create table t1 (id int default(nextval(s1)));
323 create table t2 (id int default(nextval(s1)+nextval(s1)));
324 create table t3 (id1 int default(nextval(s1)+nextval(s1)),
325 id2 int default(nextval(s2)));
326 insert t1 values ();
327 select lastval(s1);
328 lastval(s1)
329 1
330 select * from t1;
331 id
332 1
333 insert t2 values ();
334 select lastval(s1);
335 lastval(s1)
336 3
337 select * from t2;
338 id
339 5
340 insert t3 values ();
341 select lastval(s1), lastval(s2);
342 lastval(s1) lastval(s2)
343 5 1
344 select * from t3;
345 id1 id2
346 9 1
347 drop table t1,t2,t3;
348 drop sequence s1,s2;
349 # End of 10.6 tests