Left Semi Join should be pushed under Concat

[terrajobst]

Ported from CodePlex

This query is very slow:

SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT MAX(OrderId)
            FROM Orders o_1
            HAVING MAX(OrderId) = o_0.OrderID
            UNION ALL
            SELECT  AVG(OrderId)
            FROM    [Order Details] od_0
            HAVING  YEAR(OrderDate) > 1998
            AND     o_0.OrderID >= AVG(OrderId)
        )

The reason for this is that it the LSJ should be pushed under the CONCAT node if the LSJ has outer references. This could help to decorrelate the query using IJ or LSJ. Therefore the result should be something like this:

SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT MAX(OrderId)
            FROM Orders o_1
            HAVING MAX(OrderId) = o_0.OrderID
        )
UNION
SELECT  *
FROM    Orders o_0
WHERE   EXISTS (
            SELECT  AVG(OrderId)
            FROM    [Order Details] od_0
            HAVING  YEAR(OrderDate) > 1998
            AND     o_0.OrderID >= AVG(OrderId)
)

Please note that the CONCAT node is then transformed in UNION (meaning that a DISTINCT SORT should be inserted).

[dallmair]

With NQuery v.Next PR #2 the query at least produces the correct result.