phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From maryann...@apache.org
Subject [2/3] PHOENIX-167 Support semi/anti-joins
Date Mon, 06 Oct 2014 17:56:14 GMT
http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
index 075e420..4e882d1 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/JoinCompiler.java
@@ -51,6 +51,7 @@ import org.apache.phoenix.parse.ComparisonParseNode;
 import org.apache.phoenix.parse.DerivedTableNode;
 import org.apache.phoenix.parse.EqualParseNode;
 import org.apache.phoenix.parse.HintNode;
+import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
 import org.apache.phoenix.parse.HintNode.Hint;
 import org.apache.phoenix.parse.JoinTableNode;
 import org.apache.phoenix.parse.JoinTableNode.JoinType;
@@ -59,7 +60,6 @@ import org.apache.phoenix.parse.OrderByNode;
 import org.apache.phoenix.parse.ParseNode;
 import org.apache.phoenix.parse.ParseNodeFactory;
 import org.apache.phoenix.parse.SelectStatement;
-import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
 import org.apache.phoenix.parse.TableName;
 import org.apache.phoenix.parse.TableNode;
 import org.apache.phoenix.parse.TableNodeVisitor;
@@ -82,6 +82,7 @@ import org.apache.phoenix.util.SchemaUtil;
 import com.google.common.collect.ArrayListMultimap;
 import com.google.common.collect.ListMultimap;
 import com.google.common.collect.Lists;
+import com.google.common.collect.Sets;
 
 
 public class JoinCompiler {
@@ -122,9 +123,9 @@ public class JoinCompiler {
             joinTable.addFilter(select.getWhere());
         }
         
-        ColumnParseNodeVisitor generalRefVisitor = new ColumnParseNodeVisitor(resolver);
-        ColumnParseNodeVisitor joinLocalRefVisitor = new ColumnParseNodeVisitor(resolver);
-        ColumnParseNodeVisitor prefilterRefVisitor = new ColumnParseNodeVisitor(resolver);
+        ColumnRefParseNodeVisitor generalRefVisitor = new ColumnRefParseNodeVisitor(resolver);
+        ColumnRefParseNodeVisitor joinLocalRefVisitor = new ColumnRefParseNodeVisitor(resolver);
+        ColumnRefParseNodeVisitor prefilterRefVisitor = new ColumnRefParseNodeVisitor(resolver);
         
         joinTable.pushDownColumnRefVisitors(generalRefVisitor, joinLocalRefVisitor, prefilterRefVisitor);
 
@@ -256,7 +257,7 @@ public class JoinCompiler {
             this.prefilterAcceptedTables = new ArrayList<JoinTable>();
             for (int i = lastRightJoinIndex == -1 ? 0 : lastRightJoinIndex; i < joinSpecs.size(); i++) {
                 JoinSpec joinSpec = joinSpecs.get(i);
-                if (joinSpec.getType() != JoinType.Left) {
+                if (joinSpec.getType() != JoinType.Left && joinSpec.getType() != JoinType.Anti) {
                     prefilterAcceptedTables.add(joinSpec.getJoinTable());
                 }
             }
@@ -306,9 +307,9 @@ public class JoinCompiler {
             filter.accept(visitor);
         }
         
-        public void pushDownColumnRefVisitors(ColumnParseNodeVisitor generalRefVisitor, 
-                ColumnParseNodeVisitor joinLocalRefVisitor, 
-                ColumnParseNodeVisitor prefilterRefVisitor) throws SQLException {
+        public void pushDownColumnRefVisitors(ColumnRefParseNodeVisitor generalRefVisitor, 
+                ColumnRefParseNodeVisitor joinLocalRefVisitor, 
+                ColumnRefParseNodeVisitor prefilterRefVisitor) throws SQLException {
             for (ParseNode node : table.getPreFilters()) {
                 node.accept(prefilterRefVisitor);
             }
@@ -359,14 +360,18 @@ public class JoinCompiler {
             if (!table.isFlat() ||
                     (!useStarJoin 
                             && count > 1 
-                            && joinSpecs.get(count - 1).getType() != JoinType.Left))
+                            && joinSpecs.get(count - 1).getType() != JoinType.Left
+                            && joinSpecs.get(count - 1).getType() != JoinType.Semi
+                            && joinSpecs.get(count - 1).getType() != JoinType.Anti))
                 return null;
 
             boolean[] vector = new boolean[count];
             for (int i = 0; i < count; i++) {
                 JoinSpec joinSpec = joinSpecs.get(i);
                 if (joinSpec.getType() != JoinType.Left 
-                        && joinSpec.getType() != JoinType.Inner)
+                        && joinSpec.getType() != JoinType.Inner
+                        && joinSpec.getType() != JoinType.Semi
+                        && joinSpec.getType() != JoinType.Anti)
                     return null;
                 vector[i] = true;
                 Iterator<TableRef> iter = joinSpec.getDependencies().iterator();
@@ -787,22 +792,22 @@ public class JoinCompiler {
     }
     
     private static class WhereNodeVisitor extends BooleanParseNodeVisitor<Void> {
-        private ColumnResolver resolver;
         private Table table;
         private List<ParseNode> postFilters;
         private List<TableRef> selfTableRefs;
         private boolean hasRightJoin;
         private List<JoinTable> prefilterAcceptedTables;
+        ColumnRefParseNodeVisitor columnRefVisitor;
         
         public WhereNodeVisitor(ColumnResolver resolver, Table table,
                 List<ParseNode> postFilters, List<TableRef> selfTableRefs, boolean hasRightJoin, 
                 List<JoinTable> prefilterAcceptedTables) {
-            this.resolver = resolver;
             this.table = table;
             this.postFilters = postFilters;
             this.selfTableRefs = selfTableRefs;
             this.hasRightJoin = hasRightJoin;
             this.prefilterAcceptedTables = prefilterAcceptedTables;
+            this.columnRefVisitor = new ColumnRefParseNodeVisitor(resolver);
         }
         
         @Override
@@ -813,9 +818,9 @@ public class JoinCompiler {
         @Override
         protected Void leaveBooleanNode(ParseNode node,
                 List<Void> l) throws SQLException {
-            ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
-            node.accept(visitor);
-            ColumnParseNodeVisitor.ContentType type = visitor.getContentType(selfTableRefs);
+            columnRefVisitor.reset();
+            node.accept(columnRefVisitor);
+            ColumnRefParseNodeVisitor.ColumnRefType type = columnRefVisitor.getContentType(selfTableRefs);
             switch (type) {
             case NONE:
             case SELF_ONLY:
@@ -828,7 +833,7 @@ public class JoinCompiler {
             case FOREIGN_ONLY:
                 JoinTable matched = null;
                 for (JoinTable joinTable : prefilterAcceptedTables) {
-                    if (visitor.getContentType(joinTable.getTableRefs()) == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+                    if (columnRefVisitor.getContentType(joinTable.getTableRefs()) == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
                         matched = joinTable;
                         break;
                     }
@@ -868,17 +873,17 @@ public class JoinCompiler {
     }
     
     private static class OnNodeVisitor extends BooleanParseNodeVisitor<Void> {
-        private ColumnResolver resolver;
         private List<ComparisonParseNode> onConditions;
         private Set<TableRef> dependencies;
         private JoinTable joinTable;
+        private ColumnRefParseNodeVisitor columnRefVisitor;
         
         public OnNodeVisitor(ColumnResolver resolver, List<ComparisonParseNode> onConditions, 
                 Set<TableRef> dependencies, JoinTable joinTable) {
-            this.resolver = resolver;
             this.onConditions = onConditions;
             this.dependencies = dependencies;
             this.joinTable = joinTable;
+            this.columnRefVisitor = new ColumnRefParseNodeVisitor(resolver);
         }
         
         @Override
@@ -889,11 +894,11 @@ public class JoinCompiler {
         @Override
         protected Void leaveBooleanNode(ParseNode node,
                 List<Void> l) throws SQLException {
-            ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
-            node.accept(visitor);
-            ColumnParseNodeVisitor.ContentType type = visitor.getContentType(joinTable.getTableRefs());
-            if (type == ColumnParseNodeVisitor.ContentType.NONE 
-                    || type == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+            columnRefVisitor.reset();
+            node.accept(columnRefVisitor);
+            ColumnRefParseNodeVisitor.ColumnRefType type = columnRefVisitor.getContentType(joinTable.getTableRefs());
+            if (type == ColumnRefParseNodeVisitor.ColumnRefType.NONE 
+                    || type == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
                 joinTable.addFilter(node);
             } else {
                 throwUnsupportedJoinConditionException();
@@ -926,23 +931,25 @@ public class JoinCompiler {
                 throws SQLException {
             if (!(node instanceof EqualParseNode))
                 return leaveBooleanNode(node, l);
-            ColumnParseNodeVisitor lhsVisitor = new ColumnParseNodeVisitor(resolver);
-            ColumnParseNodeVisitor rhsVisitor = new ColumnParseNodeVisitor(resolver);
-            node.getLHS().accept(lhsVisitor);
-            node.getRHS().accept(rhsVisitor);
-            ColumnParseNodeVisitor.ContentType lhsType = lhsVisitor.getContentType(joinTable.getTableRefs());
-            ColumnParseNodeVisitor.ContentType rhsType = rhsVisitor.getContentType(joinTable.getTableRefs());
-            if ((lhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY || lhsType == ColumnParseNodeVisitor.ContentType.NONE)
-                    && (rhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY || rhsType == ColumnParseNodeVisitor.ContentType.NONE)) {
+            columnRefVisitor.reset();
+            node.getLHS().accept(columnRefVisitor);
+            ColumnRefParseNodeVisitor.ColumnRefType lhsType = columnRefVisitor.getContentType(joinTable.getTableRefs());
+            Set<TableRef> lhsTableRefSet = Sets.newHashSet(columnRefVisitor.getTableRefSet());
+            columnRefVisitor.reset();
+            node.getRHS().accept(columnRefVisitor);
+            ColumnRefParseNodeVisitor.ColumnRefType rhsType = columnRefVisitor.getContentType(joinTable.getTableRefs());
+            Set<TableRef> rhsTableRefSet = Sets.newHashSet(columnRefVisitor.getTableRefSet());
+            if ((lhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY || lhsType == ColumnRefParseNodeVisitor.ColumnRefType.NONE)
+                    && (rhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY || rhsType == ColumnRefParseNodeVisitor.ColumnRefType.NONE)) {
                 joinTable.addFilter(node);
-            } else if (lhsType == ColumnParseNodeVisitor.ContentType.FOREIGN_ONLY 
-                    && rhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+            } else if (lhsType == ColumnRefParseNodeVisitor.ColumnRefType.FOREIGN_ONLY 
+                    && rhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
                 onConditions.add(node);
-                dependencies.addAll(lhsVisitor.getTableRefSet());
-            } else if (rhsType == ColumnParseNodeVisitor.ContentType.FOREIGN_ONLY 
-                    && lhsType == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+                dependencies.addAll(lhsTableRefSet);
+            } else if (rhsType == ColumnRefParseNodeVisitor.ColumnRefType.FOREIGN_ONLY 
+                    && lhsType == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
                 onConditions.add(NODE_FACTORY.equal(node.getRHS(), node.getLHS()));
-                dependencies.addAll(rhsVisitor.getTableRefSet());
+                dependencies.addAll(rhsTableRefSet);
             } else {
                 throwUnsupportedJoinConditionException();
             }
@@ -961,25 +968,25 @@ public class JoinCompiler {
             throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi join conditions.");
         }           
     }
-    
-    private static class ColumnParseNodeVisitor  extends StatelessTraverseAllParseNodeVisitor {
-        public enum ContentType {NONE, SELF_ONLY, FOREIGN_ONLY, COMPLEX};
-        
+
+    private static class ColumnRefParseNodeVisitor extends StatelessTraverseAllParseNodeVisitor {
+        public enum ColumnRefType {NONE, SELF_ONLY, FOREIGN_ONLY, COMPLEX};
+
         private ColumnResolver resolver;
         private final Set<TableRef> tableRefSet;
         private final Map<ColumnRef, ColumnParseNode> columnRefMap;
-       
-        public ColumnParseNodeVisitor(ColumnResolver resolver) {
+
+        public ColumnRefParseNodeVisitor(ColumnResolver resolver) {
             this.resolver = resolver;
             this.tableRefSet = new HashSet<TableRef>();
             this.columnRefMap = new HashMap<ColumnRef, ColumnParseNode>();
         }
-        
+
         public void reset() {
             this.tableRefSet.clear();
             this.columnRefMap.clear();
         }
-        
+
         @Override
         public Void visit(ColumnParseNode node) throws SQLException {
             ColumnRef columnRef = resolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
@@ -987,41 +994,41 @@ public class JoinCompiler {
             tableRefSet.add(columnRef.getTableRef());
             return null;                
         }
-        
+
         public Set<TableRef> getTableRefSet() {
             return tableRefSet;
         }
-        
+
         public Map<ColumnRef, ColumnParseNode> getColumnRefMap() {
             return columnRefMap;
         }
-        
-        public ContentType getContentType(List<TableRef> selfTableRefs) {
+
+        public ColumnRefType getContentType(List<TableRef> selfTableRefs) {
             if (tableRefSet.isEmpty())
-                return ContentType.NONE;
-            
-            ContentType ret = ContentType.NONE;
+                return ColumnRefType.NONE;
+
+            ColumnRefType ret = ColumnRefType.NONE;
             for (TableRef tRef : tableRefSet) {
                 boolean isSelf = selfTableRefs.contains(tRef);
                 switch (ret) {
                 case NONE:
-                    ret = isSelf ? ContentType.SELF_ONLY : ContentType.FOREIGN_ONLY;
+                    ret = isSelf ? ColumnRefType.SELF_ONLY : ColumnRefType.FOREIGN_ONLY;
                     break;
                 case SELF_ONLY:
-                    ret = isSelf ? ContentType.SELF_ONLY : ContentType.COMPLEX;
+                    ret = isSelf ? ColumnRefType.SELF_ONLY : ColumnRefType.COMPLEX;
                     break;
                 case FOREIGN_ONLY:
-                    ret = isSelf ? ContentType.COMPLEX : ContentType.FOREIGN_ONLY;
+                    ret = isSelf ? ColumnRefType.COMPLEX : ColumnRefType.FOREIGN_ONLY;
                     break;
                 default: // COMPLEX do nothing
                     break;    
                 }
-                
-                if (ret == ContentType.COMPLEX) {
+
+                if (ret == ColumnRefType.COMPLEX) {
                     break;
                 }
             }
-            
+
             return ret;
         }
     }
@@ -1050,7 +1057,7 @@ public class JoinCompiler {
     
     private static List<AliasedNode> extractFromSelect(List<AliasedNode> select, TableRef tableRef, ColumnResolver resolver) throws SQLException {
         List<AliasedNode> ret = new ArrayList<AliasedNode>();
-        ColumnParseNodeVisitor visitor = new ColumnParseNodeVisitor(resolver);
+        ColumnRefParseNodeVisitor visitor = new ColumnRefParseNodeVisitor(resolver);
         for (AliasedNode aliasedNode : select) {
             ParseNode node = aliasedNode.getNode();
             if (node instanceof TableWildcardParseNode) {
@@ -1064,10 +1071,10 @@ public class JoinCompiler {
             }
             
             node.accept(visitor);
-            ColumnParseNodeVisitor.ContentType type = visitor.getContentType(Collections.singletonList(tableRef));
-            if (type == ColumnParseNodeVisitor.ContentType.SELF_ONLY) {
+            ColumnRefParseNodeVisitor.ColumnRefType type = visitor.getContentType(Collections.singletonList(tableRef));
+            if (type == ColumnRefParseNodeVisitor.ColumnRefType.SELF_ONLY) {
                 ret.add(aliasedNode);
-            } else if (type == ColumnParseNodeVisitor.ContentType.COMPLEX) {
+            } else if (type == ColumnRefParseNodeVisitor.ColumnRefType.COMPLEX) {
                 for (Map.Entry<ColumnRef, ColumnParseNode> entry : visitor.getColumnRefMap().entrySet()) {
                     if (entry.getKey().getTableRef().equals(tableRef)) {
                         ret.add(NODE_FACTORY.aliasedNode(null, entry.getValue()));
@@ -1083,7 +1090,7 @@ public class JoinCompiler {
         TableRef groupByTableRef = null;
         TableRef orderByTableRef = null;
         if (select.getGroupBy() != null && !select.getGroupBy().isEmpty()) {
-            ColumnParseNodeVisitor groupByVisitor = new ColumnParseNodeVisitor(resolver);
+            ColumnRefParseNodeVisitor groupByVisitor = new ColumnRefParseNodeVisitor(resolver);
             for (ParseNode node : select.getGroupBy()) {
                 node.accept(groupByVisitor);
             }
@@ -1092,7 +1099,7 @@ public class JoinCompiler {
                 groupByTableRef = set.iterator().next();
             }
         } else if (select.getOrderBy() != null && !select.getOrderBy().isEmpty()) {
-            ColumnParseNodeVisitor orderByVisitor = new ColumnParseNodeVisitor(resolver);
+            ColumnRefParseNodeVisitor orderByVisitor = new ColumnRefParseNodeVisitor(resolver);
             for (OrderByNode node : select.getOrderBy()) {
                 node.getNode().accept(orderByVisitor);
             }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
index 1512d82..52abb9e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
@@ -215,15 +215,17 @@ public class QueryCompiler {
                 Pair<List<Expression>, List<Expression>> joinConditions = joinSpec.compileJoinConditions(context, leftResolver, resolver);
                 joinExpressions[i] = joinConditions.getFirst();
                 List<Expression> hashExpressions = joinConditions.getSecond();
-                Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions);
+                Pair<Expression, Expression> keyRangeExpressions = new Pair<Expression, Expression>(null, null);
+                boolean complete = getKeyExpressionCombinations(keyRangeExpressions, context, tableRef, joinSpec.getType(), joinExpressions[i], hashExpressions);
                 Expression keyRangeLhsExpression = keyRangeExpressions.getFirst();
                 Expression keyRangeRhsExpression = keyRangeExpressions.getSecond();
                 boolean hasFilters = joinSpec.getJoinTable().hasFilters();
+                boolean optimized = complete && hasFilters;
                 joinTypes[i] = joinSpec.getType();
                 if (i < count - 1) {
                     fieldPositions[i + 1] = fieldPositions[i] + (tables[i] == null ? 0 : (tables[i].getColumns().size() - tables[i].getPKColumns().size()));
                 }
-                subPlans[i] = new HashSubPlan(i, joinPlan, hashExpressions, keyRangeLhsExpression, keyRangeRhsExpression, clientProjector, hasFilters);
+                subPlans[i] = new HashSubPlan(i, joinPlan, optimized ? null : hashExpressions, keyRangeLhsExpression, keyRangeRhsExpression, clientProjector, hasFilters);
             }
             if (needsProject) {
                 TupleProjector.serializeProjectorIntoScan(context.getScan(), initialProjectedTable.createTupleProjector());
@@ -292,7 +294,8 @@ public class QueryCompiler {
                 limit = LimitCompiler.compile(context, rhs);
             }
             HashJoinInfo joinInfo = new HashJoinInfo(projectedTable.getTable(), joinIds, new List[] {joinExpressions}, new JoinType[] {type == JoinType.Inner ? type : JoinType.Left}, new boolean[] {true}, new PTable[] {lhsProjTable.getTable()}, new int[] {fieldPosition}, postJoinFilterExpression, limit, forceProjection);
-            Pair<Expression, Expression> keyRangeExpressions = getKeyExpressionCombinations(context, rhsTableRef, type, joinExpressions, hashExpressions);
+            Pair<Expression, Expression> keyRangeExpressions = new Pair<Expression, Expression>(null, null);
+            getKeyExpressionCombinations(keyRangeExpressions, context, rhsTableRef, type, joinExpressions, hashExpressions);
             return HashJoinPlan.create(joinTable.getStatement(), rhsPlan, joinInfo, new HashSubPlan[] {new HashSubPlan(0, lhsPlan, hashExpressions, keyRangeExpressions.getFirst(), keyRangeExpressions.getSecond(), clientProjector, lhsJoin.hasFilters())});
         }
         
@@ -300,16 +303,17 @@ public class QueryCompiler {
         throw new SQLFeatureNotSupportedException("Joins with pattern 'A right join B left join C' not supported.");
     }
     
-    private Pair<Expression, Expression> getKeyExpressionCombinations(StatementContext context, TableRef table, JoinType type, final List<Expression> joinExpressions, final List<Expression> hashExpressions) throws SQLException {
-        if (type != JoinType.Inner)
-            return new Pair<Expression, Expression>(null, null);
+    private boolean getKeyExpressionCombinations(Pair<Expression, Expression> combination, StatementContext context, TableRef table, JoinType type, final List<Expression> joinExpressions, final List<Expression> hashExpressions) throws SQLException {
+        if (type != JoinType.Inner && type != JoinType.Semi)
+            return false;
         
         Scan scanCopy = ScanUtil.newScan(context.getScan());
         StatementContext contextCopy = new StatementContext(statement, context.getResolver(), scanCopy, new SequenceManager(statement));
         contextCopy.setCurrentTable(table);
-        List<Expression> lhsCombination = WhereOptimizer.getKeyExpressionCombination(contextCopy, this.select, joinExpressions);
+        List<Expression> lhsCombination = Lists.<Expression> newArrayList();
+        boolean complete = WhereOptimizer.getKeyExpressionCombination(lhsCombination, contextCopy, this.select, joinExpressions);
         if (lhsCombination.isEmpty())
-            return new Pair<Expression, Expression>(null, null);
+            return false;
         
         List<Expression> rhsCombination = Lists.newArrayListWithExpectedSize(lhsCombination.size());
         for (int i = 0; i < lhsCombination.size(); i++) {
@@ -322,15 +326,26 @@ public class QueryCompiler {
             }
         }
         
-        if (lhsCombination.size() == 1)
-            return new Pair<Expression, Expression>(lhsCombination.get(0), rhsCombination.get(0));
+        if (lhsCombination.size() == 1) {
+            combination.setFirst(lhsCombination.get(0));
+            combination.setSecond(rhsCombination.get(0));
+        } else {
+            combination.setFirst(new RowValueConstructorExpression(lhsCombination, false));
+            combination.setSecond(new RowValueConstructorExpression(rhsCombination, false));
+        }
         
-        return new Pair<Expression, Expression>(new RowValueConstructorExpression(lhsCombination, false), new RowValueConstructorExpression(rhsCombination, false));
+        return type == JoinType.Semi && complete;
     }
     
     protected QueryPlan compileSubquery(SelectStatement subquery) throws SQLException {
+        subquery = SubselectRewriter.flatten(subquery, this.statement.getConnection());
         ColumnResolver resolver = FromCompiler.getResolverForQuery(subquery, this.statement.getConnection());
         subquery = StatementNormalizer.normalize(subquery, resolver);
+        SelectStatement transformedSubquery = SubqueryRewriter.transform(subquery, resolver, this.statement.getConnection());
+        if (transformedSubquery != subquery) {
+            resolver = FromCompiler.getResolverForQuery(transformedSubquery, this.statement.getConnection());
+            subquery = StatementNormalizer.normalize(transformedSubquery, resolver);
+        }
         QueryPlan plan = new QueryCompiler(this.statement, subquery, resolver).compile();
         return statement.getConnection().getQueryServices().getOptimizer().optimize(statement, plan);
     }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
index 698756b..acab605 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java
@@ -41,6 +41,7 @@ import org.apache.phoenix.parse.TableNode;
 import org.apache.phoenix.parse.TableNodeVisitor;
 import org.apache.phoenix.parse.TableWildcardParseNode;
 import org.apache.phoenix.parse.WildcardParseNode;
+import org.apache.phoenix.parse.JoinTableNode.JoinType;
 import org.apache.phoenix.util.SchemaUtil;
 
 import com.google.common.collect.Lists;
@@ -121,7 +122,7 @@ public class StatementNormalizer extends ParseNodeRewriter {
         @Override
         public List<TableName> visit(JoinTableNode joinNode) throws SQLException {
             List<TableName> lhs = joinNode.getLHS().accept(this);
-            List<TableName> rhs = joinNode.getRHS().accept(this);
+            List<TableName> rhs = joinNode.getType() == JoinType.Semi || joinNode.getType() == JoinType.Anti ? Collections.<TableName> emptyList() : joinNode.getRHS().accept(this);
             List<TableName> ret = Lists.<TableName>newArrayListWithExpectedSize(lhs.size() + rhs.size());
             ret.addAll(lhs);
             ret.addAll(rhs);

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
new file mode 100644
index 0000000..42d060f
--- /dev/null
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/SubqueryRewriter.java
@@ -0,0 +1,401 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.compile;
+
+import java.sql.SQLException;
+import java.sql.SQLFeatureNotSupportedException;
+import java.util.Collections;
+import java.util.List;
+
+import org.apache.hadoop.hbase.filter.CompareFilter;
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.exception.SQLExceptionInfo;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.parse.AliasedNode;
+import org.apache.phoenix.parse.AndParseNode;
+import org.apache.phoenix.parse.BooleanParseNodeVisitor;
+import org.apache.phoenix.parse.ColumnParseNode;
+import org.apache.phoenix.parse.ComparisonParseNode;
+import org.apache.phoenix.parse.CompoundParseNode;
+import org.apache.phoenix.parse.ExistsParseNode;
+import org.apache.phoenix.parse.InParseNode;
+import org.apache.phoenix.parse.JoinTableNode.JoinType;
+import org.apache.phoenix.parse.LiteralParseNode;
+import org.apache.phoenix.parse.ParseNode;
+import org.apache.phoenix.parse.ParseNodeFactory;
+import org.apache.phoenix.parse.ParseNodeRewriter;
+import org.apache.phoenix.parse.RowValueConstructorParseNode;
+import org.apache.phoenix.parse.SelectStatement;
+import org.apache.phoenix.parse.StatelessTraverseAllParseNodeVisitor;
+import org.apache.phoenix.parse.SubqueryParseNode;
+import org.apache.phoenix.parse.TableName;
+import org.apache.phoenix.parse.TableNode;
+import org.apache.phoenix.schema.ColumnFamilyNotFoundException;
+import org.apache.phoenix.schema.ColumnNotFoundException;
+
+import com.google.common.collect.Lists;
+
+/*
+ * Class for rewriting where-clause sub-queries into join queries.
+ * 
+ * If the where-clause sub-query is one of those top-node conditions (being 
+ * the only condition node or direct descendant of AND nodes), we convert the
+ * sub-query directly into semi-joins, anti-joins or inner-joins, and meanwhile
+ * remove the original condition node from the where clause.
+ * Otherwise, we convert the sub-query into left-joins and change the original
+ * condition node into a null test of a join table field (ONE if matched, NULL 
+ * if not matched).
+ */
+public class SubqueryRewriter extends ParseNodeRewriter {
+    private static final ParseNodeFactory NODE_FACTORY = new ParseNodeFactory();
+    
+    private final ColumnResolver resolver;
+    private final PhoenixConnection connection;
+    private TableNode tableNode;
+    private ParseNode topNode;
+    
+    public static SelectStatement transform(SelectStatement select, ColumnResolver resolver, PhoenixConnection connection) throws SQLException {
+        ParseNode where = select.getWhere();
+        if (where == null)
+            return select;
+        
+        SubqueryRewriter rewriter = new SubqueryRewriter(select, resolver, connection);
+        ParseNode normWhere = rewrite(where, rewriter);
+        if (normWhere == where)
+            return select;
+        
+        return NODE_FACTORY.select(Collections.singletonList(rewriter.tableNode), select.getHint(), select.isDistinct(), select.getSelect(), normWhere, select.getGroupBy(), select.getHaving(), select.getOrderBy(), select.getLimit(), select.getBindCount(), select.isAggregate(), select.hasSequence());
+    }
+    
+    protected SubqueryRewriter(SelectStatement select, ColumnResolver resolver, PhoenixConnection connection) {
+        this.resolver = resolver;
+        this.connection = connection;
+        this.tableNode = select.getFrom().get(0);
+        this.topNode = null;
+    }
+    
+    @Override
+    protected void enterParseNode(ParseNode node) {
+        if (topNode == null) {
+            topNode = node;
+        }
+        super.enterParseNode(node);
+    }
+    
+    @Override
+    protected ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, ParseNodeRewriter.CompoundNodeFactory factory) {
+        if (topNode == node) {
+            topNode = null;
+        }
+        
+        return super.leaveCompoundNode(node, children, factory);
+    }
+
+    @Override
+    public boolean visitEnter(AndParseNode node) throws SQLException {
+        return true;
+    }
+
+    @Override
+    public ParseNode visitLeave(AndParseNode node, List<ParseNode> l) throws SQLException {
+        return leaveCompoundNode(node, l, new CompoundNodeFactory() {
+            @Override
+            public ParseNode createNode(List<ParseNode> children) {
+                if (children.isEmpty()) {
+                    return null;
+                }
+                if (children.size() == 1) {
+                    return children.get(0);
+                }
+                return NODE_FACTORY.and(children);
+            }
+        });
+    }
+
+    @Override
+    public ParseNode visitLeave(InParseNode node, List<ParseNode> l) throws SQLException {
+        SubqueryParseNode subqueryNode = (SubqueryParseNode) l.get(1);
+        SelectStatement subquery = subqueryNode.getSelectNode();
+        String rhsTableAlias = ParseNodeFactory.createTempAlias();
+        List<AliasedNode> selectNodes = fixAliasedNodes(subquery.getSelect());
+        subquery = NODE_FACTORY.select(subquery.getFrom(), subquery.getHint(), true, 
+                selectNodes, subquery.getWhere(), subquery.getGroupBy(), subquery.getHaving(), subquery.getOrderBy(), 
+                subquery.getLimit(), subquery.getBindCount(), subquery.isAggregate(), subquery.hasSequence());
+        ParseNode onNode = getJoinConditionNode(l.get(0), selectNodes, rhsTableAlias);
+        TableNode rhsTable = NODE_FACTORY.derivedTable(rhsTableAlias, subquery);
+        JoinType joinType = topNode == node ? (node.isNegate() ? JoinType.Anti : JoinType.Semi) : JoinType.Left;
+        ParseNode ret = topNode == node ? null : NODE_FACTORY.isNull(NODE_FACTORY.column(NODE_FACTORY.table(null, rhsTableAlias), selectNodes.get(0).getAlias(), null), !node.isNegate());
+        tableNode = NODE_FACTORY.join(joinType, tableNode, rhsTable, onNode);
+        
+        if (topNode == node) {
+            topNode = null;
+        }
+        
+        return ret;
+    }
+
+    @Override
+    public ParseNode visitLeave(ExistsParseNode node, List<ParseNode> l) throws SQLException {
+        SubqueryParseNode subqueryNode = (SubqueryParseNode) l.get(0);
+        SelectStatement subquery = subqueryNode.getSelectNode();
+        String rhsTableAlias = ParseNodeFactory.createTempAlias();
+        JoinConditionExtractor conditionExtractor = new JoinConditionExtractor(subquery, resolver, connection, rhsTableAlias);
+        ParseNode where = subquery.getWhere() == null ? null : subquery.getWhere().accept(conditionExtractor);
+        if (where == subquery.getWhere()) { // non-correlated EXISTS subquery, add LIMIT 1
+            subquery = NODE_FACTORY.select(subquery, NODE_FACTORY.limit(NODE_FACTORY.literal(1)));
+            subqueryNode = NODE_FACTORY.subquery(subquery, false);
+            node = NODE_FACTORY.exists(subqueryNode, node.isNegate());
+            return super.visitLeave(node, Collections.<ParseNode> singletonList(subqueryNode));
+        }
+        
+        List<AliasedNode> additionalSelectNodes = conditionExtractor.getAdditionalSelectNodes();
+        List<AliasedNode> selectNodes = Lists.newArrayListWithExpectedSize(additionalSelectNodes.size() + 1);
+        selectNodes.add(NODE_FACTORY.aliasedNode(ParseNodeFactory.createTempAlias(), LiteralParseNode.ONE));
+        selectNodes.addAll(additionalSelectNodes);
+        
+        subquery = NODE_FACTORY.select(subquery.getFrom(), subquery.getHint(), true, 
+                selectNodes, where, subquery.getGroupBy(), subquery.getHaving(), subquery.getOrderBy(), 
+                subquery.getLimit(), subquery.getBindCount(), subquery.isAggregate(), subquery.hasSequence());
+        ParseNode onNode = conditionExtractor.getJoinCondition();
+        TableNode rhsTable = NODE_FACTORY.derivedTable(rhsTableAlias, subquery);
+        JoinType joinType = topNode == node ? (node.isNegate() ? JoinType.Anti : JoinType.Semi) : JoinType.Left;
+        ParseNode ret = topNode == node ? null : NODE_FACTORY.isNull(NODE_FACTORY.column(NODE_FACTORY.table(null, rhsTableAlias), selectNodes.get(0).getAlias(), null), !node.isNegate());
+        tableNode = NODE_FACTORY.join(joinType, tableNode, rhsTable, onNode);
+        
+        if (topNode == node) {
+            topNode = null;
+        }
+        
+        return ret;
+    }
+    
+    private List<AliasedNode> fixAliasedNodes(List<AliasedNode> nodes) {
+        List<AliasedNode> normNodes = Lists.<AliasedNode> newArrayListWithExpectedSize(nodes.size() + 1);
+        normNodes.add(NODE_FACTORY.aliasedNode(ParseNodeFactory.createTempAlias(), LiteralParseNode.ONE));
+        for (int i = 0; i < nodes.size(); i++) {
+            AliasedNode aliasedNode = nodes.get(i);
+            normNodes.add(NODE_FACTORY.aliasedNode(
+                    ParseNodeFactory.createTempAlias(), aliasedNode.getNode()));
+        }
+        
+        return normNodes;
+    }
+    
+    private ParseNode getJoinConditionNode(ParseNode lhs, List<AliasedNode> rhs, String rhsTableAlias) throws SQLException {
+        List<ParseNode> lhsNodes;        
+        if (lhs instanceof RowValueConstructorParseNode) {
+            lhsNodes = ((RowValueConstructorParseNode) lhs).getChildren();
+        } else {
+            lhsNodes = Collections.singletonList(lhs);
+        }
+        if (lhsNodes.size() != (rhs.size() - 1))
+            throw new SQLExceptionInfo.Builder(SQLExceptionCode.SUBQUERY_RETURNS_DIFFERENT_NUMBER_OF_FIELDS).build().buildException();
+        
+        int count = lhsNodes.size();
+        TableName rhsTableName = NODE_FACTORY.table(null, rhsTableAlias);
+        List<ParseNode> equalNodes = Lists.newArrayListWithExpectedSize(count);
+        for (int i = 0; i < count; i++) {
+            ParseNode rhsNode = NODE_FACTORY.column(rhsTableName, rhs.get(i + 1).getAlias(), null);
+            equalNodes.add(NODE_FACTORY.equal(lhsNodes.get(i), rhsNode));
+        }
+        
+        return count == 1 ? equalNodes.get(0) : NODE_FACTORY.and(equalNodes);
+    }
+    
+    private static class JoinConditionExtractor extends BooleanParseNodeVisitor<ParseNode> {
+        private final TableName tableName;
+        private ColumnResolveVisitor columnResolveVisitor;
+        private List<AliasedNode> additionalSelectNodes;
+        private List<ParseNode> joinConditions;
+        
+        public JoinConditionExtractor(SelectStatement subquery, ColumnResolver outerResolver, 
+                PhoenixConnection connection, String tableAlias) throws SQLException {
+            this.tableName = NODE_FACTORY.table(null, tableAlias);
+            ColumnResolver localResolver = FromCompiler.getResolverForQuery(subquery, connection);
+            this.columnResolveVisitor = new ColumnResolveVisitor(localResolver, outerResolver);
+            this.additionalSelectNodes = Lists.<AliasedNode> newArrayList();
+            this.joinConditions = Lists.<ParseNode> newArrayList();
+        }
+        
+        public List<AliasedNode> getAdditionalSelectNodes() {
+            return this.additionalSelectNodes;
+        }
+        
+        public ParseNode getJoinCondition() {
+            if (this.joinConditions.isEmpty())
+                return null;
+            
+            if (this.joinConditions.size() == 1)
+                return this.joinConditions.get(0);
+            
+            return NODE_FACTORY.and(this.joinConditions);            
+        }
+        
+        @Override
+        public List<ParseNode> newElementList(int size) {
+            return Lists.<ParseNode> newArrayListWithExpectedSize(size);
+        }
+
+        @Override
+        public void addElement(List<ParseNode> l, ParseNode element) {
+            if (element != null) {
+                l.add(element);
+            }
+        }
+
+        @Override
+        public boolean visitEnter(AndParseNode node) throws SQLException {
+            return true;
+        }
+
+        @Override
+        public ParseNode visitLeave(AndParseNode node, List<ParseNode> l)
+                throws SQLException {
+            if (l.equals(node.getChildren()))
+                return node;
+
+            if (l.isEmpty())
+                return null;
+            
+            if (l.size() == 1)
+                return l.get(0);
+            
+            return NODE_FACTORY.and(l);
+        }
+
+        @Override
+        protected boolean enterBooleanNode(ParseNode node) throws SQLException {
+            return false;
+        }
+
+        @Override
+        protected ParseNode leaveBooleanNode(ParseNode node, List<ParseNode> l)
+                throws SQLException {
+            columnResolveVisitor.reset();
+            node.accept(columnResolveVisitor);
+            ColumnResolveVisitor.ColumnResolveType type = columnResolveVisitor.getColumnResolveType();
+            if (type != ColumnResolveVisitor.ColumnResolveType.NONE 
+                    && type != ColumnResolveVisitor.ColumnResolveType.LOCAL)
+                throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi correlated-subquery conditions.");
+            
+            return node;
+        }
+
+        @Override
+        protected boolean enterNonBooleanNode(ParseNode node)
+                throws SQLException {
+            return false;
+        }
+
+        @Override
+        protected ParseNode leaveNonBooleanNode(ParseNode node,
+                List<ParseNode> l) throws SQLException {
+            return node;
+        }
+
+        @Override
+        public ParseNode visitLeave(ComparisonParseNode node, List<ParseNode> l) throws SQLException {
+            if (node.getFilterOp() != CompareFilter.CompareOp.EQUAL)
+                return leaveBooleanNode(node, l);
+            
+            columnResolveVisitor.reset();
+            node.getLHS().accept(columnResolveVisitor);
+            ColumnResolveVisitor.ColumnResolveType lhsType = columnResolveVisitor.getColumnResolveType();
+            columnResolveVisitor.reset();
+            node.getRHS().accept(columnResolveVisitor);
+            ColumnResolveVisitor.ColumnResolveType rhsType = columnResolveVisitor.getColumnResolveType();
+            if ((lhsType == ColumnResolveVisitor.ColumnResolveType.NONE || lhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL)
+                    && (rhsType == ColumnResolveVisitor.ColumnResolveType.NONE || rhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL)) {
+                return node;
+            }
+            if (lhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL && rhsType == ColumnResolveVisitor.ColumnResolveType.OUTER) {
+                String alias = ParseNodeFactory.createTempAlias();
+                this.additionalSelectNodes.add(NODE_FACTORY.aliasedNode(alias, node.getLHS()));
+                ParseNode lhsNode = NODE_FACTORY.column(tableName, alias, null);
+                this.joinConditions.add(NODE_FACTORY.equal(lhsNode, node.getRHS()));
+                return null;
+            }        
+            if (lhsType == ColumnResolveVisitor.ColumnResolveType.OUTER && rhsType == ColumnResolveVisitor.ColumnResolveType.LOCAL) {
+                String alias = ParseNodeFactory.createTempAlias();
+                this.additionalSelectNodes.add(NODE_FACTORY.aliasedNode(alias, node.getRHS()));
+                ParseNode rhsNode = NODE_FACTORY.column(tableName, alias, null);
+                this.joinConditions.add(NODE_FACTORY.equal(node.getLHS(), rhsNode));
+                return null;
+            }
+            
+            throw new SQLFeatureNotSupportedException("Does not support non-standard or non-equi correlated-subquery conditions.");   
+        }
+    }
+
+    /*
+     * Class for resolving inner query column references
+     */
+    private static class ColumnResolveVisitor extends StatelessTraverseAllParseNodeVisitor {
+        public enum ColumnResolveType {NONE, LOCAL, OUTER, MIXED};
+
+        private final ColumnResolver localResolver;
+        private final ColumnResolver outerResolver;
+        private ColumnResolveType type;
+
+        public ColumnResolveVisitor(ColumnResolver localResolver, ColumnResolver outerResolver) {
+            this.localResolver = localResolver;
+            this.outerResolver = outerResolver;
+            this.type = ColumnResolveType.NONE;
+        }
+
+        public void reset() {
+            this.type = ColumnResolveType.NONE;
+        }
+        
+        public ColumnResolveType getColumnResolveType() {
+            return this.type;
+        }
+
+        @Override
+        public Void visit(ColumnParseNode node) throws SQLException {
+            // Inner query column definitions should shade those of outer query.
+            try {
+                localResolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
+                addType(true);
+                return null;
+            } catch (ColumnNotFoundException e) { 
+            } catch (ColumnFamilyNotFoundException e) {
+            }
+            
+            outerResolver.resolveColumn(node.getSchemaName(), node.getTableName(), node.getName());
+            addType(false);
+            return null;
+        }
+        
+        private void addType(boolean isLocal) {
+            switch (this.type) {
+            case NONE:
+                this.type = isLocal ? ColumnResolveType.LOCAL : ColumnResolveType.OUTER;
+                break;
+            case LOCAL:
+                this.type = isLocal ? ColumnResolveType.LOCAL : ColumnResolveType.MIXED;
+                break;
+            case OUTER:
+                this.type = isLocal ? ColumnResolveType.MIXED : ColumnResolveType.OUTER;
+                break;
+            default: // MIXED do nothing
+                break;
+            }
+        }
+    }
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
index 9f674f1..6652ce3 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/UpsertCompiler.java
@@ -372,6 +372,11 @@ public class UpsertCompiler {
                     ColumnResolver selectResolver = FromCompiler.getResolverForQuery(select, connection);
                     select = StatementNormalizer.normalize(select, selectResolver);
                     select = prependTenantAndViewConstants(table, select, tenantId, addViewColumnsToBe);
+                    SelectStatement transformedSelect = SubqueryRewriter.transform(select, selectResolver, connection);
+                    if (transformedSelect != select) {
+                        selectResolver = FromCompiler.getResolverForQuery(transformedSelect, connection);
+                        select = StatementNormalizer.normalize(transformedSelect, selectResolver);
+                    }
                     sameTable = select.getFrom().size() == 1
                         && tableRefToBe.equals(selectResolver.getTables().get(0));
                     tableRefToBe = adjustTimestampToMinOfSameTable(tableRefToBe, selectResolver.getTables());

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
index ec763fd..bb9e351 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
@@ -311,12 +311,14 @@ public class WhereOptimizer {
     
     /**
      * Get an optimal combination of key expressions for hash join key range optimization.
+     * @return returns true if the entire combined expression is covered by key range optimization
+     * @param result the optimal combination of key expressions
      * @param context the temporary context to get scan ranges set by pushKeyExpressionsToScan()
      * @param statement the statement being compiled
      * @param expressions the join key expressions
      * @return the optimal list of key expressions
      */
-    public static List<Expression> getKeyExpressionCombination(StatementContext context, FilterableStatement statement, List<Expression> expressions) throws SQLException {
+    public static boolean getKeyExpressionCombination(List<Expression> result, StatementContext context, FilterableStatement statement, List<Expression> expressions) throws SQLException {
         List<Integer> candidateIndexes = Lists.newArrayList();
         final List<Integer> pkPositions = Lists.newArrayList();
         for (int i = 0; i < expressions.size(); i++) {
@@ -339,7 +341,7 @@ public class WhereOptimizer {
         }
         
         if (candidateIndexes.isEmpty())
-            return Collections.<Expression> emptyList();
+            return false;
         
         Collections.sort(candidateIndexes, new Comparator<Integer>() {
             @Override
@@ -364,12 +366,13 @@ public class WhereOptimizer {
         
         int count = 0;
         int maxPkSpan = 0;
+        Expression remaining = null;
         while (count < candidates.size()) {
             Expression lhs = count == 0 ? candidates.get(0) : new RowValueConstructorExpression(candidates.subList(0, count + 1), false);
             Expression firstRhs = count == 0 ? sampleValues.get(0).get(0) : new RowValueConstructorExpression(sampleValues.get(0).subList(0, count + 1), true);
             Expression secondRhs = count == 0 ? sampleValues.get(1).get(0) : new RowValueConstructorExpression(sampleValues.get(1).subList(0, count + 1), true);
             Expression testExpression = InListExpression.create(Lists.newArrayList(lhs, firstRhs, secondRhs), false, context.getTempPtr());
-            pushKeyExpressionsToScan(context, statement, testExpression);
+            remaining = pushKeyExpressionsToScan(context, statement, testExpression);
             int pkSpan = context.getScanRanges().getPkColumnSpan();
             if (pkSpan <= maxPkSpan) {
                 break;
@@ -378,7 +381,11 @@ public class WhereOptimizer {
             count++;
         }
         
-        return candidates.subList(0, count);
+        result.addAll(candidates.subList(0, count));
+        
+        return count == candidates.size() 
+                && (context.getScanRanges().isPointLookup() || context.getScanRanges().useSkipScanFilter())
+                && (remaining == null || remaining.equals(LiteralExpression.newConstant(true, Determinism.ALWAYS)));
     }
 
     private static class RemoveExtractedNodesVisitor extends TraverseNoExpressionVisitor<Expression> {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
index 9872ada..b458c8c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/coprocessor/HashJoinRegionScanner.java
@@ -72,7 +72,7 @@ public class HashJoinRegionScanner implements RegionScanner {
         this.limit = Long.MAX_VALUE;
         if (joinInfo != null) {
             for (JoinType type : joinInfo.getJoinTypes()) {
-                if (type != JoinType.Inner && type != JoinType.Left)
+                if (type != JoinType.Inner && type != JoinType.Left && type != JoinType.Semi && type != JoinType.Anti)
                     throw new DoNotRetryIOException("Got join type '" + type + "'. Expect only INNER or LEFT with hash-joins.");
             }
             if (joinInfo.getLimit() != null) {
@@ -85,6 +85,12 @@ public class HashJoinRegionScanner implements RegionScanner {
             TenantCache cache = GlobalCache.getTenantCache(env, tenantId);
             for (int i = 0; i < count; i++) {
                 ImmutableBytesPtr joinId = joinInfo.getJoinIds()[i];
+                if (joinId.getLength() == 0) { // semi-join optimized into skip-scan
+                    hashCaches[i] = null;
+                    tempSrcBitSet[i] = null;
+                    tempTuples[i] = null;
+                    continue;
+                }
                 HashCache hashCache = (HashCache)cache.getServerCache(joinId);
                 if (hashCache == null)
                     throw new DoNotRetryIOException("Could not find hash cache for joinId: " 
@@ -119,12 +125,13 @@ public class HashJoinRegionScanner implements RegionScanner {
         int count = joinInfo.getJoinIds().length;
         boolean cont = true;
         for (int i = 0; i < count; i++) {
-            if (!(joinInfo.earlyEvaluation()[i]))
+            if (!(joinInfo.earlyEvaluation()[i]) || hashCaches[i] == null)
                 continue;
             ImmutableBytesPtr key = TupleUtil.getConcatenatedValue(tuple, joinInfo.getJoinExpressions()[i]);
             tempTuples[i] = hashCaches[i].get(key);
             JoinType type = joinInfo.getJoinTypes()[i];
-            if (type == JoinType.Inner && tempTuples[i] == null) {
+            if (((type == JoinType.Inner || type == JoinType.Semi) && tempTuples[i] == null)
+                    || (type == JoinType.Anti && tempTuples[i] != null)) {
                 cont = false;
                 break;
             }
@@ -146,7 +153,8 @@ public class HashJoinRegionScanner implements RegionScanner {
                 resultQueue.offer(tuple);
                 for (int i = 0; i < count; i++) {
                     boolean earlyEvaluation = joinInfo.earlyEvaluation()[i];
-                    if (earlyEvaluation && tempTuples[i] == null)
+                    JoinType type = joinInfo.getJoinTypes()[i];
+                    if (earlyEvaluation && (tempTuples[i] == null || type == JoinType.Semi))
                         continue;
                     int j = resultQueue.size();
                     while (j-- > 0) {
@@ -155,7 +163,7 @@ public class HashJoinRegionScanner implements RegionScanner {
                             ImmutableBytesPtr key = TupleUtil.getConcatenatedValue(lhs, joinInfo.getJoinExpressions()[i]);
                             tempTuples[i] = hashCaches[i].get(key);                        	
                             if (tempTuples[i] == null) {
-                                if (joinInfo.getJoinTypes()[i] != JoinType.Inner) {
+                                if (type != JoinType.Inner && type != JoinType.Semi) {
                                     resultQueue.offer(lhs);
                                 }
                                 continue;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
index 0047aac..a300611 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
@@ -78,7 +78,8 @@ public enum SQLExceptionCode {
     SERVER_ARITHMETIC_ERROR(212, "22012", "Arithmetic error on server."),
     VALUE_OUTSIDE_RANGE(213,"22003","Value outside range."),
     VALUE_IN_LIST_NOT_CONSTANT(214, "22008", "Values in IN must evaluate to a constant."),
-    SINGLE_ROW_SUBQUERY_RETURNS_MULTIPLE_ROWS(215, "22015", "Single-row subquery returns more than one row."),
+    SINGLE_ROW_SUBQUERY_RETURNS_MULTIPLE_ROWS(215, "22015", "Single-row sub-query returns more than one row."),
+    SUBQUERY_RETURNS_DIFFERENT_NUMBER_OF_FIELDS(216, "22016", "Sub-query must return the same number of fields as the left-hand-side expression of 'IN'."),
     
     /**
      * Constraint Violation (errorcode 03, sqlstate 23)

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
index 047b62d..d68bd15 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
@@ -263,7 +263,6 @@ public class HashJoinPlan implements QueryPlan {
     public ExplainPlan getExplainPlan() throws SQLException {
         List<String> planSteps = Lists.newArrayList(plan.getExplainPlan().getPlanSteps());
         int count = subPlans.length;
-        planSteps.add("    PARALLEL EQUI/SEMI/ANTI-JOIN " + count + " TABLES:");
         for (int i = 0; i < count; i++) {
             planSteps.addAll(subPlans[i].getPreSteps(this));
         }
@@ -418,12 +417,29 @@ public class HashJoinPlan implements QueryPlan {
             if (keyRangeRhsExpression != null) {
                 keyRangeRhsValues = Lists.<ImmutableBytesWritable>newArrayList();
             }
-            ServerCache cache = parent.hashClient.addHashCache(ranges, plan.iterator(), 
-                    clientProjector, plan.getEstimatedSize(), hashExpressions, parent.plan.getTableRef(), keyRangeRhsExpression, keyRangeRhsValues);
-            long endTime = System.currentTimeMillis();
-            boolean isSet = parent.firstJobEndTime.compareAndSet(0, endTime);
-            if (!isSet && (endTime - parent.firstJobEndTime.get()) > parent.maxServerCacheTimeToLive) {
-                LOG.warn("Hash plan [" + index + "] execution seems too slow. Earlier hash cache(s) might have expired on servers.");
+            ServerCache cache = null;
+            if (hashExpressions != null) {
+                cache = parent.hashClient.addHashCache(ranges, plan.iterator(), 
+                        clientProjector, plan.getEstimatedSize(), hashExpressions, parent.plan.getTableRef(), keyRangeRhsExpression, keyRangeRhsValues);
+                long endTime = System.currentTimeMillis();
+                boolean isSet = parent.firstJobEndTime.compareAndSet(0, endTime);
+                if (!isSet && (endTime - parent.firstJobEndTime.get()) > parent.maxServerCacheTimeToLive) {
+                    LOG.warn("Hash plan [" + index + "] execution seems too slow. Earlier hash cache(s) might have expired on servers.");
+                }
+            } else {
+                assert(keyRangeRhsExpression != null);
+                ResultIterator iterator = plan.iterator();
+                for (Tuple result = iterator.next(); result != null; result = iterator.next()) {
+                    if (clientProjector != null) {
+                        result = clientProjector.projectResults(result);
+                    }
+                    // Evaluate key expressions for hash join key range optimization.
+                    ImmutableBytesWritable value = new ImmutableBytesWritable();
+                    keyRangeRhsExpression.reset();
+                    if (keyRangeRhsExpression.evaluate(result, value)) {
+                        keyRangeRhsValues.add(value);
+                    }
+                }
             }
             if (keyRangeRhsValues != null) {
                 parent.keyRangeExpressions.add(parent.createKeyRangeExpression(keyRangeLhsExpression, keyRangeRhsExpression, keyRangeRhsValues, plan.getContext().getTempPtr(), hasFilters));
@@ -435,8 +451,10 @@ public class HashJoinPlan implements QueryPlan {
         public void postProcess(Object result, HashJoinPlan parent)
                 throws SQLException {
             ServerCache cache = (ServerCache) result;
-            parent.joinInfo.getJoinIds()[index].set(cache.getId());
-            parent.dependencies.add(cache);
+            if (cache != null) {
+                parent.joinInfo.getJoinIds()[index].set(cache.getId());
+                parent.dependencies.add(cache);
+            }
         }
 
         @Override
@@ -444,7 +462,13 @@ public class HashJoinPlan implements QueryPlan {
             List<String> steps = Lists.newArrayList();
             boolean earlyEvaluation = parent.joinInfo.earlyEvaluation()[index];
             boolean skipMerge = parent.joinInfo.getSchemas()[index].getFieldCount() == 0;
-            steps.add("    BUILD HASH TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)") + (skipMerge ? " (SKIP MERGE)" : ""));
+            if (hashExpressions != null) {
+                steps.add("    PARALLEL " + parent.joinInfo.getJoinTypes()[index].toString().toUpperCase()
+                        + "-JOIN TABLE " + index + (earlyEvaluation ? "" : "(DELAYED EVALUATION)") + (skipMerge ? " (SKIP MERGE)" : ""));
+            }
+            else {
+                steps.add("    SKIP-SCAN-JOIN TABLE " + index);
+            }
             for (String step : plan.getExplainPlan().getPlanSteps()) {
                 steps.add("        " + step);
             }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
index 519a7db..3bd53c5 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
@@ -45,6 +45,7 @@ import org.apache.phoenix.compile.DropSequenceCompiler;
 import org.apache.phoenix.compile.ExplainPlan;
 import org.apache.phoenix.compile.ExpressionProjector;
 import org.apache.phoenix.compile.FromCompiler;
+import org.apache.phoenix.compile.SubqueryRewriter;
 import org.apache.phoenix.compile.GroupByCompiler.GroupBy;
 import org.apache.phoenix.compile.MutationPlan;
 import org.apache.phoenix.compile.OrderByCompiler.OrderBy;
@@ -295,6 +296,11 @@ public class PhoenixStatement implements Statement, SQLCloseable, org.apache.pho
             SelectStatement select = SubselectRewriter.flatten(this, stmt.getConnection());
             ColumnResolver resolver = FromCompiler.getResolverForQuery(select, stmt.getConnection());
             select = StatementNormalizer.normalize(select, resolver);
+            SelectStatement transformedSelect = SubqueryRewriter.transform(select, resolver, stmt.getConnection());
+            if (transformedSelect != select) {
+                resolver = FromCompiler.getResolverForQuery(transformedSelect, stmt.getConnection());
+                select = StatementNormalizer.normalize(transformedSelect, resolver);
+            }
             QueryPlan plan = new QueryCompiler(stmt, select, resolver).compile();
             plan.getContext().getSequenceManager().validateSequences(seqAction);
             return plan;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
index eb68211..0d6feda 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/BooleanParseNodeVisitor.java
@@ -139,6 +139,16 @@ public abstract class BooleanParseNodeVisitor<T> extends BaseParseNodeVisitor<T>
     }
 
     @Override
+    public boolean visitEnter(ExistsParseNode node) throws SQLException {
+        return enterBooleanNode(node);
+    }
+
+    @Override
+    public T visitLeave(ExistsParseNode node, List<T> l) throws SQLException {
+        return leaveBooleanNode(node, l);
+    }
+
+    @Override
     public boolean visitEnter(InListParseNode node) throws SQLException {
         return enterBooleanNode(node);
     }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
index 2c252e3..45ccdfe 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ExistsParseNode.java
@@ -30,11 +30,11 @@ import java.util.List;
  * 
  * @since 0.1
  */
-public class ExistsParseNode extends BinaryParseNode {
+public class ExistsParseNode extends UnaryParseNode {
     private final boolean negate;
 
-    ExistsParseNode(ParseNode l, ParseNode r, boolean negate) {
-        super(l, r);
+    ExistsParseNode(ParseNode child, boolean negate) {
+        super(child);
         this.negate = negate;
     }
     

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
index cbd6bce..a51ca5c 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/JoinTableNode.java
@@ -29,7 +29,15 @@ import java.sql.SQLException;
  * @since 0.1
  */
 public class JoinTableNode extends TableNode {
-    public enum JoinType {Inner, Left, Right, Full};
+    public enum JoinType {
+        Inner, 
+        Left, 
+        Right, 
+        Full,
+        // the following two types derive from sub-query rewriting
+        Semi, 
+        Anti,
+    };
     
     private final JoinType type;
     private final TableNode lhs;

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
index b3be0d5..ab03249 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
@@ -24,6 +24,7 @@ import java.util.Arrays;
 import java.util.Collections;
 import java.util.List;
 import java.util.Map;
+import java.util.concurrent.atomic.AtomicInteger;
 
 import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
 import org.apache.hadoop.hbase.util.Pair;
@@ -179,6 +180,12 @@ public class ParseNodeFactory {
 
     public ParseNodeFactory() {
     }
+    
+    private static AtomicInteger tempAliasCounter = new AtomicInteger(0);
+    
+    public static String createTempAlias() {
+        return "$" + tempAliasCounter.incrementAndGet();
+    }
 
     public ExplainStatement explain(BindableStatement statement) {
         return new ExplainStatement(statement);
@@ -394,8 +401,8 @@ public class ParseNodeFactory {
         return new InListParseNode(children, negate);
     }
 
-    public ExistsParseNode exists(ParseNode l, ParseNode r, boolean negate) {
-        return new ExistsParseNode(l, r, negate);
+    public ExistsParseNode exists(ParseNode child, boolean negate) {
+        return new ExistsParseNode(child, negate);
     }
 
     public InParseNode in(ParseNode l, ParseNode r, boolean negate) {
@@ -554,7 +561,11 @@ public class ParseNodeFactory {
         return new NotEqualParseNode(lhs, rhs);
     }
 
-    public NotParseNode not(ParseNode child) {
+    public ParseNode not(ParseNode child) {
+        if (child instanceof ExistsParseNode) {
+            return exists(child.getChildren().get(0), !((ExistsParseNode) child).isNegate());
+        }
+        
         return new NotParseNode(child);
     }
 

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
index 7d85a1c..51168d2 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
@@ -204,17 +204,21 @@ public class ParseNodeRewriter extends TraverseAllParseNodeVisitor<ParseNode> {
         this.nodeCount = 0;
     }
 
-    private static interface CompoundNodeFactory {
+    protected static interface CompoundNodeFactory {
         ParseNode createNode(List<ParseNode> children);
     }
 
-    private ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, CompoundNodeFactory factory) {
+    protected ParseNode leaveCompoundNode(CompoundParseNode node, List<ParseNode> children, CompoundNodeFactory factory) {
         if (children.equals(node.getChildren())) {
             return node;
         } else { // Child nodes have been inverted (because a literal was found on LHS)
             return factory.createNode(children);
         }
     }
+    
+    @Override
+    protected void enterParseNode(ParseNode node) {
+    }
 
     @Override
     public ParseNode visitLeave(AndParseNode node, List<ParseNode> nodes) throws SQLException {
@@ -327,6 +331,16 @@ public class ParseNodeRewriter extends TraverseAllParseNodeVisitor<ParseNode> {
     }
 
     @Override
+    public ParseNode visitLeave(final ExistsParseNode node, List<ParseNode> nodes) throws SQLException {
+        return leaveCompoundNode(node, nodes, new CompoundNodeFactory() {
+            @Override
+            public ParseNode createNode(List<ParseNode> children) {
+                return NODE_FACTORY.exists(children.get(0), node.isNegate());
+            }
+        });
+    }
+
+    @Override
     public ParseNode visitLeave(final CastParseNode node, List<ParseNode> nodes) throws SQLException {
         return leaveCompoundNode(node, nodes, new CompoundNodeFactory() {
             @Override

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
index 01925ff..50edf91 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java
@@ -78,6 +78,9 @@ public interface ParseNodeVisitor<E> {
     public boolean visitEnter(NotParseNode node) throws SQLException;
     public E visitLeave(NotParseNode node, List<E> l) throws SQLException;
     
+    public boolean visitEnter(ExistsParseNode node) throws SQLException;
+    public E visitLeave(ExistsParseNode node, List<E> l) throws SQLException;
+    
     public boolean visitEnter(InListParseNode node) throws SQLException;
     public E visitLeave(InListParseNode node, List<E> l) throws SQLException;
     

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
index 228e1be..e95b480 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/StatelessTraverseAllParseNodeVisitor.java
@@ -23,6 +23,10 @@ import java.util.List;
 
 public class StatelessTraverseAllParseNodeVisitor extends TraverseAllParseNodeVisitor<Void> {
     @Override
+    protected void enterParseNode(ParseNode node) {
+    }
+    
+    @Override
     public Void visitLeave(LikeParseNode node, List<Void> l) throws SQLException {
         return null;
     }
@@ -81,6 +85,11 @@ public class StatelessTraverseAllParseNodeVisitor extends TraverseAllParseNodeVi
     public Void visitLeave(NotParseNode node, List<Void> l) throws SQLException {
         return null;
     }
+
+    @Override
+    public Void visitLeave(ExistsParseNode node, List<Void> l) throws SQLException {
+        return null;
+    }
     
     @Override
     public Void visitLeave(CastParseNode node, List<Void> l) throws SQLException {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
index 337a196..bbe58d0 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java
@@ -28,158 +28,197 @@ import java.sql.SQLException;
  * @since 0.1
  */
 public abstract class TraverseAllParseNodeVisitor<T> extends BaseParseNodeVisitor<T> {
+    protected abstract void enterParseNode(ParseNode node) throws SQLException;
+    
     @Override
     public boolean visitEnter(AndParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 
     @Override
     public boolean visitEnter(OrParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 
     @Override
     public boolean visitEnter(FunctionParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 
     @Override
     public boolean visitEnter(CaseParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 
     @Override
     public boolean visitEnter(ComparisonParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(LikeParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(NotParseNode node) throws SQLException {
+        enterParseNode(node);
+        return true;
+    }
+    
+    @Override
+    public boolean visitEnter(ExistsParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(CastParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(InListParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(InParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(IsNullParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(MultiplyParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(SubtractParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(AddParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(DivideParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(ModulusParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 
     @Override
     public boolean visitEnter(BetweenParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public T visit(ColumnParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
     
     @Override
     public T visit(LiteralParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
     
     @Override
     public T visit(BindParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
 
     @Override
     public T visit(WildcardParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
 
     @Override
     public T visit(TableWildcardParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
 
     @Override
     public T visit(FamilyWildcardParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
 
     @Override
     public T visit(SubqueryParseNode node) throws SQLException {
+        enterParseNode(node);
         return null;
     }
     
     @Override
     public boolean visitEnter(StringConcatParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(RowValueConstructorParseNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public T visit(SequenceValueParseNode node) throws SQLException {			
+        enterParseNode(node);
 		return null;
 	}
     
     @Override
     public boolean visitEnter(ArrayConstructorNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(ArrayAllComparisonNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(ArrayAnyComparisonNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
     
     @Override
     public boolean visitEnter(ArrayElemRefNode node) throws SQLException {
+        enterParseNode(node);
         return true;
     }
 }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
index 37be462..7a8732a 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java
@@ -75,6 +75,11 @@ public abstract class TraverseNoParseNodeVisitor<T> extends BaseParseNodeVisitor
     }
     
     @Override
+    public boolean visitEnter(ExistsParseNode node) throws SQLException {
+        return false;
+    }
+    
+    @Override
     public boolean visitEnter(CastParseNode node) throws SQLException {
         return false;
     }
@@ -85,6 +90,11 @@ public abstract class TraverseNoParseNodeVisitor<T> extends BaseParseNodeVisitor
     }
     
     @Override
+    public T visitLeave(ExistsParseNode node, List<T> l) throws SQLException {
+        return null;
+    }
+    
+    @Override
     public T visitLeave(CastParseNode node, List<T> l) throws SQLException {
         return null;
     }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java b/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
index 43cb0c3..8e6a84e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java
@@ -76,6 +76,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
     }
 
     @Override
+    public E visit(SubqueryParseNode node) throws SQLException {
+        throw new SQLFeatureNotSupportedException(node.toString());
+    }
+
+    @Override
     public boolean visitEnter(AndParseNode node) throws SQLException {
         throw new SQLFeatureNotSupportedException(node.toString());
     }
@@ -139,6 +144,16 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
     public boolean visitEnter(NotParseNode node) throws SQLException {
         throw new SQLFeatureNotSupportedException(node.toString());
     }
+
+    @Override
+    public E visitLeave(ExistsParseNode node, List<E> l) throws SQLException {
+        throw new SQLFeatureNotSupportedException(node.toString());
+    }
+    
+    @Override
+    public boolean visitEnter(ExistsParseNode node) throws SQLException {
+        throw new SQLFeatureNotSupportedException(node.toString());
+    }
     
     @Override
     public E visitLeave(CastParseNode node, List<E> l) throws SQLException {
@@ -154,6 +169,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
     public E visitLeave(InListParseNode node, List<E> l) throws SQLException {
         throw new SQLFeatureNotSupportedException(node.toString());
     }
+    
+    @Override
+    public E visitLeave(InParseNode node, List<E> l) throws SQLException {
+        throw new SQLFeatureNotSupportedException(node.toString());
+    }
 
     @Override
     public E visitLeave(BetweenParseNode node, List<E> l) throws SQLException {
@@ -164,6 +184,11 @@ abstract public class UnsupportedAllParseNodeVisitor<E> extends BaseParseNodeVis
     public boolean visitEnter(InListParseNode node) throws SQLException {
         throw new SQLFeatureNotSupportedException(node.toString());
     }
+    
+    @Override
+    public boolean visitEnter(InParseNode node) throws SQLException {
+        throw new SQLFeatureNotSupportedException(node.toString());
+    }
 
     @Override
     public boolean visitEnter(IsNullParseNode node) throws SQLException {

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
index a08b0e3..ffaafd8 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/JoinQueryCompilerTest.java
@@ -59,14 +59,12 @@ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest {
         assertEquals(
         		"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
         		"    SERVER FILTER BY FIRST KEY ONLY\n" +
-        		"    PARALLEL EQUI/SEMI/ANTI-JOIN 1 TABLES:\n" +
-        		"    BUILD HASH TABLE 0\n" +
+        		"    PARALLEL LEFT-JOIN TABLE 0\n" +
         		"        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
-        		"            PARALLEL EQUI/SEMI/ANTI-JOIN 2 TABLES:\n" +
-        		"            BUILD HASH TABLE 0\n" +
+        		"            PARALLEL LEFT-JOIN TABLE 0\n" +
         		"                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
         		"                    SERVER FILTER BY NAME LIKE 'C%'\n" +
-        		"            BUILD HASH TABLE 1\n" +
+        		"            PARALLEL LEFT-JOIN TABLE 1\n" +
         		"                CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
         		"    AFTER-JOIN SERVER FILTER BY I.NAME LIKE 'T%'", QueryUtil.getExplainPlan(rs));
     }
@@ -133,6 +131,11 @@ public class JoinQueryCompilerTest extends BaseConnectionlessQueryTest {
         SelectStatement select = SubselectRewriter.flatten(parser.parseQuery(), connection);
         ColumnResolver resolver = FromCompiler.getResolverForQuery(select, connection);
         select = StatementNormalizer.normalize(select, resolver);
+        SelectStatement transformedSelect = SubqueryRewriter.transform(select, resolver, connection);
+        if (transformedSelect != select) {
+            resolver = FromCompiler.getResolverForQuery(transformedSelect, connection);
+            select = StatementNormalizer.normalize(transformedSelect, resolver);
+        }
         PhoenixStatement stmt = connection.createStatement().unwrap(PhoenixStatement.class);
         return JoinCompiler.compile(stmt, select, resolver);        
     }

http://git-wip-us.apache.org/repos/asf/phoenix/blob/5effbbca/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
index 59eb70b..46740a1 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/query/BaseTest.java
@@ -46,6 +46,7 @@ import static org.apache.phoenix.util.TestUtil.HBASE_DYNAMIC_COLUMNS;
 import static org.apache.phoenix.util.TestUtil.HBASE_NATIVE;
 import static org.apache.phoenix.util.TestUtil.INDEX_DATA_SCHEMA;
 import static org.apache.phoenix.util.TestUtil.INDEX_DATA_TABLE;
+import static org.apache.phoenix.util.TestUtil.JOIN_COITEM_TABLE_FULL_NAME;
 import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
 import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
 import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
@@ -428,6 +429,13 @@ public abstract class BaseTest {
                 "    phone varchar(12), " +
                 "    address varchar, " +
                 "    loc_id varchar(5))");
+        builder.put(JOIN_COITEM_TABLE_FULL_NAME, "create table " + JOIN_COITEM_TABLE_FULL_NAME +
+                "   (item_id varchar(10) NOT NULL, " +
+                "    item_name varchar NOT NULL, " +
+                "    co_item_id varchar(10), " +
+                "    co_item_name varchar " +
+                "   CONSTRAINT pk PRIMARY KEY (item_id, item_name)) " +
+                "   SALT_BUCKETS=4");
         tableDDLMap = builder.build();
     }
     


Mime
View raw message