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 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
20 select * from t1;
21 a b c
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 into t1 values (1,2,3);
27 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
28 select * from t1;
29 a b c
30 delete from t1;
31 select * from t1;
32 a b c
33 # INSERT INTO tbl_name (<non_gcol_list>) VALUES...
34 insert into t1 (a) values (1), (2);
35 select * from t1 order by a;
36 a b c
37 1 -1 -1
38 2 -2 -2
39 delete from t1;
40 select * from t1;
41 a b c
42 # INSERT INTO tbl_name (<normal+gcols>) VALUES... DEFAULT is specified
43 # against gcols
44 insert into t1 (a,b) values (1,default), (2,default);
45 select * from t1;
46 a b c
47 1 -1 -1
48 2 -2 -2
49 delete from t1;
50 select * from t1;
51 a b c
52 # INSERT INTO tbl_name (<normal+gcols>) VALUES... NULL is specified against gcols
53 insert into t1 (a,b) values (1,null), (2,null);
54 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
55 select * from t1;
56 a b c
57 delete from t1;
58 select * from t1;
59 a b c
60 # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
61 # against gcols
62 insert into t1 (a,b) values (1,3), (2,4);
63 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
64 select * from t1;
65 a b c
66 delete from t1;
67 select * from t1;
68 a b c
69 drop table t1;
70 # Table with UNIQUE non-gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
71 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
72 create table t1 (a int unique,
73 b int generated always as (-a) virtual,
74 c int generated always as (-a) stored);
75 insert into t1 values (1,default,default);
76 insert into t1 values (1,default,default)
77 on duplicate key update a=2, b=default;
78 select a,b,c from t1;
79 a b c
80 2 -2 -2
81 delete from t1 where b in (1,2);
82 select * from t1;
83 a b c
84 2 -2 -2
85 drop table t1;
86 # Table with UNIQUE gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
87 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
88 create table t1 (a int,
89 b int generated always as (-a) virtual,
90 c int generated always as (-a) stored unique);
91 insert into t1 values (1,default,default);
92 insert into t1 values (1,default,default)
93 on duplicate key update a=2, b=default;
94 select a,b,c from t1;
95 a b c
96 2 -2 -2
97 # CREATE new_table ... LIKE old_table
98 # INSERT INTO new_table SELECT * from old_table
99 create table t2 like t1;
100 insert into t2 select * from t1;
101 ERROR HY000: The value specified for generated column 'b' in table 't2' is not allowed.
102 insert into t2(a) select a from t1;
103 select * from t2;
104 a b c
105 2 -2 -2
106 drop table t2;
107 # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-gcols>, <gcols>)
108 # SELECT <non-gcols>, <gcols> from old_table
109 insert into t1 values (1,default,default);
110 select * from t1;
111 a b c
112 1 -1 -1
113 2 -2 -2
114 create table t2 like t1;
115 insert into t2 (a,b) select a,b from t1;
116 ERROR HY000: The value specified for generated column 'b' in table 't2' is not allowed.
117 insert into t2 (a) select a from t1;
118 select * from t2 order by a;
119 a b c
120 1 -1 -1
121 2 -2 -2
122 drop table t2;
123 drop table t1;
124 #
125 # *** UPDATE ***
126 #
127 # UPDATE tbl_name SET non-gcol=expr WHERE non-gcol=expr
128 create table t1 (a int,
129 b int generated always as (-a) virtual,
130 c int generated always as (-a) stored);
131 insert into t1 (a) values (1), (2);
132 select * from t1 order by a;
133 a b c
134 1 -1 -1
135 2 -2 -2
136 update t1 set a=3 where a=2;
137 select * from t1 order by a;
138 a b c
139 1 -1 -1
140 3 -3 -3
141 delete from t1;
142 select * from t1;
143 a b c
144 # UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
145 insert into t1 (a) values (1), (2);
146 select * from t1;
147 a b c
148 1 -1 -1
149 2 -2 -2
150 update t1 set c=3 where a=2;
151 ERROR HY000: The value specified for generated column 'c' in table 't1' is not allowed.
152 select * from t1;
153 a b c
154 1 -1 -1
155 2 -2 -2
156 delete from t1;
157 select * from t1;
158 a b c
159 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=expr
160 insert into t1 (a) values (1), (2);
161 select * from t1;
162 a b c
163 1 -1 -1
164 2 -2 -2
165 update t1 set a=3 where b=-2;
166 select * from t1;
167 a b c
168 1 -1 -1
169 3 -3 -3
170 delete from t1;
171 select * from t1;
172 a b c
173 # UPDATE tbl_name SET gcol=expr WHERE gcol=expr
174 insert into t1 (a) values (1), (2);
175 select * from t1;
176 a b c
177 1 -1 -1
178 2 -2 -2
179 update t1 set c=3 where b=-2;
180 ERROR HY000: The value specified for generated column 'c' in table 't1' is not allowed.
181 select * from t1;
182 a b c
183 1 -1 -1
184 2 -2 -2
185 delete from t1;
186 select * from t1;
187 a b c
188 drop table t1;
189 # INDEX created on gcol
190 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=const
191 create table t1 (a int,
192 b int generated always as (-a) virtual,
193 c int generated always as (-a) stored unique);
194 insert into t1 (a) values (1), (2);
195 select * from t1 order by a;
196 a b c
197 1 -1 -1
198 2 -2 -2
199 update t1 set a=3 where c=-2;
200 select * from t1;
201 a b c
202 1 -1 -1
203 3 -3 -3
204 delete from t1;
205 select * from t1;
206 a b c
207 # INDEX created on gcol
208 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
209 insert into t1 (a) values (1), (2);
210 select * from t1;
211 a b c
212 1 -1 -1
213 2 -2 -2
214 update t1 set a=3 where c between -3 and -2;
215 select * from t1;
216 a b c
217 1 -1 -1
218 3 -3 -3
219 delete from t1;
220 select * from t1;
221 a b c
222 # No INDEX created on gcol
223 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
224 insert into t1 (a) values (1), (2);
225 select * from t1;
226 a b c
227 1 -1 -1
228 2 -2 -2
229 update t1 set a=3 where b between -3 and -2;
230 select * from t1;
231 a b c
232 1 -1 -1
233 3 -3 -3
234 delete from t1;
235 select * from t1;
236 a b c
237 # INDEX created on gcol
238 # UPDATE tbl_name SET non-gcol=expr
239 # WHERE gcol=between const1 and const2 ORDER BY gcol
240 insert into t1 (a) values (1), (2), (3), (4), (5);
241 select * from t1;
242 a b c
243 1 -1 -1
244 2 -2 -2
245 3 -3 -3
246 4 -4 -4
247 5 -5 -5
248 update t1 set a=6 where c between -1 and 0
249 order by c;
250 select * from t1;
251 a b c
252 2 -2 -2
253 3 -3 -3
254 4 -4 -4
255 5 -5 -5
256 6 -6 -6
257 delete from t1 where c between -6 and 0;
258 select * from t1;
259 a b c
260 # INDEX created on gcol
261 # UPDATE tbl_name SET non-gcol=expr
262 # WHERE gcol=between const1 and const2 ORDER BY gcol LIMIT 2
263 insert into t1 (a) values (1), (2), (3), (4), (5);
264 select * from t1 order by a;
265 a b c
266 1 -1 -1
267 2 -2 -2
268 3 -3 -3
269 4 -4 -4
270 5 -5 -5
271 update t1 set a=6 where c between -1 and 0
272 order by c limit 2;
273 select * from t1;
274 a b c
275 2 -2 -2
276 3 -3 -3
277 4 -4 -4
278 5 -5 -5
279 6 -6 -6
280 delete from t1 where c between -2 and 0 order by c;
281 select * from t1 order by a;
282 a b c
283 3 -3 -3
284 4 -4 -4
285 5 -5 -5
286 6 -6 -6
287 delete from t1;
288 # INDEX created on gcol
289 # UPDATE tbl_name SET non-gcol=expr
290 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
291 insert into t1 (a) values (1), (2), (3), (4), (5);
292 select * from t1;
293 a b c
294 1 -1 -1
295 2 -2 -2
296 3 -3 -3
297 4 -4 -4
298 5 -5 -5
299 update t1 set a=6 where (c between -2 and 0) and (b=-1);
300 select * from t1;
301 a b c
302 2 -2 -2
303 3 -3 -3
304 4 -4 -4
305 5 -5 -5
306 6 -6 -6
307 delete from t1;
308 # INDEX created on gcol
309 # UPDATE tbl_name SET non-gcol=expr
310 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
311 # ORDER BY indexed gcol
312 insert into t1 (a) values (1), (2), (3), (4), (5);
313 select * from t1;
314 a b c
315 1 -1 -1
316 2 -2 -2
317 3 -3 -3
318 4 -4 -4
319 5 -5 -5
320 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
321 select * from t1;
322 a b c
323 2 -2 -2
324 3 -3 -3
325 4 -4 -4
326 5 -5 -5
327 6 -6 -6
328 delete from t1;
329 drop table t1;
330 #
331 # Verify ON UPDATE/DELETE actions of FOREIGN KEYs
332 create table t2 (a int primary key, name varchar(10));
333 create table t1 (a int primary key, b int generated always as (a % 10) stored);
334 insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
335 insert into t1 (a) values (1),(2),(3);
336 select * from t1 order by a;
337 a b
338 1 1
339 2 2
340 3 3
341 select * from t2 order by a;
342 a name
343 1 value1
344 2 value2
345 3 value3
346 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a order by t1.a;
347 a b name
348 1 1 value1
349 2 2 value2
350 3 3 value3
351 # - ON UPDATE RESTRICT
352 alter table t1 add foreign key (b) references t2(a) on update restrict;
353 insert into t1 (a) values (4);
354 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`) ON UPDATE RESTRICT)
355 update t2 set a=4 where a=3;
356 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`) ON UPDATE RESTRICT)
357 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
358 a b name
359 1 1 value1
360 2 2 value2
361 3 3 value3
362 alter table t1 drop foreign key t1_ibfk_1;
363 # - ON DELETE RESTRICT
364 alter table t1 add foreign key (b) references t2(a) on delete restrict;
365 delete from t2 where a=3;
366 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`) ON DELETE RESTRICT)
367 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
368 a b name
369 1 1 value1
370 2 2 value2
371 3 3 value3
372 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
373 a b name
374 1 1 value1
375 2 2 value2
376 3 3 value3
377 alter table t1 drop foreign key t1_ibfk_1;
378 # - ON DELETE CASCADE
379 alter table t1 add foreign key (b) references t2(a) on delete cascade;
380 delete from t2 where a=3;
381 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
382 a b name
383 1 1 value1
384 2 2 value2
385 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
386 a b name
387 1 1 value1
388 2 2 value2
389 alter table t1 drop foreign key t1_ibfk_1;
390 drop table t1;
391 drop table t2;
392 #
393 # *** REPLACE ***
394 #
395 # UNIQUE INDEX on gcol
396 # REPLACE tbl_name (non-gcols) VALUES (non-gcols);
397 create table t1 (a int,
398 b int generated always as (-a) virtual,
399 c int generated always as (-a) stored unique,
400 d varchar(16));
401 insert into t1 (a,d) values (1,'a'), (2,'b');
402 select * from t1 order by a;
403 a b c d
404 1 -1 -1 a
405 2 -2 -2 b
406 replace t1 (a,d) values (1,'c');
407 select * from t1 order by a;
408 a b c d
409 1 -1 -1 c
410 2 -2 -2 b
411 delete from t1;
412 select * from t1;
413 a b c d
414 set sql_warnings = 0;
415 drop table t1;
416 Bug#20170778: WL411:FAILING ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
417 BITMAP_IS_SET(TABLE->WR
418 #
419 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT, col5
420 INT GENERATED ALWAYS AS (col3 * col2) VIRTUAL, col6 INT GENERATED ALWAYS AS
421 (col4 * col1) STORED, col7 INT GENERATED ALWAYS AS (col6 + col6) VIRTUAL,
422 col8 INT GENERATED ALWAYS AS (col6 / col5) STORED, col9 TEXT);
423 SET @fill_amount = (@@innodb_page_size / 2 ) + 1;
424 INSERT INTO t1 (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES /* 3 */
425 (3, 3 / 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT, DEFAULT ,REPEAT(CAST(3 AS
426 CHAR(1)),@fill_amount)) , (3, 3 * 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT,
427 DEFAULT ,REPEAT(CAST(3 AS CHAR(1)),@fill_amount));
428 UPDATE t1 SET col1 = 2;
429 UPDATE t1 SET col7 = DEFAULT;
430 UPDATE t1 SET col8 = DEFAULT;
431 DROP TABLE t1;
432 Bug#20797344: WL#8149: ALLOCATED SPACE FOR INDEXED BLOB VGC CAN BE
433 OVERWRITTEN FOR UPDATE
434 #
435 CREATE TABLE t (a varchar(100), b blob,
436 c blob GENERATED ALWAYS AS (concat(a,b)) VIRTUAL,
437 d blob GENERATED ALWAYS AS (b) VIRTUAL,
438 e int(11) GENERATED ALWAYS AS (10) VIRTUAL,
439 h int(11) NOT NULL, PRIMARY KEY (h), key(c(20)));
440 Warnings:
441 Warning 1681 Integer display width is deprecated and will be removed in a future release.
442 Warning 1681 Integer display width is deprecated and will be removed in a future release.
443 INSERT INTO t(a,b,h) VALUES('aaaaaaa','1111111', 11);
444 INSERT INTO t(a,b,h) VALUES('bbbbbbb','2222222', 22);
445 SELECT c FROM t;
446 c
447 aaaaaaa1111111
448 bbbbbbb2222222
449 UPDATE t SET a='ccccccc';
450 SELECT c FROM t;
451 c
452 ccccccc1111111
453 ccccccc2222222
454 DROP TABLE t;
455 # Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
456 # BITMAP_IS_SET(TABLE->WRITE_SET
457 #
458 CREATE TABLE b (
459 pk INTEGER AUTO_INCREMENT,
460 col_varchar_nokey VARCHAR(1),
461 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
462 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
463 PRIMARY KEY (pk)
464 );
465 INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
466 CREATE TABLE d (
467 pk INTEGER AUTO_INCREMENT,
468 col_varchar_nokey VARCHAR(1),
469 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
470 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
471 PRIMARY KEY (pk)
472 ) ;
473 INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
474 CREATE TABLE bb (
475 pk INTEGER AUTO_INCREMENT,
476 col_varchar_nokey VARCHAR(1) /*! NULL */,
477 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
478 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
479 PRIMARY KEY (pk)
480 );
481 INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
482 EXPLAIN UPDATE
483 d AS outr1, b AS outr2
484 SET outr1.col_varchar_nokey = NULL
485 WHERE
486 ( 't', 'b' ) IN
487 (
488 SELECT
489 innr1.col_varchar_nokey AS x,
490 innr1.col_varchar_key AS y
491 FROM bb AS innr1
492 WHERE outr1.pk = 1
493 );
494 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
495 1 UPDATE outr1 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
496 1 SIMPLE innr1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(outr1)
497 1 SIMPLE outr2 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
498 Warnings:
499 Note 1276 Field or reference 'test.outr1.pk' of SELECT #2 was resolved in SELECT #1
500 Note 1003 update `test`.`d` `outr1` join `test`.`b` `outr2` semi join (`test`.`bb` `innr1`) set `test`.`outr1`.`col_varchar_nokey` = NULL where ((`test`.`innr1`.`col_varchar_key` = 'b') and (`test`.`innr1`.`col_varchar_nokey` = 't'))
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 DROP TABLE t;
526 # stored
527 CREATE TABLE c (
528 col_varchar_nokey VARCHAR(1),
529 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
530 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
531 );
532 INSERT INTO c (col_varchar_nokey) VALUES ('c');
533 EXPLAIN UPDATE c AS outr1, c AS outr2
534 SET outr1.`col_varchar_nokey` = 'f',
535 outr2.`col_varchar_nokey` = "a";
536 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
537 1 UPDATE outr1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
538 1 UPDATE outr2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
539 Warnings:
540 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
541 UPDATE c AS outr1, c AS outr2
542 SET outr1.`col_varchar_nokey` = 'f',
543 outr2.`col_varchar_nokey` = "a";
544 SELECT * from c;
545 col_varchar_nokey col_varchar_key
546 a aa
547 DROP TABLE c;
548 # stored, indexed
549 CREATE TABLE c (
550 col_varchar_nokey VARCHAR(1),
551 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
552 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
553 KEY (col_varchar_key, col_varchar_nokey)
554 );
555 INSERT INTO c (col_varchar_nokey) VALUES ('c');
556 EXPLAIN UPDATE c AS outr1, c AS outr2
557 SET outr1.`col_varchar_nokey` = 'f',
558 outr2.`col_varchar_nokey` = "a";
559 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
560 1 UPDATE outr1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
561 1 UPDATE outr2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
562 Warnings:
563 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
564 UPDATE c AS outr1, c AS outr2
565 SET outr1.`col_varchar_nokey` = 'f',
566 outr2.`col_varchar_nokey` = "a";
567 SELECT * from c;
568 col_varchar_nokey col_varchar_key
569 a aa
570 DROP TABLE c;
571 # virtual
572 CREATE TABLE c (
573 col_varchar_nokey VARCHAR(1),
574 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
575 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
576 );
577 INSERT INTO c (col_varchar_nokey) VALUES ('c');
578 EXPLAIN UPDATE c AS outr1, c AS outr2
579 SET outr1.`col_varchar_nokey` = 'f',
580 outr2.`col_varchar_nokey` = "a";
581 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
582 1 UPDATE outr1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
583 1 UPDATE outr2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
584 Warnings:
585 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
586 UPDATE c AS outr1, c AS outr2
587 SET outr1.`col_varchar_nokey` = 'f',
588 outr2.`col_varchar_nokey` = "a";
589 SELECT * from c;
590 col_varchar_nokey col_varchar_key
591 a aa
592 DROP TABLE c;
593 # virtual, indexed
594 CREATE TABLE c (
595 col_varchar_nokey VARCHAR(1),
596 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
597 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
598 KEY (col_varchar_key, col_varchar_nokey)
599 );
600 INSERT INTO c (col_varchar_nokey) VALUES ('c');
601 EXPLAIN UPDATE c AS outr1, c AS outr2
602 SET outr1.`col_varchar_nokey` = 'f',
603 outr2.`col_varchar_nokey` = "a";
604 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
605 1 UPDATE outr1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
606 1 UPDATE outr2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
607 Warnings:
608 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
609 UPDATE c AS outr1, c AS outr2
610 SET outr1.`col_varchar_nokey` = 'f',
611 outr2.`col_varchar_nokey` = "a";
612 SELECT * from c;
613 col_varchar_nokey col_varchar_key
614 a aa
615 DROP TABLE c;
616 #
617 # Bug #21530366 CRASH/ASSERTION, CORRUPTION WITH INDEXES +
618 # VIRTUAL COLUMNS, BLOB
619 #
620 CREATE TABLE t (
621 a INTEGER,
622 b BLOB GENERATED ALWAYS AS (a) VIRTUAL,
623 INDEX (b(57))
624 );
625 INSERT INTO t (a) VALUES (9);
626 UPDATE t SET a = 10;
627 DELETE FROM t WHERE a = 10;
628 DROP TABLE t;
629 # Bug#21807818: Generated columns not updated with empty insert list
630 Warnings:
631 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
632 CREATE TABLE t (
633 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
634 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL NOT NULL,
635 KEY (a(183),b)
636 );
637 INSERT INTO t VALUES(), (), ();
638 Warnings:
639 Warning 1265 Data truncated for column 'b' at row 1
640 Warning 1265 Data truncated for column 'b' at row 2
641 Warning 1265 Data truncated for column 'b' at row 3
642 DELETE IGNORE FROM t;
643 Warnings:
644 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
645 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
646 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
647 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
648 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
649 Warning 1292 Incorrect datetime value: '' for column 'b' at row 1
650 DROP TABLE t;
651 #
652 # Bug#22195458:GCOLS: ASSERTION 0 AND CORRUPTION...
653 #
654 CREATE TABLE t (
655 a INT,
656 b YEAR GENERATED ALWAYS AS ('a') VIRTUAL,
657 c YEAR GENERATED ALWAYS AS ('aaaa') VIRTUAL,
658 b1 YEAR GENERATED ALWAYS AS ('a') STORED,
659 c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
660 UNIQUE(b),
661 UNIQUE(b1)
662 );
663 INSERT INTO t VALUES();
664 SELECT b from t;
665 b
666 0000
667 SELECT b1 from t;
668 b1
669 0000
670 SELECT * from t;
671 a b c b1 c1
672 NULL 0000 0000 0000 0000
673 DELETE FROM t;
674 CHECK TABLE t EXTENDED;
675 Table Op Msg_type Msg_text
676 test.t check status OK
677 DROP TABLE t;
678 # Bug#22195364:GCOLS: FAILING ASSERTION:
679 # DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
680 CREATE TABLE t (
681 a INT,
682 c BLOB GENERATED ALWAYS AS ('') VIRTUAL NOT NULL,
683 UNIQUE KEY(c(1),a)
684 );
685 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
686 SELECT * FROM t;
687 a c
688 1
689 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
690 SELECT * FROM t;
691 a c
692 2
693 SELECT GROUP_CONCAT(c ORDER BY c) FROM t;
694 GROUP_CONCAT(c ORDER BY c)
695
696 DROP TABLE t;
697 #Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
698 CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL NOT NULL,
699 c2 INT GENERATED ALWAYS AS(2) STORED NOT NULL);
700 INSERT INTO t VALUES(DEFAULT, DEFAULT);
701 SELECT * FROM t;
702 c1 c2
703 1 2
704 CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
705 INSERT INTO t1(c2) VALUES(DEFAULT);
706 SELECT * FROM t1;
707 c1 c2
708 NULL NULL
709 CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED NOT NULL);
710 INSERT INTO t2(c2) VALUES(DEFAULT);
711 SELECT * FROM t2;
712 c1 c2
713 1 2
714 DROP TABLE t, t1, t2;
715 # Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
716 # GENERATED COLUMNS
717 CREATE TABLE t1 (
718 i1 INTEGER,
719 i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
720 );
721 INSERT INTO t1 (i1, i2) SELECT 5, 6;
722 ERROR HY000: The value specified for generated column 'i2' in table 't1' is not allowed.
723 INSERT INTO t1 (i1) SELECT 5;
724 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
725 ERROR HY000: The value specified for generated column 'i2' in table 't1' is not allowed.
726 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
727 SELECT * FROM t1;
728 i1 i2
729 5 10
730 5 10
731 CREATE TABLE t2 (
732 i1 INTEGER,
733 i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
734 );
735 INSERT INTO t2 (i1, i2) SELECT 5, 6;
736 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
737 INSERT INTO t2 (i1, i2) SELECT * FROM t1;
738 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
739 INSERT INTO t2 (i1) SELECT 5;
740 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
741 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
742 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
743 SELECT * FROM t2;
744 i1 i2
745 5 10
746 5 10
747 DROP TABLE t1,t2;
748 #
749 # Bug#22070021 GCOL:ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
750 # BITMAP_IS_SET(TABLE->WRITE_SET,
751 #
752 CREATE TABLE t1(
753 c1 INT,
754 c2 INT GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
755 KEY(c2)
756 );
757 INSERT INTO t1(c1) VALUES(0);
758 DELETE O1.* FROM t1 AS O1, t1 AS O2;
759 SELECT * FROM t1;
760 c1 c2
761 DROP TABLE t1;
762 #
763 # Bug#21944199 SIMPLE DELETE QUERY CAUSES INNODB: FAILING ASSERTION: 0
764 # & DATA CORRUPTION
765 #
766 CREATE TEMPORARY TABLE t1 (
767 a INTEGER NOT NULL,
768 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
769 );
770 INSERT INTO t1 (a) VALUES (0), (0), (0);
771 ALTER TABLE t1 ADD INDEX idx (b);
772 DELETE FROM t1;
773 DROP TEMPORARY TABLE t1;
774 #
775 # WL#6599 - Add test case for SHOW COLUMNS/KEYS on a temporary table.
776 #
777 CREATE TEMPORARY TABLE t1 (
778 a INTEGER NOT NULL,
779 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
780 );
781 SHOW FULL COLUMNS FROM t1;
782 Field Type Collation Null Key Default Extra Privileges Comment
783 a int NULL NO NULL NULL #
784 b int NULL YES NULL VIRTUAL GENERATED #
785 SHOW KEYS FROM t1;
786 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
787 DROP TEMPORARY TABLE t1;
788 DROP VIEW IF EXISTS v1,v2;
789 DROP TABLE IF EXISTS t1,t2,t3;
790 DROP PROCEDURE IF EXISTS p1;
791 DROP FUNCTION IF EXISTS f1;
792 DROP TRIGGER IF EXISTS trg1;
793 DROP TRIGGER IF EXISTS trg2;
794 set sql_warnings = 0;