Query : client-side result operators may cause extensive client-side evaluation for queries with GroupJoins (and/or optional navigations) in a subquery

[maumar]

query:

from o in context.Orders
where (
   from od in context.OrderDetails
   where o.Customer.Country == od.Order.Customer.Country
   select od).Distinct().Count() > 0
select o;

produces the following query plan:

TRACKED: True
(QueryContext queryContext) => IEnumerable<Order> _Select(
    source: IEnumerable<TransparentIdentifier<Order, Customer>> _Where(
        source: IEnumerable<TransparentIdentifier<Order, Customer>> _ShapedQuery(
            queryContext: queryContext, 
            shaperCommandContext: SelectExpression: 
                SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate], [o.Customer].[CustomerID], [o.Customer].[Address], [o.Customer].[City], [o.Customer].[CompanyName], [o.Customer].[ContactName], [o.Customer].[ContactTitle], [o.Customer].[Country], [o.Customer].[Fax], [o.Customer].[Phone], [o.Customer].[PostalCode], [o.Customer].[Region]
                FROM [Orders] AS [o]
                LEFT JOIN [Customers] AS [o.Customer] ON [o].[CustomerID] = [o.Customer].[CustomerID]
                ORDER BY [o].[CustomerID]
            , 
            shaper: CompositeShaper`3
        )
        , 
        predicate: (TransparentIdentifier<Order, Customer> t1) => int Count(
            source: IEnumerable<ValueBuffer> Distinct(
                source: IEnumerable<ValueBuffer> _Select(
                    source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, IEnumerable<Customer>>> _Where(
                        source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, IEnumerable<Customer>>> _GroupJoin(
                            queryContext: (RelationalQueryContext) queryContext, 
                            source: IEnumerable<ValueBuffer> _Query(
                                queryContext: queryContext, 
                                shaperCommandContext: SelectExpression: 
                                    SELECT [od.Order.Customer1].[CustomerID], [od.Order.Customer1].[Address], [od.Order.Customer1].[City], [od.Order.Customer1].[CompanyName], [od.Order.Customer1].[ContactName], [od.Order.Customer1].[ContactTitle], [od.Order.Customer1].[Country], [od.Order.Customer1].[Fax], [od.Order.Customer1].[Phone], [od.Order.Customer1].[PostalCode], [od.Order.Customer1].[Region]
                                    FROM [Order Details] AS [od0]
                                    INNER JOIN [Orders] AS [od.Order0] ON [od0].[OrderID] = [od.Order0].[OrderID]
                                    LEFT JOIN [Customers] AS [od.Order.Customer1] ON [od.Order0].[CustomerID] = [od.Order.Customer1].[CustomerID]
                                    ORDER BY [od.Order0].[CustomerID]
                                , 
                                queryIndex: default(System.Nullable`1[System.Int32])
                            )
                            , 
                            outerShaper: CompositeShaper`3, 
                            innerShaper: BufferedOffsetEntityShaper<Customer>, 
                            innerKeySelector: (Customer od.Order.Customer) => string GetValueFromEntity(
                                clrPropertyGetter: ClrPropertyGetter`2, 
                                entity: od.Order.Customer
                            )
                            , 
                            resultSelector: (TransparentIdentifier<ValueBuffer, ValueBuffer> t0 | IEnumerable<Customer> od.Order.Customer_group) => TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, IEnumerable<Customer>> CreateTransparentIdentifier(
                                outer: t0, 
                                inner: od.Order.Customer_group
                            )
                            , 
                            outerGroupJoinInclude: default(Internal.GroupJoinInclude), 
                            innerGroupJoinInclude: default(Internal.GroupJoinInclude)
                        )
                        , 
                        predicate: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, IEnumerable<Customer>> t1) => (?t1.Inner.Country?) == (?od.Order.Customer.Country?)
                    )
                    , 
                    selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, ValueBuffer>, IEnumerable<Customer>> t1) => t1.Outer.Outer
                )
            )
        )
         > 0
    )
    , 
    selector: (TransparentIdentifier<Order, Customer> t1) => t1.Outer
)

However, very similar query (Distinct swapped for Take):

from o in context.Orders
where (
   from od in context.OrderDetails
   where o.Customer.Country == od.Order.Customer.Country
   select od).Take(10).Count() > 0
select o;

produces much more effective query plan:

TRACKED: True
(QueryContext queryContext) => IEnumerable<Order> _Select(
    source: IEnumerable<TransparentIdentifier<Order, Customer>> _ShapedQuery(
        queryContext: queryContext, 
        shaperCommandContext: SelectExpression: 
            SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate], [o.Customer].[CustomerID], [o.Customer].[Address], [o.Customer].[City], [o.Customer].[CompanyName], [o.Customer].[ContactName], [o.Customer].[ContactTitle], [o.Customer].[Country], [o.Customer].[Fax], [o.Customer].[Phone], [o.Customer].[PostalCode], [o.Customer].[Region]
            FROM [Orders] AS [o]
            LEFT JOIN [Customers] AS [o.Customer] ON [o].[CustomerID] = [o.Customer].[CustomerID]
            WHERE (
                SELECT COUNT(*)
                FROM (
                    SELECT TOP(10) [od.Order.Customer].[CustomerID], [od.Order.Customer].[Address], [od.Order.Customer].[City], [od.Order.Customer].[CompanyName], [od.Order.Customer].[ContactName], [od.Order.Customer].[ContactTitle], [od.Order.Customer].[Country], [od.Order.Customer].[Fax], [od.Order.Customer].[Phone], [od.Order.Customer].[PostalCode], [od.Order.Customer].[Region]
                    FROM [Order Details] AS [od]
                    INNER JOIN [Orders] AS [od.Order] ON [od].[OrderID] = [od.Order].[OrderID]
                    LEFT JOIN [Customers] AS [od.Order.Customer] ON [od.Order].[CustomerID] = [od.Order.Customer].[CustomerID]
                    WHERE ([o.Customer].[Country] = [od.Order.Customer].[Country]) OR ([o.Customer].[Country] IS NULL AND [od.Order.Customer].[Country] IS NULL)
                    ORDER BY [od.Order].[CustomerID]
                ) AS [t]
            ) > 0
            ORDER BY [o].[CustomerID]
        , 
        shaper: CompositeShaper`3
    )
    , 
    selector: (TransparentIdentifier<Order, Customer> t1) => t1.Outer
)

[maumar]

Problem here is that when we visit subquery model we optimize GroupJoins out (remove SelectMany-DefaultIfEmpty clauses that follow GroupJoin), however when we encounter client side (result) operator throw all that work away and do a second pass (on the already modified query model). Now, because the shape of query model is different, we fail to recognize the GroupJoin-SelectMany-DefaultIfEmpty pattern, which results in GroupJoin being performed on the client.

This issue was likely present before, but was exacerbated by #7348 (where we forced client evaluation on some result operators for GroupJoin scenarios)

[maumar]

Adding to 1.1.1 since we need to fix this before we can tackle #7290

[maumar]

@divega
Justification to include this in 1.1.1 release:

Impact: Medium. This is effectively the same issue as #7198, which is a breaking change introduced in 1.1.0 and was reported by a customer. Issue results is inefficient queries for simple cases and compilation errors for complex ones (no data corruption).

Risk: Low. Fix is local, we are undoing modifications to query model that we perform during query processing, if the same query model needs to be processed again (effectively “repairing” query model from its corrupted state)

[maumar]

fixed in c8d984e

[Eilon]

This patch bug is approved. Please use the normal code review process w/ a PR and make sure the fix is in the correct branch, then close the bug and mark it as done.

[anpete]

✅ Verified