MySQL Bugs: #9792: View with left / right outer join hangs client

Bug #9792	View with left / right outer join hangs client
Submitted:	10 Apr 2005 15:15	Modified:	16 May 2005 0:21
Reporter:	Tim Graves	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3/5.0.5 BK source	OS:	Windows (Windows XP)
Assigned to:	Oleksandr Byelkin	CPU Architecture:	Any

Description:
If I create a view containing a left outer join (see below) and then select from this view the clients hang. The DBVisualizer (with MySLQ jdbc 3.1.6) and the mysql query browser hang immediately, the mysql command line client hangs on the subsequent attempt to access the database (the DBVisualiser client does correctly report the number of rows that are returned but does not display the resulting rows.) I suspect that the GUI clients actually get the data back but attempt to do a subsequent database access before displaying them and hang on the second access.

Once the client has hung the database server itself cannot be sucessfully stopped using the mysql system tray monitor or the mysql administrator tool. - the mysql-debug.exe process continues running.

How to repeat:
Note that the example below is for a LEFT OUTER JOIN but this also fails on a RIGHT OUTER JOIN  with the tables reversed. 

DROP VIEW IF EXISTS requirementsWithNoCtqContribution ;
CREATE VIEW requirementsWithNoCtqContribution AS
SELECT r.name AS name, r.productcode AS productCode, r.majorcode AS majorCode, r.minorcode AS minorCode, 
	r.requirementCode AS requirementCode, r.importanceAdjustment AS importanceAdjustment, 
	r.description AS description, r.justification AS justification, r.measurement AS measurement, 
	r.requirementType AS requirementType 
FROM requirement AS r 
	LEFT OUTER JOIN ctqcontribution AS c ON r.productCode=c.productCode AND r.majorCode=c.majorCode 
		AND r.minorCode=c.minorCode AND r.requirementCode=c.requirementCode
WHERE c.productcode IS NULL ;

If I run the SELECT statement by itself everything behaves correctly. If I change the LEFT OUTER JOIN to a INNER JOIN in the View it all works. it is only the combination of the LEFT/RIGHT OUTER JOIN in a view that seems to cause the problem.

See the database schema and content I have supplied for bug ID 9593 for a database that can be used to reproduce this bug

Suggested fix:
Sorry, unknown

I tested with latest BK source, it sent the result:

*************************** 276. row ***************************
                name: Continuous market customer involvement
         productCode: M801
           majorCode: srv
           minorCode: q9
     requirementCode: 22
importanceAdjustment: 1
         description: You should be involving your customer at all stages. it's li
 time you look up you may be in a very different place from where you expected to
r involvement.
       justification:
         measurement:
     requirementType: Comment
276 rows in set (0.05 sec)

but it crashes the server at:

item.h
--543--
 void delete_self()
  {
    cleanup();
    delete this;
^^^^^^^^^^^^^^^^
  }
};
^
call stack:

 	a5a5a5a5()	
>	mysqld-debug.exe!Item::delete_self()  Line 546	C++
 	mysqld-debug.exe!free_items(Item * item=0x0300bfb8)  Line 1261	C++
 	mysqld-debug.exe!THD::cleanup_after_query()  Line 525 + 0xc	C++
 	mysqld-debug.exe!mysql_parse(THD * thd=0x02fe0980, char * inBuf=0x02fe8a28, unsigned int length=47)  Line 5171	C++
 	mysqld-debug.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x02fe0980, char * packet=0x02ffe041, unsigned int packet_length=48)  Line 1647 + 0x1d	C++
 	mysqld-debug.exe!do_command(THD * thd=0x02fe0980)  Line 1453 + 0x31	C++
 	mysqld-debug.exe!handle_one_connection(void * arg=0x02fe0980)  Line 1110 + 0x9	C++
 	mysqld-debug.exe!pthread_start(void * param=0x02ff5328)  Line 63 + 0x7	C
 	mysqld-debug.exe!_threadstart(void * ptd=0x02fe4600)  Line 173 + 0xd	C
 	kernel32.dll!7c80b50b() 	
 	kernel32.dll!7c8399f3() 	

Thank you for the bug report.

Thank you for bugreport!
This bug is already fixed in current bk repository.