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