vector.result

create temporary table t1 (id int auto_increment primary key, v blob not null, vector index (v));
ERROR HY000: Cannot create VECTOR index on temporary MyISAM table
create table t1 (id int auto_increment primary key,
u blob not null, vector index (u),
v blob not null, vector index (v));
ERROR 42000: This version of MariaDB doesn't yet support 'multiple VECTOR indexes'
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	Ignored
t1	0	PRIMARY	1	id	A	0	NULL	NULL		BTREE			NO
t1	1	v	1	v	A	NULL	1	NULL		VECTOR			NO
drop table t1;
set mhnsw_max_edges_per_node=@@mhnsw_max_edges_per_node+1;
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`) `max_edges_per_node`=7
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	Ignored
t1	0	PRIMARY	1	id	A	0	NULL	NULL		BTREE			NO
t1	1	v	1	v	A	NULL	1	NULL		VECTOR			NO
drop table t1;
create table t1 (id int auto_increment primary key, v blob not null,
vector index (v) max_edges_per_node=5);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`) `max_edges_per_node`=5
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	Ignored
t1	0	PRIMARY	1	id	A	0	NULL	NULL		BTREE			NO
t1	1	v	1	v	A	NULL	1	NULL		VECTOR			NO
set mhnsw_max_edges_per_node=default;
select * from information_schema.statistics where table_name='t1';
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1
NON_UNIQUE	0
INDEX_SCHEMA	test
INDEX_NAME	PRIMARY
SEQ_IN_INDEX	1
COLUMN_NAME	id
COLLATION	A
CARDINALITY	0
SUB_PART	NULL
PACKED	NULL
NULLABLE	
INDEX_TYPE	BTREE
COMMENT	
INDEX_COMMENT	
IGNORED	NO
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1
NON_UNIQUE	1
INDEX_SCHEMA	test
INDEX_NAME	v
SEQ_IN_INDEX	1
COLUMN_NAME	v
COLLATION	A
CARDINALITY	NULL
SUB_PART	1
PACKED	NULL
NULLABLE	
INDEX_TYPE	VECTOR
COMMENT	
INDEX_COMMENT	
IGNORED	NO
insert t1 (v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
select id, hex(v), vec_totext(v) from t1;
id	hex(v)	vec_totext(v)
1	E360D63EBE554F3FCDBC523F4522193F5236083D	[0.418708,0.809902,0.823193,0.598179,0.033255]
2	F511303F72224A3FDD05FE3EB22A133FFAE86A3F	[0.687774,0.789588,0.496138,0.574870,0.917617]
3	F09BAA3EA172763F123DEF3E0C7FE53E288BF33E	[0.333221,0.962687,0.467263,0.448235,0.475671]
4	B97A523F2A193E3EB4F62E3F2D23583E9DD60D3F	[0.822185,0.185643,0.683452,0.211072,0.554056]
5	F7C5DF3E984B2B3E65E59D3D7376DB3EAC63773E	[0.437057,0.167281,0.077098,0.428638,0.241591]
6	DE01453FFA486D3F10AA4D3FDD66813C71CB163F	[0.769560,0.926895,0.803376,0.015796,0.589042]
7	76EDFC3E4B57243F10F8423FB158713F020BDA3E	[0.493999,0.641957,0.761598,0.942760,0.425865]
8	56926C3FDF098D3E2C8C5E3D1AD4953DAA9D0B3E	[0.924108,0.275466,0.054333,0.073158,0.136344]
9	7B713F3E5258323F80D1113D673B2B3F66E3583F	[0.186956,0.696660,0.035600,0.668875,0.847220]
10	6CA1D43E9DF91B3FE580DA3E1C247D3F147CF33E	[0.415294,0.609278,0.426765,0.988832,0.475556]
flush tables;
select id,vec_distance_euclidean(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
9	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
select id,vec_distance_euclidean(x'b047263C9f87233fcfd27e3eae493e3f0329f43e', v) d from t1 order by d limit 3;
id	d
9	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
select id>0,vec_distance_euclidean(v, NULL) d from t1 order by d limit 3;
id>0	d
1	NULL
1	NULL
1	NULL
select id>0,vec_distance_euclidean(v, x'123456') d from t1 order by d limit 3;
id>0	d
1	NULL
1	NULL
1	NULL
select t1.id as id1, t2.id as id2, vec_distance_euclidean(t1.v, t2.v) from t1, t1 as t2 order by 3,1,2;
id1	id2	vec_distance_euclidean(t1.v, t2.v)
1	1	0
2	2	0
3	3	0
4	4	0
5	5	0
6	6	0
7	7	0
8	8	0
9	9	0
10	10	0
7	10	0.35209010323904116
10	7	0.35209010323904116
1	7	0.557267332724855
7	1	0.557267332724855
2	3	0.6065128837978769
3	2	0.6065128837978769
1	3	0.6128238020507096
3	1	0.6128238020507096
5	8	0.6219995745138945
8	5	0.6219995745138945
3	10	0.6523185662547816
10	3	0.6523185662547816
9	10	0.6732681362788765
10	9	0.6732681362788765
3	7	0.6799892416547949
7	3	0.6799892416547949
3	9	0.6820752294088018
9	3	0.6820752294088018
2	10	0.6916305331777215
10	2	0.6916305331777215
2	9	0.6966650510789955
9	2	0.6966650510789955
3	6	0.7102823580937639
6	3	0.7102823580937639
2	7	0.7120217580666971
7	2	0.7120217580666971
2	6	0.7351618106552689
6	2	0.7351618106552689
1	10	0.7386864491588024
10	1	0.7386864491588024
4	6	0.7784357824370262
6	4	0.7784357824370262
4	8	0.7795837407361241
8	4	0.7795837407361241
4	5	0.8132007346697969
5	4	0.8132007346697969
2	4	0.8260925223296488
4	2	0.8260925223296488
5	10	0.8286488932765299
10	5	0.8286488932765299
5	9	0.8769351333060768
9	5	0.8769351333060768
1	6	0.8861410875047832
6	1	0.8861410875047832
3	5	0.9224201772876247
5	3	0.9224201772876247
4	7	0.9347916246876117
7	4	0.9347916246876117
7	9	0.9364253407685257
9	7	0.9364253407685257
3	4	0.9757105842688992
4	3	0.9757105842688992
1	2	0.9810272439433514
2	1	0.9810272439433514
1	4	0.9965475544626712
4	1	0.9965475544626712
5	7	0.9976863778073342
7	5	0.9976863778073342
4	10	1.0109345944029724
10	4	1.0109345944029724
1	5	1.0208359400987237
5	1	1.0208359400987237
6	7	1.0221332668982412
7	6	1.0221332668982412
2	5	1.050769316594881
5	2	1.050769316594881
6	8	1.103420381318026
8	6	1.103420381318026
3	8	1.1170300826294572
8	3	1.1170300826294572
6	10	1.1523451990991307
10	6	1.1523451990991307
1	9	1.1637750565139302
9	1	1.1637750565139302
2	8	1.1736571017573874
8	2	1.1736571017573874
4	9	1.1746893942711878
9	4	1.1746893942711878
1	8	1.1909959973982214
8	1	1.1909959973982214
8	10	1.209359617652948
10	8	1.209359617652948
6	9	1.214529873940304
9	6	1.214529873940304
5	6	1.227278506501395
6	5	1.227278506501395
8	9	1.2575258643523053
9	8	1.2575258643523053
7	8	1.288239696195716
8	7	1.288239696195716
select * from (
select id,vec_distance_euclidean(v, x'b047263C9F87233fcfd27e3eae493e3f0329f43e') d
from t1 where id < 10
) u order by d limit 3;
id	d
9	0.4719976290006591
3	0.5865673124650332
flush session status;
select id,vec_distance_euclidean(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
9	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	0
select id,vec_distance_euclidean(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 use index () order by d limit 3;
id	d
9	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	11
flush session status;
select id,vec_distance_cosine(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
10	0.05905546376032378
9	0.06546887818344715
3	0.10750282439505232
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	11
delete from t1 where v = x'7b713f3e5258323f80d1113d673b2b3f66e3583f';
select id,vec_distance_euclidean(v, x'B047263C9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
10	0.5069011044450041
3	0.5865673124650332
7	0.7344464697214867
insert t1 (v) values (x'7b713f3e5258323f80d1113d673b2b3f66e3583f');
select id,vec_distance_euclidean(v, x'b047263c9F87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
11	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
select id,vec_distance_euclidean(v, x'B047263c9F87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 5;
id	d
11	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
7	0.7344464697214867
5	0.7671033529042712
update t1 set v=x'76EDFC3E4B57243F10F8423FB158713F020BAA3E' where v=x'6CA1D43E9DF91B3FE580DA3E1C247D3F147CF33E';
select id,vec_distance_euclidean(v, x'B047263C9F87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 5;
id	d
11	0.4719976290006591
3	0.5865673124650332
7	0.7344464697214867
10	0.746836719209219
5	0.7671033529042712
delete from t1;
insert t1 (v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
select id,vec_distance_euclidean(v, x'b047263c9f87233Fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 5;
id	d
20	0.4719976290006591
21	0.5069011044450041
14	0.5865673124650332
18	0.7344464697214867
16	0.7671033529042712
insert t1 (v) values ('');
ERROR 22007: Incorrect vector value: '...' for column `test`.`t1`.`v` at row 1
insert t1 (v) values (x'1234');
ERROR 22007: Incorrect vector value: '...' for column `test`.`t1`.`v` at row 1
insert t1 (v) values (x'12345678');
ERROR 22007: Incorrect vector value: '...' for column `test`.`t1`.`v` at row 1
drop table t1;
db.opt
# Check if CREATE TABLE ... LIKE inherits VECTOR index
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
create table t2 like t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1, t2;
db.opt
# Test insert ... select with vector index
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
create table t2 like t1;
insert t1 (v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
insert into t2 select id+10, v from t1;
insert into t1 select * from t2;
select id, hex(v) from t1;
id	hex(v)
1	E360D63EBE554F3FCDBC523F4522193F5236083D
2	F511303F72224A3FDD05FE3EB22A133FFAE86A3F
3	F09BAA3EA172763F123DEF3E0C7FE53E288BF33E
4	B97A523F2A193E3EB4F62E3F2D23583E9DD60D3F
5	F7C5DF3E984B2B3E65E59D3D7376DB3EAC63773E
6	DE01453FFA486D3F10AA4D3FDD66813C71CB163F
7	76EDFC3E4B57243F10F8423FB158713F020BDA3E
8	56926C3FDF098D3E2C8C5E3D1AD4953DAA9D0B3E
9	7B713F3E5258323F80D1113D673B2B3F66E3583F
10	6CA1D43E9DF91B3FE580DA3E1C247D3F147CF33E
11	E360D63EBE554F3FCDBC523F4522193F5236083D
12	F511303F72224A3FDD05FE3EB22A133FFAE86A3F
13	F09BAA3EA172763F123DEF3E0C7FE53E288BF33E
14	B97A523F2A193E3EB4F62E3F2D23583E9DD60D3F
15	F7C5DF3E984B2B3E65E59D3D7376DB3EAC63773E
16	DE01453FFA486D3F10AA4D3FDD66813C71CB163F
17	76EDFC3E4B57243F10F8423FB158713F020BDA3E
18	56926C3FDF098D3E2C8C5E3D1AD4953DAA9D0B3E
19	7B713F3E5258323F80D1113D673B2B3F66E3583F
20	6CA1D43E9DF91B3FE580DA3E1C247D3F147CF33E
drop table t1, t2;
db.opt
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
insert t1 (id, v) values (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d');
truncate table t1;
insert t1 (id, v) values (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d');
truncate table t1;
insert t1 (id, v) values (1, x'e360d63ebe554f3fcdbc523f4522193f5236083d');
select id, hex(v) from t1;
id	hex(v)
1	E360D63EBE554F3FCDBC523F4522193F5236083D
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
db.opt
# Test RENAME TABLE with vector index
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
rename table t1 to t2;
db.opt
t2#i#01.ibd
t2.frm
t2.ibd
create database test1;
rename table test.t2 to test1.t1;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
call mtr.add_suppression('InnoDB: Cannot rename.*because the source file does not exist');
call mtr.add_suppression('InnoDB: File ./test1/t1#i#01.ibd was not found');
rename table test1.t1 to test1.t2;
Got one of the listed errors
db.opt
t1.frm
t1.ibd
drop database test1;
db.opt
#
# Cosine distance
#
create table t1 (id int auto_increment primary key, v blob not null,
vector index (v) distance_function=cosine);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`) `distance_function`=cosine
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
insert t1 (v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
select id,vec_distance_cosine(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
10	0.05905546376032378
9	0.06546887818344715
3	0.10750282439505232
flush session status;
select id,vec_distance_cosine(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
10	0.05905546376032378
9	0.06546887818344715
3	0.10750282439505232
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	0
select id,vec_distance_cosine(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 use index () order by d limit 3;
id	d
10	0.05905546376032378
9	0.06546887818344715
3	0.10750282439505232
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	11
flush session status;
select id,vec_distance_euclidean(v, x'B047263c9f87233fcfd27e3eae493e3f0329f43e') d from t1 order by d limit 3;
id	d
9	0.4719976290006591
10	0.5069011044450041
3	0.5865673124650332
show status like 'handler_read_rnd_next';
Variable_name	Value
Handler_read_rnd_next	11
drop table t1;
set @a=vec_fromtext('[94.542572,8.735560,60.050098,74.043800,90.068710,28.212160,70.854660,69.636841,35.620232,69.190628]');
select vec_distance_cosine(@a, @a), vec_distance_euclidean(@a, @a);
vec_distance_cosine(@a, @a)	vec_distance_euclidean(@a, @a)
0	0
# Test ALTER TABLE, CREATE/DROP INDEX
create table t1 (id int auto_increment primary key, v blob not null, vector index (v));
insert t1 (v) values (x'e360d63ebe554f3fcdbc523f4522193f5236083d'),
(x'f511303f72224a3fdd05fe3eb22a133ffae86a3f'),
(x'f09baa3ea172763f123def3e0c7fe53e288bf33e'),
(x'b97a523f2a193e3eb4f62e3f2d23583e9dd60d3f'),
(x'f7c5df3e984b2b3e65e59d3d7376db3eac63773e'),
(x'de01453ffa486d3f10aa4d3fdd66813c71cb163f'),
(x'76edfc3e4b57243f10f8423fb158713f020bda3e'),
(x'56926c3fdf098d3e2c8c5e3d1ad4953daa9d0b3e'),
(x'7b713f3e5258323f80d1113d673b2b3f66e3583f'),
(x'6ca1d43e9df91b3fe580da3e1c247d3f147cf33e');
# ADD/DROP COLUMN, ALGORITHM=COPY
alter table t1 add column a int, algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 drop column a, algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# ADD/DROP INDEX, ALGORITHM=COPY (non-vector)
alter table t1 add index a(id), algorithm=copy;
db.opt
t1#i#02.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 drop index a, algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# CREATE/DROP INDEX, ALGORITHM=COPY (non-vector)
create index a on t1(id) algorithm=copy;
db.opt
t1#i#02.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop index a on t1;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# ADD/DROP COLUMN IF [NOT] EXISTS, ALGORITHM=COPY (non-vector)
alter table t1 add column if not exists a int, algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 drop column if exists a, algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# ADD/DROP INDEX, ALGORITHM=COPY (vector)
alter table t1 drop index v, algorithm=copy;
db.opt
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 add vector index v(v), algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# CREATE/DROP INDEX, ALGORITHM=COPY (vector)
drop index v on t1;
db.opt
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
create vector index v on t1(v) algorithm=copy;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# ADD/DROP INDEX, ALGORITHM=INPLACE (non-vector)
alter table t1 add index a(id), algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 add index a(id);
alter table t1 drop index a, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 drop index a;
# ADD/DROP INDEX, ALGORITHM=INPLACE (vector)
alter table t1 drop index v, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 drop index v;
alter table t1 add vector index v(v), algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 add vector index v(v);
# CHANGE/DROP/MODIFY COLUMN, ALGORITHM=INPLACE (vector)
alter table t1 change column v v mediumblob not null, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 drop column v, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
alter table t1 modify column v mediumblob not null, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
# ADD/CHANGE/DROP/MODIFY COLUMN, ALGORITHM=INPLACE (non-vector)
alter table t1 add column a varchar(10), algorithm=inplace;
alter table t1 change column a a varchar(20), algorithm=inplace;
alter table t1 modify column a varchar(30), algorithm=inplace;
alter table t1 drop column a, algorithm=inplace;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# ENABLE/DISABLE INDEXES
alter table t1 disable keys;
alter table t1 enable keys;
Warnings:
Note	1031	Storage engine InnoDB of the table `test`.`t1` doesn't have this option
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# RENAME COLUMN (vector)
alter table t1 rename column v to w;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `w` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`w`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 rename column w to v;
# RENAME INDEX (vector)
alter table t1 rename key v to w;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `w` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 rename key w to v;
# IF [NOT] EXISTS
create vector index if not exists v on t1(v);
Warnings:
Note	1061	Duplicate key name 'v'
drop index if exists v on t1;
drop index if exists v on t1;
Warnings:
Note	1091	Can't DROP INDEX `v`; check that it exists
db.opt
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
create vector index if not exists v on t1(v);
alter table t1 rename key if exists v to w;
alter table t1 rename key if exists w to v;
alter table t1 alter key if exists v ignored;
alter table t1 alter key if exists v not ignored;
# ENGINE
alter table t1 engine=myisam;
db.opt
t1#i#01.MYD
t1#i#01.MYI
t1.MYD
t1.MYI
t1.frm
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 engine=innodb;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 rename to t2, engine=myisam;
db.opt
t2#i#01.MYD
t2#i#01.MYI
t2.MYD
t2.MYI
t2.frm
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t2 rename to t1, engine=innodb;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
# CHANGE/MODIFY/DROP COLUMN (vector)
alter table t1 modify column v int;
ERROR HY000: Incorrect arguments to VECTOR INDEX
alter table t1 change column v v int;
ERROR HY000: Incorrect arguments to VECTOR INDEX
alter table t1 modify column v blob;
ERROR 42000: All parts of a VECTOR index must be NOT NULL
alter table t1 change column v v blob;
ERROR 42000: All parts of a VECTOR index must be NOT NULL
alter table t1 modify column v tinyblob not null;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` tinyblob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 change column v v blob not null;
db.opt
t1#i#01.ibd
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` blob NOT NULL,
  PRIMARY KEY (`id`),
  VECTOR KEY `v` (`v`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
alter table t1 drop column v;
db.opt
t1.frm
t1.ibd
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1;
create table t1(v blob not null, vector index(v));
alter table t1 add column a int;
drop table t1;