MySQL Bugs: #1645: MySQL server crashes when using distinct with subquery.

Bug #1645	MySQL server crashes when using distinct with subquery.
Submitted:	24 Oct 2003 8:28	Modified:	2 Nov 2003 7:44
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0-alpha-max-nt-log	OS:	Windows (Windows 2000)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
When executing a query where the first query uses DISTINCT and the subquery is joined using '=' the connection to the database is lost and the mysql server crashes.  This happens in both 4.1.0-alpha-max-nt-log & 4.1.1-alpha-max-nt-log mysql server versions.

If you remove DISTINCT from the first query, the query executes fine.  Also if you replace the join on the subquery with 'IN', the query executes fine.

How to repeat:
#TABLE SETUP
DROP TABLE IF EXISTS T20;
DROP TABLE IF EXISTS T21;
CREATE TABLE T20(
COLA FLOAT NOT NULL,
COLB FLOAT NOT NULL,
COLC VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (COLA, COLB));

CREATE TABLE T21(
COLA FLOAT NOT NULL,
COLB FLOAT NOT NULL,
COLC CHAR(1) NOT NULL,
PRIMARY KEY (COLA));

INSERT INTO T20 VALUES (1,1,'1A3240'), (1,2,'4W2365');
INSERT INTO T21 VALUES (100, 200, 'C');

#QUERY
SELECT DISTINCT COLC FROM T20 WHERE COLA = (SELECT 
COLA FROM T21 WHERE COLB = 200 AND COLC ='C' LIMIT 1)

#ACTUAL RESULTS
ERROR 2013 (HY000): Lost connection to MySQL server during query

#EXPECTED RESULTS
Empty set (0.27 sec)

Verified. Backtrace:

#0  0x8076586 in Item_subselect::fix_fields (this=0xaa24880, thd_param=0x0, tables=0x0, 
ref=0xaa3ec50) at item_subselect.cc:84
#1  0x805af9a in Item_func::fix_fields (this=0xaa3ec10, thd=0x0, tables=0x0, ref=0xaa3ed18) at 
item_func.cc:209
#2  0x806612b in Item_cond::fix_fields (this=0xaa3eb90, thd=0x0, tables=0x0, ref=0xaa1ea68) 
at item_cmpfunc.cc:1632
#3  0x80d0c3f in add_ref_to_table_cond (thd=0xa99a018, join_tab=0xaa3e840) at 
sql_select.cc:8410
#4  0x80bef1d in JOIN::optimize (this=0xaa24a90) at sql_select.cc:867
#5  0x80c10a9 in mysql_select (thd=0xa99a018, rref_pointer_array=0xa99a460, 
tables=0xaa24160, wild_num=0, fields=@0xa99a3c8, conds=0xaa24918, og_num=0, 
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=17339393, 
result=0xaa24a80, unit=0xa99a29c, select_lex=0xa99a364, tables_and_fields_initied=false) at 
sql_select.cc:1545
#6  0x80bd763 in handle_select (thd=0xa99a018, lex=0xa99a290, result=0xaa24a80) at 
sql_select.cc:191
#7  0x809e7f0 in mysql_execute_command (thd=0xa99a018) at sql_parse.cc:1802
#8  0x80a3515 in mysql_parse (thd=0xa99a018, inBuf=0xaa24028 "SELECT DISTINCT COLC FROM 
T20 WHERE COLA = (SELECT \nCOLA FROM T21 WHERE COLB = 200 AND COLC ='C' LIMIT 1)", 
length=105) at sql_parse.cc:3768
#9  0x809d57b in dispatch_command (command=COM_QUERY, thd=0xa99a018, 
packet=0xa9ea019 "SELECT DISTINCT COLC FROM T20 WHERE COLA = (SELECT \nCOLA FROM T21 
WHERE COLB = 200 AND COLC ='C' LIMIT 1)", packet_length=106) at sql_parse.cc:1295
#10 0x809cf83 in do_command (thd=0xa99a018) at sql_parse.cc:1129
#11 0x809c638 in handle_one_connection (arg=0xa99a018) at sql_parse.cc:899
#12 0x2852241f in _thread_start () from /usr/lib/libc_r.so.4
#13 0x0 in ?? ()

ChangeSet 
  1.1591 03/10/26 11:03:03 bell@sanja.is.com.ua +6 -0 
  fixed BUG#1645 
  all calls of fix_fields() are inspected

Thank You for bugreport. 
bugfix for this bug is now pushed in internal source tree repository and will 
be present in next version 4.1 release.