[#MDEV-6179] dynamic columns functions/cast()/convert() doesn't play nice with CREATE/ALTER TABLE

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)

[MDEV-6179] dynamic columns functions/cast()/convert() doesn't play nice with CREATE/ALTER TABLE Created: 2014-04-26 Updated: 2014-11-19 Resolved: 2014-11-19
Status:	Closed
Project:	MariaDB Server
Component/s:	Dynamic Columns, Virtual Columns
Affects Version/s:	5.2.14, 5.3.12, 5.5.40, 10.0.10, 10.1.1
Fix Version/s:	5.5.41, 10.0.15