You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@asterixdb.apache.org by bu...@apache.org on 2016/08/15 15:45:39 UTC

[8/8] asterixdb git commit: Clean up GROUP BY and WITH clause.

Clean up GROUP BY and WITH clause.

- fix ASTERIXDB-971, i.e., the expression to plan translator for FLWOGR without a "for";
- fix the scoping for nested WITH clause;
- fix the scoping for nested GROUP BY clause;
- fix default fields in group variables;
- fix/unify the expression substition AST visitor;
- enhance RemoveUnusedAssignAndAggregateRule to be able to remove unused GROUP BY
  decoration varaibles;
- clearly sperate SQL++ aggregate functions and SQL-92 aggregate functions, i.e.,
  SQL-92 aggregate sugars cannot apply to SQL++ aggregate functions;
- fix PushAggregateIntoGroupbyRule for nested plan deletions;
- enhance RemoveCartesianProductWithEmptyBranchRule;
- add ExtractGroupByDecorVariablesRule;
- add an aggregate function first-element;
- fix ASTERIXDB-1560, an error message issue.

Change-Id: I62fca7f937aa007d97ed87c75cef19f6aa3e5ade
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1050
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Till Westmann <ti...@apache.org>


Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo
Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/8671ddf8
Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/8671ddf8
Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/8671ddf8

Branch: refs/heads/master
Commit: 8671ddf83ed402dce7465759d6399ed84993f967
Parents: c3e84a5
Author: Yingyi Bu <yi...@couchbase.com>
Authored: Sun Aug 14 23:58:43 2016 -0700
Committer: Yingyi Bu <bu...@gmail.com>
Committed: Mon Aug 15 08:44:56 2016 -0700

----------------------------------------------------------------------
 .../asterix/optimizer/base/RuleCollections.java |   8 +-
 .../rules/PushAggregateIntoGroupbyRule.java     |  18 +-
 .../optimizer/rules/ResolveVariableRule.java    | 101 ++--
 .../AqlExpressionToPlanTranslator.java          |  32 +-
 .../AqlPlusExpressionToPlanTranslator.java      |  19 +-
 .../LangExpressionToPlanTranslator.java         |  64 ++-
 .../SqlppExpressionToPlanTranslator.java        |  22 +-
 .../translator/util/FunctionCollection.java     |   4 +
 .../asterix-algebra/src/main/javacc/AQLPlus.jj  |   8 +-
 .../results/aggregate/constant-gby-agg.plan     |   6 +-
 .../resources/optimizerts/results/loj-core.plan |  10 +-
 .../optimizerts/results/loj-sugar.plan          |  10 +-
 .../results/orderby-desc-using-gby.plan         |   4 +-
 .../optimizerts/results/subquery/exists.plan    |  22 +-
 .../optimizerts/results/subquery/in.plan        |  10 +-
 .../results/subquery/in_correlated.plan         |  23 +-
 .../results/subquery/not_exists.plan            |  22 +-
 .../optimizerts/results/tpch/q12_shipping.plan  |  18 +-
 .../results/udfs/query-ASTERIXDB-1017-2.plan    |  10 +-
 .../udfs/query-ASTERIXDB-1017-recursive-2.plan  |  10 +-
 .../udfs/query-ASTERIXDB-1017-recursive.plan    |  10 +-
 .../results/udfs/query-ASTERIXDB-1017.plan      |  16 +-
 .../results/udfs/query-ASTERIXDB-1018.plan      |  22 +-
 .../results/udfs/query-ASTERIXDB-1019.plan      |  24 +-
 .../results/udfs/query-ASTERIXDB-1020.plan      |   6 +-
 .../results/udfs/query-ASTERIXDB-1308-1.plan    |  36 +-
 .../results/udfs/query-ASTERIXDB-1308-2.plan    | 111 ++---
 .../results_parser_sqlpp/count-tweets.ast       |   3 +-
 .../results_parser_sqlpp/denorm-cust-order.ast  |   2 +-
 .../results_parser_sqlpp/fj-dblp-csx.ast        |  12 +-
 .../results_parser_sqlpp/fj-phase1.ast          |   2 +-
 .../fj-phase2-with-hints.ast                    |   4 +-
 .../inlined_q18_large_volume_customer.ast       |   8 +-
 .../results_parser_sqlpp/nest_aggregate.ast     |   4 +-
 .../results_parser_sqlpp/orders-aggreg.ast      |  12 +-
 .../q01_pricing_summary_report_nt.ast           |  12 +-
 .../q03_shipping_priority.ast                   |   4 +-
 .../q05_local_supplier_volume.ast               |   2 +-
 .../optimizerts/results_parser_sqlpp/q2.ast     |  18 +-
 .../results_parser_sqlpp/query-issue550.ast     |   1 -
 .../results_parser_sqlpp/query-issue562.ast     |  13 +-
 .../results_parser_sqlpp/query-issue785.ast     |   7 +-
 .../split-materialization-above-join.ast        |  33 +-
 .../parserts/results_parser_sqlpp/2.ast         |  23 +-
 .../results_parser_sqlpp/columnalias.ast        |  14 +-
 .../results_parser_sqlpp/columnalias3.ast       |  14 +-
 .../results_parser_sqlpp/nestedFLWOGR3.ast      |  23 +-
 .../query-ASTERIXDB-971.1.ddl.aql               |  28 ++
 .../query-ASTERIXDB-971.2.update.aql            |  27 ++
 .../query-ASTERIXDB-971.3.query.aql             |  25 +
 .../queries_sqlpp/dapd/q2-10/q2-10.1.ddl.sqlpp  |  55 +++
 .../dapd/q2-10/q2-10.2.update.sqlpp             |  19 +
 .../dapd/q2-10/q2-10.3.query.sqlpp              |  37 ++
 .../queries_sqlpp/dapd/q2-11/q2-11.1.ddl.sqlpp  |  55 +++
 .../dapd/q2-11/q2-11.2.update.sqlpp             |  19 +
 .../dapd/q2-11/q2-11.3.query.sqlpp              |  40 ++
 .../queries_sqlpp/dapd/q2-12/q2-12.1.ddl.sqlpp  |  55 +++
 .../dapd/q2-12/q2-12.2.update.sqlpp             |  19 +
 .../dapd/q2-12/q2-12.3.query.sqlpp              |  39 ++
 .../q2-2-negative/q2-2-negative.1.ddl.sqlpp     |  55 +++
 .../q2-2-negative/q2-2-negative.2.update.sqlpp  |  19 +
 .../q2-2-negative/q2-2-negative.3.query.sqlpp   |  40 ++
 .../queries_sqlpp/dapd/q2-2/q2-2.3.query.sqlpp  |   7 +-
 .../queries_sqlpp/dapd/q2-7/q2-7.1.ddl.sqlpp    |  55 +++
 .../queries_sqlpp/dapd/q2-7/q2-7.2.update.sqlpp |  19 +
 .../queries_sqlpp/dapd/q2-7/q2-7.3.query.sqlpp  |  35 ++
 .../queries_sqlpp/dapd/q2-8/q2-8.1.ddl.sqlpp    |  55 +++
 .../queries_sqlpp/dapd/q2-8/q2-8.2.update.sqlpp |  19 +
 .../queries_sqlpp/dapd/q2-8/q2-8.3.query.sqlpp  |  35 ++
 .../queries_sqlpp/dapd/q2-9/q2-9.1.ddl.sqlpp    |  55 +++
 .../queries_sqlpp/dapd/q2-9/q2-9.2.update.sqlpp |  19 +
 .../queries_sqlpp/dapd/q2-9/q2-9.3.query.sqlpp  |  35 ++
 .../queries_sqlpp/dapd/q2/q2.3.query.sqlpp      |  11 +-
 .../global-aggregate/q09/q09.1.ddl.sqlpp        |  51 +++
 .../global-aggregate/q09/q09.2.update.sqlpp     |  30 ++
 .../global-aggregate/q09/q09.3.query.sqlpp      |  26 ++
 .../global-aggregate/q10/q10.1.ddl.sqlpp        |  51 +++
 .../global-aggregate/q10/q10.2.update.sqlpp     |  30 ++
 .../global-aggregate/q10/q10.3.query.sqlpp      |  29 ++
 .../sugar-01-negative.1.ddl.sqlpp               |  29 ++
 .../sugar-01-negative.2.update.sqlpp            |  32 ++
 .../sugar-01-negative.3.query.sqlpp             |  27 ++
 .../group-by/sugar-01/sugar-01.3.query.sqlpp    |   2 +-
 .../group-by/sugar-02/sugar-02.4.query.sqlpp    |   2 +-
 .../group-by/sugar-03/sugar-03.3.query.sqlpp    |   2 +-
 .../group-by/sugar-05/sugar-05.3.query.sqlpp    |   2 +-
 .../query-ASTERIXDB-971.1.ddl.sqlpp             |  28 ++
 .../query-ASTERIXDB-971.2.update.sqlpp          |  27 ++
 .../query-ASTERIXDB-971.3.query.sqlpp           |  26 ++
 .../subquery/exists/exists.3.query.sqlpp        |   5 +-
 .../q14_promotion_effect.3.query.sqlpp          |   2 +-
 .../q14_promotion_effect.3.query.sqlpp          |   2 +-
 .../q14_promotion_effect.3.query.sqlpp          |   2 +-
 .../runtimets/results/dapd/q2-12/q2-12.1.adm    |   2 +
 .../runtimets/results/dapd/q2-7/q2-7.1.adm      |   2 +
 .../runtimets/results/dapd/q2-9/q2-9.1.adm      |   2 +
 .../results/global-aggregate/q09/q09.1.adm      |   1 +
 .../query-ASTERIXDB-971-aql.1.adm               |   2 +
 .../query-ASTERIXDB-971-sqlpp.1.adm             |   2 +
 .../results_parser_sqlpp/dapd/q2/q2.3.ast       |  42 +-
 .../results_parser_sqlpp/flwor/at02/at02.3.ast  |   2 +-
 .../flwor/query-issue550/query-issue550.3.ast   |   1 -
 .../fuzzyjoin/dblp-1_1/dblp-1_1.3.ast           |   2 +-
 .../fuzzyjoin/dblp-1_2.1.1/dblp-1_2.3.ast       |   8 +-
 .../fuzzyjoin/dblp-1_2.1/dblp-1_2.3.ast         |   8 +-
 .../fuzzyjoin/dblp-1_2/dblp-1_2.3.ast           |   8 +-
 .../fuzzyjoin/dblp-2.1_5.3.1/dblp-2.3.ast       |  14 +-
 .../fuzzyjoin/dblp-2_1/dblp-2_1.3.ast           |   4 +-
 .../fuzzyjoin/dblp-2_2/dblp-2_2.3.ast           |  10 +-
 .../fuzzyjoin/dblp-2_3/dblp-2_3.3.ast           |  11 +-
 .../fuzzyjoin/dblp-2_4/dblp-2_4.3.ast           |  12 +-
 .../fuzzyjoin/dblp-2_5.1/dblp-2_5.3.ast         |  13 +-
 .../fuzzyjoin/dblp-2_5.2/dblp-2_5.3.ast         |  13 +-
 .../fuzzyjoin/dblp-2_5.3.1/dblp-2_5.3.ast       |  13 +-
 .../fuzzyjoin/dblp-2_5.3/dblp-2_5.3.ast         |  13 +-
 .../fuzzyjoin/dblp-2_5/dblp-2_5.3.ast           |  13 +-
 .../fuzzyjoin/dblp-3_1.1/dblp-3_1.3.ast         |  33 +-
 .../fuzzyjoin/dblp-3_1.2/dblp-3_1.3.ast         |  33 +-
 .../fuzzyjoin/dblp-3_1/dblp-3_1.3.ast           |  33 +-
 .../fuzzyjoin/dblp-csx-2_1/dblp-csx-2_1.3.ast   |  24 +-
 .../fuzzyjoin/dblp-csx-2_2/dblp-csx-2_2.3.ast   |  36 +-
 .../fuzzyjoin/dblp-csx-2_3/dblp-csx-2_3.3.ast   |  41 +-
 .../fuzzyjoin/dblp-csx-2_4/dblp-csx-2_4.3.ast   |  46 +-
 .../fuzzyjoin/dblp-csx-2_5.1/dblp-csx-2_5.3.ast |  55 +--
 .../fuzzyjoin/dblp-csx-2_5.2/dblp-csx-2_5.3.ast |  55 +--
 .../dblp-csx-2_5.3.1/dblp-csx-2_5.3.ast         |  51 +--
 .../fuzzyjoin/dblp-csx-2_5.3/dblp-csx-2_5.3.ast |  55 +--
 .../fuzzyjoin/dblp-csx-2_5/dblp-csx-2_5.3.ast   |  51 +--
 .../fuzzyjoin/dblp-csx-3_1/dblp-csx-3_1.3.ast   |  24 +-
 .../fuzzyjoin/dblp-csx-3_2/dblp-csx-3_2.3.ast   |  36 +-
 .../fuzzyjoin/dblp-csx-3_3/dblp-csx-3_3.3.ast   |  41 +-
 .../fuzzyjoin/dblp-csx-3_4/dblp-csx-3_4.3.ast   |  46 +-
 .../fuzzyjoin/dblp-csx-3_5.1/dblp-csx-3_5.3.ast |  55 +--
 .../fuzzyjoin/dblp-csx-3_5.2/dblp-csx-3_5.3.ast |  55 +--
 .../dblp-csx-3_5.3.1/dblp-csx-3_5.3.ast         |  55 +--
 .../fuzzyjoin/dblp-csx-3_5.3/dblp-csx-3_5.3.ast |  51 +--
 .../dblp-csx-3_5.4.1/dblp-csx-3_5.3.ast         |  55 +--
 .../fuzzyjoin/dblp-csx-3_5.4/dblp-csx-3_5.3.ast |  55 +--
 .../fuzzyjoin/dblp-csx-3_5/dblp-csx-3_5.3.ast   |  51 +--
 .../hdfs/hdfs_02/hdfs_02.3.ast                  |  14 +-
 .../hdfs/hdfs_03/hdfs_03.3.ast                  |  14 +-
 .../hdfs_shortcircuit/hdfs_shortcircuit.3.ast   |  14 +-
 .../issue_251_dataset_hint_6.3.ast              |  14 +-
 .../groupby-orderby-count.3.ast                 |  26 +-
 .../query-issue456/query-issue456.3.ast         |   6 +-
 .../query-proposal02/query-proposal02.3.ast     |   2 +-
 .../tiny-social-example-no-complex-types.9.ast  |   2 +-
 .../tiny-social-example-only-lists.9.ast        |   2 +-
 .../tiny-social-example-only-records.9.ast      |   2 +-
 .../count-nullable/count-nullable.3.ast         |  12 +-
 .../cell-aggregation-with-filtering.3.ast       |  14 +-
 .../cell-aggregation/cell-aggregation.3.ast     |  12 +-
 .../q01_pricing_summary_report_nt.3.ast         |  12 +-
 .../overlap_bins_gby_0/overlap_bins_gby_0.3.ast |   2 +-
 .../overlap_bins_gby_1/overlap_bins_gby_1.3.ast |   2 +-
 .../overlap_bins_gby_3/overlap_bins_gby_3.3.ast |   1 -
 .../tinysocial-suite-open/group-by.26.ast       |   2 +-
 .../query-ASTERIXDB-1263.23.ast                 |   3 -
 .../query-ASTERIXDB-1263.23.ast                 |   3 -
 .../q03_shipping_priority_nt.3.ast              |   4 +-
 .../q04_order_priority/q04_order_priority.3.ast |  14 +-
 .../q05_local_supplier_volume.3.ast             |   2 +-
 .../q07_volume_shipping.3.ast                   |   3 +-
 .../q11_important_stock.3.ast                   |   3 +-
 .../q12_shipping/q12_shipping.3.ast             |   2 +-
 .../q14_promotion_effect.3.ast                  |   4 +-
 .../q18_large_volume_customer.3.ast             |   8 +-
 .../q21_suppliers_who_kept_orders_waiting.3.ast |  12 +-
 .../q22_global_sales_opportunity.3.ast          |   1 -
 .../query-issue601/query-issue601.3.ast         |  12 +-
 .../query-issue785-2/query-issue785-2.3.ast     |   7 +-
 .../query-issue785/query-issue785.3.ast         |   7 +-
 .../query-issue786/query-issue786.3.ast         |   4 +-
 .../tpch/nest_aggregate/nest_aggregate.3.ast    |   4 +-
 .../q01_pricing_summary_report_nt.3.ast         |  12 +-
 .../q03_shipping_priority_nt.3.ast              |   4 +-
 .../q04_order_priority/q04_order_priority.3.ast |   2 +-
 .../q05_local_supplier_volume.3.ast             |   2 +-
 .../q07_volume_shipping.3.ast                   |   3 +-
 .../q11_important_stock.3.ast                   |   3 +-
 .../tpch/q12_shipping/q12_shipping.3.ast        |   2 +-
 .../q14_promotion_effect.3.ast                  |   4 +-
 .../q17_large_gby_variant.3.ast                 |  12 +-
 .../q18_large_volume_customer.3.ast             |   8 +-
 .../q22_global_sales_opportunity.3.ast          |  13 +-
 .../tpch/query-issue562/query-issue562.3.ast    |   1 -
 .../query-issue785-2/query-issue785-2.3.ast     |   7 +-
 .../tpch/query-issue785/query-issue785.3.ast    |   7 +-
 .../tpch/query-issue786/query-issue786.3.ast    |   4 +-
 .../src/test/resources/runtimets/testsuite.xml  |   5 +
 .../resources/runtimets/testsuite_sqlpp.xml     |  63 ++-
 .../aql/visitor/AQLToSQLPPPrintVisitor.java     |   2 +-
 .../asterix-lang-aql/src/main/javacc/AQL.jj     |   8 +-
 .../lang/common/clause/GroupbyClause.java       |  25 +-
 .../asterix/lang/common/context/Scope.java      |   4 +
 .../lang/common/expression/VariableExpr.java    |   9 -
 .../ExpressionSubstitutionEnvironment.java      | 165 -------
 .../lang/common/struct/VarIdentifier.java       |  22 +-
 .../CloneAndSubstituteVariablesVisitor.java     |  21 +-
 .../lang/common/visitor/FormatPrintVisitor.java |  19 +-
 .../lang/common/visitor/QueryPrintVisitor.java  |  13 +-
 .../visitor/SubstituteExpressionVisitor.java    | 245 ----------
 .../rewrites/SqlppFunctionBodyRewriter.java     |   3 -
 .../lang/sqlpp/rewrites/SqlppQueryRewriter.java |  15 +-
 .../visitor/InlineColumnAliasVisitor.java       | 457 +++----------------
 .../visitor/InlineWithExpressionVisitor.java    |  32 +-
 .../SqlppGlobalAggregationSugarVisitor.java     |   7 +-
 .../visitor/SqlppGroupBySugarVisitor.java       |  69 +--
 .../rewrites/visitor/SqlppGroupByVisitor.java   | 185 +++++---
 .../visitor/SqlppInlineUdfsVisitor.java         |  15 +-
 ...uteGroupbyExpressionWithVariableVisitor.java |  55 ++-
 .../visitor/VariableCheckAndRewriteVisitor.java |   9 +-
 .../lang/sqlpp/util/SqlppRewriteUtil.java       |  35 +-
 .../util/SqlppVariableSubstitutionUtil.java     |  92 ----
 .../lang/sqlpp/util/SqlppVariableUtil.java      |  14 +-
 .../lang/sqlpp/visitor/DeepCopyVisitor.java     |  16 +-
 .../lang/sqlpp/visitor/FreeVariableVisitor.java |   6 +-
 ...SqlppCloneAndSubstituteVariablesVisitor.java |  99 ++--
 .../SqlppSubstituteExpressionVisitor.java       |  69 +++
 .../SqlppSubstituteExpressionsVisitor.java      | 287 ------------
 .../SqlppSubstituteVariablesVisitor.java        |  48 --
 .../AbstractSqlppExpressionScopingVisitor.java  |  80 +++-
 .../AbstractSqlppSimpleExpressionVisitor.java   |  90 ++--
 .../asterix-lang-sqlpp/src/main/javacc/SQLPP.jj |   3 +-
 .../om/functions/AsterixBuiltinFunctions.java   |  19 +
 .../impl/FieldAccessByNameResultType.java       |   6 +-
 .../impl/PropagateTypeComputer.java             |  37 ++
 .../org/apache/asterix/om/types/TypeHelper.java |   5 +-
 .../FirstElementAggregateDescriptor.java        |  46 ++
 .../collections/FirstElementEvalFactory.java    |  95 ++++
 .../LocalFirstElementAggregateDescriptor.java   |  45 ++
 .../properties/LocalGroupingProperty.java       |   4 +-
 .../rules/EliminateGroupByEmptyKeyRule.java     |  11 +-
 .../rules/ExtractGroupByDecorVariablesRule.java |  91 ++++
 ...moveCartesianProductWithEmptyBranchRule.java |  14 +-
 .../RemoveUnusedAssignAndAggregateRule.java     |  61 ++-
 236 files changed, 3134 insertions(+), 3291 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
index eb4751d..720de76 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
@@ -33,6 +33,7 @@ import org.apache.asterix.optimizer.rules.ConstantFoldingRule;
 import org.apache.asterix.optimizer.rules.CountVarToCountOneRule;
 import org.apache.asterix.optimizer.rules.DisjunctivePredicateToJoinRule;
 import org.apache.asterix.optimizer.rules.ExtractDistinctByExpressionsRule;
+import org.apache.hyracks.algebricks.rewriter.rules.ExtractGroupByDecorVariablesRule;
 import org.apache.asterix.optimizer.rules.ExtractOrderExpressionsRule;
 import org.apache.asterix.optimizer.rules.FeedScanCollectionToUnnest;
 import org.apache.asterix.optimizer.rules.FuzzyEqRule;
@@ -131,9 +132,10 @@ public final class RuleCollections {
     }
 
     public static final List<IAlgebraicRewriteRule> buildInitialTranslationRuleCollection() {
-        List<IAlgebraicRewriteRule> typeInfer = new LinkedList<>();
-        typeInfer.add(new TranslateIntervalExpressionRule());
-        return typeInfer;
+        List<IAlgebraicRewriteRule> translationRules = new LinkedList<>();
+        translationRules.add(new TranslateIntervalExpressionRule());
+        translationRules.add(new ExtractGroupByDecorVariablesRule());
+        return translationRules;
     }
 
     public static final List<IAlgebraicRewriteRule> buildTypeInferenceRuleCollection() {

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
index a162209..168019b 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
@@ -100,8 +100,24 @@ public class PushAggregateIntoGroupbyRule implements IAlgebraicRewriteRule {
                 }
             }
         }
+
+        // Collects subplans that is to be removed.
+        Map<GroupByOperator, List<ILogicalPlan>> gbyToSubplanListMap = new HashMap<>();
         for (Pair<GroupByOperator, Integer> remove : removeList) {
-            remove.first.getNestedPlans().remove((int)remove.second);
+            GroupByOperator groupByOperator = remove.first;
+            ILogicalPlan subplan = remove.first.getNestedPlans().get(remove.second);
+            if(gbyToSubplanListMap.containsKey(groupByOperator)) {
+                List<ILogicalPlan> subplans =  gbyToSubplanListMap.get(groupByOperator);
+                subplans.add(subplan);
+            } else {
+                List<ILogicalPlan> subplans = new ArrayList<>();
+                subplans.add(subplan);
+                gbyToSubplanListMap.put(groupByOperator, subplans);
+            }
+        }
+        // Removes subplans.
+        for(Map.Entry<GroupByOperator, List<ILogicalPlan>> entry: gbyToSubplanListMap.entrySet()){
+            entry.getKey().getNestedPlans().removeAll(entry.getValue());
         }
     }
 

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ResolveVariableRule.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ResolveVariableRule.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ResolveVariableRule.java
index d407a7b..1d3e904 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ResolveVariableRule.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/ResolveVariableRule.java
@@ -43,20 +43,18 @@ import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
 import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
 import org.apache.hyracks.algebricks.core.algebra.base.LogicalExpressionTag;
-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.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IAlgebricksConstantValue;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IVariableTypeEnvironment;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
-import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
 import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
 
 /**
  * This rule resolves references to undefined identifiers as:
- * 1. variable/field-access paths, or
+ * 1. expression + field-access paths, or
  * 2. datasets
- * based on the available type and metatadata information.
+ * based on the available type and metadata information.
  */
 public class ResolveVariableRule implements IAlgebraicRewriteRule {
 
@@ -94,8 +92,8 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
         }
         boolean changed = false;
         AbstractFunctionCallExpression funcExpr = (AbstractFunctionCallExpression) expr;
-        Triple<Boolean, String, String> fullyQualifiedDatasetPathCandidate =
-                resolveFullyQualifiedPath(funcExpr, context);
+        Triple<Boolean, String, String> fullyQualifiedDatasetPathCandidate = resolveFullyQualifiedPath(funcExpr,
+                context);
         for (Mutable<ILogicalExpression> funcArgRef : funcExpr.getArguments()) {
             if (rewriteExpressionReference(op, funcArgRef, fullyQualifiedDatasetPathCandidate, exprRef, context)) {
                 changed = true;
@@ -123,35 +121,35 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
         ILogicalExpression arg = funcExpr.getArguments().get(0).getValue();
         String unresolvedVarName = extractConstantString(arg);
         return resolveInternal(exprRef, hasMatchedDatasetForVariableName(unresolvedVarName, context),
-                findCandidatePaths(op, extractPossibleVariables(funcExpr.getArguments()), unresolvedVarName, context),
-                unresolvedVarName, fullyQualifiedDatasetPathCandidateFromParent, parentFuncRef);
+                findCandidatePaths(op, extractExprs(funcExpr.getArguments()), unresolvedVarName, context),
+                unresolvedVarName, fullyQualifiedDatasetPathCandidateFromParent, parentFuncRef, context);
     }
 
-    // Extracts all possible variables from the arguments of the "resolve" function.
-    private List<LogicalVariable> extractPossibleVariables(List<Mutable<ILogicalExpression>> args)
-            throws AlgebricksException {
-        List<LogicalVariable> vars = new ArrayList<>();
+    // Extracts all possible expressions from the arguments of the "resolve" function.
+    private List<ILogicalExpression> extractExprs(List<Mutable<ILogicalExpression>> args) throws AlgebricksException {
+        List<ILogicalExpression> exprs = new ArrayList<>();
         // The first arg is is the name of the undefined variable.
         for (int index = 1; index < args.size(); ++index) {
-            vars.add(extractVariable(args.get(index).getValue()));
+            ILogicalExpression argExpr = args.get(index).getValue();
+            exprs.add(argExpr);
         }
-        return vars;
+        return exprs;
     }
 
     // Resolves an undefined name to a dataset or a fully qualified variable/field-access path
     // based on the given information of dataset matches and candidate paths.
     private boolean resolveInternal(Mutable<ILogicalExpression> funcRef, boolean hasMatchedDataset,
-            Collection<Pair<LogicalVariable, List<String>>> varAccessCandidates, String unresolvedVarName,
+            Collection<Pair<ILogicalExpression, List<String>>> varAccessCandidates, String unresolvedVarName,
             Triple<Boolean, String, String> fullyQualifiedDatasetPathCandidateFromParent,
-            Mutable<ILogicalExpression> parentFuncRef) throws AlgebricksException {
+            Mutable<ILogicalExpression> parentFuncRef, IOptimizationContext context) throws AlgebricksException {
         AbstractFunctionCallExpression func = (AbstractFunctionCallExpression) funcRef.getValue();
         int numVarCandidates = varAccessCandidates.size();
-        boolean hasAmbiguity =
-                hasAmbiguity(hasMatchedDataset, fullyQualifiedDatasetPathCandidateFromParent, numVarCandidates);
+        boolean hasAmbiguity = hasAmbiguity(hasMatchedDataset, fullyQualifiedDatasetPathCandidateFromParent,
+                numVarCandidates);
         if (hasAmbiguity) {
             // More than one possibilities.
-            throw new AlgebricksException(
-                    "Cannot resolve ambiguous alias (variable) reference for identifier " + unresolvedVarName);
+            throw new AlgebricksException("Cannot resolve ambiguous alias reference for undefined identifier "
+                    + unresolvedVarName);
         } else if (hasMatchedDataset) {
             // Rewrites the "resolve" function to a "dataset" function and only keep the dataset name argument.
             func.setFunctionInfo(FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.DATASET));
@@ -163,15 +161,17 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
             AbstractFunctionCallExpression parentFunc = (AbstractFunctionCallExpression) parentFuncRef.getValue();
             parentFunc.setFunctionInfo(FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.DATASET));
             parentFunc.getArguments().clear();
-            parentFunc.getArguments()
-                    .add(new MutableObject<ILogicalExpression>(new ConstantExpression(
+            parentFunc.getArguments().add(
+                    new MutableObject<>(new ConstantExpression(
                             new AsterixConstantValue(new AString(fullyQualifiedDatasetPathCandidateFromParent.second
                                     + "." + fullyQualifiedDatasetPathCandidateFromParent.third)))));
         } else if (numVarCandidates == 1) {
             resolveAsFieldAccess(funcRef, varAccessCandidates.iterator().next());
         } else {
+            AqlMetadataProvider metadataProvider = (AqlMetadataProvider) context.getMetadataProvider();
             // Cannot find any resolution.
-            throw new AlgebricksException("Undefined alias (variable) reference for identifier " + unresolvedVarName);
+            throw new AlgebricksException("Cannot find dataset " + unresolvedVarName + " in dataverse "
+                    + metadataProvider.getDefaultDataverseName() + " nor an alias with name " + unresolvedVarName);
         }
         return true;
     }
@@ -187,17 +187,16 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
 
     // Resolves a "resolve" function call as a field access.
     private void resolveAsFieldAccess(Mutable<ILogicalExpression> funcRef,
-            Pair<LogicalVariable, List<String>> varAndPath) {
+            Pair<ILogicalExpression, List<String>> varAndPath) {
         // Rewrites to field-access-by-names.
-        LogicalVariable var = varAndPath.first;
+        ILogicalExpression expr = varAndPath.first;
         List<String> path = varAndPath.second;
-        Mutable<ILogicalExpression> firstArgRef = new MutableObject<>(new VariableReferenceExpression(var));
+        Mutable<ILogicalExpression> firstArgRef = new MutableObject<>(expr);
         ILogicalExpression newFunc = null;
         for (String fieldName : path) {
             List<Mutable<ILogicalExpression>> args = new ArrayList<>();
             args.add(firstArgRef);
-            args.add(new MutableObject<ILogicalExpression>(
-                    new ConstantExpression(new AsterixConstantValue(new AString(fieldName)))));
+            args.add(new MutableObject<>(new ConstantExpression(new AsterixConstantValue(new AString(fieldName)))));
             newFunc = new ScalarFunctionCallExpression(
                     FunctionUtil.getFunctionInfo(AsterixBuiltinFunctions.FIELD_ACCESS_BY_NAME), args);
             firstArgRef = new MutableObject<>(newFunc);
@@ -205,23 +204,23 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
         funcRef.setValue(newFunc);
     }
 
-    // Finds all candidate fully qualified variable/field-access paths.
-    private Set<Pair<LogicalVariable, List<String>>> findCandidatePaths(ILogicalOperator op,
-            Collection<LogicalVariable> inputLiveVars, String unresolvedVarName, IOptimizationContext context)
+    // Finds all candidate fully qualified expression/field-access paths.
+    private Set<Pair<ILogicalExpression, List<String>>> findCandidatePaths(ILogicalOperator op,
+            Collection<ILogicalExpression> referenceExprs, String unresolvedVarName, IOptimizationContext context)
             throws AlgebricksException {
-        Set<Pair<LogicalVariable, List<String>>> candidates = new HashSet<>();
+        Set<Pair<ILogicalExpression, List<String>>> candidates = new HashSet<>();
         IVariableTypeEnvironment env = context.getOutputTypeEnvironment(op.getInputs().get(0).getValue());
-        for (LogicalVariable var : inputLiveVars) {
-            IAType type = (IAType) env.getVarType(var);
-            candidates.addAll(findCandidatePathsForVariable(unresolvedVarName, type, var, new ArrayList<String>()));
+        for (ILogicalExpression referenceExpr : referenceExprs) {
+            IAType type = (IAType) env.getType(referenceExpr);
+            candidates.addAll(findCandidatePathsForExpr(unresolvedVarName, type, referenceExpr, new ArrayList<>()));
         }
         return candidates;
     }
 
-    // Recursively finds candidate paths under a variable.
-    private Set<Pair<LogicalVariable, List<String>>> findCandidatePathsForVariable(String unresolvedVarName,
-            IAType pathType, LogicalVariable var, List<String> parentPath) throws AlgebricksException {
-        Set<Pair<LogicalVariable, List<String>>> varAccessCandidates = new HashSet<>();
+    // Recursively finds candidate paths under an expression.
+    private Set<Pair<ILogicalExpression, List<String>>> findCandidatePathsForExpr(String unresolvedVarName,
+            IAType pathType, ILogicalExpression expr, List<String> parentPath) throws AlgebricksException {
+        Set<Pair<ILogicalExpression, List<String>>> varAccessCandidates = new HashSet<>();
         IAType type = pathType;
         if (type.getTypeTag() == ATypeTag.UNION) {
             type = ((AUnionType) type).getActualType();
@@ -230,7 +229,7 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
         if (tag == ATypeTag.ANY) {
             List<String> path = new ArrayList<>(parentPath);
             path.add(unresolvedVarName);
-            varAccessCandidates.add(new Pair<>(var, path));
+            varAccessCandidates.add(new Pair<>(expr, path));
         }
         if (tag == ATypeTag.RECORD) {
             ARecordType recordType = (ARecordType) type;
@@ -238,7 +237,7 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
                 // If the field name is possible.
                 List<String> path = new ArrayList<>(parentPath);
                 path.add(unresolvedVarName);
-                varAccessCandidates.add(new Pair<>(var, path));
+                varAccessCandidates.add(new Pair<>(expr, path));
             } else {
                 // Recursively identified possible paths.
                 String[] fieldNames = recordType.getFieldNames();
@@ -246,8 +245,8 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
                 for (int index = 0; index < fieldNames.length; ++index) {
                     List<String> path = new ArrayList<>(parentPath);
                     path.add(fieldNames[index]);
-                    varAccessCandidates
-                            .addAll(findCandidatePathsForVariable(unresolvedVarName, fieldTypes[index], var, path));
+                    varAccessCandidates.addAll(findCandidatePathsForExpr(unresolvedVarName, fieldTypes[index], expr,
+                            path));
                 }
             }
         }
@@ -284,10 +283,7 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
     private boolean hasMatchedDataverseDataset(String dataverseName, String datasetName, IOptimizationContext context)
             throws AlgebricksException {
         AqlMetadataProvider mdp = (AqlMetadataProvider) context.getMetadataProvider();
-        if (mdp.findDataset(dataverseName, datasetName) != null) {
-            return true;
-        }
-        return false;
+        return mdp.findDataset(dataverseName, datasetName) != null;
     }
 
     // Checks whether the name matches a dataset.
@@ -309,17 +305,6 @@ public class ResolveVariableRule implements IAlgebraicRewriteRule {
         return false;
     }
 
-    // Extracts the variable from a variable reference expression.
-    private LogicalVariable extractVariable(ILogicalExpression expr) throws AlgebricksException {
-        if (expr.getExpressionTag() == LogicalExpressionTag.VARIABLE) {
-            VariableReferenceExpression varRefExpr = (VariableReferenceExpression) expr;
-            return varRefExpr.getVariableReference();
-        } else {
-            // The sugar visitor gurantees this would not happen.
-            throw new AlgebricksException("The argument should be a variable reference expression.");
-        }
-    }
-
     // Cleans up scan collections on top of a "dataset" function call since "dataset"
     // is an unnest function.
     private void cleanupScanCollectionForDataset(AbstractFunctionCallExpression funcExpr) {

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
index 6bcdd90..79342c9 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlExpressionToPlanTranslator.java
@@ -30,8 +30,8 @@ import org.apache.asterix.lang.aql.expression.UnionExpr;
 import org.apache.asterix.lang.aql.visitor.base.IAQLVisitor;
 import org.apache.asterix.lang.common.base.Clause;
 import org.apache.asterix.lang.common.base.Expression;
-import org.apache.asterix.lang.common.base.Expression.Kind;
 import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.base.Expression.Kind;
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.statement.Query;
 import org.apache.asterix.metadata.declared.AqlMetadataProvider;
@@ -42,7 +42,10 @@ import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
 import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
 import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+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.AssignOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.DistinctOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator;
@@ -103,13 +106,13 @@ class AqlExpressionToPlanTranslator extends LangExpressionToPlanTranslator imple
         }
 
         Expression r = flwor.getReturnExpr();
-        boolean noFlworClause = flwor.noForClause();
+        boolean noForClause = flwor.noForClause();
 
         Pair<ILogicalOperator, LogicalVariable> result;
         if (r.getKind() == Kind.VARIABLE_EXPRESSION) {
             VariableExpr v = (VariableExpr) r;
             LogicalVariable var = context.getVar(v.getVar().getId());
-            result = produceFlworPlan(noFlworClause, isTop, flworPlan, var);
+            result = produceFlworPlan(noForClause, isTop, flworPlan, var);
         } else {
             Mutable<ILogicalOperator> baseOp = new MutableObject<>(flworPlan.getValue());
             Pair<ILogicalOperator, LogicalVariable> rRes = r.accept(this, baseOp);
@@ -125,7 +128,7 @@ class AqlExpressionToPlanTranslator extends LangExpressionToPlanTranslator imple
                 baseOp.setValue(new NestedTupleSourceOperator(new MutableObject<ILogicalOperator>(s)));
             }
             Mutable<ILogicalOperator> resOpRef = new MutableObject<>(resOp);
-            result = produceFlworPlan(noFlworClause, isTop, resOpRef, rRes.second);
+            result = produceFlworPlan(noForClause, isTop, resOpRef, rRes.second);
         }
         if (!isTop) {
             context.exitSubplan();
@@ -164,6 +167,17 @@ class AqlExpressionToPlanTranslator extends LangExpressionToPlanTranslator imple
         return aggListifyForSubquery(result.second, new MutableObject<>(result.first), false);
     }
 
+    @Override
+    protected boolean expressionNeedsNoNesting(Expression expr) {
+        boolean noForFLWOR = false;
+        // No nesting is needed for a FLWOR expression without a FOR clause.
+        if (expr.getKind() == Kind.FLWOGR_EXPRESSION) {
+            FLWOGRExpression flwor = (FLWOGRExpression) expr;
+            noForFLWOR = flwor.noForClause();
+        }
+        return noForFLWOR || super.expressionNeedsNoNesting(expr);
+    }
+
     private Pair<ILogicalOperator, LogicalVariable> produceFlworPlan(boolean noForClause, boolean isTop,
             Mutable<ILogicalOperator> resOpRef, LogicalVariable resVar) {
         if (isTop) {
@@ -171,7 +185,15 @@ class AqlExpressionToPlanTranslator extends LangExpressionToPlanTranslator imple
             pr.getInputs().add(resOpRef);
             return new Pair<>(pr, resVar);
         } else if (noForClause) {
-            return new Pair<>(resOpRef.getValue(), resVar);
+            ILogicalOperator resOp = resOpRef.getValue();
+            if (resOp.getOperatorTag() == LogicalOperatorTag.ASSIGN) {
+                return new Pair<>(resOp, resVar);
+            }
+            LogicalVariable newResVar = context.newVar();
+            ILogicalOperator assign =
+                    new AssignOperator(newResVar, new MutableObject<>(new VariableReferenceExpression(resVar)));
+            assign.getInputs().add(resOpRef);
+            return new Pair<>(assign, newResVar);
         } else {
             return aggListifyForSubquery(resVar, resOpRef, false);
         }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlPlusExpressionToPlanTranslator.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlPlusExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlPlusExpressionToPlanTranslator.java
index 39648d1..334436a 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlPlusExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/AqlPlusExpressionToPlanTranslator.java
@@ -22,6 +22,7 @@ import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.Iterator;
 import java.util.List;
+import java.util.Map.Entry;
 import java.util.logging.Logger;
 
 import org.apache.asterix.common.config.DatasetConfig.DatasetType;
@@ -547,22 +548,22 @@ public class AqlPlusExpressionToPlanTranslator extends AbstractLangTranslator
         }
         gOp.getInputs().add(topOp);
 
-        for (VariableExpr var : gc.getWithVarList()) {
+        for (Entry<Expression, VariableExpr> entry : gc.getWithVarMap().entrySet()) {
             LogicalVariable aggVar = context.newVar();
-            LogicalVariable oldVar = context.getVar(var);
-            List<Mutable<ILogicalExpression>> flArgs = new ArrayList<Mutable<ILogicalExpression>>(1);
-            flArgs.add(new MutableObject<ILogicalExpression>(new VariableReferenceExpression(oldVar)));
+            Pair<ILogicalExpression, Mutable<ILogicalOperator>> listifyInput = aqlExprToAlgExpression(entry.getKey(),
+                    new MutableObject<>(new NestedTupleSourceOperator(new MutableObject<ILogicalOperator>(gOp))));
+            List<Mutable<ILogicalExpression>> flArgs = new ArrayList<>(1);
+            flArgs.add(new MutableObject<>(listifyInput.first));
             AggregateFunctionCallExpression fListify =
                     AsterixBuiltinFunctions.makeAggregateFunctionExpression(AsterixBuiltinFunctions.LISTIFY, flArgs);
             AggregateOperator agg = new AggregateOperator(mkSingletonArrayList(aggVar),
-                    (List) mkSingletonArrayList(new MutableObject<ILogicalExpression>(fListify)));
-            agg.getInputs().add(new MutableObject<ILogicalOperator>(
-                    new NestedTupleSourceOperator(new MutableObject<ILogicalOperator>(gOp))));
-            ILogicalPlan plan = new ALogicalPlanImpl(new MutableObject<ILogicalOperator>(agg));
+                    mkSingletonArrayList(new MutableObject<>(fListify)));
+            agg.getInputs().add(listifyInput.second);
+            ILogicalPlan plan = new ALogicalPlanImpl(new MutableObject<>(agg));
             gOp.getNestedPlans().add(plan);
             // Hide the variable that was part of the "with", replacing it with
             // the one bound by the aggregation op.
-            context.setVar(var, aggVar);
+            context.setVar(entry.getValue(), aggVar);
         }
 
         gOp.getAnnotations().put(OperatorAnnotations.USE_HASH_GROUP_BY, gc.hasHashGroupByHint());

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
index d85419e..bd87e6e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/LangExpressionToPlanTranslator.java
@@ -18,6 +18,8 @@
  */
 package org.apache.asterix.translator;
 
+import static java.util.logging.Logger.global;
+
 import java.io.File;
 import java.io.IOException;
 import java.util.ArrayList;
@@ -28,6 +30,7 @@ import java.util.Iterator;
 import java.util.List;
 import java.util.Map;
 import java.util.Set;
+import java.util.Map.Entry;
 import java.util.concurrent.atomic.AtomicLong;
 
 import org.apache.asterix.algebra.base.ILangExpressionToPlanTranslator;
@@ -38,15 +41,15 @@ import org.apache.asterix.common.functions.FunctionConstants;
 import org.apache.asterix.common.functions.FunctionSignature;
 import org.apache.asterix.lang.aql.util.RangeMapBuilder;
 import org.apache.asterix.lang.common.base.Expression;
-import org.apache.asterix.lang.common.base.Expression.Kind;
 import org.apache.asterix.lang.common.base.ILangExpression;
 import org.apache.asterix.lang.common.base.Statement;
+import org.apache.asterix.lang.common.base.Expression.Kind;
 import org.apache.asterix.lang.common.clause.GroupbyClause;
 import org.apache.asterix.lang.common.clause.LetClause;
 import org.apache.asterix.lang.common.clause.LimitClause;
 import org.apache.asterix.lang.common.clause.OrderbyClause;
-import org.apache.asterix.lang.common.clause.OrderbyClause.OrderModifier;
 import org.apache.asterix.lang.common.clause.WhereClause;
+import org.apache.asterix.lang.common.clause.OrderbyClause.OrderModifier;
 import org.apache.asterix.lang.common.expression.CallExpr;
 import org.apache.asterix.lang.common.expression.FieldAccessor;
 import org.apache.asterix.lang.common.expression.FieldBinding;
@@ -54,14 +57,14 @@ import org.apache.asterix.lang.common.expression.GbyVariableExpressionPair;
 import org.apache.asterix.lang.common.expression.IfExpr;
 import org.apache.asterix.lang.common.expression.IndexAccessor;
 import org.apache.asterix.lang.common.expression.ListConstructor;
-import org.apache.asterix.lang.common.expression.ListConstructor.Type;
 import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.OperatorExpr;
 import org.apache.asterix.lang.common.expression.QuantifiedExpression;
-import org.apache.asterix.lang.common.expression.QuantifiedExpression.Quantifier;
 import org.apache.asterix.lang.common.expression.RecordConstructor;
 import org.apache.asterix.lang.common.expression.UnaryExpr;
 import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.expression.ListConstructor.Type;
+import org.apache.asterix.lang.common.expression.QuantifiedExpression.Quantifier;
 import org.apache.asterix.lang.common.literal.StringLiteral;
 import org.apache.asterix.lang.common.statement.FunctionDecl;
 import org.apache.asterix.lang.common.statement.Query;
@@ -72,13 +75,13 @@ import org.apache.asterix.lang.common.util.FunctionUtil;
 import org.apache.asterix.lang.common.visitor.base.AbstractQueryExpressionVisitor;
 import org.apache.asterix.metadata.MetadataException;
 import org.apache.asterix.metadata.MetadataManager;
-import org.apache.asterix.metadata.declared.AqlDataSource.AqlDataSourceType;
 import org.apache.asterix.metadata.declared.AqlMetadataProvider;
 import org.apache.asterix.metadata.declared.AqlSourceId;
 import org.apache.asterix.metadata.declared.DatasetDataSource;
 import org.apache.asterix.metadata.declared.LoadableDataSource;
 import org.apache.asterix.metadata.declared.ResultSetDataSink;
 import org.apache.asterix.metadata.declared.ResultSetSinkId;
+import org.apache.asterix.metadata.declared.AqlDataSource.AqlDataSourceType;
 import org.apache.asterix.metadata.entities.Dataset;
 import org.apache.asterix.metadata.entities.Function;
 import org.apache.asterix.metadata.entities.InternalDatasetDetails;
@@ -114,15 +117,15 @@ 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.base.OperatorAnnotations;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression;
-import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression.FunctionKind;
 import org.apache.hyracks.algebricks.core.algebra.expressions.AggregateFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation;
-import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation.BroadcastSide;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ConstantExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
 import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.UnnestingFunctionCallExpression;
 import org.apache.hyracks.algebricks.core.algebra.expressions.VariableReferenceExpression;
+import org.apache.hyracks.algebricks.core.algebra.expressions.AbstractFunctionCallExpression.FunctionKind;
+import org.apache.hyracks.algebricks.core.algebra.expressions.BroadcastExpressionAnnotation.BroadcastSide;
 import org.apache.hyracks.algebricks.core.algebra.functions.AlgebricksBuiltinFunctions;
 import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
 import org.apache.hyracks.algebricks.core.algebra.functions.IFunctionInfo;
@@ -138,13 +141,13 @@ import org.apache.hyracks.algebricks.core.algebra.operators.logical.InsertDelete
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.LimitOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.NestedTupleSourceOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator;
-import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator.IOrder.OrderKind;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.ProjectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SinkOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnionAllOperator;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.OrderOperator.IOrder.OrderKind;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.LogicalOperatorDeepCopyWithNewVariablesVisitor;
 import org.apache.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
 import org.apache.hyracks.algebricks.core.algebra.plan.ALogicalPlanImpl;
@@ -786,18 +789,38 @@ class LangExpressionToPlanTranslator
         if (gc.isGroupAll()) {
             List<LogicalVariable> aggVars = new ArrayList<>();
             List<Mutable<ILogicalExpression>> aggFuncs = new ArrayList<>();
-            for (VariableExpr var : gc.getWithVarList()) {
-                LogicalVariable aggVar = context.newVar();
-                LogicalVariable oldVar = context.getVar(var);
+            // A global aggregation can still have a decoration variable list which are used for propagate
+            // outer-scope variables. Example query:
+            // asterixdb/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/global-aggregate/q09
+            for (GbyVariableExpressionPair ve : gc.getDecorPairList()) {
+                VariableExpr vexpr = ve.getVar();
+                LogicalVariable decorVar = vexpr == null ? context.newVar() : context.newVar(vexpr);
+                Pair<ILogicalExpression, Mutable<ILogicalOperator>> eo = langExprToAlgExpression(ve.getExpr(), topOp);
+                topOp = eo.second;
+                List<Mutable<ILogicalExpression>> flArgs = new ArrayList<>();
+                flArgs.add(new MutableObject<>(eo.first));
+                // Calls the first-element aggregate function on a decoration variable to make sure the value
+                // is propagated through a global aggregation.
+                AggregateFunctionCallExpression firstElementAgg = AsterixBuiltinFunctions
+                        .makeAggregateFunctionExpression(
+                        AsterixBuiltinFunctions.FIRST_ELEMENT, flArgs);
+                aggVars.add(decorVar);
+                aggFuncs.add(new MutableObject<>(firstElementAgg));
+            }
+            for (Entry<Expression, VariableExpr> entry : gc.getWithVarMap().entrySet()) {
+                Pair<ILogicalExpression, Mutable<ILogicalOperator>> listifyInput =
+                        langExprToAlgExpression(entry.getKey(), topOp);
+                topOp = listifyInput.second;
                 List<Mutable<ILogicalExpression>> flArgs = new ArrayList<>();
-                flArgs.add(new MutableObject<>(new VariableReferenceExpression(oldVar)));
+                flArgs.add(new MutableObject<>(listifyInput.first));
                 AggregateFunctionCallExpression fListify = AsterixBuiltinFunctions
                         .makeAggregateFunctionExpression(AsterixBuiltinFunctions.LISTIFY, flArgs);
+                LogicalVariable aggVar = context.newVar();
                 aggVars.add(aggVar);
                 aggFuncs.add(new MutableObject<>(fListify));
                 // Hide the variable that was part of the "with", replacing it with
                 // the one bound by the aggregation op.
-                context.setVar(var, aggVar);
+                context.setVar(entry.getValue(), aggVar);
             }
             AggregateOperator aggOp = new AggregateOperator(aggVars, aggFuncs);
             aggOp.getInputs().add(topOp);
@@ -830,22 +853,23 @@ class LangExpressionToPlanTranslator
             }
 
             gOp.getInputs().add(topOp);
-            for (VariableExpr var : gc.getWithVarList()) {
-                LogicalVariable aggVar = context.newVar();
-                LogicalVariable oldVar = context.getVar(var);
+            for (Entry<Expression, VariableExpr> entry : gc.getWithVarMap().entrySet()) {
+                Pair<ILogicalExpression, Mutable<ILogicalOperator>> listifyInput = langExprToAlgExpression(
+                        entry.getKey(), new MutableObject<>(new NestedTupleSourceOperator(new MutableObject<>(gOp))));
                 List<Mutable<ILogicalExpression>> flArgs = new ArrayList<>(1);
-                flArgs.add(new MutableObject<>(new VariableReferenceExpression(oldVar)));
+                flArgs.add(new MutableObject<>(listifyInput.first));
                 AggregateFunctionCallExpression fListify = AsterixBuiltinFunctions
                         .makeAggregateFunctionExpression(AsterixBuiltinFunctions.LISTIFY, flArgs);
+                LogicalVariable aggVar = context.newVar();
                 AggregateOperator agg = new AggregateOperator(mkSingletonArrayList(aggVar),
-                        (List) mkSingletonArrayList(new MutableObject<>(fListify)));
+                        mkSingletonArrayList(new MutableObject<>(fListify)));
 
-                agg.getInputs().add(new MutableObject<>(new NestedTupleSourceOperator(new MutableObject<>(gOp))));
+                agg.getInputs().add(listifyInput.second);
                 ILogicalPlan plan = new ALogicalPlanImpl(new MutableObject<>(agg));
                 gOp.getNestedPlans().add(plan);
                 // Hide the variable that was part of the "with", replacing it with
                 // the one bound by the aggregation op.
-                context.setVar(var, aggVar);
+                context.setVar(entry.getValue(), aggVar);
             }
             gOp.getAnnotations().put(OperatorAnnotations.USE_HASH_GROUP_BY, gc.hasHashGroupByHint());
             return new Pair<>(gOp, null);

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
index 982a334..f2ba140 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/SqlppExpressionToPlanTranslator.java
@@ -173,9 +173,6 @@ class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTranslator imp
             Mutable<ILogicalOperator> tupleSource) throws AsterixException {
         Pair<ILogicalExpression, Mutable<ILogicalOperator>> eo =
                 langExprToAlgExpression(independentSubquery.getExpr(), tupleSource);
-        // Replaces nested tuple source with empty tuple source so that the subquery can be independent
-        // from its input operators.
-        replaceNtsWithEts(eo.second.getValue());
         LogicalVariable var = context.newVar();
         AssignOperator assignOp = new AssignOperator(var, new MutableObject<ILogicalExpression>(eo.first));
         assignOp.getInputs().add(eo.second);
@@ -586,20 +583,6 @@ class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTranslator imp
         }
     }
 
-    // Replaces nested tuple source with empty tuple source in nested subplans of
-    // a subplan operator.
-    private void replaceNtsWithEts(ILogicalOperator op) {
-        if (op.getOperatorTag() != LogicalOperatorTag.SUBPLAN) {
-            return;
-        }
-        SubplanOperator subplanOp = (SubplanOperator) op;
-        for (ILogicalPlan plan : subplanOp.getNestedPlans()) {
-            for (Mutable<ILogicalOperator> rootRef : plan.getRoots()) {
-                replaceNtsWithEtsTopDown(rootRef);
-            }
-        }
-    }
-
     // Recursively replaces nested tuple source with empty tuple source
     // in the operator tree under opRef.
     private void replaceNtsWithEtsTopDown(Mutable<ILogicalOperator> opRef) {
@@ -691,7 +674,10 @@ class SqlppExpressionToPlanTranslator extends LangExpressionToPlanTranslator imp
         for (GbyVariableExpressionPair pair : groupbyClause.getGbyPairList()) {
             fieldBindings.add(getFieldBinding(pair.getVar()));
         }
-        fieldBindings.add(getFieldBinding(groupbyClause.getGroupVar()));
+        if (groupbyClause.hasWithMap() && groupbyClause.hasGroupVar()) {
+            // Makes sure that we add the re-mapped group variable which refers to a collection.
+            fieldBindings.add(getFieldBinding(groupbyClause.getWithVarMap().get(groupbyClause.getGroupVar())));
+        }
         return fieldBindings;
     }
 

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/FunctionCollection.java
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/FunctionCollection.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/FunctionCollection.java
index 2c0aef4..3201d9e 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/FunctionCollection.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/translator/util/FunctionCollection.java
@@ -24,7 +24,9 @@ import java.util.ArrayList;
 import java.util.List;
 
 import org.apache.asterix.om.functions.IFunctionDescriptorFactory;
+import org.apache.asterix.runtime.aggregates.collections.FirstElementAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.collections.ListifyAggregateDescriptor;
+import org.apache.asterix.runtime.aggregates.collections.LocalFirstElementAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarAvgAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarCountAggregateDescriptor;
 import org.apache.asterix.runtime.aggregates.scalar.ScalarMaxAggregateDescriptor;
@@ -313,6 +315,8 @@ public class FunctionCollection {
         temp.add(LocalMaxAggregateDescriptor.FACTORY);
         temp.add(MinAggregateDescriptor.FACTORY);
         temp.add(LocalMinAggregateDescriptor.FACTORY);
+        temp.add(FirstElementAggregateDescriptor.FACTORY);
+        temp.add(LocalFirstElementAggregateDescriptor.FACTORY);
 
         // serializable aggregates
         temp.add(SerializableCountAggregateDescriptor.FACTORY);

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-algebra/src/main/javacc/AQLPlus.jj
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-algebra/src/main/javacc/AQLPlus.jj b/asterixdb/asterix-algebra/src/main/javacc/AQLPlus.jj
index 98cae63..e0e9388 100644
--- a/asterixdb/asterix-algebra/src/main/javacc/AQLPlus.jj
+++ b/asterixdb/asterix-algebra/src/main/javacc/AQLPlus.jj
@@ -1381,7 +1381,7 @@ Clause GroupClause()throws ParseException :
       // GbyVariableExpressionPair pair = new GbyVariableExpressionPair();
      List<GbyVariableExpressionPair> vePairList = new ArrayList<GbyVariableExpressionPair>();
     List<GbyVariableExpressionPair> decorPairList = new ArrayList<GbyVariableExpressionPair>();
-    List<VariableExpr> withVarList= new ArrayList<VariableExpr>();
+    Map<Expression, VariableExpr> withVarMap= new HashMap<Expression, VariableExpr>();
     VariableExpr var = null;
     VariableExpr withVar = null;
     Expression expr = null;
@@ -1437,20 +1437,20 @@ Clause GroupClause()throws ParseException :
     {
       if(withVar.getIsNewVar()==true)
           throw new ParseException("can't find variable " + withVar.getVar());
-      withVarList.add(withVar);
+      withVarMap.put(withVar, withVar);
       newScope.addNewVarSymbolToScope(withVar.getVar());
     }
     ("," withVar = VariableRef()
     {
       if(withVar.getIsNewVar()==true)
           throw new ParseException("can't find variable " + withVar.getVar());
-      withVarList.add(withVar);
+      withVarMap.put(withVar, withVar);
       newScope.addNewVarSymbolToScope(withVar.getVar());
     })*
     {
       gbc.setGbyPairList(vePairList);
       gbc.setDecorPairList(decorPairList);
-      gbc.setWithVarList(withVarList);
+      gbc.setWithVarMap(withVarMap);
       replaceCurrentScope(newScope);
       return gbc;
     }

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
index 4495551..773be15 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/aggregate/constant-gby-agg.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$19]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$26]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$19(ASC)] HASH:[$$19]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$15]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$26(ASC)] HASH:[$$26]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$22]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
index 1d34c95..fb6d5fb 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-core.plan
@@ -2,20 +2,20 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$15(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$15(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$18(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$18(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- HYBRID_HASH_JOIN [$$17][$$18]  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$17]  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$20][$$21]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$20]  |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 [$$18]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$21]  |PARTITIONED|
                       -- ASSIGN  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
index 1d34c95..c712344 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/loj-sugar.plan
@@ -2,20 +2,20 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$15(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$15(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$17(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$17(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- HYBRID_HASH_JOIN [$$17][$$18]  |PARTITIONED|
-                    -- HASH_PARTITION_EXCHANGE [$$17]  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$19][$$20]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$19]  |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 [$$18]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$20]  |PARTITIONED|
                       -- ASSIGN  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-desc-using-gby.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-desc-using-gby.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-desc-using-gby.plan
index fa71b96..8c86c6b 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-desc-using-gby.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/orderby-desc-using-gby.plan
@@ -3,12 +3,12 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$1(DESC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$9]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$10]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$9]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- ASSIGN  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
----------------------------------------------------------------------
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 cd0a17e..54716d3 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
@@ -2,14 +2,14 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$8(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$82]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$104]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$82(ASC)] HASH:[$$82]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$59]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$104(ASC)] HASH:[$$104]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$81]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -21,25 +21,25 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$79]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$101]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$79(ASC)] HASH:[$$79]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$72]  |PARTITIONED|
+                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$101(ASC)] HASH:[$$101]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$94]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- STREAM_SELECT  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$72(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$94(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$67][$$64]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$67]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$89][$$86]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$89]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -66,7 +66,7 @@
                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$64]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
index 8276df2..9efd93d 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in.plan
@@ -2,13 +2,13 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$26(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$26(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$29(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$29(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- STREAM_SELECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$19]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$22]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- STREAM_SELECT  |LOCAL|
@@ -17,7 +17,7 @@
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- HYBRID_HASH_JOIN [$$19][$$7]  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$22][$$10]  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- STREAM_SELECT  |PARTITIONED|
@@ -25,7 +25,7 @@
                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$7]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$10]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
index e0b4473..f967282 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/in_correlated.plan
@@ -2,28 +2,35 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$31(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$34(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$34(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- STREAM_SELECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$23]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$26]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- STREAM_SELECT  |LOCAL|
-                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  -- UNNEST  |LOCAL|
+                                    -- MICRO_PRE_CLUSTERED_GROUP_BY[]  |LOCAL|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- STREAM_SELECT  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
                             }
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- HYBRID_HASH_JOIN [$$23][$$22]  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$26][$$25]  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
-                              -- STREAM_SELECT  |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 [$$22]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$25]  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ASSIGN  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
----------------------------------------------------------------------
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 2a9841f..14b3f08 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
@@ -2,14 +2,14 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$8(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$83]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$31(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$83(ASC)] HASH:[$$83]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$60]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$105(ASC)] HASH:[$$105]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$82]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -21,25 +21,25 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- PRE_CLUSTERED_GROUP_BY[$$80]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$102]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$80(ASC)] HASH:[$$80]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$73]  |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 [$$73(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$95(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$68][$$65]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$90][$$87]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$90]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -66,7 +66,7 @@
                                                                                 -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$65]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$87]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/8671ddf8/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping.plan
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping.plan
index 7c1af83..754e03b 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping.plan
@@ -2,14 +2,14 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$3(ASC) ]  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$94]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$12(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$104]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$94(ASC)] HASH:[$$94]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$76]  |PARTITIONED|
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$104(ASC)] HASH:[$$104]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$86]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -17,15 +17,15 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$83][$$76]  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$93][$$86]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$93]  |PARTITIONED|
                           -- UNNEST  |UNPARTITIONED|
                             -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$76]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$85][$$80]  |PARTITIONED|
-                                -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$95][$$90]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$95]  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- STREAM_SELECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|