RQG: wrong result on JOIN + NOT IN and partial_match_table_scan = OFF

[Philip Stoev]

The following query:

SELECT table2 .`col_varchar_key`
FROM BB JOIN (
SELECT *
FROM C ) table2 ON table2 .`col_int_key`
WHERE ( 8 , 8 ) NOT IN (
SELECT SUBQUERY2_t1 .`col_int_key` , SUBQUERY2_t2 .`col_int_key`
FROM D SUBQUERY2_t1 JOIN C SUBQUERY2_t2 ON SUBQUERY2_t2 .`pk` ) ;

returns no rows under partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,semijoin=off .The EXPLAIN shows "Impossible WHERE noticed after reading const tables" which is not correct, since there are matching rows and they are properly returned under other switches and product versions.

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3 DEPENDENT SUBQUERY SUBQUERY2_t2 ref_or_null col_int_key col_int_key 5 3 Using index condition; Using where
3 DEPENDENT SUBQUERY SUBQUERY2_t1 ref_or_null col_int_key col_int_key 5 23 Using where; Using index
2 DERIVED C ALL NULL NULL NULL NULL 20

test case:

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,NULL,'r');
INSERT INTO `D` VALUES (2,0,'c');
INSERT INTO `D` VALUES (3,0,'o');
INSERT INTO `D` VALUES (4,7,'c');
INSERT INTO `D` VALUES (5,8,'d');
INSERT INTO `D` VALUES (6,4,'v');
INSERT INTO `D` VALUES (7,6,'m');
INSERT INTO `D` VALUES (8,5,'j');
INSERT INTO `D` VALUES (9,NULL,'f');
INSERT INTO `D` VALUES (10,NULL,'n');
INSERT INTO `D` VALUES (11,8,'z');
INSERT INTO `D` VALUES (12,8,'h');
INSERT INTO `D` VALUES (13,8,'q');
INSERT INTO `D` VALUES (14,1,'w');
INSERT INTO `D` VALUES (15,1,'z');
INSERT INTO `D` VALUES (16,5,'j');
INSERT INTO `D` VALUES (17,2,'a');
INSERT INTO `D` VALUES (18,7,'m');
INSERT INTO `D` VALUES (19,6,'n');
INSERT INTO `D` VALUES (20,4,'e');
INSERT INTO `D` VALUES (21,7,'u');
INSERT INTO `D` VALUES (22,0,'s');
INSERT INTO `D` VALUES (23,9,'u');
INSERT INTO `D` VALUES (24,3,'r');
INSERT INTO `D` VALUES (25,5,'g');
INSERT INTO `D` VALUES (26,1,'o');
INSERT INTO `D` VALUES (27,1,'w');
INSERT INTO `D` VALUES (28,5,'b');
INSERT INTO `D` VALUES (29,9,NULL);
INSERT INTO `D` VALUES (30,2,'y');
INSERT INTO `D` VALUES (31,5,'y');
INSERT INTO `D` VALUES (32,248,'u');
INSERT INTO `D` VALUES (33,0,'p');
INSERT INTO `D` VALUES (34,8,'s');
INSERT INTO `D` VALUES (35,1,'e');
INSERT INTO `D` VALUES (36,255,'d');
INSERT INTO `D` VALUES (37,9,'d');
INSERT INTO `D` VALUES (38,9,'c');
INSERT INTO `D` VALUES (39,3,'b');
INSERT INTO `D` VALUES (40,9,'t');
INSERT INTO `D` VALUES (41,6,NULL);
INSERT INTO `D` VALUES (42,4,'y');
INSERT INTO `D` VALUES (43,60,'c');
INSERT INTO `D` VALUES (44,7,'d');
INSERT INTO `D` VALUES (45,1,'x');
INSERT INTO `D` VALUES (46,6,'p');
INSERT INTO `D` VALUES (47,4,'e');
INSERT INTO `D` VALUES (48,NULL,'g');
INSERT INTO `D` VALUES (49,8,'x');
INSERT INTO `D` VALUES (50,0,'s');
INSERT INTO `D` VALUES (51,8,'e');
INSERT INTO `D` VALUES (52,151,'l');
INSERT INTO `D` VALUES (53,7,'p');
INSERT INTO `D` VALUES (54,6,'h');
INSERT INTO `D` VALUES (55,NULL,'m');
INSERT INTO `D` VALUES (56,23,'n');
INSERT INTO `D` VALUES (57,2,'v');
INSERT INTO `D` VALUES (58,4,'b');
INSERT INTO `D` VALUES (59,NULL,'x');
INSERT INTO `D` VALUES (60,NULL,'r');
INSERT INTO `D` VALUES (61,77,'t');
INSERT INTO `D` VALUES (62,NULL,'w');
INSERT INTO `D` VALUES (63,NULL,'w');
INSERT INTO `D` VALUES (64,7,'k');
INSERT INTO `D` VALUES (65,1,'a');
INSERT INTO `D` VALUES (66,9,'t');
INSERT INTO `D` VALUES (67,6,'z');
INSERT INTO `D` VALUES (68,2,'e');
INSERT INTO `D` VALUES (69,3,'q');
INSERT INTO `D` VALUES (70,0,'e');
INSERT INTO `D` VALUES (71,NULL,'v');
INSERT INTO `D` VALUES (72,6,'d');
INSERT INTO `D` VALUES (73,3,'u');
INSERT INTO `D` VALUES (74,195,'o');
INSERT INTO `D` VALUES (75,5,'b');
INSERT INTO `D` VALUES (76,2,'c');
INSERT INTO `D` VALUES (77,7,'q');
INSERT INTO `D` VALUES (78,25,NULL);
INSERT INTO `D` VALUES (79,NULL,'h');
INSERT INTO `D` VALUES (80,0,'d');
INSERT INTO `D` VALUES (81,98,'w');
INSERT INTO `D` VALUES (82,6,'m');
INSERT INTO `D` VALUES (83,5,'i');
INSERT INTO `D` VALUES (84,0,'w');
INSERT INTO `D` VALUES (85,3,'f');
INSERT INTO `D` VALUES (86,1,'k');
INSERT INTO `D` VALUES (87,1,'v');
INSERT INTO `D` VALUES (88,147,'c');
INSERT INTO `D` VALUES (89,3,'y');
INSERT INTO `D` VALUES (90,3,'h');
INSERT INTO `D` VALUES (91,NULL,NULL);
INSERT INTO `D` VALUES (92,2,'t');
INSERT INTO `D` VALUES (93,1,'l');
INSERT INTO `D` VALUES (94,8,'a');
INSERT INTO `D` VALUES (95,8,'r');
INSERT INTO `D` VALUES (96,8,'s');
INSERT INTO `D` VALUES (97,0,'z');
INSERT INTO `D` VALUES (98,1,'j');
INSERT INTO `D` VALUES (99,8,'c');
INSERT INTO `D` VALUES (100,5,'f');

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;

INSERT INTO `C` VALUES (1,2,'w');
INSERT INTO `C` VALUES (2,9,'m');
INSERT INTO `C` VALUES (3,3,'m');
INSERT INTO `C` VALUES (4,9,'k');
INSERT INTO `C` VALUES (5,NULL,'r');
INSERT INTO `C` VALUES (6,9,'t');
INSERT INTO `C` VALUES (7,3,'j');
INSERT INTO `C` VALUES (8,8,'u');
INSERT INTO `C` VALUES (9,8,'h');
INSERT INTO `C` VALUES (10,53,'o');
INSERT INTO `C` VALUES (11,0,NULL);
INSERT INTO `C` VALUES (12,5,'k');
INSERT INTO `C` VALUES (13,166,'e');
INSERT INTO `C` VALUES (14,3,'n');
INSERT INTO `C` VALUES (15,0,'t');
INSERT INTO `C` VALUES (16,1,'c');
INSERT INTO `C` VALUES (17,9,'m');
INSERT INTO `C` VALUES (18,5,'y');
INSERT INTO `C` VALUES (19,6,'f');
INSERT INTO `C` VALUES (20,2,'d');

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

INSERT INTO `BB` VALUES (10,8,NULL);

SET @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,semijoin=off';

SELECT table2 .`col_varchar_key`
FROM BB JOIN (
SELECT *
FROM C ) table2 ON table2 .`col_int_key`
WHERE ( 8 , 8 ) NOT IN (
SELECT SUBQUERY2_t1 .`col_int_key` , SUBQUERY2_t2 .`col_int_key`
FROM D SUBQUERY2_t1 JOIN C SUBQUERY2_t2 ON SUBQUERY2_t2 .`pk` ) ;

[Timour Katchaounov (timour)]

The bug is present also in MariaDB 5.2, as well as the current 5.3 where the relevant
logic is the same.

The bug cannot be reproduced in 5.3-mwl89.

[Timour Katchaounov (timour)]

In the end it turns out the correct result is an empty set, so its
not clear what was the bug about. We get empty set in all MariaDB
versions. Thus closing the bug as Invalid.