mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 13:23:07 +01:00
268 lines
9.8 KiB
Text
268 lines
9.8 KiB
Text
create table t1 (id int auto_increment primary key, v vector(5) 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');
|
|
select vec_totext(x'aabbcc');
|
|
vec_totext(x'aabbcc')
|
|
NULL
|
|
Warnings:
|
|
Warning 4204 Invalid binary vector format. Must use IEEE standard float representation in little-endian format. Use VEC_FromText() to generate it.
|
|
select vec_totext(x'0000f07f');
|
|
vec_totext(x'0000f07f')
|
|
[NaN]
|
|
select vec_totext(x'0000f0ff');
|
|
vec_totext(x'0000f0ff')
|
|
[NaN]
|
|
select vec_totext(x'0000807f');
|
|
vec_totext(x'0000807f')
|
|
[Inf]
|
|
select vec_totext(x'000080ff');
|
|
vec_totext(x'000080ff')
|
|
[-Inf]
|
|
select hex(vec_fromtext('["a"]'));
|
|
hex(vec_fromtext('["a"]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 4 for '["a"]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[]'));
|
|
hex(vec_fromtext('[]'))
|
|
|
|
select hex(vec_fromtext('["a"]'));
|
|
hex(vec_fromtext('["a"]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 4 for '["a"]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[{"a": "b"}]'));
|
|
hex(vec_fromtext('[{"a": "b"}]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 2 for '[{"a": "b"}]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[null]'));
|
|
hex(vec_fromtext('[null]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 5 for '[null]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[1, null]'));
|
|
hex(vec_fromtext('[1, null]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 8 for '[1, null]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[1, ["a"]]'));
|
|
hex(vec_fromtext('[1, ["a"]]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 5 for '[1, ["a"]]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[1, [2]]'));
|
|
hex(vec_fromtext('[1, [2]]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 5 for '[1, [2]]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('{"a":"b"}'));
|
|
hex(vec_fromtext('{"a":"b"}'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 1 for '{"a":"b"}'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[1, 2, "z", 3]'));
|
|
hex(vec_fromtext('[1, 2, "z", 3]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 10 for '[1, 2, "z", 3]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[1, 2, 3'));
|
|
hex(vec_fromtext('[1, 2, 3'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 8 for '[1, 2, 3'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('1, 2, 3]'));
|
|
hex(vec_fromtext('1, 2, 3]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 1 for '1, 2, 3]'. Must be a valid JSON array of numbers.
|
|
select hex(vec_fromtext('[]'));
|
|
hex(vec_fromtext('[]'))
|
|
|
|
select vec_totext(x'');
|
|
vec_totext(x'')
|
|
[]
|
|
select id, vec_totext(t1.v) as a, vec_totext(vec_fromtext(vec_totext(t1.v))) as b,
|
|
vec_distance_euclidean(t1.v, vec_fromtext(vec_totext(t1.v))) < 0.000001 as c
|
|
from t1;
|
|
id a b c
|
|
1 [0.418708,0.809902,0.823193,0.598179,0.0332549] [0.418708,0.809902,0.823193,0.598179,0.0332549] 1
|
|
2 [0.687774,0.789588,0.496138,0.57487,0.917617] [0.687774,0.789588,0.496138,0.57487,0.917617] 1
|
|
3 [0.333221,0.962687,0.467263,0.448235,0.475671] [0.333221,0.962687,0.467263,0.448235,0.475671] 1
|
|
4 [0.822185,0.185643,0.683452,0.211072,0.554056] [0.822185,0.185643,0.683452,0.211072,0.554056] 1
|
|
5 [0.437057,0.167281,0.0770977,0.428638,0.241591] [0.437057,0.167281,0.0770977,0.428638,0.241591] 1
|
|
6 [0.76956,0.926895,0.803376,0.0157961,0.589042] [0.76956,0.926895,0.803376,0.0157961,0.589042] 1
|
|
7 [0.493999,0.641957,0.761598,0.94276,0.425865] [0.493999,0.641957,0.761598,0.94276,0.425865] 1
|
|
8 [0.924108,0.275466,0.0543329,0.0731585,0.136344] [0.924108,0.275466,0.0543329,0.0731585,0.136344] 1
|
|
9 [0.186956,0.69666,0.0356002,0.668875,0.84722] [0.186956,0.69666,0.0356002,0.668875,0.84722] 1
|
|
10 [0.415294,0.609278,0.426765,0.988832,0.475556] [0.415294,0.609278,0.426765,0.988832,0.475556] 1
|
|
drop table t1;
|
|
create table t1 as select vec_totext(x'55555555') x;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` varchar(15) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
select * from t1;
|
|
x
|
|
[1.46602e13]
|
|
drop table t1;
|
|
#
|
|
# MDEV-35212 Server crashes in Item_func_vec_fromtext::val_str upon query from empty table
|
|
#
|
|
select vec_fromtext(NULL);
|
|
vec_fromtext(NULL)
|
|
NULL
|
|
#
|
|
# MDEV-35210 Vector type cannot store values which VEC_FromText produces and VEC_ToText accepts
|
|
#
|
|
select vec_totext(0x77777777);
|
|
vec_totext(0x77777777)
|
|
[5.01922e33]
|
|
select hex(vec_fromtext('[5.01922e33]'));
|
|
hex(vec_fromtext('[5.01922e33]'))
|
|
NULL
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect vector value: '[5.01922e33]'
|
|
#
|
|
# MDEV-35215 ASAN errors in Item_func_vec_fromtext::val_str upon VEC_FROMTEXT with an invalid argument
|
|
#
|
|
create table t (v vector(1));
|
|
insert into t values (vec_fromtext('[0.93]'));
|
|
select vec_fromtext(v) from t;
|
|
vec_fromtext(v)
|
|
NULL
|
|
Warnings:
|
|
Warning 4205 Invalid vector format at offset: 1 for '{\0014n?'. Must be a valid JSON array of numbers.
|
|
drop table t;
|
|
select vec_fromtext(0x00000000);
|
|
vec_fromtext(0x00000000)
|
|
NULL
|
|
Warnings:
|
|
Warning 4036 Character disallowed in JSON in argument 1 to function 'VEC_FromText' at position 1
|
|
#
|
|
# MDEV-35220 Assertion `!item->null_value' failed upon VEC_TOTEXT call
|
|
#
|
|
select vec_totext(`null`) from (values (null),(0x00000000)) x;
|
|
vec_totext(`null`)
|
|
NULL
|
|
[0]
|
|
#
|
|
# MDEV-36011 Server crashes in Charset::mbminlen / Item_func_vec_fromtext::val_str upon mixing vector type with string
|
|
#
|
|
select 0x31313131 in ('1111', vec_fromtext('[1]'));
|
|
0x31313131 in ('1111', vec_fromtext('[1]'))
|
|
1
|
|
# End of 11.7 tests
|
|
#
|
|
# MDEV-35450 VEC_DISTANCE() function to autouse the available index type
|
|
#
|
|
create table t1 (a int primary key, b vector(5) not null, vector index (b) distance=euclidean);
|
|
insert t1 values (0,vec_fromtext('[1,2,3,4,5]')), (1,vec_fromtext('[2,2,3,4,5]')),
|
|
(2,vec_fromtext('[1,3,3,4,5]')), (3,vec_fromtext('[1,2,4,4,5]')),
|
|
(4,vec_fromtext('[1,2,4,5,5]'));
|
|
create table t2 (c int primary key, d vector(5) not null, vector index (d) distance=cosine);
|
|
insert t2 select * from t1;
|
|
create table t3 (e int primary key, f vector(5) not null);
|
|
insert t3 select * from t1;
|
|
select a,c,vec_distance(b,d),vec_distance_euclidean(b,d) from t1, t2 where a=(c+11)*13%5;
|
|
a c vec_distance(b,d) vec_distance_euclidean(b,d)
|
|
3 0 1 1
|
|
1 1 0 0
|
|
4 2 1.73205 1.73205
|
|
2 3 1.41421 1.41421
|
|
0 4 1.41421 1.41421
|
|
select a,c,vec_distance(d,b),vec_distance_cosine(b,d) from t1, t2 where a=(c+11)*13%5;
|
|
a c vec_distance(d,b) vec_distance_cosine(b,d)
|
|
3 0 0.00676 0.00676
|
|
1 1 0 0
|
|
4 2 0.01943 0.01943
|
|
2 3 0.01626 0.01626
|
|
0 4 0.00784 0.00784
|
|
select a,e,vec_distance(b,f),vec_distance_euclidean(b,f) from t1, t3 where a=(e+11)*13%5;
|
|
a e vec_distance(b,f) vec_distance_euclidean(b,f)
|
|
3 0 1 1
|
|
1 1 0 0
|
|
4 2 1.73205 1.73205
|
|
2 3 1.41421 1.41421
|
|
0 4 1.41421 1.41421
|
|
select e,c,vec_distance(f,d),vec_distance_cosine(d,f) from t2, t3 where e=(c+11)*13%5;
|
|
e c vec_distance(f,d) vec_distance_cosine(d,f)
|
|
3 0 0.00676 0.00676
|
|
1 1 0 0
|
|
4 2 0.01943 0.01943
|
|
2 3 0.01626 0.01626
|
|
0 4 0.00784 0.00784
|
|
select a,vec_distance(b,vec_fromtext('[5,4,3,2,1]')),vec_distance_euclidean(b,vec_fromtext('[5,4,3,2,1]')) from t1;
|
|
a vec_distance(b,vec_fromtext('[5,4,3,2,1]')) vec_distance_euclidean(b,vec_fromtext('[5,4,3,2,1]'))
|
|
0 6.32455 6.32455
|
|
1 5.74456 5.74456
|
|
2 6.08276 6.08276
|
|
3 6.40312 6.40312
|
|
4 6.78232 6.78232
|
|
select c,vec_distance(d,vec_fromtext('[5,4,3,2,1]')),vec_distance_cosine(d,vec_fromtext('[5,4,3,2,1]')) from t2;
|
|
c vec_distance(d,vec_fromtext('[5,4,3,2,1]')) vec_distance_cosine(d,vec_fromtext('[5,4,3,2,1]'))
|
|
0 0.36363 0.36363
|
|
1 0.29178 0.29178
|
|
2 0.32109 0.32109
|
|
3 0.34926 0.34926
|
|
4 0.35989 0.35989
|
|
select e,vec_distance(f,vec_fromtext('[5,4,3,2,1]')) from t3;
|
|
ERROR HY000: Cannot determine distance type for VEC_DISTANCE, index is not found
|
|
drop table t1, t2, t3;
|
|
#
|
|
# Item_func_vec_distance::do_get_copy()
|
|
#
|
|
create table t1 (a vector(1) not null, vector(a));
|
|
create algorithm=temptable view v1 as select * from t1;
|
|
select * from v1 where vec_distance(a,0x30303030) > 0;
|
|
a
|
|
drop view v1;
|
|
drop table t1;
|
|
#
|
|
# MDEV-35724 VEC_DISTANCE does not work in HAVING clause
|
|
#
|
|
create table t (v vector(1) not null, vector(v));
|
|
insert t values (0x31313131),(0x32323232);
|
|
select v from t having vec_distance(v,0x30303030) > 0;
|
|
v
|
|
1111
|
|
2222
|
|
drop table t;
|
|
#
|
|
# MDEV-35752 VEC_DISTANCE does not work in triggers
|
|
#
|
|
create table t (id int primary key default 1, v vector(1) not null default 0x30303030, vector(v), d float);
|
|
create trigger tr before insert on t for each row set new.d = vec_distance(new.v,0x30303030);
|
|
insert t (v) values (0x31313131);
|
|
select vec_distance(default(v), 0x31313131) from t;
|
|
vec_distance(default(v), 0x31313131)
|
|
0.00000
|
|
insert t (v) values (0x32323232) on duplicate key update d=vec_distance(values(v), 0x31313131);
|
|
drop table t;
|
|
#
|
|
# MDEV-35778 Server crashes in Item_func_vec_distance::fix_length_and_dec upon using VEC_DISTANCE with temptable view
|
|
#
|
|
create table t (x vector(1) not null, vector(x));
|
|
insert into t values (0x31313131),(0x32323232);
|
|
create algorithm=temptable view v as select * from t;
|
|
select * from v order by vec_distance(0x30303030, x);
|
|
x
|
|
1111
|
|
2222
|
|
drop view v;
|
|
drop table t;
|
|
#
|
|
# MDEV-35919 Server crashes in Item_func_vec_distance::fix_length_and_dec upon reading from I_S table
|
|
#
|
|
select vec_distance(value(key_cache_name), 0x30303030) from information_schema.key_caches;
|
|
ERROR HY000: Cannot determine distance type for VEC_DISTANCE, index is not found
|
|
# End of 11.8 tests
|