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:Microsoft Windows (XP Professional 5.1.2600)
Assigned to: CPU Architecture:Any
Tags: Aggregate Function, Correlated Subquery, GROUP BY

[13 Jul 2007 20:39] Nigel Brownlow
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
[13 Jul 2007 21:49] MySQL Verification Team
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.
[13 Jul 2007 22:45] Nigel Brownlow
Create Statements for Tables needed to debug error

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

[13 Jul 2007 22:45] Nigel Brownlow
Insert Statements for data needed to debug error

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

[13 Aug 2007 23:00] Bugs System
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".