Bug #51780 randgen outer_join.yy causes crash in add_found_match_trig_cond
Submitted: 5 Mar 2010 20:03 Modified: 8 Mar 2010 13:55
Reporter: Sergey Petrunya Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1 bzr OS:Linux (x86 Ubuntu 9.04)
Assigned to: Assigned Account CPU Architecture:Any

[5 Mar 2010 20:03] Sergey Petrunya
Description:
Running randgen's current  optimizer/outer_join.yy test causes the server to crash.

How to repeat:
Get current lp:~mysql/mysql-server/mysql-5.1 , compile with BUILD/compile-pentium-debug-max-no-ndb, then run this:

~/dev/randgen$ perl runall.pl --basedir=/home/psergey/dev/mysql-5.1  --gendata=conf/optimizer/outer_join.zz --grammar=conf/optimizer/outer_join.yy   --queries=1000000 --threads=1

and observe this:
# 2010-03-05T22:35:32 Starting 
...
# 2010-03-05T22:39:40 Query:   SELECT  STRAIGHT_JOIN  MIN(  table2 . `col_int` ) AS field1 , table1 . `col_int_key` AS field2 , table3 . `col_int_key` AS field3 , table1 . `pk` AS field4 , table2 . `pk` AS field5 , table1 . `col_int` AS field6 FROM  W AS table1  RIGHT  JOIN   P AS table2  RIGHT  JOIN   H AS table3  LEFT  JOIN BB AS table4 ON  table3 . `col_int_key` =  table4 . `pk`   LEFT  JOIN W AS table5 ON  table3 . `pk` =  table5 . `pk`  ON  table2 . `col_varchar_1024_latin1` =  table4 . `col_varchar_1024_latin1_key`   RIGHT  JOIN  E AS table6  LEFT OUTER JOIN  D AS table7  LEFT  JOIN W AS table8 ON  table7 . `col_int` =  table8 . `col_int_key`  ON  table6 . `col_varchar_10_latin1` =  table8 . `col_varchar_1024_latin1_key`  ON  table3 . `col_int_key` =  table6 . `col_int`  ON  table1 . `pk` =  table8 . `pk`  WHERE ( table4 . `col_int_key`  IN (4) OR table2 . `pk` != 8 )  GROUP BY field2, field3, field4, field5, field6    failed: 2013 Lost connection to MySQL server during query

...
# 2010-03-05T22:39:42 #0  0xb80d1430 in __kernel_vsyscall ()
# 2010-03-05T22:39:42 #0  0xb80d1430 in __kernel_vsyscall ()
# 2010-03-05T22:39:42 #1  0xb80aa1c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
# 2010-03-05T22:39:42 #2  0x0860acd9 in my_write_core (sig=11) at stacktrace.c:329
# 2010-03-05T22:39:42 #3  0x08282372 in handle_segfault (sig=11) at mysqld.cc:2569
# 2010-03-05T22:39:42 #4  <signal handler called>
# 2010-03-05T22:39:42 #5  0x082fee3f in add_found_match_trig_cond (tab=0x0, cond=0xa680c28, root_tab=0xa67f6b8) at sql_select.cc:6042
# 2010-03-05T22:39:42 #6  0x083107d7 in make_join_select (join=0xa4ac590, select=0xa5ef3a0, cond=0xa4ac070) at sql_select.cc:6489
# 2010-03-05T22:39:42 #7  0x0831cb5a in JOIN::optimize (this=0xa4ac590) at sql_select.cc:1115
# 2010-03-05T22:39:42 #8  0x0831ff67 in mysql_select (thd=0xa2de3d8, rref_pointer_array=0xa2df878, tables=0xa2af998, wild_num=0, fields=@0xa2df814, conds=0xa4ac070, og_num=5, order=0x0, group=0xa4ac1a8, having=0x0, 
# 2010-03-05T22:39:42     proc_param=0x0, select_options=2147764738, result=0xa4ac578, unit=0xa2df510, select_lex=0xa2df780) at sql_select.cc:2471
# 2010-03-05T22:39:42 #9  0x0832597b in handle_select (thd=0xa2de3d8, lex=0xa2df4b4, result=0xa4ac578, setup_tables_done_option=0) at sql_select.cc:269
# 2010-03-05T22:39:42 #10 0x08293015 in execute_sqlcom_select (thd=0xa2de3d8, all_tables=0xa2af998) at sql_parse.cc:5052
# 2010-03-05T22:39:42 #11 0x082952bc in mysql_execute_command (thd=0xa2de3d8) at sql_parse.cc:2248
# 2010-03-05T22:39:42 #12 0x0829ed0a in mysql_parse (thd=0xa2de3d8, 
# 2010-03-05T22:39:42     inBuf=0xa2aec60 "SELECT  STRAIGHT_JOIN  MIN(  table2 . `col_int` ) AS field1 , table1 . `col_int_key` AS field2 , table3 . `col_int_key` AS field3 , table1 . `pk` AS field4 , table2 . `pk` AS field5 , table1 . `col_in"..., length=904, found_semicolon=0xb2ffd080) at sql_parse.cc:5971
# 2010-03-05T22:39:42 #13 0x0829fb2e in dispatch_command (command=COM_QUERY, thd=0xa2de3d8, 
# 2010-03-05T22:39:42     packet=0xa2c0b91 "  SELECT  STRAIGHT_JOIN  MIN(  table2 . `col_int` ) AS field1 , table1 . `col_int_key` AS field2 , table3 . `col_int_key` AS field3 , table1 . `pk` AS field4 , table2 . `pk` AS field5 , table1 . `col_"..., packet_length=909) at sql_parse.cc:1233
# 2010-03-05T22:39:42 #14 0x082a1093 in do_command (thd=0xa2de3d8) at sql_parse.cc:874
# 2010-03-05T22:39:42 #15 0x0828c153 in handle_one_connection (arg=0xa2de3d8) at sql_connect.cc:1127
# 2010-03-05T22:39:42 #16 0xb80a54ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
# 2010-03-05T22:39:42 #17 0xb7f9449e in clone () from /lib/tls/i686/cmov/libc.so.6
[6 Mar 2010 9:32] Sveta Smirnova
Thank you for the report.

Verified as described.
[8 Mar 2010 13:41] Philip Stoev
Patrick, can you please derive a simplified MTR test case out of this crash. Thanks!
[8 Mar 2010 13:55] Patrick Crews
This is a duplicate of http://bugs.mysql.com/bug.php?id=48971, which has unfortunately been marked private.

Simplified test case:

#/* Server0: MySQL 5.1.41-gcov-debug-log */

#/*!50400 SET SESSION optimizer_switch =
'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on'
*/;
#/*!50400 SET SESSION optimizer_use_mrr = '' */;
#/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
#/*!50400 SET SESSION join_cache_level = '' */;
#/*!50400 SET SESSION debug = '' */;

#/* Begin test case for query 0 */

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

CREATE TABLE `A` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,2);
INSERT INTO `C` VALUES (2,9);
INSERT INTO `C` VALUES (3,3);
INSERT INTO `C` VALUES (4,9);
INSERT INTO `C` VALUES (5,NULL);
INSERT INTO `C` VALUES (6,9);
INSERT INTO `C` VALUES (7,3);
INSERT INTO `C` VALUES (8,8);
INSERT INTO `C` VALUES (9,8);
INSERT INTO `C` VALUES (10,53);
INSERT INTO `C` VALUES (11,0);
INSERT INTO `C` VALUES (12,5);
INSERT INTO `C` VALUES (13,166);
INSERT INTO `C` VALUES (14,3);
INSERT INTO `C` VALUES (15,0);
INSERT INTO `C` VALUES (16,1);
INSERT INTO `C` VALUES (17,9);
INSERT INTO `C` VALUES (18,5);
INSERT INTO `C` VALUES (19,6);
INSERT INTO `C` VALUES (20,2);
CREATE TABLE `BB` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (10,8);
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_key` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `varchar_key` (`int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,7);

 
SELECT table1 .`int_key`  
FROM BB table1  LEFT  JOIN BB table2  LEFT  JOIN B table4  JOIN C table5  ON table5 .`pk`
 ON table4 .`pk`  LEFT  JOIN A  JOIN BB table8  ON table8 .`pk`  RIGHT  JOIN BB table9 
JOIN C table10  ON table10 .`int_key`  ON table9 .`pk`  ON table5 .`int_key`  = table9
.`pk`  RIGHT  JOIN BB table11  LEFT  JOIN C table12  JOIN BB table14  ON table12 .`pk`  =
table14 .`int_key`  ON table11 .`int_key`  = table12 .`pk`  ON table5 .`int_key`  =
table14 .`int_key`  ON table2 .`pk`  
WHERE table1 .`pk`  
HAVING  55   ;

DROP TABLE A;
DROP TABLE C;
DROP TABLE BB;
DROP TABLE B;
#/* End of test case for query 0 */