Query: subquery with order by but without Skip/Take gets the orderby removed

[maumar]

we should consider adding Skip(0) instead

query:

from g in gs
                    join w in ws.OrderBy(ww => ww.Name) on g.FullName equals w.OwnerFullName
                    select new { w.Name, g.FullName }

SELECT [t].[Name], [g].[FullName]
FROM [Gears] AS [g]
INNER JOIN (
    SELECT [w].[Id], [w].[AmmunitionType], [w].[IsAutomatic], [w].[Name], [w].[OwnerFullName], [w].[SynergyWithId]
    FROM [Weapons] AS [w]
) AS [t] ON [g].[FullName] = [t].[OwnerFullName]
WHERE [g].[Discriminator] IN (N'Gear', N'Officer')

we could do:

SELECT [t].[Name], [g].[FullName]
FROM [Gears] AS [g]
INNER JOIN (
    SELECT [ww].*
    FROM [Weapons] AS [ww]
    ORDER BY [ww].[Name]
    OFFSET 0 ROWS
) AS [t] ON [g].[FullName] = [t].[OwnerFullName]
WHERE [g].[Discriminator] IN (N'Officer', N'Gear')

[roji]

Isn't the point that rows coming out of the subquery aren't guaranteed to be sorted, even when the subquery contains OrderBy? Shouldn't we lift the OrderBy outside to the external query instead?

SELECT [t].[Name], [g].[FullName]
FROM [Gears] AS [g]
INNER JOIN (
    SELECT [ww].*
    FROM [Weapons] AS [ww]
) AS [t] ON [g].[FullName] = [t].[OwnerFullName]
WHERE [g].[Discriminator] IN (N'Officer', N'Gear')
ORDER BY [t].[Name]

[smitpatel]

Is there any point of lifting or applying Order by in such cases?
Adding few observations:

• If it is inner join (or even left join) with single row matching then is the result is expected?

  • In first example, even if we preserve order by and do skip 0, there is only 1 record going to match. So order by and skip both are not used.
  • In second example if we lift the order by to outer, is it expected that the gears coming out going to be sorted by name of matching weapons? Natural order would be dependent on what is ordering of Gears. Plus implementation of join may also fail to preserve such ordering.

• If it is collection join (when multiple rows match) - Possible options are Cross join & Left join

  • For left join case, we need to lift order by to outer level so when we generate collection results are sorted.
  • For cross join case, I was expecting similar but then OrderBy lifting specification #16226 says it is not possible to do so for cross join.

• For non join case (pushdown into subquery)

  • We can lift order by always to flow it to outer level except when inner has distinct.
  • If there is distinct operator inside then we cannot always lift if the ordering expression is not in projection. Further, after applying distinct, the ordering is not preserved in contract so trying to lift it in subset is low value.

• Clearing ordering after Distinct

  • We did this because contract of Distinct does not guarantee ordering.
  • While we had discussion that we should not do it, according to SQL Server, if there is distinct and order by contain term outside of projection then it is invalid SQL. So again we are in same boat of sometimes work, sometimes does not.

[roji]

If it is inner join (or even left join) with single row matching then is the result is expected?
In first example, even if we preserve order by and do skip 0, there is only 1 record going to match. So order by and skip both are not used.

I'm probably missing something, but can't there be more than one weapon for a given gear in the above example? Or does we call that situation a collection join?

However, I do agree that order of rows coming out of the subquery isn't guaranteed, so order+skip inside the subquery may be (or are) meaningless.

In second example if we lift the order by to outer, is it expected that the gears coming out going to be sorted by name of matching weapons? Natural order would be dependent on what is ordering of Gears. Plus implementation of join may also fail to preserve such ordering.

On second look you're right - it makes no sense to alter the order of gears because OrderBy was specified on joined weapons.

However, unless I'm mistaken the expected output here is to have gears come out in random/natural order, but for each gear's weapons to be sorted (i.e. by name). So for a projection of new { w.Name, g.FullName }, assuming there can be multiple weapons per gears I'd expect this to generate results where for a given g.FullName, w.Name would be sorted, no?

If so, this could be accomplished by lifting the OrderBy as an additional ordering on the outer:

SELECT [t].[Name], [g].[FullName]
FROM [Gears] AS [g]
INNER JOIN (
    SELECT [ww].*
    FROM [Weapons] AS [ww]
) AS [t] ON [g].[FullName] = [t].[OwnerFullName]
WHERE [g].[Discriminator] IN (N'Officer', N'Gear')
ORDER BY [g].Id, [t].[Name]

according to SQL Server, if there is distinct and order by contain term outside of projection then it is invalid SQL

FWIW same in PostgreSQL ([42P10] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list).

[smitpatel]

Or does we call that situation a collection join?

Yes, we call them collection join. Since collection join has different processing (traversing multiple rows to generate result).
The scenario you describe is correct. For collection joins, we want collection of weapons to be sorted for given gear. That has been enabled by #16532. So now we generate the SQL you posted.

[roji]

Thanks for the explanations @smitpatel.

[smitpatel]

There are test assertions disabled with this bug number. Assigning to @maumar to clean that up and close this issue.

[maumar]

resolved by e2ff7d8