RQG: Crash in optimize_semijoin_nests
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Invalid
|
Undecided
|
Sergey Petrunia |
Bug Description
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_
#6 0x082fde5c in make_join_
#7 0x082f654b in JOIN::optimize (this=0xb3d06d8) at sql_select.cc:921
#8 0x082309ab in subselect_
#9 0x08228a27 in Item_subselect:
#10 0x08228b4e in Item_in_
#11 0x0822a1ee in Item_in_
#12 0x081c8acc in Item::val_
#13 0x081f3bdb in Item_in_
#14 0x081b4212 in Item::val_bool (this=0xb39bdb8) at item.cc:184
#15 0x081f01b1 in Item_func_
#16 0x081b4212 in Item::val_bool (this=0xb389790) at item.cc:184
#17 0x081fb869 in Item_cond_
#18 0x083158b5 in evaluate_
#19 0x08315526 in sub_select (join=0xb3bbc10, join_tab=0xb39ce70, end_of_
#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_
order=
at sql_select.cc:2378
#23 0x082f4803 in handle_select (thd=0xb1671a0, lex=0xb1687b4, result=0xb39a7e0, setup_tables_
#24 0x0829466d in execute_
#25 0x0828b069 in mysql_execute_
#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
#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_
#30 0x00a08919 in start_thread () from /lib/libpthread
#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_
1197 join->map2table
1198 }
1199 List_iterator<Item> it(right_
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_
) 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_
) 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_
) 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 (
)
);
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_
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_datetime_key` (`col_datetime_
KEY `col_varchar_key` (`col_varchar_
) 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,
INSERT INTO `CC` VALUES (14,6,NULL,
INSERT INTO `CC` VALUES (15,92,
INSERT INTO `CC` VALUES (16,7,3,'2009-10-18 02:27:49','d','d');
INSERT INTO `CC` VALUES (17,NULL,
INSERT INTO `CC` VALUES (18,3,133,
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,
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,
INSERT INTO `CC` VALUES (26,8,4,'2000-04-03 16:33:32','v','v');
INSERT INTO `CC` VALUES (27,7,7,
INSERT INTO `CC` VALUES (28,5,6,'2001-04-25 01:26:12','m','m');
INSERT INTO `CC` VALUES (29,NULL,
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_
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_datetime_key` (`col_datetime_
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,
INSERT INTO `C` VALUES (2,7,9,
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,
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,
INSERT INTO `C` VALUES (10,5,53,
INSERT INTO `C` VALUES (11,NULL,
INSERT INTO `C` VALUES (12,6,5,'2005-08-15 12:39:41','k','k');
INSERT INTO `C` VALUES (13,188,
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,
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_
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_datetime_key` (`col_datetime_
KEY `col_varchar_key` (`col_varchar_
) 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
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 */
tags: | added: crash optimizer rqg semijoin |
summary: |
- Crash in optimize_semijoin_nests + RQG: Crash in optimize_semijoin_nests |
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
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 CHILD_SUBQUERY1_t1 .`col_int_nokey`
FROM CC CHILD_SUBQUERY1_t1 RIGHT JOIN B CHILD_SUBQUERY1_t2 ON CHILD_SUBQUERY1_t1 .`col_varchar_key`
SELECT `pk` SUBQUERY2_field2 FROM C
WHERE `col_varchar_key` AND ( `col_int_nokey` ) IN (
)
GROUP BY SUBQUERY2_field2
) ;