MySQL: integer to string type comparison in WHERE clause

Question

I have a question about MySQL comparing different data types. Let's say I have a following table:

CREATE TABLE `test`(  
  `value` VARCHAR(100)
);

Then I insert some data:

INSERT INTO test VALUES ('2XV4F2J');
INSERT INTO test VALUES ('123456');

Now if I run a simple select query:

SELECT * FROM test WHERE `value` = 2

I am aware of comparison of different types so in this case everything should be compared as 'floats' according to documentation. So as expected I get the result:

2XV4F2J

However when I try to run the update query

UPDATE test SET `value` = 'some other value' WHERE `value` = 2

I get the following error message:

Error Code: 1292
Truncated incorrect DOUBLE value: '2XV4F2J'

The thing I do not understand is why I am getting the error only when running UPDATE query. I would've expected to get the same error during SELECT as well.

I tries your case in a server installed 5.1, it works in both select and update statements. what's your env settings? — hago
– hago, Commented Jul 26, 2013 at 7:14

fancyPants · Accepted Answer · 2013-07-26 09:05:37Z

This particular behavior of the database is handled by the so called "SQL Mode", most likely your MySQL Session has "STRICT_TRANS_TABLES" set, you can verify this by issuing: SELECT @@session.sql_mode;, please see an example output below:

mysql> SELECT @@session.sql_mode;
+--------------------------------------------+
| @@session.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

To explain every mode in detail would be a bit out of scope, but to make things short, in STRICT_TRANS_TABLES mode a DML statement will be rolled back. This is for the reason to ensure database integrity. This means you can´t destroy your data integrity with UPDATES/INSERTS, short to say: "In case of DML, warnings are treated as errors". In case of SELECT statements, there is not the danger of destroying your database integrity, therefore in this mode there is a warning if you do a SELECT, but the data is converted to a matching value. This means that it is up to the programmer to ensure validity of the retrieved data.

To come back to your above example, you can turn off the strict mode for your session by set sql_mode=''; after this your UPDATE will work yielding same warning as in the SELECT from your example.

Important: Read carefully and understand following part of the MySQL-Docs: Server SQL Modes before you even dare to mess arround with the SQL Mode of your server/session. This can have as result, that your database will be inconsistent unless you and your developers don´t understand in very deep detail what they are doing. As stated above, its completly up to you to guarantee data consistency. I would strongly advice that you let the DBMS do this job!

See below a test session using your example above:

mysql> CREATE TABLE `test`(  
    ->   `value` VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test VALUES ('2XV4F2J');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES ('123456');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test WHERE `value` = 2;
+---------+
| value   |
+---------+
| 2XV4F2J |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> UPDATE test SET `value` = 'some other value' WHERE `value` = 2;

ERROR 1292 (22007): Truncated incorrect DOUBLE value: '2XV4F2J'

Now disabling strict mode and do the update:

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test SET `value` = 'some other value' WHERE `value` = 2;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2XV4F2J' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------------------+
| value            |
+------------------+
| some other value |
| 123456           |
+------------------+
2 rows in set (0.00 sec)

Collectives™ on Stack Overflow

MySQL: integer to string type comparison in WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related