Description:
On MySQL Community Server 26.7.0, the result of the same ST_Within() predicate changes depending on whether a spatial index is used.
The geographic polygon passes ST_IsValid() and ST_Validate(). For the point in the test case, ST_Within(p, @south) returns 1 while MBRContains(@south, p) returns 0.
With IGNORE INDEX, the query returns id=1. With FORCE INDEX, it returns no rows. EXPLAIN confirms a table scan for IGNORE INDEX and range access using key sp_p for FORCE INDEX.
Expected: Changing the access path should not change the result; both queries should return id=1.
Actual: IGNORE INDEX returns id=1; FORCE INDEX returns an empty result.
How to repeat:
SELECT VERSION();
CREATE DATABASE spatial_within_repro;
USE spatial_within_repro;
CREATE TABLE g (
id INT PRIMARY KEY,
p POINT NOT NULL SRID 4326,
SPATIAL INDEX sp_p (p)
) ENGINE = InnoDB;
INSERT INTO g VALUES
(1, ST_GeomFromText('POINT(0 0)', 4326, 'axis-order=long-lat'));
SET @south = ST_GeomFromText(
'POLYGON((-45 -90,45 -90,45 -80,-45 -80,-45 -90))',
4326, 'axis-order=long-lat'
);
SELECT ST_IsValid(@south), ST_Validate(@south) IS NOT NULL;
SELECT id, ST_Within(p, @south), MBRContains(@south, p) FROM g;
SELECT id FROM g IGNORE INDEX (sp_p)
WHERE ST_Within(p, @south);
SELECT id FROM g FORCE INDEX (sp_p)
WHERE ST_Within(p, @south);
EXPLAIN FORMAT=TRADITIONAL
SELECT id FROM g IGNORE INDEX (sp_p)
WHERE ST_Within(p, @south);
EXPLAIN FORMAT=TRADITIONAL
SELECT id FROM g FORCE INDEX (sp_p)
WHERE ST_Within(p, @south);
Suggested fix:
Ensure spatial-index range pruning for exact geographic predicates does not discard rows that satisfy ST_Within(). If a conservative candidate range cannot be computed for a valid geometry, use a scan instead.
Description: On MySQL Community Server 26.7.0, the result of the same ST_Within() predicate changes depending on whether a spatial index is used. The geographic polygon passes ST_IsValid() and ST_Validate(). For the point in the test case, ST_Within(p, @south) returns 1 while MBRContains(@south, p) returns 0. With IGNORE INDEX, the query returns id=1. With FORCE INDEX, it returns no rows. EXPLAIN confirms a table scan for IGNORE INDEX and range access using key sp_p for FORCE INDEX. Expected: Changing the access path should not change the result; both queries should return id=1. Actual: IGNORE INDEX returns id=1; FORCE INDEX returns an empty result. How to repeat: SELECT VERSION(); CREATE DATABASE spatial_within_repro; USE spatial_within_repro; CREATE TABLE g ( id INT PRIMARY KEY, p POINT NOT NULL SRID 4326, SPATIAL INDEX sp_p (p) ) ENGINE = InnoDB; INSERT INTO g VALUES (1, ST_GeomFromText('POINT(0 0)', 4326, 'axis-order=long-lat')); SET @south = ST_GeomFromText( 'POLYGON((-45 -90,45 -90,45 -80,-45 -80,-45 -90))', 4326, 'axis-order=long-lat' ); SELECT ST_IsValid(@south), ST_Validate(@south) IS NOT NULL; SELECT id, ST_Within(p, @south), MBRContains(@south, p) FROM g; SELECT id FROM g IGNORE INDEX (sp_p) WHERE ST_Within(p, @south); SELECT id FROM g FORCE INDEX (sp_p) WHERE ST_Within(p, @south); EXPLAIN FORMAT=TRADITIONAL SELECT id FROM g IGNORE INDEX (sp_p) WHERE ST_Within(p, @south); EXPLAIN FORMAT=TRADITIONAL SELECT id FROM g FORCE INDEX (sp_p) WHERE ST_Within(p, @south); Suggested fix: Ensure spatial-index range pruning for exact geographic predicates does not discard rows that satisfy ST_Within(). If a conservative candidate range cannot be computed for a valid geometry, use a scan instead.