Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.16
-
Fix Version/s: 10.0.21
-
Component/s: Virtual Columns
-
Labels:None
Description
Hello,
Can't find in documentation why it behaving like in the following test case.
create table t1 ( c1 varchar(10), c2 varchar(10), c3 int ); insert into t1 values ("a" , "b", 1), ("a" , "b", 2); create table t2 like t1 ; alter table t2 add column c4 bigint unsigned as (CONV(LEFT(MD5(concat(c1,c2,c3)), 16), 16, 10)) persistent unique key; select * into outfile 't1.csv' from t1; load data infile 't1.csv' into table t2 ; select * from t2; select "Wrong c4"; insert into t2 (c1,c2,c3) values ("a" , "b", 4); select * from t2; select "correct c4";
a b 1 NULL a b 2 NULL a b 4 15541743660496249717
Also seeing [GENERATED ALWAYS] in documentation but can't see any description of such keyword .
Thanks
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I could swear the issue with virtual columns not being populated upon LOAD DATA was raised before, but I can't find it anywhere.
Even if the LOAD DATA statement above is fixed to specify non-virtual columns (to get rid of the warnings about not enough data), the virtual column still ends up with NULL values.
It is not so for "normal" columns with default values, they get populated all right.
MariaDB [test]> CREATE TABLE `t3` ( -> `c1` varchar(10) DEFAULT NULL, -> `c2` varchar(10) DEFAULT NULL, -> `c3` int(11) DEFAULT NULL, -> `c4` bigint(20) unsigned AS (CONV(LEFT(MD5(concat(c1,c2,c3)), 16), 16, 10)) PERSISTENT, -> `c5` bigint(20) unsigned null default 0 -> ); Query OK, 0 rows affected (4.61 sec) MariaDB [test]> load data infile 't1.csv' into table t3 (c1,c2,c3); Query OK, 2 rows affected (1.41 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [test]> select * from t3; +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +------+------+------+------+------+ | a | b | 1 | NULL | 0 | | a | b | 2 | NULL | 0 | +------+------+------+------+------+ 2 rows in set (0.00 sec)