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:08]
Prashant Doshi
[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 !!!!