| Bug #107424 | st_intersects() returns different results with or without spatial index | ||
|---|---|---|---|
| Submitted: | 30 May 2022 5:45 | Modified: | 31 May 2022 3:25 |
| Reporter: | Jeremy Wang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S2 (Serious) |
| Version: | 8.0.26, 8.0.29, 8.0.32 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression, spatial index, st_intersects | ||
[30 May 2022 8:44]
MySQL Verification Team
Hello Jeremy Wang, Thank you for the report and test case. Verified as described. regards, Umesh
[31 May 2022 3:25]
Jeremy Wang
Hello Umesh, Thank you! Could you please tell me which result is the right one, with or without spatial index? It seems the result with index is more strict. But if the result without index is right, I may need to do some extra work in my application.
[30 Mar 2023 7:51]
MySQL Verification Team
Quoting Dev's note from internal bug - "The problem is that Boost Geometry tests equality up to some epsilon, so numbers that are very close are considered equal. When using the index the equality with the second point is never tested. the correct result is that of the index"
Description: The results of st_intersects are different with or without spatial index in some cases. How to repeat: mysql> create table t1(g1 geometry not null srid 4326); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values(st_geomfromtext('point(-6.124932923690693 23.43701608651873)', 4326)); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(st_geomfromtext('point(-6.124932923690693 23.43701608651874)', 4326)); Query OK, 1 row affected (0.00 sec) mysql> select st_astext(g1) from t1 where st_intersects(st_geomfromtext('point(-6.124932923690693 23.43701608651873)', 4326), g1); +---------------------------------------------+ | st_astext(g1) | +---------------------------------------------+ | POINT(-6.124932923690693 23.43701608651873) | | POINT(-6.124932923690693 23.43701608651874) | +---------------------------------------------+ 2 rows in set (0.00 sec) mysql> create spatial index spidx on t1(g1); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select st_astext(g1) from t1 where st_intersects(st_geomfromtext('point(-6.124932923690693 23.43701608651873)', 4326), g1); +---------------------------------------------+ | st_astext(g1) | +---------------------------------------------+ | POINT(-6.124932923690693 23.43701608651873) | +---------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: The query results should be the same whether there is a spatial index or not.