RQG: Query returns extra rows when executed with materialization=on

[Philip Stoev]

The following query returns extra rows when executed in maria 5.3 with semijoin=off. If the query is executed with semijoin=on, or if it is executed on 5.2 or with the subquery manually inlined, no rows are returned.

SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` )
ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
WHERE table1 .`col_varchar_nokey` IN (
SELECT `col_varchar_nokey`
FROM C ) ;

The EXPLAIN plan does not specify what optimization strategy was chosen instead of semijoin.

[Philip Stoev (pstoev-askmonty)]

Test case:

SET SESSION optimizer_switch = 'semijoin=off';
CREATE TABLE `CC` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date 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_date_key` (`col_date_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,NULL,'v','v');
INSERT INTO `CC` VALUES (9,'2006-06-14','r','r');
INSERT INTO `CC` VALUES (9,'2002-09-12','a','a');
INSERT INTO `CC` VALUES (186,'2005-02-15','m','m');
INSERT INTO `CC` VALUES (NULL,NULL,'y','y');
INSERT INTO `CC` VALUES (2,'2008-11-04','j','j');
INSERT INTO `CC` VALUES (3,'2004-09-04','d','d');
INSERT INTO `CC` VALUES (0,'2006-06-05','z','z');
INSERT INTO `CC` VALUES (133,'1900-01-01','e','e');
INSERT INTO `CC` VALUES (1,'1900-01-01','h','h');
INSERT INTO `CC` VALUES (8,'1900-01-01','b','b');
INSERT INTO `CC` VALUES (5,'2005-01-13','s','s');
INSERT INTO `CC` VALUES (5,'2006-05-21','e','e');
INSERT INTO `CC` VALUES (8,'2003-09-08','j','j');
INSERT INTO `CC` VALUES (6,'2006-12-23','e','e');
INSERT INTO `CC` VALUES (51,'2006-10-15','f','f');
INSERT INTO `CC` VALUES (4,'2005-04-06','v','v');
INSERT INTO `CC` VALUES (7,'2008-04-07','x','x');
INSERT INTO `CC` VALUES (6,'2006-10-10','m','m');
INSERT INTO `CC` VALUES (4,'1900-01-01','c','c');
CREATE TABLE `C` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date 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_date_key` (`col_date_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,NULL,'w','w');
INSERT INTO `C` VALUES (9,'2001-09-19','m','m');
INSERT INTO `C` VALUES (3,'2004-09-12','m','m');
INSERT INTO `C` VALUES (9,NULL,'k','k');
INSERT INTO `C` VALUES (NULL,'2002-07-19','r','r');
INSERT INTO `C` VALUES (9,'2002-12-16','t','t');
INSERT INTO `C` VALUES (3,'2006-02-08','j','j');
INSERT INTO `C` VALUES (8,'2006-08-28','u','u');
INSERT INTO `C` VALUES (8,'2001-04-14','h','h');
INSERT INTO `C` VALUES (53,'2000-01-05','o','o');
INSERT INTO `C` VALUES (0,'2003-12-06',NULL,NULL);
INSERT INTO `C` VALUES (5,'1900-01-01','k','k');
INSERT INTO `C` VALUES (166,'2002-11-27','e','e');
INSERT INTO `C` VALUES (3,NULL,'n','n');
INSERT INTO `C` VALUES (0,'2003-05-27','t','t');
INSERT INTO `C` VALUES (1,'2005-05-03','c','c');
INSERT INTO `C` VALUES (9,'2001-04-18','m','m');
INSERT INTO `C` VALUES (5,'2005-12-27','y','y');
INSERT INTO `C` VALUES (6,'2004-08-20','f','f');
INSERT INTO `C` VALUES (2,'1900-01-01','d','d');
CREATE TABLE `BB` (
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date 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_date_key` (`col_date_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,'2002-02-21',NULL,NULL);

SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( ...

[Philip Stoev (pstoev-askmonty)]

Here is another similar situation. Note that the second execution of the prepared statement returns no rows, whereas the first execution of the prepared statement and the stand-alone execution of the statement return rows:

SET SESSION optimizer_switch = 'semijoin=off';

--disable_warnings
DROP TABLE /*! IF EXISTS */ D;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ BB;
DROP TABLE /*! IF EXISTS */ B;
--enable_warnings

CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_time_key` time DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  `col_varchar_nokey` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_time_key` (`col_time_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,NULL,'r','r');
INSERT INTO `D` VALUES (2,0,'14:34:45','c','c');
INSERT INTO `D` VALUES (3,0,'11:49:48','o','o');
INSERT INTO `D` VALUES (4,7,'18:12:55','c','c');
INSERT INTO `D` VALUES (5,8,'18:30:05','d','d');
INSERT INTO `D` VALUES (6,4,'14:19:30','v','v');
INSERT INTO `D` VALUES (7,6,'05:20:04','m','m');
INSERT INTO `D` VALUES (8,5,'20:29:31','j','j');
INSERT INTO `D` VALUES (9,NULL,'07:08:09','f','f');
INSERT INTO `D` VALUES (10,NULL,'14:49:14','n','n');
INSERT INTO `D` VALUES (11,8,'00:00:00','z','z');
INSERT INTO `D` VALUES (12,8,'09:58:06','h','h');
INSERT INTO `D` VALUES (13,8,NULL,'q','q');
INSERT INTO `D` VALUES (14,1,'18:24:16','w','w');
INSERT INTO `D` VALUES (15,1,'17:39:57','z','z');
INSERT INTO `D` VALUES (16,5,'08:23:21','j','j');
INSERT INTO `D` VALUES (17,2,NULL,'a','a');
INSERT INTO `D` VALUES (18,7,'21:50:46','m','m');
INSERT INTO `D` VALUES (19,6,'12:33:17','n','n');
INSERT INTO `D` VALUES (20,4,'03:06:43','e','e');
INSERT INTO `D` VALUES (21,7,'03:46:14','u','u');
INSERT INTO `D` VALUES (22,0,'20:34:52','s','s');
INSERT INTO `D` VALUES (23,9,NULL,'u','u');
INSERT INTO `D` VALUES (24,3,'10:41:20','r','r');
INSERT INTO `D` VALUES (25,5,'08:43:11','g','g');
INSERT INTO `D` VALUES (26,1,NULL,'o','o');
INSERT INTO `D` VALUES (27,1,'10:17:51','w','w');
INSERT INTO `D` VALUES (28,5,'06:34:09','b','b');
INSERT INTO `D` VALUES (29,9,'21:22:47',NULL,NULL);
INSERT INTO `D` VALUES (30,2,'04:02:32','y','y');
INSERT INTO `D` VALUES (31,5,'02:33:14','y','y');
INSERT INTO `D` VALUES (32,248,'16:32:56','u','u');
INSERT INTO `D` VALUES (33,0,'21:32:42','p','p');
INSERT INTO `D` VALUES (34,8,'23:04:47','s','s');
INSERT INTO `D` VALUES (35,1,'22:05:43','e','e');
INSERT INTO `D` VALUES (36,255,'02:05:45','d','d');
INSERT INTO `D` VALUES (37,9,'00:00:00','d','d');
INSERT INTO `D` VALUES (38,9,'18:09:07','c','c');
INSERT INTO `D` VALUES (39,3,'10:54:06','b','b');
INSERT INTO `D` VALUES (40,9,'23:15:50','t','t');
INSERT INTO `D` VALUES (41,6,'10:17:40',NULL,NULL);
INSERT INTO `D` VALUES (42,4,'03:37:09','y','y');
INSERT INTO `D` VALUES (43,60,'22:26:06','c','c');
INSERT INTO `D` VALUES (44,7,'17:10:38','d','d');
INSERT INTO `D` VALUES (45,1,'00:00:00','x','x');
INSERT INTO `D` VALUES (46,6,'17:08:49','p','p');
INSERT INTO `D` VALUES (47,4,'19:04:40','e','e...

[Philip Stoev (pstoev-askmonty)]

The test case from the first comment is still repeatable in maria-5.3 with materialization=on . It is not repeatable in maria-5.3-mwl89, however the plan there is different.

Test case:

CREATE TABLE t1 ( f4 varchar(1)) ENGINE=MyISAM;
INSERT IGNORE INTO t1 VALUES (NULL);

CREATE TABLE t2 ( f2 date, f3 varchar(1), f4 varchar(1)) ;
INSERT IGNORE INTO t2 VALUES ('2005-05-03','c','c'),('1900-01-01','d','d');

CREATE TABLE t3 ( f3 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('c');

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

SELECT t1.f4
FROM t1 JOIN ( t2 JOIN t3 ON t3.f3 = t2.f4 ) ON t3.f3 = t2.f3
WHERE t1.f4 IN ( SELECT f4 FROM t2 ) ;

Explain in maria-5.3:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2

Explain in maria-5.3-mwl89:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t3 system NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where

[Timour Katchaounov (timour)]

This appears to be a bug in 5.3 materialization.
In 5.3-mwl89 all possible subquery execution plans produce the same empty result.

[Timour Katchaounov (timour)]

The query plan in 5.3-mwl89 equivalent to the 5.3 plan above is:
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+

[Timour Katchaounov (timour)]

Test case pushed to 5.3. The bug itself was fixed by MWL#89.