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

Wrong result (missing rows) with InnoDB, index_merge, AND and OR conditions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1
    • 5.5.55
    • Optimizer
    • None
    • 10.1.21

    Description

      Test case

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (
        id INT NOT NULL,
        state VARCHAR(64),
        capital VARCHAR(64),
        UNIQUE KEY (id),
        KEY (state),
        KEY (capital)
      ) ENGINE=InnoDB;
       
      INSERT INTO t1 VALUES  
        (1,'Arizona','Phoenix'), 
        (2,'Hawaii','Honolulu'),
        (3,'Georgia','Atlanta'), 
        (4,'Florida','Tallahassee'), 
        (5,'Alaska','Juneau'),
        (6,'Michigan','Lansing'),
        (7,'Pennsylvania','Harrisburg'),
        (8,'Virginia','Richmond')
      ;
       
      SELECT * FROM t1 FORCE KEY (state,capital) 
      WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id IS NOT NULL AND id != 9 
         OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'
      ;

      Actual result

      MariaDB [test]> SELECT * FROM t1 FORCE KEY (state,capital) 
          -> WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id IS NOT NULL AND id != 9 
          ->    OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'
          -> ;
      +----+----------+----------+
      | id | state    | capital  |
      +----+----------+----------+
      |  8 | Virginia | Richmond |
      +----+----------+----------+
      1 row in set (0.00 sec)

      Expected result

      +----+--------------+-------------+
      | id | state        | capital     |
      +----+--------------+-------------+
      |  4 | Florida      | Tallahassee |
      |  3 | Georgia      | Atlanta     |
      |  2 | Hawaii       | Honolulu    |
      |  6 | Michigan     | Lansing     |
      |  7 | Pennsylvania | Harrisburg  |
      |  8 | Virginia     | Richmond    |
      +----+--------------+-------------+
      6 rows in set (0.00 sec)

      Note: Apparently it started happening on 10.0 some time between 10.0.4 and 10.0.5; my search pointed at the merge 67e2e14627731082ea9c31392f34fc920aef86df, but it's not 100% certain.
      Could not reproduce (with this test case) on 5.5.

      Attachments

        Activity

          People

            igor Igor Babaev
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.