Debian Codegraph @

mysql-test/suite/sql_sequence/default.test

raw
1 #
2 # Testing sequence in DEFAULT clause
3 #
4 --source include/have_sequence.inc
5
6 drop table if exists t1,s1,s2;
7 drop view if exists v1;
8
9 --echo #
10 --echo # Test DEFAULT
11 --echo #
12
13 CREATE SEQUENCE s1 nocache engine=myisam;
14 CREATE table t1 (a int default next value for s1, b int);
15 show create table t1;
16 insert into t1 SET b=1;
17 insert into t1 SET b=2;
18 insert into t1 (b) values (3),(4);
19 select * from t1;
20 update t1 set b=5 where a=1;
21 delete from t1 where b=1;
22 select * from t1;
23
24 --echo #
25 --echo # Executing DEFAULT function
26 --echo #
27
28 INSERT into t1 values(default(a),10);
29 INSERT into t1 values(default(a),default(a));
30 update t1 set a=default(a), b=12 where b=2;
31 select * from t1;
32 --disable_ps2_protocol
33 select default(a), a, b from t1;
34 select * from s1;
35 select * from t1 where default(a) > 0;
36 select * from s1;
37 --enable_ps2_protocol
38
39 --echo #
40 --echo # View
41 --echo #
42
43 create view v1 as select * from t1;
44 insert into v1 set b=20;
45 select * from v1;
46 drop view v1;
47
48 --echo #
49 --echo # Alter table
50 --echo #
51
52 CREATE SEQUENCE s2 nocache engine=myisam;
53 alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
54 show create table t1;
55 select * from t1;
56 drop sequence s2;
57 show create table t1;
58 drop table t1;
59 drop sequence s1;
60
61 --echo #
62 --echo # LOCK TABLES
63 --echo #
64
65 CREATE SEQUENCE s1 nocache engine=myisam;
66 CREATE table t1 (a int default next value for s1, b int);
67 insert into t1 (b) values (3),(4);
68 LOCK TABLE t1 WRITE;
69 --error ER_TABLE_NOT_LOCKED
70 insert into t1 (b) values (5),(6);
71 UNLOCK TABLES;
72
73 LOCK TABLE t1 WRITE, s1 WRITE;
74 insert into t1 (b) values (5),(6);
75 --disable_ps2_protocol
76 select default(a) from t1;
77 --enable_ps2_protocol
78 UNLOCK TABLES;
79
80 LOCK TABLE t1 READ;
81 --error ER_TABLE_NOT_LOCKED
82 insert into t1 (b) values (5),(6);
83 --error ER_TABLE_NOT_LOCKED
84 select default(a) from t1;
85 UNLOCK TABLES;
86
87 LOCK TABLE t1 READ, s1 read;
88 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
89 insert into t1 (b) values (5),(6);
90 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
91 select default(a) from t1;
92 UNLOCK TABLES;
93
94 drop table t1;
95 drop sequence s1;
96
97 --echo #
98 --echo # Testing prepared statements
99 --echo #
100
101 CREATE or replace SEQUENCE s1 nocache engine=myisam;
102 CREATE or replace table t1 (a int default next value for s1, b int);
103 PREPARE stmt FROM "insert into t1 (b) values(?)";
104 execute stmt using 1;
105 execute stmt using 2;
106 execute stmt using 3;
107 select * from t1;
108 drop table t1,s1;
109 deallocate prepare stmt;
110
111 --echo #
112 --echo # Wrong usage of default
113 --echo #
114
115 --error ER_NO_SUCH_TABLE
116 CREATE table t1 (a int default next value for s1, b int);
117 CREATE SEQUENCE s1 nocache engine=myisam;
118 CREATE table t1 (a int default next value for s1, b int);
119 DROP SEQUENCE s1;
120 --error ER_NO_SUCH_TABLE
121 insert into t1 (b) values (5),(6);
122 --error ER_NO_SUCH_TABLE
123 ALTER TABLE t1 add column c int;
124 CREATE SEQUENCE s1 nocache engine=myisam;
125 ALTER TABLE t1 add column c int;
126 --error ER_NO_SUCH_TABLE
127 ALTER TABLE t1 add column d int default next value for s_not_exits;
128 drop table t1;
129 drop sequence s1;
130
131 --echo #
132 --echo # MDEV 22785 Crash with prepared statements and NEXTVAL()
133 --echo #
134 CREATE SEQUENCE s;
135 CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id));
136 PREPARE stmt FROM " INSERT INTO t1 () values ()";
137 INSERT INTO t1 () values ();
138 EXECUTE stmt;
139 # Cleanup
140 DROP TABLE t1;
141 DROP SEQUENCE s;
142
143 --echo #
144 --echo # MDEV-29540 Incorrect sequence values in INSERT SELECT
145 --echo #
146
147 CREATE SEQUENCE s1;
148 CREATE TABLE t1 (
149 a BIGINT UNSIGNED NOT NULL PRIMARY KEY
150 DEFAULT (NEXT VALUE FOR s1),
151 b CHAR(1) NOT NULL
152 );
153
154 INSERT INTO t1 (b) VALUES ('a');
155 INSERT INTO t1 (b) VALUES ('b'), ('c');
156 INSERT INTO t1 (b) VALUES ('d');
157 INSERT INTO t1 (b) SELECT c FROM (
158 SELECT 'e' as c
159 UNION
160 SELECT 'f'
161 UNION
162 SELECT 'g'
163 ) der;
164
165 SELECT a, b FROM t1;
166
167 ALTER SEQUENCE s1 RESTART;
168
169 --error ER_DUP_ENTRY
170 INSERT INTO t1 (b) SELECT c FROM (
171 SELECT 'a' as c
172 UNION
173 SELECT 'b'
174 UNION
175 SELECT 'c'
176 UNION
177 SELECT 'd'
178 UNION
179 SELECT 'e'
180 UNION
181 SELECT 'f'
182 UNION
183 SELECT 'g'
184 ) der;
185
186 ALTER SEQUENCE s1 RESTART;
187
188 INSERT IGNORE INTO t1 (b) SELECT c FROM (
189 SELECT 'a' as c
190 UNION
191 SELECT 'b'
192 UNION
193 SELECT 'c'
194 UNION
195 SELECT 'd'
196 UNION
197 SELECT 'e'
198 UNION
199 SELECT 'f'
200 UNION
201 SELECT 'g'
202 ) der;
203
204 SELECT a, b FROM t1;
205
206 INSERT IGNORE INTO t1 (b) SELECT c FROM (
207 SELECT 'h' as c
208 UNION
209 SELECT 'i'
210 UNION
211 SELECT 'j'
212 ) der;
213
214 SELECT a, b FROM t1;
215
216 DROP TABLE t1;
217 DROP SEQUENCE s1;
218
219 --echo # End of 10.3 tests
220
221 --echo # in UPDATE
222 create sequence s1 cache 0;
223 create table t1 (id int unsigned default nextval(s1));
224 insert t1 values ();
225 update t1 set id=default;
226 prepare stmt from "update t1 set id=?";
227 execute stmt using default;
228 deallocate prepare stmt;
229 drop table t1;
230 drop sequence s1;
231
232 --echo #
233 --echo # MDEV-37302 Assertion failure in Table_triggers_list::add_tables_and_routines_for_triggers upon attempt to insert DEFAULT into non-insertable view
234 --echo #
235 create table t1 (f int);
236 create algorithm=temptable view v1 as select * from t1;
237 create trigger tr before update on t1 for each row set @a=1;
238 --error ER_NON_INSERTABLE_TABLE
239 insert v1 values (default);
240 drop view v1;
241 drop table t1;
242
243 --echo #
244 --echo # MDEV-37172 Server crashes in Item_func_nextval::update_table after
245 --echo # INSERT to the table, that uses expression with nextval() as default
246 --echo #
247
248 create sequence s1;
249 create sequence s2;
250 create table t1 (id int default(nextval(s1)));
251 create table t2 (id int default(nextval(s1)+nextval(s1)));
252 create table t3 (id1 int default(nextval(s1)+nextval(s1)),
253 id2 int default(nextval(s2)));
254 insert t1 values ();
255 select lastval(s1);
256 select * from t1;
257 insert t2 values ();
258 select lastval(s1);
259 select * from t2;
260 insert t3 values ();
261 select lastval(s1), lastval(s2);
262 select * from t3;
263 drop table t1,t2,t3;
264 drop sequence s1,s2;
265
266 --echo # End of 10.6 tests