MySQL Bugs: #79060: Cannot get geometry object from data you send to the GEOMETRY field

Bug #79060	Cannot get geometry object from data you send to the GEOMETRY field
Submitted:	1 Nov 2015 20:00	Modified:	5 Nov 2015 19:59
Reporter:	Benjamin Morel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: GIS	Severity:	S1 (Critical)
Version:	5.7.9	OS:	Fedora (22 x64)
Assigned to:		CPU Architecture:	Any
Tags:	Geometry, gis

Description:
I certain very specific cases (using JOIN and ORDER BY), retrieving a valid geometry using ST_AsBinary() fails with the following message:

> Cannot get geometry object from data you send to the GEOMETRY field

I've stripped down my app data to a maximum, and managed to reproduce it 100% with the script below.

When playing with the script, you'll notice that:

- Using ST_AsText() instead of ST_AsBinary() works as expected
- Removing INNER JOIN (try removing DealPeriod) works as expected
- Removing ORDER BY works as expected

So it looks like valid geometry data gets internally corrupted in some very specific cases.

This is a very blocking issue as our app just cannot work with MySQL 5.7 yet.

How to repeat:
DROP DATABASE IF EXISTS test_bug;
CREATE DATABASE test_bug;
USE test_bug;

DROP TABLE IF EXISTS Deal;
CREATE TABLE Deal (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  restaurantId int(10) unsigned NOT NULL,
  creationTime int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO Deal VALUES (1,1,1446379501);

DROP TABLE IF EXISTS DealPeriod;
CREATE TABLE DealPeriod (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  dealId int(10) unsigned NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO DealPeriod VALUES (1,1);

DROP TABLE IF EXISTS Restaurant;
CREATE TABLE Restaurant (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  location point NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO Restaurant VALUES (1,ST_GeomFromText('POINT(55.2918681 -20.9388982)'));

SELECT ST_AsBinary(r1_.location)
FROM Deal d0_
INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
ORDER BY d0_.creationTime DESC;

Thank you for the bug report.

c:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.10 Source distribution PULL: 2015-OCT-24

Copyright (c) 2000, 2015, 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 5.7 > DROP DATABASE IF EXISTS test_bug;
Query OK, 3 rows affected (0.06 sec)

mysql 5.7 > CREATE DATABASE test_bug;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > USE test_bug;
Database changed
mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS Deal;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE Deal (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   restaurantId int(10) unsigned NOT NULL,
    ->   creationTime int(11) NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > INSERT INTO Deal VALUES (1,1,1446379501);
Query OK, 1 row affected (0.02 sec)

mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS DealPeriod;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE DealPeriod (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   dealId int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO DealPeriod VALUES (1,1);
Query OK, 1 row affected (0.00 sec)

mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS Restaurant;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE Restaurant (
    ->   id int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   location point NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO Restaurant VALUES (1,ST_GeomFromText('POINT(55.2918681 -20.9388982)'));
Query OK, 1 row affected (0.01 sec)

mysql 5.7 >
mysql 5.7 > SELECT ST_AsBinary(r1_.location)
    -> FROM Deal d0_
    -> INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
    -> INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
    -> ORDER BY d0_.creationTime DESC;
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

mysql 5.7 > SELECT ST_AsText(r1_.location)
    -> FROM Deal d0_
    -> INNER JOIN DealPeriod d2_ ON d0_.id = d2_.dealId
    -> INNER JOIN Restaurant r1_ ON d0_.restaurantId = r1_.id
    -> ORDER BY d0_.creationTime DESC;
+-------------------------------+
| ST_AsText(r1_.location)       |
+-------------------------------+
| POINT(55.2918681 -20.9388982) |
+-------------------------------+
1 row in set (0.00 sec)

Noted in 5.7.10, 5.8.0 changelogs.

ST_AsWKB() could cause a server exit if given invalid data.

This is actually in the 5.7.9 changelog, so I don't think it is related (and the geometry data is valid here!)

(closed by mistake)

I don't understood why you opened again when the bug was verified and commented the changelogs for the below version:

 [5 Nov 15:19] Paul Dubois

Noted in 5.7.10, 5.8.0 changelogs.

@Paul I did not find what you mentioned in the 5.7.10 changelog:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-10.html

However, I did find it in the 5.7.9 changelog, which is the affected version:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html

So I thought this was a mistake on your part to think this was related to / duplicate of another bug.

The present bug has been Verified, but is now Closed. This is confusing to me; has it been fixed? Will the fix be included in 5.7.10?

Actually, this bug was related to another bug, but it fixes a different problem, so the previous changelog entry was in error. Here is the corrected version:

Queries that needed to store the result of ST_AsWKB() in a temporary
table could fail with an error message.