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: Confirmed
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1, 10.0
        • Fix Version/s: 10.1, 10.0
        • Component/s: Optimizer
        • Labels:
          None

          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.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: