Query returns rows where no result is expected (impossible WHERE) in maria-5.3-mwl89

[Philip Stoev]

The following query:

SELECT table1 .`col_varchar_nokey`
FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
WHERE ( 's' , 'm' ) IN (
SELECT `col_varchar_nokey` , `col_varchar_key`
FROM C ) ;

returns rows even though there is no value 's' in table C

Test case:

SET LOCAL optimizer_switch='semijoin=off,materialization=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ A;
DROP TABLE /*! IF EXISTS */ C;
--enable_warnings

CREATE TABLE `A` (
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (NULL,2,'w','w');
INSERT INTO `C` VALUES (7,9,'m','m');
INSERT INTO `C` VALUES (9,3,'m','m');
INSERT INTO `C` VALUES (7,9,'k','k');
INSERT INTO `C` VALUES (4,NULL,'r','r');
INSERT INTO `C` VALUES (2,9,'t','t');
INSERT INTO `C` VALUES (6,3,'j','j');
INSERT INTO `C` VALUES (8,8,'u','u');
INSERT INTO `C` VALUES (NULL,8,'h','h');
INSERT INTO `C` VALUES (5,53,'o','o');
INSERT INTO `C` VALUES (NULL,0,NULL,NULL);
INSERT INTO `C` VALUES (6,5,'k','k');
INSERT INTO `C` VALUES (188,166,'e','e');
INSERT INTO `C` VALUES (2,3,'n','n');
INSERT INTO `C` VALUES (1,0,'t','t');
INSERT INTO `C` VALUES (1,1,'c','c');
INSERT INTO `C` VALUES (0,9,'m','m');
INSERT INTO `C` VALUES (9,5,'y','y');
INSERT INTO `C` VALUES (NULL,6,'f','f');
INSERT INTO `C` VALUES (4,2,'d','d');

SELECT table1 .`col_varchar_nokey`
FROM A table1 RIGHT JOIN C ON table1 .`col_int_nokey`
WHERE ( 's' , 'm' ) IN (
SELECT `col_varchar_nokey` , `col_varchar_key`
FROM C ) ;

[Philip Stoev (pstoev-askmonty)]

maria-5.3, as well as running the same query within a view produce the correct result - empty result set.

[Philip Stoev (pstoev-askmonty)]

The result with LEFT JOIN is also incorrect. Here is another example

SET SESSION optimizer_switch='materialization=off,subquery_cache=off,semijoin=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_datetime_key` (`col_datetime_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,8,'2002-02-26 06:14:37','v');
INSERT INTO `CC` VALUES (11,9,'1900-01-01 00:00:00','r');
INSERT INTO `CC` VALUES (12,9,'2006-12-03 09:37:26','a');
INSERT INTO `CC` VALUES (13,186,'2008-05-26 12:27:10','m');
INSERT INTO `CC` VALUES (14,NULL,'2004-12-14 16:37:30','y');
INSERT INTO `CC` VALUES (15,2,'2003-02-11 21:19:41','j');
INSERT INTO `CC` VALUES (16,3,'2009-10-18 02:27:49','d');
INSERT INTO `CC` VALUES (17,0,'2000-09-26 07:45:57','z');
INSERT INTO `CC` VALUES (18,133,NULL,'e');
INSERT INTO `CC` VALUES (19,1,'2005-11-10 12:40:29','h');
INSERT INTO `CC` VALUES (20,8,'2009-04-25 00:00:00','b');
INSERT INTO `CC` VALUES (21,5,'2002-11-27 00:00:00','s');
INSERT INTO `CC` VALUES (22,5,'2004-01-26 20:32:32','e');
INSERT INTO `CC` VALUES (23,8,'2007-10-26 11:41:40','j');
INSERT INTO `CC` VALUES (24,6,'2005-10-07 00:00:00','e');
INSERT INTO `CC` VALUES (25,51,'2000-07-15 05:00:34','f');
INSERT INTO `CC` VALUES (26,4,'2000-04-03 16:33:32','v');
INSERT INTO `CC` VALUES (27,7,NULL,'x');
INSERT INTO `CC` VALUES (28,6,'2001-04-25 01:26:12','m');
INSERT INTO `CC` VALUES (29,4,'2000-12-27 00:00:00','c');
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_datetime_key` datetime DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_datetime_key` (`col_datetime_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,'1900-01-01 00:00:00',NULL);

SELECT table2 .`col_datetime_key`
FROM BB table1 RIGHT JOIN CC table2 ON table2 .`col_int_key` = table1 .`pk`
WHERE ( 'm' , 'j' ) IN (
SELECT 'k' , 'h' )
ORDER BY table1 .`col_varchar_key` ;

[Timour Katchaounov (timour)]

Only the first test case is reproducible, with both materialization and
in-to-exists. The OUTER JOIN matters.

[Timour Katchaounov (timour)]

Simplified test case below. Notice the rewriting the
query with a LEFT join doesn't reproduce the bug.

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (
  c1 int(11) DEFAULT NULL,
  c2 varchar(1) DEFAULT NULL
);
CREATE TABLE t2 (
  c1 int(11) DEFAULT NULL,
  c2 varchar(1) DEFAULT NULL
);
INSERT INTO t2 VALUES (7,'k');
INSERT INTO t2 VALUES (4,'d');

SET @@optimizer_switch='in_to_exists=on,materialization=off,subquery_cache=off,semijoin=off';

SELECT t1.c2
FROM t1 RIGHT JOIN t2 ON t1.c1
WHERE 's' IN (SELECT c2 FROM t2);

[Timour Katchaounov (timour)]

Even simpler test case:

DROP TABLE IF EXISTS t1, t2;

CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('k');
INSERT INTO t2 VALUES ('d');

SET @@optimizer_switch='in_to_exists=on,materialization=off,subquery_cache=off,semijoin=off';

SELECT t1.c1
FROM t1 RIGHT JOIN t2 ON t1.c1
WHERE 's' IN (SELECT c1 FROM t2);