Bug #101685 Spatial generated columns + index + load data + default

• Submitted: 19 Nov 2020 17:22
• Modified: 20 Nov 2020 13:27
• Reporter: Александр Ммммммм
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.22
• OS: Any
• CPU Architecture: Any

[19 Nov 2020 17:22] Александр Ммммммм

Description:
Complex problem that I can't create SPATIAL nullable index

How to repeat:
File C:/1/1.txt
--------------
60.3030	50.2020
--------------
DROP TEMPORARY TABLE IF EXISTS tmp
;
CREATE TEMPORARY TABLE tmp (lat DECIMAL(10,7), lon DECIMAL(10,7))
;
INSERT tmp () VALUES ()
;
LOAD DATA LOCAL INFILE 'C:/1/1.txt'
INTO TABLE tmp
;
-- I can't create SPATIAL nullable index
ALTER TABLE tmp
ADD point POINT GENERATED ALWAYS AS (st_srid(ifnull(point(lon,lat),point(0,0)),4326)) STORED NOT NULL SRID 4326
, ADD SPATIAL INDEX (point)
;
SELECT *
FROM tmp
;
LOAD DATA LOCAL INFILE 'C:/1/1.txt'
INTO TABLE tmp
;
[22001][1263] Data truncation: Column set to default value; NULL supplied to NOT NULL column 'point' at row 1

[20 Nov 2020 13:27] MySQL Verification Team

Hi Mr. Mmmmmmm,

Thank you for your bug report.

However, this is not a bug.

You have defined your spatial column as NOT NULL. All NOT NULL attributes, regardless of the data type, do not accept NULL values, by its own definition.

Not a bug.