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

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

          Ascending order - Click to sort in descending order
          kolbe Kolbe Kegel (Inactive) created issue -
          kolbe Kolbe Kegel (Inactive) made changes -
          Field Original Value New Value
          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)           		I thought it would be neat to combine dynamic columns and virtual columns, but it isn't going so well.

          {noformat}
          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;
          {noformat}

          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.

          {noformat}
          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)
          {noformat}
          elenst Elena Stepanova added a comment -

          If it's really not supported, it would be safer to produce an error, and it should also be documented.

          I'm not sure whether it's a vcol issue or dyncol issue, assigned to Igor assuming it's the former. Please reassign as needed.

          elenst Elena Stepanova added a comment - If it's really not supported, it would be safer to produce an error, and it should also be documented. I'm not sure whether it's a vcol issue or dyncol issue, assigned to Igor assuming it's the former. Please reassign as needed.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.12 [ 15201 ]
          Assignee Igor Babaev [ igor ]
          kolbe Kolbe Kegel (Inactive) added a comment -

          I think it's quite important to support this operation. This is an important piece of the NoSQL puzzle in MariaDB, IMO.

          Note that this "works" if you use a column of type TEXT, instead.

          kolbe Kolbe Kegel (Inactive) added a comment - I think it's quite important to support this operation. This is an important piece of the NoSQL puzzle in MariaDB, IMO. Note that this "works" if you use a column of type TEXT, instead.
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          sanja Oleksandr Byelkin made changes -
          Summary get_columns() doesn't play nice with virtual columns get_columns() doesn't play nice with dynamic columns
          sanja Oleksandr Byelkin made changes -
          Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 39825 ] MariaDB v2 [ 42998 ]
          sanja Oleksandr Byelkin made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin added a comment -

          It looks like new alter table ignore that the field is virtual and trying to put real data in it.

          sanja Oleksandr Byelkin added a comment - It looks like new alter table ignore that the field is virtual and trying to put real data in it.
          sanja Oleksandr Byelkin made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.13 [ 16000 ]
          Fix Version/s 10.0.12 [ 15201 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.13 [ 16300 ]
          Fix Version/s 10.0 [ 16000 ]
          sanja Oleksandr Byelkin made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.14 [ 17101 ]
          Fix Version/s 10.0.13 [ 16300 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.0.14 [ 17101 ]
          kolbe Kolbe Kegel (Inactive) added a comment -

          Is there any progress on this issue?

          kolbe Kolbe Kegel (Inactive) added a comment - Is there any progress on this issue?
          sanja Oleksandr Byelkin made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          sanja Oleksandr Byelkin made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          sanja Oleksandr Byelkin made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin made changes -
          Affects Version/s 5.5.40 [ 17100 ]
          sanja Oleksandr Byelkin added a comment -

          The test suite for 5.5:

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

          select item_name, color from assets;

          drop table assets;

          sanja Oleksandr Byelkin added a comment - The test suite for 5.5: create table assets ( item_name varchar(32) primary key, – A common attribute for all items dynamic_cols blob, – Dynamic columns will be stored here color char(32) as (left(COLUMN_GET(dynamic_cols, 1 as char),32)) persistent ); INSERT INTO assets(item_name, dynamic_cols) VALUES ('MariaDB T-shirt', COLUMN_CREATE(1, 'blue', 2, 'XL')); INSERT INTO assets(item_name, dynamic_cols) VALUES ('Thinkpad Laptop', COLUMN_CREATE(1, 'black', 3, 500)); select item_name, color from assets; drop table assets;
          sanja Oleksandr Byelkin added a comment -

          The real problem is in the parser.

          CREATE TABLE and COLUMN_GET uses both the same global variables for parsing types (lex->length, lex->dec, lex->type)

          sanja Oleksandr Byelkin added a comment - The real problem is in the parser. CREATE TABLE and COLUMN_GET uses both the same global variables for parsing types (lex->length, lex->dec, lex->type)
          sanja Oleksandr Byelkin added a comment -

          It was not COLUMN_GET but
          cast_type:

          sanja Oleksandr Byelkin added a comment - It was not COLUMN_GET but cast_type:
          sanja Oleksandr Byelkin added a comment - - edited

          and cast type is from COLUMN_GET

          sanja Oleksandr Byelkin added a comment - - edited and cast type is from COLUMN_GET
          sanja Oleksandr Byelkin added a comment - - edited

          The problem is older then it seemd to be:

          create table t1 (
          		 
            n int,
          		 
            c char(32) as (cast(n as char)) persistent
          		 
          );
          		 
           
          		 
          insert into t1(n) values (1),(2),(3);
          		 
           
          		 
          select * from t1;
          		 
           
          		 
          show create table t1;
          		 
           
          		 
          drop t1;

          sanja Oleksandr Byelkin added a comment - - edited The problem is older then it seemd to be: create table t1 ( n int , c char (32) as ( cast (n as char )) persistent );   insert into t1(n) values (1),(2),(3);   select * from t1;   show create table t1;   drop t1;
          sanja Oleksandr Byelkin made changes -
          Summary get_columns() doesn't play nice with dynamic columns get_columns()/cast()/convert() doesn't play nice with CREATE/ALTER TABLE
          sanja Oleksandr Byelkin made changes -
          Summary get_columns()/cast()/convert() doesn't play nice with CREATE/ALTER TABLE dynamic columns functions/cast()/convert() doesn't play nice with CREATE/ALTER TABLE
          sanja Oleksandr Byelkin made changes -
          Fix Version/s 10.1.2 [ 15801 ]
          Fix Version/s 10.0.15 [ 17300 ]
          Fix Version/s 5.5.41 [ 17600 ]
          Fix Version/s 10.0 [ 16000 ]
          sanja Oleksandr Byelkin made changes -
          Affects Version/s 5.3.12 [ 12000 ]
          Affects Version/s 5.2.14 [ 12101 ]
          sanja Oleksandr Byelkin made changes -
          Affects Version/s 10.1.1 [ 16801 ]
          sanja Oleksandr Byelkin added a comment -

          commited for review

          sanja Oleksandr Byelkin added a comment - commited for review
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          sanja Oleksandr Byelkin added a comment -

          I think it is possible to reproduce the bug on earlier version if play with CONVERT/CAST recursive functions but I doubts in practical sens of it...

          sanja Oleksandr Byelkin added a comment - I think it is possible to reproduce the bug on earlier version if play with CONVERT/CAST recursive functions but I doubts in practical sens of it...
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
          sanja Oleksandr Byelkin added a comment -

          Your patch will go to the tree so I assign it to you.

          sanja Oleksandr Byelkin added a comment - Your patch will go to the tree so I assign it to you.
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Status In Review [ 10002 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Status Stalled [ 10000 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.1.2 [ 15801 ]
          serg Sergei Golubchik made changes -
          Component/s Dynamic Columns [ 10103 ]
          Component/s Virtual Columns [ 10803 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 42998 ] MariaDB v3 [ 65335 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 65335 ] MariaDB v4 [ 147844 ]

          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.