MySQL Bugs: #1727: EXPLAIN on subquery+union crashes server

Bug #1727	EXPLAIN on subquery+union crashes server
Submitted:	31 Oct 2003 15:28	Modified:	24 Nov 2003 3:00
Reporter:	Giovanni MC	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1 BK	OS:	Any (any)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
IF I EXECUTED THE QUERY:

select concat(areas.wp,"-",wpx) area, concat(apat," ", amat," ",nomb) nombre, id from

(select 77 per, wplaces.* from wplaces left join wplacespp on wplacespp.per=77 and wplacespp.wp=wplaces.wp where wplacespp.wp is null
UNION
select * from wplacespp where wplacespp.per=77) areas,

(select gente.* from gntlmanctrl, 
(select 77 per, gntlman.* from gntlman left join gntlmanpp on gntlmanpp.per=77 and gntlmanpp.id=gntlman.id where gntlmanpp.id is null
UNION
select * from gntlmanpp where per=77) gente
where gntlmanctrl.per=77 and  gntlmanctrl.id=gente.id) activos

where areas.wp=activos.wp order by area,nombre;

OCCURS THE NEXT THINGS:

1. "312 rows in set (0.03) sec" <===It's ok (as i hopped)
2. the results inside the GRID doesn't appear
3. After 4 ot 5 seconds the clasic message from windows appears "the applicatios has done an error and must be closed...", then I choosed "No close" and then MySQLCC continues working and the GRID appears with the correct records (but obiously with the mySQLD-NT service stopped)
4. THE MESSAGE "[chafa] ERROR 2013: Lost connection to MySQL server during query" appears.
5. I have observed that "Executing query" still in the status bar even when the results have been showed in the grid.

All datas in the grid are correct, aren't they?

The worst (and straing) is that the MySQLD-NT services is stopped.

I hope you check the mistake.

PD. I'm using mySQL 4.1.0 alpha-max-nt Bse i need to perform subqueries.

How to repeat:
(select 77 per, wplaces.* from wplaces left join wplacespp on wplacespp.per=77 and wplacespp.wp=wplaces.wp where wplacespp.wp is null
UNION
select * from wplacespp where wplacespp.per=77) areas,

(select gente.* from gntlmanctrl, 
(select 77 per, gntlman.* from gntlman left join gntlmanpp on gntlmanpp.per=77 and gntlmanpp.id=gntlman.id where gntlmanpp.id is null
UNION
select * from gntlmanpp where per=77) gente
where gntlmanctrl.per=77 and  gntlmanctrl.id=gente.id) activos

where areas.wp=activos.wp order by area,nombre;

Test case:
create table a ( a int );
explain select * from ( select * from a union select * from a ) a, 
(select * from a union select * from a ) b;

Stack trace:

Program received signal SIGSEGV, Segmentation fault.
0x808c8c3 in get_lock_data (thd=0xa99a018, table_ptr=0xaa2eef0, count=3,
    get_old_locks=false, write_lock_used=0xaa1ea58) at lock.cc:406
406           tables+=table_ptr[i]->file->lock_count();
(gdb) bt
#0  0x808c8c3 in get_lock_data (thd=0xa99a018, table_ptr=0xaa2eef0, count=3, 
    get_old_locks=false, write_lock_used=0xaa1ea58) at lock.cc:406
#1  0x808c118 in mysql_lock_tables (thd=0xa99a018, tables=0xaa2eef0, count=3)
    at lock.cc:93
#2  0x80b80c9 in lock_tables (thd=0xa99a018, tables=0xaa2ede8)
    at sql_base.cc:1557
#3  0x80b801d in open_and_lock_tables (thd=0xa99a018, tables=0xaa2ede8)
    at sql_base.cc:1516
#4  0x8143797 in mysql_derived (thd=0xa99a018, lex=0xa99a290, unit=0xaa248d0, 
    org_table_list=0xaa24de8) at sql_derived.cc:101
#5  0x809e3f3 in mysql_execute_command (thd=0xa99a018) at sql_parse.cc:1705
#6  0x80a3515 in mysql_parse (thd=0xa99a018, 
    inBuf=0xaa24028 "explain select * from ( select * from a union select * from a ) a, (select * from 
a union select * from a ) b", length=109)
    at sql_parse.cc:3768
#7  0x809d57b in dispatch_command (command=COM_QUERY, thd=0xa99a018, 
    packet=0xa9ea019 "explain select * from ( select * from a union select * from a ) a, (select * 
from a union select * from a ) b", packet_length=110)
    at sql_parse.cc:1295
#8  0x809cf83 in do_command (thd=0xa99a018) at sql_parse.cc:1129
#9  0x809c638 in handle_one_connection (arg=0xa99a018) at sql_parse.cc:899
#10 0x2852241f in _thread_start () from /usr/lib/libc_r.so.4
#11 0x0 in ?? ()

changeset 1.1624 (08 Now 2003)

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/