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

SHOW CREATE TABLE produces unusable statement for vector fields with constant default value

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • N/A
    • 11.7.1
    • Vector search
    • None

    Description

      I don't suppose it will be particularly important for real use of vectors, as it's unlikely that their default values will be set as constants (too long), but it can be problematic for users getting familiar with MariaDB's implementation of vectors.

      7fce19bd215ac0671855044520092aa4210049d1

      		 
      MariaDB [db]> create table t (v vector(1) default 0x30303030);
      		 
      Query OK, 0 rows affected (1.524 sec)
      		 
       
      		 
      MariaDB [db]> show create table t;
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                            |
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      		 
      | t     | CREATE TABLE `t` (
      		 
        `v` vector(1) DEFAULT '0000'
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci |
      		 
      +-------+-------------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.001 sec)


      MariaDB [db]> drop table t;
      		 
      Query OK, 0 rows affected (0.054 sec)
      		 
       
      		 
      MariaDB [db]> CREATE TABLE `t` (
      		 
          ->   `v` vector(1) DEFAULT '0000'
      		 
          -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
      		 
          -> ;
      		 
      ERROR 1067 (42000): Invalid default value for 'v'

      Consequently, an attempt to load a dump produced by mariadb-dump (regardless of hex-blob, as it doesn't affect CREATE) fails the same way.

      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

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Summary SHOW CREATE TABLE produces unusable statement for vector fields with default value, mariadb-dump restoration fails SHOW CREATE TABLE produces unusable statement for vector fields with constant default value
            serg Sergei Golubchik added a comment -

            Same with binary/varbinary:

            create table t (v vector(1) default 0x01020304, i int default 1, b varbinary(5) default 0x06050403);
            		 
            show create table t;
            		 
            drop table t;

            serg Sergei Golubchik added a comment - Same with binary/varbinary: create table t (v vector(1) default 0x01020304, i int default 1, b varbinary(5) default 0x06050403); show create table t; drop table t;
            elenst Elena Stepanova added a comment - - edited

            Yes, of course. But for binary it's at least not unusable. Admittedly it can cause problems (wrong results) upon copy/pasting, but it does work for dump, for example:

            create table t (i int default 1, b varbinary(5) default 0x06050403);
            		 
            create database db;
            		 
            --exec $MYSQL_DUMP test | $MYSQL db
            		 
             
            		 
            insert into db.t (i) values (1);
            		 
            select hex(b) from db.t;


            insert into db.t (i) values (1);
            		 
            select hex(b) from db.t;
            		 
            hex(b)
            		 
            06050403

            While with vector it fails with ER_INVALID_DEFAULT.

            elenst Elena Stepanova added a comment - - edited Yes, of course. But for binary it's at least not unusable . Admittedly it can cause problems (wrong results) upon copy/pasting, but it does work for dump, for example: create table t (i int default 1, b varbinary(5) default 0x06050403); create database db; --exec $MYSQL_DUMP test | $MYSQL db   insert into db.t (i) values (1); select hex(b) from db.t; insert into db.t (i) values (1); select hex(b) from db.t; hex(b) 06050403 While with vector it fails with ER_INVALID_DEFAULT.
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.7.1 [ 29913 ]
            Fix Version/s 11.7 [ 29815 ]
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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