MySQL Bugs: #65410: sql_mode TRADITIONAL doesn't apply to VARCHAR

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:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	type conversion

Description:
Even in strict mode mysql converts integers into strings before inserting them into a varchar field and before using them to query those fields.

It should stop with an error.

Verified on: Server version: 5.1.62-0ubuntu0.11.04.1 (Ubuntu)
I think it applies to all versions for all OSes.

How to repeat:
mysql> set session sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table some_text (the_text varchar(255));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into some_text (the_text) values (100);
Query OK, 1 row affected (0.02 sec)

This should have failed.

mysql> select * from some_text;
+----------+
| the_text |
+----------+
| 100      |
+----------+
1 row in set (0.04 sec)

mysql> select * from some_text where the_text = 100;
+----------+
| the_text |
+----------+
| 100      |
+----------+
1 row in set (0.00 sec)

This should have failed too.

Suggested fix:
Add a stricter mode because fixing sql_mode TRADITIONAL will probably break a lot of applications.

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.

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.