Bug #91893 LOAD DATA INFILE throws error with NOT NULL column defined via SET
Submitted: 3 Aug 14:31 Modified: 6 Aug 17:16
Reporter: A ? Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.12 OS:Other
Assigned to: CPU Architecture:Any

[3 Aug 14:31] A ?
Description:
I have a table with a geometry column (geom POINT NOT NULL SRID 32631), and two numeric columns (x double DEFAULT NULL, y double DEFAULT NULL).

In MySQL 5.7, I used LOAD DATA INFILE to populate the table from a file containing only x and y values, with this command :

LOAD DATA INFILE 'file.csv' INTO TABLE foo
(@x, @y)
SET x = @x, y = @y, geom = POINT(@x, @y);

This worked fine, but it does not work anymore in 8.0.12.
The command immediately throws an error 

How to repeat:
Create a texte '1.txt' file containing two numeric column values. (i.e 1234\t6545)
Then :
CREATE TABLE foo (x double, y double, geom POINT NOT NULL SRID 32631);
LOAD DATA INFILE '1.txt' 
INTO TABLE foo
(@x, @y) SET x = @x, y = @y, geom = Point(@x, @y);

-> ERROR 1364 (HY000): Field 'geom' doesn't have a default value
[5 Aug 17:45] Miguel Solorzano
Thank you for the bug report. Which 5.7 version it worked for you?

C:\tmp>mysql-5.7.23-winx64\bin\mysql -uroot -p test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 TABLE foo (x double, y double, geom POINT NOT NULL SRID 32631);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SRID 32631)' at line 1
mysql>

Thanks.
[5 Aug 17:57] A ?
Hi. This will not work because SRID support was added in 8.0, but the same command should work without the "SRID 32631" in the column definition in 5.7, and fail in 8.0.
I can tell you which 5.7 version tomorrow.
Thanks for looking into this !
[5 Aug 18:26] Miguel Solorzano
Thank you for the feedback. Are you able to insert that row (with same data of the file) using the mysql client, please print here. Thanks in advance.
[6 Aug 6:56] A ?
Yes, I can insert without any problem. It's only the LOAD DATA INFILE that fails.

mysql> insert into foo (x,y,geom) SELECT 1, 1, ST_SRID(Point(1, 1), 32631);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
[6 Aug 6:59] A ?
Just so that you have a valid test case :

mysql> CREATE TABLE foo (x double, y double, geom POINT NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA INFILE '1.txt'
    -> INTO TABLE foo
    -> (@x, @y) SET x = @x, y = @y, geom = Point(@x, @y);
ERROR 1364 (HY000): Field 'geom' doesn't have a default value

mysql> INSERT INTO foo (x, y, geom) VALUES (1, 1, Point(1, 1));
Query OK, 1 row affected (0.00 sec)
[6 Aug 13:29] A ?
Well, according to this page : https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html
we will be able to set a default value for types BLOB, TEXT, GEOMETRY, and JSON.
So, this would definitively fix my problem, but when will 8.0.13 be released ?
I am in a quite desperate need for a fix to this problem.

Thanks !
[6 Aug 17:16] Miguel Solorzano
Thank you for the feedback. Verified as described.