Debian Codegraph @

mysql-test/suite/gcol/inc/gcol_ins_upd.inc

raw
1 ################################################################################
2 # inc/gcol_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: #
13 # Change Date: #
14 # Change: #
15 ################################################################################
16
17 let $create1 = create table t1 (a int,
18 b int generated always as (-a) virtual,
19 c int generated always as (-a) stored);
20 let $create2 = create table t1 (a int unique,
21 b int generated always as (-a) virtual,
22 c int generated always as (-a) stored);
23 let $create3 = create table t1 (a int,
24 b int generated always as (-a) virtual,
25 c int generated always as (-a) stored unique);
26 let $create4 = create table t1 (a int,
27 b int generated always as (-a) virtual,
28 c int generated always as (-a) stored unique,
29 d varchar(16));
30 eval $create1;
31 set sql_warnings = 1;
32
33 --echo #
34 --echo # *** INSERT ***
35 --echo #
36
37 --echo # INSERT INTO tbl_name VALUES... DEFAULT is specified against gcols
38 insert into t1 values (1,default,default);
39 select * from t1;
40 delete from t1;
41 select * from t1;
42
43 --echo # INSERT INTO tbl_name VALUES... NULL is specified against gcols
44 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
45 insert into t1 values (1,null,null);
46 select * from t1;
47 delete from t1;
48 select * from t1;
49
50 --echo # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
51 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
52 insert 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_gcol_list>) VALUES...
58 insert into t1 (a) values (1), (2);
59 select * from t1 order by a;
60 delete from t1;
61 select * from t1;
62
63 --echo # INSERT INTO tbl_name (<normal+gcols>) VALUES... DEFAULT is specified
64 --echo # against gcols
65 insert into t1 (a,b) values (1,default), (2,default);
66 --sorted_result
67 select * from t1;
68 delete from t1;
69 select * from t1;
70
71 --echo # INSERT INTO tbl_name (<normal+gcols>) VALUES... NULL is specified against gcols
72 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
73 insert into t1 (a,b) values (1,null), (2,null);
74 select * from t1;
75 delete from t1;
76 select * from t1;
77
78 --echo # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
79 --echo # against gcols
80 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
81 insert into t1 (a,b) values (1,3), (2,4);
82 select * from t1;
83 delete from t1;
84 select * from t1;
85 drop table t1;
86
87 --echo # Table with UNIQUE non-gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
88 --echo # KEY UPDATE <non_gcol>=expr, <gcol>=expr
89 eval $create2;
90 insert into t1 values (1,default,default);
91 insert into t1 values (1,default,default)
92 on duplicate key update a=2, b=default;
93 select a,b,c from t1;
94 delete from t1 where b in (1,2);
95 select * from t1;
96 drop table t1;
97
98 --echo # Table with UNIQUE gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
99 --echo # KEY UPDATE <non_gcol>=expr, <gcol>=expr
100 eval $create3;
101 insert into t1 values (1,default,default);
102 insert into t1 values (1,default,default)
103 on duplicate key update a=2, b=default;
104 select a,b,c from t1;
105
106 --echo # CREATE new_table ... LIKE old_table
107 --echo # INSERT INTO new_table SELECT * from old_table
108 create table t2 like t1;
109 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
110 insert into t2 select * from t1;
111 insert into t2(a) select a from t1;
112 select * from t2;
113 drop table t2;
114
115 --echo # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-gcols>, <gcols>)
116 --echo # SELECT <non-gcols>, <gcols> from old_table
117 insert into t1 values (1,default,default);
118 --sorted_result
119 select * from t1;
120 create table t2 like t1;
121 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
122 insert into t2 (a,b) select a,b from t1;
123 insert into t2 (a) select a from t1;
124 select * from t2 order by a;
125 drop table t2;
126 drop table t1;
127
128 --echo #
129 --echo # *** UPDATE ***
130 --echo #
131
132 --echo # UPDATE tbl_name SET non-gcol=expr WHERE non-gcol=expr
133 eval $create1;
134 insert into t1 (a) values (1), (2);
135 select * from t1 order by a;
136 update t1 set a=3 where a=2;
137 select * from t1 order by a;
138 delete from t1;
139 select * from t1;
140
141 --echo # UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
142 insert into t1 (a) values (1), (2);
143 --sorted_result
144 select * from t1;
145 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
146 update t1 set c=3 where a=2;
147 --sorted_result
148 select * from t1;
149 delete from t1;
150 select * from t1;
151
152 --echo # UPDATE tbl_name SET non-gcol=expr WHERE gcol=expr
153 insert into t1 (a) values (1), (2);
154 --sorted_result
155 select * from t1;
156 update t1 set a=3 where b=-2;
157 --sorted_result
158 select * from t1;
159 delete from t1;
160 select * from t1;
161
162 --echo # UPDATE tbl_name SET gcol=expr WHERE gcol=expr
163 insert into t1 (a) values (1), (2);
164 --sorted_result
165 select * from t1;
166 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
167 update t1 set c=3 where b=-2;
168 --sorted_result
169 select * from t1;
170 delete from t1;
171 select * from t1;
172 drop table t1;
173
174 --echo # INDEX created on gcol
175 --echo # UPDATE tbl_name SET non-gcol=expr WHERE gcol=const
176 eval $create3;
177 insert into t1 (a) values (1), (2);
178 select * from t1 order by a;
179 update t1 set a=3 where c=-2;
180 --sorted_result
181 select * from t1;
182 delete from t1;
183 select * from t1;
184
185
186 --echo # INDEX created on gcol
187 --echo # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
188 insert into t1 (a) values (1), (2);
189 --sorted_result
190 select * from t1;
191 update t1 set a=3 where c between -3 and -2;
192 --sorted_result
193 select * from t1;
194 delete from t1;
195 select * from t1;
196
197 --echo # No INDEX created on gcol
198 --echo # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
199 insert into t1 (a) values (1), (2);
200 --sorted_result
201 select * from t1;
202 update t1 set a=3 where b between -3 and -2;
203 --sorted_result
204 select * from t1;
205 delete from t1;
206 select * from t1;
207
208 --echo # INDEX created on gcol
209 --echo # UPDATE tbl_name SET non-gcol=expr
210 --echo # WHERE gcol=between const1 and const2 ORDER BY gcol
211 insert into t1 (a) values (1), (2), (3), (4), (5);
212 --sorted_result
213 select * from t1;
214 update t1 set a=6 where c between -1 and 0
215 order by c;
216 --sorted_result
217 select * from t1;
218 delete from t1 where c between -6 and 0;
219 select * from t1;
220
221 --echo # INDEX created on gcol
222 --echo # UPDATE tbl_name SET non-gcol=expr
223 --echo # WHERE gcol=between const1 and const2 ORDER BY gcol LIMIT 2
224 insert into t1 (a) values (1), (2), (3), (4), (5);
225 select * from t1 order by a;
226 update t1 set a=6 where c between -1 and 0
227 order by c limit 2;
228 --sorted_result
229 select * from t1;
230 delete from t1 where c between -2 and 0 order by c;
231 select * from t1 order by a;
232 delete from t1;
233
234 --echo # INDEX created on gcol
235 --echo # UPDATE tbl_name SET non-gcol=expr
236 --echo # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
237 insert into t1 (a) values (1), (2), (3), (4), (5);
238 --sorted_result
239 select * from t1;
240 update t1 set a=6 where (c between -2 and 0) and (b=-1);
241 --sorted_result
242 select * from t1;
243 delete from t1;
244
245 --echo # INDEX created on gcol
246 --echo # UPDATE tbl_name SET non-gcol=expr
247 --echo # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
248 --echo # ORDER BY indexed gcol
249 insert into t1 (a) values (1), (2), (3), (4), (5);
250 --sorted_result
251 select * from t1;
252 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
253 --sorted_result
254 select * from t1;
255 delete from t1;
256 drop table t1;
257
258 let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`;
259 if ($innodb_engine)
260 {
261 --echo #
262 --echo # Verify ON UPDATE/DELETE actions of FOREIGN KEYs
263 create table t2 (a int primary key, name varchar(10));
264 create table t1 (a int primary key, b int generated always as (a % 10) stored);
265 insert into t2 values (1, 'value1'), (2,'value2'), (3,'value3');
266 insert into t1 (a) values (1),(2),(3);
267 select * from t1 order by a;
268 select * from t2 order by a;
269 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a order by t1.a;
270
271 --echo # - ON UPDATE RESTRICT
272 alter table t1 add foreign key (b) references t2(a) on update restrict;
273 --error 1452
274 insert into t1 (a) values (4);
275 --error 1451
276 update t2 set a=4 where a=3;
277 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
278 alter table t1 drop foreign key t1_ibfk_1;
279
280 --echo # - ON DELETE RESTRICT
281 alter table t1 add foreign key (b) references t2(a) on delete restrict;
282 --error 1451
283 delete from t2 where a=3;
284 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
285 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
286 alter table t1 drop foreign key t1_ibfk_1;
287
288 --echo # - ON DELETE CASCADE
289 alter table t1 add foreign key (b) references t2(a) on delete cascade;
290 delete from t2 where a=3;
291 select t1.a, t1.b, t2.name from t1,t2 where t1.b=t2.a;
292 select t1.a, t1.b, t2.name from t1 left outer join t2 on (t1.b=t2.a);
293 alter table t1 drop foreign key t1_ibfk_1;
294
295 drop table t1;
296 drop table t2;
297 }
298
299 --echo #
300 --echo # *** REPLACE ***
301 --echo #
302
303 --echo # UNIQUE INDEX on gcol
304 --echo # REPLACE tbl_name (non-gcols) VALUES (non-gcols);
305 eval $create4;
306 insert into t1 (a,d) values (1,'a'), (2,'b');
307 select * from t1 order by a;
308 replace t1 (a,d) values (1,'c');
309 select * from t1 order by a;
310 delete from t1;
311 select * from t1;
312
313
314 # *** DELETE
315 # All required tests for DELETE are performed as part of the above testing
316 # for INSERT, UPDATE and REPLACE.
317
318 set sql_warnings = 0;
319 drop table t1;
320
321 --echo Bug#20170778: WL411:FAILING ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
322 --echo BITMAP_IS_SET(TABLE->WR
323 --echo #
324 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT, col5
325 INT GENERATED ALWAYS AS (col3 * col2) VIRTUAL, col6 INT GENERATED ALWAYS AS
326 (col4 * col1) STORED, col7 INT GENERATED ALWAYS AS (col6 + col6) VIRTUAL,
327 col8 INT GENERATED ALWAYS AS (col6 / col5) STORED, col9 TEXT);
328
329 SET @fill_amount = (@@innodb_page_size / 2 ) + 1;
330
331 INSERT INTO t1 (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES /* 3 */
332 (3, 3 / 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT, DEFAULT ,REPEAT(CAST(3 AS
333 CHAR(1)),@fill_amount)) , (3, 3 * 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT,
334 DEFAULT ,REPEAT(CAST(3 AS CHAR(1)),@fill_amount));
335
336 UPDATE t1 SET col1 = 2;
337 UPDATE t1 SET col7 = DEFAULT;
338 UPDATE t1 SET col8 = DEFAULT;
339 DROP TABLE t1;
340
341 if ($support_virtual_index)
342 {
343 --echo Bug#20797344: WL#8149: ALLOCATED SPACE FOR INDEXED BLOB VGC CAN BE
344 --echo OVERWRITTEN FOR UPDATE
345 --echo #
346 CREATE TABLE t (a varchar(100), b blob,
347 c blob GENERATED ALWAYS AS (concat(a,b)) VIRTUAL,
348 d blob GENERATED ALWAYS AS (b) VIRTUAL,
349 e int(11) GENERATED ALWAYS AS (10) VIRTUAL,
350 h int(11) NOT NULL, PRIMARY KEY (h), key(c(20)));
351 INSERT INTO t(a,b,h) VALUES('aaaaaaa','1111111', 11);
352 INSERT INTO t(a,b,h) VALUES('bbbbbbb','2222222', 22);
353 SELECT c FROM t;
354 UPDATE t SET a='ccccccc';
355 SELECT c FROM t;
356 DROP TABLE t;
357 }
358
359 --echo # Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
360 --echo # BITMAP_IS_SET(TABLE->WRITE_SET
361 --echo #
362
363 CREATE TABLE b (
364 pk INTEGER AUTO_INCREMENT,
365 col_varchar_nokey VARCHAR(1),
366 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
367 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
368 PRIMARY KEY (pk)
369 );
370
371 INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
372
373 CREATE TABLE d (
374 pk INTEGER AUTO_INCREMENT,
375 col_varchar_nokey VARCHAR(1),
376 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
377 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
378 PRIMARY KEY (pk)
379 ) ;
380
381 INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
382
383 CREATE TABLE bb (
384 pk INTEGER AUTO_INCREMENT,
385 col_varchar_nokey VARCHAR(1) /*! NULL */,
386 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
387 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
388 PRIMARY KEY (pk)
389 );
390
391 INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
392
393 EXPLAIN UPDATE
394 d AS outr1, b AS outr2
395 SET outr1.col_varchar_nokey = NULL
396 WHERE
397 ( 't', 'b' ) IN
398 (
399 SELECT
400 innr1.col_varchar_nokey AS x,
401 innr1.col_varchar_key AS y
402 FROM bb AS innr1
403 WHERE outr1.pk = 1
404 );
405
406 DROP TABLE IF EXISTS b,bb,d;
407
408
409 --echo #
410 --echo # Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103)
411 --echo #
412
413 CREATE TABLE t (
414 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1) STORED
415 );
416 INSERT INTO t VALUES ();
417 UPDATE t t1, t t2 SET t2.y = 1, t1.x = 2;
418 SELECT * FROM t;
419 DROP TABLE t;
420
421 if ($support_virtual_index)
422 {
423 CREATE TABLE t (
424 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1), KEY (x,gc)
425 );
426 INSERT INTO t VALUES ();
427 UPDATE t t1, t t2 SET t1.x = 1, t2.y = 2;
428 SELECT * FROM t;
429 SELECT gc FROM t;
430 DROP TABLE t;
431 }
432
433 let $query=
434 UPDATE c AS outr1, c AS outr2
435 SET outr1.`col_varchar_nokey` = 'f',
436 outr2.`col_varchar_nokey` = "a";
437
438 --echo # stored
439
440 CREATE TABLE c (
441 col_varchar_nokey VARCHAR(1),
442 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
443 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
444 );
445
446 INSERT INTO c (col_varchar_nokey) VALUES ('c');
447 eval EXPLAIN $query;
448 eval $query;
449 SELECT * from c;
450 DROP TABLE c;
451
452 --echo # stored, indexed
453
454 CREATE TABLE c (
455 col_varchar_nokey VARCHAR(1),
456 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
457 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
458 KEY (col_varchar_key, col_varchar_nokey)
459 );
460
461 INSERT INTO c (col_varchar_nokey) VALUES ('c');
462 eval EXPLAIN $query;
463 eval $query;
464 SELECT * from c;
465 DROP TABLE c;
466
467 --echo # virtual
468
469 CREATE TABLE c (
470 col_varchar_nokey VARCHAR(1),
471 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
472 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
473 );
474
475 INSERT INTO c (col_varchar_nokey) VALUES ('c');
476 eval EXPLAIN $query;
477 eval $query;
478 SELECT * from c;
479 DROP TABLE c;
480
481 if ($support_virtual_index)
482 {
483 --echo # virtual, indexed
484
485 CREATE TABLE c (
486 col_varchar_nokey VARCHAR(1),
487 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
488 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL,
489 KEY (col_varchar_key, col_varchar_nokey)
490 );
491
492 INSERT INTO c (col_varchar_nokey) VALUES ('c');
493 eval EXPLAIN $query;
494 eval $query;
495 SELECT * from c;
496 DROP TABLE c;
497 }
498
499 if ($support_virtual_index)
500 {
501 --echo #
502 --echo # Bug #21530366 CRASH/ASSERTION, CORRUPTION WITH INDEXES +
503 --echo # VIRTUAL COLUMNS, BLOB
504 --echo #
505
506 CREATE TABLE t (
507 a INTEGER,
508 b BLOB GENERATED ALWAYS AS (a) VIRTUAL,
509 INDEX (b(57))
510 );
511
512 INSERT INTO t (a) VALUES (9);
513 UPDATE t SET a = 10;
514 DELETE FROM t WHERE a = 10;
515
516 DROP TABLE t;
517 }
518
519 if ($support_virtual_index)
520 {
521 --echo # Bug#21807818: Generated columns not updated with empty insert list
522
523 --source include/turn_off_strict_mode.inc
524
525 CREATE TABLE t (
526 a BLOB GENERATED ALWAYS AS ('') VIRTUAL,
527 b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL NOT NULL,
528 KEY (a(183),b)
529 );
530
531 INSERT INTO t VALUES(), (), ();
532
533 # Different row numbers in old optimizer (always 1) and hypergraph optimizer.
534 --replace_regex /row \d$/row 1/
535 DELETE IGNORE FROM t;
536
537 DROP TABLE t;
538
539 --source include/restore_strict_mode.inc
540
541 --echo #
542 --echo # Bug#22195458:GCOLS: ASSERTION 0 AND CORRUPTION...
543 --echo #
544 --disable_warnings
545 --source include/turn_off_strict_mode.inc
546 CREATE TABLE t (
547 a INT,
548 b YEAR GENERATED ALWAYS AS ('a') VIRTUAL,
549 c YEAR GENERATED ALWAYS AS ('aaaa') VIRTUAL,
550 b1 YEAR GENERATED ALWAYS AS ('a') STORED,
551 c1 YEAR GENERATED ALWAYS AS ('aaaa') STORED,
552 UNIQUE(b),
553 UNIQUE(b1)
554 );
555 INSERT INTO t VALUES();
556 SELECT b from t;
557 SELECT b1 from t;
558 SELECT * from t;
559 DELETE FROM t;
560 CHECK TABLE t EXTENDED;
561 DROP TABLE t;
562 --source include/restore_strict_mode.inc
563 --enable_warnings
564
565 --echo # Bug#22195364:GCOLS: FAILING ASSERTION:
566 --echo # DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA
567 CREATE TABLE t (
568 a INT,
569 c BLOB GENERATED ALWAYS AS ('') VIRTUAL NOT NULL,
570 UNIQUE KEY(c(1),a)
571 );
572 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
573 SELECT * FROM t;
574 INSERT INTO t(a) VALUES(1) ON DUPLICATE KEY UPDATE a=2;
575 SELECT * FROM t;
576 # Test Field_blob::store_to_mem
577 SELECT GROUP_CONCAT(c ORDER BY c) FROM t;
578 DROP TABLE t;
579 }
580
581 --echo #Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
582 CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL NOT NULL,
583 c2 INT GENERATED ALWAYS AS(2) STORED NOT NULL);
584 INSERT INTO t VALUES(DEFAULT, DEFAULT);
585 SELECT * FROM t;
586 CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
587 INSERT INTO t1(c2) VALUES(DEFAULT);
588 SELECT * FROM t1;
589 CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED NOT NULL);
590 INSERT INTO t2(c2) VALUES(DEFAULT);
591 SELECT * FROM t2;
592 DROP TABLE t, t1, t2;
593
594 --echo # Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
595 --echo # GENERATED COLUMNS
596 CREATE TABLE t1 (
597 i1 INTEGER,
598 i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
599 );
600 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
601 INSERT INTO t1 (i1, i2) SELECT 5, 6;
602 INSERT INTO t1 (i1) SELECT 5;
603 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
604 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
605 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
606 SELECT * FROM t1;
607
608 CREATE TABLE t2 (
609 i1 INTEGER,
610 i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
611 );
612 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
613 INSERT INTO t2 (i1, i2) SELECT 5, 6;
614 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
615 INSERT INTO t2 (i1, i2) SELECT * FROM t1;
616 INSERT INTO t2 (i1) SELECT 5;
617 --error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
618 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
619 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
620 SELECT * FROM t2;
621
622 DROP TABLE t1,t2;
623
624 if ($support_virtual_index)
625 {
626
627 --echo #
628 --echo # Bug#22070021 GCOL:ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
629 --echo # BITMAP_IS_SET(TABLE->WRITE_SET,
630 --echo #
631
632 CREATE TABLE t1(
633 c1 INT,
634 c2 INT GENERATED ALWAYS AS (c1 + c1) VIRTUAL,
635 KEY(c2)
636 );
637
638 INSERT INTO t1(c1) VALUES(0);
639 DELETE O1.* FROM t1 AS O1, t1 AS O2;
640 SELECT * FROM t1;
641 DROP TABLE t1;
642
643 --echo #
644 --echo # Bug#21944199 SIMPLE DELETE QUERY CAUSES INNODB: FAILING ASSERTION: 0
645 --echo # & DATA CORRUPTION
646 --echo #
647
648 CREATE TEMPORARY TABLE t1 (
649 a INTEGER NOT NULL,
650 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
651 );
652
653 INSERT INTO t1 (a) VALUES (0), (0), (0);
654
655 ALTER TABLE t1 ADD INDEX idx (b);
656
657 DELETE FROM t1;
658
659 DROP TEMPORARY TABLE t1;
660
661
662 --echo #
663 --echo # WL#6599 - Add test case for SHOW COLUMNS/KEYS on a temporary table.
664 --echo #
665
666 CREATE TEMPORARY TABLE t1 (
667 a INTEGER NOT NULL,
668 b INTEGER GENERATED ALWAYS AS (a+1) VIRTUAL
669 );
670
671 --replace_column 8 #
672 SHOW FULL COLUMNS FROM t1;
673 SHOW KEYS FROM t1;
674
675 DROP TEMPORARY TABLE t1;
676
677 }