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

dynamic columns functions/cast()/convert() doesn't play nice with CREATE/ALTER TABLE

    XMLWordPrintable

Details

    Description

      I thought it would be neat to combine dynamic columns and virtual columns, but it isn't going so well.

      create table assets (
        item_name varchar(32) primary key, -- A common attribute for all items
        dynamic_cols  blob  -- Dynamic columns will be stored here
      );
      INSERT INTO assets VALUES 
        ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));
      INSERT INTO assets VALUES
        ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));
       
      alter table assets add column color char(32) as (left(COLUMN_GET(dynamic_cols, 'color' as char),32)) persistent;

      It seems that it ignores the char(32) that I specify and instead uses char(0), thus truncating everything it tries to enter into the column.

      mysql 10.0.10-MariaDB (root) [test]> alter table assets add column color char(32) as (left(COLUMN_GET(dynamic_cols, 'color' as char),32)) persistent;
      Query OK, 2 rows affected, 2 warnings (0.03 sec)
      Records: 2  Duplicates: 0  Warnings: 2
       
      mysql 10.0.10-MariaDB (root) [test]> show warnings;
      +---------+------+--------------------------------------------+
      | Level   | Code | Message                                    |
      +---------+------+--------------------------------------------+
      | Warning | 1265 | Data truncated for column 'color' at row 1 |
      | Warning | 1265 | Data truncated for column 'color' at row 2 |
      +---------+------+--------------------------------------------+
      2 rows in set (0.00 sec)
       
      mysql 10.0.10-MariaDB (root) [test]> show create table assets\G
      *************************** 1. row ***************************
             Table: assets
      Create Table: CREATE TABLE `assets` (
        `item_name` varchar(32) NOT NULL,
        `dynamic_cols` blob,
        `color` char(0) AS (left(COLUMN_GET(dynamic_cols, 'color' as char),32)) PERSISTENT,
        PRIMARY KEY (`item_name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.00 sec)

      Attachments

        Activity

          People

            serg Sergei Golubchik
            kolbe Kolbe Kegel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.