Bug #42353 | SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query causes crash | ||
---|---|---|---|
Submitted: | 26 Jan 2009 19:30 | Modified: | 13 Nov 2009 11:46 |
Reporter: | Sergey Petrunya | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 6.0-bk | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
Tags: | optimizer_switch, outerjoin, semijoin, subquery |
[26 Jan 2009 19:30]
Sergey Petrunya
[27 Jan 2009 7:22]
Sveta Smirnova
Thank you for the report. I can not open URL provided: Not Found The requested URL /~bteam/pb2/web.pyaction=archive_download&archive_id=260389&pretty=please was not found on this server. Please provide correct URL.
[27 Jan 2009 7:50]
Sveta Smirnova
Found the page: correct URL is http://clustra.norway.sun.com/~bteam/pb2/web.py?action=archive_download&archive_id=260389&..., but still don't see "icon". Please indicate how to download dataset?
[27 Jan 2009 16:06]
Sergey Petrunya
Sveta, we've got a suspect that this repeats on previous versions also. Could you please * Try 5.1 and post the result here * Try if EXPLAIN works (or crashes) on both 5.1 and 6.0 and post the result here ?
[30 Jan 2009 9:10]
Sveta Smirnova
Crash occurs when running query: SELECT DISTINCT OUTR . `date_key` AS X FROM C AS OUTR WHERE ( OUTR . `int_key` , OUTR . `int_nokey` ) IN ( SELECT INNR . `int_nokey` AS X , INNR . `pk` AS Y FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `date_nokey` < INNR . `date_key` ) WHERE INNR . `varchar_key` <= INNR . `varchar_nokey` OR INNR . `int_nokey` <= INNR . `pk` ) AND ( OUTR . `varchar_nokey` <> 'f' OR NOT OUTR . `int_key` < 7 ) HAVING X < '2001-11-14 00:43:55' ORDER BY OUTR . `datetime_nokey` , OUTR . `pk` but not posted in the initial description
[30 Jan 2009 10:21]
Sveta Smirnova
Version 5.1 does not crash. EXPLAIN with version 6.0 crashes.
[30 Jan 2009 10:53]
Sveta Smirnova
Verified as described on both Linux and Solaris. Backtrace from Linux: stack_bottom = 0xae5b6048 thread_stack 0x30c00 /users/ssmirnova/build/mysql-6.0/libexec/mysqld(my_print_stacktrace+0x32) [0x880c7f6] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(handle_segfault+0x2f7) [0x82b9c09] [0x2ce420] /users/ssmirnova/build/mysql-6.0/libexec/mysqld [0x8353509] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(JOIN::optimize()+0xa58) [0x8361180] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x294) [0x8368dd0] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x1ec) [0x836917e] /users/ssmirnova/build/mysql-6.0/libexec/mysqld [0x82c8fe1] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(mysql_execute_command(THD*)+0x7a6) [0x82cea02] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x22b) [0x82d77d5] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x8b3) [0x82d8217] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(do_command(THD*)+0x241) [0x82d957b] /users/ssmirnova/build/mysql-6.0/libexec/mysqld(handle_one_connection+0x11d) [0x82c7563] /lib/libpthread.so.0 [0x45fbd4] /lib/libc.so.6(__clone+0x5e) [0x3b74fe]
[30 Jan 2009 10:53]
Sveta Smirnova
Reduced test: DROP TABLE IF EXISTS `BB`; CREATE TABLE `BB` ( `date_nokey` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `C`; CREATE TABLE `C` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `date_key` date NOT NULL, `datetime_nokey` datetime NOT NULL, `varchar_nokey` varchar(1) NOT NULL, PRIMARY KEY (`pk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `CC`; CREATE TABLE `CC` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) NOT NULL, `date_key` date NOT NULL, `varchar_key` varchar(1) NOT NULL, `varchar_nokey` varchar(1) NOT NULL, PRIMARY KEY (`pk`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; #explain SELECT DISTINCT OUTR . `date_key` AS X FROM C AS OUTR WHERE ( OUTR . `int_key` , OUTR .`int_nokey` ) IN ( SELECT INNR . `int_nokey` AS X , INNR . `pk` AS Y FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `date_nokey` < INNR . `date_key` ) WHERE INNR . `varchar_key` <= INNR . `varchar_nokey` OR INNR . `int_nokey` <= INNR . `pk` ) AND ( OUTR . `varchar_nokey` <> 'f' OR NOT OUTR . `int_key` < 7 ) HAVING X < '2001-11-14 00:43:55' ORDER BY OUTR . `datetime_nokey` , OUTR . `pk` ;
[30 Jan 2009 10:56]
Sveta Smirnova
Formatted query: SELECT DISTINCT OUTR . `date_key` AS X FROM C AS OUTR WHERE ( OUTR . `int_key` , OUTR .`int_nokey` ) IN ( SELECT INNR . `int_nokey` AS X , INNR . `pk` AS Y FROM BB AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `date_nokey` < INNR . `date_key` ) WHERE INNR .`varchar_key` <= INNR . `varchar_nokey` OR INNR . `int_nokey` <= INNR . `pk` ) AND ( OUTR. `varchar_nokey` <> 'f' OR NOT OUTR . `int_key` < 7 ) HAVING X < '2001-11-14 00:43:55' ORDER BY OUTR . `datetime_nokey` , OUTR . `pk`
[2 Feb 2009 8:03]
Sergey Petrunya
Updated synopsis to reflect the bug nature.
[14 Jul 2009 22:25]
Patrick Crews
Not affected by optimizer_switch, optimizer_use_mrr, or engine_condition_pushdown values
[14 Sep 2009 14:17]
Roy Lyseng
Problem goes away when disabling semijoin.
[13 Nov 2009 11:46]
Øystein Grøvlen
This is a duplicate of Bug#46692. It has been verified that a fix for that bug fixes this issue too.