Strange/inconsistent behavior of IN condition when mixing numbers and strings

Hi,

today we encountered one strange behavior in an older project. Below are simple SQL statements that quickly show where the problem is.

When combining a number and a number as a string in an IN WHERE condition, it sometimes returns extra unwanted records.

As you can see, condition id = 1234 OR id = "97716021308405775" returns expected 2 records, but equivalent id IN(1234,"97716021308405775") returns 4 records. This unexpected behavior is independent on storage engine - it works the same with InnoDB, MYISAM or TokuDB.

We'll fix our ORM layer to use the IN condition correctly (with either numbers or strings only), but maybe it's a bug in MariaDB that should be fixed. Maybe it's just old, familiar, unexpected behavior that can't be corrected?

DROP TABLE IF EXISTS test;
CREATE TABLE `test` (
`id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT IGNORE INTO `test` VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780);

– OK
SELECT * FROM `test` WHERE `id` IN(97716021308405775);
SELECT * FROM `test` WHERE `id` IN("97716021308405775");
SELECT * FROM `test` WHERE `id` IN("1234",97716021308405775);
SELECT * FROM `test` WHERE `id` IN("1234","97716021308405775");
SELECT * FROM `test` WHERE `id` = 1234 OR id = "97716021308405775";

– NOT OK (returns 4 records instead of 2)
SELECT * FROM `test` WHERE `id` IN(1234,"97716021308405775");
SELECT * FROM `test` WHERE `id` IN("97716021308405775",1234);