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`)
INSERT IGNORE INTO `test` VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780);
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);
- is blocked by
MDEV-22976 CAST(JSON_EXTRACT() AS DECIMAL) does not handle boolean values
MDEV-29041 Redundant truncation warning on CAST(string_column AS DECIMAL)
- is duplicated by
MDEV-25492 BETWEEN clause returns incorrect results on quoted 64-bit ints
- relates to
MDEV-25871 compare error bigint to varchar
MDEV-29259 Comparison semantic of int = string changes with creation of an index