WL#8592: SRID type modifier for geometric types

Affects: Server-8.0   —   Status: Complete

This worklog adds a new column property for geometric types to specify the
SRID:

  CREATE TABLE t1 (g GEOMETRY SRID 4326, p POINT SRID 0 NOT NULL);

Values inserted into a column with an SRID property must be in that SRID.
Attempts to insert values with other SRIDs results in an exception condition
being raised. Unmodified types, i.e., types with no SRID specification, will
continue to accept all SRIDs, as before.

The optimizer is changed so that only indexes on columns with the SRID specified
will be considered in query planning/execution. The specified SRID is exposed
in both INFORMATION_SCHEMA.GEOMETRY_COLUMNS and INFORMATION_SCHEMA.COLUMNS.

This WL implements:

 - An SRID column property (SRID as a non-reserved word in the parser).
 - A check to verify that data stored in the column by INSERT, UPDATE,
   ALTER TABLE, etc. is in the correct SRID
 - Restriction on MyISAM so that the SRID specified MUST be a cartesian
   coordinate system.
 - Optimizer restrictions to not use SRID-less indexes
 - Change to INFORMATION_SCHEMA.GEOMETRY_COLUMNS and INFORMATION_SCHEMA.COLUMNS
F-1: The parser MUST accept the following new column definition property:

       [SRID uint32_t]

F-2: If the SRID property value is not set, the server MUST accept geometries
     with any SRID in this column. This also includes a mix of SRIDs.

F-3: If the SRID property is set to a valid SRID, the server MUST reject
     inserting any geometries with a different SRID in this column and raise
     the error ER_WRONG_SRID_FOR_COLUMN.

F-4: If the SRS represented by the SRID property does not exist, the server
     MUST raise the error ER_SRS_NOT_FOUND.

F-5: If a user tries to create a column with a SRID property that does not
     represents a Cartesian coordinate system (projected SRS or SRID 0) on a
     MyISAM table, the server MUST raise the error ER_CHECK_NOT_IMPLEMENTED.

F-6: If a user tries to change a geometry column definition to a SRID value that
     does not match the existing data in the column, the server MUST raise
     the error ER_WRONG_SRID_FOR_COLUMN.

F-7: The SRID property MUST be available for generated columns as well as
     regular columns.

F-8: The value of the SRID property MUST be in the range from 0 to 4294967295
     inclusive.

F-9: The SRID type modifier for a column MUST be visible in the system views
     INFORMATION_SCHEMA.columns and INFORMATION_SCHEMA.st_geometry_columns.

F-10: Any spatial indexes that references a geometry column without the SRID
      property defined MUST be hidden from the optimizer during query planning
      and execution.

Inspecting the SRID property
----------------------------
We will expose the SRID property trough the information schema tables
INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS.
SHOW CREATE TABLE will also be extended, and it will give the SRID property with
a version comment:

  `col1` point NOT NULL /*!80003 SRID 4326 */

SRID 0
------
We want to support the following column definition:

  CREATE TABLE t1 (col1 POINT SRID 0);

However, we do not have a spatial reference system in the server 
(mysql.st_spatial_reference_systems) with ID 0. So in order to support this
case we will add a new spatial reference system with ID 0 to
mysql.st_spatial_reference_systems. The definition, name etc will be an empty
string, and should not be used for anything else than foreign key constraint
checking.

New error message
-----------------
As stated in F-6, the server should raise the new error message
ER_WRONG_SRID_FOR_COLUMN. The text for this error message will be the following:

  "The SRID of the geometry does not match the SRID of the column '%.64s'. The
  SRID of the geometry is %lu, but the SRID of the column is %lu. Consider
  changing the SRID of the geometry or the SRID property of the column."

Dictionary tables
-----------------
We will add a new column to the dictionary table mysql.columns with the
following definiton:

  srs_id INTEGER UNSIGNED DEFAULT NULL

If this column is set to NULL, it means that the column will allow any SRID to
be stored in this column, as well as a mix of SRIDs. If this is set to an
non-NULL value, it means that all geometries in this column MUST be in the given
SRID.

We will also add a foreign key constraint between this newly added column and
the existing dictionary table mysql.st_spatial_reference_systems:

  FOREIGN KEY (srs_id) REFERENCES st_spatial_reference_systems (id)


System views
------------
Two views will be updated to output information from this column:

INFORMATION_SCHEMA.columns
INFORMATION_SCHEMA.st_geometry_columns

They will both show the SRID, and "st_geometry_columns" will also show the
name of the spatial reference system as well.

Field hierarchy
---------------
The class Field_geom will be expanded with one new member variable:

class Field_geom
{
private:
  const Nullable<gis::srid_t> m_srid;

public:
  Nullable<gis::srid_t> get_srid() const { return m_srid; }
};

Then, the function Field_geom::store_internal() will be expanded so that if
the SRID is set, we will check that the SRID of the geometry actually
matches the SRID of this field. If not, we will raise the error
ER_WRONG_SRID_FOR_COLUMN.

Alter table
-----------
If we are changing the SRID property of a column from anything to a valid SRID,
we must force the ALTER command to use the COPY algorithm. This must be done
in order to verify that the data in the modified column actually matches the
SRID of the column. However, if we are changing from anything to a column
without the SRID property, it is possible to do with using the INPLACE algorithm
since this won't require any validation checks of the data.

The function is_inplace_alter_impossible() in sql_table.cc will be modified to
handle this.

Parser changes
--------------
We will add a new non-reserved keyword in the parser: SRID

The parser will be extended to support the new column property [SRID uint32_t].
Any of the following statements will be considered valid:

  CREATE TABLE t1 (col1 POINT SRID 0);
  CREATE TABLE t1 (col1 POINT SRID 4326 NOT NULL);
  CREATE TABLE t1 (col1 POINT SRID 4326,
                   col2 POINT AS (ST_SRID(col1, 0)) SRID 0);

The SRID value MUST be an INTEGER in the range 0 to 4294967295 (inclusive).
A value outside this range or in any other data type MUST return an error to
the client.

The SRID property will be added to the list "column_attribute" in the parser.
If a user tries to use the SRID property on a non-geometry column, the server
will return the error ER_WRONG_USAGE to the client.

Hiding SRID-less indexes
------------------------
The optimizer will be changed so that indexes on geometric columns without the
SRID property specified will not be considered in query planning/execution.
This restriction is made due to the fact that if we have an index on a geometric
column without the SRID property specified , we do not know if the index is
valid or not. In worst case, using it may yield the wrong result.

We will extend fill_indexes_from_dd() in dd_table_share.cc to implement this
restriction. Any index on a SRID-less column will be hidden in the same way
as an invisible index.

Spatial indexes on geometry columns with SRID attribute
-------------------------------------------------------
For geometry columns with the SRID property defined, any spatial index that
is created for this column fetch the SRID from the column definition. This will
ensure that the index is created with the same SRID as the column. Exactly
where in the code this will happen is to be determined.