MySQL Bugs: #8153: Stored procedure with subquery and continue handler, wrong result

Bug #8153	Stored procedure with subquery and continue handler, wrong result
Submitted:	26 Jan 2005 23:29	Modified:	7 Sep 2006 0:07
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.21-BK, 5.0.3-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
I make a stored procedure with a CONTINUE handler (so execution should continue if there is an error) and an update statement that causes an error. The handler catches the error, but it continues to do the statement -- that is, the update succeeds. But the update should fail. The continuation should be for the next statement that follows the update, not the update itself.
  

How to repeat:
mysql> delimiter //
mysql> create table tc1 (s1 int)//
Query OK, 0 rows affected (0.01 sec)

mysql> create table tc2 (s1 int)//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tc1 values (1)//
Query OK, 1 row affected (0.00 sec)

mysql> insert into tc2 values (1),(1)//
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create procedure pc () begin declare continue handler for sqlexception begin select 'statement failed'; end; update tc1 set s1=s1+1 where (select s1 from tc2 where s1=1) is null; end;//
Query OK, 0 rows affected (0.00 sec)

mysql> call pc()//
+------------------+
| statement failed |
+------------------+
| statement failed |
+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tc1//
+------+
| s1   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Latest 5.0.21-BK demonstrates the same behaviour.

A test case:

delimiter |
drop table if exists t3, t4|
drop procedure if exists bug8153|
create table t3 (a int)|
create table t4 (a int)|
insert into t3 values (1)|
insert into t4 values (1), (1)|
create procedure bug8153()
begin
  declare continue handler for sqlexception
  begin
    select 'statement failed';
  end;
  update t3 set a=a+1 where (select a from t4 where a=1) is null;
end;|

call bug8153()|
select * from t3|
call bug8153()|
select * from t3|
drop table t3,t4|
drop procedure bug8153|

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9985

ChangeSet@1.2246, 2006-08-02 22:18:49-07:00, malff@weblab.(none) +7 -0
  Bug#8153 (Stored procedure with subquery and continue handler, wrong result)
  
  Before this fix,
  - a runtime error in a statement in a stored procedure with no error handlers
  was properly detected (as expected)
  - a runtime error in a statement with an error handler inherited from a non
  local runtime context (i.e., proc a with a handler, calling proc b) was
  properly detected (as expected)
  - a runtime error in a statement with a *local* error handler was executed
  as follows :
  a) the statement would succeed, regardless of the error condition, (bug)
  b) the error handler would be called (as expected).
  
  The root cause is that functions like my_messqge_sql would "forget" to set
  the thread flag thd->net.report_error to 1, because of the check involving
  sp_rcontext::found_handler_here().
  Failure to set this flag would cause, later in the call stack,
  in Item_func::fix_fields() at line 190, the code to return FALSE and consider
  that executing the statement was successful.
  
  With this fix :
  - error handling code, that was duplicated in different places in the code,
  is now implemented in sp_rcontext::handle_error(),
  - handle_error() correctly sets thd->net.report_error when a handler is
  present, regardless of the handler location (local, or in the call stack).
  
  A test case, bug8153_subselect, has been written to demonstrate the change
  of behavior before and after the fix.
  
  Another test case, bug8153_function_a, as also been writen.
  This test has the same behavior before and after the fix.
  This test has been written to demonstrate that the previous expected
  result of procedure bug18787, was incorrect, since select no_such_function()
  should fail and therefore not produce a result.
  
  The incorrect result for bug18787 has the same root cause as Bug#8153,
  and the expected result has been adjusted.

Approved by email with a few comments.

Fixed review comments, and pushed to 5.0-runtime

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10766

ChangeSet@1.2241, 2006-08-22 18:58:14-07:00, malff@weblab.(none) +3 -0
  Bug#8153 (Stored procedure with subquery and continue handler, wrong result)
  
  Implemented code review comments
  Test cleanup

Pushed into 5.0.25 and 5.1.12

Documented bugfix in 5.0.25 and 5.1.12 changelogs.