Incorrect result in certain situations with multiple joins with difference partition schemes

[rongrong]

This is a bug introduced by #12013. The result would be wrong if the following situation happens:

• The query uses COALESCE(joinKey) on top of FULL OUTER JOIN with equi-join.
• The children of the FullJoin node uses a different hash function to compute the partition from the join keys. For example, hash is computed on (a, constant) and join key is just a.
• There is another JOIN with the result of FULL OUTER JOIN using equi-join on only the coalesced keys of the FULL OUTER JOIN.

In such situation, the newly introduced optimization would assume that the result of the FULL OUTER JOIN is already partitioned on COALESCE(a) thus there's no need for another shuffle before the next join. However, because the hash function is calculated on (a, constant), even if the data is "partitioned on a" it would be on a different node as a hash function computed with just a. Thus a shuffle would still be needed to produce correct result.

[rongrong]

Theoretically it would also produce incorrect results if the input of the FULL OUTER JOIN is partitioned on more columns than just the join keys, for example, input is partitioned on (a, b) but only join on a. Though practically I don't know what query shape would make that happen.

[wenleix]

@rongrong : Might be easier to understand the condition combination with an example query ? :) (e.g. TPCH)

[rongrong]

Here's an example that can produce the wrong plan. It's harder to come up with a query that can produce meaningful & wrong results though:

SELECT * 
FROM customer t3
JOIN (
    SELECT coalesce(c1, c2) c
    FROM (
        SELECT custkey c1, name FROM customer WHERE name = 'a') t1 
    FULL OUTER JOIN (
        SELECT custkey c2, name FROM customer WHERE name = 'b' GROUP BY 1, 2) t2
    ON t1.c1 = t2.c2) t
ON t3.custkey = t.c;

[tooptoop4]

@rongrong did #12946 fix this issue? if so, does .226 have the fix?

[rongrong]

@tooptoop4 Yes #12946 reintroduced FULL OUTER JOIN + COALESCE optimization and it should not have this bug. It will be released in 0.227.