Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21445

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.2.16, 10.3.21
    • Fix Version/s: 10.1, 10.2, 10.3, 10.4
    • Component/s: Data types
    • Labels:
    • Environment:
      Tested on Debian with 10.3.21 and Gentoo with 10.2.16.

      Description

      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);

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            jan.reges Ján Regeš
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: