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

Question

Can someone explain to me why with this request:

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

it returns me all the rows from the table tags, but when I put 2016in quotation marks does it work well?

Column

taghas type varchar.

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 Buman:

Truncated incorrect DOUBLE value: 'willa-lentza'

Answer 1

You should never mix quoted and unquoted values ​​in the IN list because the comparison rules for quoted values ​​(like strings) and incorrect values ​​(like numbers) are different. Types of mixing can lead to inconsistent results.

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

Answer 2

... .

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: , . ( 0 , mysql , , "a" . "a" 0, 0 in (0, 0)

OP

Answer 3

.

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

. , ?

Answer 4

, , varchars DOUBLEs, .

:

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 run EXPLAIN EXTENDED SELECT ...in this statement and post a warning here?

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

INSERT
INTO    tags
VALUES  (6, '1000')

This tag must be returned by none of the queries.

To avoid this, simply enclose the constants in single quotation marks so that they are parsed as CHARs