diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/Projection.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/Projection.java
index bfb5abf..6ca9b71 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/Projection.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/Projection.java
@@ -61,6 +61,10 @@
         return name;
     }
 
+    public void setName(String name) {
+        this.name = name;
+    }
+
     public boolean hasName() {
         return name != null;
     }
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/FunctionParser.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/FunctionParser.java
index 5e5f524..a1ce60d 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/FunctionParser.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/parser/FunctionParser.java
@@ -20,7 +20,6 @@
 package org.apache.asterix.lang.sqlpp.parser;
 
 import java.io.StringReader;
-import java.util.ArrayList;
 import java.util.List;
 
 import org.apache.asterix.common.exceptions.AsterixException;
@@ -29,6 +28,7 @@
 import org.apache.asterix.lang.common.base.Statement;
 import org.apache.asterix.lang.common.statement.FunctionDecl;
 import org.apache.asterix.lang.common.struct.VarIdentifier;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.asterix.metadata.entities.Function;
 
 public class FunctionParser {
@@ -42,16 +42,14 @@
     public FunctionDecl getFunctionDecl(Function function) throws AsterixException {
         String functionBody = function.getFunctionBody();
         List<String> params = function.getParams();
-        List<VarIdentifier> varIdentifiers = new ArrayList<VarIdentifier>();
 
         StringBuilder builder = new StringBuilder();
         builder.append(" use " + function.getDataverseName() + ";");
         builder.append(" declare function " + function.getName().split("@")[0]);
         builder.append("(");
         for (String param : params) {
-            VarIdentifier varId = new VarIdentifier(param);
-            varIdentifiers.add(varId);
-            builder.append(param);
+            VarIdentifier varId = SqlppVariableUtil.toUserDefinedVariableName(param);
+            builder.append(varId);
             builder.append(",");
         }
         if (params.size() > 0) {
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
index cba592a..1f119ae 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
@@ -37,6 +37,9 @@
         // Inlines column aliases.
         inlineColumnAlias();
 
+        // Group-by core/sugar rewrites.
+        rewriteGroupBys();
+
         // Generates ids for variables (considering scopes) but DOES NOT replace unbounded variable access with the dataset function.
         // An unbounded variable within a function could be a bounded variable in the top-level query.
         variableCheckAndRewrite(false);
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
index 3143214..a2c84ba 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
@@ -50,6 +50,7 @@
 import org.apache.asterix.lang.sqlpp.parser.SqlppParserFactory;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.visitor.InlineColumnAliasVisitor;
+import org.apache.asterix.lang.sqlpp.visitor.SqlppGroupByVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.SqlppInlineUdfsVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.VariableCheckAndRewriteVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.base.ISqlppVisitor;
@@ -85,6 +86,9 @@
         // Inlines column aliases.
         inlineColumnAlias();
 
+        // Group-by core/sugar rewrites.
+        rewriteGroupBys();
+
         // Generate ids for variables (considering scopes) and replace global variable access with the dataset function.
         variableCheckAndRewrite(true);
 
@@ -103,7 +107,7 @@
             return;
         }
         // Inline column aliases.
-        InlineColumnAliasVisitor inlineColumnAliasVisitor = new InlineColumnAliasVisitor();
+        InlineColumnAliasVisitor inlineColumnAliasVisitor = new InlineColumnAliasVisitor(context);
         inlineColumnAliasVisitor.visit(topExpr, false);
     }
 
@@ -112,10 +116,18 @@
             return;
         }
         VariableCheckAndRewriteVisitor variableCheckAndRewriteVisitor = new VariableCheckAndRewriteVisitor(context,
-                overwrite);
+                overwrite, metadataProvider);
         variableCheckAndRewriteVisitor.visit(topExpr, null);
     }
 
+    protected void rewriteGroupBys() throws AsterixException {
+        if (topExpr == null) {
+            return;
+        }
+        SqlppGroupByVisitor groupByVisitor = new SqlppGroupByVisitor(context, metadataProvider);
+        groupByVisitor.visit(topExpr, null);
+    }
+
     protected void inlineDeclaredUdfs() throws AsterixException {
         if (topExpr == null) {
             return;
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java
new file mode 100644
index 0000000..0f8488a
--- /dev/null
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppRewriteUtil.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.lang.sqlpp.util;
+
+import java.util.Collection;
+import java.util.HashSet;
+import java.util.Set;
+
+import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.base.ILangExpression;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.sqlpp.visitor.SqlppGroupBySugarVisitor;
+import org.apache.asterix.lang.sqlpp.visitor.UsedVariableVisitor;
+
+public class SqlppRewriteUtil {
+
+    // Applying sugar rewriting for group-by.
+    public static Expression rewriteExpressionUsingGroupVariable(VariableExpr groupVar,
+            Collection<VariableExpr> targetVarList, ILangExpression expr, LangRewritingContext context)
+                    throws AsterixException {
+        SqlppGroupBySugarVisitor visitor = new SqlppGroupBySugarVisitor(context, null, groupVar, targetVarList);
+        return expr.accept(visitor, null);
+    }
+
+    public static Set<VariableExpr> getUsedVariable(Expression expr) throws AsterixException {
+        Set<VariableExpr> vars = new HashSet<>();
+        UsedVariableVisitor visitor = new UsedVariableVisitor();
+        expr.accept(visitor, vars);
+        return vars;
+    }
+
+}
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppVariableUtil.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppVariableUtil.java
new file mode 100644
index 0000000..8a63aa5
--- /dev/null
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/util/SqlppVariableUtil.java
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.lang.sqlpp.util;
+
+import org.apache.asterix.lang.common.struct.VarIdentifier;
+
+public class SqlppVariableUtil {
+
+    private static String USER_VAR_PREFIX = "$";
+
+    public static VarIdentifier toUserDefinedVariableName(VarIdentifier var) {
+        String varName = var.getValue();
+        return toUserDefinedVariableName(varName);
+    }
+
+    public static VarIdentifier toUserDefinedVariableName(String varName) {
+        if (varName.startsWith(USER_VAR_PREFIX)) {
+            return new VarIdentifier(varName.substring(1));
+        }
+        return new VarIdentifier(varName);
+    }
+
+    public static String toInternalVariableName(String varName) {
+        return USER_VAR_PREFIX + varName;
+    }
+
+    public static VarIdentifier toInternalVariableIdentifier(String idName) {
+        return new VarIdentifier(USER_VAR_PREFIX + idName);
+    }
+
+}
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/InlineColumnAliasVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/InlineColumnAliasVisitor.java
index bafac89..98c74b4 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/InlineColumnAliasVisitor.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/InlineColumnAliasVisitor.java
@@ -19,7 +19,10 @@
 package org.apache.asterix.lang.sqlpp.visitor;
 
 import java.util.ArrayList;
+import java.util.HashMap;
 import java.util.List;
+import java.util.Map;
+import java.util.Map.Entry;
 
 import org.apache.asterix.common.exceptions.AsterixException;
 import org.apache.asterix.lang.common.base.Expression;
@@ -44,11 +47,11 @@
 import org.apache.asterix.lang.common.expression.UnaryExpr;
 import org.apache.asterix.lang.common.expression.VariableExpr;
 import org.apache.asterix.lang.common.parser.ScopeChecker;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
 import org.apache.asterix.lang.common.rewrites.VariableSubstitutionEnvironment;
 import org.apache.asterix.lang.common.statement.FunctionDecl;
 import org.apache.asterix.lang.common.statement.Query;
 import org.apache.asterix.lang.common.struct.QuantifiedPair;
-import org.apache.asterix.lang.common.struct.VarIdentifier;
 import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateClause;
 import org.apache.asterix.lang.sqlpp.clause.FromClause;
 import org.apache.asterix.lang.sqlpp.clause.FromTerm;
@@ -65,11 +68,17 @@
 import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.util.SqlppVariableSubstitutionUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppQueryExpressionVisitor;
 
 public class InlineColumnAliasVisitor extends AbstractSqlppQueryExpressionVisitor<Void, Boolean> {
 
     private final ScopeChecker scopeChecker = new ScopeChecker();
+    private final LangRewritingContext context;
+
+    public InlineColumnAliasVisitor(LangRewritingContext context) {
+        this.context = context;
+    }
 
     @Override
     public Void visit(WhereClause whereClause, Boolean arg) throws AsterixException {
@@ -126,8 +135,16 @@
     @Override
     public Void visit(Projection projection, Boolean arg) throws AsterixException {
         projection.getExpression().accept(this, arg);
-        scopeChecker.getCurrentScope().addSymbolExpressionMappingToScope(
-                new VariableExpr(new VarIdentifier(projection.getName())), projection.getExpression());
+        VariableExpr columnAlias = new VariableExpr(SqlppVariableUtil.toInternalVariableIdentifier(projection.getName()));
+        VariableSubstitutionEnvironment env = scopeChecker.getCurrentScope().getVarSubstitutionEnvironment();
+        Expression gbyKey = env.findSubstituion(columnAlias);
+        if (arg) {
+            scopeChecker.getCurrentScope().addSymbolExpressionMappingToScope(columnAlias, projection.getExpression());
+        } else {
+            if (gbyKey != null) {
+                projection.setExpression(gbyKey);
+            }
+        }
         return null;
     }
 
@@ -135,7 +152,7 @@
     public Void visit(SelectBlock selectBlock, Boolean arg) throws AsterixException {
         // Traverses the select block in the order of "select", "group-by",
         // "group-by" lets and "having".
-        selectBlock.getSelectClause().accept(this, arg);
+        selectBlock.getSelectClause().accept(this, true);
 
         if (selectBlock.hasFromClause()) {
             selectBlock.getFromClause().accept(this, arg);
@@ -156,6 +173,9 @@
         if (selectBlock.hasHavingClause()) {
             selectBlock.getHavingClause().accept(this, arg);
         }
+
+        // Visit select clause again to overwrite projection expressions if the group-by clause is rewritten.
+        selectBlock.getSelectClause().accept(this, false);
         return null;
     }
 
@@ -172,7 +192,12 @@
 
     @Override
     public Void visit(SelectElement selectElement, Boolean arg) throws AsterixException {
-        selectElement.getExpression().accept(this, true);
+        Expression expr = selectElement.getExpression();
+        expr.accept(this, arg);
+        if (expr.getKind() == Kind.RECORD_CONSTRUCTOR_EXPRESSION) {
+            // To be consistent with SelectRegular.
+            mapForRecordConstructor(arg, (RecordConstructor) expr);
+        }
         return null;
     }
 
@@ -251,15 +276,28 @@
     @Override
     public Void visit(GroupbyClause gc, Boolean arg) throws AsterixException {
         VariableSubstitutionEnvironment env = scopeChecker.getCurrentScope().getVarSubstitutionEnvironment();
+        Map<VariableExpr, VariableExpr> oldGbyExprsToNewGbyVarMap = new HashMap<>();
         for (GbyVariableExpressionPair gbyVarExpr : gc.getGbyPairList()) {
-            Expression newExpr = (Expression) SqlppVariableSubstitutionUtil.substituteVariableWithoutContext(gbyVarExpr.getExpr(),
+            Expression oldGbyExpr = gbyVarExpr.getExpr();
+            Expression newExpr = (Expression) SqlppVariableSubstitutionUtil.substituteVariableWithoutContext(oldGbyExpr,
                     env);
             newExpr.accept(this, arg);
             gbyVarExpr.setExpr(newExpr);
+            if (gbyVarExpr.getVar() == null) {
+                gbyVarExpr.setVar(new VariableExpr(context.newVariable()));
+            }
+            if (oldGbyExpr.getKind() == Kind.VARIABLE_EXPRESSION) {
+                VariableExpr oldGbyVarExpr = (VariableExpr) oldGbyExpr;
+                if (env.findSubstituion(oldGbyVarExpr) != null) {
+                    // Re-mapping that needs to be added.
+                    oldGbyExprsToNewGbyVarMap.put(oldGbyVarExpr, gbyVarExpr.getVar());
+                }
+            }
         }
-        for (GbyVariableExpressionPair gbyVarExpr : gc.getGbyPairList()) {
-            // A group-by variable will override the alias to substitute.
-            scopeChecker.getCurrentScope().removeSymbolExpressionMapping(gbyVarExpr.getVar());
+        for (Entry<VariableExpr, VariableExpr> entry : oldGbyExprsToNewGbyVarMap.entrySet()) {
+            // The group-by key variable will override the alias to substitute.
+            scopeChecker.getCurrentScope().removeSymbolExpressionMapping(entry.getKey());
+            scopeChecker.getCurrentScope().addSymbolExpressionMappingToScope(entry.getKey(), entry.getValue());
         }
         return null;
     }
@@ -310,19 +348,34 @@
     @Override
     public Void visit(RecordConstructor rc, Boolean rewrite) throws AsterixException {
         for (FieldBinding binding : rc.getFbList()) {
-            Expression leftExpr = binding.getLeftExpr();
-            leftExpr.accept(this, false);
+            binding.getLeftExpr().accept(this, false);
             binding.getRightExpr().accept(this, false);
-            if (rewrite && leftExpr.getKind() == Kind.LITERAL_EXPRESSION) {
+        }
+        return null;
+    }
+
+    private void mapForRecordConstructor(Boolean initPhase, RecordConstructor rc) {
+        for (FieldBinding binding : rc.getFbList()) {
+            Expression leftExpr = binding.getLeftExpr();
+            if (leftExpr.getKind() == Kind.LITERAL_EXPRESSION) {
                 LiteralExpr literalExpr = (LiteralExpr) leftExpr;
                 if (literalExpr.getValue().getLiteralType() == Literal.Type.STRING) {
                     String fieldName = literalExpr.getValue().getStringValue();
-                    scopeChecker.getCurrentScope().addSymbolExpressionMappingToScope(
-                            new VariableExpr(new VarIdentifier(fieldName)), binding.getRightExpr());
+                    VariableExpr columnAlias = new VariableExpr(SqlppVariableUtil.toInternalVariableIdentifier(fieldName));
+                    VariableSubstitutionEnvironment env = scopeChecker.getCurrentScope()
+                            .getVarSubstitutionEnvironment();
+                    if (initPhase) {
+                        scopeChecker.getCurrentScope().addSymbolExpressionMappingToScope(columnAlias,
+                                binding.getRightExpr());
+                    } else {
+                        Expression gbyKey = env.findSubstituion(columnAlias);
+                        if (gbyKey != null) {
+                            binding.setRightExpr(gbyKey);
+                        }
+                    }
                 }
             }
         }
-        return null;
     }
 
     @Override
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
index 06b5027..df32b01 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppAstPrintVisitor.java
@@ -21,7 +21,11 @@
 import java.io.PrintWriter;
 
 import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.clause.GroupbyClause;
 import org.apache.asterix.lang.common.clause.LetClause;
+import org.apache.asterix.lang.common.expression.GbyVariableExpressionPair;
+import org.apache.asterix.lang.common.struct.Identifier;
 import org.apache.asterix.lang.common.visitor.QueryPrintVisitor;
 import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateClause;
 import org.apache.asterix.lang.sqlpp.clause.FromClause;
@@ -39,6 +43,7 @@
 import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
 import org.apache.asterix.lang.sqlpp.visitor.base.ISqlppVisitor;
+import org.apache.hyracks.algebricks.common.utils.Pair;
 
 public class SqlppAstPrintVisitor extends QueryPrintVisitor implements ISqlppVisitor<Void, Integer> {
 
@@ -237,4 +242,30 @@
         return null;
     }
 
+    @Override
+    public Void visit(GroupbyClause gc, Integer step) throws AsterixException {
+        out.println(skip(step) + "Groupby");
+        for (GbyVariableExpressionPair pair : gc.getGbyPairList()) {
+            if (pair.getVar() != null) {
+                pair.getVar().accept(this, step + 1);
+                out.println(skip(step + 1) + ":=");
+            }
+            pair.getExpr().accept(this, step + 1);
+        }
+        if (gc.hasGroupVar()) {
+            out.println(skip(step + 1) + "GROUP AS");
+            gc.getGroupVar().accept(this, step + 1);
+            if (gc.hasGroupFieldList()) {
+                out.println(skip(step + 1) + "(");
+                for (Pair<Expression, Identifier> field : gc.getGroupFieldList()) {
+                    field.first.accept(this, step + 1);
+                    out.println(skip(step + 1) + " AS " + field.second);
+                }
+                out.println(skip(step + 1) + ")");
+            }
+        }
+        out.println();
+        return null;
+    }
+
 }
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
index 9fd9970..62581c8 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppCloneAndSubstituteVariablesVisitor.java
@@ -85,7 +85,7 @@
         VariableExpr newLeftVar = generateNewVariable(context, leftVar);
         VariableExpr newLeftPosVar = fromTerm.hasPositionalVariable()
                 ? generateNewVariable(context, fromTerm.getPositionalVariable()) : null;
-        Expression newLeftExpr = (Expression) fromTerm.getLeftExpression().accept(this, env).first;
+        Expression newLeftExpr = (Expression) visitUnnesBindingExpression(fromTerm.getLeftExpression(), env).first;
         List<AbstractBinaryCorrelateClause> newCorrelateClauses = new ArrayList<AbstractBinaryCorrelateClause>();
 
         VariableSubstitutionEnvironment currentEnv = new VariableSubstitutionEnvironment(env);
@@ -120,7 +120,7 @@
                 ? generateNewVariable(context, joinClause.getPositionalVariable()) : null;
 
         // Visits the right expression.
-        Expression newRightExpr = (Expression) joinClause.getRightExpression().accept(this, env).first;
+        Expression newRightExpr = (Expression) visitUnnesBindingExpression(joinClause.getRightExpression(), env).first;
 
         // Visits the condition.
         VariableSubstitutionEnvironment currentEnv = new VariableSubstitutionEnvironment(env);
@@ -170,7 +170,7 @@
                 ? generateNewVariable(context, unnestClause.getPositionalVariable()) : null;
 
         // Visits the right expression.
-        Expression rightExpr = (Expression) unnestClause.getRightExpression().accept(this, env).first;
+        Expression rightExpr = (Expression) visitUnnesBindingExpression(unnestClause.getRightExpression(), env).first;
 
         // Visits the condition.
         VariableSubstitutionEnvironment currentEnv = new VariableSubstitutionEnvironment(env);
@@ -187,7 +187,8 @@
     @Override
     public Pair<ILangExpression, VariableSubstitutionEnvironment> visit(Projection projection,
             VariableSubstitutionEnvironment env) throws AsterixException {
-        Projection newProjection = (Projection) projection.getExpression().accept(this, env).first;
+        Projection newProjection = new Projection((Expression) projection.getExpression().accept(this, env).first,
+                projection.getName(), projection.star(), projection.exprStar());
         return new Pair<ILangExpression, VariableSubstitutionEnvironment>(newProjection, env);
     }
 
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupBySugarVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupBySugarVisitor.java
new file mode 100644
index 0000000..5506256
--- /dev/null
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupBySugarVisitor.java
@@ -0,0 +1,127 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.lang.sqlpp.visitor;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.common.functions.FunctionConstants;
+import org.apache.asterix.common.functions.FunctionSignature;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.base.Expression.Kind;
+import org.apache.asterix.lang.common.expression.CallExpr;
+import org.apache.asterix.lang.common.expression.FieldAccessor;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.common.util.FunctionUtil;
+import org.apache.asterix.lang.sqlpp.clause.FromClause;
+import org.apache.asterix.lang.sqlpp.clause.FromTerm;
+import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
+import org.apache.asterix.lang.sqlpp.clause.SelectClause;
+import org.apache.asterix.lang.sqlpp.clause.SelectElement;
+import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation;
+import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
+import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
+import org.apache.asterix.lang.sqlpp.util.SqlppRewriteUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableSubstitutionUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
+import org.apache.asterix.metadata.declared.AqlMetadataProvider;
+import org.apache.asterix.om.functions.AsterixBuiltinFunctions;
+import org.apache.hyracks.algebricks.core.algebra.functions.FunctionIdentifier;
+import org.apache.hyracks.algebricks.core.algebra.functions.IFunctionInfo;
+
+/**
+ * An AST pre-processor to rewrite group-by sugar queries.
+ */
+public class SqlppGroupBySugarVisitor extends VariableCheckAndRewriteVisitor {
+
+    private final Expression groupVar;
+    private final Collection<VariableExpr> targetVars;
+
+    public SqlppGroupBySugarVisitor(LangRewritingContext context, AqlMetadataProvider metadataProvider,
+            Expression groupVar, Collection<VariableExpr> targetVars) {
+        super(context, false, metadataProvider);
+        this.groupVar = groupVar;
+        this.targetVars = targetVars;
+    }
+
+    @Override
+    public Expression visit(CallExpr callExpr, Expression arg) throws AsterixException {
+        List<Expression> newExprList = new ArrayList<Expression>();
+        boolean aggregate = isAggregateFunction(callExpr.getFunctionSignature());
+        for (Expression expr : callExpr.getExprList()) {
+            Expression newExpr = aggregate ? wrapAggregationArgument(expr) : expr;
+            newExprList.add(newExpr.accept(this, arg));
+        }
+        callExpr.setExprList(newExprList);
+        return callExpr;
+    }
+
+    private boolean isAggregateFunction(FunctionSignature signature) throws AsterixException {
+        IFunctionInfo finfo = FunctionUtil.getFunctionInfo(
+                new FunctionIdentifier(FunctionConstants.ASTERIX_NS, signature.getName(), signature.getArity()));
+        if (finfo == null) {
+            return false;
+        }
+        return AsterixBuiltinFunctions.getAggregateFunction(finfo.getFunctionIdentifier()) != null;
+    }
+
+    private Expression wrapAggregationArgument(Expression expr) throws AsterixException {
+        if (expr.getKind() == Kind.SELECT_EXPRESSION) {
+            return expr;
+        }
+        Set<VariableExpr> definedVars = scopeChecker.getCurrentScope().getLiveVariables();
+        Set<VariableExpr> vars = new HashSet<>(targetVars);
+        vars.remove(definedVars); // Exclude re-defined local variables.
+        Set<VariableExpr> usedVars = SqlppRewriteUtil.getUsedVariable(expr);
+        if (!vars.containsAll(usedVars)) {
+            return expr;
+        }
+        VariableExpr var = new VariableExpr(context.newVariable());
+        FromTerm fromTerm = new FromTerm(groupVar, var, null, null);
+        FromClause fromClause = new FromClause(Collections.singletonList(fromTerm));
+
+        // Select clause.
+        SelectElement selectElement = new SelectElement(expr);
+        SelectClause selectClause = new SelectClause(selectElement, null, false);
+
+        // Construct the select expression.
+        SelectBlock selectBlock = new SelectBlock(selectClause, fromClause, null, null, null, null, null);
+        SelectSetOperation selectSetOperation = new SelectSetOperation(new SetOperationInput(selectBlock, null), null);
+        SelectExpression selectExpression = new SelectExpression(null, selectSetOperation, null, null, false);
+        selectExpression.setSubquery(true);
+
+        // replace variable expressions with field access
+        Map<VariableExpr, Expression> varExprMap = new HashMap<>();
+        for (VariableExpr usedVar : usedVars) {
+            varExprMap.put(usedVar,
+                    new FieldAccessor(var, SqlppVariableUtil.toUserDefinedVariableName(usedVar.getVar())));
+        }
+        selectElement.setExpression(
+                (Expression) SqlppVariableSubstitutionUtil.substituteVariableWithoutContext(expr, varExprMap));
+        return selectExpression;
+    }
+}
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupByVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupByVisitor.java
new file mode 100644
index 0000000..ecbdecd
--- /dev/null
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppGroupByVisitor.java
@@ -0,0 +1,170 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.lang.sqlpp.visitor;
+
+import java.util.ArrayList;
+import java.util.HashSet;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Set;
+
+import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.lang.common.base.Expression;
+import org.apache.asterix.lang.common.clause.GroupbyClause;
+import org.apache.asterix.lang.common.clause.LetClause;
+import org.apache.asterix.lang.common.context.Scope;
+import org.apache.asterix.lang.common.expression.GbyVariableExpressionPair;
+import org.apache.asterix.lang.common.expression.VariableExpr;
+import org.apache.asterix.lang.common.rewrites.LangRewritingContext;
+import org.apache.asterix.lang.common.struct.Identifier;
+import org.apache.asterix.lang.common.struct.VarIdentifier;
+import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
+import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
+import org.apache.asterix.lang.sqlpp.util.SqlppRewriteUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
+import org.apache.asterix.metadata.declared.AqlMetadataProvider;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+
+/**
+ * A pre-processor that adds the group variable as well as its group field
+ * list into the AST. It will also invoke SQL group-by aggregation sugar rewritings.
+ */
+public class SqlppGroupByVisitor extends VariableCheckAndRewriteVisitor {
+
+    public SqlppGroupByVisitor(LangRewritingContext context, AqlMetadataProvider metadataProvider) {
+        super(context, false, metadataProvider);
+    }
+
+    @Override
+    public Expression visit(SelectBlock selectBlock, Expression arg) throws AsterixException {
+        // Traverses the select block in the order of "from", "let"s, "where",
+        // "group by", "let"s, "having" and "select".
+        if (selectBlock.hasFromClause()) {
+            selectBlock.getFromClause().accept(this, arg);
+        }
+        if (selectBlock.hasLetClauses()) {
+            List<LetClause> letList = selectBlock.getLetList();
+            for (LetClause letClause : letList) {
+                letClause.accept(this, arg);
+            }
+        }
+        if (selectBlock.hasWhereClause()) {
+            selectBlock.getWhereClause().accept(this, arg);
+        }
+        if (selectBlock.hasGroupbyClause()) {
+            selectBlock.getGroupbyClause().accept(this, arg);
+            Set<VariableExpr> withVarSet = new HashSet<>(selectBlock.getGroupbyClause().getWithVarList());
+            withVarSet.remove(selectBlock.getGroupbyClause().getGroupVar());
+            //selectBlock.getGroupbyClause().getWithVarList()
+            //        .retainAll(Collections.singleton(selectBlock.getGroupbyClause().getGroupVar()));
+            if (selectBlock.hasLetClausesAfterGroupby()) {
+                List<LetClause> letListAfterGby = selectBlock.getLetListAfterGroupby();
+                for (LetClause letClauseAfterGby : letListAfterGby) {
+                    // Rewrites each let clause after the group-by.
+                    SqlppRewriteUtil.rewriteExpressionUsingGroupVariable(selectBlock.getGroupbyClause().getGroupVar(),
+                            withVarSet, letClauseAfterGby, context);
+                    letClauseAfterGby.accept(this, arg);
+                }
+            }
+            if (selectBlock.hasHavingClause()) {
+                // Rewrites the having clause.
+                SqlppRewriteUtil.rewriteExpressionUsingGroupVariable(selectBlock.getGroupbyClause().getGroupVar(),
+                        withVarSet, selectBlock.getHavingClause(), context);
+                selectBlock.getHavingClause().accept(this, arg);
+            }
+            // Rewrites the select clause.
+            SqlppRewriteUtil.rewriteExpressionUsingGroupVariable(selectBlock.getGroupbyClause().getGroupVar(),
+                    withVarSet, selectBlock.getSelectClause(), context);
+
+            SelectExpression parentSelectExpression = (SelectExpression) arg;
+            if (parentSelectExpression.hasOrderby()) {
+                // Rewrites the order-by clause.
+                SqlppRewriteUtil.rewriteExpressionUsingGroupVariable(selectBlock.getGroupbyClause().getGroupVar(),
+                        withVarSet, parentSelectExpression.getOrderbyClause(), context);
+            }
+            if (parentSelectExpression.hasLimit()) {
+                // Rewrites the limit clause.
+                SqlppRewriteUtil.rewriteExpressionUsingGroupVariable(selectBlock.getGroupbyClause().getGroupVar(),
+                        withVarSet, parentSelectExpression.getLimitClause(), context);
+            }
+        }
+        selectBlock.getSelectClause().accept(this, arg);
+        return null;
+    }
+
+    @Override
+    public Expression visit(GroupbyClause gc, Expression arg) throws AsterixException {
+        Scope newScope = scopeChecker.extendCurrentScopeNoPush(true);
+        // Puts all group-by variables into the symbol set of the new scope.
+        for (GbyVariableExpressionPair gbyVarExpr : gc.getGbyPairList()) {
+            gbyVarExpr.setExpr(gbyVarExpr.getExpr().accept(this, arg));
+            VariableExpr gbyVar = gbyVarExpr.getVar();
+            if (gbyVar != null) {
+                newScope.addNewVarSymbolToScope(gbyVarExpr.getVar().getVar());
+            }
+        }
+        // Puts all live variables into withVarList.
+        List<VariableExpr> withVarList = new ArrayList<VariableExpr>();
+        Iterator<Identifier> varIterator = scopeChecker.getCurrentScope().liveSymbols();
+        while (varIterator.hasNext()) {
+            Identifier ident = varIterator.next();
+            VariableExpr varExpr = new VariableExpr();
+            if (ident instanceof VarIdentifier) {
+                varExpr.setIsNewVar(false);
+                varExpr.setVar((VarIdentifier) ident);
+                withVarList.add(varExpr);
+                newScope.addNewVarSymbolToScope((VarIdentifier) ident);
+            }
+        }
+
+        // Sets the field list for the group variable.
+        List<Pair<Expression, Identifier>> groupFieldList = new ArrayList<>();
+        if (!gc.hasGroupFieldList()) {
+            for (VariableExpr varExpr : withVarList) {
+                Pair<Expression, Identifier> varIdPair = new Pair<>(new VariableExpr(varExpr.getVar()),
+                        SqlppVariableUtil.toUserDefinedVariableName(varExpr.getVar()));
+                groupFieldList.add(varIdPair);
+            }
+            gc.setGroupFieldList(groupFieldList);
+        } else {
+            // Check the scopes of group field variables.
+            for (Pair<Expression, Identifier> groupField : gc.getGroupFieldList()) {
+                Expression newVar = groupField.first.accept(this, arg);
+                groupFieldList.add(new Pair<>(newVar, groupField.second));
+            }
+        }
+        gc.setGroupFieldList(groupFieldList);
+
+        // Sets the group variable.
+        if (!gc.hasGroupVar()) {
+            VariableExpr groupVar = new VariableExpr(context.newVariable());
+            gc.setGroupVar(groupVar);
+        }
+        newScope.addNewVarSymbolToScope(gc.getGroupVar().getVar());
+
+        // Adds the group variable into the "with" (i.e., re-binding) variable list.
+        VariableExpr gbyVarRef = new VariableExpr(gc.getGroupVar().getVar());
+        gbyVarRef.setIsNewVar(false);
+        withVarList.add(gbyVarRef);
+        gc.setWithVarList(withVarList);
+
+        scopeChecker.replaceCurrentScope(newScope);
+        return null;
+    }
+}
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteVariablesVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteVariablesVisitor.java
index 0624484..a9aff55 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteVariablesVisitor.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteVariablesVisitor.java
@@ -30,7 +30,7 @@
     }
 
     @Override
-    public Expression rewriteVariableExpr(VariableExpr expr, VariableSubstitutionEnvironment env) {
+    protected Expression rewriteVariableExpr(VariableExpr expr, VariableSubstitutionEnvironment env) {
         if (env.constainsOldVar(expr)) {
             return env.findSubstituion(expr);
         }
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/UsedVariableVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/UsedVariableVisitor.java
new file mode 100644
index 0000000..877f722
--- /dev/null
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/UsedVariableVisitor.java
@@ -0,0 +1,362 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.asterix.lang.sqlpp.visitor;
+
+import java.util.Collection;
+import java.util.List;
+
+import org.apache.asterix.common.exceptions.AsterixException;
+import org.apache.asterix.lang.common.base.Expression;
+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.WhereClause;
+import org.apache.asterix.lang.common.expression.CallExpr;
+import org.apache.asterix.lang.common.expression.FieldAccessor;
+import org.apache.asterix.lang.common.expression.FieldBinding;
+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.LiteralExpr;
+import org.apache.asterix.lang.common.expression.OperatorExpr;
+import org.apache.asterix.lang.common.expression.QuantifiedExpression;
+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.statement.FunctionDecl;
+import org.apache.asterix.lang.common.statement.Query;
+import org.apache.asterix.lang.common.struct.Identifier;
+import org.apache.asterix.lang.common.struct.QuantifiedPair;
+import org.apache.asterix.lang.sqlpp.clause.AbstractBinaryCorrelateClause;
+import org.apache.asterix.lang.sqlpp.clause.FromClause;
+import org.apache.asterix.lang.sqlpp.clause.FromTerm;
+import org.apache.asterix.lang.sqlpp.clause.HavingClause;
+import org.apache.asterix.lang.sqlpp.clause.JoinClause;
+import org.apache.asterix.lang.sqlpp.clause.NestClause;
+import org.apache.asterix.lang.sqlpp.clause.Projection;
+import org.apache.asterix.lang.sqlpp.clause.SelectBlock;
+import org.apache.asterix.lang.sqlpp.clause.SelectClause;
+import org.apache.asterix.lang.sqlpp.clause.SelectElement;
+import org.apache.asterix.lang.sqlpp.clause.SelectRegular;
+import org.apache.asterix.lang.sqlpp.clause.SelectSetOperation;
+import org.apache.asterix.lang.sqlpp.clause.UnnestClause;
+import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
+import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
+import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppQueryExpressionVisitor;
+import org.apache.hyracks.algebricks.common.utils.Pair;
+
+public class UsedVariableVisitor extends AbstractSqlppQueryExpressionVisitor<Void, Collection<VariableExpr>> {
+
+    @Override
+    public Void visit(FromClause fromClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (FromTerm fromTerm : fromClause.getFromTerms()) {
+            fromTerm.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(FromTerm fromTerm, Collection<VariableExpr> usedVars) throws AsterixException {
+        // Visit the left expression of a from term.
+        fromTerm.getLeftExpression().accept(this, usedVars);
+
+        // Visits join/unnest/nest clauses.
+        for (AbstractBinaryCorrelateClause correlateClause : fromTerm.getCorrelateClauses()) {
+            correlateClause.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(JoinClause joinClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        // NOTE: the two join branches cannot be correlated, instead of checking
+        // the correlation here,
+        // we defer the check to the query optimizer.
+        joinClause.getRightExpression().accept(this, usedVars);
+
+        // The condition expression can refer to the just registered variables
+        // for the right branch.
+        joinClause.getConditionExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(NestClause nestClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        // NOTE: the two branches of a NEST cannot be correlated, instead of
+        // checking the correlation here, we defer the check to the query
+        // optimizer.
+        nestClause.getRightExpression().accept(this, usedVars);
+
+        // The condition expression can refer to the just registered variables
+        // for the right branch.
+        nestClause.getConditionExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(UnnestClause unnestClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        unnestClause.getRightExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(Projection projection, Collection<VariableExpr> usedVars) throws AsterixException {
+        projection.getExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectBlock selectBlock, Collection<VariableExpr> usedVars) throws AsterixException {
+        // Traverses the select block in the order of "from", "let"s, "where",
+        // "group by", "let"s, "having" and "select".
+        if (selectBlock.hasFromClause()) {
+            selectBlock.getFromClause().accept(this, usedVars);
+        }
+        if (selectBlock.hasLetClauses()) {
+            List<LetClause> letList = selectBlock.getLetList();
+            for (LetClause letClause : letList) {
+                letClause.accept(this, usedVars);
+            }
+        }
+        if (selectBlock.hasWhereClause()) {
+            selectBlock.getWhereClause().accept(this, usedVars);
+        }
+        if (selectBlock.hasGroupbyClause()) {
+            selectBlock.getGroupbyClause().accept(this, usedVars);
+            if (selectBlock.hasLetClausesAfterGroupby()) {
+                List<LetClause> letListAfterGby = selectBlock.getLetListAfterGroupby();
+                for (LetClause letClauseAfterGby : letListAfterGby) {
+                    letClauseAfterGby.accept(this, usedVars);
+                }
+            }
+            if (selectBlock.hasHavingClause()) {
+                selectBlock.getHavingClause().accept(this, usedVars);
+            }
+        }
+        selectBlock.getSelectClause().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectClause selectClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        if (selectClause.selectElement()) {
+            selectClause.getSelectElement().accept(this, usedVars);
+        }
+        if (selectClause.selectRegular()) {
+            selectClause.getSelectRegular().accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectElement selectElement, Collection<VariableExpr> usedVars) throws AsterixException {
+        selectElement.getExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectRegular selectRegular, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (Projection projection : selectRegular.getProjections()) {
+            projection.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectSetOperation selectSetOperation, Collection<VariableExpr> usedVars)
+            throws AsterixException {
+        selectSetOperation.getLeftInput().accept(this, usedVars);
+        for (SetOperationRight right : selectSetOperation.getRightInputs()) {
+            right.getSetOperationRightInput().accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(HavingClause havingClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        havingClause.getFilterExpression().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(Query q, Collection<VariableExpr> usedVars) throws AsterixException {
+        q.getBody().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(FunctionDecl fd, Collection<VariableExpr> usedVars) throws AsterixException {
+        fd.getFuncBody().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(WhereClause whereClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        whereClause.getWhereExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(OrderbyClause oc, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (Expression orderExpr : oc.getOrderbyList()) {
+            orderExpr.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(GroupbyClause gc, Collection<VariableExpr> usedVars) throws AsterixException {
+        // Puts all group-by variables into the symbol set of the new scope.
+        for (GbyVariableExpressionPair gbyVarExpr : gc.getGbyPairList()) {
+            gbyVarExpr.getExpr().accept(this, usedVars);
+        }
+        for (GbyVariableExpressionPair decorVarExpr : gc.getDecorPairList()) {
+            decorVarExpr.getExpr().accept(this, usedVars);
+        }
+        if (gc.hasGroupFieldList()) {
+            for (Pair<Expression, Identifier> groupField : gc.getGroupFieldList()) {
+                groupField.first.accept(this, usedVars);
+            }
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(LimitClause limitClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        limitClause.getLimitExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(LetClause letClause, Collection<VariableExpr> usedVars) throws AsterixException {
+        letClause.getBindingExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(SelectExpression selectExpression, Collection<VariableExpr> usedVars) throws AsterixException {
+        // visit let list
+        if (selectExpression.hasLetClauses()) {
+            for (LetClause letClause : selectExpression.getLetList()) {
+                letClause.accept(this, usedVars);
+            }
+        }
+
+        // visit the main select.
+        selectExpression.getSelectSetOperation().accept(this, usedVars);
+
+        // visit order by
+        if (selectExpression.hasOrderby()) {
+            for (Expression orderExpr : selectExpression.getOrderbyClause().getOrderbyList()) {
+                orderExpr.accept(this, usedVars);
+            }
+        }
+
+        // visit limit
+        if (selectExpression.hasLimit()) {
+            selectExpression.getLimitClause().accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(LiteralExpr l, Collection<VariableExpr> usedVars) throws AsterixException {
+        return null;
+    }
+
+    @Override
+    public Void visit(ListConstructor lc, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (Expression expr : lc.getExprList()) {
+            expr.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(RecordConstructor rc, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (FieldBinding binding : rc.getFbList()) {
+            binding.getLeftExpr().accept(this, usedVars);
+            binding.getRightExpr().accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(OperatorExpr operatorExpr, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (Expression expr : operatorExpr.getExprList()) {
+            expr.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(IfExpr ifExpr, Collection<VariableExpr> usedVars) throws AsterixException {
+        ifExpr.getCondExpr().accept(this, usedVars);
+        ifExpr.getThenExpr().accept(this, usedVars);
+        ifExpr.getElseExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(QuantifiedExpression qe, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (QuantifiedPair pair : qe.getQuantifiedList()) {
+            pair.getExpr().accept(this, usedVars);
+        }
+        qe.getSatisfiesExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(CallExpr callExpr, Collection<VariableExpr> usedVars) throws AsterixException {
+        for (Expression expr : callExpr.getExprList()) {
+            expr.accept(this, usedVars);
+        }
+        return null;
+    }
+
+    @Override
+    public Void visit(VariableExpr varExpr, Collection<VariableExpr> usedVars) throws AsterixException {
+        usedVars.add(varExpr);
+        return null;
+    }
+
+    @Override
+    public Void visit(UnaryExpr u, Collection<VariableExpr> usedVars) throws AsterixException {
+        u.getExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(FieldAccessor fa, Collection<VariableExpr> usedVars) throws AsterixException {
+        fa.getExpr().accept(this, usedVars);
+        return null;
+    }
+
+    @Override
+    public Void visit(IndexAccessor ia, Collection<VariableExpr> usedVars) throws AsterixException {
+        ia.getExpr().accept(this, usedVars);
+        if (ia.getIndexExpr() != null) {
+            ia.getIndexExpr();
+        }
+        return null;
+    }
+
+}
diff --git a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/VariableCheckAndRewriteVisitor.java b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/VariableCheckAndRewriteVisitor.java
index f9cf99f..f4456a4 100644
--- a/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/VariableCheckAndRewriteVisitor.java
+++ b/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/VariableCheckAndRewriteVisitor.java
@@ -19,7 +19,6 @@
 package org.apache.asterix.lang.sqlpp.visitor;
 
 import java.util.ArrayList;
-import java.util.Iterator;
 import java.util.List;
 
 import org.apache.asterix.common.config.MetadataConstants;
@@ -68,15 +67,17 @@
 import org.apache.asterix.lang.sqlpp.clause.UnnestClause;
 import org.apache.asterix.lang.sqlpp.expression.SelectExpression;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
-import org.apache.asterix.lang.sqlpp.util.SqlppFormatPrintUtil;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppQueryExpressionVisitor;
+import org.apache.asterix.metadata.declared.AqlMetadataProvider;
 import org.apache.hyracks.algebricks.core.algebra.base.Counter;
 
-public class VariableCheckAndRewriteVisitor extends AbstractSqlppQueryExpressionVisitor<Expression, Void> {
+public class VariableCheckAndRewriteVisitor extends AbstractSqlppQueryExpressionVisitor<Expression, Expression> {
 
-    private final ScopeChecker scopeChecker = new ScopeChecker();
-    private final LangRewritingContext context;
-    private final boolean overwrite;
+    protected final ScopeChecker scopeChecker = new ScopeChecker();
+    protected final LangRewritingContext context;
+    protected final boolean overwrite;
+    protected final AqlMetadataProvider metadataProvider;
 
     /**
      * @param context,
@@ -86,14 +87,16 @@
      *            This flag can only be true for rewriting a top-level query.
      *            It should be false for rewriting the body expression of a user-defined function.
      */
-    public VariableCheckAndRewriteVisitor(LangRewritingContext context, boolean overwrite) {
+    public VariableCheckAndRewriteVisitor(LangRewritingContext context, boolean overwrite,
+            AqlMetadataProvider metadataProvider) {
         this.context = context;
         scopeChecker.setVarCounter(new Counter(context.getVarCounter()));
         this.overwrite = overwrite;
+        this.metadataProvider = metadataProvider;
     }
 
     @Override
-    public Expression visit(FromClause fromClause, Void arg) throws AsterixException {
+    public Expression visit(FromClause fromClause, Expression arg) throws AsterixException {
         scopeChecker.extendCurrentScope();
         for (FromTerm fromTerm : fromClause.getFromTerms()) {
             fromTerm.accept(this, arg);
@@ -102,7 +105,8 @@
     }
 
     @Override
-    public Expression visit(FromTerm fromTerm, Void arg) throws AsterixException {
+    public Expression visit(FromTerm fromTerm, Expression arg) throws AsterixException {
+        scopeChecker.createNewScope();
         // Visit the left expression of a from term.
         fromTerm.setLeftExpression(fromTerm.getLeftExpression().accept(this, arg));
 
@@ -115,7 +119,6 @@
             VariableExpr posVar = fromTerm.getPositionalVariable();
             scopeChecker.getCurrentScope().addNewVarSymbolToScope(posVar.getVar());
         }
-
         // Visits join/unnest/nest clauses.
         for (AbstractBinaryCorrelateClause correlateClause : fromTerm.getCorrelateClauses()) {
             correlateClause.accept(this, arg);
@@ -124,7 +127,10 @@
     }
 
     @Override
-    public Expression visit(JoinClause joinClause, Void arg) throws AsterixException {
+    public Expression visit(JoinClause joinClause, Expression arg) throws AsterixException {
+        Scope backupScope = scopeChecker.removeCurrentScope();
+        Scope parentScope = scopeChecker.getCurrentScope();
+        scopeChecker.createNewScope();
         // NOTE: the two join branches cannot be correlated, instead of checking
         // the correlation here,
         // we defer the check to the query optimizer.
@@ -140,6 +146,11 @@
             scopeChecker.getCurrentScope().addNewVarSymbolToScope(posVar.getVar());
         }
 
+        Scope rightScope = scopeChecker.removeCurrentScope();
+        Scope mergedScope = new Scope(scopeChecker, parentScope);
+        mergedScope.merge(backupScope);
+        mergedScope.merge(rightScope);
+        scopeChecker.pushExistingScope(mergedScope);
         // The condition expression can refer to the just registered variables
         // for the right branch.
         joinClause.setConditionExpression(joinClause.getConditionExpression().accept(this, arg));
@@ -147,7 +158,7 @@
     }
 
     @Override
-    public Expression visit(NestClause nestClause, Void arg) throws AsterixException {
+    public Expression visit(NestClause nestClause, Expression arg) throws AsterixException {
         // NOTE: the two branches of a NEST cannot be correlated, instead of
         // checking the correlation here, we defer the check to the query
         // optimizer.
@@ -170,7 +181,7 @@
     }
 
     @Override
-    public Expression visit(UnnestClause unnestClause, Void arg) throws AsterixException {
+    public Expression visit(UnnestClause unnestClause, Expression arg) throws AsterixException {
         unnestClause.setRightExpression(unnestClause.getRightExpression().accept(this, arg));
 
         // register the data item variable
@@ -186,13 +197,13 @@
     }
 
     @Override
-    public Expression visit(Projection projection, Void arg) throws AsterixException {
+    public Expression visit(Projection projection, Expression arg) throws AsterixException {
         projection.setExpression(projection.getExpression().accept(this, arg));
         return null;
     }
 
     @Override
-    public Expression visit(SelectBlock selectBlock, Void arg) throws AsterixException {
+    public Expression visit(SelectBlock selectBlock, Expression arg) throws AsterixException {
         // Traverses the select block in the order of "from", "let"s, "where",
         // "group by", "let"s, "having" and "select".
         if (selectBlock.hasFromClause()) {
@@ -209,22 +220,23 @@
         }
         if (selectBlock.hasGroupbyClause()) {
             selectBlock.getGroupbyClause().accept(this, arg);
-        }
-        if (selectBlock.hasLetClausesAfterGroupby()) {
-            List<LetClause> letListAfterGby = selectBlock.getLetListAfterGroupby();
-            for (LetClause letClauseAfterGby : letListAfterGby) {
-                letClauseAfterGby.accept(this, arg);
+            if (selectBlock.hasLetClausesAfterGroupby()) {
+                List<LetClause> letListAfterGby = selectBlock.getLetListAfterGroupby();
+                for (LetClause letClauseAfterGby : letListAfterGby) {
+                    letClauseAfterGby.accept(this, arg);
+                }
             }
-        }
-        if (selectBlock.hasHavingClause()) {
-            selectBlock.getHavingClause().accept(this, arg);
+            if (selectBlock.hasHavingClause()) {
+                // Rewrites the having clause.
+                selectBlock.getHavingClause().accept(this, arg);
+            }
         }
         selectBlock.getSelectClause().accept(this, arg);
         return null;
     }
 
     @Override
-    public Expression visit(SelectClause selectClause, Void arg) throws AsterixException {
+    public Expression visit(SelectClause selectClause, Expression arg) throws AsterixException {
         if (selectClause.selectElement()) {
             selectClause.getSelectElement().accept(this, arg);
         }
@@ -235,13 +247,13 @@
     }
 
     @Override
-    public Expression visit(SelectElement selectElement, Void arg) throws AsterixException {
+    public Expression visit(SelectElement selectElement, Expression arg) throws AsterixException {
         selectElement.setExpression(selectElement.getExpression().accept(this, arg));
         return null;
     }
 
     @Override
-    public Expression visit(SelectRegular selectRegular, Void arg) throws AsterixException {
+    public Expression visit(SelectRegular selectRegular, Expression arg) throws AsterixException {
         for (Projection projection : selectRegular.getProjections()) {
             projection.accept(this, arg);
         }
@@ -249,7 +261,7 @@
     }
 
     @Override
-    public Expression visit(SelectSetOperation selectSetOperation, Void arg) throws AsterixException {
+    public Expression visit(SelectSetOperation selectSetOperation, Expression arg) throws AsterixException {
         selectSetOperation.getLeftInput().accept(this, arg);
         for (SetOperationRight right : selectSetOperation.getRightInputs()) {
             scopeChecker.createNewScope();
@@ -259,13 +271,13 @@
     }
 
     @Override
-    public Expression visit(HavingClause havingClause, Void arg) throws AsterixException {
+    public Expression visit(HavingClause havingClause, Expression arg) throws AsterixException {
         havingClause.setFilterExpression(havingClause.getFilterExpression().accept(this, arg));
         return null;
     }
 
     @Override
-    public Expression visit(Query q, Void arg) throws AsterixException {
+    public Expression visit(Query q, Expression arg) throws AsterixException {
         q.setBody(q.getBody().accept(this, arg));
         q.setVarCounter(scopeChecker.getVarCounter());
         context.setVarCounter(scopeChecker.getVarCounter());
@@ -273,7 +285,7 @@
     }
 
     @Override
-    public Expression visit(FunctionDecl fd, Void arg) throws AsterixException {
+    public Expression visit(FunctionDecl fd, Expression arg) throws AsterixException {
         scopeChecker.createNewScope();
         fd.setFuncBody(fd.getFuncBody().accept(this, arg));
         scopeChecker.removeCurrentScope();
@@ -281,13 +293,13 @@
     }
 
     @Override
-    public Expression visit(WhereClause whereClause, Void arg) throws AsterixException {
+    public Expression visit(WhereClause whereClause, Expression arg) throws AsterixException {
         whereClause.setWhereExpr(whereClause.getWhereExpr().accept(this, arg));
         return null;
     }
 
     @Override
-    public Expression visit(OrderbyClause oc, Void arg) throws AsterixException {
+    public Expression visit(OrderbyClause oc, Expression arg) throws AsterixException {
         List<Expression> newOrderbyList = new ArrayList<Expression>();
         for (Expression orderExpr : oc.getOrderbyList()) {
             newOrderbyList.add(orderExpr.accept(this, arg));
@@ -297,7 +309,7 @@
     }
 
     @Override
-    public Expression visit(GroupbyClause gc, Void arg) throws AsterixException {
+    public Expression visit(GroupbyClause gc, Expression arg) throws AsterixException {
         Scope newScope = scopeChecker.extendCurrentScopeNoPush(true);
         // Puts all group-by variables into the symbol set of the new scope.
         for (GbyVariableExpressionPair gbyVarExpr : gc.getGbyPairList()) {
@@ -307,26 +319,15 @@
                 newScope.addNewVarSymbolToScope(gbyVarExpr.getVar().getVar());
             }
         }
-        // Puts all live variables into withVarList.
-        List<VariableExpr> withVarList = new ArrayList<VariableExpr>();
-        Iterator<Identifier> varIterator = scopeChecker.getCurrentScope().liveSymbols();
-        while (varIterator.hasNext()) {
-            Identifier ident = varIterator.next();
-            VariableExpr varExpr = new VariableExpr();
-            if (ident instanceof VarIdentifier) {
-                varExpr.setIsNewVar(false);
-                varExpr.setVar((VarIdentifier) ident);
-                withVarList.add(varExpr);
-                newScope.addNewVarSymbolToScope((VarIdentifier) ident);
-            }
+        for (VariableExpr withVar : gc.getWithVarList()) {
+            newScope.addNewVarSymbolToScope(withVar.getVar());
         }
-        gc.setWithVarList(withVarList);
         scopeChecker.replaceCurrentScope(newScope);
         return null;
     }
 
     @Override
-    public Expression visit(LimitClause limitClause, Void arg) throws AsterixException {
+    public Expression visit(LimitClause limitClause, Expression arg) throws AsterixException {
         scopeChecker.pushForbiddenScope(scopeChecker.getCurrentScope());
         limitClause.setLimitExpr(limitClause.getLimitExpr().accept(this, arg));
         scopeChecker.popForbiddenScope();
@@ -334,7 +335,7 @@
     }
 
     @Override
-    public Expression visit(LetClause letClause, Void arg) throws AsterixException {
+    public Expression visit(LetClause letClause, Expression arg) throws AsterixException {
         scopeChecker.extendCurrentScope();
         letClause.setBindingExpr(letClause.getBindingExpr().accept(this, arg));
         scopeChecker.getCurrentScope().addNewVarSymbolToScope(letClause.getVarExpr().getVar());
@@ -342,7 +343,7 @@
     }
 
     @Override
-    public Expression visit(SelectExpression selectExpression, Void arg) throws AsterixException {
+    public Expression visit(SelectExpression selectExpression, Expression arg) throws AsterixException {
         Scope scopeBeforeSelectExpression = scopeChecker.getCurrentScope();
         scopeChecker.createNewScope();
 
@@ -354,13 +355,11 @@
         }
 
         // visit the main select.
-        selectExpression.getSelectSetOperation().accept(this, arg);
+        selectExpression.getSelectSetOperation().accept(this, selectExpression);
 
         // visit order by
         if (selectExpression.hasOrderby()) {
-            for (Expression orderExpr : selectExpression.getOrderbyClause().getOrderbyList()) {
-                orderExpr.accept(this, arg);
-            }
+            selectExpression.getOrderbyClause().accept(this, arg);
         }
 
         // visit limit
@@ -376,12 +375,12 @@
     }
 
     @Override
-    public Expression visit(LiteralExpr l, Void arg) throws AsterixException {
+    public Expression visit(LiteralExpr l, Expression arg) throws AsterixException {
         return l;
     }
 
     @Override
-    public Expression visit(ListConstructor lc, Void arg) throws AsterixException {
+    public Expression visit(ListConstructor lc, Expression arg) throws AsterixException {
         List<Expression> newExprList = new ArrayList<Expression>();
         for (Expression expr : lc.getExprList()) {
             newExprList.add(expr.accept(this, arg));
@@ -391,7 +390,7 @@
     }
 
     @Override
-    public Expression visit(RecordConstructor rc, Void arg) throws AsterixException {
+    public Expression visit(RecordConstructor rc, Expression arg) throws AsterixException {
         for (FieldBinding binding : rc.getFbList()) {
             binding.setLeftExpr(binding.getLeftExpr().accept(this, arg));
             binding.setRightExpr(binding.getRightExpr().accept(this, arg));
@@ -400,7 +399,7 @@
     }
 
     @Override
-    public Expression visit(OperatorExpr operatorExpr, Void arg) throws AsterixException {
+    public Expression visit(OperatorExpr operatorExpr, Expression arg) throws AsterixException {
         List<Expression> newExprList = new ArrayList<Expression>();
         for (Expression expr : operatorExpr.getExprList()) {
             newExprList.add(expr.accept(this, arg));
@@ -410,7 +409,7 @@
     }
 
     @Override
-    public Expression visit(IfExpr ifExpr, Void arg) throws AsterixException {
+    public Expression visit(IfExpr ifExpr, Expression arg) throws AsterixException {
         ifExpr.setCondExpr(ifExpr.getCondExpr().accept(this, arg));
         ifExpr.setThenExpr(ifExpr.getThenExpr().accept(this, arg));
         ifExpr.setElseExpr(ifExpr.getElseExpr().accept(this, arg));
@@ -418,7 +417,7 @@
     }
 
     @Override
-    public Expression visit(QuantifiedExpression qe, Void arg) throws AsterixException {
+    public Expression visit(QuantifiedExpression qe, Expression arg) throws AsterixException {
         scopeChecker.createNewScope();
         for (QuantifiedPair pair : qe.getQuantifiedList()) {
             scopeChecker.getCurrentScope().addNewVarSymbolToScope(pair.getVarExpr().getVar());
@@ -430,7 +429,7 @@
     }
 
     @Override
-    public Expression visit(CallExpr callExpr, Void arg) throws AsterixException {
+    public Expression visit(CallExpr callExpr, Expression arg) throws AsterixException {
         List<Expression> newExprList = new ArrayList<Expression>();
         for (Expression expr : callExpr.getExprList()) {
             newExprList.add(expr.accept(this, arg));
@@ -440,7 +439,7 @@
     }
 
     @Override
-    public Expression visit(VariableExpr varExpr, Void arg) throws AsterixException {
+    public Expression visit(VariableExpr varExpr, Expression arg) throws AsterixException {
         String varName = varExpr.getVar().getValue();
         if (scopeChecker.isInForbiddenScopes(varName)) {
             throw new AsterixException(
@@ -454,19 +453,19 @@
     }
 
     @Override
-    public Expression visit(UnaryExpr u, Void arg) throws AsterixException {
+    public Expression visit(UnaryExpr u, Expression arg) throws AsterixException {
         u.setExpr(u.getExpr().accept(this, arg));
         return u;
     }
 
     @Override
-    public Expression visit(FieldAccessor fa, Void arg) throws AsterixException {
+    public Expression visit(FieldAccessor fa, Expression arg) throws AsterixException {
         fa.setExpr(fa.getExpr().accept(this, arg));
         return fa;
     }
 
     @Override
-    public Expression visit(IndexAccessor ia, Void arg) throws AsterixException {
+    public Expression visit(IndexAccessor ia, Expression arg) throws AsterixException {
         ia.setExpr(ia.getExpr().accept(this, arg));
         if (ia.getIndexExpr() != null) {
             ia.setIndexExpr(ia.getIndexExpr());
@@ -490,7 +489,7 @@
     }
 
     // Rewrites for global variable (e.g., dataset) references.
-    private Expression datasetRewrite(Expression expr) throws AsterixException {
+    private Expression datasetRewrite(VariableExpr expr) throws AsterixException {
         if (!overwrite) {
             return expr;
         }
@@ -498,7 +497,9 @@
         String dataverse = MetadataConstants.METADATA_DATAVERSE_NAME;
         FunctionSignature signature = new FunctionSignature(dataverse, funcName, 1);
         List<Expression> argList = new ArrayList<Expression>();
-        argList.add(new LiteralExpr(new StringLiteral(SqlppFormatPrintUtil.toString(expr))));
+        //Ignore the parser-generated prefix "$" for a dataset.
+        String dataset = SqlppVariableUtil.toUserDefinedVariableName(expr.getVar()).getValue();
+        argList.add(new LiteralExpr(new StringLiteral(dataset)));
         return new CallExpr(signature, argList);
     }
 }
diff --git a/asterix-lang-sqlpp/src/main/javacc/SQLPP.html b/asterix-lang-sqlpp/src/main/javacc/SQLPP.html
index ebe7926..bbecc21 100644
--- a/asterix-lang-sqlpp/src/main/javacc/SQLPP.html
+++ b/asterix-lang-sqlpp/src/main/javacc/SQLPP.html
@@ -863,7 +863,7 @@
 <TR>
 <TD ALIGN=RIGHT VALIGN=BASELINE><A NAME="prod93">GroupbyClause</A></TD>
 <TD ALIGN=CENTER VALIGN=BASELINE>::=</TD>
-<TD ALIGN=LEFT VALIGN=BASELINE>&lt;GROUP&gt; &lt;BY&gt; ( <A HREF="#prod43">Expression</A> ( ( &lt;AS&gt; )? <A HREF="#prod49">Variable</A> )? ( &lt;COMMA&gt; <A HREF="#prod43">Expression</A> ( ( &lt;AS&gt; )? <A HREF="#prod49">Variable</A> )? )* )</TD>
+<TD ALIGN=LEFT VALIGN=BASELINE>&lt;GROUP&gt; &lt;BY&gt; ( <A HREF="#prod43">Expression</A> ( ( &lt;AS&gt; )? <A HREF="#prod49">Variable</A> )? ( &lt;COMMA&gt; <A HREF="#prod43">Expression</A> ( ( &lt;AS&gt; )? <A HREF="#prod49">Variable</A> )? )* ) ( &lt;GROUP&gt; &lt;AS&gt; <A HREF="#prod49">Variable</A> ( &lt;LEFTPAREN&gt; <A HREF="#prod75">VariableRef</A> &lt;AS&gt; <A HREF="#prod18">Identifier</A> ( &lt;COMMA&gt; <A HREF="#prod75">VariableRef</A> &lt;AS&gt; <A HREF="#prod18">Identifier</A> )* &lt;RIGHTPAREN&gt; )? )?</TD>
 </TR>
 <TR>
 <TD ALIGN=RIGHT VALIGN=BASELINE><A NAME="prod94">HavingClause</A></TD>
diff --git a/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj b/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
index 38b4429..3c81cff 100644
--- a/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
+++ b/asterix-lang-sqlpp/src/main/javacc/SQLPP.jj
@@ -141,6 +141,7 @@
 import org.apache.asterix.lang.sqlpp.optype.SetOpType;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationInput;
 import org.apache.asterix.lang.sqlpp.struct.SetOperationRight;
+import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.algebricks.common.utils.Triple;
 import org.apache.hyracks.algebricks.core.algebra.expressions.IExpressionAnnotation;
@@ -757,14 +758,12 @@
 {
   <LEFTPAREN> (<IDENTIFIER>
     {
-      var = new VarIdentifier();
-      var.setValue(token.image);
+      var = SqlppVariableUtil.toInternalVariableIdentifier(token.image);
       paramList.add(var);
     }
   (<COMMA> <IDENTIFIER>
     {
-      var = new VarIdentifier();
-      var.setValue(token.image);
+      var = SqlppVariableUtil.toInternalVariableIdentifier(token.image);
       paramList.add(var);
     }
   )*)? <RIGHTPAREN>
@@ -1598,7 +1597,7 @@
           op.addOperand(operand);
         op.setCurrentop(true);
         }
-      op.addOperator(token.image);
+      op.addOperator(token.image.toLowerCase());
     }
 
     operand = AndExpr()
@@ -1629,7 +1628,7 @@
           op.addOperand(operand);
         op.setCurrentop(true);
         }
-      op.addOperator(token.image);
+      op.addOperator(token.image.toLowerCase());
     }
 
     operand = RelExpr()
@@ -1925,17 +1924,18 @@
     { String id = null; }
     (<IDENTIFIER> { id = token.image; } | id = QuotedString())
     {
+     id = SqlppVariableUtil.toInternalVariableName(id); // Prefix user-defined variables with "$"
      Identifier ident = lookupSymbol(id);
      if (isInForbiddenScopes(id)) {
        throw new ParseException("Inside limit clauses, it is disallowed to reference a variable having the same name as any variable bound in the same scope as the limit clause.");
      }
      if(ident != null) { // exist such ident
-       varExp.setIsNewVar(false);
        varExp.setVar((VarIdentifier)ident);
      } else {
        varExp.setVar(var);
+       varExp.setIsNewVar(false);
+       var.setValue(id);
      }
-     var.setValue(id);
      return varExp;
     }
 }
@@ -1950,6 +1950,7 @@
     { String id = null; }
     (<IDENTIFIER> { id = token.image; } | id = QuotedString())
     {
+     id = SqlppVariableUtil.toInternalVariableName(id); // prefix user-defined variables with "$".
      Identifier ident = lookupSymbol(id);
      if(ident != null) { // exist such ident
        varExp.setIsNewVar(false);
@@ -2498,6 +2499,9 @@
     Expression expr = null;
     VariableExpr decorVar = null;
     Expression decorExpr = null;
+
+    VariableExpr groupVar = null;
+    List<Pair<Expression, Identifier>> groupFieldList = new ArrayList<Pair<Expression, Identifier>>();
 }
 {
       {
@@ -2531,10 +2535,32 @@
          }
         )*
     )
+    (<GROUP> <AS> groupVar = Variable()
+      ( LOOKAHEAD(1)
+        {
+            VariableExpr fieldVarExpr = null;
+            String fieldIdentifierStr = null;
+        }
+        <LEFTPAREN>
+               fieldVarExpr = VariableRef() <AS> fieldIdentifierStr = Identifier()
+               {
+                   groupFieldList.add(new Pair<Expression, Identifier>(fieldVarExpr, new Identifier(fieldIdentifierStr)));
+               }
+        (<COMMA>
+               fieldVarExpr = VariableRef() <AS> fieldIdentifierStr = Identifier()
+               {
+                   groupFieldList.add(new Pair<Expression, Identifier>(fieldVarExpr, new Identifier(fieldIdentifierStr)));
+               }
+        )*
+        <RIGHTPAREN>
+      )?
+    )?
     {
       gbc.setGbyPairList(vePairList);
       gbc.setDecorPairList(new ArrayList<GbyVariableExpressionPair>());
       gbc.setWithVarList(new ArrayList<VariableExpr>());
+      gbc.setGroupVar(groupVar);
+      gbc.setGroupFieldList(groupFieldList);
       replaceCurrentScope(newScope);
       return gbc;
     }