CODEWITHSUNDEEP
mysqlsql
Roel Veldhuizen
Roel Veldhuizen

Reputation: 4723

Wrong query with the right result

I got a query which lookslike the one bellow. The query should query for records in example where the value equals 3. The comparisson is value = '3 value' where I would expect value = 3.

Why results value = '3 value' in the same records returned as value = 3?

SELECT 
    *
FROM
    test.example
WHERE
    value = '3 value';

value is of the type INT and i would expect to get an error or at least the wrong result. Though, i get the right result(depending on how you define 'right') (the result where the value is 3).

Data in the table (id, name, value):

1   Example 1   1
2   Example 2   2
3   Example 3   3
4   Example 4   3
5   Example 5   2

Upvotes: 1

Views: 73

Answers (2)

Ryan
Ryan

Reputation: 3582

If you checked the warnings outputted by the query, you would see something as follows:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3 value' |
+---------+------+---------------------------------------------+

MySQL will truncate the value leaving just the integer.

Upvotes: 0

Luca Rainone
Luca Rainone

Reputation: 16458

From doc

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

then 

select "3 value" = 3

it returns 1 because

select CAST("3 value" AS SIGNED)

returns 3

Upvotes: 5

Related Questions