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