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

XOR returns bad results for an indexed column

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.0
    • Fix Version/s: 10.1.6
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
         k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
         c CHAR(120) DEFAULT '' NOT NULL,
         pad CHAR(60) DEFAULT '' NOT NULL,
         PRIMARY KEY (id)
      ) ENGINE=MyISAM;
      INSERT INTO t1 (k, c, pad) VALUES (10, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (11, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (12, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (13, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (14, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (15, 'a', 'xxx');
      INSERT INTO t1 (k, c, pad) VALUES (16, 'a', 'xxx');
      SELECT * FROM t1 WHERE id XOR 0;

      erroneously returns empty set.

      If I add IGNORE KEY:

      SELECT * FROM t1 IGNORE KEY(PRIMARY) WHERE id XOR 0;

      it correctly returns rows:

      +----+----+---+-----+
      | id | k  | c | pad |
      +----+----+---+-----+
      |  1 | 10 | a | xxx |
      |  2 | 11 | a | xxx |
      |  3 | 12 | a | xxx |
      |  4 | 13 | a | xxx |
      |  5 | 14 | a | xxx |
      |  6 | 15 | a | xxx |
      |  7 | 16 | a | xxx |
      +----+----+---+-----+
      7 rows in set (0.00 sec)

        Attachments

          Activity

            People

            Assignee:
            bar Alexander Barkov
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: