MySQL/MariaDB setting values with INTO inside cursor has incorrect iterations

Question

Could someone please explain me why if I run this code:

    BEGIN
        -- Declare loop constructs --
        DECLARE done INT DEFAULT FALSE; 

        -- Declare Person variables --
        DECLARE s_trnsf_id INT(11) DEFAULT NULL;
    DECLARE s_trnsf_trnsp_id INT(11) DEFAULT NULL;
    DECLARE s_trnsf_bacc_id INT(11) DEFAULT NULL;
    DECLARE s_trnsf_cus_id INT(11) DEFAULT NULL;
    DECLARE s_trnsf_bacc_amnt DECIMAL(18,4) DEFAULT NULL;
    DECLARE s_trnsf_recidue_amnt DECIMAL(18,4) DEFAULT NULL;

    DECLARE t_trnsf_id INT(11) DEFAULT NULL;
    DECLARE t_trnsf_recidue_amnt DECIMAL(18,4) DEFAULT NULL;

    DECLARE c1 INT(11) DEFAULT 0;

        -- Declare Cursor --
        DECLARE member_cursor CURSOR FOR 
            SELECT trnsf_id, trnsf_trnsp_id, trnsf_bacc_id, trnsf_cus_id, trnsf_bacc_amnt,trnsf_recidue_amnt
    FROM d_transfers
    WHERE 
    trnsf_bacc_amnt < 0
    AND trnsf_recidue_amnt <> 0
    ORDER BY #trnsf_trnsp_id ASC, trnsf_bacc_id ASC, trnsf_cus_id ASC, 
    trnsf_transfer_ts ASC;

        -- Declare Continue Handler --
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN member_cursor;

        read_loop: LOOP

    SET c1 = c1+1;

            -- Fetch data from cursor --
            FETCH member_cursor 
            INTO s_trnsf_id,
    s_trnsf_trnsp_id,
    s_trnsf_bacc_id,
    s_trnsf_cus_id,
    s_trnsf_bacc_amnt,
    s_trnsf_recidue_amnt;

            -- Exit loop if finished --
            IF done THEN
                LEAVE read_loop;
            END IF;

    ##MARKED_BEGIN######################  
    SELECT trnsf_id#,trnsf_recidue_amnt 
    into t_trnsf_id#,t_trnsf_recidue_amnt 
    from d_transfers 
    WHERE trnsf_bacc_amnt > 0
    AND trnsf_recidue_amnt > 0
    AND trnsf_trnsp_id = s_trnsf_trnsp_id
    AND trnsf_bacc_id = s_trnsf_bacc_id
    AND trnsf_cus_id = s_trnsf_cus_id 
    ORDER BY trnsf_transfer_ts ASC limit 1;
    ##MARKED_END######################  


    END LOOP read_loop;

        CLOSE member_cursor;

    SELECT c1;

    END

It returns:

+-------+
| c1    |
+-------+
| 138 |
+-------+

But if I replace the code between MARKED_BEGIN and MARKED_END with code:

SET t_trnsf_id = (
SELECT trnsf_id#,trnsf_recidue_amnt 
FROM d_transfers 
WHERE trnsf_bacc_amnt > 0
AND trnsf_recidue_amnt > 0
AND trnsf_trnsp_id = s_trnsf_trnsp_id
AND trnsf_bacc_id = s_trnsf_bacc_id
AND trnsf_cus_id = s_trnsf_cus_id 
ORDER BY trnsf_transfer_ts ASC limit 1 
);

I get:

+-------+
| c1    |
+-------+
| 54742 |
+-------+

I think it should return same results!

If I exclude

AND trnsf_trnsp_id = s_trnsf_trnsp_id
AND trnsf_bacc_id = s_trnsf_bacc_id
AND trnsf_cus_id = s_trnsf_cus_id

from the code above it returns correct iterations (number of rows in select statement)

I'm using MariaDB 10.1.10 on MacOS

The continue handler will get called (and thus will end your loop) too if your current `into`-query returns zero results, but not if you replace that part with `set t_trnsf_id = `, which does not have `into`. You may want to add an additional check for that, e.g. if your value cannot be null, try `if t_trnsf_id is null then leave ...`. — Solarflare, Mar 05 '18 at 14:44

sag · Answer 1 · 2018-03-06T10:46:56.203

@Solarflare, thank you for the hint! Adding CONTINUE HANDLER resolves the problem.

BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = FALSE;
  BEGIN
    -- body of handler

    ##MARKED_BEGIN######################  
    SELECT trnsf_id,trnsf_recidue_amnt 
    into t_trnsf_id,t_trnsf_recidue_amnt 
    from d_transfers 
    WHERE trnsf_bacc_amnt > 0
    AND trnsf_recidue_amnt > 0
    AND trnsf_trnsp_id = s_trnsf_trnsp_id
    AND trnsf_bacc_id = s_trnsf_bacc_id
    AND trnsf_cus_id = s_trnsf_cus_id 
    ORDER BY trnsf_transfer_ts ASC limit 1;
    ##MARKED_END######################  

  END;
END;

MySQL/MariaDB setting values with INTO inside cursor has incorrect iterations

1 Answers1