Bug #107896 if exchange the order of input geo, st_intersects will get opposite result

• Submitted: 15 Jul 2022 13:50
• Modified: 16 Jul 2022 20:50
• Reporter: T H
• Status: Verified
• Category: MySQL Server: GIS
• Severity: S2 (Serious)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[15 Jul 2022 13:50] T H

Description:
input specific data to sql : select st_intersects(geo1, geo2);
if exchange the order of input geo, such as (geo2, geo1), will get opposite result

mysql> select st_intersects(st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326), st_geomfromtext('POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st_intersects(st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326), st_geomfromtext('POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                          0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select st_intersects(st_geomfromtext(' POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326), st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st_intersects(st_geomfromtext(' POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326), st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326)) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                           1 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
select st_intersects(st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326), st_geomfromtext('POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326));

select st_intersects(st_geomfromtext(' POLYGON((0 0,0 0.000000001,0.000000001 0.000000001,0.000000001 0,0 0))' ,4326), st_geomfromtext('POLYGON((-0.000000001 0.000000001,-0.000000001 0.000000002,0 0.000000002,0 0.000000001,-0.000000001 0.000000001))' ,4326));

Suggested fix:
Is it caused by boost::geometry lib?

[16 Jul 2022 20:50] MySQL Verification Team

Thank you for the bug report.