Description:
The server (versions 5.0+) is incorrectly processing a RIGHT JOIN with a WHERE clause and no indexes on the table.
The query is properly processed:
* Without the WHERE clause
* With the WHERE clause if an index is present
Variations on the query from the attached test case:
# Tables with indexes here
SELECT table1 .`col_datetime_key`, table2.col_varchar_nokey, table1.col_varchar_key
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key`;
col_datetime_key col_varchar_nokey col_varchar_key
NULL NULL NULL
# The WHERE clause has no effect on the query here
SELECT table1 .`col_datetime_key`, table2.col_varchar_nokey, table1.col_varchar_key
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key` WHERE 7;
col_datetime_key col_varchar_nokey col_varchar_key
NULL NULL NULL
# We drop the index
ALTER TABLE BB DISABLE KEYS;
# Same query 1 as above, this is correct without the WHERE clause
SELECT table1 .`col_datetime_key`, table2.col_varchar_nokey, table1.col_varchar_key
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key`;
col_datetime_key col_varchar_nokey col_varchar_key
NULL NULL NULL
# With the WHERE clause, we see we get bad results
SELECT table1 .`col_datetime_key`, table2.col_varchar_nokey, table1.col_varchar_key
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key` WHERE 7;
col_datetime_key col_varchar_nokey col_varchar_key
1900-01-01 00:00:00 NULL NULL
How to repeat:
MTR test case for 6.0.
Comment out the SET statements as necessary for use with older versions of the server
#/* Server0: MySQL 6.0.14-alpha-gcov-debug-log */
/*!50400 SET SESSION optimizer_switch = 'firstmatch=off,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=off,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;
/*!50400 SET SESSION debug = 'd,optimizer_no_icp,optimizer_innodb_ds_mrr' */;
#/* Begin test case for query 0 */
--disable_warnings
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings
CREATE TABLE `BB` (
`col_datetime_key` datetime DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_datetime_key` (`col_datetime_key`),
KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL);
SELECT table1 .`col_datetime_key`
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key`
WHERE 7 ;
#/* End of test case for query 0 */
#/* Begin test case for query 1 */
ALTER TABLE BB DISABLE KEYS;
SELECT table1 .`col_datetime_key`
FROM BB table1 RIGHT JOIN BB table2 ON table2 .`col_varchar_nokey` = table1 .`col_varchar_key` WHERE 7;
ALTER TABLE BB ENABLE KEYS;
DROP TABLE BB;
#/* End of test case for query 1 */