Bug #114200 Incorrect result of getting data from a spatial index on column with SRID
Submitted: 4 Mar 6:44 Modified: 14 Jun 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

[4 Mar 6:44] Jinyou Ma
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)
[4 Mar 6:58] MySQL Verification Team
Hello jin ma,

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

regards,
Umesh
[5 Mar 0:43] Jinyou Ma
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;
  }
[14 Jun 0:39] Philip Olson
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.