5

Closed

Inner join instead of outer

description

Inner join generated where an outer join is expected.

This happens when a join is made from T1 to T2 and then to T3 if relation T1-T2 is optional and T2-T3 is mandatory. Concrete example below.

Details:

Two tables (here T1=T2=Post, T3=Blog):

Blog
  • Id (PK)
  • Name
Post
  • Id (PK)
  • BlogId (FK to Blog.Id), Not NULL <== mandatory relation
  • ParentPostId (FK to Post.Id), NULL <== optional relation
  • Text
LINQ Query:
from p in db.Posts
where p.Id == 1
select new {p.Id, p.Text, p.ParentPost.Blog.Name}
EF5 SQL Statement (correct):
SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Text] AS [Text], 
[Extent3].[Name] AS [Name]
FROM    (SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[ParentPostId] AS [ParentPostId], [Extent1].[Text] AS [Text]
    FROM [dbo].[Post] AS [Extent1]
    WHERE 1 = [Extent1].[Id] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Post] AS [Extent2] ON [Limit1].[ParentPostId] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Blog] AS [Extent3] ON [Extent2].[BlogId] = [Extent3].[Id]
EF6 SQL Statement (wrong: inner join):
SELECT 
[Limit1].[Id] AS [Id], 
[Limit1].[Text] AS [Text], 
[Extent3].[Name] AS [Name]
FROM    (SELECT TOP (2) [Extent1].[Id] AS [Id], [Extent1].[ParentPostId] AS [ParentPostId], [Extent1].[Text] AS [Text]
    FROM [dbo].[Post] AS [Extent1]
    WHERE 1 = [Extent1].[Id] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[Post] AS [Extent2] ON [Limit1].[ParentPostId] = [Extent2].[Id]
INNER JOIN [dbo].[Blog] AS [Extent3] ON [Extent2].[BlogId] = [Extent3].[Id]
The inner join is generated because Post.Blog is mandatory (BlogId is not null) but I expect an outer join because Post.ParentPost is optional (ParentPostId accepts null). As a result in EF6 no record is returned while EF5 returns one as expected.
Closed Mar 28, 2013 at 10:28 PM by lukew
Verified fix and regression test in place

comments

moozzyk wrote Mar 20, 2013 at 2:28 AM

This: http://entityframework.codeplex.com/discussions/436875 can be another manifestation of the same issue.

ovidiu_stan wrote Mar 20, 2013 at 9:24 AM

Yep, it looks like it's the same issue.

emilcicos wrote Mar 20, 2013 at 9:59 PM

This is a regression introduced with the fix for #655, in the join elimination logic, and should be working now as expected.

ovidiu_stan wrote Mar 20, 2013 at 10:27 PM

You mean it was fixed since yesterday's nightly build ?

ovidiu_stan wrote Mar 21, 2013 at 7:28 AM

Just checked: the issue is reproducing in the latest build, alpha3-20320

moozzyk wrote Mar 21, 2013 at 2:52 PM

It was checked in yesterday so I think it will show up in 21 and not 20

ovidiu_stan wrote Mar 22, 2013 at 9:19 AM

Great, it's fixed in 21. Thanks guys!

vforteli wrote Mar 22, 2013 at 1:05 PM

Great, thanks!