Debian Codegraph @

mysql-test/suite/vcol/r/vcol_ins_upd_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int,
3 b int as (-a),
4 c int as (-a) persistent);
5 set sql_warnings = 1;
6 set @@optimizer_scan_setup_cost=0;
7 #
8 # *** INSERT ***
9 #
10 # INSERT INTO tbl_name VALUES... DEFAULT is specified against vcols
11 insert into t1 values (1,default,default);
12 select * from t1;
13 a b c
14 1 -1 -1
15 delete from t1;
16 select * from t1;
17 a b c
18 # INSERT INTO tbl_name VALUES... NULL is specified against vcols
19 insert into t1 values (1,null,null);
20 select * from t1;
21 a b c
22 1 -1 -1
23 delete from t1;
24 select * from t1;
25 a b c
26 # INSERT INTO tbl_name VALUES... a non-NULL value is specified against vcols
27 insert ignore into t1 values (1,2,3);
28 Warnings:
29 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
30 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
31 select * from t1;
32 a b c
33 1 -1 -1
34 delete from t1;
35 select * from t1;
36 a b c
37 # INSERT INTO tbl_name (<non_vcol_list>) VALUES...
38 insert into t1 (a) values (1), (2);
39 select * from t1;
40 a b c
41 1 -1 -1
42 2 -2 -2
43 delete from t1;
44 select * from t1;
45 a b c
46 # INSERT INTO tbl_name (<normal+vcols>) VALUES... DEFAULT is specified
47 # against vcols
48 insert into t1 (a,b) values (1,default), (2,default);
49 select * from t1;
50 a b c
51 1 -1 -1
52 2 -2 -2
53 delete from t1;
54 select * from t1;
55 a b c
56 # INSERT INTO tbl_name (<normal+vcols>) VALUES... NULL is specified against vcols
57 insert into t1 (a,b) values (1,null), (2,null);
58 select * from t1;
59 a b c
60 1 -1 -1
61 2 -2 -2
62 delete from t1;
63 select * from t1;
64 a b c
65 # INSERT INTO tbl_name (<normal+vcols>) VALUES... a non-NULL value is specified
66 # against vcols
67 insert ignore into t1 (a,b) values (1,3), (2,4);
68 Warnings:
69 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
70 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
71 select * from t1;
72 a b c
73 1 -1 -1
74 2 -2 -2
75 delete from t1;
76 select * from t1;
77 a b c
78 drop table t1;
79 # Table with UNIQUE non-vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
80 # KEY UPDATE <non_vcol>=expr, <vcol>=expr
81 create table t1 (a int unique,
82 b int as (-a),
83 c int as (-a) persistent);
84 insert into t1 values (1,default,default);
85 insert into t1 values (1,default,default)
86 on duplicate key update a=2, b=default;
87 select a,b,c from t1;
88 a b c
89 2 -2 -2
90 delete from t1 where b in (1,2);
91 select * from t1;
92 a b c
93 2 -2 -2
94 drop table t1;
95 # Table with UNIQUE vcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
96 # KEY UPDATE <non_vcol>=expr, <vcol>=expr
97 create table t1 (a int,
98 b int as (-a),
99 c int as (-a) persistent unique);
100 insert into t1 values (1,default,default);
101 insert into t1 values (1,default,default)
102 on duplicate key update a=2, b=default;
103 select a,b,c from t1;
104 a b c
105 2 -2 -2
106 # CREATE new_table ... LIKE old_table
107 # INSERT INTO new_table SELECT * from old_table
108 create table t2 like t1;
109 insert ignore into t2 select * from t1;
110 Warnings:
111 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
112 Warning 1906 The value specified for generated column 'c' in table 't2' has been ignored
113 select * from t1;
114 a b c
115 2 -2 -2
116 drop table t2;
117 # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-vcols>, <vcols>)
118 # SELECT <non-vcols>, <vcols> from old_table
119 insert into t1 values (1,default,default);
120 select * from t1;
121 a b c
122 2 -2 -2
123 1 -1 -1
124 create table t2 like t1;
125 insert ignore into t2 (a,b) select a,b from t1;
126 Warnings:
127 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
128 Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored
129 select * from t2;
130 a b c
131 2 -2 -2
132 1 -1 -1
133 drop table t2;
134 drop table t1;
135 #
136 # *** UPDATE ***
137 #
138 # UPDATE tbl_name SET non-vcol=expr WHERE non-vcol=expr
139 create table t1 (a int,
140 b int as (-a),
141 c int as (-a) persistent);
142 insert into t1 (a) values (1), (2);
143 select * from t1;
144 a b c
145 1 -1 -1
146 2 -2 -2
147 update t1 set a=3 where a=2;
148 select * from t1;
149 a b c
150 1 -1 -1
151 3 -3 -3
152 delete from t1;
153 select * from t1;
154 a b c
155 # UPDATE tbl_name SET vcol=expr WHERE non-vcol=expr
156 insert into t1 (a) values (1), (2);
157 select * from t1;
158 a b c
159 1 -1 -1
160 2 -2 -2
161 update ignore t1 set c=3 where a=2;
162 Warnings:
163 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
164 select * from t1;
165 a b c
166 1 -1 -1
167 2 -2 -2
168 delete from t1;
169 select * from t1;
170 a b c
171 # UPDATE tbl_name SET non-vcol=expr WHERE vcol=expr
172 insert into t1 (a) values (1), (2);
173 select * from t1;
174 a b c
175 1 -1 -1
176 2 -2 -2
177 update t1 set a=3 where b=-2;
178 select * from t1;
179 a b c
180 1 -1 -1
181 3 -3 -3
182 delete from t1;
183 select * from t1;
184 a b c
185 # UPDATE tbl_name SET vcol=expr WHERE vcol=expr
186 insert into t1 (a) values (1), (2);
187 select * from t1;
188 a b c
189 1 -1 -1
190 2 -2 -2
191 update ignore t1 set c=3 where b=-2;
192 Warnings:
193 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
194 select * from t1;
195 a b c
196 1 -1 -1
197 2 -2 -2
198 delete from t1;
199 select * from t1;
200 a b c
201 drop table t1;
202 # INDEX created on vcol
203 # UPDATE tbl_name SET non-vcol=expr WHERE vcol=const
204 create table t1 (a int,
205 b int as (-a),
206 c int as (-a) persistent unique);
207 insert into t1 (a) values (1), (2);
208 select * from t1;
209 a b c
210 1 -1 -1
211 2 -2 -2
212 update t1 set a=3 where c=-2;
213 select * from t1;
214 a b c
215 1 -1 -1
216 3 -3 -3
217 delete from t1;
218 select * from t1;
219 a b c
220 # INDEX created on vcol
221 # UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
222 insert into t1 (a) values (1), (2);
223 select * from t1;
224 a b c
225 1 -1 -1
226 2 -2 -2
227 update t1 set a=3 where c between -3 and -2;
228 select * from t1;
229 a b c
230 1 -1 -1
231 3 -3 -3
232 delete from t1;
233 select * from t1;
234 a b c
235 # No INDEX created on vcol
236 # UPDATE tbl_name SET non-vcol=expr WHERE vcol=between const1 and const2
237 insert into t1 (a) values (1), (2);
238 select * from t1;
239 a b c
240 1 -1 -1
241 2 -2 -2
242 update t1 set a=3 where b between -3 and -2;
243 select * from t1;
244 a b c
245 1 -1 -1
246 3 -3 -3
247 delete from t1;
248 select * from t1;
249 a b c
250 # INDEX created on vcol
251 # UPDATE tbl_name SET non-vcol=expr
252 # WHERE vcol=between const1 and const2 ORDER BY vcol
253 insert into t1 (a) values (1), (2), (3), (4), (5);
254 select * from t1;
255 a b c
256 1 -1 -1
257 2 -2 -2
258 3 -3 -3
259 4 -4 -4
260 5 -5 -5
261 update t1 set a=6 where c between -1 and 0
262 order by c;
263 select * from t1;
264 a b c
265 6 -6 -6
266 2 -2 -2
267 3 -3 -3
268 4 -4 -4
269 5 -5 -5
270 delete from t1 where c between -6 and 0;
271 select * from t1;
272 a b c
273 # INDEX created on vcol
274 # UPDATE tbl_name SET non-vcol=expr
275 # WHERE vcol=between const1 and const2 ORDER BY vcol LIMIT 2
276 insert into t1 (a) values (1), (2), (3), (4), (5);
277 select * from t1;
278 a b c
279 5 -5 -5
280 4 -4 -4
281 3 -3 -3
282 2 -2 -2
283 1 -1 -1
284 update t1 set a=6 where c between -1 and 0
285 order by c limit 2;
286 select * from t1;
287 a b c
288 5 -5 -5
289 4 -4 -4
290 3 -3 -3
291 2 -2 -2
292 6 -6 -6
293 delete from t1 where c between -2 and 0 order by c;
294 select * from t1;
295 a b c
296 5 -5 -5
297 4 -4 -4
298 3 -3 -3
299 6 -6 -6
300 delete from t1;
301 # INDEX created on vcol
302 # UPDATE tbl_name SET non-vcol=expr
303 # WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
304 insert into t1 (a) values (1), (2), (3), (4), (5);
305 select * from t1;
306 a b c
307 1 -1 -1
308 2 -2 -2
309 3 -3 -3
310 4 -4 -4
311 5 -5 -5
312 update t1 set a=6 where (c between -2 and 0) and (b=-1);
313 select * from t1;
314 a b c
315 6 -6 -6
316 2 -2 -2
317 3 -3 -3
318 4 -4 -4
319 5 -5 -5
320 delete from t1;
321 # INDEX created on vcol
322 # UPDATE tbl_name SET non-vcol=expr
323 # WHERE indexed vcol=between const1 and const2 and non-indexed vcol=const3
324 # ORDER BY indexed vcol
325 insert into t1 (a) values (1), (2), (3), (4), (5);
326 select * from t1;
327 a b c
328 1 -1 -1
329 2 -2 -2
330 3 -3 -3
331 4 -4 -4
332 5 -5 -5
333 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
334 select * from t1;
335 a b c
336 6 -6 -6
337 2 -2 -2
338 3 -3 -3
339 4 -4 -4
340 5 -5 -5
341 delete from t1;
342 drop table t1;
343 #
344 # *** REPLACE ***
345 #
346 # UNIQUE INDEX on vcol
347 # REPLACE tbl_name (non-vcols) VALUES (non-vcols);
348 create table t1 (a int,
349 b int as (-a),
350 c int as (-a) persistent unique,
351 d varchar(16));
352 insert into t1 (a,d) values (1,'a'), (2,'b');
353 select * from t1;
354 a b c d
355 1 -1 -1 a
356 2 -2 -2 b
357 replace t1 (a,d) values (1,'c');
358 select * from t1;
359 a b c d
360 1 -1 -1 c
361 2 -2 -2 b
362 delete from t1;
363 select * from t1;
364 a b c d
365 set sql_warnings = 0;
366 drop table t1;
367 #
368 # MDEV-9093: Persistent computed column is not updated when
369 # update query contains join
370 #
371 CREATE TABLE `t1` (
372 `id` bigint(20) NOT NULL,
373 `name` varchar(254) DEFAULT NULL,
374 `name_hash` varchar(64) AS (sha1(name)) PERSISTENT,
375 PRIMARY KEY (`id`)
376 );
377 insert into t1(id,name) values (2050, 'name1'),(2051, 'name2'),(2041, 'name3');
378 create table t2 (id bigint);
379 insert into t2 values (2050),(2051),(2041);
380 select * from t1;
381 id name name_hash
382 2050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da
383 2051 name2 39ea84acf1fef629fef18a9c6f5799bba32ecc25
384 2041 name3 1aefcd1b0f39da45fa1fd7236f683c907c15ef82
385 update t1 join t2 using(id) set name = concat(name,
386 '+1') where t1.id in (2051,2041);
387 select * from t1;
388 id name name_hash
389 2050 name1 9b46b0dd3a8083c070c3b9953bb5f3f95c5ab4da
390 2051 name2+1 fd4f236320db3956a5ec073c5ec39707d7f05708
391 2041 name3+1 93c9096df48221428de46e146abc9f4f94bf7d2e
392 drop table t1,t2;
393 #
394 # Test error handling with virtual columns
395 #
396 CREATE TABLE IF NOT EXISTS t1 (
397 f1 DOUBLE,
398 f2 DOUBLE NOT NULL DEFAULT '0',
399 f3 DOUBLE,
400 f4 DOUBLE NOT NULL DEFAULT '0',
401 v1 DOUBLE AS ( ( f1 DIV ( f1 ) ) <= f2 ) VIRTUAL,
402 v2 DOUBLE AS ( ( f2 DIV ( f2 ) ) <= f2 ) VIRTUAL,
403 KEY (v2)
404 );
405 set sql_mode='strict_all_tables,error_for_division_by_zero';
406 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
407 ERROR 22012: Division by 0
408 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
409 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
410 ERROR 22012: Division by 0
411 INSERT IGNORE INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
412 Warnings:
413 Warning 1365 Division by 0
414 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
415 select v1 from t1;
416 v1
417 1
418 0
419 NULL
420 Warnings:
421 Warning 1365 Division by 0
422 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
423 ERROR 22012: Division by 0
424 INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
425 INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
426 ERROR 22012: Division by 0
427 INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
428 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
429 ERROR 22012: Division by 0
430 select count(*) from t1;
431 count(*)
432 13
433 DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
434 Warnings:
435 Warning 1365 Division by 0
436 Warning 1365 Division by 0
437 select count(*) from t1;
438 count(*)
439 8
440 select * from t1;
441 f1 f2 f3 f4 v1 v2
442 1 1 1 1 1 1
443 1 0 1 1 0 NULL
444 5 5 5 5 1 1
445 6 6 0 0 1 1
446 1 1 1 1 1 1
447 1 1 1 1 1 1
448 1 1 1 1 1 1
449 5 5 5 5 1 1
450 Warnings:
451 Warning 1365 Division by 0
452 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
453 ERROR 23000: Column 'f2' cannot be null
454 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1 where f2 !=0;
455 Warnings:
456 Warning 1365 Division by 0
457 UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
458 Warnings:
459 Warning 1365 Division by 0
460 SELECT * FROM t1;
461 f1 f2 f3 f4 v1 v2
462 1 1 1 1 1 1
463 1 0 1 1 0 NULL
464 1 1 10 10 1 1
465 1 1 10 10 1 1
466 1 1 10 10 1 1
467 5 5 5 5 1 1
468 6 6 0 0 1 1
469 1 1 1 1 1 1
470 1 1 1 1 1 1
471 1 1 1 1 1 1
472 1 1 10 10 1 1
473 1 1 10 10 1 1
474 5 5 5 5 1 1
475 1 1 10 10 1 1
476 1 1 10 10 1 1
477 1 1 10 10 1 1
478 1 1 10 10 1 1
479 Warnings:
480 Warning 1365 Division by 0
481 TRUNCATE TABLE t1;
482 set sql_mode='error_for_division_by_zero';
483 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 0, 0, 0);
484 Warnings:
485 Warning 1365 Division by 0
486 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 1, 1, 1);
487 INSERT INTO t1 (f1, f2, f3, f4) VALUES (1, 0, 1, 1);
488 Warnings:
489 Warning 1365 Division by 0
490 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0, 1, 1, 1);
491 select v1 from t1;
492 v1
493 NULL
494 1
495 0
496 NULL
497 Warnings:
498 Warning 1365 Division by 0
499 Warning 1365 Division by 0
500 INSERT INTO t1 (f1, f2, f3, f4) VALUES (0,0,0,0), (2,2,2,2);
501 Warnings:
502 Warning 1365 Division by 0
503 INSERT INTO t1 (f1, f2, f3, f4) VALUES (3,3,3,3), (4,4,4,4);
504 INSERT INTO t1 (f1, f2, f3, f4) VALUES (5,5,5,5), (1,0,0,0);
505 Warnings:
506 Warning 1365 Division by 0
507 INSERT INTO t1 (f1, f2, f3, f4) VALUES (6,6,0,0);
508 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT f3, f4, f3, f4 FROM t1;
509 Warnings:
510 Warning 1365 Division by 0
511 Warning 1365 Division by 0
512 Warning 1365 Division by 0
513 Warning 1365 Division by 0
514 select count(*) from t1;
515 count(*)
516 22
517 DELETE FROM t1 WHERE v2 != f1 and f1 < 5;
518 Warnings:
519 Warning 1365 Division by 0
520 Warning 1365 Division by 0
521 Warning 1365 Division by 0
522 Warning 1365 Division by 0
523 Warning 1365 Division by 0
524 Warning 1365 Division by 0
525 Warning 1365 Division by 0
526 Warning 1365 Division by 0
527 Warning 1365 Division by 0
528 Warning 1365 Division by 0
529 Warning 1365 Division by 0
530 Warning 1365 Division by 0
531 Warning 1365 Division by 0
532 Warning 1365 Division by 0
533 Warning 1365 Division by 0
534 Warning 1365 Division by 0
535 select count(*) from t1;
536 count(*)
537 15
538 select * from t1;
539 f1 f2 f3 f4 v1 v2
540 0 0 0 0 NULL NULL
541 1 1 1 1 1 1
542 1 0 1 1 0 NULL
543 0 0 0 0 NULL NULL
544 5 5 5 5 1 1
545 1 0 0 0 0 NULL
546 6 6 0 0 1 1
547 0 0 0 0 NULL NULL
548 1 1 1 1 1 1
549 1 1 1 1 1 1
550 1 1 1 1 1 1
551 0 0 0 0 NULL NULL
552 5 5 5 5 1 1
553 0 0 0 0 NULL NULL
554 0 0 0 0 NULL NULL
555 Warnings:
556 Warning 1365 Division by 0
557 Warning 1365 Division by 0
558 Warning 1365 Division by 0
559 Warning 1365 Division by 0
560 Warning 1365 Division by 0
561 Warning 1365 Division by 0
562 Warning 1365 Division by 0
563 Warning 1365 Division by 0
564 Warning 1365 Division by 0
565 Warning 1365 Division by 0
566 Warning 1365 Division by 0
567 Warning 1365 Division by 0
568 Warning 1365 Division by 0
569 Warning 1365 Division by 0
570 INSERT INTO t1 ( f1, f2, f3, f4 ) SELECT v1, v2, 10,10 FROM t1;
571 Warnings:
572 Warning 1365 Division by 0
573 Warning 1365 Division by 0
574 Warning 1365 Division by 0
575 Warning 1365 Division by 0
576 Warning 1365 Division by 0
577 Warning 1365 Division by 0
578 Warning 1365 Division by 0
579 Warning 1365 Division by 0
580 Warning 1365 Division by 0
581 Warning 1365 Division by 0
582 Warning 1365 Division by 0
583 Warning 1365 Division by 0
584 Warning 1365 Division by 0
585 Warning 1365 Division by 0
586 Warning 1048 Column 'f2' cannot be null
587 Warning 1365 Division by 0
588 Warning 1048 Column 'f2' cannot be null
589 Warning 1365 Division by 0
590 Warning 1048 Column 'f2' cannot be null
591 Warning 1365 Division by 0
592 Warning 1048 Column 'f2' cannot be null
593 Warning 1365 Division by 0
594 Warning 1048 Column 'f2' cannot be null
595 Warning 1365 Division by 0
596 Warning 1048 Column 'f2' cannot be null
597 Warning 1365 Division by 0
598 Warning 1048 Column 'f2' cannot be null
599 Warning 1365 Division by 0
600 Warning 1048 Column 'f2' cannot be null
601 Warning 1365 Division by 0
602 UPDATE t1 SET f3 = v1 WHERE f2 = 2 AND v2 is null;
603 Warnings:
604 Warning 1365 Division by 0
605 Warning 1365 Division by 0
606 Warning 1365 Division by 0
607 Warning 1365 Division by 0
608 Warning 1365 Division by 0
609 Warning 1365 Division by 0
610 Warning 1365 Division by 0
611 Warning 1365 Division by 0
612 Warning 1365 Division by 0
613 Warning 1365 Division by 0
614 Warning 1365 Division by 0
615 Warning 1365 Division by 0
616 Warning 1365 Division by 0
617 Warning 1365 Division by 0
618 Warning 1365 Division by 0
619 Warning 1365 Division by 0
620 Warning 1365 Division by 0
621 Warning 1365 Division by 0
622 Warning 1365 Division by 0
623 Warning 1365 Division by 0
624 Warning 1365 Division by 0
625 Warning 1365 Division by 0
626 Warning 1365 Division by 0
627 Warning 1365 Division by 0
628 drop table t1;
629 set sql_mode=@@global.sql_mode;