Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.8, 10.3.9, 10.3(EOL)
-
None
-
CentOS 7 3.10.0-862.9.1.el7.x86_64
Description
CREATE TABLE IF NOT EXISTS `test` ( |
`n1` int(10) NOT NULL, |
`n2` int(10) NOT NULL, |
`c1` char(1) NOT NULL, |
KEY `c1` (`c1`) USING BTREE, |
KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
|
INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES |
(0, 2, 'a'), |
(1, 3, 'a'); |
|
SELECT test.n1 |
FROM test |
INNER JOIN ( |
SELECT n1, n2 |
FROM test |
WHERE |
c1 = 'a' |
GROUP BY n1 |
) t2
|
ON (t2.n1 = test.n1 AND t2.n2 = test.n2) |
WHERE c1 = 'a' |
GROUP BY n1 |
Running the above query, as setup above, gives the incorrect result of:
1
Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1
The combination of that specific multi-column index with the single-column index of c1 seems to trigger the issue.