QueryRewrite: convert correlated SelectMany to JOINs #15081
Assignees
Labels
closed-fixed
The issue has been fixed and is/will be included in the release indicated by the issue milestone.
punted-for-3.0
type-enhancement
Milestone
Comments
maumar
changed the title
smitpatel
added
the
closed-fixed
smitpatel
added a commit
that referenced
this issue
Aug 10, 2019
SelectMany in linq look something like following (second clause is considered the collection selector) ```C# from c in cs from o in c.Orders select.... ``` Following are the translations of SelectMany Unrelated collection selector ```C# from c in cs from o in os ``` Generates CROSS JOIN Correlated collection selector with correlation being a predicate ```C# from c in cs from o in os.Where(o => o.CustomerID == c.CustomerID) ``` Such predicate can be lifted and used in generating a join. So this query generates JOIN. If collection selector ends with DefaultIfEmpty then it is LEFT JOIN. Correlated collection selector with correlation not a predicate ```C# from c in cs from o in os.Select(o => c.City) ``` Since we cannot generate a join predicate here, this translates to CROSS APPLY. If collection selector ends with DefaultIfEmpty then it is OUTER APPLY. Add support for Cross Apply Add Support for Outer Apply Convert Cross Apply to Inner Join when possible Convert Outer Apply to Left Join when possible Add translation for DefaultIfEmpty Add translation for both overloads of SelectMany Handle DefaultIfEmpty & SelectMany without collectionSelector in Navigation Expansion Ensure columns are in projection when generating join predicate from a correlated subquery Currently there are no tests for Cross/Outer Apply. Our earlier Cross Apply got converted to join. N+1 evaluation tests are disabled right now, which would generate Cross Apply. We never supported Outer Apply in past. Resolves #15711 Resolves #12567 Resolves #12572 Resolves #12872 Resolves #16330 Resolves #15081 Resolves #16989 Re-enable tests for #12449
smitpatel
added a commit
that referenced
this issue
Aug 10, 2019
SelectMany in linq look something like following (second clause is considered the collection selector) ```C# from c in cs from o in c.Orders select.... ``` Following are the translations of SelectMany Unrelated collection selector ```C# from c in cs from o in os ``` Generates CROSS JOIN Correlated collection selector with correlation being a predicate ```C# from c in cs from o in os.Where(o => o.CustomerID == c.CustomerID) ``` Such predicate can be lifted and used in generating a join. So this query generates JOIN. If collection selector ends with DefaultIfEmpty then it is LEFT JOIN. Correlated collection selector with correlation not a predicate ```C# from c in cs from o in os.Select(o => c.City) ``` Since we cannot generate a join predicate here, this translates to CROSS APPLY. If collection selector ends with DefaultIfEmpty then it is OUTER APPLY. - Add support for Cross Apply - Add Support for Outer Apply - Convert Cross Apply to Inner Join when possible - Convert Outer Apply to Left Join when possible - Add translation for DefaultIfEmpty - Add translation for both overloads of SelectMany - Handle DefaultIfEmpty & SelectMany without collectionSelector in Navigation Expansion - Ensure columns are in projection when generating join predicate from a correlated subquery Currently there are no tests for Cross/Outer Apply. Our earlier Cross Apply got converted to join. N+1 evaluation tests are disabled right now, which would generate Cross Apply. We never supported Outer Apply in past. Resolves #15711 Resolves #12567 Resolves #12572 Resolves #12872 Resolves #16330 Resolves #15081 Resolves #16989 Re-enable tests for #12449
smitpatel
added a commit
that referenced
this issue
Aug 12, 2019
SelectMany in linq look something like following (second clause is considered the collection selector) ```C# from c in cs from o in c.Orders select.... ``` Following are the translations of SelectMany Unrelated collection selector ```C# from c in cs from o in os ``` Generates CROSS JOIN Correlated collection selector with correlation being a predicate ```C# from c in cs from o in os.Where(o => o.CustomerID == c.CustomerID) ``` Such predicate can be lifted and used in generating a join. So this query generates JOIN. If collection selector ends with DefaultIfEmpty then it is LEFT JOIN. Correlated collection selector with correlation not a predicate ```C# from c in cs from o in os.Select(o => c.City) ``` Since we cannot generate a join predicate here, this translates to CROSS APPLY. If collection selector ends with DefaultIfEmpty then it is OUTER APPLY. - Add support for Cross Apply - Add Support for Outer Apply - Convert Cross Apply to Inner Join when possible - Convert Outer Apply to Left Join when possible - Add translation for DefaultIfEmpty - Add translation for both overloads of SelectMany - Handle DefaultIfEmpty & SelectMany without collectionSelector in Navigation Expansion - Ensure columns are in projection when generating join predicate from a correlated subquery Currently there are no tests for Cross/Outer Apply. Our earlier Cross Apply got converted to join. N+1 evaluation tests are disabled right now, which would generate Cross Apply. We never supported Outer Apply in past. Resolves #15711 Resolves #12567 Resolves #12572 Resolves #12872 Resolves #16330 Resolves #15081 Resolves #16989 Re-enable tests for #12449
smitpatel
added a commit
that referenced
this issue
Aug 13, 2019
SelectMany in linq look something like following (second clause is considered the collection selector) ```C# from c in cs from o in c.Orders select.... ``` Following are the translations of SelectMany Unrelated collection selector ```C# from c in cs from o in os ``` Generates CROSS JOIN Correlated collection selector with correlation being a predicate ```C# from c in cs from o in os.Where(o => o.CustomerID == c.CustomerID) ``` Such predicate can be lifted and used in generating a join. So this query generates JOIN. If collection selector ends with DefaultIfEmpty then it is LEFT JOIN. Correlated collection selector with correlation not a predicate ```C# from c in cs from o in os.Select(o => c.City) ``` Since we cannot generate a join predicate here, this translates to CROSS APPLY. If collection selector ends with DefaultIfEmpty then it is OUTER APPLY. - Add support for Cross Apply - Add Support for Outer Apply - Convert Cross Apply to Inner Join when possible - Convert Outer Apply to Left Join when possible - Add translation for DefaultIfEmpty - Add translation for both overloads of SelectMany - Handle DefaultIfEmpty & SelectMany without collectionSelector in Navigation Expansion - Ensure columns are in projection when generating join predicate from a correlated subquery Currently there are no tests for Cross/Outer Apply. Our earlier Cross Apply got converted to join. N+1 evaluation tests are disabled right now, which would generate Cross Apply. We never supported Outer Apply in past. Resolves #15711 Resolves #12567 Resolves #12572 Resolves #12872 Resolves #16330 Resolves #15081 Resolves #16989 Re-enable tests for #12449
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
query:
expected:
current:
The text was updated successfully, but these errors were encountered: