WL#10439: Support SRID in InnoDB Spatial Index
Affects: Server-8.0 — Status: Complete
This WL adds SRID support to InnoDB R-tree indexes. Currently, InnoDB R-trees are not restricted to one spatial reference system (SRS), even though it doesn't make sense to compare geometries in different SRSs. Until 8.0, MySQL only supported Cartesian SRSs, so the computations were the same, but in 8.0 there is geography support which requires different formulas for comparing geometries. Therefore, the index must know which SRS it is in in order to work correctly. The SRID shall be obtained from the column definition and never be changed during the lifetime of the column (note the SRID is a column property than an index property). The SRID constraint in the column definition could be NULL, in which case any SRID is allowed. If the SRID is not NULL, any insertion that does not match the SRID will be rejected. For geometry columns, SRID NULL is a valid value. It means that an SRID was not specified in the column definition and that geometries of any SRS may be stored in the column. It does not make sense to have an index of mixed SRSs. However, for backwards compatibility, it is allowed to create (and maintain) indexes for such columns. The index should emulate SRID 0 behavior. The optimizer will never use such indexes. The consistency of SRID across a column and rejecting incorrect SRID data must be implemented so we assume all column has the same SRID. This WL: - Fetches the SRID from the column definition, and cache it in InnoDB index metadata struct (dict_index_t) and passes it on in calls bounding box functions declared in rtree_support.h, thereby enabling geographical indexes. - When create index, InnoDB will do a sanity check that SRID for all rows are of the same type as specified by column. However, it is only a sanity check. The rejection of insert/updates of mixed SRID must be implemented. - Enables geographic computations in Item_func_spatial_realation::val_int.
Requirements from optimizer team will be fulfilled, but not by this worklog, for example: - The SRID of an index MUST be defined in the data dictionary (DD). - It MUST be possible to retrieve the SRID of an index from the DD. - It MUST be possible to retrieve all indexes with a given SRID from the DD. - It MUST be possible to retrieve the SRID of an index from INFORMATION_SCHEMA. - It MUST be possible to identify from INFORMATION_SCHEMA which R-tree indexes were created before the SRID restriction was implemented (and therefore may contain values in any SRID), and which SRIDs have been created after the restriction was implemented. (We need to recreate these indexes in an idempotent step during mysql_upgrade, so we need to separate those that have already been recreated from those that need to be recreated.) - - SRID needs to be consistent across a single column For this worklog, the actual functional and non-functional requirements are: - InnoDB index must be aware of SRID. - The SRID MUST be passed on to geometry computations functions in sql/gis/rtree_support.h. and proper calculate is done. The SRID is cached in dict_index_t when it is generated when parsing DD information. Since dict_index_t cannot be changed without corresponding DD operations, so it is safe to access it for DML.
This worklog supposes do following three things: 1. Add a new "srid" field in dict_index_t. This will be used to pass SRID to all spatial functions/callbacks. 2. During create index time, InnoDB will extract SRID from indexed column from server (key_part->field), and set it in dict_index_t (the cached InnoDB index metadata). Note, we do not set it in se_private_data of dd::Index, since the SRID is persisted in dd::Column (mysql.column), so there is no longer a need to persist it (again) in index. 3. If such index is already created, when the table is opened, this worklog will also get the SRID from indexed column. More specifically, it will get such info in dd_fill_one_dict_index(), where we get each indexed field column info. Since for spatial index, we can only index one spatial column, so SRID must be affiliated with the only "key_part->field" the index has.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.