MySQL Bugs: #78579: load data infile does not populate stored generated columns

Bug #78579	load data infile does not populate stored generated columns
Submitted:	26 Sep 2015 22:42	Modified:	28 Sep 2015 0:44
Reporter:	James Dempster	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7.8-rc	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When loading data into a table which has a generated column, specifically through "LOAD DATA INFILE" this doesn't generate and store the value as expected. Instead the value for the column comes out as NULL until a change to the row occurs.

How to repeat:
CREATE TABLE `tt` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `details` json DEFAULT NULL,
  `details_name` VARCHAR(30) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details,"$.name"))) STORED,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

LOAD DATA INFILE '/tmp/tt' INTO TABLE tt (id, details);

Added MySQL version.

Thank you for the bug report. Repeatable with released version 5.7.8 but not anymore with current server source build.

c:\mysql\mysql-5.7.8-rc-winx64>bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database d1;
Query OK, 1 row affected (0.00 sec)

mysql> use d1
Database changed
mysql> CREATE TABLE `tt` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `details` json DEFAULT NULL,
    ->   `details_name` VARCHAR(30) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details,"$.name"))) STORED,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.53 sec)

mysql> LOAD DATA INFILE 'c:/tmp/tt.dat' INTO TABLE tt (id, details);
Query OK, 15000 rows affected (4.02 sec)
Records: 15000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tt limit 2\G
*************************** 1. row ***************************
          id: 1
     details: {"age": 18, "ice": {"age": 31, "name": "Shannon Johnson", "text": "Est odit id accusantium autem. Assumenda cumqu
e fugit et eligendi explicabo. Sit nulla aut soluta officia.", "email": "Quentin84@example.net", "address": "Studio 52b\nStefan
 Burg\nSouth Phoebe\nX5 9VF", "company": "Davis, Hunt and Palmer"}, "name": "Caitlin Wright", "text": "Facere consectetur incid
unt voluptatibus voluptas laudantium velit. Ipsum sit eum commodi odio sed aut earum tempore. Placeat est laudantium maxime fug
it dolorem.", "email": "Gordon.Williams@example.org", "address": "Studio 88\nGreen Junctions\nWilliamborough\nY0 5NH", "company
": "Cooper, Cook and Rose"}
details_name: NULL
*************************** 2. row ***************************
          id: 2
     details: {"age": 37, "ice": {"age": 33, "name": "Isobel James", "text": "Explicabo occaecati maxime totam sit et et. Corpo
ris dolores rem aspernatur et nobis nostrum. Sapiente accusamus nihil consectetur. Similique autem aut qui dolorem veritatis in
ventore voluptatibus.", "email": "Tim.Turner@example.org", "address": "Studio 07o\nOwen Spring\nNicoleborough\nE2 6JU", "compan
y": "Clarke-Knight"}, "name": "Theo Robinson", "text": "Dolores molestiae recusandae ab et perspiciatis sunt qui. Doloremque pr
aesentium laudantium quia mollitia. Dolorem quia quis modi perferendis aut.", "email": "Miller.Barry@example.org", "address": "
27 Leanne Valley\nNorth Claire\nR80 4WF", "company": "Powell-Mitchell"}
details_name: NULL
2 rows in set (0.00 sec)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
c:\dbs>5.7\bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 Source distribution PULL: 2015-SEP-26

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database d1;
Query OK, 1 row affected (0.25 sec)

mysql> use d1
Database changed
mysql> CREATE TABLE `tt` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `details` json DEFAULT NULL,
    ->   `details_name` VARCHAR(30) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details,"$.name"))) STORED,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.42 sec)

mysql> LOAD DATA INFILE 'c:/tmp/tt.dat' INTO TABLE tt (id, details);
Query OK, 15000 rows affected (4.42 sec)
Records: 15000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tt limit 2\G
*************************** 1. row ***************************
          id: 1
     details: {"age": 18, "ice": {"age": 31, "name": "Shannon Johnson", "text": "Est odit id accusantium autem. Assumenda cumqu
e fugit et eligendi explicabo. Sit nulla aut soluta officia.", "email": "Quentin84@example.net", "address": "Studio 52b\nStefan
 Burg\nSouth Phoebe\nX5 9VF", "company": "Davis, Hunt and Palmer"}, "name": "Caitlin Wright", "text": "Facere consectetur incid
unt voluptatibus voluptas laudantium velit. Ipsum sit eum commodi odio sed aut earum tempore. Placeat est laudantium maxime fug
it dolorem.", "email": "Gordon.Williams@example.org", "address": "Studio 88\nGreen Junctions\nWilliamborough\nY0 5NH", "company
": "Cooper, Cook and Rose"}
details_name: Caitlin Wright
*************************** 2. row ***************************
          id: 2
     details: {"age": 37, "ice": {"age": 33, "name": "Isobel James", "text": "Explicabo occaecati maxime totam sit et et. Corpo
ris dolores rem aspernatur et nobis nostrum. Sapiente accusamus nihil consectetur. Similique autem aut qui dolorem veritatis in
ventore voluptatibus.", "email": "Tim.Turner@example.org", "address": "Studio 07o\nOwen Spring\nNicoleborough\nE2 6JU", "compan
y": "Clarke-Knight"}, "name": "Theo Robinson", "text": "Dolores molestiae recusandae ab et perspiciatis sunt qui. Doloremque pr
aesentium laudantium quia mollitia. Dolorem quia quis modi perferendis aut.", "email": "Miller.Barry@example.org", "address": "
27 Leanne Valley\nNorth Claire\nR80 4WF", "company": "Powell-Mitchell"}
details_name: Theo Robinson
2 rows in set (0.04 sec)