An Issue with storing data with Stored Procedure Cursors

Question

Here I'm trying to implement the below stored-procedure. But the value is not saving as expected

DELIMITER $$

USE `someDB`$$

DROP PROCEDURE IF EXISTS `AAB`$$

CREATE PROCEDURE `AAB`(IN `feed_setting_user_id` BIGINT)
BEGIN
DECLARE v_friendID, FoFID BIGINT; # use datatype which is used for u.ID used int based on ur result
DECLARE exit_loop, exit_loop1 BOOLEAN DEFAULT FALSE;

DECLARE friend_cursor CURSOR FOR 

    SELECT `u`.`ID` FROM `users` `u`  
    WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1` 
    WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id ) 
    OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id) 
    AND `u1`.`ID` != feed_setting_user_id); 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

DROP TEMPORARY TABLE IF EXISTS unique_tbl; #precaution: when sp stops with error 
CREATE TEMPORARY TABLE unique_tbl (user_id BIGINT UNIQUE); #to avoid duplicate added unique

INSERT INTO unique_tbl VALUES (feed_setting_user_id); # added input from sp

    OPEN friend_cursor;
    friend_loop: LOOP

        FETCH friend_cursor INTO v_friendID;

        IF exit_loop THEN
            LEAVE friend_loop;
        ELSE

            REPLACE INTO unique_tbl VALUES (v_friendID); # since we need all unique id's using replace if exists

            fof: BEGIN

                DECLARE friend_of_friend_cursor CURSOR FOR 


                    SELECT `u`.`ID` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID` 
            IN (SELECT `u1`.`ID` FROM `users` `u1` 
            WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = v_friendID AND `uf`.`friendid` != v_friendID) 
            OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = v_friendID AND `uf2`.`userid` != v_friendID) 
            AND `u1`.`ID` != v_friendID ); 

                DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop1 = TRUE;

                OPEN friend_of_friend_cursor;
                friend_of_friend_loop: LOOP

                    FETCH friend_of_friend_cursor INTO FoFID;

                    IF exit_loop1 THEN
                        LEAVE friend_of_friend_loop;
                    END IF;

                    REPLACE INTO unique_tbl VALUES (FoFID);

                END LOOP friend_of_friend_loop;
                CLOSE friend_of_friend_cursor;
            END;
        END IF;
    END LOOP friend_loop;
    CLOSE friend_cursor;

SELECT GROUP_CONCAT(user_id) FROM unique_tbl; #this will show result in comma seperated 2,34,56,78

#cleanup
DROP TEMPORARY TABLE IF EXISTS unique_tbl;

END$$

DELIMITER ;

Actual Results:

+-----------------------------------+
| GROUP_CONCAT(user_id)             |
+-----------------------------------+
| 1,2,3,5,6,7,8,9,11,14,15,19,20,26 |
+-----------------------------------+
1 row in set (0,01 sec)

Query OK, 0 rows affected (0,01 sec)

Expected Results:

+--------------------------------------+
| GROUP_CONCAT(user_id)                |
+--------------------------------------+
| 1,2,3,5,6,7,8,9,11,14,15,19,20,25,26 |
+--------------------------------------+
1 row in set (0,01 sec)

Query OK, 0 rows affected (0,01 sec)

ID = 25 has been left off

Debugging the above two SQL's:

Setting feed_setting_user_id = 15 to the below SQL query

SELECT
  `u`.`ID`
FROM
  `users` `u`
WHERE `u`.`ID` IN
  (SELECT
    `u1`.`ID`
  FROM
    users `u1`
  WHERE `u1`.`ID` IN
    (SELECT
      `uf`.`friendid`
    FROM
      user_friends `uf`
    WHERE `uf`.`status` = '2'
      AND `uf`.`userid` = feed_setting_user_id)
    OR `u1`.`ID` IN
    (SELECT
      `uf2`.`userid`
    FROM
      `user_friends` `uf2`
    WHERE `uf2`.`status` = '2'
      AND `uf2`.`friendid` = feed_setting_user_id)
    AND `u1`.`ID` != feed_setting_user_id);

Results:

+----+
| ID |
+----+
|  1 |
| 14 |
| 26 |
+----+
3 rows in set (0,00 sec)

For recursively substituting the above ID's to v_friendID and keeping feed_setting_user_id = 15 and running the below SQL

SELECT
  `u`.`ID`
FROM
  `users` `u`
WHERE `u`.`ID` NOT IN (feed_setting_user_id)
  AND `u`.`ID` IN
  (SELECT
    `u1`.`ID`
  FROM
    `users` `u1`
  WHERE `u1`.`ID` IN
    (SELECT
      `uf`.`friendid`
    FROM
      `user_friends` `uf`
    WHERE `uf`.`status` = '2'
      AND `uf`.`userid` = v_friendID
      AND `uf`.`friendid` != v_friendID)
    OR `u1`.`ID` IN
    (SELECT
      `uf2`.`userid`
    FROM
      `user_friends` `uf2`
    WHERE `uf2`.`status` = '2'
      AND `uf2`.`friendid` = v_friendID
      AND `uf2`.`userid` != v_friendID)
    AND `u1`.`ID` != v_friendID);

Results are as follows:

Substituting for v_friendID = 1 and feed_setting_user_id = 15

+----+
| ID |
+----+
|  2 |
|  3 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 11 |
| 19 |
| 20 |
+----+
10 rows in set (0,00 sec)

Substituting for v_friendID = 14 and feed_setting_user_id = 15

+----+
| ID |
+----+
| 15 |
| 25 |
+----+
2 rows in set (0,00 sec)

Substituting for v_friendID = 26 and feed_setting_user_id = 15

Empty set (0,00 sec)

The ID with value=15 is being saved into unique_tbl. But not for ID with value=25

I did not check if that could actually be causing your problem (or if there are others), but it sprang out to me, so you should give it a try: you did not reset `exit_loop1` to `false` before your `inner friend_of_friend_loop`-loop. So the 2nd time you go there, it is already true and will stop at once. — Solarflare, Jul 23 '19 at 08:29
Is there any alternative better approach to do the same? @Solarflare — Nɪsʜᴀɴᴛʜ ॐ, Jul 23 '19 at 09:29
You mean as an alternative to your procedure? Probably, it looks like [a recursive query](https://stackoverflow.com/q/20215744) might be able to achieve the same (although, as I said, I didn't actually fully check your code). Although to decide if it's "better" (faster/easier to understand/adaptable/...) is up to you. — Solarflare, Jul 23 '19 at 09:48

score 0 · Answer 1 · answered Jul 23 '19 at 09:25

BEGIN
DECLARE v_friendID, FoFID BIGINT; # use datatype which is used for u.ID used int based on ur result
DECLARE exit_loop, exit_loop1 BOOLEAN DEFAULT FALSE;

DECLARE friend_cursor CURSOR FOR 

    SELECT `u`.`ID` FROM `users` `u`  
    WHERE `u`.`ID` IN (SELECT `u1`.`ID` FROM users `u1` 
    WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM user_friends `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = feed_setting_user_id ) 
    OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = feed_setting_user_id) 
    AND `u1`.`ID` != feed_setting_user_id); 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

DROP TEMPORARY TABLE IF EXISTS unique_tbl; #precaution: when sp stops with error 
CREATE TEMPORARY TABLE unique_tbl (user_id BIGINT UNIQUE); #to avoid duplicate added unique

INSERT INTO unique_tbl VALUES (feed_setting_user_id); # added input from sp

    OPEN friend_cursor;
    friend_loop: LOOP

        FETCH friend_cursor INTO v_friendID;

        IF exit_loop THEN
            LEAVE friend_loop;
        ELSE

            REPLACE INTO unique_tbl VALUES (v_friendID); # since we need all unique id's using replace if exists

            fof: BEGIN

                DECLARE friend_of_friend_cursor CURSOR FOR 


                    SELECT `u`.`ID` FROM `users` `u` WHERE `u`.`ID` NOT IN (feed_setting_user_id) AND `u`.`ID` 
            IN (SELECT `u1`.`ID` FROM `users` `u1` 
            WHERE `u1`.`ID` IN (SELECT `uf`.`friendid` FROM `user_friends` `uf` WHERE `uf`.`status` = '2' AND `uf`.`userid` = v_friendID AND `uf`.`friendid` != v_friendID) 
            OR `u1`.`ID` IN (SELECT `uf2`.`userid` FROM `user_friends` `uf2` WHERE `uf2`.`status` = '2' AND `uf2`.`friendid` = v_friendID AND `uf2`.`userid` != v_friendID) 
            AND `u1`.`ID` != v_friendID ); 

                DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop1 = TRUE;

                OPEN friend_of_friend_cursor;
                friend_of_friend_loop: LOOP

                    FETCH friend_of_friend_cursor INTO FoFID;

                    IF exit_loop1 THEN
                        LEAVE friend_of_friend_loop;
                    END IF;

                    REPLACE INTO unique_tbl VALUES (FoFID);

                END LOOP friend_of_friend_loop;
                CLOSE friend_of_friend_cursor;
                SET exit_loop1 = FALSE; 
            END;
        END IF;
    END LOOP friend_loop;
    CLOSE friend_cursor;
    SET exit_loop = FALSE;

SELECT GROUP_CONCAT(user_id) FROM unique_tbl; #this will show result in comma seperated 2,34,56,78

#cleanup
DROP TEMPORARY TABLE IF EXISTS unique_tbl;

END$$

DELIMITER ;

An Issue with storing data with Stored Procedure Cursors

1 Answers1