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 2:52	Modified:	25 Feb 2004 4:17
Reporter:	Beat Vontobel (Silver Quality Contributor) (SCA)
Status:	Duplicate
Category:	Server: MyISAM	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Linux (Linux i386)
Assigned to:	Bugs System	Target Version:

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