Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
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.