MySQL Bugs: #6951: Triggers/Traditional: SET @ result wrong

Bug #6951	Triggers/Traditional: SET @ result wrong
Submitted:	2 Dec 2004 16:49	Modified:	22 May 2006 20:00
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
Under sql_mode='traditional', division by zero causes an error. 
But if the division happens as a result of activating a trigger, 
the target is set to zero even though the server correctly 
returns an error. 

How to repeat:
mysql> create table t1 (col1 int) engine=innodb; 
mysql> set sql_mode='traditional'; 
mysql> create trigger t1_au after update on t1 for each row set @x=5/0; 
mysql> insert into t1 values(0); 
mysql> set @x=10; 
 
mysql> select @x; 
+------+ 
| @x   | 
+------+ 
| 10   | 
+------+ 
 
mysql> update t1 set col1=col1+1; 
ERROR 1365 (22012): Division by 0 
-- This is the correct response by the server. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
|    0 | 
+------+ 
-- Here we see that table t1 was not changed; this is correct. 
 
mysql> select @x; 
+------+ 
| @x   | 
+------+ 
| NULL | 
+------+ 
-- But this should not have happened. The division by zero 
error should have ensured that @x would also remain unchanged.

Thank you for the bug report

In 5.0.17-bk bug is not repeatable in its original form:
mysql> drop table if exists t1;
mysql> create table t1 (col1 int) engine=innodb; 
mysql> set sql_mode='traditional'; 
mysql> create trigger t1_au after update on t1 for each row set @x=5/0; 
mysql> insert into t1 values(0);
mysql> set @x=10; 
mysql> select @x; 
+------+
| @x   |
+------+
| 10   |
+------+
mysql>  update t1 set col1=col1+1;
ERROR 1365 (22012): Division by 0
mysql> select * from t1;
+------+
| col1 |
+------+
|    0 |
+------+

mysql> select @x; 
+------+
| @x   |
+------+
| 10   |
+------+

But if you will turn-off traditional mode for execution of update statement you will encounter similar problem.

mysql> set @@sql_mode:='';
mysql>  update t1 set col1=col1+1;
#Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+

So SET behavior erroneously depends on mode in which statement invoking trigger is executed!

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

Discussed with Monty, it's okay to push the original patch.

Fixed in 5.0.22 and 5.1.11

Noted in 5.0.22, 5.1.11 changelogs.

Within a trigger, <literal>SET</literal> used the SQL mode of
the invoking statement, not the mode in effect at trigger
creation time.

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

ChangeSet@1.2271, 2006-10-05 20:01:30-07:00, malff@weblab.(none) +8 -0
  Bug#20028 (Function with select return no data)
  
  Before this change, THD::abort_on_warning was set in sp_head::execute,
  causing Stored Procedures, Stored Functions and Triggers to treat warnings
  as errors when executing in a strict SQL_MODE.
  
  After this change, THD::abort_on_warning is set in process_triggers in
  sql/sql_trigger.cc, which only affects triggers. Only INSERT and UPDATE
  triggers can elevate the abort_on_warning flag, which does not affect
  DELETE triggers.
  
  This fix is a revision of the change done for Bug#6951:, it produces the
  same results for the INSERT and UPDATE triggers in strict mode, while not
  causing side effects in DELETE triggers, Stored Procedures or Stored
  Functions, which -- according to the definition of a STRICT mode -- should
  not produre errors when no insert or update is performed.
  
  New tests cases have been added.
  
  An existing test case, sp-vars, was, according to the definition of the
  TRADITIONAL mode, incorrect in expecting errors from stored functions when
  no insert or update is present. The expected result for this test has been
  adjusted.
  
  Note to the reviewer: changes in sp-vars needs to be confirmed by an expert,
  my knowledge is too limited in this area.

This buf is closed, BK added a change set here instead of in Bug#20028,
which is related to this change.