Query: SubQueryMemberPushDown optimization is too aggressive, which may lead to invalid sql or data corruption

[maumar]

Part of QM optimization is SubQueryMemberPushDownExpressionVisitor, which pushes down member from outside subquery into it. This way we get much simpler QMs and are able to translate more queries fully to sql. Problem is that we are doing it too aggresively, e.g. query:

from l1 in ctx.LevelOne
select (from l3 in ctx.LevelThree
		orderby l3.Id
		select l3).Distinct().Skip(1).FirstOrDefault().Name

with the following QM (before optimization):

from Level1 l1 in DbSet<Level1>
select 
    (from Level3 l3 in DbSet<Level3>
    order by [l3].Id asc
    select [l3])
    .Distinct()
    .Skip(1)
    .FirstOrDefault().Name

will get "optimized" to:

from Level1 l1 in DbSet<Level1>
select 
    (from Level3 l3 in DbSet<Level3>
    order by [l3].Id asc
    select [l3].Name)
    .Distinct()
    .Skip(1)
    .FirstOrDefault()

which is wrong. This produces invalid sql, but even if the sql would be valid, results would be incorrect - Distinct is now applied on the Name, rather than entity itself, which will produce unexpected results if the Name has duplicated values

We should not perform the pushdown if Distinct is present in the subquery ( other operators also? Intersect, Except, Union)

[ajcvickers]

@maumar Do you think you can get this merged today?

[maumar]

I hope so, hit a bunch of issues yesterday and today but I think I finally got it

[maumar]

fixed in 3f8baeb