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

Virtual column type cannot be converted from one to another - unhelpful error message

    XMLWordPrintable

    Details

      Description

      Unable to convert virtual to generated in MariaDB as I can in MySQL:

      MariaDB [test]> show create table  bad_with_key\G
      *************************** 1. row ***************************
             Table: bad_with_key
      Create Table: CREATE TABLE `bad_with_key` (
        `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
        `id_md5` char(32) GENERATED ALWAYS AS (md5(`id`)) VIRTUAL,
        `f` float DEFAULT NULL,
        `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        UNIQUE KEY `id` (`id`,`id_md5`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      1 row in set (0.000 sec)
       
      MariaDB [test]> alter table bad_with_key change column id_len id_md5 binary(16) generated always as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1054 (42S22): Unknown column 'id_len' in 'bad_with_key'
      MariaDB [test]> alter table bad_with_key modify  column id_md5 binary(16) generated always as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
      MariaDB [test]> alter table bad_with_key modify column id_md5 char(32) generated always as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
      MariaDB [test]> alter table bad_with_key modify column id_md5 char(32) generated always as (md5(id)) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
      MariaDB [test]> alter table bad_with_key modify  column id_md5 binary(16) as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
      MariaDB [test]> alter table bad_with_key modify  column id_md5 char(32) as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
      MariaDB [test]> alter table bad_with_key drop index id;
      Query OK, 0 rows affected (0.030 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      MariaDB [test]> alter table bad_with_key modify  column id_md5 binary(16) generated always as (unhex(md5(id))) PERSISTENT AFTER id;
      ERROR 1907 (HY000): This is not yet supported for generated columns
       
      MariaDB [test]> show create table bad_with_key
          -> \G
      *************************** 1. row ***************************
             Table: bad_with_key
      Create Table: CREATE TABLE `bad_with_key` (
        `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
        `id_md5` char(32) GENERATED ALWAYS AS (md5(`id`)) VIRTUAL,
        `f` float DEFAULT NULL,
        `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      1 row in set (0.000 sec)
       
      MariaDB [test]> alter table bad_with_key drop column  id_md5;
      Query OK, 0 rows affected (0.035 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> alter table bad_with_key add column id_md5 binary(16) generated always as (unhex(md5(id))) PERSISTENT AFTER id;
      Query OK, 8 rows affected (0.059 sec)
      Records: 8  Duplicates: 0  Warnings: 0
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              manjot Manjot Singh
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: