MySQL Bugs: #69517: Inconsistent result in stored procedure

Bug #69517	Inconsistent result in stored procedure
Submitted:	19 Jun 2013 16:25	Modified:	2 Oct 2013 20:59
Reporter:	Jeff Russo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.31	OS:	Other (Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any
Tags:	point, spatial, stored procedure

Description:
I am making a stored procedure to see if the distance between two POINTs is less than a mile.  Its is calling a function to do the comparision.

The result is not consistent and changes unexpectedly.  Passing (0, 0) should always return 'Not within a mile'.  Sometimes it takes many tries for it to change.

Passing the values directly to my function does not produce inconsistency.

This is happening on my machine as well as our AWS instance.

How to repeat:
| version                 | 5.5.31-0ubuntu0.12.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |

1.)  I am creating a table to store locations as below (with a row of test data):

CREATE TABLE locales(
    locationId   SERIAL,
    street       VARCHAR(100) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    state        CHAR(2) NOT NULL,
    zip          VARCHAR(5) NOT NULL,
    coordinates POINT UNIQUE NOT NULL
)ENGINE=InnoDB;

INSERT INTO locales VALUES(DEFAULT, '426 S Main St.', 'Town', 'ST', '83838', GEOMFROMTEXT('POINT(41.368186 -75.737603)'));

2.) I then create this function which checks if two locations are within 1 mile of each other:

DELIMITER ~
DROP FUNCTION IF EXISTS pointWithinRadiusOfPoint~
CREATE FUNCTION pointWithinRadiusOfPoint(
    _pt_a   POINT,
    _pt_b   POINT
)
RETURNS BOOLEAN
NO SQL
SQL SECURITY DEFINER
BEGIN

    DECLARE dLat DOUBLE;
    DECLARE dLon DOUBLE;
    DECLARE a    DOUBLE;

    SET dLat = RADIANS(X(_pt_b) - X(_pt_a));
    SET dLon = RADIANS(Y(_pt_b) - Y(_pt_a));
    SET a = SIN(dLat/2) * SIN(dLat/2) + SIN(dLon/2) * SIN(dLon/2) * COS(RADIANS(X(_pt_a))) * COS(RADIANS(X(_pt_b)));

    RETURN (7919.76836 * ASIN(SQRT(a))) <= 1;

END~
DELIMITER ;

3.)  I create this procedure which gets the coordinates from the locales table and checks if the passed coordinates are within a mile of the coordinates in the table.

DELIMITER ~
DROP PROCEDURE IF EXISTS checkLessThanMile~
CREATE PROCEDURE checkLessThanMile(
    IN _latitude DECIMAL(9,6),
    IN _longitude DECIMAL(9,6)
)
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

    DECLARE eventLocation POINT DEFAULT NULL;

    START TRANSACTION;

    SELECT coordinates INTO eventLocation FROM locales WHERE locationId = 1;

    SELECT eventLocation;
    SELECT eventLocation, X(eventLocation), Y(eventLocation), pointWithinRadiusOfPoint(GEOMFROMTEXT(CONCAT('POINT(', _latitude, ' ', _longitude, ')')), eventLocation) AS within;

    IF eventLocation IS NOT NULL AND NOT pointWithinRadiusOfPoint(GEOMFROMTEXT(CONCAT('POINT(', _latitude, ' ', _longitude, ')')), eventLocation) THEN
        SELECT 'Not within a mile';
    ELSE
        SELECT 'OK';
    END IF;

    COMMIT;

END~
DELIMITER ;

4.)  Here is the output from running this where I am getting inconsistent results:

mysql> CALL checkLessThanMile(0, 0);
+---------------------------+
| eventLocation             |
+---------------------------+
|        +l� �D@��6�4�R�        |
+---------------------------+
1 row in set (0.00 sec)

+---------------------------+------------------+------------------+--------+
| eventLocation             | X(eventLocation) | Y(eventLocation) | within |
+---------------------------+------------------+------------------+--------+
|        +l� �D@��6�4�R�        |        41.368186 |       -75.737603 |      0 |
+---------------------------+------------------+------------------+--------+
1 row in set (0.00 sec)

+-------------------+
| Not within a mile |
+-------------------+
| Not within a mile |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL checkLessThanMile(0, 0);
+---------------------------+
| eventLocation             |
+---------------------------+
|        +l� �D@��6�4�R�        |
+---------------------------+
1 row in set (0.00 sec)

+---------------------------+------------------+------------------+--------+
| eventLocation             | X(eventLocation) | Y(eventLocation) | within |
+---------------------------+------------------+------------------+--------+
|        +l� �D@��6�4�R�        |        41.368186 |       -75.737603 |      0 |
+---------------------------+------------------+------------------+--------+
1 row in set (0.00 sec)

+----+
| OK |
+----+
| OK |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

5.) After disconnecting and reconnecting:

mysql> CALL checkLessThanMile(0, 0);
+---------------------------+
| eventLocation             |
+---------------------------+
|        +l� �D@��6�4�R�        |
+---------------------------+
1 row in set (0.00 sec)

+---------------------------+------------------+------------------+--------+
| eventLocation             | X(eventLocation) | Y(eventLocation) | within |
+---------------------------+------------------+------------------+--------+
|                           |             NULL |             NULL |   NULL |
+---------------------------+------------------+------------------+--------+
1 row in set (0.00 sec)

+----+
| OK |
+----+
| OK |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 

Suggested fix:
Not sure which part is going wrong.  I don't know why eventLocation is suddenly null.  This is on my local machine which no one else can connect to, so the data is not changing.

It's reading freed memory.  Hence, crash is even possible in addition to wrong results.

Version: '5.6.13'   MySQL Community Server (GPL)
 Thread 22:
Invalid read of size 1
at4: memcpy@@GLIBC_2.14 (mc_replace_strmem.c:882)
by: Protocol::net_store_data(unsigned char const*, unsigned long) (protocol.cc:58)
by: Protocol_text::store(Field*) (protocol.cc:1121)
by: Protocol::send_result_set_row (protocol.cc:847)
by: select_send::send_data (sql_class.cc:2489)
by: JOIN::exec (sql_executor.cc:148)
by: mysql_execute_select (sql_select.cc:1100)
by: mysql_select (sql_select.cc:1221)
by: handle_select (sql_select.cc:110)
by: execute_sqlcom_select (sql_parse.cc:5044)
by: mysql_execute_command (sql_parse.cc:2602)
by: sp_instr_stmt::exec_core (sp_instr.cc:881)
by: sp_lex_instr::reset_lex_and_exec_core (sp_instr.cc:382)
by: sp_lex_instr::validate_lex_and_execute_core(THD*, unsigned int*, bool) (sp_instr.cc:610)
by: sp_instr_stmt::execute (sp_instr.cc:788)
by: sp_head::execute (sp_head.cc:645)
by: sp_head::execute_procedure(THD*, List<Item>*) (sp_head.cc:1307)
by: mysql_execute_command (sql_parse.cc:4520)
by: mysql_parse (sql_parse.cc:6185)
by: dispatch_command (sql_parse.cc:1332)
by: do_handle_one_connection (sql_connect.cc:977)
by: handle_one_connection (sql_connect.cc:893)
by: start_thread (pthread_create.c:309)
by: clone (clone.S:115)
==3393==  Address 0x141c65f0 is 144 bytes inside a block of size 16,504 free'd
at: free (vg_replace_malloc.c:468)
by: mem_area_free (mem0pool.cc:519)
by: mem_heap_block_free (mem0mem.cc:518)
by: mem_heap_free_func (mem0mem.ic:495)
by: row_mysql_prebuilt_free_blob_heap (row0mysql.cc:169)
by: ha_innobase::reset (ha_innodb.cc:11849)
by: close_thread_table (sql_base.cc:1502)
by: close_open_tables (sql_base.cc:1241)
by: mysql_execute_command (sql_parse.cc:4965)
by: sp_instr_stmt::exec_core (sp_instr.cc:881)
by: sp_lex_instr::reset_lex_and_exec_core (sp_instr.cc:382)
by: sp_lex_instr::validate_lex_and_execute_core (sp_instr.cc:610)
by: sp_instr_stmt::execute (sp_instr.cc:788)
by: sp_head::execute (sp_head.cc:645)
by: sp_head::execute_procedure (sp_head.cc:1307)
by: mysql_execute_command (sql_parse.cc:4520)
by: mysql_parse (sql_parse.cc:6185)
by: dispatch_command (sql_parse.cc:1332)
by 0x6FDF96: do_handle_one_connection (sql_connect.cc:977)
by 0x6FE01F: handle_one_connection (sql_connect.cc:893)
by 0x3679C07D13: start_thread (pthread_create.c:309)
by 0x36798F168C: clone (clone.S:115)

Reduced testcase to show a problem:
Run mysqld in valgrind, or notice varying results from the call to p1.

-----
delimiter ;
drop table if exists `t1`;
create table `t1`(`z` point)engine=innodb;
insert into `t1` values(geomfromtext('point(1 1)'));

delimiter $
drop procedure if exists `p1`$
create procedure `p1`()
begin
	declare `e` point default null;
    select `z` into `e` from `t1`;
    select now(),version(),hex(`e`),`e`;
end $
delimiter ;
call `p1`();
-----

Noted in 5.7.3 changelog.

Reads of Geometry values within a stored program could read
already-freed memory and produce incorrect results.