Amazon Aurora Spatial Query Returning Duplicate Results Despite Unique Index

Question

I am encountering a weird issue with a MySQL spatial query that is returning duplicate results, even though the selected column has a unique index. The query involves spatial function like ST_CONTAINS on a spatial indexed column (coords point NOT NULL /*!80003 SRID 4326 */). I am using MySQL v8.0.28 hosted on Aurora MySQL RDS v3.04.1

The query

SELECT
    uid
FROM
    table
WHERE
    ST_CONTAINS(
        (
            ST_POLYGONFROMTEXT(
                'POLYGON ((37.4434532793747721 -122.2358371757517972, 37.3985377206252210 -122.2358371757517972, 37.3985377206252210 -122.1783706242482168, 37.4434532793747721 -122.1783706242482168, 37.4434532793747721 -122.2358371757517972))',
                4326
            )
        ),
        coords
    )

The table schema:

CREATE TABLE `table` (
  `uid` bigint NOT NULL,
  `coords` point NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`uid`),
  SPATIAL KEY `sp_idx` (`coords`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

To troubleshoot the issue, I have:

• Verified the integrity of the spatial data and ensured that the coordinates stored in the coords column are valid and represent valid geometries.
• Checked for locking issues and ensured that there are no locks on the table or rows that might cause contention.
• Reviewed the query execution plan and ensured that the query is optimized for performance.
• Ensured that database statistics are up-to-date to enable the query optimizer to make informed decisions about query execution plans.
• I have rolled back any uncommitted changes to ensure that the data being queried is consistent and not affected by ongoing transactions.
• Row Count for Duplicate Listings: When I check the row count for the duplicate listings returned by the spatial query, it shows only one row. This suggests that the duplicates are not actual duplicates in the database but are being incorrectly returned by the query.

Despite taking these steps, the issue persists. I am seeking assistance in identifying the root cause of the duplicate results and finding a solution to resolve it.

Any insights or suggestions on how to troubleshoot and resolve this issue would be greatly appreciated. Thank you.