FoundationDB/sql-layer — pull request #1241 by MMcM (2014-03-21 04:46:38+00:00)
Title: Do not turn correlated subqueries into derived tables with IN

An IN (more generally ANY) condition can be turned into a semi-join in two possible ways:

* by adding the IN condition as a semi-join condition on a join with the join tree inside the subquery.
* by just turning the subquery into a joinable, keeping its joins and conditions inside.

The second makes join enumeration less complex and so is to be preferred in simple cases.

Prior to this branch, it was preferred even when the outer table(s) was used inside the subquery. This leads to trouble, since inside the derived table the join enumerator does not recognize the outer table references as inducing graph edges. Things are particularly bad when the IN condition's LHS does not involve those outer tables. Because then all the possible edges are absent and the query falls into the problematic incomplete graph case.

This branch does not introduce any new transformations. The caller was already prepared to take the alternative (if possible -- leaving the subquery condition as a subquery expression is always an option). It just adds another case in which that happens.

Referring to the new example, before this branch, the semi-join would be ```child.pid == ANY[0]```, with the RHS being a SubquerySource with the ```child.id != orders.oid``` inside in a Select.