MySQL Bugs: #8849: problem with insert statement with table alias's

Bug #8849	problem with insert statement with table alias's
Submitted:	28 Feb 2005 19:11	Modified:	3 Mar 2005 13:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3 Alpha	OS:	Windows (Windows XP)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Basically it seems to run successfully the first time I run it, however the next time I run it I get the following message;

>ERROR 2013 (HY000): Lost connection to MySQL server during query

Sure enough, I then check the MySql Service and it has stopped.

How to repeat:
CREATE PROCEDURE `dbpos`.`upuruntimecreation`() begin

delete from slsruntimeprice;
delete from slsruntimemap;

insert into slsruntimeprice
(
slsruntimeprice.lbsiid,
slsruntimeprice.ltradingareaid,
slsruntimeprice.cmode1,
slsruntimeprice.cmode2,
slsruntimeprice.cmode3,
slsruntimeprice.cmode4,
slsruntimeprice.cmode5,
slsruntimeprice.cmode6,
slsruntimeprice.cmode7,
slsruntimeprice.cmode8,
slsruntimeprice.cmode9,
slsruntimeprice.cmode10
)
select distinct slsruntimeindex.`lpitnumber`, slssellingprice.`ltradingareaid`, price01.`csellingprice` as cprice1, price02.`csellingprice` as cprice2, price03.`csellingprice` as cprice3, price04.`csellingprice` as cprice4, price05.`csellingprice` as cprice5, price06.`csellingprice` as cprice6, price07.`csellingprice` as cprice7, price08.`csellingprice` as cprice8, price09.`csellingprice` as cprice9, price10.`csellingprice` as cprice10 from slssellingprice inner join slsruntimeindex on slsruntimeindex.`lpitnumber` = slssellingprice.`lbsiid` and slsruntimeindex.`lrecordtype` = 1 left outer join slssellingprice as price01 on price01.`lbsiid` = slssellingprice.`lbsiid` and price01.`ltradingmodeid` = 1 and slssellingprice.`ltradingareaid` = price01.`ltradingareaid` left outer join slssellingprice as price02 on price02.`lbsiid` = slssellingprice.`lbsiid` and price02.`ltradingmodeid` = 2 and slssellingprice.`ltradingareaid` = price02.`ltradingareaid` left outer join slssellingprice as price03 on price03.`lbsiid` = slssellingprice.lbsiid and price03.`ltradingmodeid` = 3 and slssellingprice.`ltradingareaid` = price03.`ltradingareaid` left outer join slssellingprice as price04 on price04.`lbsiid` = slssellingprice.`lbsiid` and price04.`ltradingmodeid` = 4 and slssellingprice.`ltradingareaid` = price04.`ltradingareaid` left outer join slssellingprice as price05 on price05.`lbsiid` = slssellingprice.`lbsiid` and price05.`ltradingmodeid` = 5 and slssellingprice.`ltradingareaid` = price05.`ltradingareaid` left outer join slssellingprice as price06 on price06.`lbsiid` = slssellingprice.`lbsiid` and price06.`ltradingmodeid` = 6 and slssellingprice.`ltradingareaid` = price06.`ltradingareaid` left outer join slssellingprice as price07 on price07.`lbsiid` = slssellingprice.`lbsiid` and price07.`ltradingmodeid` = 7 and slssellingprice.`ltradingareaid` = price07.`ltradingareaid` left outer join slssellingprice as price08 on price08.`lbsiid` = slssellingprice.`lbsiid` and price08.`ltradingmodeid` = 8 and slssellingprice.`ltradingareaid` = price08.`ltradingareaid` left outer join slssellingprice as price09 on price09.`lbsiid` = slssellingprice.`lbsiid` and price09.`ltradingmodeid` = 9 and slssellingprice.`ltradingareaid` = price09.`ltradingareaid` left outer join slssellingprice as price10 on price10.`lbsiid` = slssellingprice.`lbsiid` and price10.`ltradingmodeid` = 10 and slssellingprice.`ltradingareaid` = price10.`ltradingareaid` order by slssellingprice.`ltradingareaid`, slsruntimeindex.`lpitnumber`; end

Suggested fix:
Find out why the server is stopping after the first execution.

Sinisa, Shane,
simplification of the test case will ease and speed up fixing a lot.
Which query makes the server fail?
Does the procedure with this query only crashes the server?
Is it possible to reduce the data set?
Thank you in advance for your help.

Update: you need no data in the tables to make the stored procedure in question crash.

A simple test case:
drop table if exists t1,t3,t2;
drop procedure if exists p8849;

CREATE TABLE t1 (
  lpitnumber int(11) default NULL,
  lrecordtype int(11) default NULL
);

CREATE TABLE t2 (
  lbsiid int(11) NOT NULL default '0',
  ltradingmodeid int(11) NOT NULL default '0',
  ltradingareaid int(11) NOT NULL default '0',
  csellingprice decimal(19,4) default NULL,
  PRIMARY KEY  (lbsiid,ltradingmodeid,ltradingareaid)
);

CREATE TABLE t3 (
  lbsiid int(11) NOT NULL default '0',
  ltradingareaid int(11) NOT NULL default '0',
  PRIMARY KEY  (lbsiid,ltradingareaid)
);

delimiter |;
CREATE PROCEDURE p8849()
begin
  insert into t3
  (
   t3.lbsiid,
   t3.ltradingareaid
  )
  select distinct t1.lpitnumber, t2.ltradingareaid
  from
    t2 join t1 on
      t1.lpitnumber = t2.lbsiid
      and t1.lrecordtype = 1
    left join t2 as price01 on
      price01.lbsiid = t2.lbsiid and
      price01.ltradingmodeid = 1 and
      t2.ltradingareaid = price01.ltradingareaid;
end|
delimiter ;|
call p8849();
call p8849();

On attempt to prepare and execute this statement the server does not crash.
The backtrace clearly shows that the crash happens due to damaged parse tree:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1085615024 (LWP 17048)]
0x0817fd35 in Item_cond::fix_fields (this=0x8859978, thd=0x8824540, 
    tables=0x8858438, ref=0x8858fd8) at item_cmpfunc.cc:2310
2310        while (item->type() == Item::COND_ITEM &&
(gdb) bt
#0  0x0817fd35 in Item_cond::fix_fields (this=0x8859978, thd=0x8824540, 
    tables=0x8858438, ref=0x8858fd8) at item_cmpfunc.cc:2310
#1  0x0821ce03 in setup_conds (thd=0x8824540, tables=0x8858438, 
    leaves=0x8858438, conds=0x8848708) at sql_base.cc:3291
#2  0x08244b13 in setup_without_group (thd=0x8824540, 
    ref_pointer_array=0x8846680, tables=0x8858438, leaves=0x8858438, 
    fields=@0x88518fc, all_fields=@0x8848684, conds=0x8848708, order=0x0, 
    group=0x0, hidden_group_fields=0x884866a) at sql_select.cc:272
#3  0x08224393 in JOIN::prepare (this=0x8847960, rref_pointer_array=0x88519b4, 
    tables_init=0x8858438, wild_num=0, conds_init=0x8847520, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
    select_lex_arg=0x8851890, unit_arg=0x88516bc) at sql_select.cc:321
#4  0x08229acc in mysql_select (thd=0x8824540, rref_pointer_array=0x88519b4, 
    tables=0x8858438, wild_num=0, fields=@0x88518fc, conds=0x8847520, 
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=3500427777, result=0x88475e0, unit=0x88516bc, 
    select_lex=0x8851890) at sql_select.cc:2026
#5  0x08224000 in handle_select (thd=0x8824540, lex=0x88516b0, 
    result=0x88475e0, setup_tables_done_option=1073741824) at sql_select.cc:227
#6  0x081f4c2e in mysql_execute_command (thd=0x8824540) at sql_parse.cc:3294
#7  0x083361a7 in sp_instr_stmt::exec_stmt (this=0x8859c78, thd=0x8824540, 
    lex=0x88516b0) at sp_head.cc:1198
#8  0x0833603f in sp_instr_stmt::execute (this=0x8859c78, thd=0x8824540, 
(...)

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/internals/22616

Subject: bk commit - 5.0 tree (konstantin:1.1786) BUG#8849

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.