CODEWITHSUNDEEP
mysqlsqlequality
silkfire
silkfire

Reputation: 26033

Query returns "LIKE" results despite not having wildcards?

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.

enter image description here

Upvotes: 6

Views: 142

Answers (3)

Alma Do
Alma Do

Reputation: 37365

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)

Upvotes: 11

Alex Pliutau
Alex Pliutau

Reputation: 21947

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

You should quote 5 because MySQL convert string from the table to int without quotes.

Upvotes: 0

AdrianBR
AdrianBR

Reputation: 2588

SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = '5'
-- this will compare the string 'size' with the string '5'


SELECT * FROM `articles` WHERE `form` = 'Depotplåster' AND `size` = 5
-- this will convert string 'size' to integer and then compare with the integer 5

The conversion of string to integer looks for ints i nthe beginning of the string, and takes the largest integer until the first non-numeric character.

select '5s4'=5, 's5'=5, '5'=5 -- =>1,0,1

Upvotes: 1

Related Questions