title: CURSOR in a PROCEDURE that have a sub query
body: <p>I have this procedure in MySQL (a part of it)</p>
  
  <pre><code>DECLARE num_rows INT(11) DEFAULT NULL;
  DECLARE insert_result INT(11) DEFAULT NULL;
  
  DECLARE var_user_id INT DEFAULT NULL;
  DECLARE user_that_posted_this_post INT DEFAULT NULL;
  DECLARE arg_in_group_id INT DEFAULT NULL;
  DECLARE how_many_comments INT DEFAULT 0;
  
  /* Cursor 1 (posts) */
  DECLARE done INT DEFAULT 0;
  DECLARE c1 CURSOR FOR 
      SELECT user_id 
      FROM user_rights 
      WHERE user_rights.right = 101 AND 
      user_rights.group_id  = 
      (
          SELECT 
          posted_in
          FROM posts
          WHERE
          id = arg_post_id
      )
      ORDER BY user_id DESC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  /* See how many comments has this post */
  SELECT count(id) FROM  comments_posts WHERE posted_in = arg_post_id INTO how_many_comments;
  
  /* Fetch the autor of this post and the group_id */
  SELECT posted_by,posted_in FROM posts WHERE id = arg_post_id INTO user_that_posted_this_post,arg_in_group_id;
  
  IF arg_post_id IS NULL OR arg_post_id = ''
  THEN
      SELECT '0' AS response;
  ELSE
      DELETE FROM posts WHERE id = arg_post_id;
  END IF;
  
  /* Increment the notifications for every afected user */
  INSERT INTO t VALUES(0);
  OPEN c1;
  read_loop: LOOP
      FETCH c1 INTO var_user_id;
          IF done THEN
              LEAVE read_loop;
          ELSE
              INSERT INTO t VALUES(var_user_id);
          END IF;
  END LOOP;
  CLOSE c1;
  </code></pre>
  
  <p>and i have some wired things happening (cursor is not taking any values), for example this query</p>
  
  <pre><code>SELECT user_id 
  FROM user_rights 
  WHERE user_rights.right = 101 AND 
  user_rights.group_id  = 
  (
      SELECT 
      posted_in
      FROM posts
      WHERE
      id = arg_post_id
  )
  </code></pre>
  
  <p>returns all users that have right 101 (read) in the group that a post has been posted. The result works perfectly in sql, for example</p>
  
  <pre><code>SELECT user_id 
  FROM user_rights 
  WHERE user_rights.right = 101 AND 
  user_rights.group_id  = 
  (
      SELECT 
      posted_in
      FROM posts
      WHERE
      id = 247
  )
  
  RESULT
  user_id
  1
  3
  2
  16
  17
  20
  19  
  </code></pre>
  
  <p>but when i try to output from the <code>CURSOR</code> </p>
  
  <pre><code>/* Increment the notifications for every afected user */
  INSERT INTO t VALUES(0);
  OPEN c1;
  read_loop: LOOP
      FETCH c1 INTO var_user_id;
          IF done THEN
              LEAVE read_loop;
          ELSE
              INSERT INTO t VALUES(var_user_id);
          END IF;
  END LOOP;
  CLOSE c1;
  </code></pre>
  
  <p>it does not work ... it only writes "0" in <code>t</code> table why is that? I can't use sub query in a cursor?    </p>