Query returns "LIKE" results despite not having wildcards?

Question

I'm really confused here. Running the following query:

SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5

returns rows that also start with "5", despite me neither using LIKE nor a % wildcard operator. How come?

The size field is of type VARCHAR.

Answer 1

That is because you're using comparison between numeric and varchar data. MySQL will implicitly convert your column to double, resulting in 5. See this simple test data:

mysql> select * from test;
+-----------------+
| name            |
+-----------------+
| 5               |
| 5 and some crap |
+-----------------+
2 rows in set (0.00 sec)

Now, "good" way: compare strings:

mysql> select * from test where name = '5';
+------+
| name |
+------+
| 5    |
+------+
1 row in set (0.00 sec)

And "bad" way: compare integers:

mysql> select * from test where name = 5;
+-----------------+
| name            |
+-----------------+
| 5               |
| 5 and some crap |
+-----------------+
2 rows in set, 1 warning (0.05 sec)

-and here is your reason:

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '5 and some crap' |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

Finally, to understand, why is it so:

SELECT 
  CAST('5' AS DECIMAL) AS 5d, 
  CAST('5 and some crap' AS DECIMAL) AS 5sd, 
  CAST('5' AS DECIMAL) = CAST('5 and some crap' AS DECIMAL) AS areEqual;

Will result in:

+----+-----+----------+
| 5d | 5sd | areEqual |
+----+-----+----------+
|  5 |   5 |        1 |
+----+-----+----------+
1 row in set (0.00 sec)

-as you can see, non-significant part was just truncated (as mentioned in warning message above)