FoundationDB/sql-layer — pull request #1219 by MMcM (2014-03-06 14:41:15+00:00)
Title: Handle uncorrelated semi join better

Transform

``SELECT * FROM t1 WHERE t1conds AND EXISTS (SELECT * FROM t2 WHERE t2conds)```

into

```SELECT t1.* FROM t1, (SELECT * FROM t2 WHERE t2conds LIMIT 1) sq WHERE t1conds```

This is similar to what happens with ```IN (SELECT DISTINCT ...)```. In both cases, the requirement is that the subquery have no references to the outer scope.

This avoids another case not handled well by join enumeration: adding extra edges does not compute their conflicts relative to non-reorderable joins. As with several other recent changes, the idea is to avoid attacking that now as potentially destabilizing.

Moreover, this transform allows a better plan to be generated in many cases. The subquery can be executed in an outer loop. If it returns its one row, then the rest is executed inside the loop. Again, this is similar to what happens with IN DISTINCT.