MySQL Bugs: #2901: Server crash on use uf COUNT(*) in subquery

Bug #2901	Server crash on use uf COUNT(*) in subquery
Submitted:	20 Feb 2004 1:52	Modified:	25 Feb 2004 3:17
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Linux (Linux i386)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL server crashes on (multiple) use of COUNT(*) in SELECT expressions in a LEFT JOINed subquery 
(unnamed view). Please check out attached crash.sql.

What I want to achieve: Some columns in a table should be cumulated (SUM()) over multiple rows, for 
others there should be returned the value of just one row. I use an unnamed view to prepare the 
cumulated values and JOIN them with the original table of single values. Cumulating works, if I just use 
SUM(column). But I actually want to extrapolate a cumulated value, too, if some values are missing 
(NULL). I therefore use the following expression:

SUM(c) / COUNT(c) * COUNT(*)

This takes the average for all existing (non-NULL values) and multiplies it with the number of all rows. 
This expression works fine in a stand-alone query and gives the expected result. It works fine in a 
JOINed subquery, too, if it shows up only once. But the server crashes if I try to cumulate more than one 
column using this expression in a subquery. (Details in attached crash.sql)

How to repeat:
Please use attached crash.sql test case file. I tested it on the latest binary distribution of 4.1.1-alpha-
standard-log on a lot of different Linux Servers running different Linux versions and it always gave the 
same result (crash).

I'm sorry I don't have a trace attached. But it took me already pretty long time to figure out the stripped 
down test case, because the real-world example was a _really_ big query. I was able to reproduce the 
crash on all of the different Linux Distributions I have running, so it's probably easy to reproduce the 
bug for you, too, by just running crash.sql (possibly on other operating systems, too). If it shouldn't be 
possible, please don't hesitate to contact me by mail or phone for further information.

Suggested fix:
none

I only tried the test case on standard MyISAM tables.

Test case to crash server using multiple COUNT(*) in subquery

Attachment: crash.sql (application/octet-stream, text), 963 bytes.

Verified against 4.1.2-alpha-log with the supplied test case.  Thank you for the report.

Thank you for bugreport. 
 
This bug is same as Bug #2421. You can use 
http://bugs.mysql.com/bug.php?id=2421 to track progress on this bug