diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
index f95490a..ad50dd0 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
@@ -19,6 +19,7 @@
 import java.util.HashSet;
 import java.util.LinkedList;
 import java.util.List;
+import java.util.Map;
 
 import org.apache.commons.lang3.mutable.Mutable;
 import org.apache.commons.lang3.mutable.MutableObject;
@@ -58,39 +59,52 @@
 
     @Override
     public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context) throws AlgebricksException {
-        HashMap<LogicalVariable, Integer> gbyAggVars = new HashMap<LogicalVariable, Integer>();
-        HashMap<LogicalVariable, GroupByOperator> gbyWithAgg = new HashMap<LogicalVariable, GroupByOperator>();
+        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>();
         // first collect vars. referring to listified sequences
-        boolean changed = collectVarsBottomUp(opRef, context, gbyAggVars, gbyWithAgg);
+        boolean changed = collectVarsBottomUp(opRef, context, gbyAggVars, gbyWithAgg, gbyAggVarToPlanIndex,
+                aggExprToVarExpr);
         if (changed) {
-            removeRedundantListifies(opRef, context, gbyAggVars, gbyWithAgg);
+            removeRedundantListifies(opRef, context, gbyAggVars, gbyWithAgg, gbyAggVarToPlanIndex);
         }
         return changed;
     }
 
     private void removeRedundantListifies(Mutable<ILogicalOperator> opRef, IOptimizationContext context,
-            HashMap<LogicalVariable, Integer> gbyAggVars, HashMap<LogicalVariable, GroupByOperator> gbyWithAgg) {
+            Map<LogicalVariable, Integer> gbyAggVars, Map<LogicalVariable, GroupByOperator> gbyWithAgg,
+            Map<LogicalVariable, Integer> gbyAggVarToPlanIndex) throws AlgebricksException {
         for (LogicalVariable aggVar : gbyAggVars.keySet()) {
             int occurs = gbyAggVars.get(aggVar);
             if (occurs == 0) {
                 GroupByOperator gbyOp = gbyWithAgg.get(aggVar);
-                AggregateOperator aggOp = (AggregateOperator) gbyOp.getNestedPlans().get(0).getRoots().get(0)
-                        .getValue();
+                AggregateOperator aggOp = (AggregateOperator) gbyOp.getNestedPlans()
+                        .get(gbyAggVarToPlanIndex.get(aggVar)).getRoots().get(0).getValue();
                 int pos = aggOp.getVariables().indexOf(aggVar);
-                aggOp.getVariables().remove(pos);
-                aggOp.getExpressions().remove(pos);
+                if (pos >= 0) {
+                    aggOp.getVariables().remove(pos);
+                    aggOp.getExpressions().remove(pos);
+                    List<LogicalVariable> producedVarsAtAgg = new ArrayList<LogicalVariable>();
+                    VariableUtilities.getProducedVariablesInDescendantsAndSelf(aggOp, producedVarsAtAgg);
+                    if (producedVarsAtAgg.isEmpty()) {
+                        gbyOp.getNestedPlans().remove(gbyAggVarToPlanIndex.get(aggVar));
+                    }
+                }
             }
         }
     }
 
     private boolean collectVarsBottomUp(Mutable<ILogicalOperator> opRef, IOptimizationContext context,
-            HashMap<LogicalVariable, Integer> gbyListifyVarsCount, HashMap<LogicalVariable, GroupByOperator> gbyWithAgg)
-            throws AlgebricksException {
+            Map<LogicalVariable, Integer> gbyListifyVarsCount, Map<LogicalVariable, GroupByOperator> gbyWithAgg,
+            Map<LogicalVariable, Integer> gbyAggVarToPlanIndex,
+            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)) {
+            if (collectVarsBottomUp(child, context, gbyListifyVarsCount, gbyWithAgg, gbyAggVarToPlanIndex,
+                    aggregateExprToVarExpr)) {
                 change = true;
             }
         }
@@ -112,7 +126,7 @@
                     AssignOperator assign = (AssignOperator) op1;
                     for (Mutable<ILogicalExpression> exprRef : assign.getExpressions()) {
                         Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, gbyWithAgg,
-                                context);
+                                aggregateExprToVarExpr, context);
                         if (p.first) {
                             change = true;
                             exprRef.setValue(p.second);
@@ -135,7 +149,8 @@
                     Integer m = gbyListifyVarsCount.get(v);
                     if (m != null) {
                         GroupByOperator gbyOp = gbyWithAgg.get(v);
-                        if (pushSubplanAsAggIntoGby(opRef, gbyOp, v, gbyListifyVarsCount, gbyWithAgg, context)) {
+                        if (pushSubplanAsAggIntoGby(opRef, gbyOp, v, gbyListifyVarsCount, gbyWithAgg,
+                                gbyAggVarToPlanIndex, context)) {
                             change = true;
                         } else {
                             gbyListifyVarsCount.put(v, m + 1);
@@ -146,10 +161,16 @@
                 break;
             }
             case GROUP: {
-                LogicalVariable v1 = collectOneVarFromGroupOp((GroupByOperator) op1);
-                if (v1 != null) {
-                    gbyListifyVarsCount.put(v1, 0);
-                    gbyWithAgg.put(v1, (GroupByOperator) op1);
+                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);
+                        }
+                    }
                 }
                 break;
             }
@@ -165,31 +186,34 @@
         return change;
     }
 
-    private LogicalVariable collectOneVarFromGroupOp(GroupByOperator group) {
+    private List<LogicalVariable> collectOneVarPerAggFromGroupOp(GroupByOperator group) {
         List<ILogicalPlan> nPlans = group.getNestedPlans();
         if (nPlans == null || nPlans.size() < 1) {
             return null;
         }
 
+        List<LogicalVariable> aggVars = new ArrayList<LogicalVariable>();
         // test that the group-by computes a "listify" aggregate
-        AbstractLogicalOperator topOp = (AbstractLogicalOperator) nPlans.get(0).getRoots().get(0).getValue();
-        if (topOp.getOperatorTag() != LogicalOperatorTag.AGGREGATE) {
-            return null;
+        for (int i = 0; i < nPlans.size(); i++) {
+            AbstractLogicalOperator topOp = (AbstractLogicalOperator) nPlans.get(i).getRoots().get(0).getValue();
+            if (topOp.getOperatorTag() != LogicalOperatorTag.AGGREGATE) {
+                continue;
+            }
+            AggregateOperator agg = (AggregateOperator) topOp;
+            if (agg.getVariables().size() != 1) {
+                continue;
+            }
+            ILogicalExpression expr = agg.getExpressions().get(0).getValue();
+            if (((AbstractLogicalExpression) expr).getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
+                continue;
+            }
+            AbstractFunctionCallExpression fceAgg = (AbstractFunctionCallExpression) expr;
+            if (fceAgg.getFunctionIdentifier() != AsterixBuiltinFunctions.LISTIFY) {
+                continue;
+            }
+            aggVars.add(agg.getVariables().get(0));
         }
-        AggregateOperator agg = (AggregateOperator) topOp;
-        if (agg.getVariables().size() != 1) {
-            return null;
-        }
-        ILogicalExpression expr = agg.getExpressions().get(0).getValue();
-        if (((AbstractLogicalExpression) expr).getExpressionTag() != LogicalExpressionTag.FUNCTION_CALL) {
-            return null;
-        }
-        AbstractFunctionCallExpression fceAgg = (AbstractFunctionCallExpression) expr;
-        if (fceAgg.getFunctionIdentifier() != AsterixBuiltinFunctions.LISTIFY) {
-            return null;
-        }
-
-        return agg.getVariables().get(0);
+        return aggVars;
     }
 
     /**
@@ -203,7 +227,8 @@
      * @throws AlgebricksException
      */
     private Pair<Boolean, ILogicalExpression> extractAggFunctionsFromExpression(Mutable<ILogicalExpression> exprRef,
-            HashMap<LogicalVariable, GroupByOperator> gbyWithAgg, IOptimizationContext context)
+            Map<LogicalVariable, GroupByOperator> gbyWithAgg,
+            Map<ILogicalExpression, ILogicalExpression> aggregateExprToVarExpr, IOptimizationContext context)
             throws AlgebricksException {
         ILogicalExpression expr = exprRef.getValue();
         switch (expr.getExpressionTag()) {
@@ -217,19 +242,26 @@
                         GroupByOperator gbyOp = gbyWithAgg.get(argVar);
 
                         if (gbyOp != null) {
-                            LogicalVariable newVar = context.newVar();
-                            AggregateFunctionCallExpression aggFun = AsterixBuiltinFunctions
-                                    .makeAggregateFunctionExpression(fi, fce.getArguments());
-                            rewriteGroupByAggregate(argVar, gbyOp, aggFun, newVar, context);
-                            return new Pair<Boolean, ILogicalExpression>(Boolean.TRUE, new VariableReferenceExpression(
-                                    newVar));
+                            if (!aggregateExprToVarExpr.containsKey(expr)) {
+                                LogicalVariable newVar = context.newVar();
+                                AggregateFunctionCallExpression aggFun = AsterixBuiltinFunctions
+                                        .makeAggregateFunctionExpression(fi, fce.getArguments());
+                                rewriteGroupByAggregate(argVar, gbyOp, aggFun, newVar, context);
+                                ILogicalExpression newVarExpr = new VariableReferenceExpression(newVar);
+                                aggregateExprToVarExpr.put(expr, newVarExpr);
+                                return new Pair<Boolean, ILogicalExpression>(Boolean.TRUE, newVarExpr);
+                            } else {
+                                ILogicalExpression varExpr = aggregateExprToVarExpr.get(expr);
+                                return new Pair<Boolean, ILogicalExpression>(Boolean.TRUE, varExpr);
+                            }
                         }
                     }
                 }
 
                 boolean change = false;
                 for (Mutable<ILogicalExpression> a : fce.getArguments()) {
-                    Pair<Boolean, ILogicalExpression> aggArg = extractAggFunctionsFromExpression(a, gbyWithAgg, context);
+                    Pair<Boolean, ILogicalExpression> aggArg = extractAggFunctionsFromExpression(a, gbyWithAgg,
+                            aggregateExprToVarExpr, context);
                     if (aggArg.first.booleanValue()) {
                         a.setValue(aggArg.second);
                         change = true;
@@ -250,31 +282,34 @@
     private void rewriteGroupByAggregate(LogicalVariable oldAggVar, GroupByOperator gbyOp,
             AggregateFunctionCallExpression aggFun, LogicalVariable newAggVar, IOptimizationContext context)
             throws AlgebricksException {
-        AggregateOperator aggOp = (AggregateOperator) gbyOp.getNestedPlans().get(0).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>>());
-                for (Mutable<ILogicalExpression> arg : oldAggExpr.getArguments()) {
-                    ILogicalExpression cloned = ((AbstractLogicalExpression) arg.getValue()).cloneExpression();
-                    newAggFun.getArguments().add(new MutableObject<ILogicalExpression>(cloned));
+        for (int j = 0; j < gbyOp.getNestedPlans().size(); j++) {
+            AggregateOperator aggOp = (AggregateOperator) gbyOp.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>>());
+                    for (Mutable<ILogicalExpression> arg : oldAggExpr.getArguments()) {
+                        ILogicalExpression cloned = ((AbstractLogicalExpression) arg.getValue()).cloneExpression();
+                        newAggFun.getArguments().add(new MutableObject<ILogicalExpression>(cloned));
+                    }
+                    aggOp.getVariables().add(newAggVar);
+                    aggOp.getExpressions().add(new MutableObject<ILogicalExpression>(newAggFun));
+                    context.computeAndSetTypeEnvironmentForOperator(aggOp);
+                    break;
                 }
-                aggOp.getVariables().add(newAggVar);
-                aggOp.getExpressions().add(new MutableObject<ILogicalExpression>(newAggFun));
-                context.computeAndSetTypeEnvironmentForOperator(aggOp);
-                break;
             }
         }
     }
 
     private boolean pushSubplanAsAggIntoGby(Mutable<ILogicalOperator> subplanOpRef, GroupByOperator gbyOp,
-            LogicalVariable varFromGroupAgg, HashMap<LogicalVariable, Integer> gbyAggVars,
-            HashMap<LogicalVariable, GroupByOperator> gbyWithAgg, IOptimizationContext context)
-            throws AlgebricksException {
+            LogicalVariable varFromGroupAgg, Map<LogicalVariable, Integer> gbyAggVars,
+            Map<LogicalVariable, GroupByOperator> gbyWithAgg, Map<LogicalVariable, Integer> gbyAggVarToPlanIndex,
+            IOptimizationContext context) throws AlgebricksException {
         SubplanOperator subplan = (SubplanOperator) subplanOpRef.getValue();
         // only free var can be varFromGroupAgg
         HashSet<LogicalVariable> freeVars = new HashSet<LogicalVariable>();
@@ -345,44 +380,47 @@
             }
         }
         if (pushableNestedSubplan) {
-            Mutable<ILogicalOperator> gbyAggRef = gbyOp.getNestedPlans().get(0).getRoots().get(0);
-            AggregateOperator gbyAgg = (AggregateOperator) gbyAggRef.getValue();
-            Mutable<ILogicalOperator> gbyAggChildRef = gbyAgg.getInputs().get(0);
-            OperatorManipulationUtil.substituteVarRec(aggInSubplanOp, unnestVar,
-                    findListifiedVariable(gbyAgg, varFromGroupAgg), 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);
-            }
+            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);
+                OperatorManipulationUtil.substituteVarRec(aggInSubplanOp, unnestVar,
+                        findListifiedVariable(gbyAgg, varFromGroupAgg), 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);
+                }
 
-            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("PushAggregateIntoGroupbyRule: could not find UNNEST.");
+                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("PushAggregateIntoGroupbyRule: could not find UNNEST.");
+                            }
                         }
                     }
                 }
+                subplanOpRef.setValue(subplan.getInputs().get(0).getValue());
+                OperatorPropertiesUtil.typeOpRec(gbyAggRef, context);
             }
-            subplanOpRef.setValue(subplan.getInputs().get(0).getValue());
-            OperatorPropertiesUtil.typeOpRec(gbyAggRef, context);
             return true;
         } else {
             return false;
@@ -407,4 +445,4 @@
         return null;
     }
 
-}
+}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-issue697.aql b/asterix-app/src/test/resources/optimizerts/queries/query-issue697.aql
new file mode 100644
index 0000000..1442482
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-issue697.aql
@@ -0,0 +1,27 @@
+/*
+ * Description     : This test case is to verify the fix for issue697
+ * https://code.google.com/p/asterixdb/issues/detail?id=697
+ * Expected Result : Success
+ * Date            : 16th Nov. 2014
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+
+use dataverse test;
+
+create type TestType as closed {
+  key1: int32,
+  value: int32
+}
+
+create dataset test(TestType)
+  primary key key1;
+
+
+for $i in dataset("test")
+group by $aid := $i.key1 with $i
+return {
+  "gid": $aid,
+  "avg": avg(for $j in $i where not(is-null($j.value)) return $j.value)
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-issue810-2.aql b/asterix-app/src/test/resources/optimizerts/queries/query-issue810-2.aql
new file mode 100644
index 0000000..aa60667
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-issue810-2.aql
@@ -0,0 +1,54 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 24th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  l_linenumber: int32,
+  l_quantity: double,
+  l_extendedprice: double,
+  l_discount: double,
+  l_tax: double,
+  l_returnflag: string,
+  l_linestatus: string,
+  l_shipdate: string,
+  l_commitdate: string,
+  l_receiptdate: string,
+  l_shipinstruct: string,
+  l_shipmode: string,
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
+
+for $l in dataset('LineItem')
+where $l.l_shipdate <= '1998-09-02'
+/*+ hash*/
+group by $l_returnflag := $l.l_returnflag,
+         $l_linestatus := $l.l_linestatus
+  with $l
+ let $expensives := for $i in $l where ($i.l_discount<=0.05) return $i.l_discount
+ let $cheaps := for $i in $l where ($i.l_discount>0.05) return $i
+ let $charges := for $i in $l return $i.l_extendedprice * (1 - $i.l_discount) * (1 + $i.l_tax)
+ let $disc_prices := for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)
+order by $l_returnflag, $l_linestatus
+return {
+  "l_returnflag": $l_returnflag,
+  "l_linestatus": $l_linestatus,
+  "count_cheaps": count($cheaps),
+  "avg_expensive_discounts": avg($expensives),
+  "sum_disc_prices": sum($disc_prices),
+  "total_charges": sum($charges)
+}
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-issue810.aql b/asterix-app/src/test/resources/optimizerts/queries/query-issue810.aql
new file mode 100644
index 0000000..097a48b
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-issue810.aql
@@ -0,0 +1,49 @@
+/*
+ * Description     : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Result : Success
+ * Date            : 16th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  l_linenumber: int32,
+  l_quantity: double,
+  l_extendedprice: double,
+  l_discount: double,
+  l_tax: double,
+  l_returnflag: string,
+  l_linestatus: string,
+  l_shipdate: string,
+  l_commitdate: string,
+  l_receiptdate: string,
+  l_shipinstruct: string,
+  l_shipmode: string,
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
+for $l in dataset('LineItem')
+where $l.l_shipdate <= '1998-09-02'
+/*+ hash*/
+group by $l_returnflag := $l.l_returnflag,
+         $l_linestatus := $l.l_linestatus
+  with $l
+ let $cheap := for $m in $l where ($m.l_discount>0.05) return $m
+ let $expensive := for $a in $l where ($a.l_discount<=0.05) return $a
+order by $l_returnflag, $l_linestatus
+return {
+  "l_returnflag": $l_returnflag,
+  "l_linestatus": $l_linestatus,
+  "count_cheaps": count($cheap),
+  "count_expensives": count($expensive)
+}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/results/q03_shipping_priority.plan b/asterix-app/src/test/resources/optimizerts/results/q03_shipping_priority.plan
index c0a4207..0618b41 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q03_shipping_priority.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q03_shipping_priority.plan
@@ -3,52 +3,51 @@
     -- STREAM_PROJECT  |UNPARTITIONED|
       -- ASSIGN  |UNPARTITIONED|
         -- STREAM_LIMIT  |UNPARTITIONED|
-          -- STREAM_PROJECT  |PARTITIONED|
-            -- SORT_MERGE_EXCHANGE [$$49(DESC), $$4(ASC) ]  |PARTITIONED|
-              -- STREAM_LIMIT  |PARTITIONED|
-                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- STABLE_SORT [$$49(DESC), $$4(ASC)]  |PARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- EXTERNAL_GROUP_BY[$$56, $$57, $$58]  |PARTITIONED|
-                              {
-                                -- AGGREGATE  |LOCAL|
-                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                              }
-                        -- HASH_PARTITION_EXCHANGE [$$56, $$57, $$58]  |PARTITIONED|
-                          -- EXTERNAL_GROUP_BY[$$44, $$41, $$39]  |PARTITIONED|
-                                  {
-                                    -- AGGREGATE  |LOCAL|
-                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                  }
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- HYBRID_HASH_JOIN [$$43][$$44]  |PARTITIONED|
-                                    -- HASH_PARTITION_EXCHANGE [$$43]  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
-                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- HYBRID_HASH_JOIN [$$42][$$47]  |PARTITIONED|
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$49(DESC), $$4(ASC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$49(DESC), $$4(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EXTERNAL_GROUP_BY[$$55, $$56, $$57]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- HASH_PARTITION_EXCHANGE [$$55, $$56, $$57]  |PARTITIONED|
+                        -- EXTERNAL_GROUP_BY[$$44, $$41, $$39]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$43][$$44]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$43]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$42][$$47]  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$47]  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                -- STREAM_SELECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- DATASOURCE_SCAN  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                         -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$47]  |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 [$$44]  |PARTITIONED|
-                                      -- STREAM_PROJECT  |PARTITIONED|
-                                        -- STREAM_SELECT  |PARTITIONED|
-                                          -- ASSIGN  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$44]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- STREAM_SELECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
index 76d9f49..1f99eee 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q05_local_supplier_volume.plan
@@ -2,83 +2,82 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- STREAM_PROJECT  |PARTITIONED|
-          -- SORT_MERGE_EXCHANGE [$$100(DESC) ]  |PARTITIONED|
-            -- STABLE_SORT [$$100(DESC)]  |PARTITIONED|
-              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- EXTERNAL_GROUP_BY[$$120]  |PARTITIONED|
-                        {
-                          -- AGGREGATE  |LOCAL|
-                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                        }
-                  -- HASH_PARTITION_EXCHANGE [$$120]  |PARTITIONED|
-                    -- EXTERNAL_GROUP_BY[$$103]  |PARTITIONED|
-                            {
-                              -- AGGREGATE  |LOCAL|
-                                -- NESTED_TUPLE_SOURCE  |LOCAL|
-                            }
-                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- STREAM_PROJECT  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- HYBRID_HASH_JOIN [$$81, $$98][$$116, $$88]  |PARTITIONED|
-                              -- ONE_TO_ONE_EXCHANGE  |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 [$$116]  |PARTITIONED|
-                                -- STREAM_PROJECT  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$100(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$100(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- EXTERNAL_GROUP_BY[$$119]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- HASH_PARTITION_EXCHANGE [$$119]  |PARTITIONED|
+                  -- EXTERNAL_GROUP_BY[$$102]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$81, $$98][$$115, $$88]  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- HYBRID_HASH_JOIN [$$82][$$83]  |PARTITIONED|
+                                    -- DATASOURCE_SCAN  |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 [$$83]  |PARTITIONED|
-                                        -- STREAM_PROJECT  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$92][$$85]  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$92]  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                  -- ASSIGN  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
-                                                -- STREAM_PROJECT  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$115]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$82][$$83]  |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|
-                                                    -- HYBRID_HASH_JOIN [$$88][$$86]  |PARTITIONED|
-                                                      -- HASH_PARTITION_EXCHANGE [$$88]  |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 [$$86]  |PARTITIONED|
-                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$92][$$85]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$92]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$88][$$86]  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$88]  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- HYBRID_HASH_JOIN [$$89][$$87]  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
-                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- ASSIGN  |PARTITIONED|
-                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                            -- DATASOURCE_SCAN  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- STREAM_SELECT  |PARTITIONED|
-                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- HYBRID_HASH_JOIN [$$89][$$87]  |PARTITIONED|
+                                                            -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |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|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- STREAM_SELECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/q2.plan b/asterix-app/src/test/resources/optimizerts/results/q2.plan
index 827526f..14382c1 100644
--- a/asterix-app/src/test/resources/optimizerts/results/q2.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/q2.plan
@@ -3,41 +3,40 @@
     -- STREAM_PROJECT  |UNPARTITIONED|
       -- ASSIGN  |UNPARTITIONED|
         -- STREAM_LIMIT  |UNPARTITIONED|
-          -- STREAM_PROJECT  |PARTITIONED|
-            -- SORT_MERGE_EXCHANGE [$$26(DESC) ]  |PARTITIONED|
-              -- STREAM_LIMIT  |PARTITIONED|
-                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- STABLE_SORT [$$26(DESC)]  |PARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- PRE_CLUSTERED_GROUP_BY[$$32]  |PARTITIONED|
-                              {
-                                -- AGGREGATE  |LOCAL|
+          -- SORT_MERGE_EXCHANGE [$$26(DESC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [$$26(DESC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$32]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- MICRO_PRE_CLUSTERED_GROUP_BY[$$33]  |LOCAL|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
                                   -- NESTED_TUPLE_SOURCE  |LOCAL|
-                              }
-                              {
-                                -- AGGREGATE  |LOCAL|
-                                  -- MICRO_PRE_CLUSTERED_GROUP_BY[$$33]  |LOCAL|
-                                          {
-                                            -- AGGREGATE  |LOCAL|
-                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                          }
+                            }
+                      -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$32(ASC), $$33(ASC)] HASH:[$$32]  |PARTITIONED|
+                        -- SORT_GROUP_BY[$$23, $$24]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
                                     -- NESTED_TUPLE_SOURCE  |LOCAL|
-                              }
-                        -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$32(ASC), $$33(ASC)] HASH:[$$32]  |PARTITIONED|
-                          -- SORT_GROUP_BY[$$23, $$24]  |PARTITIONED|
-                                  {
-                                    -- AGGREGATE  |LOCAL|
-                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                  }
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
-                                -- ASSIGN  |PARTITIONED|
-                                  -- STREAM_PROJECT  |PARTITIONED|
-                                    -- UNNEST  |PARTITIONED|
-                                      -- STREAM_PROJECT  |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|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- UNNEST  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- DATASOURCE_SCAN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue697.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue697.plan
new file mode 100644
index 0000000..e2a985d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue697.plan
@@ -0,0 +1,24 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$19]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$19(ASC)] HASH:[$$19]  |PARTITIONED|
+              -- PRE_CLUSTERED_GROUP_BY[$$16]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |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/asterix-app/src/test/resources/optimizerts/results/query-issue810-2.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue810-2.plan
new file mode 100644
index 0000000..2b98aec
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue810-2.plan
@@ -0,0 +1,44 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$1(ASC), $$2(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$75, $$76]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$75(ASC), $$76(ASC)] HASH:[$$75, $$76]  |PARTITIONED|
+              -- PRE_CLUSTERED_GROUP_BY[$$42, $$43]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [$$42(ASC), $$43(ASC)]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_SELECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- DATASOURCE_SCAN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue810.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue810.plan
new file mode 100644
index 0000000..fc4ab7d
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue810.plan
@@ -0,0 +1,36 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$1(ASC), $$2(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$34, $$35]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$34(ASC), $$35(ASC)] HASH:[$$34, $$35]  |PARTITIONED|
+              -- PRE_CLUSTERED_GROUP_BY[$$23, $$24]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- STREAM_SELECT  |LOCAL|
+                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [$$23(ASC), $$24(ASC)]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_SELECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- DATASOURCE_SCAN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.1.ddl.aql
new file mode 100644
index 0000000..69ed6c1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.1.ddl.aql
@@ -0,0 +1,7 @@
+/*
+ * Description     : This test case is to verify the fix for issue567
+ * https://code.google.com/p/asterixdb/issues/detail?id=567
+ * Expected Result : Success
+ * Date            : 16th Nov. 2014
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.2.update.aql
new file mode 100644
index 0000000..69ed6c1
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.2.update.aql
@@ -0,0 +1,7 @@
+/*
+ * Description     : This test case is to verify the fix for issue567
+ * https://code.google.com/p/asterixdb/issues/detail?id=567
+ * Expected Result : Success
+ * Date            : 16th Nov. 2014
+ */
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.3.query.aql
new file mode 100644
index 0000000..5f0a788
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/flwor/query-issue567/query-issue567.3.query.aql
@@ -0,0 +1,29 @@
+/*
+ * Description     : This test case is to verify the fix for issue567
+ * https://code.google.com/p/asterixdb/issues/detail?id=567
+ * Expected Result : Success
+ * Date            : 16th Nov. 2014
+ */
+
+let $sample :=
+{{
+  {"r": 1, "uid": "1a2b", "t": datetime("2000-01-01T01:00:00"), "event": "e1"},
+  {"r": 2, "uid": "1a2b", "t": datetime("2000-01-01T01:01:00"), "event": "e2"},
+  {"r": 3, "uid": "3c4d", "t": datetime("2000-01-01T01:02:00"), "event": "e1"},
+  {"r": 4, "uid": "3c4d", "t": datetime("2000-01-01T01:03:00"), "event": "e3"},
+  {"r": 5, "uid": "1a2b", "t": datetime("2000-01-01T01:04:00"), "event": "e1"},
+  {"r": 6, "uid": "1a2b", "t": datetime("2000-01-01T01:05:00"), "event": "e4"}
+}}
+
+for $s1 in $sample
+for $s2 in $sample
+where $s1.uid = $s2.uid
+  and $s1.t < $s2.t
+let $pair := { "s1": $s1, "s2": $s2 }
+group by $s1.uid, $s1.t with $pair
+let $next := 
+    for $p in $pair
+    order by $p.s2.t
+    limit 1
+    return $p
+return $next
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.1.ddl.aql
new file mode 100644
index 0000000..fe29c8a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.1.ddl.aql
@@ -0,0 +1,34 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  l_linenumber: int32,
+  l_quantity: double,
+  l_extendedprice: double,
+  l_discount: double,
+  l_tax: double,
+  l_returnflag: string,
+  l_linestatus: string,
+  l_shipdate: string,
+  l_commitdate: string,
+  l_receiptdate: string,
+  l_shipinstruct: string,
+  l_shipmode: string,
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.2.update.aql
new file mode 100644
index 0000000..9933227
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.2.update.aql
@@ -0,0 +1,13 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+load dataset LineItem
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.3.query.aql
new file mode 100644
index 0000000..6a2a4e4
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-2/query-issue810-2.3.query.aql
@@ -0,0 +1,24 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+for $l in dataset('LineItem')
+where $l.l_shipdate <= '1998-09-02'
+/*+ hash*/
+group by $l_returnflag := $l.l_returnflag,
+         $l_linestatus := $l.l_linestatus
+  with $l
+ let $cheaps := for $m in $l where ($m.l_discount>0.05) return $m
+ let $charges := for $a in $l return $a.l_extendedprice * (1 - $a.l_discount) * (1 + $a.l_tax)
+order by $l_returnflag, $l_linestatus
+return {
+  "l_returnflag": $l_returnflag,
+  "l_linestatus": $l_linestatus,
+  "count_cheaps": count($cheaps),
+  "total_charges": sum($charges)
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.1.ddl.aql
new file mode 100644
index 0000000..fe29c8a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.1.ddl.aql
@@ -0,0 +1,34 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  l_linenumber: int32,
+  l_quantity: double,
+  l_extendedprice: double,
+  l_discount: double,
+  l_tax: double,
+  l_returnflag: string,
+  l_linestatus: string,
+  l_shipdate: string,
+  l_commitdate: string,
+  l_receiptdate: string,
+  l_shipinstruct: string,
+  l_shipmode: string,
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.2.update.aql
new file mode 100644
index 0000000..9933227
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.2.update.aql
@@ -0,0 +1,13 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+load dataset LineItem
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.3.query.aql
new file mode 100644
index 0000000..cf57683
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810-3/query-issue810-3.3.query.aql
@@ -0,0 +1,28 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+for $l in dataset('LineItem')
+where $l.l_shipdate <= '1998-09-02'
+/*+ hash*/
+group by $l_returnflag := $l.l_returnflag,
+         $l_linestatus := $l.l_linestatus
+  with $l
+ let $expensives := for $i in $l where ($i.l_discount<=0.05) return $i.l_discount
+ let $cheaps := for $i in $l where ($i.l_discount>0.05) return $i
+ let $charges := for $i in $l return $i.l_extendedprice * (1 - $i.l_discount) * (1 + $i.l_tax)
+ let $disc_prices := for $i in $l return $i.l_extendedprice * (1 - $i.l_discount)
+order by $l_returnflag, $l_linestatus
+return {
+  "l_returnflag": $l_returnflag,
+  "l_linestatus": $l_linestatus,
+  "count_cheaps": count($cheaps),
+  "avg_expensive_discounts": avg($expensives),
+  "sum_disc_prices": sum($disc_prices),
+  "total_charges": sum($charges)
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.1.ddl.aql
new file mode 100644
index 0000000..fe29c8a
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.1.ddl.aql
@@ -0,0 +1,34 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  l_linenumber: int32,
+  l_quantity: double,
+  l_extendedprice: double,
+  l_discount: double,
+  l_tax: double,
+  l_returnflag: string,
+  l_linestatus: string,
+  l_shipdate: string,
+  l_commitdate: string,
+  l_receiptdate: string,
+  l_shipinstruct: string,
+  l_shipmode: string,
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.2.update.aql
new file mode 100644
index 0000000..9933227
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.2.update.aql
@@ -0,0 +1,13 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+load dataset LineItem
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.3.query.aql
new file mode 100644
index 0000000..54fe889
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue810/query-issue810.3.query.aql
@@ -0,0 +1,24 @@
+/*
+ * Description  : This test case is to verify the fix for issue810
+ * https://code.google.com/p/asterixdb/issues/detail?id=810
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+for $l in dataset('LineItem')
+where $l.l_shipdate <= '1998-09-02'
+/*+ hash*/
+group by $l_returnflag := $l.l_returnflag,
+         $l_linestatus := $l.l_linestatus
+  with $l
+ let $cheap := for $m in $l where ($m.l_discount>0.05) return $m
+ let $expensive := for $a in $l where ($a.l_discount<=0.05) return $a
+order by $l_returnflag, $l_linestatus
+return {
+  "l_returnflag": $l_returnflag,
+  "l_linestatus": $l_linestatus,
+  "count_cheaps": count($cheap),
+  "count_expensives": count($expensive)
+}
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.1.ddl.aql
new file mode 100644
index 0000000..7ae9798
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.1.ddl.aql
@@ -0,0 +1,34 @@
+/*
+ * Description  : This test case is to verify the fix for issue827
+ * https://code.google.com/p/asterixdb/issues/detail?id=827
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32, 
+  l_partkey: int32, 
+  l_suppkey: int32, 
+  l_linenumber: int32, 
+  l_quantity: double, 
+  l_extendedprice: double,
+  l_discount: double, 
+  l_tax: double,
+  l_returnflag: string, 
+  l_linestatus: string, 
+  l_shipdate: string,
+  l_commitdate: string, 
+  l_receiptdate: string, 
+  l_shipinstruct: string, 
+  l_shipmode: string, 
+  l_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.2.update.aql
new file mode 100644
index 0000000..ff9e419
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.2.update.aql
@@ -0,0 +1,13 @@
+/*
+ * Description  : This test case is to verify the fix for issue827
+ * https://code.google.com/p/asterixdb/issues/detail?id=827
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+load dataset LineItem 
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|")) pre-sorted;
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.3.query.aql
new file mode 100644
index 0000000..2a025b3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue827/query-issue827.3.query.aql
@@ -0,0 +1,15 @@
+/*
+ * Description  : This test case is to verify the fix for issue827
+ * https://code.google.com/p/asterixdb/issues/detail?id=827
+ * Expected Res : SUCCESS
+ * Date         : 16th Nov. 2014
+ */
+
+use dataverse tpch;
+
+let $quantities := for $l in dataset('LineItem') return $l.l_quantity
+//let $extendedprices := for $l in dataset('LineItem') return $l.l_extendedprice
+return {
+  "count_cheaps": count($quantities)
+  //"count_expensives": sum(for $e in $extendedprices return $e)
+}
diff --git a/asterix-app/src/test/resources/runtimets/results/flwor/query-issue567/query-issue567.1.adm b/asterix-app/src/test/resources/runtimets/results/flwor/query-issue567/query-issue567.1.adm
new file mode 100644
index 0000000..19921b6
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/flwor/query-issue567/query-issue567.1.adm
@@ -0,0 +1,5 @@
+[ [ { "s1": { "r": 1, "uid": "1a2b", "t": datetime("2000-01-01T01:00:00.000Z"), "event": "e1" }, "s2": { "r": 2, "uid": "1a2b", "t": datetime("2000-01-01T01:01:00.000Z"), "event": "e2" } } ]
+, [ { "s1": { "r": 2, "uid": "1a2b", "t": datetime("2000-01-01T01:01:00.000Z"), "event": "e2" }, "s2": { "r": 5, "uid": "1a2b", "t": datetime("2000-01-01T01:04:00.000Z"), "event": "e1" } } ]
+, [ { "s1": { "r": 5, "uid": "1a2b", "t": datetime("2000-01-01T01:04:00.000Z"), "event": "e1" }, "s2": { "r": 6, "uid": "1a2b", "t": datetime("2000-01-01T01:05:00.000Z"), "event": "e4" } } ]
+, [ { "s1": { "r": 3, "uid": "3c4d", "t": datetime("2000-01-01T01:02:00.000Z"), "event": "e1" }, "s2": { "r": 4, "uid": "3c4d", "t": datetime("2000-01-01T01:03:00.000Z"), "event": "e3" } } ]
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-2/query-issue810-2.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-2/query-issue810-2.1.adm
new file mode 100644
index 0000000..4647846
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-2/query-issue810-2.1.adm
@@ -0,0 +1,5 @@
+[ { "l_returnflag": "A", "l_linestatus": "F", "count_cheaps": 680i64, "total_charges": 3.7101416222424E7d }
+, { "l_returnflag": "N", "l_linestatus": "F", "count_cheaps": 12i64, "total_charges": 1036450.80228d }
+, { "l_returnflag": "N", "l_linestatus": "O", "count_cheaps": 1345i64, "total_charges": 7.4498798133073E7d }
+, { "l_returnflag": "R", "l_linestatus": "F", "count_cheaps": 679i64, "total_charges": 3.616906011219301E7d }
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-3/query-issue810-3.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-3/query-issue810-3.1.adm
new file mode 100644
index 0000000..eb58ce3
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810-3/query-issue810-3.1.adm
@@ -0,0 +1,5 @@
+[ { "l_returnflag": "A", "l_linestatus": "F", "count_cheaps": 680i64, "avg_expensive_discounts": 0.025714285714285662d, "sum_disc_prices": 3.5676192097E7d, "total_charges": 3.7101416222424E7d }
+, { "l_returnflag": "N", "l_linestatus": "F", "count_cheaps": 12i64, "avg_expensive_discounts": 0.024615384615384615d, "sum_disc_prices": 999060.898d, "total_charges": 1036450.80228d }
+, { "l_returnflag": "N", "l_linestatus": "O", "count_cheaps": 1345i64, "avg_expensive_discounts": 0.024699248120300644d, "sum_disc_prices": 7.165316630340001E7d, "total_charges": 7.4498798133073E7d }
+, { "l_returnflag": "R", "l_linestatus": "F", "count_cheaps": 679i64, "avg_expensive_discounts": 0.0244601542416452d, "sum_disc_prices": 3.473847287579999E7d, "total_charges": 3.616906011219301E7d }
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810/query-issue810.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810/query-issue810.1.adm
new file mode 100644
index 0000000..e0be478
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue810/query-issue810.1.adm
@@ -0,0 +1,5 @@
+[ { "l_returnflag": "A", "l_linestatus": "F", "count_cheaps": 680i64, "count_expensives": 798i64 }
+, { "l_returnflag": "N", "l_linestatus": "F", "count_cheaps": 12i64, "count_expensives": 26i64 }
+, { "l_returnflag": "N", "l_linestatus": "O", "count_cheaps": 1345i64, "count_expensives": 1596i64 }
+, { "l_returnflag": "R", "l_linestatus": "F", "count_cheaps": 679i64, "count_expensives": 778i64 }
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-issue827/query-issue810.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue827/query-issue810.1.adm
new file mode 100644
index 0000000..e0be478
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue827/query-issue810.1.adm
@@ -0,0 +1,5 @@
+[ { "l_returnflag": "A", "l_linestatus": "F", "count_cheaps": 680i64, "count_expensives": 798i64 }
+, { "l_returnflag": "N", "l_linestatus": "F", "count_cheaps": 12i64, "count_expensives": 26i64 }
+, { "l_returnflag": "N", "l_linestatus": "O", "count_cheaps": 1345i64, "count_expensives": 1596i64 }
+, { "l_returnflag": "R", "l_linestatus": "F", "count_cheaps": 679i64, "count_expensives": 778i64 }
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index e31a375..c9da615 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -2078,153 +2078,6 @@
       </compilation-unit>
     </test-case>
   </test-group>
-  <test-group name="tpch-sql-like">
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="query-issue638">
-        <output-dir compare="Text">query-issue638</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="query-issue785">
-        <output-dir compare="Text">query-issue785</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="query-issue785-2">
-        <output-dir compare="Text">query-issue785-2</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="query-issue786">
-        <output-dir compare="Text">query-issue786</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="query-issue601">
-        <output-dir compare="Text">query-issue601</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q10_returned_item">
-        <output-dir compare="Text">q10_returned_item</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q10_returned_item_int64">
-       <output-dir compare="Text">q10_returned_item_int64</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q11_important_stock">
-        <output-dir compare="Text">q11_important_stock</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q12_shipping">
-        <output-dir compare="Text">q12_shipping</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q13_customer_distribution">
-        <output-dir compare="Text">q13_customer_distribution</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q14_promotion_effect">
-        <output-dir compare="Text">q14_promotion_effect</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q15_top_supplier">
-        <output-dir compare="Text">q15_top_supplier</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q16_parts_supplier_relationship">
-        <output-dir compare="Text">q16_parts_supplier_relationship</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q17_small_quantity_order_revenue">
-        <output-dir compare="Text">q17_small_quantity_order_revenue</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q17_large_gby_variant">
-        <output-dir compare="Text">q17_large_gby_variant</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q18_large_volume_customer">
-        <output-dir compare="Text">q18_large_volume_customer</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q19_discounted_revenue">
-        <output-dir compare="Text">q19_discounted_revenue</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q01_pricing_summary_report_nt">
-        <output-dir compare="Text">q01_pricing_summary_report_nt</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q20_potential_part_promotion">
-        <output-dir compare="Text">q20_potential_part_promotion</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q21_suppliers_who_kept_orders_waiting">
-        <output-dir compare="Text">q21_suppliers_who_kept_orders_waiting</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q22_global_sales_opportunity">
-        <output-dir compare="Text">q22_global_sales_opportunity</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q02_minimum_cost_supplier">
-        <output-dir compare="Text">q02_minimum_cost_supplier</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q03_shipping_priority_nt">
-        <output-dir compare="Text">q03_shipping_priority_nt</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q04_order_priority">
-        <output-dir compare="Text">q04_order_priority</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q05_local_supplier_volume">
-        <output-dir compare="Text">q05_local_supplier_volume</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q06_forecast_revenue_change">
-        <output-dir compare="Text">q06_forecast_revenue_change</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q07_volume_shipping">
-        <output-dir compare="Text">q07_volume_shipping</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q08_national_market_share">
-        <output-dir compare="Text">q08_national_market_share</output-dir>
-      </compilation-unit>
-    </test-case>
-    <test-case FilePath="tpch-sql-like">
-      <compilation-unit name="q09_product_type_profit_nt">
-        <output-dir compare="Text">q09_product_type_profit_nt</output-dir>
-      </compilation-unit>
-    </test-case>
-  </test-group>
   <test-group name="writers">
     <test-case FilePath="writers">
       <compilation-unit name="print_01">
@@ -4688,6 +4541,168 @@
                 <output-dir compare="Text">q09_product_type_profit_nt</output-dir>
             </compilation-unit>
         </test-case>
+        <test-case FilePath="tpch">
+            <compilation-unit name="query-issue810">
+                <output-dir compare="Text">query-issue810</output-dir>
+        </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch">
+            <compilation-unit name="query-issue810-2">
+                <output-dir compare="Text">query-issue810-2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch">
+            <compilation-unit name="query-issue810-3">
+                <output-dir compare="Text">query-issue810-3</output-dir>
+            </compilation-unit>
+        </test-case>
+    </test-group>
+    <test-group name="tpch-sql-like">
+        <test-case FilePath="tpch-sql-like">
+        <compilation-unit name="query-issue638">
+                <output-dir compare="Text">query-issue638</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="query-issue785">
+                <output-dir compare="Text">query-issue785</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="query-issue785-2">
+                <output-dir compare="Text">query-issue785-2</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="query-issue786">
+                <output-dir compare="Text">query-issue786</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="query-issue601">
+                <output-dir compare="Text">query-issue601</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q10_returned_item">
+                <output-dir compare="Text">q10_returned_item</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q10_returned_item_int64">
+                <output-dir compare="Text">q10_returned_item_int64</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q11_important_stock">
+                <output-dir compare="Text">q11_important_stock</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q12_shipping">
+                <output-dir compare="Text">q12_shipping</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q13_customer_distribution">
+                <output-dir compare="Text">q13_customer_distribution</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q14_promotion_effect">
+                <output-dir compare="Text">q14_promotion_effect</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q15_top_supplier">
+                <output-dir compare="Text">q15_top_supplier</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q16_parts_supplier_relationship">
+                <output-dir compare="Text">q16_parts_supplier_relationship</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q17_small_quantity_order_revenue">
+                <output-dir compare="Text">q17_small_quantity_order_revenue</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q17_large_gby_variant">
+                <output-dir compare="Text">q17_large_gby_variant</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q18_large_volume_customer">
+                <output-dir compare="Text">q18_large_volume_customer</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q19_discounted_revenue">
+                <output-dir compare="Text">q19_discounted_revenue</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q01_pricing_summary_report_nt">
+                <output-dir compare="Text">q01_pricing_summary_report_nt</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q20_potential_part_promotion">
+                <output-dir compare="Text">q20_potential_part_promotion</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q21_suppliers_who_kept_orders_waiting">
+                <output-dir compare="Text">q21_suppliers_who_kept_orders_waiting</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+        <compilation-unit name="q22_global_sales_opportunity">
+            <output-dir compare="Text">q22_global_sales_opportunity</output-dir>
+        </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q02_minimum_cost_supplier">
+                <output-dir compare="Text">q02_minimum_cost_supplier</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q03_shipping_priority_nt">
+                <output-dir compare="Text">q03_shipping_priority_nt</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q04_order_priority">
+                <output-dir compare="Text">q04_order_priority</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q05_local_supplier_volume">
+                <output-dir compare="Text">q05_local_supplier_volume</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q06_forecast_revenue_change">
+                <output-dir compare="Text">q06_forecast_revenue_change</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q07_volume_shipping">
+                <output-dir compare="Text">q07_volume_shipping</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+            <compilation-unit name="q08_national_market_share">
+                <output-dir compare="Text">q08_national_market_share</output-dir>
+            </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch-sql-like">
+        <compilation-unit name="q09_product_type_profit_nt">
+                <output-dir compare="Text">q09_product_type_profit_nt</output-dir>
+            </compilation-unit>
+        </test-case>
     </test-group>
     <test-group name="writers">
         <test-case FilePath="writers">