Spatial FOR UPDATE SKIP LOCKED scan omits unlocked matching rows

A broad spatial range scan with FOR UPDATE SKIP LOCKED returns no rows when one matching row is locked, although two other matching rows remain unlocked. The equivalent primary-key scan and a narrower spatial scan both return the unlocked rows.

Steps to reproduce

The account running this test must be able to create and drop databases and events and to set the global event_scheduler variable.

1. Save the following as setup.sql:

   CREATE DATABASE spatial_test;

   USE spatial_test;

   CREATE TABLE points(id INT PRIMARY KEY, pt POINT NOT NULL, SPATIAL INDEX pt_idx(pt)) ENGINE=InnoDB;

   INSERT INTO points VALUES (1,POINT(1,1)),(2,POINT(2,2)),(3,POINT(3,3));

2. Save the following as event.sql. The event updates row 1 inside a transaction, signals that it holds the lock, waits for 12 seconds, and then rolls back.

   USE spatial_test;

   SET GLOBAL event_scheduler=ON;

   DELIMITER //

   CREATE EVENT holder ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND ON COMPLETION PRESERVE DO

   BEGIN

   START TRANSACTION;

   UPDATE points SET pt=POINT(1.5,1.5) WHERE id=1;

   DO GET_LOCK('holder_ready',0);

   DO SLEEP(12);

   ROLLBACK;

   DO RELEASE_LOCK('holder_ready');

   END//

   DELIMITER ;

3. Save the following as probe.sql:

   USE spatial_test;

   SET SESSION innodb_lock_wait_timeout=1;

   SELECT IS_USED_LOCK('holder_ready') IS NOT NULL AS holder_before;

   EXPLAIN SELECT id FROM points FORCE INDEX(pt_idx) WHERE MBRWithin(pt,ST_GeomFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))')) FOR UPDATE SKIP LOCKED;

   BEGIN;

   SELECT 'spatial' AS path, id FROM points FORCE INDEX(pt_idx) WHERE MBRWithin(pt,ST_GeomFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))')) FOR UPDATE SKIP LOCKED;

   ROLLBACK;

   BEGIN;

   SELECT 'primary' AS path, id FROM points FORCE INDEX(PRIMARY) WHERE MBRWithin(pt,ST_GeomFromText('POLYGON((0 0,0 5,5 5,5 0,0 0))')) FOR UPDATE SKIP LOCKED;

   ROLLBACK;

   BEGIN;

   SELECT 'spatial_narrow' AS path, id FROM points FORCE INDEX(pt_idx) WHERE MBRWithin(pt,ST_GeomFromText('POLYGON((1.8 1.8,1.8 5,5 5,5 1.8,1.8 1.8))')) FOR UPDATE SKIP LOCKED;

   ROLLBACK;

   SELECT IS_USED_LOCK('holder_ready') IS NOT NULL AS holder_after;

4. Run the setup and schedule the locking transaction:

   mariadb < setup.sql

   mariadb < event.sql

5. Wait until the following query returns 1, then immediately run the probe while the event is still sleeping:

   SELECT IS_USED_LOCK('holder_ready') IS NOT NULL;

   mariadb --batch --skip-column-names < probe.sql

6. After the event finishes, remove the test database. Restore event_scheduler to its previous value if it was changed for this test.

   DROP DATABASE IF EXISTS spatial_test;

Actual result

The readiness checks before and after the scans both return 1, confirming that row 1 remains locked throughout the probe. EXPLAIN reports a range scan through pt_idx. The broad spatial query emits no result row, while the controls emit:

primary	2

primary	3

spatial_narrow	3

spatial_narrow	2

Thus, the broad spatial result is empty even though rows 2 and 3 are unlocked and satisfy the same broad spatial predicate.

Expected result

FOR UPDATE SKIP LOCKED should skip the conflicting locked row 1 while returning qualifying unlocked rows 2 and 3. The result must not become empty solely because the query uses the spatial index rather than the primary-key access path.

This occurs when an InnoDB spatial-index locking read uses a broad MBRWithin range while one matching row is locked by another transaction.