Bug #80697 | ST_AsGeoJSON() doesn't work with session variables | ||
---|---|---|---|
Submitted: | 10 Mar 2016 19:19 | Modified: | 9 Apr 2016 16:06 |
Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: JSON | Severity: | S3 (Non-critical) |
Version: | 5.7.11 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | geo, geojson, json, spatial |
[10 Mar 2016 19:19]
Daniël van Eeden
[10 Mar 2016 19:27]
Daniël van Eeden
This works... but I consider it a hack. mysql> SELECT ST_AsGeoJSON(ST_GeomFromWKB(ST_AsBinary(@ams))); +----------------------------------------------------+ | ST_AsGeoJSON(ST_GeomFromWKB(ST_AsBinary(@ams))) | +----------------------------------------------------+ | {"type": "Point", "coordinates": [4.9, 52.366667]} | +----------------------------------------------------+ 1 row in set (0.00 sec)
[11 Mar 2016 5:05]
MySQL Verification Team
Hello Daniël, Thank you for the report and test case. Verified as described. Thanks, Umesh
[11 Mar 2016 5:06]
MySQL Verification Team
// 5.7.11 [root@cluster-repo ~]# mysql -uroot -p test Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.7.11-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT ST_AsGeoJSON(ST_GeomFromText("POINT(4.9 52.366667)")); +-------------------------------------------------------+ | ST_AsGeoJSON(ST_GeomFromText("POINT(4.9 52.366667)")) | +-------------------------------------------------------+ | {"type": "Point", "coordinates": [4.9, 52.366667]} | +-------------------------------------------------------+ 1 row in set (0.03 sec) mysql> SET @ams := ST_GeomFromText("POINT(4.9 52.366667)"); Query OK, 0 rows affected (0.00 sec) mysql> SELECT ST_AsGeoJSON(@ams); ERROR 3064 (HY000): Incorrect type for argument geojson in function st_asgeojson. mysql> create temporary table t1 as select @ams, ST_GeomFromText("POINT(4.9 52.366667)"); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TEMPORARY TABLE `t1` ( `@ams` longblob, `ST_GeomFromText("POINT(4.9 52.366667)")` geometry DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT CAST(@ams AS geometry); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'geometry)' at line 1 mysql> SELECT ST_AsGeoJSON(ST_GeomFromWKB(ST_AsBinary(@ams))); +----------------------------------------------------+ | ST_AsGeoJSON(ST_GeomFromWKB(ST_AsBinary(@ams))) | +----------------------------------------------------+ | {"type": "Point", "coordinates": [4.9, 52.366667]} | +----------------------------------------------------+ 1 row in set (0.01 sec)
[9 Apr 2016 16:06]
Paul DuBois
Posted by developer: Noted in 5.8.0 changelog. ST_AsGeoJSON() failed when geometry arguments were supplied using user-defined variables.