Bug #49600 Server incorrectly processing RIGHT JOIN with constant WHERE clause and no index
Submitted: 10 Dec 2009 18:33 Modified: 20 Dec 2010 13:20
Reporter: Patrick Crews Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0+ OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Tags: INDEX, RIGHT JOIN, where

[10 Dec 2009 18:33] Patrick Crews
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 */
[3 Dec 2010 17:10] Ole John Aske
This bug seems to have been fixed with available fix for bug#57034: http://lists.mysql.com/commits/125972

-> likely a duplicate
[20 Dec 2010 13:20] Ole John Aske
Closed as duplicate of bug#57034 which there as an available patch for.