Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.2.14, 5.3.12, 5.5.40, 10.0.10, 10.1.1
-
Component/s: Dynamic Columns, Virtual Columns
-
Labels: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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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;
commited for review
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...
Your patch will go to the tree so I assign it to you.
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.