Select by varchar column with IN() part in condition and int value returns all rows

Question

Hello !

Can someone explain me why with that query:

SELECT * FROM `tags` WHERE (tag IN ('willa-lentza', 2016))

it return me all rows from tags table, but when I put 2016 into quotes it works good ?

tag column is varchar type.

SAMPLE ENVIRONMENT

CREATE TABLE  `tags` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `tag` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

INSERT INTO `tags` (`id`, `tag`) VALUES
  (1, '2016'),
  (2, 'plum'),
  (3, 'banana'),
  (4, 'apple'),
  (5, 'willa-lentza');

I also get the same error as Roland Bouman got:

Truncated incorrect DOUBLE value: 'willa-lentza'

Answer 1

My brief testing does not confirm this behavior.

SELECT 'test' IN ('other-string', 2016) /* returns 0 */

Not just any string should be able to match an integer. Are you sure you haven't changed anything else?

Answer 2

Interesting...i got this.

mysql> select 'a' in (0, 'b');
+-----------------+
| 'a' in (0, 'b') |
+-----------------+
|               1 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'a'

EDIT: actually i think I can exaplain this one. (because 0 is in the in list, mysql thinks the rest og the in list and also the left hand argument 'a' should all be numbers. Casting 'a' will result in 0, so this will intenally be seen as 0 in (0, 0)

but I still can't explain the behavior of the OP

Answer 3

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in

Answer 4

I cannot reproduce this behaviour, but it seems that your varchars get casted into a DOUBLEs and not the other way around.

In this case, the query turns into this:

SELECT  *
FROM    tags
WHERE   CAST(tag AS DOUBLE) /* =0 for non-numeric tags */ IN (CAST('willa-lentza AS DOUBLE) /* = 0 */, 2016)

which is always true for all non-numeric tags.

Could you please run EXPLAIN EXTENDED SELECT ... on this statement and post the warning here?

To confirm this behavior, you can add another numerical tag:

INSERT
INTO    tags
VALUES  (6, '1000')

This tag should be returned by neither of the queries.

To avoid this, just always enclose your constants into single quotes so that they are parsed as CHARs