WL#11808: Make the SPATIAL keyword optional in index creation

Currently, users have to specify that an index should be spatial, e.g.:

CREATE SPATIAL INDEX idx ON table (geometry_column);

If the user doesn't specify SPATIAL, a B-tree index is created. B-tree indexes on geometry columns aren't useful. The only reasonable index to create on a geometry column is an R-tree index.

This WL makes the SPATIAL keyword optional. If the column is a geometry column (column type GEOMETRY, POINT, LINESTRING, POLYGON, GEOMCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON), an R-tree index is created.

The WL also makes it impossible to use geometry columns in primary keys and unique indexes. It also makes it impossible to create prefix indexes on geometry columns.

In the following, unless otherwise specified, CREATE INDEX is used to mean any statement that creates a spatial index, including, but not limited to:

• CREATE INDEX
• CREATE KEY
• ALTER TABLE ADD INDEX
• ALTER TABLE ADD KEY
• CREATE TABLE (<column name> <column type> INDEX ...)
• CREATE TABLE (<column name> <column type> KEY ...)
• CREATE TABLE (..., INDEX (<column names>), ...)
• CREATE TABLE (..., KEY (<column names>), ...)

F-1

CREATE INDEX MUST create a spatial index if the index is over one non-nullable geometry column, and the index has no prefix specification, no USING clause, no ASC or DESC clause, and is not a FULLTEXT or UNIQUE index.

F-2

CREATE INDEX MUST raise ER_TOO_MANY_KEY_PARTS if the index is over multiple columns and one or more columns are geometry columns.

F-3

CREATE INDEX MUST raise ER_WRONG_SUB_KEY if the index is over one geometry column and is a prefix index.

F-4

CREATE INDEX MUST raise ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX if the index is over one geometry column and an index type is specified with USING BTREE or USING HASH.

F-5

CREATE INDEX MUST raise ER_WRONG_USAGE if the index is over one geometry column and ASC or DESC is specified.

F-6

CREATE INDEX MUST raise ER_BAD_FT_COLUMN if the index is over one geometry column and is a fulltext index.

F-7

CREATE INDEX MUST raise ER_SPATIAL_CANT_HAVE_NULL if the index is over one geometry column and the column is nullable.

F-8

CREATE INDEX MUST raise ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN if the index is over one geometry column and the column is a virtual generated column.

F-9

CREATE INDEX MUST raise ER_SPATIAL_UNIQUE_INDEX if the index is over one geometry column and is a UNIQUE index.

F-10

CREATE TABLE and ALTER TABLE MUST raise ER_SPATIAL_UNIQUE_INDEX if a geometry column is used in the primary key.

F-11

If multiple error conditions are fulfilled, it is undefined which error is raised.

I-1

No new files.

I-2

No new syntax. Interface SQL01 is modified so that the SPATIAL keyword is optional in statements that create indexes.

I-3

No new commands.

I-4

No new tools.

I-5

Interface ERR01 is extended with 2 new error message:
ER_SPATIAL_UNIQUE_INDEX, SQLSTATE HY000, "Spatial indexes can't be primary or unique indexes."
ER_INDEX_TYPE_NOT_SUPPORTED_FOR_SPATIAL_INDEX, SQLSTATE HY000, "The index type %.20s is not supported for spatial indexes."