Description:
In some cases queries with ST_CONTAINS do not return any results unless >0 is added.
The same query using the same data did return results in 8.0.11.
How to repeat:
CREATE TABLE test (
`id` int(11) NOT NULL AUTO_INCREMENT,
`geo_footprint` geometry NOT NULL /*!80003 SRID 4326 */,
SPATIAL KEY `geo_footprint` (`geo_footprint`),
PRIMARY KEY (`id`)
);
INSERT INTO test (geo_footprint) VALUES(ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[[14.9997279, 48.753013], [14.9997331, 47.76525654], [16.46465601, 47.75591029], [16.4931335, 48.74333898], [14.9997279, 48.753013]]]}'));
-- this query does not return any results
select * FROM test
WHERE ST_CONTAINS(
geo_footprint,
ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[15.113334808199966,48.1337532388],[15.113329984100005,48.1337371609],[15.113411697200036,48.1337166354],[15.113673777399981,48.1336819199],[15.114544787600039,48.1335464618],[15.115336574000025,48.1334415189],[15.116374992200008,48.1332937084],[15.117266346799966,48.1330924824],[15.11769786879995,48.1329803459],[15.118129375299986,48.132868199],[15.118515258099933,48.1327388086],[15.118597296700045,48.1327141533],[15.118635348899943,48.132702717],[15.11867907729993,48.1327796282],[15.11876276890007,48.13290987],[15.118805112699988,48.1330357889],[15.118850101500016,48.1333486685],[15.118823191700017,48.1334777297],[15.118820984299987,48.1334784295],[15.118821076099948,48.1334779691],[15.113334808199966,48.1337532388],[15.113334808199966,48.1337532388]]]}')
);
-- note that adding >0 will cause the query to return results
select * FROM test
WHERE ST_CONTAINS(
geo_footprint,
ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[15.113334808199966,48.1337532388],[15.113329984100005,48.1337371609],[15.113411697200036,48.1337166354],[15.113673777399981,48.1336819199],[15.114544787600039,48.1335464618],[15.115336574000025,48.1334415189],[15.116374992200008,48.1332937084],[15.117266346799966,48.1330924824],[15.11769786879995,48.1329803459],[15.118129375299986,48.132868199],[15.118515258099933,48.1327388086],[15.118597296700045,48.1327141533],[15.118635348899943,48.132702717],[15.11867907729993,48.1327796282],[15.11876276890007,48.13290987],[15.118805112699988,48.1330357889],[15.118850101500016,48.1333486685],[15.118823191700017,48.1334777297],[15.118820984299987,48.1334784295],[15.118821076099948,48.1334779691],[15.113334808199966,48.1337532388],[15.113334808199966,48.1337532388]]]}')
)>0;
Description: In some cases queries with ST_CONTAINS do not return any results unless >0 is added. The same query using the same data did return results in 8.0.11. How to repeat: CREATE TABLE test ( `id` int(11) NOT NULL AUTO_INCREMENT, `geo_footprint` geometry NOT NULL /*!80003 SRID 4326 */, SPATIAL KEY `geo_footprint` (`geo_footprint`), PRIMARY KEY (`id`) ); INSERT INTO test (geo_footprint) VALUES(ST_GeomFromGeoJSON('{"type": "Polygon", "coordinates": [[[14.9997279, 48.753013], [14.9997331, 47.76525654], [16.46465601, 47.75591029], [16.4931335, 48.74333898], [14.9997279, 48.753013]]]}')); -- this query does not return any results select * FROM test WHERE ST_CONTAINS( geo_footprint, ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[15.113334808199966,48.1337532388],[15.113329984100005,48.1337371609],[15.113411697200036,48.1337166354],[15.113673777399981,48.1336819199],[15.114544787600039,48.1335464618],[15.115336574000025,48.1334415189],[15.116374992200008,48.1332937084],[15.117266346799966,48.1330924824],[15.11769786879995,48.1329803459],[15.118129375299986,48.132868199],[15.118515258099933,48.1327388086],[15.118597296700045,48.1327141533],[15.118635348899943,48.132702717],[15.11867907729993,48.1327796282],[15.11876276890007,48.13290987],[15.118805112699988,48.1330357889],[15.118850101500016,48.1333486685],[15.118823191700017,48.1334777297],[15.118820984299987,48.1334784295],[15.118821076099948,48.1334779691],[15.113334808199966,48.1337532388],[15.113334808199966,48.1337532388]]]}') ); -- note that adding >0 will cause the query to return results select * FROM test WHERE ST_CONTAINS( geo_footprint, ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[15.113334808199966,48.1337532388],[15.113329984100005,48.1337371609],[15.113411697200036,48.1337166354],[15.113673777399981,48.1336819199],[15.114544787600039,48.1335464618],[15.115336574000025,48.1334415189],[15.116374992200008,48.1332937084],[15.117266346799966,48.1330924824],[15.11769786879995,48.1329803459],[15.118129375299986,48.132868199],[15.118515258099933,48.1327388086],[15.118597296700045,48.1327141533],[15.118635348899943,48.132702717],[15.11867907729993,48.1327796282],[15.11876276890007,48.13290987],[15.118805112699988,48.1330357889],[15.118850101500016,48.1333486685],[15.118823191700017,48.1334777297],[15.118820984299987,48.1334784295],[15.118821076099948,48.1334779691],[15.113334808199966,48.1337532388],[15.113334808199966,48.1337532388]]]}') )>0;