[MDEV-7968] Virtual column set to NULL using load data infile Created: 2015-04-09  Updated: 2015-07-27  Resolved: 2015-07-27

Status: Closed
Project: MariaDB Server
Component/s: Virtual Columns
Affects Version/s: 10.0.16
Fix Version/s: 10.0.21

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
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



 Comments   
Comment by Elena Stepanova [ 2015-04-09 ]

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)

Generated at Thu Feb 08 07:23:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.