Bug #19638 MySQL CASTS string to int and truncates before TRIGGER gets to it
Submitted: 9 May 2006 15:02 Modified: 24 May 2006 16:17
Reporter: Sheeri Cabral (Candidate Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19-standard OS:Linux (Fedora Core 3)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 May 2006 15:02] Sheeri Cabral
Description:
Basically, I write a trigger to change a value BEFORE it's placed into a table, and MySQL gets to it first, casting the string to an int, before the TRIGGER gets to it.  If the trigger is truly BEFORE the INSERT occurs, why does MySQL cast the string to an int before the trigger runs?

See the "how to repeat section" for what I did first, this is what I did to confirm that it was MySQL casting to an int first and then running the trigger:

delimiter //
DROP TRIGGER insertIP //
CREATE TRIGGER insertIP BEFORE INSERT ON IPHistoryNum FOR EACH ROW
BEGIN set @bar=CAST(NEW.ip AS char); SET NEW.ip = INET_ATON(NEW.ip); SET @foo=NEW.ip; END;//
delimiter ;

mysql> insert into IPHistoryNum (ip) VALUES ('192.168.2.50') ;
Query OK, 1 row affected, 1 warnings (0.00 sec)

Warning (Code 1265): Data truncated for column 'ip' at row 1
mysql> select @bar,@foo;
+------+------+
| @bar | @foo |
+------+------+
| 255 | 255 |
+------+------+
1 row in set (0.00 sec)

mysql> select ip from IPHistoryNum;
+------------+
| ip |
+------------+
| 192 |
| 255 |
+------------+

Order of operations seems to be:

send the command
MySQL casts the string to an int
The trigger acts on the int
The line gets inserted into the database.

How to repeat:
how to repeat:

CREATE TABLE `IPHistoryNum` (
`ip` int(10) unsigned NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter //

drop trigger insertIP //

CREATE TRIGGER insertIP BEFORE INSERT ON IPHistoryNum FOR EACH ROW
BEGIN SET NEW.ip = INET_ATON(NEW.ip); END;//

delimiter ;

insert into IPHistoryNum (ip) VALUES ('192.168.1.1');

(you should get:
Warning (Code 1265): Data truncated for column 'ip' at row 1
)

And indeed, only the 192 part showed up.

select ip from IPHistoryNum;
+------------+
| ip |
+------------+
| 192 |
+------------+

Suggested fix:
I'd like it to work like so:

send the command
The trigger acts on the string, converting it to an int
[no casting necessary]
The line gets inserted into the database.

Seems like MySQL "checks" for the appropriate value first.  Isn't that what the trigger is for?
[24 May 2006 16:17] Dmitry Lenev
Hi, Sheeri!

Peter Gulutzan our SQL-standard expert provided the following information on the subject:

---
Of course, the SQL standard doesn't ask you to convert or truncate strings the way MySQL does, so there's nothing explicit for this exact situation.
But when you insert 'A' into a CHAR(3) column, a standard-compliant DBMS pads with spaces so that the value is 'A  '.
Or, when you insert 5.53 into a DECIMAL(3,1) column, a standard-compliant truncates or rounds so that the value is 5.5.
Those are conversions, therefore they are analogous.
When do they happen?
During "store assignment", as discussed in SQL/Foundation document Section 9.2 "<store assignment>", General Rule 2)b).

I look at the standard's SQL/Foundation document, Section 14.8 "<insert statement>".
Its General Rules, leaving out some details, say:
- First evaluate the query expression (in an INSERT ... SELECT) or the constructor (in an INSERT ... VALUES)
- Then for each row, for each column in the row, apply the General Rules of Section 9.2 "<store assignment>"
- Then apply 14.19 "Effect of inserting tables into base tables". (One of 14.19's general rules causes execution of BEFORE  triggers.)

Therefore store assignment happens before BEFORE triggers.
Therefore analogous conversion happens before BEFORE triggers.
---

Also note that MySQL documentation says (http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html):

---
The OLD and NEW keywords enable you to access columns in the rows affected by a trigger.
---

So we say that OLD and NEW keywords are used to refer to the columns in the rows affected by triggering action. In case of BEFORE INSERT trigger they refer to the columns of the rows to be inserted. These columns have types which you have specified at CREATE TABLE time and hold values of these types which were produced by (possibly) converting values that you have specified in your statement to these types.

So behavior which you observe is consistent with both SQL-standard and our manual. Therefore this is not a bug (and I doubt that it makes sense to convert it to valid Feature request since you are proposing behavior which is not standard-compatible).

But feel free to reopen this bug as request for documentation improvement if you think that we should be more explicit about this issue.

Thanks!