ERROR 1207 when ST_Contains() uses a POINT() returned by a scalar subquery and a SPATIAL index is used

1. 1. Summary

A query using `ST_Contains()` with a `POINT()` returned by a scalar subquery fails with:

ERROR 1207 (HY000):
Update locks cannot be acquired during a READ UNCOMMITTED transaction

This only happens when MariaDB uses the InnoDB SPATIAL index.

1. 1. Tested versions

The issue is reproducible on:

• MariaDB 10.11.14-MariaDB-0+deb12u2 (Debian 12)
• MariaDB 11.8.6-MariaDB-0+deb13u1 (Debian 13)

Transaction isolation level:

SELECT @@transaction_isolation;

returns:

REPEATABLE-READ

1. 1. Minimal failing query

SELECT idparcelle
FROM BDDparcelle
WHERE ST_Contains(
parcellepolygone,
(
SELECT POINT(LONGITUDE, LATITUDE)
FROM BDDphysore
WHERE id = 262514
)
);

Result:

ERROR 1207 (HY000):
Update locks cannot be acquired during a READ UNCOMMITTED transaction

1. 1. EXPLAIN

```
BDDparcelle range key=parcellepolygone
BDDphysore const PRIMARY
```

The optimizer chooses the SPATIAL index.

1. 1. Queries that work

Using a literal geometry:

SELECT idparcelle
FROM BDDparcelle
WHERE ST_Contains(
parcellepolygone,
POINT(9.45655, 42.51592)
);

Using a user variable:

SET @pt =
(
SELECT POINT(LONGITUDE, LATITUDE)
FROM BDDphysore
WHERE id = 262514
);

SELECT idparcelle
FROM BDDparcelle
WHERE ST_Contains(parcellepolygone, @pt);

Both queries execute successfully.

1. 1. Workarounds

The error disappears if:

• the SPATIAL index is dropped;
• `IGNORE INDEX(spatial_index_name)` is used;
• the geometry is first stored in a user variable (`@pt`);
• the optimizer no longer treats the subquery as `const` (for example when multiple rows are returned).

1. 1. Expected behavior

The query should execute successfully and return the matching polygon, exactly as when using a literal `POINT()` or a user variable.

The query is valid SQL and should not raise ERROR 1207.