Debian Codegraph @

mysql-test/suite/vcol/inc/vcol_ins_upd.inc

raw
1 ################################################################################
2 # inc/vcol_ins_upd.inc #
3 # #
4 # Purpose: #
5 # Testing DDL operations such as INSERT, UPDATE, REPLACE and DELETE. #
6 # #
7 # #
8 # #
9 #------------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: Oleksandr Byelkin (Monty program Ab)
13 # Date: 2009-03-24
14 # Change: Syntax changed
15 ################################################################################
16
17 let $create1 = create table t1 (a int,
18 b int as (-a),
19 c int as (-a) persistent);
20 let $create2 = create table t1 (a int unique,
21 b int as (-a),
22 c int as (-a) persistent);
23 let $create3 = create table t1 (a int,
24 b int as (-a),
25 c int as (-a) persistent unique);
26 let $create4 = create table t1 (a int,
27 b int as (-a),
28 c int as (-a) persistent unique,
29 d varchar(16));
30 eval $create1;
31 set sql_warnings = 1;
32 # Prefer table scans to range
33 set @@optimizer_scan_setup_cost=0;
34
35 --echo #
36 --echo # *** INSERT ***
37 --echo #
38
39 --echo # INSERT INTO tbl_name VALUES... DEFAULT is specified against vcols
40 insert into t1 values (1,default,default);
41 select * from t1;
42 delete from t1;
43 select * from t1;
44
45 --echo # INSERT INTO tbl_name VALUES... NULL is specified against vcols
46 insert into t1 values (1,null,null);
47 select * from t1;
48 delete from t1;
49 select * from t1;
50
51 --echo # INSERT INTO tbl_name VALUES... a non-NULL value is specified against vcols
52 insert ignore into t1 values (1,2,3);
53 select * from t1;
54 delete from t1;
55 select * from t1;
56
57 --echo # INSERT INTO tbl_name (<non_vcol_list>) VALUES...
58 insert into t1 (a) values (1), (2);
59 select * from t1;
60 delete from t1;
61 select * from t1;
62
63 --echo # INSERT INTO tbl_name (<normal+vcols>) VALUES... DEFAULT is specified
64 --echo # against vcols
65 insert into t1 (a,b) values (1,default), (2,default);
66 select * from t1;
67 delete from t1;
68 select * from t1;
69
70 --echo # INSERT INTO tbl_name (<normal+vcols>) VALUES... NULL is specified against vcols
71 insert into t1 (a,b) values (1,null), (2,null);
72 select * from t1;
73 delete from t1;
74 select * from t1;
75
76 --echo # INSERT INTO tbl_name (<normal+vcols>) VALUES... a non-NULL value is specified
77 --echo # against vcols
78 insert ignore into t1 (a,b) values (1,3), (2,4);
79 select * from t1;
80 delete from t1;
81 select * from t1;
82 drop table t1;
83
84 --echo # Table with UNIQUE non-vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
85 --echo # KEY UPDATE <non_vcol>=expr, <vcol>=expr
86 eval $create2;
87 insert into t1 values (1,default,default);
88 insert into t1 values (1,default,default)
89 on duplicate key update a=2, b=default;
90 select a,b,c from t1;
91 delete from t1 where b in (1,2);
92 select * from t1;
93 drop table t1;
94
95 --echo # Table with UNIQUE vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
96 --echo # KEY UPDATE <non_vcol>=expr, <vcol>=expr
97 eval $create3;
98 insert into t1 values (1,default,default);
99 insert into t1 values (1,default,default)
100 on duplicate key update a=2, b=default;
101 select a,b,c from t1;
102
103 --echo # CREATE new_table ... LIKE old_table
104 --echo # INSERT INTO new_table SELECT * from old_table
105 create table t2 like t1;
106 insert ignore into t2 select * from t1;
107 select * from t1;
108 drop table t2;
109
110 --echo # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-vcols>, <vcols>)
111 --echo # SELECT <non-vcols>, <vcols> from old_table
112 insert into t1 values (1,default,default);
113 select * from t1;
114 create table t2 like t1;
115 insert ignore into t2 (a,b) select a,b from t1;
116 select * from t2;
117 drop table t2;
118 drop table t1;
119
120 --echo #
121 --echo # *** UPDATE ***
122 --echo #
123
124 --echo # UPDATE tbl_name SET non-vcol=expr WHERE non-vcol=expr
125 eval $create1;
126 insert into t1 (a) values (1), (2);
127 select * from t1;
128 update t1 set a=3 where a=2;
129 select * from t1;
130 delete from t1;
131 select * from t1;
132
133 --echo # UPDATE tbl_name SET vcol=expr WHERE non-vcol=expr
134 insert into t1 (a) values (1), (2);
135 select * from t1;
136 update ignore t1 set c=3 where a=2;
137 select * from t1;
138 delete from t1;
139 select * from t1;
140
141 --echo # UPDATE tbl_name SET non-vcol=expr WHERE vcol=expr
142 insert into t1 (a) values (1), (2);
143 select * from t1;
144 update t1 set a=3 where b=-2;
145 select * from t1;
146 delete from t1;
147 select * from t1;
148
149 --echo # UPDATE tbl_name SET vcol=expr WHERE vcol=expr
150 insert into t1 (a) values (1), (2);
151 select * from t1;
152 update ignore t1 set c=3 where b=-2;
153 select * from t1;
154 delete from t1;
155 select * from t1;
156 drop table t1;
157
158 --echo # INDEX created on vcol
159 --echo # UPDATE tbl_name SET non-vcol=expr WHERE vcol=const
160 eval $create3;
161 insert into t1 (a) values (1), (2);
162 select * from t1;
163 update t1 set a=3 where c=-2;
164 select * from t1;
165 delete from t1;
166 select * from t1;
167
168
169 --echo # INDEX created on vcol
170 --echo # UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
171 insert into t1 (a) values (1), (2);
172 select * from t1;
173 update t1 set a=3 where c between -3 and -2;
174 select * from t1;
175 delete from t1;
176 select * from t1;
177
178 --echo # No INDEX created on vcol
179 --echo # UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
180 insert into t1 (a) values (1), (2);
181 select * from t1;
182 update t1 set a=3 where b between -3 and -2;
183 select * from t1;
184 delete from t1;
185 select * from t1;
186
187 --echo # INDEX created on vcol
188 --echo # UPDATE tbl_name SET non-vcol=expr
189 --echo # WHERE vcol=between const1 and const2 ORDER BY vcol
190 insert into t1 (a) values (1), (2), (3), (4), (5);
191 select * from t1;
192 update t1 set a=6 where c between -1 and 0
193 order by c;
194 select * from t1;
195 delete from t1 where c between -6 and 0;
196 select * from t1;
197
198 --echo # INDEX created on vcol
199 --echo # UPDATE tbl_name SET non-vcol=expr
200 --echo # WHERE vcol=between const1 and const2 ORDER BY vcol LIMIT 2
201 insert into t1 (a) values (1), (2), (3), (4), (5);
202
203 select * from t1;
204 update t1 set a=6 where c between -1 and 0
205 order by c limit 2;
206 select * from t1;
207 delete from t1 where c between -2 and 0 order by c;
208 select * from t1;
209 delete from t1;
210
211 --echo # INDEX created on vcol
212 --echo # UPDATE tbl_name SET non-vcol=expr
213 --echo # WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
214 insert into t1 (a) values (1), (2), (3), (4), (5);
215 select * from t1;
216 update t1 set a=6 where (c between -2 and 0) and (b=-1);
217 select * from t1;
218 delete from t1;
219
220 --echo # INDEX created on vcol
221 --echo # UPDATE tbl_name SET non-vcol=expr
222 --echo # WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
223 --echo # ORDER BY indexed vcol
224 insert into t1 (a) values (1), (2), (3), (4), (5);
225 select * from t1;
226 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
227 select * from t1;
228 delete from t1;
229 drop table t1;
230
231 let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`;
232 if ($innodb_engine)
233 {
234 --echo #
235 --echo # Verify ON UPDATE/DELETE actions of FOREIGN KEYs
236 create table t2 (a int primary key, name varchar(10));
237 create table t1 (a int primary key, b int as (a % 10) persistent);
238 insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
239 insert into t1 (a) values (1),(2),(3);
240 select * from t1;
241 select * from t2;
242 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
243
244 --echo # - ON UPDATE RESTRICT
245 alter table t1 add foreign key (b) references t2(a) on update restrict;
246 --error ER_NO_REFERENCED_ROW_2
247 insert into t1 (a) values (4);
248 --error ER_ROW_IS_REFERENCED_2
249 update t2 set a=4 where a=3;
250 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
251 alter table t1 drop foreign key t1_ibfk_1;
252
253 --echo # - ON DELETE RESTRICT
254 alter table t1 add foreign key (b) references t2(a) on delete restrict;
255 --error ER_ROW_IS_REFERENCED_2
256 delete from t2 where a=3;
257 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
258 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
259 alter table t1 drop foreign key t1_ibfk_1;
260
261 --echo # - ON DELETE CASCADE
262 alter table t1 add foreign key (b) references t2(a) on delete cascade;
263 delete from t2 where a=3;
264 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
265 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
266 alter table t1 drop foreign key t1_ibfk_1;
267
268 drop table t1;
269 drop table t2;
270 }
271
272 --echo #
273 --echo # *** REPLACE ***
274 --echo #
275
276 --echo # UNIQUE INDEX on vcol
277 --echo # REPLACE tbl_name (non-vcols) VALUES (non-vcols);
278 eval $create4;
279 insert into t1 (a,d) values (1,'a'), (2,'b');
280 select * from t1;
281 replace t1 (a,d) values (1,'c');
282 select * from t1;
283 delete from t1;
284 select * from t1;
285
286
287 # *** DELETE
288 # All required tests for DELETE are performed as part of the above testing
289 # for INSERT, UPDATE and REPLACE.
290
291 set sql_warnings = 0;
292 drop table t1;
293
294 --echo #
295 --echo # MDEV-9093: Persistent computed column is not updated when
296 --echo # update query contains join
297 --echo #
298
299 CREATE TABLE `t1` (
300 `id` bigint(20) NOT NULL,
301 `name` varchar(254) DEFAULT NULL,
302 `name_hash` varchar(64) AS (sha1(name)) PERSISTENT,
303 PRIMARY KEY (`id`)
304 );
305
306 insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
307
308 create table t2 (id bigint);
309 insert into t2 values (2050),(2051),(2041);
310
311 select * from t1;
312
313 update t1 join t2 using(id) set name = concat(name,
314 '+1') where t1.id in (2051,2041);
315
316 select * from t1;
317
318 drop table t1,t2;
319
320 --echo #
321 --echo # Test error handling with virtual columns
322 --echo #
323
324 CREATE TABLE IF NOT EXISTS t1 (
325 f1 DOUBLE,
326 f2 DOUBLE NOT NULL DEFAULT '0',
327 f3 DOUBLE,
328 f4 DOUBLE NOT NULL DEFAULT '0',
329 v1 DOUBLE AS ( ( f1 DIV ( f1 ) ) <= f2 ) VIRTUAL,
330 v2 DOUBLE AS ( ( f2 DIV ( f2 ) ) <= f2 ) VIRTUAL,
331 KEY (v2)
332 );
333
334 set sql_mode='strict_all_tables,error_for_division_by_zero';
335 --error ER_DIVISION_BY_ZERO
336 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
337 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
338 --error ER_DIVISION_BY_ZERO
339 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
340 INSERT IGNORE INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
341 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
342 select v1 from t1;
343
344 --error ER_DIVISION_BY_ZERO
345 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
346 INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
347 --error ER_DIVISION_BY_ZERO
348 INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
349 INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
350
351 --error ER_DIVISION_BY_ZERO
352 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
353 select count(*) from t1;
354 DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
355 select count(*) from t1;
356 select * from t1;
357 --error ER_BAD_NULL_ERROR
358 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
359 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1 where f2 !=0;
360 UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
361 SELECT * FROM t1;
362 TRUNCATE TABLE t1;
363
364 set sql_mode='error_for_division_by_zero';
365 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
366 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
367 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
368 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
369 select v1 from t1;
370
371 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
372 INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
373 INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
374 INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
375
376 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
377 select count(*) from t1;
378 DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
379 select count(*) from t1;
380 select * from t1;
381 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
382 UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
383 drop table t1;
384 set sql_mode=@@global.sql_mode;