2

I am experiencing some weird behavior with MySQL. Basically I have a table like this:

ID     string
1      14 
2      10,14,25

Why does this query pull id 2?

SELECT * FROM exampletable where string = 10

Surely it should be looking for an exact match, because this only pulls id 1:

SELECT * FROM exampletable where string = 14

I am aware of FIND_IN_SET, I just find it odd that the first query even pulls anything. Its behaving like this query:

SELECT * FROM exampletable where string LIKE '10%'
Salman A
  • 262,204
  • 82
  • 430
  • 521
Johan Mouritsen
  • 29
  • 3
  • 1
    comma-separated columns are pure evil. don't use them. – Joel Coehoorn Sep 10 '18 at 13:44
  • See also [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Sep 10 '18 at 17:44

1 Answers1

5

When you compare a numeric and a string value, MySQL will attempt to convert the string to number and match. Number like strings are also parsed. This we have:

SELECT '10,14,25'      =   1     -- 0
SELECT '10,14,25'      =  10     -- 1
SELECT 'FOOBAR'        =   1     -- 0
SELECT 'FOOBAR'        =   0     -- 1
SELECT '123.456'       = 123     -- 0
SELECT '123.456FOOBAR' = 123.456 -- 1

The behavior is documented here (in your example it is the last rule):

...

If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • It's worse even than this, because a conversion like this also breaks any index that might have existed on the `string` column, meaning the database has to attempt this conversion and compare on _every row in the table_. – Joel Coehoorn Sep 10 '18 at 13:46
  • @JoelCoehoorn as a programmer you must make sure that you are comparing apples to apples. – Salman A Sep 10 '18 at 13:49
  • This makes sense, thank you. it converted 10,14,25 into 10 to be able to compare it. The closest documentation i have found if anyone is interested is: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html – Johan Mouritsen Sep 10 '18 at 13:51