0

I've created a stored procedure in MySQL (server version: 5.0.83-community) as shown below. When I try to call it I get a really strange output that differs a whole lot from when I try to call the query outside the procedure. To show what I mean, I've unioned the two different calls so you can see the very different outputs.

I know that a CONCAT will return a NULL variable if any argument is NULL as explained in CONCAT in stored procedure returns null, but I really don't understand why it returns NULL in one scenario as below while not in the other. In fact, both callings should return the exact same result. Any ideas on why this happens? (The delimiter // is used by phpMyAdmin - hence the weird lack of declaration).

NOTE: I've also looked at Stored Procedure - OUT param returns null when selected and Mysql - Stored procedure OUT variable return null but none of them lead me into any sightful conclusions. Nor does this article help me Why does this query return different results when in a stored procedure? since MySQL throws this error when I try to invoke SET ANSI_NULLS ON; #1193 - Unknown system variable 'ANSI_NULLS'.

DROP PROCEDURE `GetVoteID`//
CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT vote_id CHAR(11))
BEGIN
    SET @eliminated_candidates := (
        SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))
        FROM vote_final
        WHERE votes = minimum_votes
        AND round_id = current_round
    );
    SELECT GROUP_CONCAT(CONVERT(vote_id, CHAR(11))) INTO vote_id
    FROM votes_copy
    WHERE FIND_IN_SET(@eliminated_candidates, vote_candidate_1) != 0;
END


CALL GetVoteID('5','1',@vote_id);
SELECT *
FROM (
    (
        SELECT @vote_id vote_id
    ) UNION (
        SELECT GROUP_CONCAT(CONVERT(vote_id, CHAR(11)))
        FROM votes_copy
        WHERE FIND_IN_SET((
            SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))
            FROM vote_final
            WHERE votes = '5'
            AND round_id = '1'
        ), vote_candidate_1) != 0
    )
) t;

Outputs:

+--------------+
| vote_id      |
+--------------+
| NULL         |  <- incorrect result
+--------------+
| 2,8,33,44,48 |  <- correct result
+--------------+
Community
  • 1
  • 1
Kristofer Gisslén
  • 1,252
  • 1
  • 11
  • 28
  • Can you provide some test data? – wchiquito Jul 15 '15 at 11:25
  • What kind of test data other than the one provided in the question would you like? Like a sqlfiddle? – Kristofer Gisslén Jul 15 '15 at 11:29
  • A SQL Fiddle would help. – wchiquito Jul 15 '15 at 11:40
  • Okay. I deleted my previous comment because I didn't get the fidde to work correctly, but now I've managed to at least get the procedure into the schema builder. I can't get it to work though. I've commented it out - if you know of how to get it to work please let me know. [sqlfiddle of some test data](http://sqlfiddle.com/#!9/3f528/1). – Kristofer Gisslén Jul 15 '15 at 12:33
  • You need to change the `DELIMITER` for the stored procedure can work [SQL Fiddle](http://sqlfiddle.com/#!9/e657a/1). `votes_copy` table doesn't exist. – wchiquito Jul 15 '15 at 13:21
  • Ouch - I missed to change it to vote_orders - not votes_copy. My bad. So, based on this test data what could lead to the different results? – Kristofer Gisslén Jul 15 '15 at 13:25
  • Once again I missed on the details in the fiddle. It shall be table votes - nor votes_copy or vote_orders. Furthermore vote_candidate_1 should be vote_candidate_a. I'm working on my iPad and I can't copy/paste the entire fields into a new fiddle on it. One could say that sqlfiddle isn't tablet proof... I'll fix it when I get home. :) – Kristofer Gisslén Jul 15 '15 at 13:57
  • 2
    A couple of suggestions: avoid naming your variables as columns of your tables and the length of the variable `INOUT` is exceeded (you must evaluate the length to be assigned). To test work properly, change: `... INOUT vote_id CHAR(11) ...` by `... INOUT _vote_id CHAR(50) ...` and `... CHAR(11))) INTO vote_id ...` by `... CHAR(11))) INTO _vote_id ...` in the stored procedure. See [SQL Fiddle](http://sqlfiddle.com/#!9/3a911/2). – wchiquito Jul 15 '15 at 14:51
  • Wonderful. This really solved it. Could you extend an explanation to it as an answer and I'll reward it with an answer mark? ;) – Kristofer Gisslén Jul 15 '15 at 16:50

0 Answers0