Debian Codegraph @

mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int,
3 b int generated always as (-a) virtual,
4 c int generated always as (-a) stored);
5 set sql_warnings = 1;
6 #
7 # *** INSERT ***
8 #
9 # INSERT INTO tbl_name VALUES... DEFAULT is specified against gcols
10 insert into t1 values (1,default,default);
11 select * from t1;
12 a b c
13 1 -1 -1
14 delete from t1;
15 select * from t1;
16 a b c
17 # INSERT INTO tbl_name VALUES... NULL is specified against gcols
18 insert into t1 values (1,null,null);
19 select * from t1;
20 a b c
21 1 -1 -1
22 delete from t1;
23 select * from t1;
24 a b c
25 # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
26 insert ignore into t1 values (1,2,3);
27 Warnings:
28 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
29 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
30 select * from t1;
31 a b c
32 1 -1 -1
33 delete from t1;
34 select * from t1;
35 a b c
36 # INSERT INTO tbl_name (<non_gcol_list>) VALUES...
37 insert into t1 (a) values (1), (2);
38 select * from t1 order by a;
39 a b c
40 1 -1 -1
41 2 -2 -2
42 delete from t1;
43 select * from t1;
44 a b c
45 # INSERT INTO tbl_name (<normal+gcols>) VALUES... DEFAULT is specified
46 # against gcols
47 insert into t1 (a,b) values (1,default), (2,default);
48 select * from t1 order by a;
49 a b c
50 1 -1 -1
51 2 -2 -2
52 delete from t1;
53 select * from t1;
54 a b c
55 # INSERT INTO tbl_name (<normal+gcols>) VALUES... NULL is specified against gcols
56 insert into t1 (a,b) values (1,null), (2,null);
57 select * from t1;
58 a b c
59 1 -1 -1
60 2 -2 -2
61 delete from t1;
62 select * from t1;
63 a b c
64 # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
65 # against gcols
66 insert ignore into t1 (a,b) values (1,3), (2,4);
67 Warnings:
68 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
69 Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored
70 select * from t1;
71 a b c
72 1 -1 -1
73 2 -2 -2
74 delete from t1;
75 select * from t1;
76 a b c
77 drop table t1;
78 # Table with UNIQUE non-gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
79 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
80 create table t1 (a int unique,
81 b int generated always as (-a) virtual,
82 c int generated always as (-a) stored);
83 insert into t1 values (1,default,default);
84 insert into t1 values (1,default,default)
85 on duplicate key update a=2, b=default;
86 select a,b,c from t1;
87 a b c
88 2 -2 -2
89 delete from t1 where b in (1,2);
90 select * from t1;
91 a b c
92 2 -2 -2
93 drop table t1;
94 # Table with UNIQUE gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
95 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
96 create table t1 (a int,
97 b int generated always as (-a) virtual,
98 c int generated always as (-a) stored unique);
99 insert into t1 values (1,default,default);
100 insert into t1 values (1,default,default)
101 on duplicate key update a=2, b=default;
102 select a,b,c from t1;
103 a b c
104 2 -2 -2
105 # CREATE new_table ... LIKE old_table
106 # INSERT INTO new_table SELECT * from old_table
107 create table t2 like t1;
108 insert into t2(a) select a from t1;
109 select * from t2;
110 a b c
111 2 -2 -2
112 drop table t2;
113 # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-gcols>, <gcols>)
114 # SELECT <non-gcols>, <gcols> from old_table
115 insert into t1 values (1,default,default);
116 select * from t1;
117 a b c
118 2 -2 -2
119 1 -1 -1
120 create table t2 like t1;
121 insert into t2 (a) select a from t1;
122 select * from t2 order by a;
123 a b c
124 1 -1 -1
125 2 -2 -2
126 drop table t2;
127 drop table t1;
128 #
129 # *** UPDATE ***
130 #
131 # UPDATE tbl_name SET non-gcol=expr WHERE non-gcol=expr
132 create table t1 (a int,
133 b int generated always as (-a) virtual,
134 c int generated always as (-a) stored);
135 insert into t1 (a) values (1), (2);
136 select * from t1 order by a;
137 a b c
138 1 -1 -1
139 2 -2 -2
140 update t1 set a=3 where a=2;
141 select * from t1 order by a;
142 a b c
143 1 -1 -1
144 3 -3 -3
145 delete from t1;
146 select * from t1;
147 a b c
148 # UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
149 insert into t1 (a) values (1), (2);
150 select * from t1 order by a;
151 a b c
152 1 -1 -1
153 2 -2 -2
154 update ignore t1 set c=3 where a=2;
155 Warnings:
156 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
157 select * from t1 order by a;
158 a b c
159 1 -1 -1
160 2 -2 -2
161 delete from t1;
162 select * from t1;
163 a b c
164 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=expr
165 insert into t1 (a) values (1), (2);
166 select * from t1 order by a;
167 a b c
168 1 -1 -1
169 2 -2 -2
170 update t1 set a=3 where b=-2;
171 select * from t1 order by a;
172 a b c
173 1 -1 -1
174 3 -3 -3
175 delete from t1;
176 select * from t1;
177 a b c
178 # UPDATE tbl_name SET gcol=expr WHERE gcol=expr
179 insert into t1 (a) values (1), (2);
180 select * from t1 order by a;
181 a b c
182 1 -1 -1
183 2 -2 -2
184 update ignore t1 set c=3 where b=-2;
185 Warnings:
186 Warning 1906 The value specified for generated column 'c' in table 't1' has been ignored
187 select * from t1 order by a;
188 a b c
189 1 -1 -1
190 2 -2 -2
191 delete from t1;
192 select * from t1;
193 a b c
194 drop table t1;
195 # INDEX created on gcol
196 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=const
197 create table t1 (a int,
198 b int generated always as (-a) virtual,
199 c int generated always as (-a) stored unique);
200 insert into t1 (a) values (1), (2);
201 select * from t1 order by a;
202 a b c
203 1 -1 -1
204 2 -2 -2
205 update t1 set a=3 where c=-2;
206 select * from t1;
207 a b c
208 1 -1 -1
209 3 -3 -3
210 delete from t1;
211 select * from t1;
212 a b c
213 # INDEX created on gcol
214 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
215 insert into t1 (a) values (1), (2);
216 select * from t1 order by a;
217 a b c
218 1 -1 -1
219 2 -2 -2
220 update t1 set a=3 where c between -3 and -2;
221 select * from t1 order by a;
222 a b c
223 1 -1 -1
224 3 -3 -3
225 delete from t1;
226 select * from t1;
227 a b c
228 # No INDEX created on gcol
229 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
230 insert into t1 (a) values (1), (2);
231 select * from t1 order by a;
232 a b c
233 1 -1 -1
234 2 -2 -2
235 update t1 set a=3 where b between -3 and -2;
236 select * from t1 order by a;
237 a b c
238 1 -1 -1
239 3 -3 -3
240 delete from t1;
241 select * from t1;
242 a b c
243 # INDEX created on gcol
244 # UPDATE tbl_name SET non-gcol=expr
245 # WHERE gcol=between const1 and const2 ORDER BY gcol
246 insert into t1 (a) values (1), (2), (3), (4), (5);
247 select * from t1 order by a;
248 a b c
249 1 -1 -1
250 2 -2 -2
251 3 -3 -3
252 4 -4 -4
253 5 -5 -5
254 update t1 set a=6 where c between -1 and 0
255 order by c;
256 select * from t1 order by a;
257 a b c
258 2 -2 -2
259 3 -3 -3
260 4 -4 -4
261 5 -5 -5
262 6 -6 -6
263 delete from t1 where c between -6 and 0;
264 select * from t1;
265 a b c
266 # INDEX created on gcol
267 # UPDATE tbl_name SET non-gcol=expr
268 # WHERE gcol=between const1 and const2 ORDER BY gcol LIMIT 2
269 insert into t1 (a) values (1), (2), (3), (4), (5);
270 select * from t1 order by a;
271 a b c
272 1 -1 -1
273 2 -2 -2
274 3 -3 -3
275 4 -4 -4
276 5 -5 -5
277 update t1 set a=6 where c between -1 and 0
278 order by c limit 2;
279 select * from t1 order by a;
280 a b c
281 2 -2 -2
282 3 -3 -3
283 4 -4 -4
284 5 -5 -5
285 6 -6 -6
286 delete from t1 where c between -2 and 0 order by c;
287 select * from t1 order by a;
288 a b c
289 3 -3 -3
290 4 -4 -4
291 5 -5 -5
292 6 -6 -6
293 delete from t1;
294 # INDEX created on gcol
295 # UPDATE tbl_name SET non-gcol=expr
296 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
297 insert into t1 (a) values (1), (2), (3), (4), (5);
298 select * from t1 order by a;
299 a b c
300 1 -1 -1
301 2 -2 -2
302 3 -3 -3
303 4 -4 -4
304 5 -5 -5
305 update t1 set a=6 where (c between -2 and 0) and (b=-1);
306 select * from t1 order by a;
307 a b c
308 2 -2 -2
309 3 -3 -3
310 4 -4 -4
311 5 -5 -5
312 6 -6 -6
313 delete from t1;
314 # INDEX created on gcol
315 # UPDATE tbl_name SET non-gcol=expr
316 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
317 # ORDER BY indexed gcol
318 insert into t1 (a) values (1), (2), (3), (4), (5);
319 select * from t1 order by a;
320 a b c
321 1 -1 -1
322 2 -2 -2
323 3 -3 -3
324 4 -4 -4
325 5 -5 -5
326 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
327 select * from t1 order by a;
328 a b c
329 2 -2 -2
330 3 -3 -3
331 4 -4 -4
332 5 -5 -5
333 6 -6 -6
334 delete from t1;
335 drop table t1;
336 #
337 # *** REPLACE ***
338 #
339 # UNIQUE INDEX on gcol
340 # REPLACE tbl_name (non-gcols) VALUES (non-gcols);
341 create table t1 (a int,
342 b int generated always as (-a) virtual,
343 c int generated always as (-a) stored unique,
344 d varchar(16));
345 insert into t1 (a,d) values (1,'a'), (2,'b');
346 select * from t1 order by a;
347 a b c d
348 1 -1 -1 a
349 2 -2 -2 b
350 replace t1 (a,d) values (1,'c');
351 select * from t1 order by a;
352 a b c d
353 1 -1 -1 c
354 2 -2 -2 b
355 delete from t1;
356 select * from t1;
357 a b c d
358 set sql_warnings = 0;
359 drop table t1;
360 Bug#20797344: WL#8149: ALLOCATED SPACE FOR INDEXED BLOB VGC CAN BE
361 OVERWRITTEN FOR UPDATE
362 #
363 CREATE TABLE t (a varchar(100), b blob,
364 c blob GENERATED ALWAYS AS (concat(a,b)) VIRTUAL,
365 d blob GENERATED ALWAYS AS (b) VIRTUAL,
366 e int(11) GENERATED ALWAYS AS (10) VIRTUAL,
367 h int(11) NOT NULL, PRIMARY KEY (h), key(c(20)));
368 INSERT INTO t(a,b,h) VALUES('aaaaaaa','1111111', 11);
369 INSERT INTO t(a,b,h) VALUES('bbbbbbb','2222222', 22);
370 SELECT c FROM t;
371 c
372 aaaaaaa1111111
373 bbbbbbb2222222
374 UPDATE t SET a='ccccccc';
375 SELECT c FROM t;
376 c
377 ccccccc1111111
378 ccccccc2222222
379 DROP TABLE t;
380 # Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
381 # BITMAP_IS_SET(TABLE->WRITE_SET
382 #
383 CREATE TABLE b (
384 pk INTEGER AUTO_INCREMENT,
385 col_varchar_nokey VARCHAR(1),
386 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
387 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
388 PRIMARY KEY (pk)
389 );
390 INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
391 CREATE TABLE d (
392 pk INTEGER AUTO_INCREMENT,
393 col_varchar_nokey VARCHAR(1),
394 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
395 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
396 PRIMARY KEY (pk)
397 ) ;
398 INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
399 CREATE TABLE bb (
400 pk INTEGER AUTO_INCREMENT,
401 col_varchar_nokey VARCHAR(1) /*! NULL */,
402 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
403 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
404 PRIMARY KEY (pk)
405 );
406 INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
407 EXPLAIN UPDATE
408 d AS OUTR1, b AS OUTR2
409 SET OUTR1.col_varchar_nokey = NULL
410 WHERE
411 ( 't', 'b' ) IN
412 (
413 SELECT
414 INNR1.col_varchar_nokey AS x,
415 INNR1.col_varchar_key AS y
416 FROM bb AS INNR1
417 WHERE OUTR1.pk = 1
418 );
419 id select_type table type possible_keys key key_len ref rows Extra
420 1 PRIMARY OUTR1 const PRIMARY PRIMARY 4 const 1
421 1 PRIMARY INNR1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR1)
422 1 PRIMARY OUTR2 index NULL PRIMARY 4 NULL 2 Using index
423 DROP TABLE IF EXISTS b,bb,d;
424 #
425 # Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103)
426 #
427 CREATE TABLE t (
428 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1) STORED
429 );
430 INSERT INTO t VALUES ();
431 UPDATE t t1, t t2 SET t2.y = 1, t1.x = 2;
432 SELECT * FROM t;
433 x y gc
434 2 1 3
435 DROP TABLE t;
436 CREATE TABLE t (
437 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1), KEY (x,gc)
438 );
439 INSERT INTO t VALUES ();
440 UPDATE t t1, t t2 SET t1.x = 1, t2.y = 2;
441 SELECT * FROM t;
442 x y gc
443 1 2 2
444 SELECT gc FROM t;
445 gc
446 2
447 CHECK TABLE t;
448 Table Op Msg_type Msg_text
449 test.t check status OK
450 DROP TABLE t;
451 # stored
452 CREATE TABLE C (
453 col_varchar_nokey VARCHAR(1),
454 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
455 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
456 );
457 INSERT INTO C (col_varchar_nokey) VALUES ('c');
458 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
459 SET OUTR1.`col_varchar_nokey` = 'f',
460 OUTR2.`col_varchar_nokey` = "a";
461 id select_type table type possible_keys key key_len ref rows Extra
462 1 SIMPLE OUTR1 system NULL NULL NULL NULL 1
463 1 SIMPLE OUTR2 system NULL NULL NULL NULL 1
464 UPDATE C AS OUTR1, C AS OUTR2
465 SET OUTR1.`col_varchar_nokey` = 'f',
466 OUTR2.`col_varchar_nokey` = "a";
467 SELECT * from C;
468 col_varchar_nokey col_varchar_key
469 a aa
470 DROP TABLE C;
471 # stored, indexed
472 CREATE TABLE C (
473 col_varchar_nokey VARCHAR(1),
474 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
475 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
476 KEY (col_varchar_key, col_varchar_nokey)
477 );
478 INSERT INTO C (col_varchar_nokey) VALUES ('c');
479 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
480 SET OUTR1.`col_varchar_nokey` = 'f',
481 OUTR2.`col_varchar_nokey` = "a";
482 id select_type table type possible_keys key key_len ref rows Extra
483 1 SIMPLE OUTR1 system NULL NULL NULL NULL 1
484 1 SIMPLE OUTR2 system NULL NULL NULL NULL 1
485 UPDATE C AS OUTR1, C AS OUTR2
486 SET OUTR1.`col_varchar_nokey` = 'f',
487 OUTR2.`col_varchar_nokey` = "a";
488 SELECT * from C;
489 col_varchar_nokey col_varchar_key
490 a aa
491 DROP TABLE C;
492 # virtual
493 CREATE TABLE C (
494 col_varchar_nokey VARCHAR(1),
495 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
496 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
497 );
498 INSERT INTO C (col_varchar_nokey) VALUES ('c');
499 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
500 SET OUTR1.`col_varchar_nokey` = 'f',
501 OUTR2.`col_varchar_nokey` = "a";
502 id select_type table type possible_keys key key_len ref rows Extra
503 1 SIMPLE OUTR1 system NULL NULL NULL NULL 1
504 1 SIMPLE OUTR2 system NULL NULL NULL NULL 1
505 UPDATE C AS OUTR1, C AS OUTR2
506 SET OUTR1.`col_varchar_nokey` = 'f',
507 OUTR2.`col_varchar_nokey` = "a";
508 SELECT * from C;
509 col_varchar_nokey col_varchar_key
510 a aa
511 DROP TABLE C;
512 # virtual, indexed
513 CREATE TABLE C (
514 col_varchar_nokey VARCHAR(1),
515 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
516 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
517 KEY (col_varchar_key, col_varchar_nokey)
518 );
519 INSERT INTO C (col_varchar_nokey) VALUES ('c');
520 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
521 SET OUTR1.`col_varchar_nokey` = 'f',
522 OUTR2.`col_varchar_nokey` = "a";
523 id select_type table type possible_keys key key_len ref rows Extra
524 1 SIMPLE OUTR1 system NULL NULL NULL NULL 1
525 1 SIMPLE OUTR2 system NULL NULL NULL NULL 1
526 UPDATE C AS OUTR1, C AS OUTR2
527 SET OUTR1.`col_varchar_nokey` = 'f',
528 OUTR2.`col_varchar_nokey` = "a";
529 SELECT * from C;
530 col_varchar_nokey col_varchar_key
531 a aa
532 DROP TABLE C;
533 #
534 # Bug #21530366 CRASH/ASSERTION, CORRUPTION WITH INDEXES +
535 # VIRTUAL COLUMNS, BLOB
536 #
537 CREATE TABLE t (
538 a INTEGER,
539 b BLOB GENERATED ALWAYS AS (a) VIRTUAL,
540 INDEX (b(57))
541 );
542 INSERT INTO t (a) VALUES (9);
543 UPDATE t SET a = 10;
544 DELETE FROM t WHERE a = 10;
545 DROP TABLE t;
546 # Bug#21807818: Generated columns not updated with empty insert list
547 CREATE TABLE t (
548 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
549 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL,
550 KEY (a(183),b)
551 );
552 ERROR HY000: Function or expression '''' cannot be used in the GENERATED ALWAYS AS clause of `b`
553 CREATE TABLE t (
554 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
555 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL
556 );
557 INSERT IGNORE INTO t VALUES(), (), ();
558 DELETE IGNORE FROM t;
559 DROP TABLE t;
560 #
561 # Bug#22195458:GCOLS: ASSERTION 0 AND CORRUPTION...
562 #
563 CREATE TABLE t (
564 a INT,
565 b YEAR GENERATED ALWAYS AS ('a') VIRTUAL,
566 c YEAR GENERATED ALWAYS AS ('aaaa') VIRTUAL,
567 b1 YEAR GENERATED ALWAYS AS ('a') STORED,
568 c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
569 UNIQUE(b),
570 UNIQUE(b1)
571 );
572 INSERT IGNORE INTO t VALUES();
573 SELECT b from t;
574 b
575 0000
576 SELECT b1 from t;
577 b1
578 0000
579 SELECT * from t;
580 a b c b1 c1
581 NULL 0000 0000 0000 0000
582 DELETE FROM t;
583 CHECK TABLE t EXTENDED;
584 Table Op Msg_type Msg_text
585 test.t check status OK
586 DROP TABLE t;
587 # Bug#22195364:GCOLS: FAILING ASSERTION:
588 # DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
589 CREATE TABLE t (
590 a INT,
591 c BLOB GENERATED ALWAYS AS ('') VIRTUAL,
592 UNIQUE KEY(c(1),a)
593 );
594 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
595 SELECT * FROM t;
596 a c
597 1
598 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
599 SELECT * FROM t;
600 a c
601 2
602 SELECT GROUP_CONCAT(c ORDER BY c) FROM t;
603 GROUP_CONCAT(c ORDER BY c)
604
605 DROP TABLE t;
606 #Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
607 CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL,
608 c2 INT GENERATED ALWAYS AS(2) STORED);
609 INSERT INTO t VALUES(DEFAULT, DEFAULT);
610 SELECT * FROM t;
611 c1 c2
612 1 2
613 CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
614 INSERT INTO t1(c2) VALUES(DEFAULT);
615 SELECT * FROM t1;
616 c1 c2
617 NULL NULL
618 CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
619 INSERT INTO t2(c2) VALUES(DEFAULT);
620 SELECT * FROM t2;
621 c1 c2
622 1 2
623 DROP TABLE t, t1, t2;
624 # Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
625 # GENERATED COLUMNS
626 CREATE TABLE t1 (
627 i1 INTEGER,
628 i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
629 );
630 INSERT INTO t1 (i1) SELECT 5;
631 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
632 SELECT * FROM t1;
633 i1 i2
634 5 10
635 5 10
636 CREATE TABLE t2 (
637 i1 INTEGER,
638 i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
639 );
640 INSERT INTO t2 (i1) SELECT 5;
641 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
642 SELECT * FROM t2;
643 i1 i2
644 5 10
645 5 10
646 DROP TABLE t1,t2;
647 #
648 # Bug#22070021 GCOL:ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
649 # BITMAP_IS_SET(TABLE->WRITE_SET,
650 #
651 CREATE TABLE t1(
652 c1 INT,
653 c2 INT GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
654 KEY(c2)
655 );
656 INSERT INTO t1(c1) VALUES(0);
657 DELETE O1.* FROM t1 AS O1, t1 AS O2;
658 SELECT * FROM t1;
659 c1 c2
660 DROP TABLE t1;
661 #
662 # Bug#21944199 SIMPLE DELETE QUERY CAUSES INNODB: FAILING ASSERTION: 0
663 # & DATA CORRUPTION
664 #
665 CREATE TEMPORARY TABLE t1 (
666 a INTEGER NOT NULL,
667 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
668 );
669 INSERT INTO t1 (a) VALUES (0), (0), (0);
670 ALTER TABLE t1 ADD INDEX idx (b);
671 DELETE FROM t1;
672 DROP TEMPORARY TABLE t1;
673 #
674 # Original test case from MDEV-17890
675 #
676 CREATE TABLE t1 (
677 pk BIGINT AUTO_INCREMENT,
678 b BIT(15),
679 v BIT(10) AS (b) VIRTUAL,
680 PRIMARY KEY(pk),
681 UNIQUE(v)
682 );
683 INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
684 Warnings:
685 Warning 1264 Out of range value for column 'v' at row 1
686 SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
687 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
688 ERROR 22001: Data too long for column 'v' at row 1
689 DROP TABLE t1;
690 #
691 # MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
692 #
693 CREATE TABLE t1 (
694 id INT NOT NULL AUTO_INCREMENT,
695 f ENUM('a','b','c'),
696 v ENUM('a','b','c') AS (f),
697 KEY(v,id)
698 ) ENGINE=MyISAM;
699 INSERT INTO t1 (f) VALUES ('a'),('b');
700 INSERT IGNORE INTO t1 SELECT * FROM t1;
701 Warnings:
702 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
703 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
704 DROP TABLE t1;
705 CREATE TABLE t1 (
706 id INT NOT NULL AUTO_INCREMENT,
707 f ENUM('a','b','c'),
708 v ENUM('a','b','c') AS (f),
709 KEY(v,id)
710 ) ENGINE=MyISAM;
711 INSERT INTO t1 (f) VALUES ('a'),('b');
712 INSERT IGNORE INTO t1 SELECT * FROM t1;
713 Warnings:
714 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
715 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
716 DROP TABLE t1;
717 #
718 # MDEV-23597 Assertion `marked_for_read()' failed while evaluating DEFAULT
719 #
720 CREATE TABLE t1 (a INT UNIQUE, b INT DEFAULT (c+1), c int);
721 INSERT INTO t1 VALUES (1,1,1);
722 UPDATE t1 SET b=DEFAULT;
723 SELECT * from t1;
724 a b c
725 1 2 1
726 REPLACE t1 VALUES(1,1,1);
727 INSERT INTO t1 VALUES (1,1,1) ON DUPLICATE KEY UPDATE b= DEFAULT;
728 SELECT * from t1;
729 a b c
730 1 2 1
731 REPLACE t1 VALUES(1,1,1);
732 CREATE TABLE t2 (a INT, b INT DEFAULT (c+1), c int);
733 INSERT INTO t2 VALUES (5,5,5);
734 UPDATE t1 join t2 set t1.b= DEFAULT, t2.b= DEFAULT;
735 SELECT * from t1, t2;
736 a b c a b c
737 1 2 1 5 6 5
738 DROP TABLE t1, t2;
739 DROP VIEW IF EXISTS v1,v2;
740 DROP TABLE IF EXISTS t1,t2,t3;
741 DROP PROCEDURE IF EXISTS p1;
742 DROP FUNCTION IF EXISTS f1;
743 DROP TRIGGER IF EXISTS trg1;
744 DROP TRIGGER IF EXISTS trg2;
745 set sql_warnings = 0;