| 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; |