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:
None 
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
Description:
Storing a geometry feature in a variable will result in a longblob.
The ST_AsGeoJSON() function requires a geometry type as input.
The CAST() function doesn't cast to geometry.

This makes it hard to use ST_AsGeoJSON() with session variables.

How to repeat:
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.00 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

Suggested fix:
1. Store result of ST_GeomFromText() in a variable _with type geometry_
2. MAYBE: Make ST_AsGeoJSON() use longblob as input 
3. MAYBE: Make CAST() able to cast from longblob to geometry
[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.