Debian Codegraph @

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

raw
1 SET @@session.default_storage_engine = 'InnoDB';
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 # Verify ON UPDATE/DELETE actions of FOREIGN KEYs
338 create table t2 (a int primary key, name varchar(10));
339 create table t1 (a int primary key, b int generated always as (a % 10) stored);
340 insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
341 insert into t1 (a) values (1),(2),(3);
342 select * from t1 order by a;
343 a b
344 1 1
345 2 2
346 3 3
347 select * from t2 order by a;
348 a name
349 1 value1
350 2 value2
351 3 value3
352 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a order by t1.a;
353 a b name
354 1 1 value1
355 2 2 value2
356 3 3 value3
357 # - ON UPDATE RESTRICT
358 alter table t1 add foreign key (b) references t2(a) on update restrict;
359 insert into t1 (a) values (4);
360 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
361 update t2 set a=4 where a=3;
362 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
363 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
364 a b name
365 1 1 value1
366 2 2 value2
367 3 3 value3
368 alter table t1 drop foreign key t1_ibfk_1;
369 # - ON DELETE RESTRICT
370 alter table t1 add foreign key (b) references t2(a) on delete restrict;
371 delete from t2 where a=3;
372 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`a`))
373 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
374 a b name
375 1 1 value1
376 2 2 value2
377 3 3 value3
378 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
379 a b name
380 1 1 value1
381 2 2 value2
382 3 3 value3
383 alter table t1 drop foreign key t1_ibfk_1;
384 # - ON DELETE CASCADE
385 alter table t1 add foreign key (b) references t2(a) on delete cascade;
386 delete from t2 where a=3;
387 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
388 a b name
389 1 1 value1
390 2 2 value2
391 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
392 a b name
393 1 1 value1
394 2 2 value2
395 alter table t1 drop foreign key t1_ibfk_1;
396 drop table t1;
397 drop table t2;
398 #
399 # *** REPLACE ***
400 #
401 # UNIQUE INDEX on gcol
402 # REPLACE tbl_name (non-gcols) VALUES (non-gcols);
403 create table t1 (a int,
404 b int generated always as (-a) virtual,
405 c int generated always as (-a) stored unique,
406 d varchar(16));
407 insert into t1 (a,d) values (1,'a'), (2,'b');
408 select * from t1 order by a;
409 a b c d
410 1 -1 -1 a
411 2 -2 -2 b
412 replace t1 (a,d) values (1,'c');
413 select * from t1 order by a;
414 a b c d
415 1 -1 -1 c
416 2 -2 -2 b
417 delete from t1;
418 select * from t1;
419 a b c d
420 set sql_warnings = 0;
421 drop table t1;
422 Bug#20170778: WL411:FAILING ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
423 BITMAP_IS_SET(TABLE->WR
424 #
425 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT, col5
426 INT GENERATED ALWAYS AS (col3 * col2) VIRTUAL, col6 INT GENERATED ALWAYS AS
427 (col4 * col1) STORED, col7 INT GENERATED ALWAYS AS (col6 + col6) VIRTUAL,
428 col8 INT GENERATED ALWAYS AS (col6 / col5) STORED, col9 TEXT);
429 SET @fill_amount = (@@innodb_page_size / 2 ) + 1;
430 INSERT INTO t1 (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES /* 3 */
431 (3, 3 / 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT, DEFAULT ,REPEAT(CAST(3 AS
432 CHAR(1)),@fill_amount)) , (3, 3 * 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT,
433 DEFAULT ,REPEAT(CAST(3 AS CHAR(1)),@fill_amount));
434 UPDATE t1 SET col1 = 2;
435 UPDATE t1 SET col7 = DEFAULT;
436 UPDATE t1 SET col8 = DEFAULT;
437 DROP TABLE t1;
438 Bug#20797344: WL#8149: ALLOCATED SPACE FOR INDEXED BLOB VGC CAN BE
439 OVERWRITTEN FOR UPDATE
440 #
441 CREATE TABLE t (a varchar(100), b blob,
442 c blob GENERATED ALWAYS AS (concat(a,b)) VIRTUAL,
443 d blob GENERATED ALWAYS AS (b) VIRTUAL,
444 e int(11) GENERATED ALWAYS AS (10) VIRTUAL,
445 h int(11) NOT NULL, PRIMARY KEY (h), key(c(20)));
446 INSERT INTO t(a,b,h) VALUES('aaaaaaa','1111111', 11);
447 INSERT INTO t(a,b,h) VALUES('bbbbbbb','2222222', 22);
448 SELECT c FROM t;
449 c
450 aaaaaaa1111111
451 bbbbbbb2222222
452 UPDATE t SET a='ccccccc';
453 SELECT c FROM t;
454 c
455 ccccccc1111111
456 ccccccc2222222
457 DROP TABLE t;
458 # Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
459 # BITMAP_IS_SET(TABLE->WRITE_SET
460 #
461 CREATE TABLE b (
462 pk INTEGER AUTO_INCREMENT,
463 col_varchar_nokey VARCHAR(1),
464 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
465 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
466 PRIMARY KEY (pk)
467 );
468 INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
469 CREATE TABLE d (
470 pk INTEGER AUTO_INCREMENT,
471 col_varchar_nokey VARCHAR(1),
472 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
473 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
474 PRIMARY KEY (pk)
475 ) ;
476 INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
477 CREATE TABLE bb (
478 pk INTEGER AUTO_INCREMENT,
479 col_varchar_nokey VARCHAR(1) /*! NULL */,
480 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
481 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
482 PRIMARY KEY (pk)
483 );
484 INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
485 EXPLAIN UPDATE
486 d AS OUTR1, b AS OUTR2
487 SET OUTR1.col_varchar_nokey = NULL
488 WHERE
489 ( 't', 'b' ) IN
490 (
491 SELECT
492 INNR1.col_varchar_nokey AS x,
493 INNR1.col_varchar_key AS y
494 FROM bb AS INNR1
495 WHERE OUTR1.pk = 1
496 );
497 id select_type table type possible_keys key key_len ref rows Extra
498 1 PRIMARY OUTR1 const PRIMARY PRIMARY 4 const 1
499 1 PRIMARY INNR1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR1)
500 1 PRIMARY OUTR2 ALL NULL NULL NULL NULL 2
501 DROP TABLE IF EXISTS b,bb,d;
502 #
503 # Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103)
504 #
505 CREATE TABLE t (
506 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1) STORED
507 );
508 INSERT INTO t VALUES ();
509 UPDATE t t1, t t2 SET t2.y = 1, t1.x = 2;
510 SELECT * FROM t;
511 x y gc
512 2 1 3
513 DROP TABLE t;
514 CREATE TABLE t (
515 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1), KEY (x,gc)
516 );
517 INSERT INTO t VALUES ();
518 UPDATE t t1, t t2 SET t1.x = 1, t2.y = 2;
519 SELECT * FROM t;
520 x y gc
521 1 2 2
522 SELECT gc FROM t;
523 gc
524 2
525 CHECK TABLE t;
526 Table Op Msg_type Msg_text
527 test.t check status OK
528 DROP TABLE t;
529 # stored
530 CREATE TABLE C (
531 col_varchar_nokey VARCHAR(1),
532 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
533 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
534 );
535 INSERT INTO C (col_varchar_nokey) VALUES ('c');
536 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
537 SET OUTR1.`col_varchar_nokey` = 'f',
538 OUTR2.`col_varchar_nokey` = "a";
539 id select_type table type possible_keys key key_len ref rows Extra
540 1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
541 1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
542 UPDATE C AS OUTR1, C AS OUTR2
543 SET OUTR1.`col_varchar_nokey` = 'f',
544 OUTR2.`col_varchar_nokey` = "a";
545 SELECT * from C;
546 col_varchar_nokey col_varchar_key
547 a aa
548 DROP TABLE C;
549 # stored, indexed
550 CREATE TABLE C (
551 col_varchar_nokey VARCHAR(1),
552 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
553 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
554 KEY (col_varchar_key, col_varchar_nokey)
555 );
556 INSERT INTO C (col_varchar_nokey) VALUES ('c');
557 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
558 SET OUTR1.`col_varchar_nokey` = 'f',
559 OUTR2.`col_varchar_nokey` = "a";
560 id select_type table type possible_keys key key_len ref rows Extra
561 1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
562 1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
563 UPDATE C AS OUTR1, C AS OUTR2
564 SET OUTR1.`col_varchar_nokey` = 'f',
565 OUTR2.`col_varchar_nokey` = "a";
566 SELECT * from C;
567 col_varchar_nokey col_varchar_key
568 a aa
569 DROP TABLE C;
570 # virtual
571 CREATE TABLE C (
572 col_varchar_nokey VARCHAR(1),
573 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
574 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
575 );
576 INSERT INTO C (col_varchar_nokey) VALUES ('c');
577 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
578 SET OUTR1.`col_varchar_nokey` = 'f',
579 OUTR2.`col_varchar_nokey` = "a";
580 id select_type table type possible_keys key key_len ref rows Extra
581 1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
582 1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
583 UPDATE C AS OUTR1, C AS OUTR2
584 SET OUTR1.`col_varchar_nokey` = 'f',
585 OUTR2.`col_varchar_nokey` = "a";
586 SELECT * from C;
587 col_varchar_nokey col_varchar_key
588 a aa
589 DROP TABLE C;
590 # virtual, indexed
591 CREATE TABLE C (
592 col_varchar_nokey VARCHAR(1),
593 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
594 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
595 KEY (col_varchar_key, col_varchar_nokey)
596 );
597 INSERT INTO C (col_varchar_nokey) VALUES ('c');
598 EXPLAIN UPDATE C AS OUTR1, C AS OUTR2
599 SET OUTR1.`col_varchar_nokey` = 'f',
600 OUTR2.`col_varchar_nokey` = "a";
601 id select_type table type possible_keys key key_len ref rows Extra
602 1 SIMPLE OUTR1 ALL NULL NULL NULL NULL 1
603 1 SIMPLE OUTR2 ALL NULL NULL NULL NULL 1
604 UPDATE C AS OUTR1, C AS OUTR2
605 SET OUTR1.`col_varchar_nokey` = 'f',
606 OUTR2.`col_varchar_nokey` = "a";
607 SELECT * from C;
608 col_varchar_nokey col_varchar_key
609 a aa
610 DROP TABLE C;
611 #
612 # Bug #21530366 CRASH/ASSERTION, CORRUPTION WITH INDEXES +
613 # VIRTUAL COLUMNS, BLOB
614 #
615 CREATE TABLE t (
616 a INTEGER,
617 b BLOB GENERATED ALWAYS AS (a) VIRTUAL,
618 INDEX (b(57))
619 );
620 INSERT INTO t (a) VALUES (9);
621 UPDATE t SET a = 10;
622 DELETE FROM t WHERE a = 10;
623 DROP TABLE t;
624 # Bug#21807818: Generated columns not updated with empty insert list
625 CREATE TABLE t (
626 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
627 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL,
628 KEY (a(183),b)
629 );
630 ERROR HY000: Function or expression '''' cannot be used in the GENERATED ALWAYS AS clause of `b`
631 CREATE TABLE t (
632 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
633 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL
634 );
635 INSERT IGNORE INTO t VALUES(), (), ();
636 DELETE IGNORE FROM t;
637 DROP TABLE t;
638 #
639 # Bug#22195458:GCOLS: ASSERTION 0 AND CORRUPTION...
640 #
641 CREATE TABLE t (
642 a INT,
643 b YEAR GENERATED ALWAYS AS ('a') VIRTUAL,
644 c YEAR GENERATED ALWAYS AS ('aaaa') VIRTUAL,
645 b1 YEAR GENERATED ALWAYS AS ('a') STORED,
646 c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
647 UNIQUE(b),
648 UNIQUE(b1)
649 );
650 INSERT IGNORE INTO t VALUES();
651 SELECT b from t;
652 b
653 0000
654 SELECT b1 from t;
655 b1
656 0000
657 SELECT * from t;
658 a b c b1 c1
659 NULL 0000 0000 0000 0000
660 DELETE FROM t;
661 CHECK TABLE t EXTENDED;
662 Table Op Msg_type Msg_text
663 test.t check status OK
664 DROP TABLE t;
665 # Bug#22195364:GCOLS: FAILING ASSERTION:
666 # DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
667 CREATE TABLE t (
668 a INT,
669 c BLOB GENERATED ALWAYS AS ('') VIRTUAL,
670 UNIQUE KEY(c(1),a)
671 );
672 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
673 SELECT * FROM t;
674 a c
675 1
676 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
677 SELECT * FROM t;
678 a c
679 2
680 SELECT GROUP_CONCAT(c ORDER BY c) FROM t;
681 GROUP_CONCAT(c ORDER BY c)
682
683 DROP TABLE t;
684 #Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
685 CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL,
686 c2 INT GENERATED ALWAYS AS(2) STORED);
687 INSERT INTO t VALUES(DEFAULT, DEFAULT);
688 SELECT * FROM t;
689 c1 c2
690 1 2
691 CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
692 INSERT INTO t1(c2) VALUES(DEFAULT);
693 SELECT * FROM t1;
694 c1 c2
695 NULL NULL
696 CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
697 INSERT INTO t2(c2) VALUES(DEFAULT);
698 SELECT * FROM t2;
699 c1 c2
700 1 2
701 DROP TABLE t, t1, t2;
702 # Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
703 # GENERATED COLUMNS
704 CREATE TABLE t1 (
705 i1 INTEGER,
706 i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
707 );
708 INSERT INTO t1 (i1) SELECT 5;
709 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
710 SELECT * FROM t1;
711 i1 i2
712 5 10
713 5 10
714 CREATE TABLE t2 (
715 i1 INTEGER,
716 i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
717 );
718 INSERT INTO t2 (i1) SELECT 5;
719 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
720 SELECT * FROM t2;
721 i1 i2
722 5 10
723 5 10
724 DROP TABLE t1,t2;
725 #
726 # Bug#22070021 GCOL:ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
727 # BITMAP_IS_SET(TABLE->WRITE_SET,
728 #
729 CREATE TABLE t1(
730 c1 INT,
731 c2 INT GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
732 KEY(c2)
733 );
734 INSERT INTO t1(c1) VALUES(0);
735 DELETE O1.* FROM t1 AS O1, t1 AS O2;
736 SELECT * FROM t1;
737 c1 c2
738 DROP TABLE t1;
739 #
740 # Bug#21944199 SIMPLE DELETE QUERY CAUSES INNODB: FAILING ASSERTION: 0
741 # & DATA CORRUPTION
742 #
743 CREATE TEMPORARY TABLE t1 (
744 a INTEGER NOT NULL,
745 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
746 );
747 INSERT INTO t1 (a) VALUES (0), (0), (0);
748 ALTER TABLE t1 ADD INDEX idx (b);
749 DELETE FROM t1;
750 DROP TEMPORARY TABLE t1;
751 #
752 # Original test case from MDEV-17890
753 #
754 CREATE TABLE t1 (
755 pk BIGINT AUTO_INCREMENT,
756 b BIT(15),
757 v BIT(10) AS (b) VIRTUAL,
758 PRIMARY KEY(pk),
759 UNIQUE(v)
760 );
761 INSERT IGNORE INTO t1 (b) VALUES (b'101110001110100'),(b'011101');
762 Warnings:
763 Warning 1264 Out of range value for column 'v' at row 1
764 SELECT pk, b INTO OUTFILE 'load.data' FROM t1;
765 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1 (pk, b);
766 ERROR 22001: Data too long for column 'v' at row 1
767 DROP TABLE t1;
768 #
769 # MDEV-18166 ASSERT_COLUMN_MARKED_FOR_READ failed on tables with vcols
770 #
771 CREATE TABLE t1 (
772 id INT NOT NULL AUTO_INCREMENT,
773 f ENUM('a','b','c'),
774 v ENUM('a','b','c') AS (f),
775 KEY(v,id)
776 ) ENGINE=MyISAM;
777 INSERT INTO t1 (f) VALUES ('a'),('b');
778 INSERT IGNORE INTO t1 SELECT * FROM t1;
779 Warnings:
780 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
781 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
782 DROP TABLE t1;
783 CREATE TABLE t1 (
784 id INT NOT NULL AUTO_INCREMENT,
785 f ENUM('a','b','c'),
786 v ENUM('a','b','c') AS (f),
787 KEY(v,id)
788 ) ENGINE=MyISAM;
789 INSERT INTO t1 (f) VALUES ('a'),('b');
790 INSERT IGNORE INTO t1 SELECT * FROM t1;
791 Warnings:
792 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
793 Warning 1906 The value specified for generated column 'v' in table 't1' has been ignored
794 DROP TABLE t1;
795 #
796 # MDEV-23597 Assertion `marked_for_read()' failed while evaluating DEFAULT
797 #
798 CREATE TABLE t1 (a INT UNIQUE, b INT DEFAULT (c+1), c int);
799 INSERT INTO t1 VALUES (1,1,1);
800 UPDATE t1 SET b=DEFAULT;
801 SELECT * from t1;
802 a b c
803 1 2 1
804 REPLACE t1 VALUES(1,1,1);
805 INSERT INTO t1 VALUES (1,1,1) ON DUPLICATE KEY UPDATE b= DEFAULT;
806 SELECT * from t1;
807 a b c
808 1 2 1
809 REPLACE t1 VALUES(1,1,1);
810 CREATE TABLE t2 (a INT, b INT DEFAULT (c+1), c int);
811 INSERT INTO t2 VALUES (5,5,5);
812 UPDATE t1 join t2 set t1.b= DEFAULT, t2.b= DEFAULT;
813 SELECT * from t1, t2;
814 a b c a b c
815 1 2 1 5 6 5
816 DROP TABLE t1, t2;
817 DROP VIEW IF EXISTS v1,v2;
818 DROP TABLE IF EXISTS t1,t2,t3;
819 DROP PROCEDURE IF EXISTS p1;
820 DROP FUNCTION IF EXISTS f1;
821 DROP TRIGGER IF EXISTS trg1;
822 DROP TRIGGER IF EXISTS trg2;
823 set sql_warnings = 0;