Bug #48954 | Server is processing compound WHERE clause incorrectly - missing rows | ||
---|---|---|---|
Submitted: | 20 Nov 2009 21:25 | Modified: | 17 Feb 2010 9:42 |
Reporter: | Patrick Crews | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0+ | OS: | Any |
Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[20 Nov 2009 21:25]
Patrick Crews
[17 Feb 2010 9:43]
Sergei Glukhov
can not repeat the bug on latest 5.1-bugteam tree(see test result below): -- +/*!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 = 'disable' */; +/*!50400 SET SESSION engine_condition_pushdown = 'ON' */; +/*!50400 SET SESSION join_cache_level = 1 */; +/*!50400 SET SESSION debug = 'd,optimizer_no_icp' */; +DROP TABLE /*! IF EXISTS */ C; +CREATE TABLE `C` ( +`pk` int(11) NOT NULL AUTO_INCREMENT, +`int_key` int(11) DEFAULT NULL, +`varchar_key` varchar(1) DEFAULT NULL, +PRIMARY KEY (`pk`), +KEY `int_key` (`int_key`), +KEY `varchar_key` (`varchar_key`,`int_key`) +) AUTO_INCREMENT=21 DEFAULT CHARSET=latin1; +INSERT INTO `C` VALUES (11,0,NULL); +INSERT INTO `C` VALUES (16,1,'c'); +INSERT INTO `C` VALUES (20,2,'d'); +INSERT INTO `C` VALUES (13,166,'e'); +INSERT INTO `C` VALUES (19,6,'f'); +INSERT INTO `C` VALUES (9,8,'h'); +INSERT INTO `C` VALUES (7,3,'j'); +INSERT INTO `C` VALUES (12,5,'k'); +INSERT INTO `C` VALUES (4,9,'k'); +INSERT INTO `C` VALUES (3,3,'m'); +INSERT INTO `C` VALUES (2,9,'m'); +INSERT INTO `C` VALUES (17,9,'m'); +INSERT INTO `C` VALUES (14,3,'n'); +INSERT INTO `C` VALUES (10,53,'o'); +INSERT INTO `C` VALUES (5,NULL,'r'); +INSERT INTO `C` VALUES (15,0,'t'); +INSERT INTO `C` VALUES (6,9,'t'); +INSERT INTO `C` VALUES (8,8,'u'); +INSERT INTO `C` VALUES (1,2,'w'); +INSERT INTO `C` VALUES (18,5,'y'); +SELECT table2.pk, table1.int_key, table2.varchar_key from C table1 JOIN C table2 WHERE +table2.pk = table1.int_key; +pk int_key varchar_key +1 1 w +2 2 m +2 2 m +3 3 m +3 3 m +3 3 m +5 5 r +5 5 r +6 6 t +8 8 u +8 8 u +9 9 h +9 9 h +9 9 h +9 9 h +SELECT table1.int_key, table2 .`int_key`, table2.varchar_key +FROM C table1 JOIN C table2 ON table2 .`pk` = table1 .`int_key` +WHERE table1 .`varchar_key` IN ( 'i' , 't' ) OR table1 .`varchar_key` != 'x' ; +int_key int_key varchar_key +1 2 w +2 9 m +6 9 t +8 8 u +3 3 m +5 NULL r +9 8 h +3 3 m +9 8 h +9 8 h +3 3 m +9 8 h +8 8 u +2 9 m +5 NULL r -- Both results are correct.