MySQL Bugs: #116352: spatial index cause check table wrong

Bug #116352	spatial index cause check table wrong
Submitted:	14 Oct 2024 2:57	Modified:	14 Oct 2024 6:47
Reporter:	seaver wu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S3 (Non-critical)
Version:	8.0.31, 8.0.39, 8.4.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CHECK TABLE, spatial index

Description:
Some special values of geometry makes 'check table' fail if there is a spatial index.

How to repeat:
 CREATE TABLE t (
  `id` int NOT NULL,
  `polygon` geometry NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `polygon` (`polygon`)
);

insert into t values(1, 0x00000000010700000000000000);

check table t;

+--------+-------+----------+----------------------------------------------------------+
| Table  | Op    | Msg_type | Msg_text                                                 |
+--------+-------+----------+----------------------------------------------------------+
| test.t | check | Warning  | InnoDB: Index 'polygon' contains 0 entries, should be 1. |
| test.t | check | error    | Corrupt                                                  |

Suggested fix:
By analyzing the code, check table will traverse the records in all secondary index pages. For each record, use cmp_dtuple_rec_with_gis to determine whether the record meets the traversal conditions, but for some special values, such as 0x00000000010700000000000000 given in the example, the record will be skipped However, the number of secondary index records counted by the check table is less than the dd record, and the table is marked as corrupted.
Maybe we should check geometry before insert.

Hello QIFAN WU,

Thank you for the report and feedback.

regards,
Umesh

The problem data comes from GeometryCollection storing empty objects. According to the official manual, GeometryCollection supports storing empty objects. This may be because the check table is not compatible.

Quote from official content:
Only GeometryCollection can be empty. Such a value is stored with 0 elements.

mysql> select ST_GeomFromText('GEOMETRYCOLLECTION()');
+----------------------------------------------------------------------------------+
| ST_GeomFromText('GEOMETRYCOLLECTION()')                                          |
+----------------------------------------------------------------------------------+
| 0x00000000010700000000000000                                                     |
+----------------------------------------------------------------------------------+

I think maybe the minimum and maximum values are written in reverse here? I'm not sure

8.0.39 storage/innobase/row/row0sel.cc:6111
dberr_t row_count_rtree_recs{
...
      for (uint j = 0; j < SPDIMS; j++) {
        tmp_mbr[j * 2] = DBL_MAX;
        tmp_mbr[j * 2 + 1] = -DBL_MAX;
      }
...

might be fixed in 8.0.42 by internal:

 Bug 37286473 - CHECK TABLE reports false corruption

"ISSUE:
- CHECK TABLE reports false corruption for Spatial Index, when there is
  "GEOMETRYCOLLECTION EMPTY" or a Geometry with MBR which covers the
  full range.

ROOT CAUSE:
- In 5.7 GEOMETRYCOLLECTION() had Minimum Bounded Rectangle computed
  and stored as:

      {x,y}min = DBL_MAX and {x,y}max = -DBL_MAX

- In 8.0 we've introduced more standard syntax: GEOMETRYCOLLECTION EMPTY,
  and changed its MBR definition to be computed and stored as:

      {x,y}min = -DBL_MAX and {x,y}max = DBL_MAX

- In each version of MySQL, the CHECK TABLE command tries to count
  number of entries in SPATIAL index by constructing a geometric query
  with a predicate it believes should be matched by all rows:

      find rows s.t. box{{x,y}min = DBL_MAX and {x,y}max = -DBL_MAX}
      is covered by geometry

- However, the logic inside mbr_within_cmp() was buggy to support this
  change. And CHECK TABLE would report false corruption of the spatial
  indexes.

FIX:
- Updated mbr_within_cmp() to handle both the legacy and new format of
  encoding GEOMETRYCOLLECTION EMPTY by returning true when the MBR
  passed as part of geometric query is recognized to represent the
  constant used by InnoDB, as box meant to be covered by every other
  box."