| Bug #37893 | Crash in select_describe line 19251 on a FirstMatch query with OUTER JOIN | ||
|---|---|---|---|
| Submitted: | 5 Jul 2008 20:00 | Modified: | 29 Dec 2009 10:12 |
| Reporter: | Philip Stoev | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 6.0 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | optimizer_switch, outerjoin, semijoin, subquery | ||
[25 Jan 2009 17:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64002 2809 Sergey Petrunia 2009-01-25 BUG#37893 and others: Handing of OUTER JOINs and semi-joins in one select is broken - Disable subquery-to-semi-join conversion when there is an outer join in the child or in the parent select - Testcases
[14 Jul 2009 22:23]
Patrick Crews
The optimizer_switch, optimizer_use_mrr, and engine_condition_pushdown variable settings did not affect the presence of this bug in my testing.
[15 Sep 2009 10:48]
Roy Lyseng
Disabling semijoin with the following statement: set optimizer_switch='default,semijoin=off'; makes this query not dump core.
[10 Dec 2009 10:35]
Roy Lyseng
Normalized test case:
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
int_nokey int(11) DEFAULT NULL,
int_key int(11) DEFAULT NULL,
date_key date DEFAULT NULL,
date_nokey date DEFAULT NULL,
time_key time DEFAULT NULL,
time_nokey time DEFAULT NULL,
datetime_key datetime DEFAULT NULL,
datetime_nokey datetime DEFAULT NULL,
varchar_key varchar(5) DEFAULT NULL,
varchar_nokey varchar(5) DEFAULT NULL,
PRIMARY KEY(pk),
KEY int_key(int_key),
KEY date_key(date_key),
KEY time_key(time_key),
KEY datetime_key(datetime_key),
KEY varchar_key(varchar_key)
);
INSERT INTO t1 VALUES
(1,NULL,NULL,NULL,NULL,'00:00:00','00:00:00','2007-10-14 00:00:00',
'2007-10-14 00:00:00','dtrp','dtrp'),
(2,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15',
'2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'),
(3,NULL,NULL,'2009-09-14','2009-09-14',NULL,NULL,
'2000-01-30 16:39:40','2000-01-30 16:39:40','qj','qj'),
(4,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39',
'0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'),
(5,7,7,NULL,NULL,NULL,NULL,'0000-00-00 00:00:00',
'0000-00-00 00:00:00','{bq','{bq'),
(6,8,8,'2000-01-14','2000-01-14','23:07:21','23:07:21',NULL,NULL,'xhn','xhn'),
(7,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19',
'2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'),
(8,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00',
'2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL),
(9,NULL,NULL,'0000-00-00','0000-00-00','23:41:06','23:41:06',
'0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL),
(10,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38',
'2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
int_nokey int(11) DEFAULT NULL,
int_key int(11) DEFAULT NULL,
date_key date DEFAULT NULL,
date_nokey date DEFAULT NULL,
time_key time DEFAULT NULL,
time_nokey time DEFAULT NULL,
datetime_key datetime DEFAULT NULL,
datetime_nokey datetime DEFAULT NULL,
varchar_key varchar(5) DEFAULT NULL,
varchar_nokey varchar(5) DEFAULT NULL,
PRIMARY KEY(pk),
KEY int_key(int_key),
KEY date_key(date_key),
KEY time_key(time_key),
KEY datetime_key(datetime_key),
KEY varchar_key(varchar_key)
);
EXPLAIN
SELECT *
FROM t1 AS OUTR
WHERE '2006-2-22 1:52:21' IN (
SELECT INNR.time_nokey AS Y
FROM t1 AS INNR2
LEFT JOIN t2 AS INNR ON INNR2.pk <= INNR.int_key
)
AND OUTR.int_key > 218 ;
[10 Dec 2009 10:38]
Roy Lyseng
Verified that this bug is fixed by the fix to bug#49489. Verified using all combinations of semijoin-related optimizer switches: Materialization, FirstMatch, LooseScan. Reassigning to Øystein for further work.
[10 Dec 2009 10:46]
Roy Lyseng
Sorry, the bug fix that fixes this bug is bug#46692.
[29 Dec 2009 10:12]
Øystein Grøvlen
This is a duplicate of bug#46692. (The right table of the left outer join is empty).
Description: When executing an EXPLAIN SELECT query, mysqld crashed as follows. Executing the query without EXPLAIN does not cause a crash. #0 0x00110416 in __kernel_vsyscall () #1 0x00581c78 in pthread_kill () from /lib/libpthread.so.0 #2 0x085b0da4 in my_write_core (sig=11) at stacktrace.c:307 #3 0x08213e98 in handle_segfault (sig=11) at mysqld.cc:2638 #4 <signal handler called> #5 select_describe (join=0x98afb80, need_tmp_table=false, need_order=false, distinct=<value optimized out>, message=0x0) at sql_select.cc:19251 #6 0x0828bee0 in JOIN::exec (this=0x98afb80) at sql_select.cc:2363 #7 0x0828e1fa in mysql_select (thd=0x9847eb0, rref_pointer_array=0x9849394, tables=0x98aba70, wild_num=1, fields=@0x9849324, conds=0x98af660, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764740, result=0x98af778, unit=0x9849000, select_lex=0x9849290) at sql_select.cc:3001 #8 0x0828e528 in mysql_explain_union (thd=0x9847eb0, unit=0x9849000, result=0x98af778) at sql_select.cc:19356 #9 0x0822002e in execute_sqlcom_select (thd=0x9847eb0, all_tables=0x98aba70) at sql_parse.cc:4825 #10 0x08220f61 in mysql_execute_command (thd=0x9847eb0) at sql_parse.cc:2015 #11 0x0822a02e in mysql_parse (thd=0x9847eb0, inBuf=0x98ab590 "EXPLAIN\nSELECT *\nFROM t1 AS OUTR\nWHERE '2006-2-22 1:52:21' IN (\nSELECT INNR . `time_nokey` AS Y\nFROM t1 AS INNR2 \nLEFT JOIN t2 AS INNR\nON ( INNR2 . `pk` <= INNR . `int_key` ) \n)\nAND OUTR . `int_key` >"..., length=204, found_semicolon=0xadda4314) at sql_parse.cc:5811 #12 0x0822a921 in dispatch_command (command=COM_QUERY, thd=0x9847eb0, packet=0x989c9d1 "", packet_length=205) at sql_parse.cc:1051 #13 0x0822ba49 in do_command (thd=0x9847eb0) at sql_parse.cc:724 #14 0x0821bef0 in handle_one_connection (arg=0x9847eb0) at sql_connect.cc:1153 #15 0x0057d32f in start_thread () from /lib/libpthread.so.0 #16 0x0049a27e in clone () from /lib/libc.so.6 The crash is here: 19246 extra.append(STRING_WITH_LEN("; End temporary")); 19247 else if (tab->do_firstmatch) 19248 { 19249 extra.append(STRING_WITH_LEN("; FirstMatch(")); 19250 TABLE *prev_table=tab->do_firstmatch->table; 19251 if (prev_table->derived_select_number) <<<<< HERE 19252 { 19253 char namebuf[NAME_LEN]; 19254 /* Derived table name generation */ 19255 int len= my_snprintf(namebuf, sizeof(namebuf)-1, (gdb) print tab $1 = (JOIN_TAB *) 0x98be354 (gdb) print tab->do_firstmatch $2 = (st_join_table *) 0x98be00c (gdb) print tab->do_firstmatch->table How to repeat: CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date DEFAULT NULL, `date_nokey` date DEFAULT NULL, `time_key` time DEFAULT NULL, `time_nokey` time DEFAULT NULL, `datetime_key` datetime DEFAULT NULL, `datetime_nokey` datetime DEFAULT NULL, `varchar_key` varchar(5) DEFAULT NULL, `varchar_nokey` varchar(5) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`) ); INSERT INTO t1 VALUES (1,NULL,NULL,NULL,NULL,'00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','dtrp','dtrp'), (2,5,5,'2009-10-16','2009-10-16','09:28:15','09:28:15','2007-09-14 05:34:08','2007-09-14 05:34:08','qk','qk'), (3,NULL,NULL,'2009-09-14','2009-09-14',NULL,NULL,'2000-01-30 16:39:40','2000-01-30 16:39:40','qj','qj'), (4,6,6,'0000-00-00','0000-00-00','23:06:39','23:06:39','0000-00-00 00:00:00','0000-00-00 00:00:00','j','j'), (5,7,7,NULL,NULL,NULL,NULL,'0000-00-00 00:00:00','0000-00-00 00:00:00','{bq','{bq'), (6,8,8,'2000-01-14','2000-01-14','23:07:21','23:07:21',NULL,NULL,'xhn','xhn'), (7,10,10,'2000-12-18','2000-12-18','22:16:19','22:16:19','2006-11-04 15:42:50','2006-11-04 15:42:50','aew','aew'), (8,0,0,'2001-09-18','2001-09-18','00:00:00','00:00:00','2004-03-23 13:23:35','2004-03-23 13:23:35',NULL,NULL), (9,NULL,NULL,'0000-00-00','0000-00-00','23:41:06','23:41:06','0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,NULL), (10,6,6,'2007-08-16','2007-08-16','22:13:38','22:13:38','2004-08-19 11:01:28','2004-08-19 11:01:28','qu','qu'); CREATE TABLE t2 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) DEFAULT NULL, `int_key` int(11) DEFAULT NULL, `date_key` date DEFAULT NULL, `date_nokey` date DEFAULT NULL, `time_key` time DEFAULT NULL, `time_nokey` time DEFAULT NULL, `datetime_key` datetime DEFAULT NULL, `datetime_nokey` datetime DEFAULT NULL, `varchar_key` varchar(5) DEFAULT NULL, `varchar_nokey` varchar(5) DEFAULT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`) ); EXPLAIN SELECT * FROM t1 AS OUTR WHERE '2006-2-22 1:52:21' IN ( SELECT INNR . `time_nokey` AS Y FROM t1 AS INNR2 LEFT JOIN t2 AS INNR ON ( INNR2 . `pk` <= INNR . `int_key` ) ) AND OUTR . `int_key` > 218 ;