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

    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

        1. MDEV-21445.diff
          64 kB
          Alexander Barkov
        2. MDEV-21445-REVIEW.txt
          3 kB
          Alexander Barkov

        Issue Links

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.