Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.2.14, 5.3.12, 5.5.40, 10.0.10, 10.1.1
-
None
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)
|