MySQL Bugs: #2656: select with join in stored procedure: erroneous result on 2nd call

Bug #2656	select with join in stored procedure: erroneous result on 2nd call
Submitted:	5 Feb 2004 12:31	Modified:	28 May 2004 14:48
Reporter:	Shari Rubin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Red Hat Linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:

In a stored procedure the select statement with a join ( see below ) produces an incorrect result on the 2nd call from the same MySQL session. The first call to the stored procedure w/in each session produces the expected result.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.0-alpha-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> call order_market();
+--------------+
| Market       |
+--------------+
| MarketID Two |
+--------------+
mysql> call order_market();
+--------------+
| Market       |
+--------------+
| MarketID One |
| MarketID Two |
| MarketID One |
| MarketID Two |
+--------------+
mysql> exit

How to repeat:
create table order_test ( 
OrderID  int not null,
MarketID int,
primary key (OrderID)
);

create table market ( 
MarketID int not null,
Market varchar(60),
Status char(1),
primary key (MarketID)
);
insert market (MarketID,Market,Status) values (1,"MarketID One","A");
insert market (MarketID,Market,Status) values (2,"MarketID Two","A");
insert order_test (OrderID,MarketID) values (1,1);
insert order_test (OrderID,MarketID) values (2,2);

delimiter |
create procedure order_market()
begin 
	select
		m.Market
	from    market m JOIN order_test o 
                ON o.MarketID != 1 and o.MarketID = m.MarketID;
	
end |

call order_market()|
call order_market()|

create procedure order_market2()
begin 
	select
	     	m.Market
	from 	
		market m, order_test o
	where       
			m.MarketID != 1
		and 	m.MarketID  = o.MarketID;
	
end |

call order_market2()|
call order_market2()|

Verified against current 5.0 BK tree.

I was also able to crash  mysqld by calling the procedure, flushing tables, then calling the procedure again:

0x8145793 handle_segfault + 647
0xffffe420 _end + -139636496
0x8180383 _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 279
0x8180383 _ZN4JOIN7prepareEPPP4ItemP13st_table_listjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit + 279
0x81838dc _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 698
0x81801be _Z13handle_selectP3THDP6st_lexP13select_result + 326
0x81575b6 _Z21mysql_execute_commandP3THD + 780
0x820c141 _ZN13sp_instr_stmt9exec_stmtEP3THDP6st_lex + 345
0x820bfd6 _ZN13sp_instr_stmt7executeEP3THDPj + 34
0x820aae7 _ZN7sp_head7executeEP3THD + 235
0x820b000 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 456
0x815c85d _Z21mysql_execute_commandP3THD + 21939
0x815dff0 _Z11mysql_parseP3THDPcj + 332
0x81561cf _Z16dispatch_command19enum_server_commandP3THDPcj + 1017
0x8155d83 _Z10do_commandP3THD + 155
0x815534b handle_one_connection + 351
0x40158762 _end + 935523378
0x4035ebea _end + 937646266

The latest theory is that this might be due to some known problems in
fix_fields (in 4.1 already). Further debugging after the next merge
with 4.1.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html