MySQL Bugs: #8115: Server Crash with prepared statement

Bug #8115	Server Crash with prepared statement
Submitted:	24 Jan 2005 17:12	Modified:	3 Mar 2005 15:32
Reporter:	Georg Richter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.3	OS:	Linux (Linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
reexecution of prepared statement crashes server
see how to repeat.

How to repeat:
Create Table: CREATE TABLE `t1` (
  `a` char(3) NOT NULL default '',
  `b` char(3) NOT NULL default '',
  `c` char(3) NOT NULL default '',
  PRIMARY KEY  (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE t2 LIKE t1;

PREPARE my_stmt FROM "SELECT t1.a, t1.b, t1.c, t2.a, t2.b, t2.c FROM ((t1 LEFT OUTER JOIN t2 ON t2.a=? AND t1.b=t2.b) LEFT OUTER JOIN t2 t3 ON t3.a=?) WHERE t1.a=?";

SET @a:=1, @b:=1, @c:=1;

EXECUTE my_stmt USING @a,@b,@c;

EXECUTE my_stmt USING @a,@b,@c;

Stacktrace:

0x81819e1 handle_segfault + 459
0x4004696c _end + 933858684
0x8125903 Item_cond::fix_fields(THD*, st_table_list*, Item**) + 213
0x81c1904 setup_conds(THD*, st_table_list*, st_table_list*, Item**) + 410
0x81c8068 JOIN::prepare(Item***, st_table_list*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) + 658
0x81cca4d _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 479
0x81c7d24 handle_select(THD*, st_lex*, select_result*, unsigned long) + 276
0x8198b21 mysql_execute_command(THD*) + 1463
0x81ef84b mysql_stmt_execute(THD*, char*, unsigned int) + 989
0x8196f30 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1488
0x8196954 do_command(THD*) + 556
0x8195cee handle_one_connection + 732
0x40040f60 _end + 933835632
0x401d6327 _end + 935495479

I was able to simplify the crashing query to

prepare stmt from "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) where t1.a=1";

Georg,
the bug is apparently caused by equality propogation optimization recently pushed into 5.0 which is not prepared-statements friendly yet.
To fix it I need hours or even days, and to pass Igor's review after that, until then you can switch it off with this patch:
kostja@dragonfly:~/work/mysql-5.0-root/sql> bk diffs -u
===== sql_select.cc 1.278 vs edited =====
--- 1.278/sql/sql_select.cc     2005-01-22 14:12:39 +03:00
+++ edited/sql_select.cc        2005-01-25 04:58:17 +03:00
@@ -6426,6 +6426,7 @@
 static COND *build_equal_items_for_cond(COND *cond,
                                         COND_EQUAL *inherited)
 {
+#if 0
   Item_equal *item_equal;
   uint members;
   COND_EQUAL cond_equal;
@@ -6521,6 +6522,7 @@
                             (byte *) inherited);
     cond->update_used_tables();
   }
+#endif
   return cond;
 }

ChangeSet
  1.1786 05/03/03 16:28:27 konstantin@mysql.com +11 -0
  Fixes for bug#8115 "Server Crash with prepared statement"
   and bug#8849 "problem with insert statement with table alias's":
  make equality propagation work in stored procedures and prepared
  statements.
  Equality propagation can change AND/OR structure of ON expressions,
  so the fix is to provide each execution of PS/SP with it's own
  copy of AND/OR tree. We have been doing that already for WHERE clauses,
  now ON clauses are also copied.

Pushed into current 5.0, tagged 5.0.3

Noted in 5.0.3 changelog.