MariaDB

RQG: Crash in fix_semijoin_strategies_for_picked_join_order()

Bug #600968 reported by Philip Stoev on 2010-07-02

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Invalid	High	Sergey Petrunia	MariaDB 5.3

Bug Description

Queries of the following form cause a crash in maria 5.3 and not on 5.2 or lp:mysql. The correct response instead seems to be "1242: Subquery returns more than 1 row"

SELECT `col_int_key` FROM C
WHERE (
  SELECT `col_varchar_nokey`
  FROM CC SUBQUERY1_t1
  WHERE `col_varchar_key` = ANY (
    SELECT CHILD_SUBQUERY1_t1 .`col_varchar_key`
    FROM CC CHILD_SUBQUERY1_t1
    LEFT JOIN A ON CHILD_SUBQUERY1_t1 .`pk`
    WHERE SUBQUERY1_t1 .`col_int_nokey`
  )
)

bzr version-info:

[philips@eve mysql-test]$ bzr version-info
revision-id: <email address hidden>
date: 2010-06-14 15:17:54 +0400
build-date: 2010-07-02 02:40:41 -0700
revno: 2789
branch-nick: maria

backtrace:

#3 0x082776c0 in handle_segfault (sig=11) at mysqld.cc:2683
#4 <signal handler called>
#5 0x083ad313 in fix_semijoin_strategies_for_picked_join_order (join=0xadacc58) at opt_subselect.cc:2044
#6 0x08304e2f in get_best_combination (join=0xadacc58) at sql_select.cc:5859
#7 0x082fdf92 in make_join_statistics (join=0xadacc58, tables_arg=0xace1780, conds=0xadb6f30, keyuse_array=0xadb1990) at sql_select.cc:2970
#8 0x082f654b in JOIN::optimize (this=0xadacc58) at sql_select.cc:921
#9 0x0822dbe4 in subselect_single_select_engine::exec (this=0xace29c8) at item_subselect.cc:2521
#10 0x08228a27 in Item_subselect::exec (this=0xace2910) at item_subselect.cc:461
#11 0x0822976f in Item_singlerow_subselect::val_int (this=0xace2910) at item_subselect.cc:818
#12 0x081d93c7 in eval_const_cond (cond=0xace2910) at item_func.cc:63
#13 0x0830f231 in remove_eq_conds (thd=0xab881a0, cond=0xace2910, cond_value=0xadacb34) at sql_select.cc:10325
#14 0x0830ec20 in optimize_cond (join=0xada7de8, conds=0xace2910, join_list=0xab89b5c, cond_value=0xadacb34) at sql_select.cc:10163
#15 0x082f5e56 in JOIN::optimize (this=0xada7de8) at sql_select.cc:784
#16 0x082fb8fa in mysql_select (thd=0xab881a0, rref_pointer_array=0xab89b9c, tables=0xace1038, wild_num=0, fields=..., conds=0xace2910, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xace2a60, unit=0xab89810, select_lex=0xab89a98) at sql_select.cc:2364
#17 0x082f4803 in handle_select (thd=0xab881a0, lex=0xab897b4, result=0xace2a60, setup_tables_done_option=0) at sql_select.cc:276
#18 0x0829466d in execute_sqlcom_select (thd=0xab881a0, all_tables=0xace1038) at sql_parse.cc:5133
#19 0x0828b069 in mysql_execute_command (thd=0xab881a0) at sql_parse.cc:2318
#20 0x08296857 in mysql_parse (thd=0xab881a0,
    inBuf=0xace0cc8 "SELECT `col_int_key`\nFROM C\nWHERE (\nSELECT `col_varchar_nokey`\nFROM CC SUBQUERY1_t1\nWHERE `col_varchar_key` = ANY (\nSELECT CHILD_SUBQUERY1_t1 .`col_varchar_key`\nFROM CC CHILD_SUBQUERY1_t1 LEFT JOI"..., length=273, found_semicolon=0xaedb4228) at sql_parse.cc:6079
#21 0x08288b11 in dispatch_command (command=COM_QUERY, thd=0xab881a0,
    packet=0xac93571 "SELECT `col_int_key`\nFROM C\nWHERE (\nSELECT `col_varchar_nokey`\nFROM CC SUBQUERY1_t1\nWHERE `col_varchar_key` = ANY (\nSELECT CHILD_SUBQUERY1_t1 .`col_varchar_key`\nFROM CC CHILD_SUBQUERY1_t1 LEFT JOI"..., packet_length=276) at sql_parse.cc:1253
#22 0x08287d4f in do_command (thd=0xab881a0) at sql_parse.cc:891
#23 0x08284e96 in handle_one_connection (arg=0xab881a0) at sql_connect.cc:1599
#24 0x00a08919 in start_thread () from /lib/libpthread.so.0
#25 0x00951e5e in clone () from /lib/libc.so.6

code:

(gdb) list
2039 POSITION *pos= join->best_positions + tablenr;
2040 JOIN_TAB *s= pos->table;
2041 uint first;
2042 LINT_INIT(first); // Set by every branch except SJ_OPT_NONE which doesn't use it
2043
2044 if ((handled_tabs & s->table->map) || pos->sj_strategy == SJ_OPT_NONE)
2045 {
2046 remaining_tables |= s->table->map;
2047 continue;
2048 }

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

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2010-07-02:

Download full text (7.8 KiB)

Test case:

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

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

SELECT `col_int_key`
FROM C
WHERE (
SELECT `col_varchar_nokey`
FROM CC SUBQUERY1_t1
WHERE `col_varchar_key` = ANY (
SELECT CHILD_SUBQUERY1_t1 .`col_varchar_key`
FR...

Test case:

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

SELECT `col_int_key`
FROM C
WHERE (
SELECT `col_varchar_nokey`
FROM CC SUBQUERY1_t1
WHERE `col_varchar_key`  =  ANY (
SELECT CHILD_SUBQUERY1_t1 .`col_varchar_key`
FROM CC CHILD_SUBQUERY1_t1  LEFT  JOIN A  ON CHILD_SUBQUERY1_t1 .`pk`
WHERE SUBQUERY1_t1 .`col_int_nokey` )  )   ;

DROP TABLE A;
DROP TABLE CC;
DROP TABLE C;

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

CREATE TABLE `A` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `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,
  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 DEFAULT CHARSET=latin1;
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_date_key` date 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_date_key` (`col_date_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,NULL,'v','v');
INSERT INTO `CC` VALUES (11,1,9,'2006-06-14','r','r');
INSERT INTO `CC` VALUES (12,5,9,'2002-09-12','a','a');
INSERT INTO `CC` VALUES (13,3,186,'2005-02-15','m','m');
INSERT INTO `CC` VALUES (14,6,NULL,NULL,'y','y');
INSERT INTO `CC` VALUES (15,92,2,'2008-11-04','j','j');
INSERT INTO `CC` VALUES (16,7,3,'2004-09-04','d','d');
INSERT INTO `CC` VALUES (17,NULL,0,'2006-06-05','z','z');
INSERT INTO `CC` VALUES (18,3,133,'1900-01-01','e','e');
INSERT INTO `CC` VALUES (19,5,1,'1900-01-01','h','h');
INSERT INTO `CC` VALUES (20,1,8,'1900-01-01','b','b');
INSERT INTO `CC` VALUES (21,2,5,'2005-01-13','s','s');
INSERT INTO `CC` VALUES (22,NULL,5,'2006-05-21','e','e');
INSERT INTO `CC` VALUES (23,1,8,'2003-09-08','j','j');
INSERT INTO `CC` VALUES (24,0,6,'2006-12-23','e','e');
INSERT INTO `CC` VALUES (25,210,51,'2006-10-15','f','f');
INSERT INTO `CC` VALUES (26,8,4,'2005-04-06','v','v');
INSERT INTO `CC` VALUES (27,7,7,'2008-04-07','x','x');
INSERT INTO `CC` VALUES (28,5,6,'2006-10-10','m','m');
INSERT INTO `CC` VALUES (29,NULL,4,'1900-01-01','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_date_key` date 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_date_key` (`col_date_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,NULL,'w','w');
INSERT INTO `C` VALUES (2,7,9,'2001-09-19','m','m');
INSERT INTO `C` VALUES (3,9,3,'2004-09-12','m','m');
INSERT INTO `C` VALUES (4,7,9,NULL,'k','k');
INSERT INTO `C` VALUES (5,4,NULL,'2002-07-19','r','r');
INSERT INTO `C` VALUES (6,2,9,'2002-12-16','t','t');
INSERT INTO `C` VALUES (7,6,3,'2006-02-08','j','j');
INSERT INTO `C` VALUES (8,8,8,'2006-08-28','u','u');
INSERT INTO `C` VALUES (9,NULL,8,'2001-04-14','h','h');
INSERT INTO `C` VALUES (10,5,53,'2000-01-05','o','o');
INSERT INTO `C` VALUES (11,NULL,0,'2003-12-06',NULL,NULL);
INSERT INTO `C` VALUES (12,6,5,'1900-01-01','k','k');
INSERT INTO `C` VALUES (13,188,166,'2002-11-27','e','e');
INSERT INTO `C` VALUES (14,2,3,NULL,'n','n');
INSERT INTO `C` VALUES (15,1,0,'2003-05-27','t','t');
INSERT INTO `C` VALUES (16,1,1,'2005-05-03','c','c');
INSERT INTO `C` VALUES (17,0,9,'2001-04-18','m','m');
INSERT INTO `C` VALUES (18,9,5,'2005-12-27','y','y');
INSERT INTO `C` VALUES (19,NULL,6,'2004-08-20','f','f');
INSERT INTO `C` VALUES (20,4,2,'1900-01-01','d','d');

SELECT    table1 . `col_int_key` AS field1
FROM ( C AS table1 RIGHT  JOIN CC AS table2 ON (table2 . `pk` = table1 . `col_int_key`  ) )
WHERE (  NOT EXISTS ( (
SELECT DISTINCT SQL_SMALL_RESULT SUBQUERY1_t1 . `col_varchar_nokey` AS SUBQUERY1_field1
FROM CC AS SUBQUERY1_t1
WHERE (  SUBQUERY1_t1 . `col_varchar_key` = ANY (
SELECT DISTINCT  CHILD_SUBQUERY1_t1 . `col_varchar_key` AS CHILD_SUBQUERY1_field1
FROM ( CC AS CHILD_SUBQUERY1_t1 LEFT OUTER JOIN A AS CHILD_SUBQUERY1_t2 ON (CHILD_SUBQUERY1_t2 . `col_int_key` = CHILD_SUBQUERY1_t1 . `pk` ) )
WHERE CHILD_SUBQUERY1_t2 . `col_int_nokey` != SUBQUERY1_t1 . `col_int_nokey` ) AND SUBQUERY1_t1 . `col_int_key` = SUBQUERY1_t1 . `col_int_key` )  ) ) ) AND table1 . `pk` = 230 AND ( table2 . `col_varchar_key` = table2 . `col_varchar_key` OR table1 . `col_int_nokey` IS  NULL )
ORDER BY table1 . `col_date_key` , field1
LIMIT 100
;

DROP TABLE A;
DROP TABLE CC;
DROP TABLE C;

Philip Stoev (philip-stoev) on 2010-07-07

summary:

- fix_semijoin_strategies_for_picked_join_order
+ RQG: Crash in fix_semijoin_strategies_for_picked_join_order()

Philip Stoev (pstoev-askmonty) on 2010-08-03

Changed in maria:
milestone:	none → 5.3

Sergey Petrunia (sergefp) on 2011-03-01

Changed in maria:
assignee:	nobody → Sergey Petrunia (sergefp)

Sergey Petrunia (sergefp) on 2011-06-22

Changed in maria:
importance:	Undecided → High

Revision history for this message

Sergey Petrunia (sergefp) wrote on 2011-06-22:

Not repeatable with the latest 5.3 tree.

Changed in maria:
status:	New → Invalid

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.