| 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: | |
| 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: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 !!!!
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;