Debian Codegraph @

mysql-test/suite/vcol/t/vcol_keys_innodb.test

raw
1 ###############################################################################
2 # t/vcol_keys_innodb.test #
3 # #
4 # Purpose: #
5 # Testing keys, indexes defined upon virtual columns. #
6 # #
7 # InnoDB branch #
8 # #
9 #-----------------------------------------------------------------------------#
10 # Original Author: Andrey Zhakov #
11 # Original Date: 2008-09-04 #
12 # Change Author: #
13 # Change Date: #
14 # Change: #
15 ###############################################################################
16
17 #
18 # NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE !
19 # TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
20 # THE SOURCED FILES ONLY.
21 #
22
23 #-----------------------------------------------------------------------------#
24 # General not engine specific settings and requirements
25 --source suite/vcol/inc/vcol_init_vars.pre
26
27 #-----------------------------------------------------------------------------#
28 # Cleanup
29 --source suite/vcol/inc/vcol_cleanup.inc
30
31 #-----------------------------------------------------------------------------#
32 # Engine specific settings and requirements
33
34 ##### Storage engine to be tested
35 # Set the session storage engine
36 --source include/have_innodb.inc
37 SET @@session.default_storage_engine = 'InnoDB';
38
39 ##### Workarounds for known open engine specific bugs
40 # none
41
42 #-----------------------------------------------------------------------------#
43 # Execute the tests to be applied to all storage engines
44 let $with_foreign_keys = 1;
45 --source suite/vcol/inc/vcol_keys.inc
46
47 # Cleanup
48 --source suite/vcol/inc/vcol_cleanup.inc
49
50 #-----------------------------------------------------------------------------#
51 # Execute storage engine specific tests
52 #-----------------------------------------------------------------------------#
53
54 --echo #
55 --echo # MDEV-11737 Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N
56 --echo #
57
58 CREATE TABLE t1 (i INT PRIMARY KEY, vi INT AS (i*2) VIRTUAL UNIQUE)
59 ENGINE=InnoDB;
60 CREATE TABLE t2 (i INT) ENGINE=InnoDB;
61 ALTER TABLE t1 ADD COLUMN col INT;
62 SELECT * FROM t1 WHERE vi < 2;
63 DROP TABLE t1, t2;
64
65
66 #
67 # MDEV-11604 Assertion `!check_datetime_range(ltime)' failed in TIME_to_longlong_datetime_packed
68 #
69 create table t1 (
70 pk int auto_increment,
71 col_varchar varchar(847) not null default '',
72 col_int bigint(15) unsigned zerofill,
73 col_datetime datetime(3) not null default '1900-01-01 00:00:00',
74 col_time time(5) not null default '00:00:00',
75 col_blob text,
76 col_bit bit(34),
77 col_year year,
78 col_char char(10),
79 col_dec decimal(18,9) not null default 0,
80 col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '',
81 col_date date not null default '1900-01-01',
82 col_timestamp timestamp(3) not null default '1971-01-01 00:00:00',
83 vcol_datetime datetime as (truncate(col_datetime,0)) virtual,
84 vcol_dec decimal(18,9) zerofill as (col_dec) virtual,
85 vcol_bit bit(63) as (col_bit) virtual,
86 vcol_char binary(51) as (col_char) virtual,
87 vcol_timestamp timestamp(5) as (col_timestamp) virtual,
88 vcol_enum enum('','a','b','c','d','e','f','foo','bar') as (col_enum) virtual,
89 vcol_int tinyint(48) zerofill as (col_int) virtual,
90 vcol_time time(4) as (col_time) virtual,
91 vcol_varchar varbinary(3873) as (col_varchar) virtual,
92 vcol_year year as (col_year) virtual,
93 vcol_date date as (col_date) virtual,
94 vcol_blob longtext as (col_blob) virtual,
95 primary key(pk)
96 ) engine=innodb;
97
98 insert into t1 (col_varchar,col_int,col_datetime,col_time,col_blob,col_bit,col_year,col_char,col_dec,col_enum,col_date,col_timestamp) values
99 ('foo',1,'2010-05-08 13:08:12.034783','18:32:14','foo',b'0111110101001001',1992,'f',0.2,'','1994-12-26','2019-01-11 00:00:00'),
100 ('bar',6,'1900-01-01 00:00:00','00:00:00','bar',b'10011000001101011000101',1985,'b',0.7,'','2028-04-06','1971-01-01 00:00:00');
101
102 alter table t1 add index(vcol_datetime);
103 drop table t1;
104
105
106 #
107 # MDEV-11704 InnoDB: Failing assertion: dfield_is_null(dfield2) || dfield2->data
108 #
109 create table t1 (
110 pk int,
111 col_blob mediumtext not null default '',
112 vcol_blob tinyblob as (col_blob) virtual,
113 col_char char(22) null,
114 primary key(pk),
115 index(col_char,vcol_blob(64))
116 ) engine=innodb;
117 insert ignore into t1 (pk) values (1),(2);
118 update t1 set col_char = 'foo' where pk = 1;
119 drop table t1;
120
121 #
122 # MDEV-20799 DROP Virtual Column crashes MariaDB
123 #
124 --source include/have_sequence.inc
125 create table t1 (
126 id int not null primary key,
127 a varchar(200),
128 b varchar(200),
129 c int,
130 va char(200) generated always as (ucase(a)) virtual,
131 vb char(200) generated always as (ucase(b)) virtual,
132 key (c,va,vb)
133 ) engine=innodb;
134 insert t1 (id,a,c) select seq,seq,seq from seq_1_to_330;
135 select IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE') from t1;
136 alter table t1 drop column va;
137 drop table t1;
138
139 --echo #
140 --echo # MDEV-39081 InnoDB: tried to purge non-delete-marked record,
141 --echo # assertion fails in row_purge_del_mark_error
142 --echo #
143 CREATE TABLE t1(
144 pk INT AUTO_INCREMENT,
145 f1 INT DEFAULT 0,
146 f2 INT as (1 + 1) VIRTUAL,
147 KEY (f1), PRIMARY KEY (pk), UNIQUE (f2)) ENGINE=InnoDB;
148 START TRANSACTION WITH CONSISTENT SNAPSHOT;
149
150 CONNECT(con1,localhost,root,,);
151 INSERT INTO t1 (pk) VALUES(1);
152 DELETE FROM t1;
153 INSERT INTO t1 (pk) VALUES(1);
154 UPDATE t1 set f1 = 2;
155 DISCONNECT con1;
156 CONNECTION default;
157 COMMIT;
158 --source ../innodb/include/wait_all_purged.inc
159 DROP TABLE t1;