MySQL select statement in stored proc returns different results than outside of proc

Question

I cannot figure out why the exact same query that runs outside of a mysql proc does not return the same data inside of it.

Here is the proc.

DELIMITER $$

USE `poll_app`$$

DROP PROCEDURE IF EXISTS `record_new_vote`$$

CREATE DEFINER=`somemysqluser`@`%` PROCEDURE `record_new_vote`(
    p_member_id INT (10),
    p_option_ids VARCHAR(255), -- 12,13,14,15
    p_option_id INT (10),
    p_stream_id INT (10),
    p_scene_id INT (10)
    )
BEGIN

    DECLARE _user_already_voted VARCHAR(255);
    DECLARE _update_off_id INT;
    DECLARE _update_on_id INT;

    -- 1. Query to figure out what to update

        SELECT
            p_member_id, p_option_ids, p_option_id, p_stream_id, p_scene_id;

        SET SESSION group_concat_max_len = 1000000;

        SELECT
            GROUP_CONCAT(selections.status, "~~~", selections.id, "~~~", selections.option_id),
            GROUP_CONCAT(IF(selections.status = "ON", id, NULL)),
            GROUP_CONCAT(IF(selections.status = "OFF" && selections.option_id = p_option_id, selections.id, NULL))
        INTO
            _user_already_voted, _update_off_id, _update_on_id
        FROM
            selections
        WHERE
            selections.member_id = p_member_id
        AND
            selections.option_id IN (p_option_ids)
        AND
            selections.stream_id = p_stream_id
        AND
            selections.scene_id = p_scene_id;

    -- 2. Check to see if the user has already voted on the poll or not


        IF (_user_already_voted IS NOT NULL) THEN

            SELECT
                _user_already_voted, _update_off_id, _update_on_id;         
        END IF;
    END$$

DELIMITER ;

Here is what it outputs

[ [ RowDataPacket {
      p_member_id: 107,
      p_option_ids: '1005,1006,1007,1008',
      p_option_id: 1007,
      p_stream_id: 7,
      p_scene_id: 1 } ],
  [ RowDataPacket {
      _user_already_voted: 'OFF~~~451~~~1005',
      _update_off_id: NULL,
      _update_on_id: NULL } ],

When I run the select query outside of the proc (with the same data plugged in)

    SELECT
        GROUP_CONCAT(`status`, "~~~", id, "~~~", option_id) AS "selections",
        GROUP_CONCAT(IF(`status` = "ON", id, NULL)) AS "id_to_turn_status_off",
        GROUP_CONCAT(IF(`status` = "OFF" && option_id = 1007, id, NULL)) AS "id_to_turn_status_on"
    FROM
        selections
    WHERE
        member_id = 107
    AND
        option_id IN (1005,1006,1007,1008)
    AND
        stream_id = 7
    AND
        scene_id = 1

Results

selections                          id_to_turn_status_off        id_to_turnstatus_on
 OFF~~~451~~~1005,ON~~~452~~~1006            452                      null

As you can see, the group concat string isn't as long, and the id_to_turn_status_off is 452 whereas the variable _update_off_id is NULL when the same query runs in the proc.

I cannot figure this out for the life of me, any help would be GREATLY appreciated.

score 1 · Answer 1 · answered Feb 23 '16 at 19:19

I figured it out guys.

Apparently

IN("1005,1006,1007,1008")

is NOT equivalent to

IN(1005,1006,1007,1008)

In my query inside the proc, since the parameter it accepted was a string it was using the double quote version "1005,1006,1007,1008", where as the query I was running outside was the non double quote version 1005,1006,1007,1008

That is what caused the difference.

I corrected it by using this instead

FIND_IN_SET(selections.option_id, p_option_ids)

or outside of the query

FIND_IN_SET(selections.option_id, "1005,1006,1007,1008")

score 0 · Accepted Answer · answered Feb 23 '16 at 19:32

Glad you figured it out :)

The reason it seemed to partially work is because of typecasting.

p_option_ids VARCHAR(255)
p_option_id INT (10),

selections.option_id IN (p_option_ids)

This is trying to find an INT inside a VARCHAR. MySQL is dynamically casting the string as an int, so it goes down the string until it finds a non-numeric character and truncates the rest. "1005,1006,1007,1008" converts to just 1005 (truncating the ",1006,1007,1008"). That's why it was finding the option_id=1005 row but not the option_id=1007 row.

MySQL select statement in stored proc returns different results than outside of proc

2 Answers2