GROUP BY with LEFT JOIN problem

GROUP BY with LEFT JOIN problem

Postby dilbert » Tue 01 Sep 2009 11:21

Hello, I have a question about GROUP BY function across multiple tables joined by LEFT JOIN clause.

Let's see the following simple example:

Code: Select all
from t1 in DB.Parent
from t2 in DB.Child.Where(t2 => t2.Id == t1.Id).DefaultIfEmpty()
group t1 by new {t1.Id, t2.Date} into grouped
select new { grouped.Key.Id, grouped.Key.Date, Last_date = grouped.Max(c => c.Date_inserted) };


is translated into this SQL code:

Code: Select all
SELECT t1.id, t1.date, (
    SELECT MAX(t4.date_inserted) AS C1
    FROM db.parent t4
    LEFT OUTER JOIN db.child t5 ON (t5.id = t4.id)
    WHERE (t1.id = t4.id) AND (t1.date = t5.date)
    ) AS C1
FROM (
    SELECT t2.id, t3.date
    FROM db.table1 t2
    LEFT OUTER JOIN db.table2 t3 ON (t3.id = t2.id)
    GROUP BY t2.id, t3.date
    ) t1


I don't understand, why the nested query is required. I suppose the correct coresponding query should be something like this:
Code: Select all
SELECT t2.id, t3.date, MAX(t2.date_inserted)
    FROM db.table1 t2
    LEFT OUTER JOIN db.table2 t3 ON (t3.id = t2.id)
    GROUP BY t2.id, t3.date


The results of these two queries are affected by (t1.date = t5.date) condition in the first (nested) query.
If the set of records in child table is empty, this condition is always FALSE and therefore LEFT JOIN has the same effect as INNER JOIN.

So, for example, if the following result set is returned by the last query:
Code: Select all
id       date                   MAX( t2 . date_inserted )
7        2009-08-31 19:13:28    2009-08-09 16:18:29
14       NULL                   2009-08-02 17:10:05


The first query, translated from linq, would return just the first row.


Thank you for attendance. I hope, it isn't too confused.
Last edited by dilbert on Fri 04 Sep 2009 10:23, edited 1 time in total.
dilbert
 
Posts: 68
Joined: Tue 28 Apr 2009 10:11

Postby Shalex » Fri 04 Sep 2009 08:14

Thank you for the bug report. We are resolving this problem at the moment. The fix will be available in the future builds.
Shalex
Devart Team
 
Posts: 7841
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 04 Sep 2009 15:05

The problem is fixed. Look forward to the next build. I will post here when it is available.
Shalex
Devart Team
 
Posts: 7841
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Fri 25 Sep 2009 07:54

The new build of dotConnect for MySQL 5.40.44 is available for download now.
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=15925 .
Shalex
Devart Team
 
Posts: 7841
Joined: Thu 14 Aug 2008 12:44


Return to LinqConnect (LINQ to SQL support)