| 1 |
SET @@session.default_storage_engine = 'InnoDB'; |
| 2 |
# - UNIQUE KEY |
| 3 |
# - INDEX |
| 4 |
# - FULLTEXT INDEX |
| 5 |
# - SPATIAL INDEX (not supported) |
| 6 |
# - FOREIGN INDEX (partially supported) |
| 7 |
# - CHECK (allowed but not used) |
| 8 |
# UNIQUE |
| 9 |
create table t1 (a int, b int as (a*2) unique); |
| 10 |
drop table t1; |
| 11 |
create table t1 (a int, b int as (a*2) persistent unique); |
| 12 |
show create table t1; |
| 13 |
Table Create Table |
| 14 |
t1 CREATE TABLE `t1` ( |
| 15 |
`a` int(11) DEFAULT NULL, |
| 16 |
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, |
| 17 |
UNIQUE KEY `b` (`b`) |
| 18 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 19 |
describe t1; |
| 20 |
Field Type Null Key Default Extra |
| 21 |
a int(11) YES NULL |
| 22 |
b int(11) YES UNI NULL STORED GENERATED |
| 23 |
drop table t1; |
| 24 |
create table t1 (a int, b int as (a*2), unique key (b)); |
| 25 |
drop table t1; |
| 26 |
create table t1 (a int, b int as (a*2) persistent, unique (b)); |
| 27 |
show create table t1; |
| 28 |
Table Create Table |
| 29 |
t1 CREATE TABLE `t1` ( |
| 30 |
`a` int(11) DEFAULT NULL, |
| 31 |
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, |
| 32 |
UNIQUE KEY `b` (`b`) |
| 33 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 34 |
describe t1; |
| 35 |
Field Type Null Key Default Extra |
| 36 |
a int(11) YES NULL |
| 37 |
b int(11) YES UNI NULL STORED GENERATED |
| 38 |
drop table t1; |
| 39 |
create table t1 (a int, b int as (a*2)); |
| 40 |
alter table t1 add unique key (b); |
| 41 |
drop table t1; |
| 42 |
create table t1 (a int, b int as (a*2) persistent); |
| 43 |
alter table t1 add unique key (b); |
| 44 |
drop table t1; |
| 45 |
# Testing data manipulation operations involving UNIQUE keys |
| 46 |
# on virtual columns can be found in: |
| 47 |
# - vcol_ins_upd.inc |
| 48 |
# - vcol_select.inc |
| 49 |
# |
| 50 |
# INDEX |
| 51 |
create table t1 (a int, b int as (a*2), index (b)); |
| 52 |
drop table t1; |
| 53 |
create table t1 (a int, b int as (a*2), index (a,b)); |
| 54 |
drop table t1; |
| 55 |
create table t1 (a int, b int as (a*2) persistent, index (b)); |
| 56 |
show create table t1; |
| 57 |
Table Create Table |
| 58 |
t1 CREATE TABLE `t1` ( |
| 59 |
`a` int(11) DEFAULT NULL, |
| 60 |
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, |
| 61 |
KEY `b` (`b`) |
| 62 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 63 |
describe t1; |
| 64 |
Field Type Null Key Default Extra |
| 65 |
a int(11) YES NULL |
| 66 |
b int(11) YES MUL NULL STORED GENERATED |
| 67 |
drop table t1; |
| 68 |
create table t1 (a int, b int as (a*2) persistent, index (a,b)); |
| 69 |
show create table t1; |
| 70 |
Table Create Table |
| 71 |
t1 CREATE TABLE `t1` ( |
| 72 |
`a` int(11) DEFAULT NULL, |
| 73 |
`b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED, |
| 74 |
KEY `a` (`a`,`b`) |
| 75 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
| 76 |
describe t1; |
| 77 |
Field Type Null Key Default Extra |
| 78 |
a int(11) YES MUL NULL |
| 79 |
b int(11) YES NULL STORED GENERATED |
| 80 |
drop table t1; |
| 81 |
create table t1 (a int, b int as (a*2)); |
| 82 |
alter table t1 add index (b); |
| 83 |
alter table t1 add index (a,b); |
| 84 |
drop table t1; |
| 85 |
create table t1 (a int, b int as (a*2) persistent); |
| 86 |
alter table t1 add index (b); |
| 87 |
drop table t1; |
| 88 |
create table t1 (a int, b int as (a*2) persistent); |
| 89 |
alter table t1 add index (a,b); |
| 90 |
create table t2 like t1; |
| 91 |
drop table t2; |
| 92 |
drop table t1; |
| 93 |
# Testing data manipulation operations involving INDEX |
| 94 |
# on virtual columns can be found in: |
| 95 |
# - vcol_select.inc |
| 96 |
# |
| 97 |
# TODO: FULLTEXT INDEX |
| 98 |
# SPATIAL INDEX |
| 99 |
# Error "All parts of a SPATIAL index must be NOT NULL" |
| 100 |
create table t1 (a int, b geometry as (a+1) persistent, spatial index (b)); |
| 101 |
ERROR 42000: All parts of a SPATIAL index must be NOT NULL |
| 102 |
create table t1 (a int, b int as (a+1) persistent); |
| 103 |
alter table t1 add spatial index (b); |
| 104 |
ERROR HY000: Incorrect arguments to SPATIAL INDEX |
| 105 |
drop table t1; |
| 106 |
# FOREIGN KEY |
| 107 |
# Rejected FK options. |
| 108 |
create table t1 (a int, b int as (a+1) persistent, |
| 109 |
foreign key (b) references t2(a) on update set null); |
| 110 |
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column |
| 111 |
create table t1 (a int, b int as (a+1) persistent, |
| 112 |
foreign key (b) references t2(a) on update cascade); |
| 113 |
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column |
| 114 |
create table t1 (a int, b int as (a+1) persistent, |
| 115 |
foreign key (b) references t2(a) on delete set null); |
| 116 |
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column |
| 117 |
create table t1 (a int, b int as (a+1) persistent); |
| 118 |
alter table t1 add foreign key (b) references t2(a) on update set null; |
| 119 |
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column |
| 120 |
alter table t1 add foreign key (b) references t2(a) on update cascade; |
| 121 |
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column |
| 122 |
alter table t1 add foreign key (b) references t2(a) on delete set null; |
| 123 |
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column |
| 124 |
drop table t1; |
| 125 |
create table t1 (a int, b int as (a+1), foreign key (b) references t2(a)); |
| 126 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 127 |
create table t1 (a int, b int as (a+1)); |
| 128 |
alter table t1 add foreign key (b) references t2(a); |
| 129 |
ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") |
| 130 |
drop table t1; |
| 131 |
# Allowed FK options. |
| 132 |
create table t2 (a int primary key, b char(5)); |
| 133 |
create table t1 (a int, b int as (a % 10) persistent, |
| 134 |
foreign key (b) references t2(a) on update restrict); |
| 135 |
drop table t1; |
| 136 |
create table t1 (a int, b int as (a % 10) persistent, |
| 137 |
foreign key (b) references t2(a) on update no action); |
| 138 |
drop table t1; |
| 139 |
create table t1 (a int, b int as (a % 10) persistent, |
| 140 |
foreign key (b) references t2(a) on delete restrict); |
| 141 |
drop table t1; |
| 142 |
create table t1 (a int, b int as (a % 10) persistent, |
| 143 |
foreign key (b) references t2(a) on delete cascade); |
| 144 |
drop table t1; |
| 145 |
create table t1 (a int, b int as (a % 10) persistent, |
| 146 |
foreign key (b) references t2(a) on delete no action); |
| 147 |
drop table t1; |
| 148 |
|
| 149 |
# Testing data manipulation operations involving FOREIGN KEY |
| 150 |
# on virtual columns can be found in: |
| 151 |
# - vcol_ins_upd.inc |
| 152 |
# - vcol_select.inc |
| 153 |
create table t1 (a int, b timestamp as (now()), key (b)); |
| 154 |
ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 155 |
create table t1 (a int, b timestamp as (now())); |
| 156 |
alter table t1 add index (b); |
| 157 |
ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 158 |
drop table t1; |
| 159 |
create table t1 (a int, b varchar(100) as (user()), key (b)); |
| 160 |
ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 161 |
create table t1 (a int, b varchar(100) as (user())); |
| 162 |
alter table t1 add index (b); |
| 163 |
ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 164 |
drop table t1; |
| 165 |
create table t1 (a int, b double as (rand()), key (b)); |
| 166 |
ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 167 |
create table t1 (a int, b double as (rand())); |
| 168 |
alter table t1 add index (b); |
| 169 |
ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b` |
| 170 |
drop table t1; |
| 171 |
CREATE OR REPLACE TABLE t1 ( |
| 172 |
f2 DOUBLE NOT NULL DEFAULT '0', |
| 173 |
f3 DOUBLE NOT NULL DEFAULT '0', |
| 174 |
f4 DOUBLE, |
| 175 |
f5 DOUBLE DEFAULT '0', |
| 176 |
v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL, |
| 177 |
KEY (f5), |
| 178 |
KEY (v4) |
| 179 |
); |
| 180 |
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0); |
| 181 |
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1; |
| 182 |
INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1); |
| 183 |
INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1; |
| 184 |
DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9; |
| 185 |
SELECT * from t1; |
| 186 |
f2 f3 f4 f5 v4 |
| 187 |
5 7 NULL 0 5 |
| 188 |
5 4 0 4 5 |
| 189 |
5 7 0 7 5 |
| 190 |
5 0 0 4 5 |
| 191 |
5 0 0 7 5 |
| 192 |
5 7 7 7 7 |
| 193 |
5 1 1 0 1 |
| 194 |
DROP TABLE t1; |
| 195 |
CREATE TABLE t1 ( |
| 196 |
d DECIMAL(63,0) NOT NULL DEFAULT 0, |
| 197 |
c VARCHAR(64) NOT NULL DEFAULT '', |
| 198 |
vd DECIMAL(63,0) AS (d) VIRTUAL, |
| 199 |
vc VARCHAR(2048) AS (c) VIRTUAL, |
| 200 |
pk BIGINT AUTO_INCREMENT, |
| 201 |
PRIMARY KEY(pk)); |
| 202 |
INSERT INTO t1 (d,c) VALUES (0.5,'foo'); |
| 203 |
Warnings: |
| 204 |
Note 1265 Data truncated for column 'd' at row 1 |
| 205 |
SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd; |
| 206 |
d c vd vc pk |
| 207 |
1 foo 1 foo 1 |
| 208 |
DROP TABLE t1; |
| 209 |
CREATE TABLE t1 ( |
| 210 |
pk BIGINT, |
| 211 |
c CHAR(64) NOT NULL DEFAULT '', |
| 212 |
vc CHAR(64) AS (c) VIRTUAL, |
| 213 |
PRIMARY KEY(pk), |
| 214 |
INDEX(vc(32)) |
| 215 |
); |
| 216 |
DELETE FROM t1 WHERE vc IS NULL ORDER BY pk; |
| 217 |
DROP TABLE t1; |
| 218 |
SET sql_mode=''; |
| 219 |
CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM; |
| 220 |
INSERT INTO t1 SELECT 1 FROM seq_1_to_2 ; |
| 221 |
Warnings: |
| 222 |
Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored |
| 223 |
Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored |
| 224 |
SELECT * FROM t1; |
| 225 |
a |
| 226 |
1 |
| 227 |
1 |
| 228 |
DROP TABLE t1; |
| 229 |
CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM; |
| 230 |
INSERT INTO t1 SELECT 1 UNION SELECT 1; |
| 231 |
Warnings: |
| 232 |
Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored |
| 233 |
SELECT * FROM t1; |
| 234 |
a |
| 235 |
1 |
| 236 |
DROP TABLE t1; |
| 237 |
# |
| 238 |
# MDEV-11737 Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N |
| 239 |
# |
| 240 |
CREATE TABLE t1 (i INT PRIMARY KEY, vi INT AS (i*2) VIRTUAL UNIQUE) |
| 241 |
ENGINE=InnoDB; |
| 242 |
CREATE TABLE t2 (i INT) ENGINE=InnoDB; |
| 243 |
ALTER TABLE t1 ADD COLUMN col INT; |
| 244 |
SELECT * FROM t1 WHERE vi < 2; |
| 245 |
i vi col |
| 246 |
DROP TABLE t1, t2; |
| 247 |
create table t1 ( |
| 248 |
pk int auto_increment, |
| 249 |
col_varchar varchar(847) not null default '', |
| 250 |
col_int bigint(15) unsigned zerofill, |
| 251 |
col_datetime datetime(3) not null default '1900-01-01 00:00:00', |
| 252 |
col_time time(5) not null default '00:00:00', |
| 253 |
col_blob text, |
| 254 |
col_bit bit(34), |
| 255 |
col_year year, |
| 256 |
col_char char(10), |
| 257 |
col_dec decimal(18,9) not null default 0, |
| 258 |
col_enum enum('','a','b','c','d','e','f','foo','bar') not null default '', |
| 259 |
col_date date not null default '1900-01-01', |
| 260 |
col_timestamp timestamp(3) not null default '1971-01-01 00:00:00', |
| 261 |
vcol_datetime datetime as (truncate(col_datetime,0)) virtual, |
| 262 |
vcol_dec decimal(18,9) zerofill as (col_dec) virtual, |
| 263 |
vcol_bit bit(63) as (col_bit) virtual, |
| 264 |
vcol_char binary(51) as (col_char) virtual, |
| 265 |
vcol_timestamp timestamp(5) as (col_timestamp) virtual, |
| 266 |
vcol_enum enum('','a','b','c','d','e','f','foo','bar') as (col_enum) virtual, |
| 267 |
vcol_int tinyint(48) zerofill as (col_int) virtual, |
| 268 |
vcol_time time(4) as (col_time) virtual, |
| 269 |
vcol_varchar varbinary(3873) as (col_varchar) virtual, |
| 270 |
vcol_year year as (col_year) virtual, |
| 271 |
vcol_date date as (col_date) virtual, |
| 272 |
vcol_blob longtext as (col_blob) virtual, |
| 273 |
primary key(pk) |
| 274 |
) engine=innodb; |
| 275 |
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 |
| 276 |
('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'), |
| 277 |
('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'); |
| 278 |
alter table t1 add index(vcol_datetime); |
| 279 |
drop table t1; |
| 280 |
create table t1 ( |
| 281 |
pk int, |
| 282 |
col_blob mediumtext not null default '', |
| 283 |
vcol_blob tinyblob as (col_blob) virtual, |
| 284 |
col_char char(22) null, |
| 285 |
primary key(pk), |
| 286 |
index(col_char,vcol_blob(64)) |
| 287 |
) engine=innodb; |
| 288 |
insert ignore into t1 (pk) values (1),(2); |
| 289 |
update t1 set col_char = 'foo' where pk = 1; |
| 290 |
drop table t1; |
| 291 |
create table t1 ( |
| 292 |
id int not null primary key, |
| 293 |
a varchar(200), |
| 294 |
b varchar(200), |
| 295 |
c int, |
| 296 |
va char(200) generated always as (ucase(a)) virtual, |
| 297 |
vb char(200) generated always as (ucase(b)) virtual, |
| 298 |
key (c,va,vb) |
| 299 |
) engine=innodb; |
| 300 |
insert t1 (id,a,c) select seq,seq,seq from seq_1_to_330; |
| 301 |
select IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE') from t1; |
| 302 |
IF(@@innodb_sort_buffer_size < count(*)*200, 'GOOD', 'WRONG SIZE') |
| 303 |
GOOD |
| 304 |
alter table t1 drop column va; |
| 305 |
drop table t1; |
| 306 |
# |
| 307 |
# MDEV-39081 InnoDB: tried to purge non-delete-marked record, |
| 308 |
# assertion fails in row_purge_del_mark_error |
| 309 |
# |
| 310 |
CREATE TABLE t1( |
| 311 |
pk INT AUTO_INCREMENT, |
| 312 |
f1 INT DEFAULT 0, |
| 313 |
f2 INT as (1 + 1) VIRTUAL, |
| 314 |
KEY (f1), PRIMARY KEY (pk), UNIQUE (f2)) ENGINE=InnoDB; |
| 315 |
START TRANSACTION WITH CONSISTENT SNAPSHOT; |
| 316 |
CONNECT con1,localhost,root,,; |
| 317 |
INSERT INTO t1 (pk) VALUES(1); |
| 318 |
DELETE FROM t1; |
| 319 |
INSERT INTO t1 (pk) VALUES(1); |
| 320 |
UPDATE t1 set f1 = 2; |
| 321 |
disconnect con1; |
| 322 |
connection default; |
| 323 |
COMMIT; |
| 324 |
InnoDB 0 transactions not purged |
| 325 |
DROP TABLE t1; |