Unneeded subquery and projection when using ordering without limit/offset in set operations

[roji]

LINQ:

_ = ctx.Blogs
    .Select(b => b.Id)
    .Union(ctx.Blogs.OrderBy(b => b.Name).Select(b => b.Id))
    .ToList();

SQL:

SELECT [b].[Id]
FROM [Blogs] AS [b]
UNION
SELECT [t0].[Id]
FROM (
  SELECT [b0].[Id], [b0].[Name]
  FROM [Blogs] AS [b0]
) AS [t0]

This happens because when applying a set operation, we pushdown to a subquery when there are orderings, but those orderings are subsequently removed in the subquery because subquery ordering isn't preserved outside.

Note that set operations generally don't maintain ordering - except for Concat. However, even for Concat, databases don't generally allow applying ordering to set operation operands (which is very different from applying ordering to the result of the set operation). We may need to research more into this if we decide it's important.

Also, when we do push down (e.g. because there's limit/offset), we currently project the ordering column(s) out of the subquery, since usually the the outer query orders by it (ordering lifting). But in the case of set operations the outer ordering is immediately cleared - again since set operations don't preserve ordering - and we're just left with the useless projection.