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

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1
    • Fix Version/s: 5.5.55
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      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

            • Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: