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

XOR returns bad results for an indexed column

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1
    • 10.1.6
    • Optimizer
    • 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

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.