Query: Flatten out TPC union tables in join

[smitpatel]

Test: Correlated_collections_different_collections_projected

SELECT [t].[Nickname], [t].[SquadId], [t0].[Name], [t0].[IsAutomatic], [t0].[Id], [t1].[Nickname], [t1].[Rank], [t1].[SquadId]
FROM (
    SELECT [o].[Nickname], [o].[SquadId], [o].[FullName]
    FROM [Officers] AS [o]
) AS [t]
LEFT JOIN (
    SELECT [w].[Name], [w].[IsAutomatic], [w].[Id], [w].[OwnerFullName]
    FROM [Weapons] AS [w]
    WHERE [w].[IsAutomatic] = CAST(1 AS bit)
) AS [t0] ON [t].[FullName] = [t0].[OwnerFullName]
LEFT JOIN (
-- HERE
    SELECT [t2].[Nickname], [t2].[Rank], [t2].[SquadId], [t2].[FullName], [t2].[LeaderNickname], [t2].[LeaderSquadId]
    FROM (
        SELECT [g0].[Nickname], [g0].[SquadId], [g0].[FullName], [g0].[LeaderNickname], [g0].[LeaderSquadId], [g0].[Rank]
        FROM [Gears] AS [g0]
        UNION ALL
        SELECT [o0].[Nickname], [o0].[SquadId], [o0].[FullName], [o0].[LeaderNickname], [o0].[LeaderSquadId], [o0].[Rank]
        FROM [Officers] AS [o0]
    ) AS [t2]
-- HERE
) AS [t1] ON [t].[Nickname] = [t1].[LeaderNickname] AND [t].[SquadId] = [t1].[LeaderSquadId]
ORDER BY [t].[FullName], [t].[Nickname], [t].[SquadId], [t0].[Id], [t1].[FullName], [t1].[Nickname]

Since there are no additional components we can lift it. Currently we fail to because the order of projection has changed. (this could have side-effects of reading wrong data, need to verify).
The order of projection changed due to client projection + collection projection.
First columns from user defined projection added then identifier for collection result added then ordering specified on inner for collection projection added then join columns added.