RQG: Crash in optimize_semijoin_nests

[Philip Stoev]

Queries containing nested subselect in an ON clause crash maria 5.3 as follows. Maria 5.2 and mysql-next-mr do not appear to be affected.

bzr version-info:

revision-id: <email address hidden>
date: 2010-06-14 15:17:54 +0400
build-date: 2010-07-02 02:08:38 -0700
revno: 2789
branch-nick: maria

backtrace:

#5 0x083ab1ca in optimize_semijoin_nests (join=0xb3d06d8, all_table_map=7) at opt_subselect.cc:1197
#6 0x082fde5c in make_join_statistics (join=0xb3d06d8, tables_arg=0xb387918, conds=0xb3d6220, keyuse_array=0xb3d5410) at sql_select.cc:2954
#7 0x082f654b in JOIN::optimize (this=0xb3d06d8) at sql_select.cc:921
#8 0x082309ab in subselect_hash_sj_engine::exec (this=0xb39dcc8) at item_subselect.cc:3998
#9 0x08228a27 in Item_subselect::exec (this=0xb389680) at item_subselect.cc:461
#10 0x08228b4e in Item_in_subselect::exec (this=0xb389680) at item_subselect.cc:508
#11 0x0822a1ee in Item_in_subselect::val_bool (this=0xb389680) at item_subselect.cc:1116
#12 0x081c8acc in Item::val_bool_result (this=0xb389680) at item.h:772
#13 0x081f3bdb in Item_in_optimizer::val_int (this=0xb39bdb8) at item_cmpfunc.cc:1823
#14 0x081b4212 in Item::val_bool (this=0xb39bdb8) at item.cc:184
#15 0x081f01b1 in Item_func_not::val_int (this=0xb389790) at item_cmpfunc.cc:287
#16 0x081b4212 in Item::val_bool (this=0xb389790) at item.cc:184
#17 0x081fb869 in Item_cond_and::val_int (this=0xb39d558) at item_cmpfunc.cc:4498
#18 0x083158b5 in evaluate_join_record (join=0xb3bbc10, join_tab=0xb39ce70, error=0) at sql_select.cc:12896
#19 0x08315526 in sub_select (join=0xb3bbc10, join_tab=0xb39ce70, end_of_records=false) at sql_select.cc:12810
#20 0x08314954 in do_select (join=0xb3bbc10, fields=0xb3c09ac, table=0x0, procedure=0x0) at sql_select.cc:12360
#21 0x082fb25f in JOIN::exec (this=0xb3bbc10) at sql_select.cc:2183
#22 0x082fb97f in mysql_select (thd=0xb1671a0, rref_pointer_array=0xb168b9c, tables=0xb2c4ac0, wild_num=0, fields=..., conds=0xb38bd30, og_num=1,
    order=0xb38bed8, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb39a7e0, unit=0xb168810, select_lex=0xb168a98)
    at sql_select.cc:2378
#23 0x082f4803 in handle_select (thd=0xb1671a0, lex=0xb1687b4, result=0xb39a7e0, setup_tables_done_option=0) at sql_select.cc:276
#24 0x0829466d in execute_sqlcom_select (thd=0xb1671a0, all_tables=0xb2c4ac0) at sql_parse.cc:5133
#25 0x0828b069 in mysql_execute_command (thd=0xb1671a0) at sql_parse.cc:2318
#26 0x08296857 in mysql_parse (thd=0xb1671a0,
    inBuf=0xb2c3c18 "SELECT MIN( table2 . `col_varchar_key` ) AS field1\nFROM ( C AS table1 STRAIGHT_JOIN (\nSELECT SUBQUERY1_t1 . *\nFROM C AS SUBQUERY1_t1\nWHERE SUBQUERY1_t1 . `col_varchar_key` <= SUBQUERY1_t1 . `col"..., length=1305, found_semicolon=0xaeeb4228) at sql_parse.cc:6079
#27 0x08288b11 in dispatch_command (command=COM_QUERY, thd=0xb1671a0, packet=0xb272571 "", packet_length=1306) at sql_parse.cc:1253
#28 0x08287d4f in do_command (thd=0xb1671a0) at sql_parse.cc:891
#29 0x08284e96 in handle_one_connection (arg=0xb1671a0) at sql_connect.cc:1599
#30 0x00a08919 in start_thread () from /lib/libpthread.so.0
#31 0x00951e5e in clone () from /lib/libc.so.6

(gdb) list
1192 */
1193 {
1194 for (uint i=0 ; i < join->const_tables + sjm->tables ; i++)
1195 {
1196 JOIN_TAB *tab= join->best_positions[i].table;
1197 join->map2table[tab->table->tablenr]= tab;
1198 }
1199 List_iterator<Item> it(right_expr_list);
1200 Item *item;
1201 table_map map= 0;

(gdb) print tab
$1 = (JOIN_TAB *) 0xa5a5a5a5

Test case:

--disable_warnings

DROP TABLE /*! IF EXISTS */ CC;
DROP TABLE /*! IF EXISTS */ C;
DROP TABLE /*! IF EXISTS */ B;

--enable_warnings

CREATE TABLE `CC` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `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=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'v');
INSERT INTO `CC` VALUES (11,1,9,'r');
INSERT INTO `CC` VALUES (12,5,9,'a');
INSERT INTO `CC` VALUES (13,3,186,'m');
INSERT INTO `CC` VALUES (14,6,NULL,'y');
INSERT INTO `CC` VALUES (15,92,2,'j');
INSERT INTO `CC` VALUES (16,7,3,'d');
INSERT INTO `CC` VALUES (17,NULL,0,'z');
INSERT INTO `CC` VALUES (18,3,133,'e');
INSERT INTO `CC` VALUES (19,5,1,'h');
INSERT INTO `CC` VALUES (20,1,8,'b');
INSERT INTO `CC` VALUES (21,2,5,'s');
INSERT INTO `CC` VALUES (22,NULL,5,'e');
INSERT INTO `CC` VALUES (23,1,8,'j');
INSERT INTO `CC` VALUES (24,0,6,'e');
INSERT INTO `CC` VALUES (25,210,51,'f');
INSERT INTO `CC` VALUES (26,8,4,'v');
INSERT INTO `CC` VALUES (27,7,7,'x');
INSERT INTO `CC` VALUES (28,5,6,'m');
INSERT INTO `CC` VALUES (29,NULL,4,'c');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `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,NULL,2,'w');
INSERT INTO `C` VALUES (2,7,9,'m');
INSERT INTO `C` VALUES (3,9,3,'m');
INSERT INTO `C` VALUES (4,7,9,'k');
INSERT INTO `C` VALUES (5,4,NULL,'r');
INSERT INTO `C` VALUES (6,2,9,'t');
INSERT INTO `C` VALUES (7,6,3,'j');
INSERT INTO `C` VALUES (8,8,8,'u');
INSERT INTO `C` VALUES (9,NULL,8,'h');
INSERT INTO `C` VALUES (10,5,53,'o');
INSERT INTO `C` VALUES (11,NULL,0,NULL);
INSERT INTO `C` VALUES (12,6,5,'k');
INSERT INTO `C` VALUES (13,188,166,'e');
INSERT INTO `C` VALUES (14,2,3,'n');
INSERT INTO `C` VALUES (15,1,0,'t');
INSERT INTO `C` VALUES (16,1,1,'c');
INSERT INTO `C` VALUES (17,0,9,'m');
INSERT INTO `C` VALUES (18,9,5,'y');
INSERT INTO `C` VALUES (19,NULL,6,'f');
INSERT INTO `C` VALUES (20,4,2,'d');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `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=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'f');

SELECT table2 .`col_varchar_key`
FROM C table1 LEFT JOIN C table2 ON table1 .`col_int_key`
WHERE ( 8 ) IN (
        SELECT `pk` SUBQUERY2_field2
        FROM C
        WHERE `col_varchar_key`
        AND ( `col_int_nokey` ) IN (
                SELECT CHILD_SUBQUERY1_t1 .`col_int_nokey`
                FROM CC CHILD_SUBQUERY1_t1 RIGHT JOIN B CHILD_SUBQUERY1_t2 ON CHILD_SUBQUERY1_t1 .`col_varchar_key`
        )
);

DROP TABLE CC;
DROP TABLE C;
DROP TABLE B;

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

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

SELECT MIN( table2 . `col_varchar_key` ) AS field1
FROM ( C AS table1 STRAIGHT_JOIN (
SELECT SUBQUERY1_t1 . *
FROM C AS SUBQUERY1_t1
WHERE SUBQUERY1_t1 . `col_varchar_key` <= SUBQUERY1_t1 . `col_varchar_key` ) AS table2 ON (table2 . `pk` = table1 . `col_int_key` AND ( 8, 1 ) NOT IN (
SELECT SUBQUERY2_t1 . `pk` AS SUBQUERY2_field1 , SUBQUERY2_t1 . `col_int_nokey` AS SUBQUERY2_field2
FROM C AS SUBQUERY2_t1
WHERE ( SUBQUERY2_t1 . `col_varchar_nokey` <> SUBQUERY2_t1 . `col_varchar_key` AND ( SUBQUERY2_t1 . `col_int_nokey` , SUBQUERY2_t1 . `pk` ) IN (
SELECT CHILD_SUBQUERY1_t1 . `col_int_nokey` AS CHILD_SUBQUERY1_field1 , CHILD_SUBQUERY1_t2 . `col_int_nokey` AS child_subquery1_field2
FROM ( CC AS CHILD_SUBQUERY1_t1 RIGHT OUTER JOIN B AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_varchar_key` = CHILD_SUBQUERY1_t1 . `col_varchar_key` ) ) ) )
GROUP BY SUBQUERY2_field1 , SUBQUERY2_field2 ) ) )
WHERE ( table1 . `col_varchar_nokey` <= (
SELECT COUNT( SUBQUERY3_t1 . `col_varchar_key` ) AS SUBQUERY3_field1
FROM ( C AS SUBQUERY3_t1 INNER JOIN C AS SUBQUERY3_t2 ON (SUBQUERY3_t2 . `col_int_nokey` = SUBQUERY3_t1 . `pk` ) ) ) ) AND ( table1 . `col_int_key` <= table2 . `pk` OR table1 . `col_int_key` IN (
SELECT 8 UNION
SELECT 6 ) )
ORDER BY table1 . `col_datetime_key` DESC
;

DROP TABLE CC;
DROP TABLE C;
DROP TABLE B;
/* End of test case for query 1 */

[Philip Stoev (pstoev-askmonty)]

Sorry, actual crashing query is:

SELECT table2 .`col_varchar_key` FROM C table1 LEFT JOIN C table2 ON table1 .`col_int_key` AND ( 8 ) IN (
        SELECT `pk` SUBQUERY2_field2 FROM C
        WHERE `col_varchar_key` AND ( `col_int_nokey` ) IN (
                SELECT CHILD_SUBQUERY1_t1 .`col_int_nokey`
                FROM CC CHILD_SUBQUERY1_t1 RIGHT JOIN B CHILD_SUBQUERY1_t2 ON CHILD_SUBQUERY1_t1 .`col_varchar_key`
        )
        GROUP BY SUBQUERY2_field2
) ;

[Philip Stoev (pstoev-askmonty)]

An even simpler test case:

CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  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 AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8,'2002-02-21',NULL);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_date_key` date DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  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 AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7,'1900-01-01','f');

SELECT `col_date_key` FROM BB
WHERE 5 IN (
        SELECT SUBQUERY3_t1 .`col_int_key`
        FROM B SUBQUERY3_t1
        LEFT JOIN BB SUBQUERY3_t2 ON SUBQUERY3_t1 .`col_varchar_key`
);

[Philip Stoev (philip-stoev)]

Also reproducible on •Branch: 5.3-mwl-66 •Revision: 2790

[Philip Stoev (philip-stoev)]

Also reproducible on 5.3-subqueries-mwl90

[Philip Stoev (philip-stoev)]

See MySQL bug and patch - http://bugs.mysql.com/bug.php?id=46692

[Sergey Petrunia (sergefp)]

Isn't repeatable with the current 5.3 tree. Committed a testcase. MySQL bug will be handled with other MySQL bugs.