| Bug #107402 | value has changed when 1e-16 is input of GIS expr | ||
|---|---|---|---|
| Submitted: | 26 May 2022 8:53 | Modified: | 31 May 2022 14:24 |
| Reporter: | Wenbo Li | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: GIS | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 May 2022 13:46]
MySQL Verification Team
Hi Mr. Li, Thank you for your bug report. We have managed to repeat your test case with 8.0.29. This report is now a verified bug.
[31 May 2022 6:03]
huahua xu
Hi Wenbo Li,
I don't think it's a bug. See the following test case:
mysql> select st_x(st_geomfromtext('point(1e-16 1e-16)', 4326));
+---------------------------------------------------+
| st_x(st_geomfromtext('point(1e-16 1e-16)', 4326)) |
+---------------------------------------------------+
| 9.999999999999999e-17 |
+---------------------------------------------------+
mysql> select st_x(st_geomfromtext('point(1e-16 1e-16)', 0));
+------------------------------------------------+
| st_x(st_geomfromtext('point(1e-16 1e-16)', 0)) |
+------------------------------------------------+
| 1e-16 |
+------------------------------------------------+
1 row in set (8.81 sec)
For your case, I think that the precision loss is due to the normalized transformation based on reference coordinate system(4326), which angular unit is 0.017453292519943278.
[31 May 2022 7:28]
Wenbo Li
I think there is still a precision issue here, the value still changes after normalized translation. will this problem be dealt with in later version of mysql?
[31 May 2022 12:49]
huahua xu
Yes, you are right. double x = ...; double x1 = x * 0.017453292519943278; double x2 = x1 / 0.017453292519943278; then, x2 != x. It is impossible to avoid the precision issue.
[31 May 2022 14:24]
Wenbo Li
Actually, there is a way to avoid this problem. The reason why mysql need to transform lat/long to radian is that boost only accept radian as the geography input in my opinion. In st_latitude(st_x), is the procedure that transform lat/long to radian redundant?
Description: Gis expr st_latitude may cause precision problem. How to repeat: mysql> select st_latitude(st_geomfromtext('point(1e-16 1e-16)', 4326)); +----------------------------------------------------------+ | st_latitude(st_geomfromtext('point(1e-16 1e-16)', 4326)) | +----------------------------------------------------------+ | 9.999999999999999e-17 | +----------------------------------------------------------+ Actually it is 1e-16 right?