Bug #61777 Cursor loop stops prematurely due to SELECT INTO that returns zero rows
Submitted: 6 Jul 2011 20:08 Modified: 6 Jul 2011 22:08
Reporter: Prashant Doshi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.13 OS:Windows
Assigned to: CPU Architecture:Any
Tags: cursor, NO ROWS, PREMATURE, select into, stop, ZERO ROWS

[6 Jul 2011 20:08] Prashant Doshi
Description:
Stored procedure structure is outlined below.

The loop in this stored procedure exits prematurely if the SELECT INTO statement does not return any rows. The data being fectched in the SELECT statment is of type DOUBLE.

CREATE PROCEDURE sp_proc()
BEGIN

declare l_last_row int;

declare l_plan_fee double(10,2);
declare l_pass_fee double(10,2);
declare l_icsi_fee double(10,2);
declare l_cxl_plan_fee double(10,2);
declare l_cxl_pass_fee double(10,2);
declare l_xfr_plan_fee double(10,2);
declare l_xfr_pass_fee double(10,2);

declare c1 cursor for
  select ....

DECLARE CONTINUE HANDLER FOR NOT FOUND set l_last_row = 1;

open c1;
c1_loop: LOOP

  FETCH c1 INTO ....

  if l_last_row = 1 then
    leave c1_loop;     
  end if;

-- other code ...

-- offending statement below ...

          select ifnull(f.PLAN_FEE,0), ifnull(f.PASS_FEE,0), ifnull(f.ICSI_FEE,0), 
            ifnull(f.CANCELLED_PRIOR_RETRIEVAL_FEE,0) as CANCELLED_PLAN_FEE, ifnull(f.CANCELLED_PASS_FEE,0), 
            ifnull(f.CANCELLED_PRIOR_TRANSFER_FEE,0) as TRANSFER_PLAN_FEE, ifnull(f.TRANSFER_PASS_FEE,0)
          into l_plan_fee, l_pass_fee, l_icsi_fee, l_cxl_plan_fee, l_cxl_pass_fee, l_xfr_plan_fee, l_xfr_pass_fee
          from practice_fee_schedule f
          where f.location_id = l_location and f.eff_end_dt is null 
              and f.treatment_id = l_perform_id 
              and l_medically_cleared_dt between f.effective_date and ifnull(f.end_date, '2099-12-31')
          order by f.effective_date desc
          limit 1;

-- other code ...

END LOOP c1_loop;
close c1;

 

How to repeat:
Create a stored procedure as outlined above ...

Suggested fix:
Prevent execution of SELECT INTO statement as follows when there is no matching row:

declare l_exists int;

 set l_exists = (select exists (
            select * from practice_fee_schedule f
            where f.location_id = l_location and f.eff_end_dt is null 
              and f.treatment_id = l_perform_id 
              and l_medically_cleared_dt between f.effective_date and ifnull(f.end_date, '2099-12-31')
          ));
          
        if l_exists = 1 then
          select ifnull(f.PLAN_FEE,0), ifnull(f.PASS_FEE,0), ifnull(f.ICSI_FEE,0), 
            ifnull(f.CANCELLED_PRIOR_RETRIEVAL_FEE,0) as CANCELLED_PLAN_FEE, ifnull(f.CANCELLED_PASS_FEE,0), 
            ifnull(f.CANCELLED_PRIOR_TRANSFER_FEE,0) as TRANSFER_PLAN_FEE, ifnull(f.TRANSFER_PASS_FEE,0)
          into l_plan_fee, l_pass_fee, l_icsi_fee, l_cxl_plan_fee, l_cxl_pass_fee, l_xfr_plan_fee, l_xfr_pass_fee
          from practice_fee_schedule f
          where f.location_id = l_location and f.eff_end_dt is null 
              and f.treatment_id = l_perform_id 
              and l_medically_cleared_dt between f.effective_date and ifnull(f.end_date, '2099-12-31')
          order by f.effective_date desc
          limit 1;
        end if;
[6 Jul 2011 20:49] Valeriy Kravchuk
Our manual, http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html, clearly says about NOT FOUND:

"NOT FOUND is shorthand for the class of SQLSTATE values that begin with '02'. This is relevant only within the context of cursors and is used to control what happens when a cursor reaches the end of a data set. If no more rows are available, a No Data condition occurs with SQLSTATE value 02000. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition). An example is shown in Section 12.7.5, “Cursors”. This condition also occurs for SELECT ... INTO var_list statements that retrieve no rows."

So, this is a documented behavior and, thus, not a bug. Just put SELECT ... INTO that may return no rows into a separate BEGIN ... END block with its own CONTINUE handler for NOT FOUND as a workaround...
[6 Jul 2011 22:08] Prashant Doshi
Thank you for the prompt response !!!!