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.
Bug
Major
