vcol_keys_myisam.result 8.26 KB
Newer Older
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
create table t1 (a int, b int as (a+1), c int, index(b));
insert t1 (a,c) values (0x7890abcd, 0x76543210);
insert t1 (a,c) select seq, sin(seq)*10000 from seq_1_to_1000;
explain select * from t1 where b=10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	b	b	5	const	1	
select * from t1 where b=10;
a	b	c
9	10	4121

MyISAM file:         datadir/test/t1
Record format:       Fixed length
Character set:       latin1_swedish_ci (8)
Data records:                 1001  Deleted blocks:                 0
Recordlength:                    9

table description:
Key Start Len Index   Type
1   10    4   multip. long NULL            
update t1 set a=20 where b=10;
select * from t1 where b=10;
a	b	c
select * from t1 where b=21;
a	b	c
20	21	4121
20	21	9129
delete from t1 where b=21;
select * from t1 where b=21;
a	b	c
alter table t1 add column d char(20) as (concat(a,c));
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
create index i on t1 (d);
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
check table t1 quick;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
check table t1 medium;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
check table t1 extended;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	1	b	1	b	A	999	NULL	NULL	YES	BTREE		
t1	1	i	1	d	A	999	NULL	NULL	YES	BTREE		
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
delete from t1 where b=12;
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	1	b	1	b	A	998	NULL	NULL	YES	BTREE		
t1	1	i	1	d	A	998	NULL	NULL	YES	BTREE		
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	1	b	1	b	A	998	NULL	NULL	YES	BTREE		
t1	1	i	1	d	A	998	NULL	NULL	YES	BTREE		
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
repair table t1 quick;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
repair table t1 extended;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
repair table t1 use_frm;
Table	Op	Msg_type	Msg_text
test.t1	repair	warning	Number of rows changed from 0 to 998
test.t1	repair	status	OK
select * from t1 where b=11;
a	b	c	d
10	11	-5440	10-5440
update t1 set a=30 where b=11;
select * from t1 where b=11;
a	b	c	d
select * from t1 where b=31;
a	b	c	d
30	31	-5440	30-5440
30	31	-9880	30-9880
drop table t1;
117 118 119 120 121 122 123 124 125
SET @@session.storage_engine = 'MyISAM';
#            - UNIQUE KEY
#            - INDEX
#            - FULLTEXT INDEX
#            - SPATIAL INDEX (not supported)
#            - FOREIGN INDEX (partially supported)
#            - CHECK (allowed but not used)
# UNIQUE
create table t1 (a int, b int as (a*2) unique);
126
drop table t1;
127 128 129 130 131
create table t1 (a int, b int as (a*2) persistent unique);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
132
  `b` int(11) AS ((`a` * 2)) PERSISTENT,
133 134 135 136 137
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
138
b	int(11)	YES	UNI	NULL	PERSISTENT
139 140
drop table t1;
create table t1 (a int, b int as (a*2), unique key (b));
141
drop table t1;
142 143 144 145 146
create table t1 (a int, b int as (a*2) persistent, unique (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
147
  `b` int(11) AS ((`a` * 2)) PERSISTENT,
148 149 150 151 152
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
153
b	int(11)	YES	UNI	NULL	PERSISTENT
154 155 156 157 158 159 160 161 162 163 164 165 166 167
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add unique key (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add unique key (b);
drop table t1;
# Testing data manipulation operations involving UNIQUE keys 
# on virtual columns can be found in:
#  - vcol_ins_upd.inc
#  - vcol_select.inc
# 
# INDEX
create table t1 (a int, b int as (a*2), index (b));
168
drop table t1;
169
create table t1 (a int, b int as (a*2), index (a,b));
170
drop table t1;
171 172 173 174 175
create table t1 (a int, b int as (a*2) persistent, index (b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
176
  `b` int(11) AS ((`a` * 2)) PERSISTENT,
177 178 179 180 181
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
182
b	int(11)	YES	MUL	NULL	PERSISTENT
183 184 185 186 187 188
drop table t1;
create table t1 (a int, b int as (a*2) persistent, index (a,b));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
189
  `b` int(11) AS ((`a` * 2)) PERSISTENT,
190 191 192 193 194
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
describe t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES	MUL	NULL	
195
b	int(11)	YES		NULL	PERSISTENT
196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215
drop table t1;
create table t1 (a int, b int as (a*2));
alter table t1 add index (b);
alter table t1 add index (a,b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (b);
drop table t1;
create table t1 (a int, b int as (a*2) persistent);
alter table t1 add index (a,b);
create table t2 like t1;
drop table t2;
drop table t1;
# Testing data manipulation operations involving INDEX
# on virtual columns can be found in:
#  - vcol_select.inc
#
# TODO: FULLTEXT INDEX
# SPATIAL INDEX
# Error "All parts of a SPATIAL index must be NOT NULL"
216
create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
217 218 219
ERROR 42000: All parts of a SPATIAL index must be NOT NULL
create table t1 (a int, b int as (a+1) persistent);
alter table t1 add spatial index (b);
Michael Widenius's avatar
Michael Widenius committed
220
ERROR HY000: Incorrect arguments to SPATIAL INDEX
221 222 223 224 225
drop table t1;
# FOREIGN KEY
# Rejected FK options.
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update set null);
Igor Babaev's avatar
Igor Babaev committed
226
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a computed column
227 228
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on update cascade);
Igor Babaev's avatar
Igor Babaev committed
229
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a computed column
230 231
create table t1 (a int, b int as (a+1) persistent,
foreign key (b) references t2(a) on delete set null);
Igor Babaev's avatar
Igor Babaev committed
232
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a computed column
233 234
create table t1 (a int, b int as (a+1) persistent);
alter table t1 add foreign key (b) references t2(a) on update set null;
Igor Babaev's avatar
Igor Babaev committed
235
ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a computed column
236
alter table t1 add foreign key (b) references t2(a) on update cascade;
Igor Babaev's avatar
Igor Babaev committed
237
ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a computed column
238
alter table t1 add foreign key (b) references t2(a) on delete set null;
Igor Babaev's avatar
Igor Babaev committed
239
ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a computed column
240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264
drop table t1;
# Allowed FK options.
create table t2 (a int primary key, b char(5));
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on update no action);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete restrict);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete cascade);
drop table t1;
create table t1 (a int, b int as (a % 10) persistent,
foreign key (b) references t2(a) on delete no action);
drop table t1;

# Testing data manipulation operations involving FOREIGN KEY 
# on virtual columns can be found in:
#  - vcol_ins_upd.inc
#  - vcol_select.inc
#
# TODO: CHECK