Description:
When executing a query of the form:
SELECT ...
FROM BB table1
RIGHT JOIN C table3
JOIN C table4 ON table3 .`int_nokey`
ON table4 .`int_nokey`;
adding a STRAIGHT_JOIN in order to force a particular query plan causes a different result to be returned. Since all mysql versions are affected, at this time it is not known if the result with or without STRAIGHT_JOIN is the correct one.
How to repeat:
--disable_warnings
DROP TABLE IF EXISTS C, BB;
--enable_warnings
CREATE TABLE `C` (
`int_nokey` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (5),(3),(0),(3),(1),(0),(1),(7),(1),(0),(0),(8),(4),(9),(0),(2),(0),(8),(5),(1);
CREATE TABLE `BB` (
`int_nokey` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (9),(0);
SELECT STRAIGHT_JOIN COUNT(*)
FROM BB table1
RIGHT JOIN C table3
JOIN C table4 ON table3 .`int_nokey`
ON table4 .`int_nokey`;
Suggested fix:
Fixing this is needed in both 5.1 and 5.4 because query plan modification via STRAIGHT_JOIN plays an important role in automatic testing -- basically we generate a query, then mess up with its query plan, and check if we still get the original result set.
