diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoNestedSubplanRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoNestedSubplanRule.java
index b346417..aeb527d 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoNestedSubplanRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoNestedSubplanRule.java
@@ -19,6 +19,7 @@
 package org.apache.asterix.optimizer.rules;
 
 import java.util.ArrayList;
+import java.util.Collections;
 import java.util.HashMap;
 import java.util.HashSet;
 import java.util.List;
@@ -38,14 +39,13 @@
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
-import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractLogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AggregateFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
@@ -57,37 +57,32 @@
 public class PushAggregateIntoNestedSubplanRule implements IAlgebraicRewriteRule {
 
     @Override
-    public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
-        return false;
-    }
-
-    @Override
     public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
             throws AlgebricksException {
-        Map<LogicalVariable, Integer> gbyAggVars = new HashMap<LogicalVariable, Integer>();
-        Map<LogicalVariable, Integer> gbyAggVarToPlanIndex = new HashMap<LogicalVariable, Integer>();
-        Map<LogicalVariable, GroupByOperator> gbyWithAgg = new HashMap<LogicalVariable, GroupByOperator>();
-        Map<ILogicalExpression, ILogicalExpression> aggExprToVarExpr = new HashMap<ILogicalExpression, ILogicalExpression>();
+        Map<LogicalVariable, Integer> nspAggVars = new HashMap<>();
+        Map<LogicalVariable, Integer> nspAggVarToPlanIndex = new HashMap<>();
+        Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg = new HashMap<>();
+        Map<ILogicalExpression, ILogicalExpression> aggExprToVarExpr = new HashMap<>();
         // first collect vars. referring to listified sequences
-        boolean changed = collectVarsBottomUp(opRef, context, gbyAggVars, gbyWithAgg, gbyAggVarToPlanIndex,
+        boolean changed = collectVarsBottomUp(opRef, context, nspAggVars, nspWithAgg, nspAggVarToPlanIndex,
                 aggExprToVarExpr);
         if (changed) {
-            removeRedundantListifies(opRef, context, gbyAggVars, gbyWithAgg, gbyAggVarToPlanIndex);
+            removeRedundantListifies(nspAggVars, nspWithAgg, nspAggVarToPlanIndex);
         }
         return changed;
     }
 
-    private void removeRedundantListifies(Mutable<ILogicalOperator> opRef, IOptimizationContext context,
-            Map<LogicalVariable, Integer> gbyAggVars, Map<LogicalVariable, GroupByOperator> gbyWithAgg,
-            Map<LogicalVariable, Integer> gbyAggVarToPlanIndex) throws AlgebricksException {
-        List<Pair<GroupByOperator, Integer>> removeList = new ArrayList<>();
-        for (Map.Entry<LogicalVariable, Integer> aggVarEntry : gbyAggVars.entrySet()) {
+    private void removeRedundantListifies(Map<LogicalVariable, Integer> nspAggVars,
+            Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg,
+            Map<LogicalVariable, Integer> nspAggVarToPlanIndex) throws AlgebricksException {
+        List<Pair<AbstractOperatorWithNestedPlans, Integer>> removeList = new ArrayList<>();
+        for (Map.Entry<LogicalVariable, Integer> aggVarEntry : nspAggVars.entrySet()) {
             LogicalVariable aggVar = aggVarEntry.getKey();
             int occurs = aggVarEntry.getValue();
             if (occurs == 0) {
-                GroupByOperator gbyOp = gbyWithAgg.get(aggVar);
-                AggregateOperator aggOp = (AggregateOperator) gbyOp.getNestedPlans()
-                        .get(gbyAggVarToPlanIndex.get(aggVar)).getRoots().get(0).getValue();
+                AbstractOperatorWithNestedPlans nspOp = nspWithAgg.get(aggVar);
+                AggregateOperator aggOp = (AggregateOperator) nspOp.getNestedPlans()
+                        .get(nspAggVarToPlanIndex.get(aggVar)).getRoots().get(0).getValue();
                 int pos = aggOp.getVariables().indexOf(aggVar);
                 if (pos >= 0) {
                     aggOp.getVariables().remove(pos);
@@ -95,41 +90,42 @@
                     List<LogicalVariable> producedVarsAtAgg = new ArrayList<>();
                     VariableUtilities.getProducedVariablesInDescendantsAndSelf(aggOp, producedVarsAtAgg);
                     if (producedVarsAtAgg.isEmpty()) {
-                        removeList.add(new Pair<>(gbyOp, gbyAggVarToPlanIndex.get(aggVar)));
+                        removeList.add(new Pair<>(nspOp, nspAggVarToPlanIndex.get(aggVar)));
                     }
                 }
             }
         }
 
         // Collects subplans that is to be removed.
-        Map<GroupByOperator, List<ILogicalPlan>> gbyToSubplanListMap = new HashMap<>();
-        for (Pair<GroupByOperator, Integer> remove : removeList) {
-            GroupByOperator groupByOperator = remove.first;
+        Map<AbstractOperatorWithNestedPlans, List<ILogicalPlan>> nspToSubplanListMap = new HashMap<>();
+        for (Pair<AbstractOperatorWithNestedPlans, Integer> remove : removeList) {
+            AbstractOperatorWithNestedPlans groupByOperator = remove.first;
             ILogicalPlan subplan = remove.first.getNestedPlans().get(remove.second);
-            if(gbyToSubplanListMap.containsKey(groupByOperator)) {
-                List<ILogicalPlan> subplans =  gbyToSubplanListMap.get(groupByOperator);
+            if (nspToSubplanListMap.containsKey(groupByOperator)) {
+                List<ILogicalPlan> subplans = nspToSubplanListMap.get(groupByOperator);
                 subplans.add(subplan);
             } else {
                 List<ILogicalPlan> subplans = new ArrayList<>();
                 subplans.add(subplan);
-                gbyToSubplanListMap.put(groupByOperator, subplans);
+                nspToSubplanListMap.put(groupByOperator, subplans);
             }
         }
         // Removes subplans.
-        for(Map.Entry<GroupByOperator, List<ILogicalPlan>> entry: gbyToSubplanListMap.entrySet()){
+        for (Map.Entry<AbstractOperatorWithNestedPlans, List<ILogicalPlan>> entry : nspToSubplanListMap.entrySet()) {
             entry.getKey().getNestedPlans().removeAll(entry.getValue());
         }
     }
 
     private boolean collectVarsBottomUp(Mutable<ILogicalOperator> opRef, IOptimizationContext context,
-            Map<LogicalVariable, Integer> gbyListifyVarsCount, Map<LogicalVariable, GroupByOperator> gbyWithAgg,
-            Map<LogicalVariable, Integer> gbyAggVarToPlanIndex,
+            Map<LogicalVariable, Integer> nspListifyVarsCount,
+            Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg,
+            Map<LogicalVariable, Integer> nspAggVarToPlanIndex,
             Map<ILogicalExpression, ILogicalExpression> aggregateExprToVarExpr) throws AlgebricksException {
         AbstractLogicalOperator op1 = (AbstractLogicalOperator) opRef.getValue();
         context.addToDontApplySet(this, op1);
         boolean change = false;
         for (Mutable<ILogicalOperator> child : op1.getInputs()) {
-            if (collectVarsBottomUp(child, context, gbyListifyVarsCount, gbyWithAgg, gbyAggVarToPlanIndex,
+            if (collectVarsBottomUp(child, context, nspListifyVarsCount, nspWithAgg, nspAggVarToPlanIndex,
                     aggregateExprToVarExpr)) {
                 change = true;
             }
@@ -138,98 +134,108 @@
         VariableUtilities.getUsedVariables(op1, used);
         switch (op1.getOperatorTag()) {
             case ASSIGN:
-            case SELECT: {
+            case SELECT:
                 boolean found = false;
-                // Do some prefiltering: check if the Assign uses any gby vars.
+                // Do some prefiltering: check if the Assign uses any nsp vars.
                 for (LogicalVariable v : used) {
-                    if (gbyListifyVarsCount.get(v) != null) {
+                    if (nspListifyVarsCount.get(v) != null) {
                         found = true;
                         break;
                     }
                 }
-                if (found) {
-                    if (op1.getOperatorTag() == LogicalOperatorTag.ASSIGN) {
-                        AssignOperator assign = (AssignOperator) op1;
-                        for (Mutable<ILogicalExpression> exprRef : assign.getExpressions()) {
-                            Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, gbyWithAgg,
-                                    aggregateExprToVarExpr, context);
-                            if (p.first) {
-                                change = true;
-                                exprRef.setValue(p.second);
-                            }
-                        }
-                    }
-                    if (op1.getOperatorTag() == LogicalOperatorTag.SELECT) {
-                        SelectOperator select = (SelectOperator) op1;
-                        Mutable<ILogicalExpression> exprRef = select.getCondition();
-                        Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, gbyWithAgg,
+                if (!found) {
+                    break;
+                }
+                if (op1.getOperatorTag() == LogicalOperatorTag.ASSIGN) {
+                    AssignOperator assign = (AssignOperator) op1;
+                    for (Mutable<ILogicalExpression> exprRef : assign.getExpressions()) {
+                        Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, nspWithAgg,
                                 aggregateExprToVarExpr, context);
                         if (p.first) {
                             change = true;
                             exprRef.setValue(p.second);
                         }
                     }
-                    used.clear();
-                    VariableUtilities.getUsedVariables(op1, used);
-                    // increment the count for the ones which are still used
-                    for (LogicalVariable v : used) {
-                        Integer m = gbyListifyVarsCount.get(v);
-                        if (m != null) {
-                            gbyListifyVarsCount.put(v, m + 1);
-                        }
+                }
+                if (op1.getOperatorTag() == LogicalOperatorTag.SELECT) {
+                    SelectOperator select = (SelectOperator) op1;
+                    Mutable<ILogicalExpression> exprRef = select.getCondition();
+                    Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, nspWithAgg,
+                            aggregateExprToVarExpr, context);
+                    if (p.first) {
+                        change = true;
+                        exprRef.setValue(p.second);
+                    }
+                }
+                used.clear();
+                VariableUtilities.getUsedVariables(op1, used);
+                // increment the count for the ones which are still used
+                for (LogicalVariable v : used) {
+                    Integer m = nspListifyVarsCount.get(v);
+                    if (m != null) {
+                        nspListifyVarsCount.put(v, m + 1);
                     }
                 }
                 break;
-            }
-            case SUBPLAN: {
+            case SUBPLAN:
+                // Try to push the subplan into a group-by operator if possible.
                 for (LogicalVariable v : used) {
-                    Integer m = gbyListifyVarsCount.get(v);
+                    Integer m = nspListifyVarsCount.get(v);
                     if (m != null) {
-                        GroupByOperator gbyOp = gbyWithAgg.get(v);
-                        if (pushSubplanAsAggIntoGby(opRef, gbyOp, v, gbyListifyVarsCount, gbyWithAgg,
-                                gbyAggVarToPlanIndex, context)) {
+                        AbstractOperatorWithNestedPlans nspOp = nspWithAgg.get(v);
+                        if (pushSubplanAsAggIntoNestedSubplan(opRef, nspOp, v, nspListifyVarsCount, nspWithAgg,
+                                nspAggVarToPlanIndex, context)) {
                             change = true;
                         } else {
-                            gbyListifyVarsCount.put(v, m + 1);
+                            nspListifyVarsCount.put(v, m + 1);
                         }
                     }
                 }
-                break;
-            }
-            case GROUP: {
-                List<LogicalVariable> vars = collectOneVarPerAggFromGroupOp((GroupByOperator) op1);
-                if (vars != null) {
-                    for (int i = 0; i < vars.size(); i++) {
-                        LogicalVariable v = vars.get(i);
-                        if (v != null) {
-                            gbyListifyVarsCount.put(v, 0);
-                            gbyAggVarToPlanIndex.put(v, i);
-                            gbyWithAgg.put(v, (GroupByOperator) op1);
-                        }
-                    }
+                if (!change) {
+                    // Collect aggregate variables for pushing aggregates into the subplan (if possible).
+                    collectAggregateVars(nspListifyVarsCount, nspWithAgg, nspAggVarToPlanIndex,
+                            (AbstractOperatorWithNestedPlans) op1);
                 }
                 break;
-            }
-            default: {
+            case GROUP:
+                // Collect aggregate variables for pushing aggregates into the nested subplan
+                // of the group by operator (if possible).
+                collectAggregateVars(nspListifyVarsCount, nspWithAgg, nspAggVarToPlanIndex,
+                        (AbstractOperatorWithNestedPlans) op1);
+                break;
+            default:
                 for (LogicalVariable v : used) {
-                    Integer m = gbyListifyVarsCount.get(v);
+                    Integer m = nspListifyVarsCount.get(v);
                     if (m != null) {
-                        gbyListifyVarsCount.put(v, m + 1);
+                        nspListifyVarsCount.put(v, m + 1);
                     }
                 }
-            }
         }
         return change;
     }
 
-    private List<LogicalVariable> collectOneVarPerAggFromGroupOp(GroupByOperator group) {
-        List<ILogicalPlan> nPlans = group.getNestedPlans();
-        if (nPlans == null || nPlans.size() < 1) {
-            return null;
+    private void collectAggregateVars(Map<LogicalVariable, Integer> nspListifyVarsCount,
+            Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg,
+            Map<LogicalVariable, Integer> nspAggVarToPlanIndex, AbstractOperatorWithNestedPlans op) {
+        List<LogicalVariable> vars = collectOneVarPerAggFromOpWithNestedPlans(op);
+        for (int i = 0; i < vars.size(); i++) {
+            LogicalVariable v = vars.get(i);
+            if (v != null) {
+                nspListifyVarsCount.put(v, 0);
+                nspAggVarToPlanIndex.put(v, i);
+                nspWithAgg.put(v, op);
+            }
+        }
+    }
+
+    private List<LogicalVariable> collectOneVarPerAggFromOpWithNestedPlans(AbstractOperatorWithNestedPlans op) {
+        List<ILogicalPlan> nPlans = op.getNestedPlans();
+        if (nPlans == null || nPlans.isEmpty()) {
+            return Collections.emptyList();
         }
 
-        List<LogicalVariable> aggVars = new ArrayList<LogicalVariable>();
-        // test that the group-by computes a "listify" aggregate
+        List<LogicalVariable> aggVars = new ArrayList<>();
+        // test that the operator computes a "listify" aggregate
         for (int i = 0; i < nPlans.size(); i++) {
             AbstractLogicalOperator topOp = (AbstractLogicalOperator) nPlans.get(i).getRoots().get(0).getValue();
             if (topOp.getOperatorTag() != LogicalOperatorTag.AGGREGATE) {
@@ -240,7 +246,7 @@
                 continue;
             }
             ILogicalExpression expr = agg.getExpressions().get(0).getValue();
-            if (((AbstractLogicalExpression) expr).getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+            if (expr.getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
                 continue;
             }
             AbstractFunctionCallExpression fceAgg = (AbstractFunctionCallExpression) expr;
@@ -253,9 +259,8 @@
     }
 
     /**
-     * @param expr
-     * @param aggVars
-     * @param gbyWithAgg
+     * @param exprRef
+     * @param nspWithAgg
      * @param context
      * @return a pair whose first member is a boolean which is true iff
      *         something was changed in the expression tree rooted at expr. The
@@ -263,32 +268,32 @@
      * @throws AlgebricksException
      */
     private Pair<Boolean, ILogicalExpression> extractAggFunctionsFromExpression(Mutable<ILogicalExpression> exprRef,
-            Map<LogicalVariable, GroupByOperator> gbyWithAgg,
+            Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg,
             Map<ILogicalExpression, ILogicalExpression> aggregateExprToVarExpr, IOptimizationContext context)
-                    throws AlgebricksException {
+            throws AlgebricksException {
         ILogicalExpression expr = exprRef.getValue();
         switch (expr.getExpressionTag()) {
-            case FUNCTION_CALL: {
+            case FUNCTION_CALL:
                 AbstractFunctionCallExpression fce = (AbstractFunctionCallExpression) expr;
                 FunctionIdentifier fi = AsterixBuiltinFunctions.getAggregateFunction(fce.getFunctionIdentifier());
                 if (fi != null) {
                     ILogicalExpression a1 = fce.getArguments().get(0).getValue();
                     if (a1.getExpressionTag() == LogicalExpressionTag.VARIABLE) {
                         LogicalVariable argVar = ((VariableReferenceExpression) a1).getVariableReference();
-                        GroupByOperator gbyOp = gbyWithAgg.get(argVar);
+                        AbstractOperatorWithNestedPlans nspOp = nspWithAgg.get(argVar);
 
-                        if (gbyOp != null) {
+                        if (nspOp != null) {
                             if (!aggregateExprToVarExpr.containsKey(expr)) {
                                 LogicalVariable newVar = context.newVar();
                                 AggregateFunctionCallExpression aggFun = AsterixBuiltinFunctions
                                         .makeAggregateFunctionExpression(fi, fce.getArguments());
-                                rewriteGroupByAggregate(argVar, gbyOp, aggFun, newVar, context);
+                                rewriteAggregateInNestedSubplan(argVar, nspOp, aggFun, newVar, context);
                                 ILogicalExpression newVarExpr = new VariableReferenceExpression(newVar);
                                 aggregateExprToVarExpr.put(expr, newVarExpr);
-                                return new Pair<Boolean, ILogicalExpression>(Boolean.TRUE, newVarExpr);
+                                return new Pair<>(Boolean.TRUE, newVarExpr);
                             } else {
                                 ILogicalExpression varExpr = aggregateExprToVarExpr.get(expr);
-                                return new Pair<Boolean, ILogicalExpression>(Boolean.TRUE, varExpr);
+                                return new Pair<>(Boolean.TRUE, varExpr);
                             }
                         }
                     }
@@ -296,44 +301,41 @@
 
                 boolean change = false;
                 for (Mutable<ILogicalExpression> a : fce.getArguments()) {
-                    Pair<Boolean, ILogicalExpression> aggArg = extractAggFunctionsFromExpression(a, gbyWithAgg,
+                    Pair<Boolean, ILogicalExpression> aggArg = extractAggFunctionsFromExpression(a, nspWithAgg,
                             aggregateExprToVarExpr, context);
                     if (aggArg.first.booleanValue()) {
                         a.setValue(aggArg.second);
                         change = true;
                     }
                 }
-                return new Pair<Boolean, ILogicalExpression>(change, fce);
-            }
+                return new Pair<>(change, fce);
             case VARIABLE:
-            case CONSTANT: {
-                return new Pair<Boolean, ILogicalExpression>(Boolean.FALSE, expr);
-            }
-            default: {
+            case CONSTANT:
+                return new Pair<>(Boolean.FALSE, expr);
+            default:
                 throw new IllegalArgumentException();
-            }
         }
     }
 
-    private void rewriteGroupByAggregate(LogicalVariable oldAggVar, GroupByOperator gbyOp,
+    private void rewriteAggregateInNestedSubplan(LogicalVariable oldAggVar, AbstractOperatorWithNestedPlans nspOp,
             AggregateFunctionCallExpression aggFun, LogicalVariable newAggVar, IOptimizationContext context)
-                    throws AlgebricksException {
-        for (int j = 0; j < gbyOp.getNestedPlans().size(); j++) {
-            AggregateOperator aggOp = (AggregateOperator) gbyOp.getNestedPlans().get(j).getRoots().get(0).getValue();
+            throws AlgebricksException {
+        for (int j = 0; j < nspOp.getNestedPlans().size(); j++) {
+            AggregateOperator aggOp = (AggregateOperator) nspOp.getNestedPlans().get(j).getRoots().get(0).getValue();
             int n = aggOp.getVariables().size();
             for (int i = 0; i < n; i++) {
                 LogicalVariable v = aggOp.getVariables().get(i);
                 if (v.equals(oldAggVar)) {
                     AbstractFunctionCallExpression oldAggExpr = (AbstractFunctionCallExpression) aggOp.getExpressions()
                             .get(i).getValue();
-                    AggregateFunctionCallExpression newAggFun = AsterixBuiltinFunctions.makeAggregateFunctionExpression(
-                            aggFun.getFunctionIdentifier(), new ArrayList<Mutable<ILogicalExpression>>());
+                    AggregateFunctionCallExpression newAggFun = AsterixBuiltinFunctions
+                            .makeAggregateFunctionExpression(aggFun.getFunctionIdentifier(), new ArrayList<>());
                     for (Mutable<ILogicalExpression> arg : oldAggExpr.getArguments()) {
-                        ILogicalExpression cloned = ((AbstractLogicalExpression) arg.getValue()).cloneExpression();
-                        newAggFun.getArguments().add(new MutableObject<ILogicalExpression>(cloned));
+                        ILogicalExpression cloned = arg.getValue().cloneExpression();
+                        newAggFun.getArguments().add(new MutableObject<>(cloned));
                     }
                     aggOp.getVariables().add(newAggVar);
-                    aggOp.getExpressions().add(new MutableObject<ILogicalExpression>(newAggFun));
+                    aggOp.getExpressions().add(new MutableObject<>(newAggFun));
                     context.computeAndSetTypeEnvironmentForOperator(aggOp);
                     break;
                 }
@@ -341,16 +343,17 @@
         }
     }
 
-    private boolean pushSubplanAsAggIntoGby(Mutable<ILogicalOperator> subplanOpRef, GroupByOperator gbyOp,
-            LogicalVariable varFromGroupAgg, Map<LogicalVariable, Integer> gbyAggVars,
-            Map<LogicalVariable, GroupByOperator> gbyWithAgg, Map<LogicalVariable, Integer> gbyAggVarToPlanIndex,
-            IOptimizationContext context) throws AlgebricksException {
+    private boolean pushSubplanAsAggIntoNestedSubplan(Mutable<ILogicalOperator> subplanOpRef,
+            AbstractOperatorWithNestedPlans nspOp, LogicalVariable varFromNestedAgg,
+            Map<LogicalVariable, Integer> nspAggVars, Map<LogicalVariable, AbstractOperatorWithNestedPlans> nspWithAgg,
+            Map<LogicalVariable, Integer> nspAggVarToPlanIndex, IOptimizationContext context)
+            throws AlgebricksException {
         SubplanOperator subplan = (SubplanOperator) subplanOpRef.getValue();
-        // only free var can be varFromGroupAgg
-        HashSet<LogicalVariable> freeVars = new HashSet<LogicalVariable>();
+        // only free var can be varFromNestedAgg
+        HashSet<LogicalVariable> freeVars = new HashSet<>();
         OperatorPropertiesUtil.getFreeVariablesInSubplans(subplan, freeVars);
         for (LogicalVariable vFree : freeVars) {
-            if (!vFree.equals(varFromGroupAgg)) {
+            if (!vFree.equals(varFromNestedAgg)) {
                 return false;
             }
         }
@@ -375,10 +378,9 @@
             opRef = op.getInputs().get(0);
             op = (AbstractLogicalOperator) opRef.getValue();
             switch (op.getOperatorTag()) {
-                case ASSIGN: {
+                case ASSIGN:
                     break;
-                }
-                case UNNEST: {
+                case UNNEST:
                     UnnestOperator unnest = (UnnestOperator) op;
                     if (unnest.getPositionalVariable() != null) {
                         // TODO currently subplan with both accumulating and running aggregate is not supported.
@@ -397,7 +399,7 @@
                         return false;
                     }
                     VariableReferenceExpression varExpr = (VariableReferenceExpression) arg0;
-                    if (!varExpr.getVariableReference().equals(varFromGroupAgg)) {
+                    if (!varExpr.getVariableReference().equals(varFromNestedAgg)) {
                         return false;
                     }
                     opRef = op.getInputs().get(0);
@@ -408,73 +410,71 @@
                     pushableNestedSubplan = true;
                     unnestVar = unnest.getVariable();
                     break;
-                }
-                default: {
+                default:
                     return false;
-                }
             }
         }
-        if (pushableNestedSubplan) {
-            for (int i = 0; i < gbyOp.getNestedPlans().size(); i++) {
-                Mutable<ILogicalOperator> gbyAggRef = gbyOp.getNestedPlans().get(i).getRoots().get(0);
-                AggregateOperator gbyAgg = (AggregateOperator) gbyAggRef.getValue();
-                Mutable<ILogicalOperator> gbyAggChildRef = gbyAgg.getInputs().get(0);
-                LogicalVariable listifyVar = findListifiedVariable(gbyAgg, varFromGroupAgg);
-                if (listifyVar == null) {
-                    continue;
-                }
-                OperatorManipulationUtil.substituteVarRec(aggInSubplanOp, unnestVar, listifyVar, true, context);
-                gbyAgg.getVariables().addAll(aggInSubplanOp.getVariables());
-                gbyAgg.getExpressions().addAll(aggInSubplanOp.getExpressions());
-                for (LogicalVariable v : aggInSubplanOp.getVariables()) {
-                    gbyWithAgg.put(v, gbyOp);
-                    gbyAggVars.put(v, 0);
-                    gbyAggVarToPlanIndex.put(v, i);
-                }
+        if (!pushableNestedSubplan) {
+            return false;
+        }
 
-                Mutable<ILogicalOperator> opRef1InSubplan = aggInSubplanOp.getInputs().get(0);
-                if (opRef1InSubplan.getValue().getInputs().size() > 0) {
-                    Mutable<ILogicalOperator> opRef2InSubplan = opRef1InSubplan.getValue().getInputs().get(0);
-                    AbstractLogicalOperator op2InSubplan = (AbstractLogicalOperator) opRef2InSubplan.getValue();
-                    if (op2InSubplan.getOperatorTag() != LogicalOperatorTag.NESTEDTUPLESOURCE) {
-                        List<Mutable<ILogicalOperator>> gbyInpList = gbyAgg.getInputs();
-                        gbyInpList.clear();
-                        gbyInpList.add(opRef1InSubplan);
-                        while (true) {
-                            opRef2InSubplan = opRef1InSubplan.getValue().getInputs().get(0);
-                            op2InSubplan = (AbstractLogicalOperator) opRef2InSubplan.getValue();
-                            if (op2InSubplan.getOperatorTag() == LogicalOperatorTag.UNNEST) {
-                                List<Mutable<ILogicalOperator>> opInpList = opRef1InSubplan.getValue().getInputs();
-                                opInpList.clear();
-                                opInpList.add(gbyAggChildRef);
-                                break;
-                            }
-                            opRef1InSubplan = opRef2InSubplan;
-                            if (opRef1InSubplan.getValue().getInputs().size() == 0) {
-                                throw new IllegalStateException("PushAggregateIntoNestedSubplanRule: could not find UNNEST.");
-                            }
+        for (int i = 0; i < nspOp.getNestedPlans().size(); i++) {
+            Mutable<ILogicalOperator> nspAggRef = nspOp.getNestedPlans().get(i).getRoots().get(0);
+            AggregateOperator nspAgg = (AggregateOperator) nspAggRef.getValue();
+            Mutable<ILogicalOperator> nspAggChildRef = nspAgg.getInputs().get(0);
+            LogicalVariable listifyVar = findListifiedVariable(nspAgg, varFromNestedAgg);
+            if (listifyVar == null) {
+                    continue;
+            }
+            OperatorManipulationUtil.substituteVarRec(aggInSubplanOp, unnestVar, listifyVar, true, context);
+            nspAgg.getVariables().addAll(aggInSubplanOp.getVariables());
+            nspAgg.getExpressions().addAll(aggInSubplanOp.getExpressions());
+            for (LogicalVariable v : aggInSubplanOp.getVariables()) {
+                nspWithAgg.put(v, nspOp);
+                nspAggVars.put(v, 0);
+                nspAggVarToPlanIndex.put(v, i);
+            }
+
+            Mutable<ILogicalOperator> opRef1InSubplan = aggInSubplanOp.getInputs().get(0);
+            if (!opRef1InSubplan.getValue().getInputs().isEmpty()) {
+                Mutable<ILogicalOperator> opRef2InSubplan = opRef1InSubplan.getValue().getInputs().get(0);
+                AbstractLogicalOperator op2InSubplan = (AbstractLogicalOperator) opRef2InSubplan.getValue();
+                if (op2InSubplan.getOperatorTag() != LogicalOperatorTag.NESTEDTUPLESOURCE) {
+                    List<Mutable<ILogicalOperator>> nspInpList = nspAgg.getInputs();
+                    nspInpList.clear();
+                    nspInpList.add(opRef1InSubplan);
+                    while (true) {
+                        opRef2InSubplan = opRef1InSubplan.getValue().getInputs().get(0);
+                        op2InSubplan = (AbstractLogicalOperator) opRef2InSubplan.getValue();
+                        if (op2InSubplan.getOperatorTag() == LogicalOperatorTag.UNNEST) {
+                            List<Mutable<ILogicalOperator>> opInpList = opRef1InSubplan.getValue().getInputs();
+                            opInpList.clear();
+                            opInpList.add(nspAggChildRef);
+                            break;
+                        }
+                        opRef1InSubplan = opRef2InSubplan;
+                        if (opRef1InSubplan.getValue().getInputs().isEmpty()) {
+                            throw new IllegalStateException(
+                                        "PushAggregateIntoNestedSubplanRule: could not find UNNEST.");
                         }
                     }
                 }
-                subplanOpRef.setValue(subplan.getInputs().get(0).getValue());
-                OperatorPropertiesUtil.typeOpRec(gbyAggRef, context);
             }
-            return true;
-        } else {
-            return false;
+            subplanOpRef.setValue(subplan.getInputs().get(0).getValue());
+            OperatorPropertiesUtil.typeOpRec(nspAggRef, context);
         }
+        return true;
     }
 
-    private LogicalVariable findListifiedVariable(AggregateOperator gbyAgg, LogicalVariable varFromGroupAgg) {
-        int n = gbyAgg.getVariables().size();
-
+    private LogicalVariable findListifiedVariable(AggregateOperator nspAgg, LogicalVariable varFromNestedAgg) {
+        int n = nspAgg.getVariables().size();
         for (int i = 0; i < n; i++) {
-            if (gbyAgg.getVariables().get(i).equals(varFromGroupAgg)) {
-                AbstractFunctionCallExpression fce = (AbstractFunctionCallExpression) gbyAgg.getExpressions().get(i)
+            if (nspAgg.getVariables().get(i).equals(varFromNestedAgg)) {
+                AbstractFunctionCallExpression fce = (AbstractFunctionCallExpression) nspAgg.getExpressions().get(i)
                         .getValue();
                 if (fce.getFunctionIdentifier().equals(AsterixBuiltinFunctions.LISTIFY)) {
                     ILogicalExpression argExpr = fce.getArguments().get(0).getValue();
-                    if (((AbstractLogicalExpression) argExpr).getExpressionTag() == LogicalExpressionTag.VARIABLE) {
+                    if (argExpr.getExpressionTag() == LogicalExpressionTag.VARIABLE) {
                         return ((VariableReferenceExpression) argExpr).getVariableReference();
                     }
                 }
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
index 2e368c0..9f28515 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/subplan/InlineSubplanInputForNestedTupleSourceRule.java
@@ -325,9 +325,17 @@
                     context);
             changed = changed || resultFromChild.first;
             for (Map.Entry<LogicalVariable, LogicalVariable> entry : resultFromChild.second.entrySet()) {
-                if (liveVars.contains(entry.getKey())) {
-                    // Only needs to map live variables for its ancestors.
-                    replacedVarMapForAncestor.put(entry.getKey(), entry.getValue());
+                LogicalVariable oldVar = entry.getKey();
+                LogicalVariable newVar = entry.getValue();
+                if (liveVars.contains(oldVar)) {
+                    // Maps live variables for its ancestors.
+                    replacedVarMapForAncestor.put(oldVar, newVar);
+                    // Recursively maps live variables for its ancestors.
+                    oldVar = newVar;
+                    while ((newVar = resultFromChild.second.get(newVar)) != null) {
+                        replacedVarMapForAncestor.put(oldVar, newVar);
+                        oldVar = newVar;
+                    }
                 }
             }
             replacedVarMap.putAll(resultFromChild.second);
diff --git a/asterixdb/asterix-app/data/tpcds/catalog_sales.csv b/asterixdb/asterix-app/data/tpcds/catalog_sales.csv
index 100f191..6ae0773 100644
--- a/asterixdb/asterix-app/data/tpcds/catalog_sales.csv
+++ b/asterixdb/asterix-app/data/tpcds/catalog_sales.csv
@@ -1,7 +1,7 @@
-2450815|38212|2450886|62153|1822764|5775|19986|62153|1822764|5775|19986|4|62|3|4|1|196|1|47|27.70|44.32|42.99|62.51|2020.53|1301.90|2083.04|101.02|0.00|1041.52|2020.53|2121.55|3062.05|3163.07|718.63|
-2450815|38212|2450846|62153|1822764|5775|19986|62153|1822764|5775|19986|4|31|8|2|2|270|1|20|87.55|260.89|153.92|2139.40|3078.40|1751.00|5217.80|71.41|1292.92|1356.60|1785.48|1856.89|3142.08|3213.49|34.48|
-2450815|38212|2450868|62153|1822764|5775|19986|62153|1822764|5775|19986|4|76|2|2|3|97|1|19|69.86|88.72|29.27|1129.55|556.13|1327.34|1685.68|33.36|0.00|168.53|556.13|589.49|724.66|758.02|-771.21|
-2450815|38212|2450851|62153|1822764|5775|19986|62153|1822764|5775|19986|4|89|15|2|2|284|2|50|70.00|205.10|188.69|820.50|9434.50|3500.00|10255.00|377.38|0.00|4307.00|9434.50|9811.88|13741.50|14118.88|5934.50|
+2450815|38212|2450886|1|1822764|5775|19986|1|1822764|5775|19986|4|62|3|4|1|196|1|47|27.70|44.32|42.99|62.51|2020.53|1301.90|2083.04|101.02|0.00|1041.52|2020.53|2121.55|3062.05|3163.07|718.63|
+2450815|38212|2450846|1|1822764|5775|19986|1|1822764|5775|19986|4|31|8|2|2|270|1|20|87.55|260.89|153.92|2139.40|3078.40|1751.00|5217.80|71.41|1292.92|1356.60|1785.48|1856.89|3142.08|3213.49|34.48|
+2450815|38212|2450868|1|1822764|5775|19986|1|1822764|5775|19986|4|76|2|2|3|97|1|19|69.86|88.72|29.27|1129.55|556.13|1327.34|1685.68|33.36|0.00|168.53|556.13|589.49|724.66|758.02|-771.21|
+2450815|38212|2450851|1|1822764|5775|19986|1|1822764|5775|19986|4|89|15|2|2|284|2|50|70.00|205.10|188.69|820.50|9434.50|3500.00|10255.00|377.38|0.00|4307.00|9434.50|9811.88|13741.50|14118.88|5934.50|
 2450815|29485|2450904|14601|797995|6189|9583|14601|797995|6189|9583|1|64|18|3|4|176|2|56|67.54|166.82|18.35|8314.32|1027.60|3782.24|9341.92|0.00|0.00|3736.32|1027.60|1027.60|4763.92|4763.92|-2754.64|
 2450815|29485|2450890|14601|797995|6189|9583|14601|797995|6189|9583|1|75|8|1|5|278|2|88|20.08|60.03|20.41|3486.56|1796.08|1767.04|5282.64|13.82|1598.51|1056.00|197.57|211.39|1253.57|1267.39|-1569.47|
 2450815|29485|2450849|14601|797995|6189|9583|14601|797995|6189|9583|1|39|4|3|6|207|2|31|40.88|51.91|6.22|1416.39|192.82|1267.28|1609.21|11.56|0.00|321.78|192.82|204.38|514.60|526.16|-1074.46|
diff --git a/asterixdb/asterix-app/data/tpcds/date_dim.csv b/asterixdb/asterix-app/data/tpcds/date_dim.csv
index 3aa8a8f..5300c68 100644
--- a/asterixdb/asterix-app/data/tpcds/date_dim.csv
+++ b/asterixdb/asterix-app/data/tpcds/date_dim.csv
@@ -1,4 +1,4 @@
-2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
+2450815|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
 2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
 2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
 2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
diff --git a/asterixdb/asterix-app/data/tpcds/store_sales.csv b/asterixdb/asterix-app/data/tpcds/store_sales.csv
index 29a1f05..7d94449 100644
--- a/asterixdb/asterix-app/data/tpcds/store_sales.csv
+++ b/asterixdb/asterix-app/data/tpcds/store_sales.csv
@@ -1,11 +1,11 @@
-2451293|43503|1|1|518725|1359|31593|8|39|239999|20|61.65|61.65|16.64|0.00|332.80|1233.00|1233.00|0.00|0.00|332.80|332.80|-900.20|
-2451293|43503|3|1|518725|1359|31593|8|104|239999|98|55.42|110.84|70.93|0.00|6951.14|5431.16|10862.32|139.02|0.00|6951.14|7090.16|1519.98|
-2451293|43503|5|1|518725|1359|31593|8|294|239999|22|20.66|24.58|9.34|16.43|205.48|454.52|540.76|1.89|16.43|189.05|190.94|-265.47|
-2451293|43503|7|1|518725|1359|31593|8|137|239999|42|12.62|18.29|1.09|0.00|45.78|530.04|768.18|2.28|0.00|45.78|48.06|-484.26|
-2451293||9||518725|||||239999|76||23.04||0.00||1260.08|||0.00|297.16|303.10||
-2451293|43503|11|1|518725|1359|31593|8|256|239999|13|23.87|39.62|5.54|12.96|72.02|310.31|515.06|1.77|12.96|59.06|60.83|-251.25|
-2451293|43503|13|1|518725|1359|31593|8|128|239999|2|88.60|151.50|133.32|0.00|266.64|177.20|303.00|13.33|0.00|266.64|279.97|89.44|
-2451293|43503|15|1|518725|1359|31593|8|266|239999|13|60.52|95.62|13.38|0.00|173.94|786.76|1243.06|12.17|0.00|173.94|186.11|-612.82|
+2450815|43503|1|1|518725|1359|31593|8|39|239999|20|61.65|61.65|16.64|0.00|332.80|1233.00|1233.00|0.00|0.00|332.80|332.80|-900.20|
+2450815|43503|3|1|518725|1359|31593|8|104|239999|98|55.42|110.84|70.93|0.00|6951.14|5431.16|10862.32|139.02|0.00|6951.14|7090.16|1519.98|
+2450815|43503|5|1|518725|1359|31593|8|294|239999|22|20.66|24.58|9.34|16.43|205.48|454.52|540.76|1.89|16.43|189.05|190.94|-265.47|
+2450815|43503|7|1|518725|1359|31593|8|137|239999|42|12.62|18.29|1.09|0.00|45.78|530.04|768.18|2.28|0.00|45.78|48.06|-484.26|
+2450815||9||518725|||||239999|76||23.04||0.00||1260.08|||0.00|297.16|303.10||
+2450815|43503|11|1|518725|1359|31593|8|256|239999|13|23.87|39.62|5.54|12.96|72.02|310.31|515.06|1.77|12.96|59.06|60.83|-251.25|
+2450815|43503|13|1|518725|1359|31593|8|128|239999|2|88.60|151.50|133.32|0.00|266.64|177.20|303.00|13.33|0.00|266.64|279.97|89.44|
+2450815|43503|15|1|518725|1359|31593|8|266|239999|13|60.52|95.62|13.38|0.00|173.94|786.76|1243.06|12.17|0.00|173.94|186.11|-612.82|
 2451293|43503|17|1|518725|1359|31593|8|179|239999|45|93.14|95.00|4.75|0.00|213.75|4191.30|4275.00|19.23|0.00|213.75|232.98|-3977.55|
 2451176|47181|2|10|1873544|2153|1962|10|92|240000|30|67.43|84.96|37.38|583.12|1121.40|2022.90|2548.80|5.38|583.12|538.28|543.66|-1484.62|
 2451176|47181|4|10|1873544|2153|1962|10|143|240000|14|51.64|66.61|8.65|0.00|121.10|722.96|932.54|8.47|0.00|121.10|129.57|-601.86|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-1572.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-1572.sqlpp
new file mode 100644
index 0000000..1bafb45
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/subquery/query-ASTERIXDB-1572.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+drop dataverse sampdb if exists;
+create dataverse sampdb;
+use sampdb;
+
+drop dataset samptable if exists;
+drop type samptabletype if exists;
+
+create type samptabletype as closed {
+  id: int64
+};
+
+create dataset samptable(samptabletype) primary key id;
+
+select *
+from
+(
+  select id from samptable
+  where (id in [0] and id in [1])
+        or (id in [1] and id in [2])
+) st1;
+
+drop dataverse sampdb;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1591.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1591.sqlpp
new file mode 100644
index 0000000..42b9462
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1591.sqlpp
@@ -0,0 +1,306 @@
+/*
+ * 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.
+ */
+
+
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type customer_address_type as closed {
+    ca_address_sk:              int64,
+    ca_address_id:              string,
+    ca_street_number:           string?,
+    ca_street_name:             string?,
+    ca_street_type:             string?,
+    ca_suite_number:            string?,
+    ca_city:                    string?,
+    ca_county:                  string?,
+    ca_state:                   string?,
+    ca_zip:                     string?,
+    ca_country:                 string?,
+    ca_gmt_offset:              double?,
+    ca_location_type:           string?
+}
+
+create type customer_type as closed {
+    c_customer_sk:             int64,
+    c_customer_id:             string,
+    c_current_cdemo_sk:        int64?,
+    c_current_hdemo_sk:        int64?,
+    c_current_addr_sk:         int64?,
+    c_first_shipto_date_sk:    int64?,
+    c_first_sales_date_sk:     int64?,
+    c_salutation:              string?,
+    c_first_name:              string?,
+    c_last_name:               string?,
+    c_preferred_cust_flag:     string?,
+    c_birth_day:               int64?,
+    c_birth_month:             int64?,
+    c_birth_year:              int64?,
+    c_birth_country:           string?,
+    c_login:                   string?,
+    c_email_address:           string?,
+    c_last_review_date:        string?
+}
+
+create type store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+create type catalog_sales_type as closed {
+    cs_sold_date_sk:           int64?,
+    cs_sold_time_sk:           int64?,
+    cs_ship_date_sk:           int64?,
+    cs_bill_customer_sk:       int64?,
+    cs_bill_cdemo_sk:          int64?,
+    cs_bill_hdemo_sk:          int64?,
+    cs_bill_addr_sk:           int64?,
+    cs_ship_customer_sk:       int64?,
+    cs_ship_cdemo_sk:          int64?,
+    cs_ship_hdemo_sk:          int64?,
+    cs_ship_addr_sk:           int64?,
+    cs_call_center_sk:         int64?,
+    cs_catalog_page_sk:        int64?,
+    cs_ship_mode_sk:           int64?,
+    cs_warehouse_sk:           int64?,
+    cs_item_sk:                int64,
+    cs_promo_sk:               int64?,
+    cs_order_number:           int64,
+    cs_quantity:               int64?,
+    cs_wholesale_cost:         double?,
+    cs_list_price:             double?,
+    cs_sales_price:            double?,
+    cs_ext_discount_amt:       double?,
+    cs_ext_sales_price:        double?,
+    cs_ext_wholesale_cost:     double?,
+    cs_ext_list_price:         double?,
+    cs_ext_tax:                double?,
+    cs_coupon_amt:             double?,
+    cs_ext_ship_cost:          double?,
+    cs_net_paid:               double?,
+    cs_net_paid_inc_tax:       double?,
+    cs_net_paid_inc_ship:      double?,
+    cs_net_paid_inc_ship_tax:  double?,
+    cs_net_profit:             double?
+}
+
+create type catalog_returns_type as closed {
+    cr_returned_date_sk:       int64?,
+    cr_returned_time_sk:       int64?,
+    cr_item_sk:                int64,
+    cr_refunded_customer_sk:   int64?,
+    cr_refunded_cdemo_sk:      int64?,
+    cr_refunded_hdemo_sk:      int64?,
+    cr_refunded_addr_sk:       int64?,
+    cr_returning_customer_sk:  int64?,
+    cr_returning_cdemo_sk:     int64?,
+    cr_returning_hdemo_sk:     int64?,
+    cr_returning_addr_sk:      int64?,
+    cr_call_center_sk:         int64?,
+    cr_catalog_page_sk:        int64?,
+    cr_ship_mode_sk:           int64?,
+    cr_warehouse_sk:           int64?,
+    cr_reason_sk:              int64?,
+    cr_order_number:           int64,
+    cr_return_quantity:        int64?,
+    cr_return_amount:          double?,
+    cr_return_tax:             double?,
+    cr_return_amt_inc_tax:     double?,
+    cr_fee:                    double?,
+    cr_return_ship_cost:       double?,
+    cr_refunded_cash:          double?,
+    cr_reversed_charge:        double?,
+    cr_store_credit:           double?,
+    cr_net_loss:               double?
+}
+
+create type tpcds.date_dim_type as closed {
+    d_date_sk:                 int64,
+    d_date_id:                 string,
+    d_date:                    string? ,
+    d_month_seq:               int64?,
+    d_week_seq:                int64?,
+    d_quarter_seq:             int64?,
+    d_year:                    int64?,
+    d_dow:                     int64?,
+    d_moy:                     int64?,
+    d_dom:                     int64?,
+    d_qoy:                     int64?,
+    d_fy_year:                 int64?,
+    d_fy_quarter_seq:          int64?,
+    d_fy_week_seq:             int64?,
+    d_day_name:                string?,
+    d_quarter_name:            string?,
+    d_holiday:                 string?,
+    d_weekend:                 string?,
+    d_following_holiday:       string?,
+    d_first_dom:               int64?,
+    d_last_dom:                int64?,
+    d_same_day_ly:             int64?,
+    d_same_day_lq:             int64?,
+    d_current_day:             string?,
+    d_current_week:            string?,
+    d_current_month:           string?,
+    d_current_quarter:         string?,
+    d_current_year:            string?
+}
+
+create type item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create type web_sales_type as closed {
+    ws_sold_date_sk:           int64?,
+    ws_sold_time_sk:           int64?,
+    ws_ship_date_sk:           int64?,
+    ws_item_sk:                int64,
+    ws_bill_customer_sk:       int64?,
+    ws_bill_cdemo_sk:          int64?,
+    ws_bill_hdemo_sk:          int64?,
+    ws_bill_addr_sk:           int64?,
+    ws_ship_customer_sk:       int64?,
+    ws_ship_cdemo_sk:          int64?,
+    ws_ship_hdemo_sk:          int64?,
+    ws_ship_addr_sk:           int64?,
+    ws_web_page_sk:            int64?,
+    ws_web_site_sk:            int64?,
+    ws_ship_mode_sk:           int64?,
+    ws_warehouse_sk:           int64?,
+    ws_promo_sk:               int64?,
+    ws_order_number:           int64,
+    ws_quantity:               int64?,
+    ws_wholesale_cost:         double?,
+    ws_list_price:             double?,
+    ws_sales_price:            double?,
+    ws_ext_discount_amt:       double?,
+    ws_ext_sales_price:        double?,
+    ws_ext_wholesale_cost:     double?,
+    ws_ext_list_price:         double?,
+    ws_ext_tax:                double?,
+    ws_coupon_amt:             double?,
+    ws_ext_ship_cost:          double?,
+    ws_net_paid:               double?,
+    ws_net_paid_inc_tax:       double?,
+    ws_net_paid_inc_ship:      double?,
+    ws_net_paid_inc_ship_tax:  double?,
+    ws_net_profit:             double?
+}
+
+create dataset customer (customer_type)
+primary key c_customer_sk;
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset customer_address(customer_address_type)
+primary key ca_address_sk;
+
+create dataset catalog_sales (catalog_sales_type)
+primary key cs_item_sk, cs_order_number;
+
+create dataset catalog_returns (catalog_returns_type)
+primary key cr_item_sk, cr_order_number;
+
+create dataset item (item_type)
+primary key i_item_sk;
+
+create dataset date_dim(date_dim_type)
+primary key d_date_sk;
+
+create dataset web_sales (web_sales_type)
+primary key ws_item_sk, ws_order_number;
+
+select *
+from
+  customer c,customer_address ca
+where
+  c.c_current_addr_sk = ca.ca_address_sk and
+  exists (select *
+          from store_sales ss1,date_dim dd1
+          where c.c_customer_sk = ss1.ss_customer_sk and
+                ss1.ss_sold_date_sk = dd1.d_date_sk and
+                dd1.d_year = 1900 and
+                dd1.d_qoy < 4)
+                and
+                (
+                  exists (select *
+                          from web_sales ws1,date_dim dd1
+                          where c.c_customer_sk = ws1.ws_bill_customer_sk and
+                                ws1.ws_sold_date_sk = dd1.d_date_sk and
+                                dd1.d_year = 1900 and
+                                dd1.d_qoy < 4
+                         )
+                  or
+                  exists (select *
+                          from catalog_sales cs1,date_dim dd1
+                          where c.c_customer_sk = cs1.cs_ship_customer_sk and
+                                cs1.cs_sold_date_sk = dd1.d_date_sk and
+                                dd1.d_year = 1900 and
+                                dd1.d_qoy < 4
+                         )
+                 )
+order by c.c_customer_sk
+limit 100;
+
+drop dataverse tpcds;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/inverted-index-join/issue741.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/inverted-index-join/issue741.plan
index 5b08bf5..4e40dd2 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/inverted-index-join/issue741.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/inverted-index-join/issue741.plan
@@ -13,33 +13,20 @@
               -- STABLE_SORT [$$25(ASC)]  |PARTITIONED|
                 -- HASH_PARTITION_EXCHANGE [$$25]  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$36][$$25]  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            -- STREAM_SELECT  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
-                                -- ASSIGN  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$25]  |PARTITIONED|
-                          -- STREAM_PROJECT  |PARTITIONED|
-                            -- STREAM_SELECT  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
+                          -- BTREE_SEARCH  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STABLE_SORT [$$39(ASC)]  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- BTREE_SEARCH  |PARTITIONED|
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$39(ASC)]  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- LENGTH_PARTITIONED_INVERTED_INDEX_SEARCH  |PARTITIONED|
-                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- STREAM_SELECT  |PARTITIONED|
-                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                    -- ASSIGN  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- LENGTH_PARTITIONED_INVERTED_INDEX_SEARCH  |PARTITIONED|
+                                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- STREAM_SELECT  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-159-3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-159-3.plan
index 7699b8e..60de69a 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-159-3.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-159-3.plan
@@ -10,7 +10,7 @@
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                     -- STREAM_PROJECT  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- HYBRID_HASH_JOIN [$$29][$$37]  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$29][$$38]  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ASSIGN  |PARTITIONED|
@@ -27,37 +27,35 @@
                                                     -- DATASOURCE_SCAN  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                          -- HASH_PARTITION_EXCHANGE [$$37]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$38]  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- STREAM_SELECT  |PARTITIONED|
-                                -- STREAM_PROJECT  |PARTITIONED|
-                                  -- ASSIGN  |PARTITIONED|
-                                    -- SUBPLAN  |PARTITIONED|
-                                            {
-                                              -- AGGREGATE  |LOCAL|
-                                                -- UNNEST  |LOCAL|
-                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                            }
+                                -- SUBPLAN  |PARTITIONED|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- UNNEST  |LOCAL|
+                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- NESTED_LOOP  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- NESTED_LOOP  |PARTITIONED|
+                                        -- SPLIT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- SPLIT  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- SPLIT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- SPLIT  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- DATASOURCE_SCAN  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
-                                            -- SPLIT  |PARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                      -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                        -- SPLIT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                -- DATASOURCE_SCAN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
index 008339a..366d67c 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
@@ -9,53 +9,57 @@
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
             -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$83(ASC)] HASH:[$$83]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$11]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$58]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                       }
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
-                    -- STREAM_SELECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
-                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- PRE_CLUSTERED_GROUP_BY[$$80]  |PARTITIONED|
-                                  {
-                                    -- AGGREGATE  |LOCAL|
-                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                  }
-                            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$80(ASC)] HASH:[$$80]  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$75]  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$80]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
-                                          -- STREAM_SELECT  |LOCAL|
-                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- STABLE_SORT [$$75(ASC)]  |PARTITIONED|
+                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$80(ASC)] HASH:[$$80]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$76]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- STREAM_SELECT  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
+                                      -- STABLE_SORT [$$76(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$62][$$69]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$62]  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- HYBRID_HASH_JOIN [$$65][$$11]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
-                                                        -- UNNEST  |UNPARTITIONED|
-                                                          -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                          -- ASSIGN  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$69]  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$62][$$70]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$62]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$65][$$11]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
+                                                              -- UNNEST  |UNPARTITIONED|
+                                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$70]  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
index 54716d3..fcb9d0d 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
@@ -3,12 +3,12 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$104]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$104(ASC)] HASH:[$$104]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$105(ASC)] HASH:[$$105]  |PARTITIONED|
               -- SORT_GROUP_BY[$$81]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
@@ -21,37 +21,38 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$101]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$102]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$101(ASC)] HASH:[$$101]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$94]  |PARTITIONED|
+                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$102(ASC)] HASH:[$$102]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$95]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- STREAM_SELECT  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$94(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$95(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$89][$$86]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$90][$$87]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$90]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- NESTED_LOOP  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ASSIGN  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                           -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                             -- STREAM_PROJECT  |UNPARTITIONED|
                                                               -- ASSIGN  |UNPARTITIONED|
@@ -66,7 +67,7 @@
                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$87]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
index 14b3f08..2ef9a24 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
@@ -3,12 +3,12 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$106]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$105(ASC)] HASH:[$$105]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$106(ASC)] HASH:[$$106]  |PARTITIONED|
               -- SORT_GROUP_BY[$$82]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
@@ -21,37 +21,38 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$102]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$103]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$102(ASC)] HASH:[$$102]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$95]  |PARTITIONED|
+                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$103(ASC)] HASH:[$$103]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$96]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- STREAM_SELECT  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$95(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$96(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$90][$$87]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$90]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$91][$$88]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$91]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- NESTED_LOOP  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
-                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- ASSIGN  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                           -- BROADCAST_EXCHANGE  |PARTITIONED|
                                                             -- STREAM_PROJECT  |UNPARTITIONED|
                                                               -- ASSIGN  |UNPARTITIONED|
@@ -66,7 +67,7 @@
                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$87]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$88]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-1572.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-1572.plan
new file mode 100644
index 0000000..66736df
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/query-ASTERIXDB-1572.plan
@@ -0,0 +1,78 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- STREAM_SELECT  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- PRE_CLUSTERED_GROUP_BY[$$50]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [$$50(ASC)]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$50][$$11]  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$50]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$48]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- STREAM_SELECT  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STABLE_SORT [$$48(ASC)]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$48][$$10]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$48]  |PARTITIONED|
+                                              -- PRE_CLUSTERED_GROUP_BY[$$46]  |PARTITIONED|
+                                                      {
+                                                        -- AGGREGATE  |LOCAL|
+                                                          -- STREAM_SELECT  |LOCAL|
+                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                      }
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STABLE_SORT [$$46(ASC)]  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$46][$$9]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$46]  |PARTITIONED|
+                                                              -- PRE_CLUSTERED_GROUP_BY[$$39]  |PARTITIONED|
+                                                                      {
+                                                                        -- AGGREGATE  |LOCAL|
+                                                                          -- STREAM_SELECT  |LOCAL|
+                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                      }
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- HYBRID_HASH_JOIN [$$39][$$8]  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$8]  |PARTITIONED|
+                                                                          -- ASSIGN  |UNPARTITIONED|
+                                                                            -- UNNEST  |UNPARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$9]  |PARTITIONED|
+                                                              -- ASSIGN  |UNPARTITIONED|
+                                                                -- UNNEST  |UNPARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
+                                              -- ASSIGN  |UNPARTITIONED|
+                                                -- UNNEST  |UNPARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$11]  |PARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- UNNEST  |UNPARTITIONED|
+                                  -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
index 234c91b..0d1b1cc 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
@@ -8,7 +8,7 @@
               -- ASSIGN  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$117]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$119]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- AGGREGATE  |LOCAL|
@@ -18,13 +18,13 @@
                                         -- NESTED_TUPLE_SOURCE  |LOCAL|
                             }
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- STABLE_SORT [$$117(ASC)]  |PARTITIONED|
+                        -- STABLE_SORT [$$119(ASC)]  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- HYBRID_HASH_JOIN [$$117][$$118]  |PARTITIONED|
-                                  -- HASH_PARTITION_EXCHANGE [$$117]  |PARTITIONED|
-                                    -- PRE_CLUSTERED_GROUP_BY[$$104]  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$119][$$120]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$119]  |PARTITIONED|
+                                    -- PRE_CLUSTERED_GROUP_BY[$$107]  |PARTITIONED|
                                             {
                                               -- AGGREGATE  |LOCAL|
                                                 -- AGGREGATE  |LOCAL|
@@ -34,12 +34,12 @@
                                                         -- NESTED_TUPLE_SOURCE  |LOCAL|
                                             }
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- STABLE_SORT [$$104(ASC)]  |PARTITIONED|
+                                        -- STABLE_SORT [$$107(ASC)]  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                             -- STREAM_PROJECT  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- HYBRID_HASH_JOIN [$$104][$$105]  |PARTITIONED|
-                                                  -- HASH_PARTITION_EXCHANGE [$$104]  |PARTITIONED|
+                                                -- HYBRID_HASH_JOIN [$$107][$$108]  |PARTITIONED|
+                                                  -- HASH_PARTITION_EXCHANGE [$$107]  |PARTITIONED|
                                                     -- PRE_CLUSTERED_GROUP_BY[$$87]  |PARTITIONED|
                                                             {
                                                               -- AGGREGATE  |LOCAL|
@@ -50,7 +50,7 @@
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- HYBRID_HASH_JOIN [$$87][$$94]  |PARTITIONED|
+                                                            -- HYBRID_HASH_JOIN [$$87][$$97]  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ASSIGN  |PARTITIONED|
@@ -71,7 +71,7 @@
                                                                   -- ASSIGN  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                       -- SPLIT  |PARTITIONED|
-                                                                        -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$144]  |PARTITIONED|
                                                                           -- ASSIGN  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                               -- ASSIGN  |PARTITIONED|
@@ -83,23 +83,23 @@
                                                                                           -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                  -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
+                                                  -- HASH_PARTITION_EXCHANGE [$$108]  |PARTITIONED|
                                                     -- ASSIGN  |PARTITIONED|
                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$98][$$97]  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$101][$$100]  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- ASSIGN  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- SPLIT  |PARTITIONED|
-                                                                      -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$138]  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ASSIGN  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                               -- STREAM_SELECT  |PARTITIONED|
                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  -- PRE_CLUSTERED_GROUP_BY[$$142]  |PARTITIONED|
+                                                                                  -- PRE_CLUSTERED_GROUP_BY[$$143]  |PARTITIONED|
                                                                                           {
                                                                                             -- AGGREGATE  |LOCAL|
                                                                                               -- AGGREGATE  |LOCAL|
@@ -109,7 +109,7 @@
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                          -- HYBRID_HASH_JOIN [$$142][$$143]  |PARTITIONED|
+                                                                                          -- HYBRID_HASH_JOIN [$$143][$$144]  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                               -- SPLIT  |PARTITIONED|
                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -119,7 +119,7 @@
                                                                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                               -- SPLIT  |PARTITIONED|
-                                                                                                -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                                                                                                -- HASH_PARTITION_EXCHANGE [$$144]  |PARTITIONED|
                                                                                                   -- ASSIGN  |PARTITIONED|
                                                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                                                       -- ASSIGN  |PARTITIONED|
@@ -131,7 +131,7 @@
                                                                                                                   -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$100]  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- ASSIGN  |PARTITIONED|
                                                                   -- STREAM_PROJECT  |PARTITIONED|
@@ -144,13 +144,13 @@
                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                  -- HASH_PARTITION_EXCHANGE [$$118]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$120]  |PARTITIONED|
                                     -- ASSIGN  |PARTITIONED|
                                       -- STREAM_PROJECT  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$100][$$99]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$100]  |PARTITIONED|
-                                              -- PRE_CLUSTERED_GROUP_BY[$$119]  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$103][$$102]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$103]  |PARTITIONED|
+                                              -- PRE_CLUSTERED_GROUP_BY[$$121]  |PARTITIONED|
                                                       {
                                                         -- AGGREGATE  |LOCAL|
                                                           -- AGGREGATE  |LOCAL|
@@ -159,18 +159,18 @@
                                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                       }
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- STABLE_SORT [$$119(ASC)]  |PARTITIONED|
+                                                  -- STABLE_SORT [$$121(ASC)]  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- HYBRID_HASH_JOIN [$$119][$$122]  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$119]  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$121][$$124]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- STREAM_SELECT  |PARTITIONED|
                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                     -- ASSIGN  |PARTITIONED|
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- PRE_CLUSTERED_GROUP_BY[$$123]  |PARTITIONED|
+                                                                        -- PRE_CLUSTERED_GROUP_BY[$$125]  |PARTITIONED|
                                                                                 {
                                                                                   -- AGGREGATE  |LOCAL|
                                                                                     -- AGGREGATE  |LOCAL|
@@ -180,7 +180,7 @@
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                -- HYBRID_HASH_JOIN [$$123][$$124]  |PARTITIONED|
+                                                                                -- HYBRID_HASH_JOIN [$$125][$$126]  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- SPLIT  |PARTITIONED|
                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -198,7 +198,7 @@
                                                                                       -- ASSIGN  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- SPLIT  |PARTITIONED|
-                                                                                            -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                                                                                            -- HASH_PARTITION_EXCHANGE [$$144]  |PARTITIONED|
                                                                                               -- ASSIGN  |PARTITIONED|
                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                                   -- ASSIGN  |PARTITIONED|
@@ -210,20 +210,20 @@
                                                                                                               -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$122]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$124]  |PARTITIONED|
                                                               -- ASSIGN  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- HYBRID_HASH_JOIN [$$137][$$136]  |PARTITIONED|
+                                                                    -- HYBRID_HASH_JOIN [$$138][$$137]  |PARTITIONED|
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                         -- SPLIT  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$138]  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                               -- ASSIGN  |PARTITIONED|
                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                   -- STREAM_SELECT  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                      -- PRE_CLUSTERED_GROUP_BY[$$142]  |PARTITIONED|
+                                                                                      -- PRE_CLUSTERED_GROUP_BY[$$143]  |PARTITIONED|
                                                                                               {
                                                                                                 -- AGGREGATE  |LOCAL|
                                                                                                   -- AGGREGATE  |LOCAL|
@@ -233,7 +233,7 @@
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                              -- HYBRID_HASH_JOIN [$$142][$$143]  |PARTITIONED|
+                                                                                              -- HYBRID_HASH_JOIN [$$143][$$144]  |PARTITIONED|
                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                   -- SPLIT  |PARTITIONED|
                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -243,7 +243,7 @@
                                                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                   -- SPLIT  |PARTITIONED|
-                                                                                                    -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                                                                                                    -- HASH_PARTITION_EXCHANGE [$$144]  |PARTITIONED|
                                                                                                       -- ASSIGN  |PARTITIONED|
                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                                                           -- ASSIGN  |PARTITIONED|
@@ -255,7 +255,7 @@
                                                                                                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                      -- HASH_PARTITION_EXCHANGE [$$136]  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ASSIGN  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -266,7 +266,7 @@
                                                                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$99]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$102]  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ASSIGN  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
index c09dedb..bfcdf43 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
@@ -16,7 +16,7 @@
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ASSIGN  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- PRE_CLUSTERED_GROUP_BY[$$114]  |PARTITIONED|
+                      -- PRE_CLUSTERED_GROUP_BY[$$116]  |PARTITIONED|
                               {
                                 -- AGGREGATE  |LOCAL|
                                   -- AGGREGATE  |LOCAL|
@@ -26,12 +26,12 @@
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                               }
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          -- STABLE_SORT [$$114(ASC)]  |PARTITIONED|
+                          -- STABLE_SORT [$$116(ASC)]  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- HYBRID_HASH_JOIN [$$114][$$115]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$114]  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$116][$$117]  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$116]  |PARTITIONED|
                                       -- PRE_CLUSTERED_GROUP_BY[$$54]  |PARTITIONED|
                                               {
                                                 -- AGGREGATE  |LOCAL|
@@ -46,13 +46,13 @@
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- HYBRID_HASH_JOIN [$$54][$$106]  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$54][$$109]  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                         -- ASSIGN  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- SPLIT  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$116]  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$118]  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |UNPARTITIONED|
                                                                   -- ASSIGN  |UNPARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
@@ -70,7 +70,7 @@
                                                                                             -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$106]  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$109]  |PARTITIONED|
                                                       -- ASSIGN  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- STREAM_SELECT  |PARTITIONED|
@@ -101,7 +101,7 @@
                                                                                                             -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$115]  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$117]  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- STREAM_SELECT  |PARTITIONED|
@@ -115,7 +115,7 @@
                                                           -- STREAM_SELECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- PRE_CLUSTERED_GROUP_BY[$$116]  |PARTITIONED|
+                                                                -- PRE_CLUSTERED_GROUP_BY[$$118]  |PARTITIONED|
                                                                         {
                                                                           -- AGGREGATE  |LOCAL|
                                                                             -- AGGREGATE  |LOCAL|
@@ -124,14 +124,14 @@
                                                                                   -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                                         }
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- STABLE_SORT [$$116(ASC)]  |PARTITIONED|
+                                                                    -- STABLE_SORT [$$118(ASC)]  |PARTITIONED|
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- HYBRID_HASH_JOIN [$$116][$$117]  |PARTITIONED|
+                                                                            -- HYBRID_HASH_JOIN [$$118][$$119]  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- SPLIT  |PARTITIONED|
-                                                                                  -- HASH_PARTITION_EXCHANGE [$$116]  |PARTITIONED|
+                                                                                  -- HASH_PARTITION_EXCHANGE [$$118]  |PARTITIONED|
                                                                                     -- STREAM_PROJECT  |UNPARTITIONED|
                                                                                       -- ASSIGN  |UNPARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
@@ -149,7 +149,7 @@
                                                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                              -- HASH_PARTITION_EXCHANGE [$$117]  |PARTITIONED|
+                                                                              -- HASH_PARTITION_EXCHANGE [$$119]  |PARTITIONED|
                                                                                 -- ASSIGN  |PARTITIONED|
                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                     -- STREAM_SELECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1591.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1591.plan
new file mode 100644
index 0000000..fcb3481
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1591.plan
@@ -0,0 +1,151 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- STREAM_PROJECT  |PARTITIONED|
+            -- SORT_MERGE_EXCHANGE [$$120(ASC) ]  |PARTITIONED|
+              -- STREAM_LIMIT  |PARTITIONED|
+                -- STREAM_PROJECT  |PARTITIONED|
+                  -- STREAM_SELECT  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- PRE_CLUSTERED_GROUP_BY[$$129, $$130]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$129(ASC), $$130(ASC)] HASH:[$$129, $$130]  |PARTITIONED|
+                            -- PRE_CLUSTERED_GROUP_BY[$$117, $$118]  |PARTITIONED|
+                                    {
+                                      -- AGGREGATE  |LOCAL|
+                                        -- STREAM_SELECT  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                    }
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- STABLE_SORT [$$117(ASC), $$118(ASC)]  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$117][$$99]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$117]  |PARTITIONED|
+                                            -- PRE_CLUSTERED_GROUP_BY[$$126, $$127]  |PARTITIONED|
+                                                    {
+                                                      -- AGGREGATE  |LOCAL|
+                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                    }
+                                              -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$126(ASC), $$127(ASC)] HASH:[$$126, $$127]  |PARTITIONED|
+                                                -- PRE_CLUSTERED_GROUP_BY[$$114, $$115]  |PARTITIONED|
+                                                        {
+                                                          -- AGGREGATE  |LOCAL|
+                                                            -- STREAM_SELECT  |LOCAL|
+                                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                        }
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- STABLE_SORT [$$114(ASC), $$115(ASC)]  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- HYBRID_HASH_JOIN [$$114][$$97]  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$114]  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- STREAM_SELECT  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- PRE_CLUSTERED_GROUP_BY[$$123, $$124]  |PARTITIONED|
+                                                                              {
+                                                                                -- AGGREGATE  |LOCAL|
+                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                              }
+                                                                        -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$123(ASC), $$124(ASC)] HASH:[$$123, $$124]  |PARTITIONED|
+                                                                          -- PRE_CLUSTERED_GROUP_BY[$$78, $$79]  |PARTITIONED|
+                                                                                  {
+                                                                                    -- AGGREGATE  |LOCAL|
+                                                                                      -- STREAM_SELECT  |LOCAL|
+                                                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                  }
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- STABLE_SORT [$$78(ASC), $$79(ASC)]  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- HYBRID_HASH_JOIN [$$78][$$95]  |PARTITIONED|
+                                                                                        -- HASH_PARTITION_EXCHANGE [$$78]  |PARTITIONED|
+                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- HYBRID_HASH_JOIN [$$89][$$79]  |PARTITIONED|
+                                                                                                -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                        -- HASH_PARTITION_EXCHANGE [$$95]  |PARTITIONED|
+                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- HYBRID_HASH_JOIN [$$100][$$82]  |PARTITIONED|
+                                                                                                  -- HASH_PARTITION_EXCHANGE [$$100]  |PARTITIONED|
+                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                    -- SPLIT  |PARTITIONED|
+                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                        -- STREAM_SELECT  |PARTITIONED|
+                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                            -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$97]  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- HYBRID_HASH_JOIN [$$102][$$85]  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$102]  |PARTITIONED|
+                                                                          -- ASSIGN  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- SPLIT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$99]  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$104][$$88]  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$104]  |PARTITIONED|
+                                                      -- ASSIGN  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- DATASOURCE_SCAN  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- SPLIT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- STREAM_SELECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
index 5558ad4..946bd7c 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1020.plan
@@ -27,7 +27,7 @@
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- NESTED_LOOP  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- ASSIGN  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- DATASOURCE_SCAN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1308-1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1308-1.plan
index 99240dc..f6ee3fa 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1308-1.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1308-1.plan
@@ -5,27 +5,27 @@
         -- STREAM_PROJECT  |UNPARTITIONED|
           -- STREAM_SELECT  |UNPARTITIONED|
             -- STREAM_PROJECT  |UNPARTITIONED|
-              -- ASSIGN  |UNPARTITIONED|
+              -- UNNEST  |UNPARTITIONED|
                 -- STREAM_PROJECT  |UNPARTITIONED|
-                  -- UNNEST  |UNPARTITIONED|
-                    -- SUBPLAN  |UNPARTITIONED|
-                            {
-                              -- AGGREGATE  |UNPARTITIONED|
-                                -- IN_MEMORY_STABLE_SORT [$$28(ASC)]  |UNPARTITIONED|
+                  -- SUBPLAN  |UNPARTITIONED|
+                          {
+                            -- AGGREGATE  |UNPARTITIONED|
+                              -- IN_MEMORY_STABLE_SORT [$$28(ASC)]  |UNPARTITIONED|
+                                -- UNNEST  |UNPARTITIONED|
+                                  -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
+                          }
+                    -- AGGREGATE  |UNPARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                        -- STABLE_SORT [$$59(ASC)]  |UNPARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+                            -- STREAM_PROJECT  |UNPARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- STREAM_PROJECT  |UNPARTITIONED|
                                   -- UNNEST  |UNPARTITIONED|
-                                    -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
-                            }
-                      -- AGGREGATE  |UNPARTITIONED|
-                        -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-                          -- STABLE_SORT [$$59(ASC)]  |UNPARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
-                              -- STREAM_PROJECT  |UNPARTITIONED|
-                                -- ASSIGN  |UNPARTITIONED|
-                                  -- STREAM_PROJECT  |UNPARTITIONED|
-                                    -- UNNEST  |UNPARTITIONED|
-                                      -- SUBPLAN  |UNPARTITIONED|
-                                              {
-                                                -- AGGREGATE  |UNPARTITIONED|
+                                    -- SUBPLAN  |UNPARTITIONED|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- AGGREGATE  |LOCAL|
                                                   -- STREAM_SELECT  |UNPARTITIONED|
                                                     -- UNNEST  |UNPARTITIONED|
                                                       -- SUBPLAN  |UNPARTITIONED|
@@ -36,6 +36,6 @@
                                                                       -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
                                                               }
                                                         -- NESTED_TUPLE_SOURCE  |UNPARTITIONED|
-                                              }
-                                        -- ASSIGN  |UNPARTITIONED|
-                                          -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                            }
+                                      -- ASSIGN  |UNPARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.ddl.sqlpp
new file mode 100644
index 0000000..18e3e80
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.ddl.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+
+drop dataverse sampdb if exists;
+create dataverse sampdb;
+use sampdb;
+
+drop dataset samptable if exists;
+drop type samptabletype if exists;
+
+create type samptabletype as closed {
+  id: int64
+};
+
+create dataset samptable(samptabletype) primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.2.update.sqlpp
new file mode 100644
index 0000000..2cc27cb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.2.update.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * 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.
+ */
+
+use sampdb;
+
+insert into samptable ({'id' : 0});
+insert into samptable ({'id' : 1});
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.3.query.sqlpp
new file mode 100644
index 0000000..a4b144f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.3.query.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+use sampdb;
+
+select *
+from
+(
+  select id from samptable
+  where (id in [0] and id in [1])
+        or (id in [1] and id in [2])
+) st1;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.ddl.sqlpp
new file mode 100644
index 0000000..c0e9f7c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.ddl.sqlpp
@@ -0,0 +1,270 @@
+/*
+ * 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.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type customer_address_type as closed {
+    ca_address_sk:              int64,
+    ca_address_id:              string,
+    ca_street_number:           string?,
+    ca_street_name:             string?,
+    ca_street_type:             string?,
+    ca_suite_number:            string?,
+    ca_city:                    string?,
+    ca_county:                  string?,
+    ca_state:                   string?,
+    ca_zip:                     string?,
+    ca_country:                 string?,
+    ca_gmt_offset:              double?,
+    ca_location_type:           string?
+}
+
+create type customer_type as closed {
+    c_customer_sk:             int64,
+    c_customer_id:             string,
+    c_current_cdemo_sk:        int64?,
+    c_current_hdemo_sk:        int64?,
+    c_current_addr_sk:         int64?,
+    c_first_shipto_date_sk:    int64?,
+    c_first_sales_date_sk:     int64?,
+    c_salutation:              string?,
+    c_first_name:              string?,
+    c_last_name:               string?,
+    c_preferred_cust_flag:     string?,
+    c_birth_day:               int64?,
+    c_birth_month:             int64?,
+    c_birth_year:              int64?,
+    c_birth_country:           string?,
+    c_login:                   string?,
+    c_email_address:           string?,
+    c_last_review_date:        string?
+}
+
+create type store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+create type catalog_sales_type as closed {
+    cs_sold_date_sk:           int64?,
+    cs_sold_time_sk:           int64?,
+    cs_ship_date_sk:           int64?,
+    cs_bill_customer_sk:       int64?,
+    cs_bill_cdemo_sk:          int64?,
+    cs_bill_hdemo_sk:          int64?,
+    cs_bill_addr_sk:           int64?,
+    cs_ship_customer_sk:       int64?,
+    cs_ship_cdemo_sk:          int64?,
+    cs_ship_hdemo_sk:          int64?,
+    cs_ship_addr_sk:           int64?,
+    cs_call_center_sk:         int64?,
+    cs_catalog_page_sk:        int64?,
+    cs_ship_mode_sk:           int64?,
+    cs_warehouse_sk:           int64?,
+    cs_item_sk:                int64,
+    cs_promo_sk:               int64?,
+    cs_order_number:           int64,
+    cs_quantity:               int64?,
+    cs_wholesale_cost:         double?,
+    cs_list_price:             double?,
+    cs_sales_price:            double?,
+    cs_ext_discount_amt:       double?,
+    cs_ext_sales_price:        double?,
+    cs_ext_wholesale_cost:     double?,
+    cs_ext_list_price:         double?,
+    cs_ext_tax:                double?,
+    cs_coupon_amt:             double?,
+    cs_ext_ship_cost:          double?,
+    cs_net_paid:               double?,
+    cs_net_paid_inc_tax:       double?,
+    cs_net_paid_inc_ship:      double?,
+    cs_net_paid_inc_ship_tax:  double?,
+    cs_net_profit:             double?
+}
+
+create type catalog_returns_type as closed {
+    cr_returned_date_sk:       int64?,
+    cr_returned_time_sk:       int64?,
+    cr_item_sk:                int64,
+    cr_refunded_customer_sk:   int64?,
+    cr_refunded_cdemo_sk:      int64?,
+    cr_refunded_hdemo_sk:      int64?,
+    cr_refunded_addr_sk:       int64?,
+    cr_returning_customer_sk:  int64?,
+    cr_returning_cdemo_sk:     int64?,
+    cr_returning_hdemo_sk:     int64?,
+    cr_returning_addr_sk:      int64?,
+    cr_call_center_sk:         int64?,
+    cr_catalog_page_sk:        int64?,
+    cr_ship_mode_sk:           int64?,
+    cr_warehouse_sk:           int64?,
+    cr_reason_sk:              int64?,
+    cr_order_number:           int64,
+    cr_return_quantity:        int64?,
+    cr_return_amount:          double?,
+    cr_return_tax:             double?,
+    cr_return_amt_inc_tax:     double?,
+    cr_fee:                    double?,
+    cr_return_ship_cost:       double?,
+    cr_refunded_cash:          double?,
+    cr_reversed_charge:        double?,
+    cr_store_credit:           double?,
+    cr_net_loss:               double?
+}
+
+create type tpcds.date_dim_type as closed {
+    d_date_sk:                 int64,
+    d_date_id:                 string,
+    d_date:                    string? ,
+    d_month_seq:               int64?,
+    d_week_seq:                int64?,
+    d_quarter_seq:             int64?,
+    d_year:                    int64?,
+    d_dow:                     int64?,
+    d_moy:                     int64?,
+    d_dom:                     int64?,
+    d_qoy:                     int64?,
+    d_fy_year:                 int64?,
+    d_fy_quarter_seq:          int64?,
+    d_fy_week_seq:             int64?,
+    d_day_name:                string?,
+    d_quarter_name:            string?,
+    d_holiday:                 string?,
+    d_weekend:                 string?,
+    d_following_holiday:       string?,
+    d_first_dom:               int64?,
+    d_last_dom:                int64?,
+    d_same_day_ly:             int64?,
+    d_same_day_lq:             int64?,
+    d_current_day:             string?,
+    d_current_week:            string?,
+    d_current_month:           string?,
+    d_current_quarter:         string?,
+    d_current_year:            string?
+}
+
+create type item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create type web_sales_type as closed {
+    ws_sold_date_sk:           int64?,
+    ws_sold_time_sk:           int64?,
+    ws_ship_date_sk:           int64?,
+    ws_item_sk:                int64,
+    ws_bill_customer_sk:       int64?,
+    ws_bill_cdemo_sk:          int64?,
+    ws_bill_hdemo_sk:          int64?,
+    ws_bill_addr_sk:           int64?,
+    ws_ship_customer_sk:       int64?,
+    ws_ship_cdemo_sk:          int64?,
+    ws_ship_hdemo_sk:          int64?,
+    ws_ship_addr_sk:           int64?,
+    ws_web_page_sk:            int64?,
+    ws_web_site_sk:            int64?,
+    ws_ship_mode_sk:           int64?,
+    ws_warehouse_sk:           int64?,
+    ws_promo_sk:               int64?,
+    ws_order_number:           int64,
+    ws_quantity:               int64?,
+    ws_wholesale_cost:         double?,
+    ws_list_price:             double?,
+    ws_sales_price:            double?,
+    ws_ext_discount_amt:       double?,
+    ws_ext_sales_price:        double?,
+    ws_ext_wholesale_cost:     double?,
+    ws_ext_list_price:         double?,
+    ws_ext_tax:                double?,
+    ws_coupon_amt:             double?,
+    ws_ext_ship_cost:          double?,
+    ws_net_paid:               double?,
+    ws_net_paid_inc_tax:       double?,
+    ws_net_paid_inc_ship:      double?,
+    ws_net_paid_inc_ship_tax:  double?,
+    ws_net_profit:             double?
+}
+
+create dataset customer (customer_type)
+primary key c_customer_sk;
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset customer_address(customer_address_type)
+primary key ca_address_sk;
+
+create dataset catalog_sales (catalog_sales_type)
+primary key cs_item_sk, cs_order_number;
+
+create dataset catalog_returns (catalog_returns_type)
+primary key cr_item_sk, cr_order_number;
+
+create dataset item (item_type)
+primary key i_item_sk;
+
+create dataset date_dim(date_dim_type)
+primary key d_date_sk;
+
+create dataset web_sales (web_sales_type)
+primary key ws_item_sk, ws_order_number;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.2.update.sqlpp
new file mode 100644
index 0000000..9401cee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.2.update.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+use tpcds;
+
+load  dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset catalog_sales using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset catalog_returns using localfs ((`path`=`asterix_nc1://data/tpcds/catalog_returns.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.3.query.sqlpp
new file mode 100644
index 0000000..6bff3f1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.3.query.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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.
+ */
+
+use tpcds;
+
+select *
+from
+  customer c,customer_address ca
+where
+  c.c_current_addr_sk = ca.ca_address_sk and
+  exists (select *
+          from store_sales ss1,date_dim dd1
+          where c.c_customer_sk = ss1.ss_customer_sk and
+                ss1.ss_sold_date_sk = dd1.d_date_sk and
+                dd1.d_year = 1900 and
+                dd1.d_qoy < 4)
+                and
+                (
+                  exists (select *
+                          from web_sales ws1,date_dim dd1
+                          where c.c_customer_sk = ws1.ws_bill_customer_sk and
+                                ws1.ws_sold_date_sk = dd1.d_date_sk and
+                                dd1.d_year = 1900 and
+                                dd1.d_qoy < 4
+                         )
+                  or
+                  exists (select *
+                          from catalog_sales cs1,date_dim dd1
+                          where c.c_customer_sk = cs1.cs_ship_customer_sk and
+                                cs1.cs_sold_date_sk = dd1.d_date_sk and
+                                dd1.d_year = 1900 and
+                                dd1.d_qoy < 4
+                         )
+                 )
+order by c.c_customer_sk
+limit 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.adm
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/subquery/query-ASTERIXDB-1572/query-ASTERIXDB-1572.1.adm
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.adm
new file mode 100644
index 0000000..d2aaa79
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1591/query-ASTERIXDB-1591.1.adm
@@ -0,0 +1 @@
+{ "c": { "c_customer_sk": 1, "c_customer_id": "AAAAAAAABAAAAAAA", "c_current_cdemo_sk": 980124, "c_current_hdemo_sk": 7135, "c_current_addr_sk": 13513, "c_first_shipto_date_sk": 2452238, "c_first_sales_date_sk": 2452208, "c_salutation": "Mr.", "c_first_name": "Javier", "c_last_name": "Lewis", "c_preferred_cust_flag": "Y", "c_birth_day": 9, "c_birth_month": 12, "c_birth_year": 1936, "c_birth_country": "CHILE", "c_login": null, "c_email_address": "Javier.Lewis@VFAxlnZEvOx.org", "c_last_review_date": "2452508" }, "ca": { "ca_address_sk": 13513, "ca_address_id": "AAAAAAAAJMEDAAAA", "ca_street_number": "545", "ca_street_name": "Meadow ", "ca_street_type": "RD", "ca_suite_number": "Suite X", "ca_city": "Crystal", "ca_county": "DeKalb County", "ca_state": "MO", "ca_zip": "65258", "ca_country": "United States", "ca_gmt_offset": -6.0, "ca_location_type": "condo" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1596/query-ASTERIXDB-1596.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1596/query-ASTERIXDB-1596.1.adm
index 4fef5d9..6c8c02c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1596/query-ASTERIXDB-1596.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1596/query-ASTERIXDB-1596.1.adm
@@ -1,8 +1,8 @@
-{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450886, "cs_bill_customer_sk": 62153, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 62153, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 62, "cs_ship_mode_sk": 3, "cs_warehouse_sk": 4, "cs_item_sk": 1, "cs_promo_sk": 196, "cs_order_number": 1, "cs_quantity": 47, "cs_wholesale_cost": 27.7, "cs_list_price": 44.32, "cs_sales_price": 42.99, "cs_ext_discount_amt": 62.51, "cs_ext_sales_price": 2020.53, "cs_ext_wholesale_cost": 1301.9, "cs_ext_list_price": 2083.04, "cs_ext_tax": 101.02, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 1041.52, "cs_net_paid": 2020.53, "cs_net_paid_inc_tax": 2121.55, "cs_net_paid_inc_ship": 3062.05, "cs_net_paid_inc_ship_tax": 3163.07, "cs_net_profit": 718.63 }, "i1": { "i_item_sk": 1, "i_item_id": "AAAAAAABAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": null, "i_item_desc": "Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el", "i_current_price": 27.02, "i_wholesale_cost": 23.23, "i_brand_id": 5003002, "i_brand": "exportischolar #2", "i_class_id": 3, "i_class": "pop", "i_category_id": 5, "i_category": "Music", "i_manufact_id": 52, "i_manufact": "ableanti", "i_size": "N/A", "i_formulation": "3663peru009490160959", "i_color": "spring", "i_units": "Tsp", "i_container": "Unknown", "i_manager_id": 6, "i_product_name": "ought" } }
+{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450886, "cs_bill_customer_sk": 1, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 1, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 62, "cs_ship_mode_sk": 3, "cs_warehouse_sk": 4, "cs_item_sk": 1, "cs_promo_sk": 196, "cs_order_number": 1, "cs_quantity": 47, "cs_wholesale_cost": 27.7, "cs_list_price": 44.32, "cs_sales_price": 42.99, "cs_ext_discount_amt": 62.51, "cs_ext_sales_price": 2020.53, "cs_ext_wholesale_cost": 1301.9, "cs_ext_list_price": 2083.04, "cs_ext_tax": 101.02, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 1041.52, "cs_net_paid": 2020.53, "cs_net_paid_inc_tax": 2121.55, "cs_net_paid_inc_ship": 3062.05, "cs_net_paid_inc_ship_tax": 3163.07, "cs_net_profit": 718.63 }, "i1": { "i_item_sk": 1, "i_item_id": "AAAAAAABAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": null, "i_item_desc": "Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el", "i_current_price": 27.02, "i_wholesale_cost": 23.23, "i_brand_id": 5003002, "i_brand": "exportischolar #2", "i_class_id": 3, "i_class": "pop", "i_category_id": 5, "i_category": "Music", "i_manufact_id": 52, "i_manufact": "ableanti", "i_size": "N/A", "i_formulation": "3663peru009490160959", "i_color": "spring", "i_units": "Tsp", "i_container": "Unknown", "i_manager_id": 6, "i_product_name": "ought" } }
 { "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 29485, "cs_ship_date_sk": 2450862, "cs_bill_customer_sk": 14601, "cs_bill_cdemo_sk": 797995, "cs_bill_hdemo_sk": 6189, "cs_bill_addr_sk": 9583, "cs_ship_customer_sk": 14601, "cs_ship_cdemo_sk": 797995, "cs_ship_hdemo_sk": 6189, "cs_ship_addr_sk": 9583, "cs_call_center_sk": 1, "cs_catalog_page_sk": 23, "cs_ship_mode_sk": 16, "cs_warehouse_sk": 5, "cs_item_sk": 1, "cs_promo_sk": 272, "cs_order_number": 3, "cs_quantity": 45, "cs_wholesale_cost": 9.54, "cs_list_price": 23.37, "cs_sales_price": 21.5, "cs_ext_discount_amt": 84.15, "cs_ext_sales_price": 967.5, "cs_ext_wholesale_cost": 429.3, "cs_ext_list_price": 1051.65, "cs_ext_tax": 21.76, "cs_coupon_amt": 725.62, "cs_ext_ship_cost": 294.3, "cs_net_paid": 241.88, "cs_net_paid_inc_tax": 263.64, "cs_net_paid_inc_ship": 536.18, "cs_net_paid_inc_ship_tax": 557.94, "cs_net_profit": -187.42 }, "i1": { "i_item_sk": 1, "i_item_id": "AAAAAAABAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": null, "i_item_desc": "Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el", "i_current_price": 27.02, "i_wholesale_cost": 23.23, "i_brand_id": 5003002, "i_brand": "exportischolar #2", "i_class_id": 3, "i_class": "pop", "i_category_id": 5, "i_category": "Music", "i_manufact_id": 52, "i_manufact": "ableanti", "i_size": "N/A", "i_formulation": "3663peru009490160959", "i_color": "spring", "i_units": "Tsp", "i_container": "Unknown", "i_manager_id": 6, "i_product_name": "ought" } }
-{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450846, "cs_bill_customer_sk": 62153, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 62153, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 31, "cs_ship_mode_sk": 8, "cs_warehouse_sk": 2, "cs_item_sk": 2, "cs_promo_sk": 270, "cs_order_number": 1, "cs_quantity": 20, "cs_wholesale_cost": 87.55, "cs_list_price": 260.89, "cs_sales_price": 153.92, "cs_ext_discount_amt": 2139.4, "cs_ext_sales_price": 3078.4, "cs_ext_wholesale_cost": 1751.0, "cs_ext_list_price": 5217.8, "cs_ext_tax": 71.41, "cs_coupon_amt": 1292.92, "cs_ext_ship_cost": 1356.6, "cs_net_paid": 1785.48, "cs_net_paid_inc_tax": 1856.89, "cs_net_paid_inc_ship": 3142.08, "cs_net_paid_inc_ship_tax": 3213.49, "cs_net_profit": 34.48 }, "i1": { "i_item_sk": 2, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": "2000-10-26", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 1.12, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "amalgamalg #1", "i_class_id": 1, "i_class": "dresses", "i_category_id": 1, "i_category": "Women", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "petite", "i_formulation": "516steel060826230906", "i_color": "rosy", "i_units": "Bunch", "i_container": "Unknown", "i_manager_id": 98, "i_product_name": "able" } }
-{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450851, "cs_bill_customer_sk": 62153, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 62153, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 89, "cs_ship_mode_sk": 15, "cs_warehouse_sk": 2, "cs_item_sk": 2, "cs_promo_sk": 284, "cs_order_number": 2, "cs_quantity": 50, "cs_wholesale_cost": 70.0, "cs_list_price": 205.1, "cs_sales_price": 188.69, "cs_ext_discount_amt": 820.5, "cs_ext_sales_price": 9434.5, "cs_ext_wholesale_cost": 3500.0, "cs_ext_list_price": 10255.0, "cs_ext_tax": 377.38, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 4307.0, "cs_net_paid": 9434.5, "cs_net_paid_inc_tax": 9811.88, "cs_net_paid_inc_ship": 13741.5, "cs_net_paid_inc_ship_tax": 14118.88, "cs_net_profit": 5934.5 }, "cr1": { "cr_returned_date_sk": 2450926, "cr_returned_time_sk": 45816, "cr_item_sk": 2, "cr_refunded_customer_sk": 14601, "cr_refunded_cdemo_sk": 797995, "cr_refunded_hdemo_sk": 6189, "cr_refunded_addr_sk": 9583, "cr_returning_customer_sk": 14601, "cr_returning_cdemo_sk": 797995, "cr_returning_hdemo_sk": 4703, "cr_returning_addr_sk": 9583, "cr_call_center_sk": 1, "cr_catalog_page_sk": 106, "cr_ship_mode_sk": 2, "cr_warehouse_sk": 2, "cr_reason_sk": 30, "cr_order_number": 2, "cr_return_quantity": 47, "cr_return_amount": 3888.31, "cr_return_tax": 233.29, "cr_return_amt_inc_tax": 4121.6, "cr_fee": 91.23, "cr_return_ship_cost": 1348.9, "cr_refunded_cash": 3577.24, "cr_reversed_charge": 186.64, "cr_store_credit": 124.43, "cr_net_loss": 1673.42 }, "i1": { "i_item_sk": 2, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": "2000-10-26", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 1.12, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "amalgamalg #1", "i_class_id": 1, "i_class": "dresses", "i_category_id": 1, "i_category": "Women", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "petite", "i_formulation": "516steel060826230906", "i_color": "rosy", "i_units": "Bunch", "i_container": "Unknown", "i_manager_id": 98, "i_product_name": "able" } }
-{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450868, "cs_bill_customer_sk": 62153, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 62153, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 76, "cs_ship_mode_sk": 2, "cs_warehouse_sk": 2, "cs_item_sk": 3, "cs_promo_sk": 97, "cs_order_number": 1, "cs_quantity": 19, "cs_wholesale_cost": 69.86, "cs_list_price": 88.72, "cs_sales_price": 29.27, "cs_ext_discount_amt": 1129.55, "cs_ext_sales_price": 556.13, "cs_ext_wholesale_cost": 1327.34, "cs_ext_list_price": 1685.68, "cs_ext_tax": 33.36, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 168.53, "cs_net_paid": 556.13, "cs_net_paid_inc_tax": 589.49, "cs_net_paid_inc_ship": 724.66, "cs_net_paid_inc_ship_tax": 758.02, "cs_net_profit": -771.21 }, "i1": { "i_item_sk": 3, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "2000-10-27", "i_rec_end_date": null, "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 7.11, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "brandbrand #4", "i_class_id": 7, "i_class": "decor", "i_category_id": 7, "i_category": "Home", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "N/A", "i_formulation": "516steel060826230906", "i_color": "sienna", "i_units": "Cup", "i_container": "Unknown", "i_manager_id": 18, "i_product_name": "pri" } }
+{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450846, "cs_bill_customer_sk": 1, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 1, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 31, "cs_ship_mode_sk": 8, "cs_warehouse_sk": 2, "cs_item_sk": 2, "cs_promo_sk": 270, "cs_order_number": 1, "cs_quantity": 20, "cs_wholesale_cost": 87.55, "cs_list_price": 260.89, "cs_sales_price": 153.92, "cs_ext_discount_amt": 2139.4, "cs_ext_sales_price": 3078.4, "cs_ext_wholesale_cost": 1751.0, "cs_ext_list_price": 5217.8, "cs_ext_tax": 71.41, "cs_coupon_amt": 1292.92, "cs_ext_ship_cost": 1356.6, "cs_net_paid": 1785.48, "cs_net_paid_inc_tax": 1856.89, "cs_net_paid_inc_ship": 3142.08, "cs_net_paid_inc_ship_tax": 3213.49, "cs_net_profit": 34.48 }, "i1": { "i_item_sk": 2, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": "2000-10-26", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 1.12, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "amalgamalg #1", "i_class_id": 1, "i_class": "dresses", "i_category_id": 1, "i_category": "Women", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "petite", "i_formulation": "516steel060826230906", "i_color": "rosy", "i_units": "Bunch", "i_container": "Unknown", "i_manager_id": 98, "i_product_name": "able" } }
+{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450851, "cs_bill_customer_sk": 1, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 1, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 89, "cs_ship_mode_sk": 15, "cs_warehouse_sk": 2, "cs_item_sk": 2, "cs_promo_sk": 284, "cs_order_number": 2, "cs_quantity": 50, "cs_wholesale_cost": 70.0, "cs_list_price": 205.1, "cs_sales_price": 188.69, "cs_ext_discount_amt": 820.5, "cs_ext_sales_price": 9434.5, "cs_ext_wholesale_cost": 3500.0, "cs_ext_list_price": 10255.0, "cs_ext_tax": 377.38, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 4307.0, "cs_net_paid": 9434.5, "cs_net_paid_inc_tax": 9811.88, "cs_net_paid_inc_ship": 13741.5, "cs_net_paid_inc_ship_tax": 14118.88, "cs_net_profit": 5934.5 }, "cr1": { "cr_returned_date_sk": 2450926, "cr_returned_time_sk": 45816, "cr_item_sk": 2, "cr_refunded_customer_sk": 14601, "cr_refunded_cdemo_sk": 797995, "cr_refunded_hdemo_sk": 6189, "cr_refunded_addr_sk": 9583, "cr_returning_customer_sk": 14601, "cr_returning_cdemo_sk": 797995, "cr_returning_hdemo_sk": 4703, "cr_returning_addr_sk": 9583, "cr_call_center_sk": 1, "cr_catalog_page_sk": 106, "cr_ship_mode_sk": 2, "cr_warehouse_sk": 2, "cr_reason_sk": 30, "cr_order_number": 2, "cr_return_quantity": 47, "cr_return_amount": 3888.31, "cr_return_tax": 233.29, "cr_return_amt_inc_tax": 4121.6, "cr_fee": 91.23, "cr_return_ship_cost": 1348.9, "cr_refunded_cash": 3577.24, "cr_reversed_charge": 186.64, "cr_store_credit": 124.43, "cr_net_loss": 1673.42 }, "i1": { "i_item_sk": 2, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": "2000-10-26", "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 1.12, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "amalgamalg #1", "i_class_id": 1, "i_class": "dresses", "i_category_id": 1, "i_category": "Women", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "petite", "i_formulation": "516steel060826230906", "i_color": "rosy", "i_units": "Bunch", "i_container": "Unknown", "i_manager_id": 98, "i_product_name": "able" } }
+{ "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 38212, "cs_ship_date_sk": 2450868, "cs_bill_customer_sk": 1, "cs_bill_cdemo_sk": 1822764, "cs_bill_hdemo_sk": 5775, "cs_bill_addr_sk": 19986, "cs_ship_customer_sk": 1, "cs_ship_cdemo_sk": 1822764, "cs_ship_hdemo_sk": 5775, "cs_ship_addr_sk": 19986, "cs_call_center_sk": 4, "cs_catalog_page_sk": 76, "cs_ship_mode_sk": 2, "cs_warehouse_sk": 2, "cs_item_sk": 3, "cs_promo_sk": 97, "cs_order_number": 1, "cs_quantity": 19, "cs_wholesale_cost": 69.86, "cs_list_price": 88.72, "cs_sales_price": 29.27, "cs_ext_discount_amt": 1129.55, "cs_ext_sales_price": 556.13, "cs_ext_wholesale_cost": 1327.34, "cs_ext_list_price": 1685.68, "cs_ext_tax": 33.36, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 168.53, "cs_net_paid": 556.13, "cs_net_paid_inc_tax": 589.49, "cs_net_paid_inc_ship": 724.66, "cs_net_paid_inc_ship_tax": 758.02, "cs_net_profit": -771.21 }, "i1": { "i_item_sk": 3, "i_item_id": "AAAAAAAACAAAAAAA", "i_rec_start_date": "2000-10-27", "i_rec_end_date": null, "i_item_desc": "False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, ", "i_current_price": 7.11, "i_wholesale_cost": 0.38, "i_brand_id": 1001001, "i_brand": "brandbrand #4", "i_class_id": 7, "i_class": "decor", "i_category_id": 7, "i_category": "Home", "i_manufact_id": 294, "i_manufact": "esen stable", "i_size": "N/A", "i_formulation": "516steel060826230906", "i_color": "sienna", "i_units": "Cup", "i_container": "Unknown", "i_manager_id": 18, "i_product_name": "pri" } }
 { "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 29485, "cs_ship_date_sk": 2450904, "cs_bill_customer_sk": 14601, "cs_bill_cdemo_sk": 797995, "cs_bill_hdemo_sk": 6189, "cs_bill_addr_sk": 9583, "cs_ship_customer_sk": 14601, "cs_ship_cdemo_sk": 797995, "cs_ship_hdemo_sk": 6189, "cs_ship_addr_sk": 9583, "cs_call_center_sk": 1, "cs_catalog_page_sk": 64, "cs_ship_mode_sk": 18, "cs_warehouse_sk": 3, "cs_item_sk": 4, "cs_promo_sk": 176, "cs_order_number": 2, "cs_quantity": 56, "cs_wholesale_cost": 67.54, "cs_list_price": 166.82, "cs_sales_price": 18.35, "cs_ext_discount_amt": 8314.32, "cs_ext_sales_price": 1027.6, "cs_ext_wholesale_cost": 3782.24, "cs_ext_list_price": 9341.92, "cs_ext_tax": 0.0, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 3736.32, "cs_net_paid": 1027.6, "cs_net_paid_inc_tax": 1027.6, "cs_net_paid_inc_ship": 4763.92, "cs_net_paid_inc_ship_tax": 4763.92, "cs_net_profit": -2754.64 }, "cr1": { "cr_returned_date_sk": 2450946, "cr_returned_time_sk": 74710, "cr_item_sk": 4, "cr_refunded_customer_sk": 14601, "cr_refunded_cdemo_sk": 797995, "cr_refunded_hdemo_sk": 6189, "cr_refunded_addr_sk": 9583, "cr_returning_customer_sk": 82809, "cr_returning_cdemo_sk": 665550, "cr_returning_hdemo_sk": 991, "cr_returning_addr_sk": 14832, "cr_call_center_sk": 1, "cr_catalog_page_sk": 17, "cr_ship_mode_sk": 2, "cr_warehouse_sk": 5, "cr_reason_sk": 6, "cr_order_number": 2, "cr_return_quantity": 49, "cr_return_amount": 2490.18, "cr_return_tax": 99.6, "cr_return_amt_inc_tax": 2589.78, "cr_fee": 52.54, "cr_return_ship_cost": 1867.39, "cr_refunded_cash": 323.72, "cr_reversed_charge": 931.57, "cr_store_credit": 1234.89, "cr_net_loss": 2019.53 }, "i1": { "i_item_sk": 4, "i_item_id": "AAAAAAAAEAAAAAAA", "i_rec_start_date": "1997-10-27", "i_rec_end_date": "1999-10-27", "i_item_desc": "Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D", "i_current_price": 1.35, "i_wholesale_cost": 0.85, "i_brand_id": 3002001, "i_brand": "importoexporti #1", "i_class_id": 2, "i_class": "infants", "i_category_id": 3, "i_category": "Children", "i_manufact_id": 479, "i_manufact": "n stationese", "i_size": "extra large", "i_formulation": "610157moccasin018327", "i_color": "red", "i_units": "Tbl", "i_container": "Unknown", "i_manager_id": 26, "i_product_name": "ese" } }
 { "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 29485, "cs_ship_date_sk": 2450890, "cs_bill_customer_sk": 14601, "cs_bill_cdemo_sk": 797995, "cs_bill_hdemo_sk": 6189, "cs_bill_addr_sk": 9583, "cs_ship_customer_sk": 14601, "cs_ship_cdemo_sk": 797995, "cs_ship_hdemo_sk": 6189, "cs_ship_addr_sk": 9583, "cs_call_center_sk": 1, "cs_catalog_page_sk": 75, "cs_ship_mode_sk": 8, "cs_warehouse_sk": 1, "cs_item_sk": 5, "cs_promo_sk": 278, "cs_order_number": 2, "cs_quantity": 88, "cs_wholesale_cost": 20.08, "cs_list_price": 60.03, "cs_sales_price": 20.41, "cs_ext_discount_amt": 3486.56, "cs_ext_sales_price": 1796.08, "cs_ext_wholesale_cost": 1767.04, "cs_ext_list_price": 5282.64, "cs_ext_tax": 13.82, "cs_coupon_amt": 1598.51, "cs_ext_ship_cost": 1056.0, "cs_net_paid": 197.57, "cs_net_paid_inc_tax": 211.39, "cs_net_paid_inc_ship": 1253.57, "cs_net_paid_inc_ship_tax": 1267.39, "cs_net_profit": -1569.47 }, "i1": { "i_item_sk": 5, "i_item_id": "AAAAAAAAEAAAAAAA", "i_rec_start_date": "1999-10-28", "i_rec_end_date": "2001-10-26", "i_item_desc": "Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D", "i_current_price": 4.0, "i_wholesale_cost": 1.76, "i_brand_id": 2002002, "i_brand": "importoimporto #2", "i_class_id": 2, "i_class": "shirts", "i_category_id": 2, "i_category": "Men", "i_manufact_id": 220, "i_manufact": "barableable", "i_size": "petite", "i_formulation": "42214rosy28066558020", "i_color": "pink", "i_units": "Cup", "i_container": "Unknown", "i_manager_id": 27, "i_product_name": "anti" } }
 { "cs1": { "cs_sold_date_sk": 2450815, "cs_sold_time_sk": 29485, "cs_ship_date_sk": 2450849, "cs_bill_customer_sk": 14601, "cs_bill_cdemo_sk": 797995, "cs_bill_hdemo_sk": 6189, "cs_bill_addr_sk": 9583, "cs_ship_customer_sk": 14601, "cs_ship_cdemo_sk": 797995, "cs_ship_hdemo_sk": 6189, "cs_ship_addr_sk": 9583, "cs_call_center_sk": 1, "cs_catalog_page_sk": 39, "cs_ship_mode_sk": 4, "cs_warehouse_sk": 3, "cs_item_sk": 6, "cs_promo_sk": 207, "cs_order_number": 2, "cs_quantity": 31, "cs_wholesale_cost": 40.88, "cs_list_price": 51.91, "cs_sales_price": 6.22, "cs_ext_discount_amt": 1416.39, "cs_ext_sales_price": 192.82, "cs_ext_wholesale_cost": 1267.28, "cs_ext_list_price": 1609.21, "cs_ext_tax": 11.56, "cs_coupon_amt": 0.0, "cs_ext_ship_cost": 321.78, "cs_net_paid": 192.82, "cs_net_paid_inc_tax": 204.38, "cs_net_paid_inc_ship": 514.6, "cs_net_paid_inc_ship_tax": 526.16, "cs_net_profit": -1074.46 }, "i1": { "i_item_sk": 6, "i_item_id": "AAAAAAAAEAAAAAAA", "i_rec_start_date": "2001-10-27", "i_rec_end_date": null, "i_item_desc": "Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D", "i_current_price": 0.85, "i_wholesale_cost": 1.76, "i_brand_id": 2002002, "i_brand": "exportiimporto #1", "i_class_id": 3, "i_class": "pants", "i_category_id": 2, "i_category": "Men", "i_manufact_id": 212, "i_manufact": "barableable", "i_size": "large", "i_formulation": "42214rosy28066558020", "i_color": "moccasin", "i_units": "Bundle", "i_container": "Unknown", "i_manager_id": 6, "i_product_name": "cally" } }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 7fe7c40..be3ca8d 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -5768,6 +5768,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="subquery">
+      <compilation-unit name="query-ASTERIXDB-1572">
+        <output-dir compare="Text">query-ASTERIXDB-1572</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="subquery">
       <compilation-unit name="query-ASTERIXDB-1574">
         <output-dir compare="Text">query-ASTERIXDB-1574</output-dir>
         <expected-error>Unnest or index access expects the input to be a collection, but it was of type</expected-error>
@@ -5921,6 +5926,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1591">
+        <output-dir compare="Text">query-ASTERIXDB-1591</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="tpcds">
       <compilation-unit name="query-ASTERIXDB-1596">
         <output-dir compare="Text">query-ASTERIXDB-1596</output-dir>
       </compilation-unit>
diff --git a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
index 4437822..9b8258c 100644
--- a/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
+++ b/hyracks-fullstack/algebricks/algebricks-core/src/main/java/org/apache/hyracks/algebricks/core/algebra/util/OperatorManipulationUtil.java
@@ -79,7 +79,7 @@
     }
 
     public static boolean setOperatorMode(AbstractLogicalOperator op) {
-        boolean change = false;
+        AbstractLogicalOperator.ExecutionMode oldMode = op.getExecutionMode();
         switch (op.getOperatorTag()) {
             case DATASOURCESCAN: {
                 op.setExecutionMode(AbstractLogicalOperator.ExecutionMode.PARTITIONED);
@@ -93,7 +93,6 @@
                     child.setExecutionMode(AbstractLogicalOperator.ExecutionMode.PARTITIONED);
                     currentOp = child;
                 }
-                change = true;
                 break;
             }
             case NESTEDTUPLESOURCE: {
@@ -102,7 +101,6 @@
                         .getInputs().get(0).getValue();
                 if (prevOp.getExecutionMode() != AbstractLogicalOperator.ExecutionMode.UNPARTITIONED) {
                     nts.setExecutionMode(AbstractLogicalOperator.ExecutionMode.LOCAL);
-                    change = true;
                 }
                 break;
             }
@@ -112,7 +110,6 @@
                     LimitOperator opLim = (LimitOperator) op;
                     if (opLim.isTopmostLimitOp()) {
                         opLim.setExecutionMode(AbstractLogicalOperator.ExecutionMode.UNPARTITIONED);
-                        change = true;
                         forceUnpartitioned = true;
                     }
                 }
@@ -120,7 +117,6 @@
                     AggregateOperator aggOp = (AggregateOperator) op;
                     if (aggOp.isGlobal()) {
                         op.setExecutionMode(AbstractLogicalOperator.ExecutionMode.UNPARTITIONED);
-                        change = true;
                         forceUnpartitioned = true;
                     }
                 }
@@ -134,13 +130,11 @@
                                 break;
                             }
                             op.setExecutionMode(AbstractLogicalOperator.ExecutionMode.PARTITIONED);
-                            change = true;
                             exit = true;
                             break;
                         }
                         case LOCAL: {
                             op.setExecutionMode(AbstractLogicalOperator.ExecutionMode.LOCAL);
-                            change = true;
                             break;
                         }
                     }
@@ -151,7 +145,7 @@
                 break;
             }
         }
-        return change;
+        return oldMode != op.getExecutionMode();
     }
 
     public static void substituteVarRec(AbstractLogicalOperator op, LogicalVariable v1, LogicalVariable v2,
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
index 139f1ed..90ab975 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/RemoveUnusedAssignAndAggregateRule.java
@@ -36,6 +36,7 @@
 import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractLogicalOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AbstractOperatorWithNestedPlans;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
@@ -47,7 +48,7 @@
 import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
 
 /**
- * Removes unused variables from Assign, Unnest, Aggregate, and UnionAll operators.
+ * Removes unused variables from Assign, Unnest, Aggregate, UnionAll, and Group-by operators.
  */
 public class RemoveUnusedAssignAndAggregateRule implements IAlgebraicRewriteRule {
 
@@ -183,9 +184,18 @@
         while (iter.hasNext()) {
             Pair<LogicalVariable, Mutable<ILogicalExpression>> varMapping = iter.next();
             LogicalVariable decorVar = varMapping.first;
-            if (decorVar != null && toRemove.contains(decorVar)) {
+            // A decor var mapping can have a variable reference expression without a new variable definition,
+            // which is for rebinding the referred variable.
+            VariableReferenceExpression varExpr = (VariableReferenceExpression) varMapping.second.getValue();
+            LogicalVariable decorReferredVar = varExpr.getVariableReference();
+            boolean removeReBoundDecorVar = toRemove.contains(decorReferredVar);
+            if ((decorVar != null && toRemove.contains(decorVar)) || removeReBoundDecorVar) {
                 iter.remove();
                 modified = true;
+                if (removeReBoundDecorVar) {
+                    // Do not need to remove that in the children pipeline.
+                    toRemove.remove(decorReferredVar);
+                }
             }
         }
         return modified;
@@ -225,6 +235,7 @@
             }
         }
         boolean removeUsedVars = true;
+        Set<LogicalVariable> reBoundDecorVars = new HashSet<>();
         switch (op.getOperatorTag()) {
             case ASSIGN:
                 AssignOperator assign = (AssignOperator) op;
@@ -255,6 +266,14 @@
                     LogicalVariable decorVar = decorMapping.first;
                     if (decorVar != null) {
                         toRemove.add(decorVar);
+                    } else {
+                        // A decor var mapping can have a variable reference expression without a new variable
+                        // definition, which is for rebinding the referred variable.
+                        VariableReferenceExpression varExpr = (VariableReferenceExpression) decorMapping.second
+                                .getValue();
+                        LogicalVariable reboundDecorVar = varExpr.getVariableReference();
+                        toRemove.add(reboundDecorVar);
+                        reBoundDecorVars.add(reboundDecorVar);
                     }
                 }
                 break;
@@ -265,6 +284,7 @@
             List<LogicalVariable> used = new LinkedList<LogicalVariable>();
             VariableUtilities.getUsedVariables(op, used);
             toRemove.removeAll(used);
+            toRemove.addAll(reBoundDecorVars);
         }
     }
 
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/SetExecutionModeRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/SetExecutionModeRule.java
index 63a33b5..ed85001 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/SetExecutionModeRule.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/SetExecutionModeRule.java
@@ -40,30 +40,7 @@
     @Override
     public boolean rewritePost(Mutable<ILogicalOperator> opRef, IOptimizationContext context) {
         AbstractLogicalOperator op = (AbstractLogicalOperator) opRef.getValue();
-        boolean changed = OperatorManipulationUtil.setOperatorMode(op);
-        if (op.getExecutionMode() == AbstractLogicalOperator.ExecutionMode.UNPARTITIONED
-                || op.getExecutionMode() == AbstractLogicalOperator.ExecutionMode.LOCAL) {
-            return changed;
-        }
-        switch (op.getOperatorTag()) {
-        // case DISTINCT:
-        // case AGGREGATE:
-        // case GROUP:
-        // case ORDER:
-        // case INNERJOIN:
-        // case LEFTOUTERJOIN: {
-        // op.setExecutionMode(ExecutionMode.GLOBAL);
-        // return true;
-        // }
-
-            case PARTITIONINGSPLIT: {
-                throw new NotImplementedException();
-            }
-            default: {
-                return changed;
-            }
-        }
-
+        return OperatorManipulationUtil.setOperatorMode(op);
     }
 
     @Override