Bug #113515 LOAD DATA INFILE fails with GENERATED ALWAYS Point COLUMN

• Submitted: 27 Dec 2023 0:53
• Modified: 29 Dec 2023 13:30
• Reporter: James Watmuff
• Status: Verified
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.2.0
• OS: MacOS (homebrew)
• CPU Architecture: ARM

[27 Dec 2023 0:53] James Watmuff

Description:
When a table contains a GENERATED ALWAYS column of type Point, it produces an error when attempting to populate the table using LOAD DATA LOCAL INFILE.

This bug makes it impossible to load a dump using the MySQL Shell for such tables, since that uses the LOAD DATA LOCAL INFILE statement. This is how I originally encountered the issue.

How to repeat:
Create a filed named "test.csv" consisting of the following line only:

1.0,1.0

Now run the following SQL statements:

CREATE TABLE test (
  lat double NOT NULL,
  lon double NOT NULL,
  location Point GENERATED ALWAYS AS (Point(lon, lat)) STORED NOT NULL SRID 0
);

LOAD DATA LOCAL INFILE 'test.csv' REPLACE INTO TABLE test CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES STARTING BY '' TERMINATED BY '\n' (lat,lon);

This produces the error:

ERROR 1364 (HY000): Field 'location' doesn't have a default value

[29 Dec 2023 13:30] MySQL Verification Team

Hello James Watmuff,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil