MySQL Bugs: #80697: ST_AsGeoJSON() doesn't work with session variables

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

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

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)

Hello Daniël,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

// 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)

Posted by developer:
 
Noted in 5.8.0 changelog.

ST_AsGeoJSON() failed when geometry arguments were supplied using
user-defined variables.