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: | |
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
[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!