Wrong column added when alter table with additional vector column

[chiacy]

Wrong column added when alter table with additional vector column

[tomatolog]

could you provide the stream of the create table or alter table statements that recreates the issue here locally?

[chiacy]

hi!

create table

create TABLE rt10 (
id bigint,
title string attribute,
alias text,
shortlink text,
video_url text,
audio text,
edited string attribute,
img string,
caption text,
slider text,
summary text,
points text,
content text,
keywords string attribute,
status integer,
audioflag integer,
created timestamp,
updated timestamp,
type string attribute,
language string attribute,
pcategory string attribute,
category string attribute,
options string attribute,
flash string attribute,
tags string attribute,
author string attribute,
source string attribute,
vector float_vector knn_type='hnsw' knn_dims='1024' hnsw_similarity='cosine'
) html_strip='1' charset_table='non_cjk, U+3400..U+4DBF, U+4E00..U+9FFF, U+20000..U+2A6DF, U+2A700..U+2B73F, U+2B740..U+2B81F, U+2B820..U+2CEAF, U+F900..U+FAFF, U+2F800..U+2FA1F' blend_chars='-, +, &->+' morphology='stem_en, icu_chinese' wordform='/etc/manticoresearch/masterWordForm.txt';

then subsequently

alter table rt10 add column vector_zh float_vector knn_type='hnsw' knn_dims='768' hnsw_similarity='cosine';

then result of show create table rt10

CREATE TABLE rt10 (
id bigint,
alias text,
shortlink text,
video_url text,
audio text,
caption text,
slider text,
summary text,
points text,
content text,
title string attribute,
edited string attribute,
img string attribute,
keywords string attribute,
status integer,
audioflag integer,
created timestamp,
updated timestamp,
type string attribute,
language string attribute,
pcategory string attribute,
category string attribute,
options string attribute,
flash string attribute,
tags string attribute,
author string attribute,
source string attribute,
vector float_vector knn_type='hnsw' knn_dims='1024' hnsw_similarity='COSINE',
vector_zh float_vector knn_type='hnsw' knn_dims='0' hnsw_similarity='L2'
) html_strip='1' charset_table='non_cjk, U+3400..U+4DBF, U+4E00..U+9FFF, U+20000..U+2A6DF, U+2A700..U+2B73F, U+2B740..U+2B81F, U+2B820..U+2CEAF, U+F900..U+FAFF, U+2F800..U+2FA1F' blend_chars='-, +, &->+' morphology='stem_en, icu_chinese'

if I create the index directly with 2 vector columns of different dimensions:

create TABLE rt20 (
id bigint,
title string attribute,
alias text,
shortlink text,
video_url text,
audio text,
edited string attribute,
img string,
caption text,
slider text,
summary text,
points text,
content text,
keywords string attribute,
status integer,
audioflag integer,
created timestamp,
updated timestamp,
type string attribute,
language string attribute,
pcategory string attribute,
category string attribute,
options string attribute,
flash string attribute,
tags string attribute,
author string attribute,
source string attribute,
vector float_vector knn_type='hnsw' knn_dims='1024' hnsw_similarity='cosine',
vector_zh float_vector knn_type='hnsw' knn_dims='768' hnsw_similarity='cosine'
) html_strip='1' charset_table='non_cjk, U+3400..U+4DBF, U+4E00..U+9FFF, U+20000..U+2A6DF, U+2A700..U+2B73F, U+2B740..U+2B81F, U+2B820..U+2CEAF, U+F900..U+FAFF, U+2F800..U+2FA1F' blend_chars='-, +, &->+' morphology='stem_en, icu_chinese' wordform='/etc/manticoresearch/masterWordForm.txt';

it then shows correctly when show create table rt20

CREATE TABLE rt20 (
id bigint,
alias text,
shortlink text,
video_url text,
audio text,
caption text,
slider text,
summary text,
points text,
content text,
title string attribute,
edited string attribute,
img string attribute,
keywords string attribute,
status integer,
audioflag integer,
created timestamp,
updated timestamp,
type string attribute,
language string attribute,
pcategory string attribute,
category string attribute,
options string attribute,
flash string attribute,
tags string attribute,
author string attribute,
source string attribute,
vector float_vector knn_type='hnsw' knn_dims='1024' hnsw_similarity='COSINE',
vector_zh float_vector knn_type='hnsw' knn_dims='768' hnsw_similarity='COSINE'
) html_strip='1' charset_table='non_cjk, U+3400..U+4DBF, U+4E00..U+9FFF, U+20000..U+2A6DF, U+2A700..U+2B73F, U+2B740..U+2B81F, U+2B820..U+2CEAF, U+F900..U+FAFF, U+2F800..U+2FA1F' blend_chars='-, +, &->+' morphology='stem_en, icu_chinese'

is this what you need?

[sanikolaev]

MRE

mysql> drop table if exists t; create table t ( f1 float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='l2' ); alter table t add column f2 float_vector knn_type='hnsw' knn_dims='3' hnsw_similarity='l2'; show create table t\G
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t ( f1 float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='l2' )
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
alter table t add column f2 float_vector knn_type='hnsw' knn_dims='3' hnsw_similarity='l2'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
show create table t
--------------

*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
id bigint,
f1 float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='L2',
f2 float_vector knn_type='hnsw' knn_dims='0' hnsw_similarity='L2'
)
1 row in set (0.00 sec)

Expected non-zero dimensions:

Create Table: CREATE TABLE t (
id bigint,
f1 float_vector knn_type='hnsw' knn_dims='2' hnsw_similarity='L2',
f2 float_vector knn_type='hnsw' knn_dims='3' hnsw_similarity='L2'
)