Bug #65410 | sql_mode TRADITIONAL doesn't apply to VARCHAR | ||
---|---|---|---|
Submitted: | 24 May 2012 9:20 | Modified: | 24 May 2012 11:01 |
Reporter: | Paolo Montrasio | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.1 | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | type conversion |
[24 May 2012 9:20]
Paolo Montrasio
[24 May 2012 10:44]
Valeriy Kravchuk
Can you name RDBMS where this kind of implicit data type conversion leads to error? Does our manual, http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_strict_all_tables, say anything about your specific cases? My point is: MySQL works as designed, and as other databases traditionally work. Your request for more strict handling of implicit conversions of numbers to strings during INSERT is valid, but it is a new feature request.
[24 May 2012 11:01]
Paolo Montrasio
Thank you for the quick feedback. I quote from the documentation link you added: "Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range." I think that 0 (the integer) assigned to a varchar column is a "wrong data type for the column". I tried with only set session sql_mode=STRICT_ALL_TABLES because I read "For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value", but I was still able to insert an integer into a varchar. Might this resolve to be a documentation bug plus a request for a new feature? As for other databases where that SQL statement fails, this is what happens on postgresql 8.4, with the stock Ubuntu configuration: create table some_text (the_text varchar(255)); insert into some_text (the_text) values (100); This works as in MySQL (I confess I assumed that it wouldn't and I will check their documentation), but at least this fails: select * from some_text where the_text = 100; ERROR: operator does not exist: character varying = integer LINE 1: select * from some_text where the_text = 100; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. and it might be enough to catch some bugs.