FoundationDB/sql-layer — pull request #1152 by MMcM (2014-01-07 00:00:51+00:00)
Title: Problems associated with semi-joins via IN with a subquery that has a LIMIT

This branch fixes two reports on similar queries, but with rather different symptoms. Tests based on both failing cases are included.

The root cause is that while transforming the IN into a semi-join with a SubquerySource (a derived table, more or less) the LIMIT was left where it was and not where it would be in a regular subquery join. The difference is that the IN condition tacks onto the tip of the subquery. This moves the LIMIT to the tip of the subquery, with the Project, now generating result columns and not boolean conditions, beneath it.

As a result, the transforms in Map folding to add Projects to capture stuff in inner loops (materializing the join, more or less) works. This fixes the column not found problem. (Loop with it had expired.)

The too many updates is more subtle. Here the key is that matching an indexable condition depends on the subquery source having the Project right there. And it did, previously. Now it has the Limit, and so fails to match the conditions for indexing that condition. So the semi-join predicate does not get pushed down.

The generated plans are not all that great. If this pattern of IN (SELECT ... LIMIT 1) is actually common, there is an opportunity for an optimization based on LIMIT 1 being DISTINCT by implication. A distinct semi-join can be reordered: do the inside part first and then find the matching key from the distinct (in this case, 1 or 0) rows. If the common pattern is some other limit value or more varied subqueries, then investing in buffering the subquery result set and doing the IN against that may help. I wouldn't propose doing either of these before the first ORM release, though.