MySQL Bugs: #23001: 'traditional' mode + triggers/stored functions result in inconsistent behavior

Bug #23001	'traditional' mode + triggers/stored functions result in inconsistent behavior
Submitted:	5 Oct 2006 5:00	Modified:	12 Oct 2006 14:47
Reporter:	Dmitry Lenev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.27-bk	OS:	Linux (Linux Suse 10.1)
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
This is follow-up for the bug#6951.

Behavior of stored functions and triggers in 'traditional' mode contradicts documentation and is not consistent. See how-to repeat section for more info:

How to repeat:
delimiter |;
set sql_mode='traditional'|
create table t1 (i int default 10)|
insert into t1 values ()|
# both emit only warnings which is ok per documentation
set @a:=1/0|
select 1/0 from t1|

create function f1() returns int begin set @a:=1/0; return 1; end|
# Emits error, but documentation says that only INSERT and
# UPDATE are affected by ERROR_FOR_DIVISION_BY_ZERO mode !!!
--error 1365
select f1()|

create trigger t1_bi before insert on t1 for each row set @a:=1/0|
# Should emit errors per bug #6951
--error 1365
insert into t1 values()|

create table t2 (i int)|
drop trigger t1_bi;
create trigger t1_bi before insert on t1 for each row
begin
  insert into t2 values (new.i);
  set @a:=1/0;
end|
# Does not return error for some reason!
insert into t1 values()|

Thank you for the report.

Verified as described on Linux using last BK sources.

Root cause is related to thd->abort_on_warning=0,
as found during investigation of Bug#20028

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/13139

ChangeSet@1.2275, 2006-10-05 13:05:13-07:00, malff@weblab.(none) +7 -0
  Bug#23001 ('traditional' mode + triggers/stored functions result in
    inconsistent behavior)
  
  As per the definition of SQL_MODE='TRADITIONAL', execution of an INSERT or a
  DELETE statement must fail with an error if a warning is raised during the
  statement execution.
  
  The code covered by this behavior includes the logic present in INSERT or
  UPDATE TRIGGERS, if any.
  
  At the code level, the traditional mode is implemented using
  THD::abort_on_warning.
  
  Before this fix, any statement that is sensitive to SQL_MODE='TRADITIONAL'
  would set THD::abort_on_warning, execute protected code, and reset
  THD::abort_on_warning to 0.
  This in fact destroyed the THD::abort_on_warning property for nested
  statements, because the code is recursive in nature with triggers.
  
  After this fix, THD::abort_on_warning is restored to it's former value
  instead, so that execution of the calling statement can resume with the
  correct execution context.
  
  A test case has been added to exibit the bugs, and shows all the statements
  that are involved.
  
  LOAD DATA INTO TABLE also affects the THD::abort_on_warning property and
  has been fixed for consistency, even if this statement is not allowed
  inside a trigger.

Note to the reviewer:
This fix covers the trigger part only,
the issue with stored functions is a duplicate of Bug#20028.

After very long discussions (kostja, dlenev, marc), the current understanding
of the definition of 'TRADITIONAL' is that thd->abort_on_warning should not be set inside a trigger.

The issue will be addressed by Bug#20028

The side effect of the insert statement (and others) clearing thd->abort_on_warning when executed inside a trigger caused the behavior seen,
but turns out to be "not a bug" due to the change of interpretation of the
meaning of 'TRADITIONAL' with triggers.

See the code review comments from kostja on the proposed patch for details.

Changing from "Patch Pending" to "Not a bug".