Same SQL code returning different results sets on different MySQL clients

Question

I have some SQL code returning a result set as expected in both of the MySQL clients, namely MySQL Workbench and Sequel Pro. However, when I put the SQL code in a stored procedure (SP), it returns a result set with one line (different than before) on Sequel Pro. The SP still runs fine on MySQL Workbench (correct result set). I don't understand why. Same SQL code, same database, but different result sets on two different SQL clients.

My ultimate goal is to call this SP from a PHP file. When I do that, I get the same result I get from Sequel Pro. What do you think the problem may be?

Here is my SQL code:

SET @prev_val:=24;
SET @num := 0;
SELECT @prev_date := DATE(MAX(logDate)) FROM EmotionsDB.`Log`;
SELECT @prev_date_outerLoop := DATE(MAX(logDate)) FROM EmotionsDB.`Log`;

SELECT dateOfEmotion, emotionID
FROM
(
SELECT dateOfEmotion, diffHour, emotionID,
        @num := IF(dateOfEmotion = @prev_date_outerLoop, @num + 1, 1) AS row_number, 
        @prev_date_outerLoop := dateOfEmotion AS previousDate
FROM
(
    SELECT DATE(logDate) AS dateOfEmotion, TIME(logDate)  AS timeOfEmotion, emotionID,
            IF(DATE(logDate) = @prev_date, @prev_val - HOUR(logDate), 24 - HOUR(logDate)) AS diffHour,
            @prev_val:=HOUR(logDate) AS previousHour,
            @prev_date:=DATE(logDate) AS previousDate
    FROM EmotionsDB.Log
    WHERE DATE(logDate) <> DATE(CURDATE()) AND MONTH(logDate) = MONTH(CURDATE())
    ORDER BY DATE(logDate) DESC, TIME(logDate) DESC
) AS InnerSubQuery
ORDER BY dateOfEmotion DESC, diffHour DESC
    ) AS OuterSubQuery
    WHERE row_number = 1;

Here is the correct result set I get from MySQL Workbench (two columns, "dateOfEmotion", and "emotionID"):

2014-02-17  6
2014-02-14  2
2014-02-13  2
2014-02-07  5
2014-02-06  2
2014-02-05  0
2014-02-02  3

Here is the wrong result set I get from the SP in Sequel Pro (only one column with column name "@prev_date := DATE(MAX(logDate))"):

2014-02-17

Thanks in advance.

Answer 1

It seems that SequelPro is returning interim result sets. You might change the variable assignments to SELECT .. INTO, as follows:

SELECT DATE(MAX(logDate)) INTO @prev_date FROM EmotionsDB.`Log`;
SELECT DATE(MAX(logDate)) INTO @prev_date_outerLoop FROM EmotionsDB.`Log`;

To suppress the interim result sets.