MySQL Bugs: #65839: Spatial field value differs if obtained directly or via binding in stored proc.

Bug #65839	Spatial field value differs if obtained directly or via binding in stored proc.
Submitted:	6 Jul 2012 21:46	Modified:	7 Aug 2012 7:46
Reporter:	Simon Nuttall	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.5.24-0ubuntu0.12.04.1-log	OS:	Linux (Ubuntu 12.04 LTS)
Assigned to:		CPU Architecture:	Any
Tags:	spatial field, stored procedure

Description:
Different results when the value of a spatial field inside a stored procedure is obtained either directly or via intermediate binding to a local variable.

-- This test run shows that the linestrings obtained in the two ways differ.

-- The problem
-- The final value in the first linestring output is incorrect: 2.143215748875e-312
-- The final value in the second linestring output is correct: 52

mysql> call highlightDifferingResults(1);
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.5.24-0ubuntu0.12.04.1-log |
+-----------------------------+
1 row in set (0.00 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| asText(vLinestring)                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 2.143215748875e-312) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| asText(linePoints)                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 52) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

How to repeat:

DROP TABLE IF EXISTS `testTable`;
CREATE TABLE `testTable` (
 `id` smallint NOT NULL PRIMARY KEY,
 `linePoints` linestring NOT NULL COMMENT 'Spatial field',
 SPATIAL KEY `linePoints` (`linePoints`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Create a test value
INSERT testTable
VALUES
(1, LINESTRINGFROMTEXT('LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 52)'));

-- This simple stored procedure highlights the different results.
DROP PROCEDURE IF EXISTS highlightDifferingResults;
DELIMITER //
CREATE PROCEDURE highlightDifferingResults (in pId SMALLINT)
    CONTAINS SQL
    NOT DETERMINISTIC
    SQL SECURITY INVOKER

BEGIN
	-- Define variable to hold the linepoints
	DECLARE vLinestring linestring;

	-- Bind
	SELECT linePoints INTO vLinestring FROM testTable WHERE id = pId;

	-- Version info
	SELECT VERSION();

	-- Display result of binding
	SELECT asText(vLinestring);

	-- Display directly
	SELECT asText(linePoints) FROM testTable WHERE id = pId;
END//
DELIMITER ;

-- This test run shows that the linestrings obtained in the two ways differ.
call highlightDifferingResults(1);

-- The problem
-- The final value in the first linestring output is incorrect: 2.143215748875e-312
-- The final value in the second linestring output is correct: 52

Please, check with a recent version, 5.5.25a. I can not repeat with current code:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
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 13
Server version: 5.5.26-debug-log Source distribution

Copyright (c) 2000, 2011, 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> DROP TABLE IF EXISTS `testTable`;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> CREATE TABLE `testTable` (
    ->  `id` smallint NOT NULL PRIMARY KEY,
    ->  `linePoints` linestring NOT NULL COMMENT 'Spatial field',
    ->  SPATIAL KEY `linePoints` (`linePoints`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.29 sec)

mysql> INSERT testTable
    -> VALUES
    -> (1, LINESTRINGFROMTEXT('LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 52)'));
Query OK, 1 row affected (0.52 sec)

mysql> DROP PROCEDURE IF EXISTS highlightDifferingResults;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE highlightDifferingResults (in pId SMALLINT)
    ->     CONTAINS SQL
    ->     NOT DETERMINISTIC
    ->     SQL SECURITY INVOKER
    -> 
    -> BEGIN
    -> -- Define variable to hold the linepoints
    -> DECLARE vLinestring linestring;
    -> 
    -> -- Bind
    -> SELECT linePoints INTO vLinestring FROM testTable WHERE id = pId;
    -> 
    -> -- Version info
    -> SELECT VERSION();
    -> 
    -> -- Display result of binding
    -> SELECT asText(vLinestring);
    -> 
    -> -- Display directly
    -> SELECT asText(linePoints) FROM testTable WHERE id = pId;
    -> END//
Query OK, 0 rows affected (0.15 sec)

mysql> DELIMITER ;
mysql> call highlightDifferingResults(1);
+------------------+
| VERSION()        |
+------------------+
| 5.5.26-debug-log |
+------------------+
1 row in set (0.05 sec)

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| asText(vLinestring)                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 52) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| asText(linePoints)                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| LINESTRING(0.14852 52.20198,0.148492 52.201935,0.148495 52.201874,0.148561 52.201633,0.148555 52.201481,0.148536 52.201401,0.148498 52.201328,0 52) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

As you can see, values are the same.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".