| Bug #77133 | MBRContains function behaves differently on 5.5, 5.6 and 5.7 | ||
|---|---|---|---|
| Submitted: | 22 May 2015 15:46 | Modified: | 26 May 2015 8:12 |
| Reporter: | Gábor Varga | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 5.7, 5.7.8 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mbrcontains, regression | ||
[25 May 2015 9:12]
MySQL Verification Team
Hello Gábor Varga, Thank you for the report and test case. Thanks, Umesh
[25 May 2015 9:14]
MySQL Verification Team
// 5.6.24
mysql> SELECT MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)'))
-> ;
+-----------------------------------------------------------------------------------------+
| MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)')) |
+-----------------------------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
// 5.7.8
mysql> SELECT MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)'));
+-----------------------------------------------------------------------------------------+
| MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)')) |
+-----------------------------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead |
| Warning | 1287 | 'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead |
+---------+------+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT MBRContains(ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), ST_GeomFromText('Point(0 1)'));
+-----------------------------------------------------------------------------------------------+
| MBRContains(ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), ST_GeomFromText('Point(0 1)')) |
+-----------------------------------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[26 May 2015 8:12]
Wei Zhao
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Further explanations: This is not a bug, the result in MySQL 5.6/5.5 is wrong, the one returned by MySQL 5.7 is correct. So in MySQL 5.7 the bug is fixed. The MBRContains(along with other MBRXxxx() functions) is a MySQL specific spatial function to test the spatial relationship of the MBRs of the two geometry arguments, and other than using the MBRs of the two geometries, the definition of MBRContains is the same as that of ST_Contains. The definition of ST_Contains from The Open Geospatial Consortium (OGC) says: G1 CONTAINS G2 is defined as: No point in G2 is in the exterior of G1, and at least one point in G2 must be in the interior of G1. The problem with this case is that the only point in G2 is not in the interior of G1, it's on the boundary of G1. If you want the original semantics you need to now call MBRCovers(), and MBRCovers(G1, G2) is defined as: no point in G2 is in the exterior of G1. And after replacing MBRContains with MBRCovers, the query in the test case returns 1.
Description: The MBRContains function gives different results on different versions of MySQL servers (5.5-5.6 and 5.7) with the same dataset. In versions 5.5 and 5.6 points on the borders of the minimum bounding rectangle were parts of it (mbrcontains returned 1) but they are not a part in version 5.7. How to repeat: mysql 5.5 and 5.6: SELECT MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)')) returns: 1 mysql 5.7: SELECT MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'), GeomFromText('Point(0 1)')) [or using the new name (ST_GeomFromText instead of GeomFromText)] returns: 0