Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35338

Non-copying ALTER does not pad VECTOR column, vector search further does not work

    XMLWordPrintable

Details

    Description

      If the length of vector column is increased on an InnoDB table while there is no yet vector key on the table, it is performed as a non-copying ALTER by default, and leaves the column values intact, without extra padding.

      After that, vector distance function comparing the column to values of the new length returns NULL (with or without vector key, all the same), which leads to meaningless vector search results.

      Rebuilding the table with FORCE, ALGORITHM=COPY after the fact does not help.
      CHECK TABLE doesn't detect the discrepancy.

      --source include/have_innodb.inc
      		 
       
      		 
      create or replace table t (a int, v vector(1) not null, primary key (a)) engine=InnoDB;
      		 
      insert into t values (1,0x38383838),(2,0x37373737),(3,0x31313131);
      		 
       
      		 
      alter table t modify v vector(2) not null;
      		 
      select hex(v) from t;
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
      alter table t add vector(v);
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
       
      		 
      alter table t force, algorithm=copy;
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
       
      		 
      check table t extended;
      		 
       
      		 
      # Cleanup
      		 
      drop table t;

      f5c717cd90418370807463dbfb95bcd05bed76dc

      		 
      alter table t modify v vector(2) not null;
      		 
      select hex(v) from t;
      		 
      hex(v)
      		 
      38383838
      		 
      37373737
      		 
      31313131
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
      a	d
      		 
      1	NULL
      		 
      2	NULL
      		 
      alter table t add vector(v);
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
      a	d
      		 
      1	NULL
      		 
      2	NULL
      		 
      alter table t force, algorithm=copy;
      		 
      select a, vec_distance_euclidean(v,0x3030303030303030) d from t order by d limit 2;
      		 
      a	d
      		 
      1	NULL
      		 
      2	NULL
      		 
      check table t extended;
      		 
      Table	Op	Msg_type	Msg_text
      		 
      test.t	check	status	OK

      The workaround is to use ALGORITHM=COPY in the initial ALTER modifying the column, but it will be hard to guess/remember in advance.

      Attachments

        Issue Links

          is caused by

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34939 vector search in 11.7

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.