Bug #88972 Can't create a generated column with NOT NULL in one step

• Submitted: 19 Dec 2017 20:22
• Modified: 4 Apr 2018 12:24
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.3, 5.7.20
• OS: Any
• CPU Architecture: Any

[19 Dec 2017 20:22] Daniël van Eeden

Description:
Take a table with two columns: latitude and longitude.

Now add a point column with an index.

This needs two alters instead of one.
1. Add a stored generated column
2. Make the column NOT NULL and add the index.

Also the error message is not very clear.

How to repeat:
mysql> CREATE TABLE t1 (id SERIAL PRIMARY KEY, lat DOUBLE, lon DOUBLE);
Query OK, 0 rows affected (1.40 sec)

mysql> INSERT INTO t1(lat, lon) VALUES (0, 10),(10,10),(20,20);
Query OK, 3 rows affected (0.34 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD COLUMN latlon point AS (POINT(lat,lon)) STORED NOT NULL, ADD SPATIAL INDEX (latlon);
ERROR 1138 (22004): Invalid use of NULL value
mysql> ALTER TABLE t1 ADD COLUMN latlon point AS (POINT(lat,lon)) STORED, ADD SPATIAL INDEX (latlon);
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL
mysql> ALTER TABLE t1 ADD COLUMN latlon point AS (POINT(lat,lon)) STORED;
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 MODIFY COLUMN latlon point AS (POINT(lat,lon)) STORED NOT NULL, ADD SPATIAL INDEX(latlon);
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

Suggested fix:
Make it possible to do this in one ALTER statement.

[20 Dec 2017 6:38] MySQL Verification Team

Hello Daniël,

Thank you for the report and test case.
Observed with 5.7.20.

Thanks,
Umesh

[4 Apr 2018 12:24] Erlend Dahl

Fixed in 8.0.11 under the heading of

Bug#27252609 UNABLE TO CREATE INDEX WHEN SPATIAL DATATYPES ARE USED WITH REQUIRED OPTION