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 > 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

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` <> `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 > 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 > 0 THEN
                IF (loc_points - loc_spent) > 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 ;

StackExchange Database Administrators Q#112285, answer score: 2