Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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
(gdb) print s
$1 = (JOIN_TAB *) 0xa5a5a5a5
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: RQG: Crash in fix_semijoin_strategies_for_picked_join_order()
Not repeatable with the latest 5.3 tree.
Launchpad bug id: 600968
Re: fix_semijoin_strategies_for_picked_join_order
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`
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;