Query: Lift subquery with nested orderby

[anpete]

The following query:

from c1_Orders in context.Orders
join _c1 in
   (from c1 in
	(from c in context.Customers
		orderby c.CustomerID
		select c)
	.Take(2)
	from c2 in context.Customers
	select new { CustomerID = EF.Property<string>(c1, "CustomerID") })
   .Distinct()
on EF.Property<string>(c1_Orders, "CustomerID") equals _c1.CustomerID
orderby _c1.CustomerID
select c1_Orders

produces

@__p_0: 2

SELECT DISTINCT [t0].[CustomerID]
FROM (
    SELECT TOP(@__p_0) [c0].[CustomerID], [c0].[Address], [c0].[City], [c0].[CompanyName], [c0].[ContactName], [c0].[ContactTitle], [c0].[Country], [c0].[Fax], [c0].[Phone], [c0].[PostalCode], [c0].[Region]
    FROM [Customers] AS [c0]
    ORDER BY [c0].[CustomerID]
) AS [t0]
CROSS JOIN [Customers] AS [c20]
ORDER BY [t0].[CustomerID]

SELECT [c1_Orders].[OrderID], [c1_Orders].[CustomerID], [c1_Orders].[EmployeeID], [c1_Orders].[OrderDate]
FROM [Orders] AS [c1_Orders]

I think this is because of the orderby lifting happening in SE.PushDownSubquery. This query represents some Include queries that are worse with the new compiler

[anpete]

@smitpatel @maumar Any ideas?

Another thing I noticed is that 'join _c1 in..' is being marked for materialization.

[smitpatel]

What is the condition which is blocking flattening of join?

In Sql, join clause is not being materialized but inner Customer is which is weird.

[anpete]

@smitpatel I think it is the presence of OrderBy without skip/take. The OrderBy is lifted to the outer table expression during Subquery push-down.

[smitpatel]

If we remove the code which is lifting order by outside in PushDownSubquery, does this query work fine?

[anpete]

Yes! But others don't 😬

[smitpatel]

@anpete - Is this blocking issue for you? The side effects so far I have found

• Join ignores ordering for join clause. Causing ordering difference between l2o/sql queries
• Unnecessary pushdown happening when we don't lift joins.
• There is a blocking bug in ProjectionShaper.

[anpete]

@smitpatel Not blocking, just regresses certain include queries. Do we know how many other queries may be improved if we fix this?

[smitpatel]

I worked out to remove copying order by for all push down queries to only limit/offset/group join (include works because query model already lifts it).
There is only 1 failure left which is group join which requires ordering added by us and has inner ordering too. RowNumberPagingExpressionVisitor is having trouble dealing with it.

[smitpatel]

Also, query in first post fails at runtime due to bug in projection shaper. I did not see any other query being lifted anywhere though.