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:
None 
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
Description:
optimizer_subquery_stability test fails in PB2. For instance, for the push

 sergefp@mysql.com  2009-01-26 12:34:20 

one can view the log: (http://clustra.norway.sun.com/~bteam/pb2/web.pyaction=archive_download&archive_id=260389&p... , love the URL) and see the following:

Fails as follows (i've added line breaks to show the query structure): 
# 16:59:50 Query: 
 SELECT  OUTR . `varchar_nokey` AS X 
 FROM C AS OUTR 
  WHERE ( OUTR . `int_key` , OUTR . `int_nokey` ) IN ( SELECT  
      INNR . `pk` AS X , INNR . `pk` AS Y FROM CC AS INNR 
      WHERE OUTR . `datetime_nokey` IS NULL ORDER BY INNR . `varchar_nokey` 
  ) 
 OR 
 ( OUTR . `datetime_key` = '2002-09-10' OR OUTR . `time_key` IS NULL ) 
HAVING 
   X = NULL ORDER BY OUTR . `varchar_key` , OUTR . `pk` 
failed: 2013 Lost connection to MySQL server during query

How to repeat:
Typically it will repeat if you
* download the dataset (click on diskette icon next to test name)
* run the query
[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.