apache/spark — pull request #3249 by ravipesala (2014-11-13 18:24:05+00:00)
Title: [SPARK-4226][SQL] SparkSQL - Add support for subqueries in predicates('in' clause)

This PR supports subqueries in preicates 'in' clause. The queries will be transformed to the LeftSemi join as mentioned below.

Case 1 Uncorelated queries

-- original query
select C
from R1
where R1.A in (Select B from R2)
-- rewritten query
Select C
from R1 left semijoin R2 on R1.A = R2.B

Case 2 Corelated queries

-- original query
select C
from R1
where R1.A in (Select B from R2 where R1.X = R2.Y)
-- rewritten query
select C
from R1 left semi join
(select B, R2.Y as sq1_col0 from R2) sq1
on R1.X = sq1.sq1_col0 and R1.A = sq1.B

Restriction : Alias need to be used as we convert it into join queries.
Complete specification is available in https://issues.apache.org/jira/secure/attachment/12614003/SubQuerySpec.pdf