MySQL Bugs: #114200: Incorrect result of getting data from a spatial index on column with SRID

Bug #114200	Incorrect result of getting data from a spatial index on column with SRID
Submitted:	4 Mar 2024 6:44	Modified:	14 Jun 2024 0:39
Reporter:	Jinyou Ma	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The result is empty when MySQL gets data from a spatial index containing a column with SRID attributes.

How to repeat:
- 1. create table

CREATE TABLE `a` (
  `id` int  auto_increment NOT NULL,
  `shape` geometry not null /*!80003 SRID 0 */,
  PRIMARY KEY (`id`),
  SPATIAL KEY (`shape`)
) ENGINE=InnoDB;

- 2. add data

insert a values (2, point(1,100));

- 3. query data from the spatial index is empty
select id from a force index (shape) where id > 0;
Empty set (0.00 sec)

- 4. query data from the primary key is correct

select id from a force index (primary) where id > 0;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

Hello jin ma,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

After adding the break point at the row_search_mvcc, I can see that InnoDB does support sequential scan for Rtree index

Thread 38 "connection" hit Breakpoint 1, row_search_mvcc (buf=0xffff28c2e140 "", mode=PAGE_CUR_G, prebuilt=0xffff28c10758, match_mode=0, direction=0)
    at /usr/src/debug/mysql-community-8.0.36-1.el9.aarch64/mysql-8.0.36/storage/innobase/row/row0sel.cc:4401
4401	                        const ulint direction) {
(gdb) p prebuilt->index.name
$1 = {m_name = 0xffff28c08828 "shape"}

(gdb) n
4612	  if (dict_index_is_spatial(index) && !RTREE_SEARCH_MODE(mode)) {
(gdb) n
4613	    err = DB_END_OF_INDEX;
(gdb) n
6002	  if (heap != nullptr) {

--------------------------------

  /* We don't support sequential scan for Rtree index, because it
  is no meaning to do so. */
  if (dict_index_is_spatial(index) && !RTREE_SEARCH_MODE(mode)) {
    err = DB_END_OF_INDEX;
    goto func_exit;
  }

Posted by developer:
 
Fixed as of the upcoming MySQL Server 8.0.38, 8.4.1, and 9.0.0 releases, and here's the proposed changelog entry from the documentation team:

The result from a spatial index containing a column with a spacial
reference identifier (SRID) attribute would return as empty.

Thank you for the bug report.