GROUP BY with LEFT JOIN problem

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
dilbert
Posts: 68
Joined: Tue 28 Apr 2009 10:11

GROUP BY with LEFT JOIN problem

Post by 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.

Shalex
Devart Team
Posts: 8072
Joined: Thu 14 Aug 2008 12:44

Post by 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: 8072
Joined: Thu 14 Aug 2008 12:44

Post by 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: 8072
Joined: Thu 14 Aug 2008 12:44

Post by 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 .

Post Reply