Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7968

Virtual column set to NULL using load data infile

Details

    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

      Attachments

        Activity

          elenst Elena Stepanova added a comment - - edited

          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)

          elenst Elena Stepanova added a comment - - edited 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)

          People

            serg Sergei Golubchik
            stephane@skysql.com VAROQUI Stephane
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.