SELECT ... INTO variable results in NULL or idk you can find all the relevant code on pastebin.

The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend &gt; points) is evaluated FALSE then SQLSTATE 45000 is signalled.

If I evaluate the query separately in PHPMyAdmin 

SELECT `current_points` INTO @var FROM `smartfuel_users` WHERE `id` = user_id;
SELECT @var;


the expected value is returned. I have been searching the whole day to find similar issues. Everywhere the datatype incompatibility has been the solution. After I checked the datatypes, it seems, this one is not the case.

Thanks for any clues, in advance.
The complete code:


CREATE TABLE IF NOT EXISTS `smartfuel_users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `surname` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(50) CHARACTER SET ascii NOT NULL,
    `password` varchar(255) COLLATE utf8_czech_ci NOT NULL,
    `chip_card_id` varchar(20) CHARACTER SET ascii DEFAULT NULL,
    `total_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    `current_points` mediumint(8) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`,`chip_card_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `user_id` INT UNSIGNED, IN `points` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE to_spend MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = user_id AND `expired` = 0 ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO to_spend FROM `smartfuel_users` WHERE `id` = user_id;

    IF to_spend &gt; points THEN
        UPDATE `smartfuel_users` SET `current_points` = `current_points` - points WHERE `id` = user_id;

        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF points &gt; 0 THEN
                IF (loc_points - loc_spent) &gt; points THEN
                    UPDATE `smartfuel_activities` SET `spent` = points WHERE `id` = id;
                    SET points = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = id;
                    SET points = points - loc_points;
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;
So, the ultimate answer to my own question is, as follows.

The stored procedures on MySQL version 5.6.13 don't like, when you use local variables and procedure parameters named exactly as the column names returned by queries used in the procedure's body - even though I used backticks in the queries. Documentation on the use of backticks in MySQL queries.

I have made some changes in the logic of the procedure, as it was not working as I expected. But after renaming the following variables the code did not throw any exceptions, at all.


user_id -> usr_id
points -> to_spend
to_spend -> cur_pts


The edited code:


DELIMITER $$

DROP PROCEDURE IF EXISTS `user_spends_points`$$
CREATE DEFINER=`a30267_palivo`@`%` PROCEDURE `user_spends_points`(IN `usr_id` INT UNSIGNED, IN `to_spend` MEDIUMINT UNSIGNED)
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE cur_pts MEDIUMINT UNSIGNED DEFAULT 0;
    DECLARE v_id INT UNSIGNED;
    DECLARE loc_points MEDIUMINT UNSIGNED;
    DECLARE loc_spent MEDIUMINT UNSIGNED;
    DECLARE cur CURSOR FOR SELECT `id`, `points`, `spent` FROM `smartfuel_activities` WHERE `user_id` = usr_id AND `expired` = 0 AND `points` &lt;&gt; `spent` ORDER BY `created_at` ASC;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLEXCEPTION");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        ROLLBACK;
        CALL `log`("handler", "SQLSTATE");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
        RESIGNAL;
    END;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    BEGIN
        SET done:= TRUE;
        CALL `log`("handler", "NOT FOUND");
        CALL `log`("to_spend", CAST(cur_pts as CHAR(5)));
        CALL `log`("user_id", CAST(usr_id as CHAR(5)));
        CALL `log`("points", CAST(to_spend as CHAR(5)));
    END;

    START TRANSACTION;
    SELECT `current_points` INTO cur_pts FROM `smartfuel_users` WHERE `id` = usr_id;

    IF cur_pts &gt; to_spend THEN    
        OPEN cur;

        userPointsLoop: LOOP
            FETCH cur INTO v_id, loc_points, loc_spent;
            IF done THEN
                LEAVE userPointsLoop;
            END IF;

            IF to_spend &gt; 0 THEN
                IF (loc_points - loc_spent) &gt; to_spend THEN
                    UPDATE `smartfuel_activities` SET `spent` = loc_spent + to_spend WHERE `id` = v_id;
                    SET to_spend = 0;
                    LEAVE userPointsLoop;
                ELSE
                    UPDATE `smartfuel_activities` SET `spent` = loc_points WHERE `id` = v_id;
                    SET to_spend = to_spend - (loc_points - loc_spent);
                END IF;
            ELSE
                LEAVE userPointsLoop;
            END IF;
        END LOOP userPointsLoop;

        CLOSE cur;
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Not enough points to complete expenditure', MYSQL_ERRNO = 1000;
    END IF;

    COMMIT;
END$$

DELIMITER ;