MySQL Bugs: #29796: Correlated subquery return zero rows when Group By included in the outer query

Bug #29796	Correlated subquery return zero rows when Group By included in the outer query
Submitted:	13 Jul 2007 20:39	Modified:	13 Aug 2007 21:49
Reporter:	Nigel Brownlow	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Windows (XP Professional 5.1.2600)
Assigned to:		CPU Architecture:	Any
Tags:	Aggregate Function, Correlated Subquery, GROUP BY

Description:
This may be related to bug 27352 but not sure.  My code executes a correlate sub-query with a SUM() function in the outer SELECT and associated 
GROUP BY statement.  The query incorrectly returns zero rows.  If I remove all fields from the SELECT except for the SUM and remove the GROUP BY, the query returns the correct results.

How to repeat:
The following query returns zero rows:
**************
SELECT c1.CabinClassId,sum(A.Bookings)
FROM CabinClassAssignment AS C1, BookingActivity AS A
WHERE A.ClassId = C1.ClassId AND
       C1.EffectiveFrom =
       (
       Select Max(C2.EffectiveFrom)
       From CabinClassAssignment as C2
       Where
             C1.ClassId  = C2.ClassId and
             C2.EffectiveFrom <= '2006-07-13'
       )
Group by c1.CabinClassId
;
************
Where as the next query works correctly
SELECT sum(A.Bookings)
FROM CabinClassAssignment AS C1, BookingActivity AS A
WHERE A.ClassId = C1.ClassId AND
       C1.EffectiveFrom =
       (
       Select Max(C2.EffectiveFrom)
       From CabinClassAssignment as C2
       Where
             C1.ClassId  = C2.ClassId and
             C2.EffectiveFrom <= '2006-07-13'
       )
The only difference is the removal of the GROUP BY field

The database includes the following two tables:

BookingActivity:
ClassId	  DaysLeft	Bookings
1	      -1	2
1	       0	1
1	       1	2
1	       2	1
1	       7	2
2	      -1	2
2	       0	3
2	       1	2
2	       2	3
2	       7	2

CabinClassAssignment:
ClassId	EffectiveFrom	CabinClassId
1	2003-9-1	1
2	2003-9-1	2
1	2007-1-1	2

Thank you for the bug report. Could you please provide a dump file
with create tables and insert data statements. You can attach a zip
file as private if you wish. Thanks in advance.

Create Statements for Tables needed to debug error

Attachment: Creates.sql (application/octet-stream, text), 1.30 KiB.

Insert Statements for data needed to debug error

Attachment: Inserts.sql (application/octet-stream, text), 1.66 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".