MySQL unexpectedly returning results for "WHERE integer_column = 'string_value'"

Question

I'm querying an INT column with a string, like this:

SELECT id
FROM (`navigation_links`)
WHERE `navigation_links`.`navigation_link_id` =  'anyRandomString'

^{(originally, this was by accident)}

Expected results: 0, because navigation_link_id is an INT
Actual results: It seems to return every row whose value is 0

This returns the exact inverse:

SELECT id
FROM (`navigation_links`)
WHERE `navigation_links`.`navigation_link_id` !=  'anyRandomString'

Here is the table, stripped down:

CREATE TABLE `navigation_links` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `navigation_link_id` INT(10) UNSIGNED NULL DEFAULT '0',
    `navigation_group_id` INT(10) UNSIGNED NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Any idea what this is all about?

outis · Accepted Answer · 2011-11-29 06:15:54Z

3

When comparing values of different types, MySQL will attempt to convert one of the values so comparison is possible. In the case of strings and numbers, MySQL will convert the string by interpreting any leading digits as a number. No leading digits corresponds to a numeric value of 0, which makes sense in a couple of ways:

leading zeros can often be dropped; '042' is the same as '42'. Thus '0' and '' are the same.
When converting a numeric string to a number, the initial value for the number is 0. The string is iterated over, adding the digits to the number and multiplying by the base:
```
n = 0
while str[i] is a digit character:
    n += str[i] as a digit
    ++i
return n
```
If there are no digit characters, the value is 0.
An empty string is nothing, so the numeric string at the beginning of a string that starts with no digits is nothing. Under this way of thinking, NULL could also be the value for a string with no leading digits interpreted as a number. This latter option was not picked by the MySQL developers, possibly to make implementation slightly simpler.

edited Nov 29, 2011 at 6:15

answered Nov 29, 2011 at 6:07

outis

77.7k23 gold badges155 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

No Results Found Over a year ago

I see, so it sort of boils down to 0 after each "digit" is checked then?. I noticed that where int = '3x' gets cast to 3, but where int = 'x3' gets cast to 0. I guess the conversion can make sense sometimes, but I'm surprised at this behavior.

outis Over a year ago

@Madmartigan: note that only leading digits get converted, rather than the first contiguous digit substring. This is the same behavior as the C functions atoi and strtol; MySQL might use one of these (a quick perusal of the source will tell you whether this is so). You can use the CAST and CONVERT functions to see what number MySQL will convert a string to.

Collectives™ on Stack Overflow

MySQL unexpectedly returning results for "WHERE integer_column = 'string_value'"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related