| Bug #23804 | "select ... into ... where" in procedure can break execution | ||
|---|---|---|---|
| Submitted: | 31 Oct 2006 13:46 | Modified: | 1 Nov 2006 13:46 |
| Reporter: | Andrey Kochin | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.24 | OS: | Windows (Windows) |
| Assigned to: | CPU Architecture: | Any | |
[31 Oct 2006 16:45]
Hartmut Holzgraefe
what do you mean by "break"? If you're getting a warning like | Warning | 1329 | No data - zero rows fetched, selected, or processed | then this is expected behavior as this kind of query is supposed to return *exactly* one row
[1 Nov 2006 7:42]
Andrey Kochin
"Break" means next linex of procedure does not execute.
CREATE PROCEDURE `Test1`(OUT param1 INTEGER)
BEGIN
select c1 into param1 from Table1 where c2 = 0 limit 1;
select 1, 2, 3, 4; --this line does not work
END;
CREATE PROCEDURE `Test2`()
BEGIN
call Test1();
select 1, 2, 3, 4; --and this line does not work too
END;
[1 Nov 2006 12:25]
Valeriy Kravchuk
It is expected behaviour. Read http://dev.mysql.com/doc/refman/5.0/en/declare-handlers.html opn how to handle situations like this one.
[1 Nov 2006 13:46]
Andrey Kochin
OK. But why this "expected behaviour" differs for "select ... into ..." and "set param1 = (select c1 from ...)"?
Description: construction "select ... into ... where" in procedure can break execution of this procedure if "where" condition is not satisfied. If this procedure is nested, then all procedures breaks. How to repeat: sample table: CREATE TABLE `table1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL ) ENGINE=InnoDB; sample procedure: CREATE PROCEDURE `Test1`(OUT param1 INTEGER) BEGIN select c1 into param1 from Table1 where c2 = 0 limit 1; END; `Test1` breaks if `table1` has no records with c2 = 0 Suggested fix: in my case problem solved like this: set param1 = (select c1 from Table1 where c2 = 0 limit 1);