MySQL Bugs: #5889: Exit handler for a warning doesn't hide the warning in trigger

Bug #5889	Exit handler for a warning doesn't hide the warning in trigger
Submitted:	5 Oct 2004 2:57	Modified:	3 May 2006 9:12
Reporter:	Peter Gulutzan
Status:	Verified
Category:	Server: SP	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Davi Arnaut	Target Version:
Triage:	Triaged: D3 (Medium)

Description:
If a trigger causes a warning, I see "Warnings: 1" 
after executing the statement that activates the 
triger. But the warning is handled (by a handler) 
within the trigger. So "Warnings: 1" is confusing. 

How to repeat:
mysql> create trigger x4_bu before update on x4 for each row begin declare exit handler 
for sqlwarning set new.col1=7; set new.col1 = 77777777777; end;// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> update x4 set col2 = 1;// 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1  Changed: 1  Warnings: 1 
 
mysql> show warnings// 
Empty set (0.00 sec)

Verified with 5.0.2-alpha-debug-log.

Test case:

create table x4(col1 int,col2 int);
insert into x4 values (7,5);
delimiter //
create trigger x4_bu before update on x4 for each row
begin
declare exit handler 
for sqlwarning set new.col1=7; set new.col1 = 77777777777;
end //
update x4 set col2 = 1// 
show warnings//

This bug is still repeatable in MySQL 5.0.17

Please note that number of "warnings" in "Rows matched: 1  Changed: 1  Warnings: 1"
string is not really number of warnings produced by the update statement. It is rather
number of new values for fields which were adjusted to fit in these fields (to correspond
restrictions of column types).
So it is OK that this number does not correspond to number of 'real' warnings. But it is
still open question if number of fields adjusted for update statement should include
number of fields adjusted in trigger that this statement has invoked.

Here is another test case.

mysql> delimiter //
mysql> create table t (t_1 int)//
Query OK, 0 rows affected (0.00 sec)

mysql> create table u (u_1 char(1))//
Query OK, 0 rows affected (0.28 sec)

mysql> create trigger t_bi before insert on t for each row begin insert into u values
('ab'); insert into u values ('a'); end//
Query OK, 0 rows affected (0.00 sec)

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

mysql> show warnings//
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'u_1' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

The first "insert into u" statement causes the warning.
The second "insert into u" statement should clear the warning.
But it doesn't, so the user sees it.