MySQL - SELECT col, function(col) WHERE IN (..) returns same value

Question

I have defined a function that calculates the nearest item using the haversine formula. This function works correctly when I execute the query in an ad-hoc fashion such as:

SELECT CONCAT(address, ", ", deadline, ", NC") AS addr FROM tblTickets WHERE ticket = 'A152012363' LIMIT 1 INTO @Address;
SELECT lat, lng FROM AddressGeocode WHERE address = @Address LIMIT 1 INTO @LAT, @LNG;
SELECT ROUND(( 3959 * acos( cos( radians(@LAT) ) * cos( radians( startlat ) ) * cos( radians( startlon ) - radians(@LNG) ) + sin( radians(@LAT) ) * sin( radians( startlat ) ) ) ) * 5280.0 ) AS distance
FROM tblAsBuiltPolys
ORDER BY distance
LIMIT 1 INTO @RET;
SELECT @RET

When I place the logic in a function so that I can re use it, the result is always the same until I start another session.

I initially thought the issue was that I was using session variables so I adjusted the function to be this:

CREATE FUNCTION `getTicketBuffer`(`ticket` VARCHAR(50))
    RETURNS DOUBLE
    LANGUAGE SQL
    NOT DETERMINISTIC
    READS SQL DATA
    COMMENT ''
BEGIN
DECLARE ADDR VARCHAR(250) DEFAULT NULL;
DECLARE LT DOUBLE DEFAULT NULL;
DECLARE LG DOUBLE DEFAULT NULL;
DECLARE RET DOUBLE DEFAULT NULL;

SET ADDR = (SELECT CONCAT(address, ", ", deadline, ", NC") AS addr FROM tblTickets WHERE ticket = ticket LIMIT 1);
SELECT lat, lng INTO LT, LG FROM AddressGeocode WHERE address = ADDRESS LIMIT 1;
SET RET = (SELECT ROUND(( 3959 * acos( cos( radians(LT) ) * cos( radians( startlat ) ) * cos( radians( startlon ) - radians(LG) ) + sin( radians(LT) ) * sin( radians( startlat ) )     ) ) * 5280.0 ) AS distance
    FROM tblAsBuiltPolys
    ORDER BY distance
    LIMIT 1);
RETURN RET;
END

I am pulling my hair out trying to make this work so that the following query will execute correctly.

SELECT ticket, getTicketBuffer(ticket) AS d 
FROM tblTickets WHERE ticket IN
("A152012363","C152011366","A152012358","C152011309","A152012353","A152011315");

When I exectute the logic directly in a query I get the following:

mysql> SELECT ROUND(( 3959 * acos( cos( radians(@LAT) ) * cos( radians( startlat ) ) * cos( radians( startlon ) - radians(@LNG) ) + sin( radians(@LAT) ) * sin( radians( startlat ) ) ) ) * 5280.0 ) AS distance
-> FROM tblAsBuiltPolys
-> ORDER BY distance
-> LIMIT 1 INTO @RET;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT @RET
    -> ;
+------+
| @RET |
+------+
|  130 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT ticket, getTicketBuffer(ticket) AS d FROM tblTickets WHERE ticket IN("A152012363","C152011366","A152012358","C152011309","A152012353","A152011315");
+------------+------+
| ticket     | d    |
+------------+------+
| A152011315 |   81 |
| A152012353 |   81 |
| A152012358 |   81 |
| A152012363 |   81 |
| C152011309 |   81 |
| C152011366 |   81 |
+------------+------+
6 rows in set (0.12 sec)

I am trying to get the "TicketBuffer" for each ticket in a list of tickets, if this can be made into a view, then that would work as well.

Does anyone see any obvious mistakes? I am not an expert at SQL, particularly stored functions/procedures so I am sure this can be done a better way.

Avoid naming your variables and parameters with a name equal to the columns of your tables. I recommend changing the `ticket` function parameter something like `p_ticket` throughout the code. Also switch `ADDRESS` with `ADDR` in the code of function. — wchiquito, Jul 23 '15 at 16:49
@wchiquito How can I express my love to you? That fixed it!!!!!!! — xandout, Jul 23 '15 at 19:18

MySQL - SELECT col, function(col) WHERE IN (..) returns same value

0 Answers0