Bug #18262 Datatype ranges seem to be checked before a trigger has a chance to change them
Submitted: 15 Mar 2006 18:56 Modified: 15 Mar 2006 22:33
Reporter: Tobias Asplund Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.7, 5.0.18 OS:Windows (Windows, FreeBSD)
Assigned to: CPU Architecture:Any

[15 Mar 2006 18:56] Tobias Asplund
Description:
A trigger filtering data and replacing values of incorrect datatypes isn't allowed to modify the data before the datatype checking for the column itself can abort it.
See example:

eeyore> CREATE TABLE trigtest ( a INT );
Query OK, 0 rows affected (0.01 sec)

eeyore> CREATE TRIGGER trigtest BEFORE INSERT ON trigtest FOR EACH ROW SET NEW.a = IF(NEW.a = '-', NULL, NEW.a);
Query OK, 0 rows affected (0.02 sec)

eeyore> INSERT INTO trigtest VALUES ('-');
Query OK, 1 row affected, 1 warning (0.02 sec)

eeyore> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

eeyore> SELECT * FROM trigtest;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

eeyore> SET SQL_MODE = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.02 sec)

eeyore> INSERT INTO trigtest VALUES ('-');
ERROR 1264 (22003): Out of range value for column 'a' at row 1

So, you can modify the rows inserted, but not if you also want strict mode, and if you run in non-strict mode, you get warnings, despite your data being correct and the trigger firing off perfectly fine.

How to repeat:
-- Copy and paste friendly.

CREATE TABLE trigtest ( a INT );

CREATE TRIGGER trigtest BEFORE INSERT ON trigtest FOR EACH ROW SET NEW.a = IF(NEW.a = '-', NULL, NEW.a);

INSERT INTO trigtest VALUES ('-');

SHOW WARNINGS;

SELECT * FROM trigtest;

SET SQL_MODE = 'STRICT_ALL_TABLES';

INSERT INTO trigtest VALUES ('-');
[15 Mar 2006 21:26] MySQL Verification Team
Thank you for the bug report. Looks like a duplicate of bug:
http://bugs.mysql.com/bug.php?id=18196. Do you agree?
[15 Mar 2006 21:58] Tobias Asplund
agreed, feel free to close as duplicate.
[15 Mar 2006 22:33] MySQL Verification Team
Duplicate of bug already mentioned.