diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/group-by/sugar-06-distinct.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/group-by/sugar-06-distinct.sqlpp
new file mode 100644
index 0000000..d961374
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/group-by/sugar-06-distinct.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE gby IF EXISTS;
+CREATE DATAVERSE gby;
+
+USE gby;
+
+CREATE TYPE EmpType AS {
+  name : string
+};
+
+CREATE DATASET Employee(EmpType) PRIMARY KEY name;
+
+FROM Employee e
+GROUP BY deptno
+SELECT deptno, MAX(DISTINCT salary) salary_agg
+ORDER BY salary_agg DESC
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
index fced7a9..18ec417 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
@@ -5,12 +5,12 @@
               {
                 -- AGGREGATE  |LOCAL|
                   -- ASSIGN  |LOCAL|
-                    -- MICRO_PRE_CLUSTERED_GROUP_BY[$$183]  |LOCAL|
+                    -- MICRO_PRE_CLUSTERED_GROUP_BY[$$184]  |LOCAL|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                             }
-                      -- IN_MEMORY_STABLE_SORT [$$183(ASC)]  |LOCAL|
+                      -- IN_MEMORY_STABLE_SORT [$$184(ASC)]  |LOCAL|
                         -- ASSIGN  |LOCAL|
                           -- UNNEST  |LOCAL|
                             -- SUBPLAN  |LOCAL|
@@ -27,7 +27,7 @@
             -- STREAM_PROJECT  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- INSERT_DELETE  |PARTITIONED|
-                  -- HASH_PARTITION_EXCHANGE [$$167]  |PARTITIONED|
+                  -- HASH_PARTITION_EXCHANGE [$$168]  |PARTITIONED|
                     -- ASSIGN  |PARTITIONED|
                       -- STREAM_PROJECT  |PARTITIONED|
                         -- ASSIGN  |PARTITIONED|
@@ -37,28 +37,28 @@
                                 -- ASSIGN  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- PRE_CLUSTERED_GROUP_BY[$$221]  |PARTITIONED|
+                                      -- PRE_CLUSTERED_GROUP_BY[$$222]  |PARTITIONED|
                                               {
                                                 -- AGGREGATE  |LOCAL|
                                                   -- STREAM_SELECT  |LOCAL|
                                                     -- NESTED_TUPLE_SOURCE  |LOCAL|
                                               }
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- STABLE_SORT [$$221(ASC)]  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$221]  |PARTITIONED|
+                                          -- STABLE_SORT [$$222(ASC)]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$222]  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- HYBRID_HASH_JOIN [$$267][$$190]  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$267]  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$268][$$191]  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$268]  |PARTITIONED|
                                                       -- ASSIGN  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- UNNEST  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- PRE_CLUSTERED_GROUP_BY[$$275]  |PARTITIONED|
+                                                                -- PRE_CLUSTERED_GROUP_BY[$$276]  |PARTITIONED|
                                                                         {
                                                                           -- AGGREGATE  |LOCAL|
-                                                                            -- MICRO_PRE_CLUSTERED_GROUP_BY[$$277, $$279]  |LOCAL|
+                                                                            -- MICRO_PRE_CLUSTERED_GROUP_BY[$$278, $$280]  |LOCAL|
                                                                                     {
                                                                                       -- AGGREGATE  |LOCAL|
                                                                                         -- STREAM_SELECT  |LOCAL|
@@ -68,8 +68,8 @@
                                                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                                         }
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- STABLE_SORT [$$275(ASC), $$277(ASC), $$279(ASC)]  |PARTITIONED|
-                                                                      -- HASH_PARTITION_EXCHANGE [$$275]  |PARTITIONED|
+                                                                    -- STABLE_SORT [$$276(ASC), $$278(ASC), $$280(ASC)]  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$276]  |PARTITIONED|
                                                                         -- UNION_ALL  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |PARTITIONED|
@@ -94,7 +94,7 @@
                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                     -- BTREE_SEARCH  |PARTITIONED|
                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                        -- STABLE_SORT [$$289(ASC)]  |PARTITIONED|
+                                                                                                                        -- STABLE_SORT [$$290(ASC)]  |PARTITIONED|
                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -117,7 +117,7 @@
                                                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                     -- BTREE_SEARCH  |PARTITIONED|
                                                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                        -- STABLE_SORT [$$228(ASC)]  |PARTITIONED|
+                                                                                                                                                        -- STABLE_SORT [$$229(ASC)]  |PARTITIONED|
                                                                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -145,7 +145,7 @@
                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                               -- BTREE_SEARCH  |PARTITIONED|
                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                  -- STABLE_SORT [$$289(ASC)]  |PARTITIONED|
+                                                                                                                  -- STABLE_SORT [$$290(ASC)]  |PARTITIONED|
                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -168,7 +168,7 @@
                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                               -- BTREE_SEARCH  |PARTITIONED|
                                                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                                  -- STABLE_SORT [$$228(ASC)]  |PARTITIONED|
+                                                                                                                                                  -- STABLE_SORT [$$229(ASC)]  |PARTITIONED|
                                                                                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                       -- STREAM_PROJECT  |PARTITIONED|
                                                                                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -176,12 +176,12 @@
                                                                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                                                               -- ASSIGN  |PARTITIONED|
                                                                                                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                    -- HASH_PARTITION_EXCHANGE [$$190]  |PARTITIONED|
+                                                    -- HASH_PARTITION_EXCHANGE [$$191]  |PARTITIONED|
                                                       -- ASSIGN  |PARTITIONED|
                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- HYBRID_HASH_JOIN [$$198, $$200][$$192, $$193]  |PARTITIONED|
-                                                              -- HASH_PARTITION_EXCHANGE [$$198, $$200]  |PARTITIONED|
+                                                            -- HYBRID_HASH_JOIN [$$199, $$201][$$193, $$194]  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$199, $$201]  |PARTITIONED|
                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                   -- ASSIGN  |PARTITIONED|
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
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 59ddab8..88cb592 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|
-          -- SORT_GROUP_BY[$$39]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$40]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$39]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$35]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$40]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$36]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join_06.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join_06.plan
index 5163d81..c81b6b0 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join_06.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/btree-index-join/secondary-equi-join_06.plan
@@ -8,7 +8,7 @@
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- BTREE_SEARCH  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- STABLE_SORT [$$68(ASC)]  |PARTITIONED|
+                    -- STABLE_SORT [$$69(ASC)]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -17,13 +17,13 @@
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- SORT_GROUP_BY[$$66]  |PARTITIONED|
+                                      -- SORT_GROUP_BY[$$67]  |PARTITIONED|
                                               {
                                                 -- AGGREGATE  |LOCAL|
                                                   -- NESTED_TUPLE_SOURCE  |LOCAL|
                                               }
-                                        -- HASH_PARTITION_EXCHANGE [$$66]  |PARTITIONED|
-                                          -- SORT_GROUP_BY[$$59]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$67]  |PARTITIONED|
+                                          -- SORT_GROUP_BY[$$60]  |PARTITIONED|
                                                   {
                                                     -- AGGREGATE  |LOCAL|
                                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/cust_group_no_agg.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/cust_group_no_agg.plan
index 1e82aa4..cdea343 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/cust_group_no_agg.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/cust_group_no_agg.plan
@@ -1,11 +1,11 @@
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    -- SORT_GROUP_BY[$$27]  |PARTITIONED|
+    -- SORT_GROUP_BY[$$28]  |PARTITIONED|
             {
               -- AGGREGATE  |LOCAL|
                 -- NESTED_TUPLE_SOURCE  |LOCAL|
             }
-      -- HASH_PARTITION_EXCHANGE [$$27]  |PARTITIONED|
+      -- HASH_PARTITION_EXCHANGE [$$28]  |PARTITIONED|
         -- STREAM_PROJECT  |PARTITIONED|
           -- ASSIGN  |PARTITIONED|
             -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/gby_partitioning_property_01.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/gby_partitioning_property_01.plan
index 254bf24..1bb5b99 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/gby_partitioning_property_01.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/gby_partitioning_property_01.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$60]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$61]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              -- SORT_GROUP_BY[$$50]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$51]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -17,8 +17,8 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$50][$$53]  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$50]  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$51][$$54]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$51]  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ASSIGN  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
@@ -26,7 +26,7 @@
                                   -- DATASOURCE_SCAN  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                        -- HASH_PARTITION_EXCHANGE [$$53]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$54]  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ASSIGN  |PARTITIONED|
                               -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/group-by/sugar-06-distinct.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/group-by/sugar-06-distinct.plan
new file mode 100644
index 0000000..81b8787
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/group-by/sugar-06-distinct.plan
@@ -0,0 +1,27 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$48(DESC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 3] [$$48(DESC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$46]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- MICRO_PRE_SORTED_DISTINCT_BY  |LOCAL|
+                                  -- IN_MEMORY_STABLE_SORT [$$41(ASC)]  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- STABLE_SORT [$$46(ASC)]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$46]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1806.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1806.plan
index 124d677..fef4ae3 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1806.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1806.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$l_returnflag(ASC), $$l_linestatus(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$148, $$149]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$149, $$150]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$148, $$149]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$116, $$117]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$149, $$150]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$117, $$118]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2354.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2354.plan
index 90d5b09..b255ab2 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2354.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2354.plan
@@ -1,8 +1,8 @@
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
-      -- SORT_MERGE_EXCHANGE [$$69(ASC), $$70(ASC), $$71(ASC) ]  |PARTITIONED|
-        -- STABLE_SORT [$$69(ASC), $$70(ASC), $$71(ASC)]  |PARTITIONED|
+      -- SORT_MERGE_EXCHANGE [$$70(ASC), $$71(ASC), $$72(ASC) ]  |PARTITIONED|
+        -- STABLE_SORT [$$70(ASC), $$71(ASC), $$72(ASC)]  |PARTITIONED|
           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
             -- UNION_ALL  |PARTITIONED|
               -- RANDOM_PARTITION_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2408.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2408.plan
index e6e3520..640a064 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2408.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-2408.plan
@@ -1,68 +1,34 @@
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
-      -- SORT_MERGE_EXCHANGE [$$49(ASC) ]  |PARTITIONED|
-        -- STABLE_SORT [$$49(ASC)]  |PARTITIONED|
-          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-            -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- STREAM_PROJECT  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$39(ASC) ]  |PARTITIONED|
+            -- STABLE_SORT [$$39(ASC)]  |PARTITIONED|
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                -- PRE_CLUSTERED_GROUP_BY[$$60]  |PARTITIONED|
-                        {
-                          -- AGGREGATE  |LOCAL|
-                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                        }
+                -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$56]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$37]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- STREAM_SELECT  |LOCAL|
                                   -- NESTED_TUPLE_SOURCE  |LOCAL|
                             }
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$37][$$40]  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- HYBRID_HASH_JOIN [$$56][$$52]  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- PRE_CLUSTERED_GROUP_BY[$$46]  |PARTITIONED|
-                                                  {
-                                                    -- AGGREGATE  |LOCAL|
-                                                      -- STREAM_SELECT  |LOCAL|
-                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                  }
-                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- HYBRID_HASH_JOIN [$$46][$$50]  |PARTITIONED|
-                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                        -- ASSIGN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- DATASOURCE_SCAN  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- REPLICATE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$50]  |PARTITIONED|
-                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                            -- ASSIGN  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ASSIGN  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$40]  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ASSIGN  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                          -- REPLICATE  |PARTITIONED|
-                                            -- HASH_PARTITION_EXCHANGE [$$50]  |PARTITIONED|
-                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                -- ASSIGN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue601.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue601.plan
index 8e3ca40..3651eba 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue601.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/query-issue601.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- SORT_GROUP_BY[$$41]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$42]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$41]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$38]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$42]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$39]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/rtree-index/rtree-sidx-idxonly-01-disable-idxonly.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rtree-index/rtree-sidx-idxonly-01-disable-idxonly.plan
index 1b959cb..331d31f 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/rtree-index/rtree-sidx-idxonly-01-disable-idxonly.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/rtree-index/rtree-sidx-idxonly-01-disable-idxonly.plan
@@ -12,7 +12,7 @@
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- BTREE_SEARCH  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- STABLE_SORT [$$55(ASC)]  |PARTITIONED|
+                            -- STABLE_SORT [$$56(ASC)]  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
index 9672972..a010ace 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/exists.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$cntrycode(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$162]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$164]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$162]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$139]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$164]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$141]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -21,25 +21,25 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- SORT_GROUP_BY[$$159]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$161]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_EXCHANGE [$$159]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$153]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$161]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$155]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- STREAM_SELECT  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$153(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$155(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$148][$$145]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$148]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$150][$$147]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$150]  |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 [$$145]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$147]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|
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 9dcdd24..6567388 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 [$$41(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$41(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$44(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$44(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- STREAM_SELECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$34]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$39]  |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 [$$34][$$22]  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$39][$$29]  |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 [$$22]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$29]  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
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 2d9d64b..bf5fa63 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,13 +2,13 @@
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
-        -- SORT_MERGE_EXCHANGE [$$46(ASC) ]  |PARTITIONED|
-          -- STABLE_SORT [$$46(ASC)]  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$49(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$49(ASC)]  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
               -- STREAM_PROJECT  |PARTITIONED|
                 -- STREAM_SELECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$38]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$43]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- STREAM_SELECT  |LOCAL|
@@ -22,7 +22,7 @@
                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                             }
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                        -- HYBRID_HASH_JOIN [$$38][$$37]  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$43][$$42]  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                             -- STREAM_SELECT  |PARTITIONED|
                               -- ASSIGN  |PARTITIONED|
@@ -30,7 +30,7 @@
                                   -- DATASOURCE_SCAN  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                          -- HASH_PARTITION_EXCHANGE [$$37]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$42]  |PARTITIONED|
                             -- STREAM_PROJECT  |PARTITIONED|
                               -- ASSIGN  |PARTITIONED|
                                 -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
index 170a15b..57e0fa0 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/subquery/not_exists.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$cntrycode(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$163]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$165]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$163]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$140]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$165]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$142]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -21,25 +21,25 @@
                         -- STREAM_SELECT  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- SORT_GROUP_BY[$$160]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$162]  |PARTITIONED|
                                       {
                                         -- AGGREGATE  |LOCAL|
                                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                                       }
-                                -- HASH_PARTITION_EXCHANGE [$$160]  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$154]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$162]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$156]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- STREAM_SELECT  |LOCAL|
                                                 -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$154(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$156(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$149][$$146]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$149]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$151][$$148]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$151]  |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 [$$146]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$148]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                       -- DATASOURCE_SCAN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1580.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1580.plan
index 0fabd6d..8887588 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1580.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1580.plan
@@ -3,72 +3,71 @@
     -- STREAM_LIMIT  |UNPARTITIONED|
       -- STREAM_PROJECT  |PARTITIONED|
         -- ASSIGN  |PARTITIONED|
-          -- STREAM_PROJECT  |PARTITIONED|
-            -- SORT_MERGE_EXCHANGE [$$113(ASC) ]  |PARTITIONED|
-              -- STREAM_LIMIT  |PARTITIONED|
-                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  -- STABLE_SORT [topK: 100] [$$113(ASC)]  |PARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- STREAM_SELECT  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- SORT_GROUP_BY[$$123]  |PARTITIONED|
-                                    {
-                                      -- AGGREGATE  |LOCAL|
-                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                    }
-                              -- HASH_PARTITION_EXCHANGE [$$123]  |PARTITIONED|
-                                -- SORT_GROUP_BY[$$101]  |PARTITIONED|
-                                        {
-                                          -- AGGREGATE  |LOCAL|
-                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                        }
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- HYBRID_HASH_JOIN [$$105][$$108]  |PARTITIONED|
-                                          -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- HYBRID_HASH_JOIN [$$114][$$107]  |PARTITIONED|
-                                                  -- HASH_PARTITION_EXCHANGE [$$114]  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- HYBRID_HASH_JOIN [$$104][$$119]  |PARTITIONED|
-                                                          -- HASH_PARTITION_EXCHANGE [$$104]  |PARTITIONED|
-                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- HYBRID_HASH_JOIN [$$103][$$117]  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$109(ASC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 100] [$$109(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- STREAM_SELECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$119]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- HASH_PARTITION_EXCHANGE [$$119]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$100]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$103][$$106]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$103]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$111][$$105]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$111]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$102][$$116]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$102]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- HYBRID_HASH_JOIN [$$101][$$114]  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$114]  |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 [$$117]  |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 [$$119]  |PARTITIONED|
-                                                            -- STREAM_PROJECT  |PARTITIONED|
-                                                              -- ASSIGN  |PARTITIONED|
-                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$116]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
index c27116d..c636836 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581-correlated.plan
@@ -8,7 +8,7 @@
               -- ASSIGN  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- PRE_CLUSTERED_GROUP_BY[$$149]  |PARTITIONED|
+                    -- PRE_CLUSTERED_GROUP_BY[$$152]  |PARTITIONED|
                             {
                               -- AGGREGATE  |LOCAL|
                                 -- AGGREGATE  |LOCAL|
@@ -20,9 +20,9 @@
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- STREAM_PROJECT  |PARTITIONED|
                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- HYBRID_HASH_JOIN [$$149][$$150]  |PARTITIONED|
+                            -- HYBRID_HASH_JOIN [$$152][$$153]  |PARTITIONED|
                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                -- PRE_CLUSTERED_GROUP_BY[$$137]  |PARTITIONED|
+                                -- PRE_CLUSTERED_GROUP_BY[$$140]  |PARTITIONED|
                                         {
                                           -- AGGREGATE  |LOCAL|
                                             -- AGGREGATE  |LOCAL|
@@ -32,13 +32,13 @@
                                                     -- NESTED_TUPLE_SOURCE  |LOCAL|
                                         }
                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STABLE_SORT [$$137(ASC)]  |PARTITIONED|
+                                    -- STABLE_SORT [$$140(ASC)]  |PARTITIONED|
                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                         -- STREAM_PROJECT  |PARTITIONED|
                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                            -- HYBRID_HASH_JOIN [$$137][$$138]  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$140][$$141]  |PARTITIONED|
                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- PRE_CLUSTERED_GROUP_BY[$$117]  |PARTITIONED|
+                                                -- PRE_CLUSTERED_GROUP_BY[$$120]  |PARTITIONED|
                                                         {
                                                           -- AGGREGATE  |LOCAL|
                                                             -- AGGREGATE  |LOCAL|
@@ -48,7 +48,7 @@
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- HYBRID_HASH_JOIN [$$117][$$127]  |PARTITIONED|
+                                                        -- HYBRID_HASH_JOIN [$$120][$$130]  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -66,7 +66,7 @@
                                                               -- ASSIGN  |PARTITIONED|
                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                   -- REPLICATE  |PARTITIONED|
-                                                                    -- HASH_PARTITION_EXCHANGE [$$174]  |PARTITIONED|
+                                                                    -- HASH_PARTITION_EXCHANGE [$$177]  |PARTITIONED|
                                                                       -- ASSIGN  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ASSIGN  |PARTITIONED|
@@ -78,18 +78,18 @@
                                                                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                              -- HASH_PARTITION_EXCHANGE [$$138]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$141]  |PARTITIONED|
                                                 -- ASSIGN  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- HYBRID_HASH_JOIN [$$131][$$130]  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$131]  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$134][$$133]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$134]  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- STREAM_SELECT  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- PRE_CLUSTERED_GROUP_BY[$$139]  |PARTITIONED|
+                                                                    -- PRE_CLUSTERED_GROUP_BY[$$142]  |PARTITIONED|
                                                                             {
                                                                               -- AGGREGATE  |LOCAL|
                                                                                 -- AGGREGATE  |LOCAL|
@@ -99,7 +99,7 @@
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- HYBRID_HASH_JOIN [$$139][$$141]  |PARTITIONED|
+                                                                            -- HYBRID_HASH_JOIN [$$142][$$144]  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                   -- ASSIGN  |PARTITIONED|
@@ -115,7 +115,7 @@
                                                                                   -- ASSIGN  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- REPLICATE  |PARTITIONED|
-                                                                                        -- HASH_PARTITION_EXCHANGE [$$174]  |PARTITIONED|
+                                                                                        -- HASH_PARTITION_EXCHANGE [$$177]  |PARTITIONED|
                                                                                           -- ASSIGN  |PARTITIONED|
                                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                                               -- ASSIGN  |PARTITIONED|
@@ -127,7 +127,7 @@
                                                                                                           -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$130]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$133]  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ASSIGN  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
@@ -140,13 +140,13 @@
                                                                             -- DATASOURCE_SCAN  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                              -- HASH_PARTITION_EXCHANGE [$$150]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$153]  |PARTITIONED|
                                 -- ASSIGN  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- HYBRID_HASH_JOIN [$$133][$$132]  |PARTITIONED|
-                                        -- HASH_PARTITION_EXCHANGE [$$133]  |PARTITIONED|
-                                          -- PRE_CLUSTERED_GROUP_BY[$$151]  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$136][$$135]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$136]  |PARTITIONED|
+                                          -- PRE_CLUSTERED_GROUP_BY[$$154]  |PARTITIONED|
                                                   {
                                                     -- AGGREGATE  |LOCAL|
                                                       -- AGGREGATE  |LOCAL|
@@ -155,18 +155,18 @@
                                                             -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                   }
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- STABLE_SORT [$$151(ASC)]  |PARTITIONED|
+                                              -- STABLE_SORT [$$154(ASC)]  |PARTITIONED|
                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- HYBRID_HASH_JOIN [$$151][$$154]  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$154][$$157]  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- STREAM_SELECT  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                 -- ASSIGN  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                    -- PRE_CLUSTERED_GROUP_BY[$$155]  |PARTITIONED|
+                                                                    -- PRE_CLUSTERED_GROUP_BY[$$158]  |PARTITIONED|
                                                                             {
                                                                               -- AGGREGATE  |LOCAL|
                                                                                 -- AGGREGATE  |LOCAL|
@@ -176,7 +176,7 @@
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- HYBRID_HASH_JOIN [$$155][$$156]  |PARTITIONED|
+                                                                            -- HYBRID_HASH_JOIN [$$158][$$159]  |PARTITIONED|
                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -194,7 +194,7 @@
                                                                                   -- ASSIGN  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- REPLICATE  |PARTITIONED|
-                                                                                        -- HASH_PARTITION_EXCHANGE [$$174]  |PARTITIONED|
+                                                                                        -- HASH_PARTITION_EXCHANGE [$$177]  |PARTITIONED|
                                                                                           -- ASSIGN  |PARTITIONED|
                                                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                                                               -- ASSIGN  |PARTITIONED|
@@ -206,18 +206,18 @@
                                                                                                           -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                               -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- HASH_PARTITION_EXCHANGE [$$154]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$157]  |PARTITIONED|
                                                           -- ASSIGN  |PARTITIONED|
                                                             -- STREAM_PROJECT  |PARTITIONED|
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- HYBRID_HASH_JOIN [$$168][$$167]  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$168]  |PARTITIONED|
+                                                                -- HYBRID_HASH_JOIN [$$171][$$170]  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$171]  |PARTITIONED|
                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                       -- ASSIGN  |PARTITIONED|
                                                                         -- STREAM_PROJECT  |PARTITIONED|
                                                                           -- STREAM_SELECT  |PARTITIONED|
                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                              -- PRE_CLUSTERED_GROUP_BY[$$173]  |PARTITIONED|
+                                                                              -- PRE_CLUSTERED_GROUP_BY[$$176]  |PARTITIONED|
                                                                                       {
                                                                                         -- AGGREGATE  |LOCAL|
                                                                                           -- AGGREGATE  |LOCAL|
@@ -227,7 +227,7 @@
                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                   -- STREAM_PROJECT  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                      -- HYBRID_HASH_JOIN [$$173][$$174]  |PARTITIONED|
+                                                                                      -- HYBRID_HASH_JOIN [$$176][$$177]  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- REPLICATE  |PARTITIONED|
                                                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -237,7 +237,7 @@
                                                                                                     -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                           -- REPLICATE  |PARTITIONED|
-                                                                                            -- HASH_PARTITION_EXCHANGE [$$174]  |PARTITIONED|
+                                                                                            -- HASH_PARTITION_EXCHANGE [$$177]  |PARTITIONED|
                                                                                               -- ASSIGN  |PARTITIONED|
                                                                                                 -- STREAM_PROJECT  |PARTITIONED|
                                                                                                   -- ASSIGN  |PARTITIONED|
@@ -249,7 +249,7 @@
                                                                                                               -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$167]  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$170]  |PARTITIONED|
                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                       -- ASSIGN  |PARTITIONED|
                                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
@@ -260,7 +260,7 @@
                                                                                   -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                       -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                        -- HASH_PARTITION_EXCHANGE [$$132]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$135]  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ASSIGN  |PARTITIONED|
                                               -- STREAM_PROJECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
index 67965bf..38d0b5f 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1581.plan
@@ -16,7 +16,7 @@
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ASSIGN  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- PRE_CLUSTERED_GROUP_BY[$$146]  |PARTITIONED|
+                      -- PRE_CLUSTERED_GROUP_BY[$$149]  |PARTITIONED|
                               {
                                 -- AGGREGATE  |LOCAL|
                                   -- AGGREGATE  |LOCAL|
@@ -28,9 +28,9 @@
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$146][$$147]  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$149][$$150]  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$82]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$85]  |PARTITIONED|
                                           {
                                             -- AGGREGATE  |LOCAL|
                                               -- AGGREGATE  |LOCAL|
@@ -40,12 +40,12 @@
                                                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                                           }
                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$82(ASC)]  |PARTITIONED|
+                                      -- STABLE_SORT [$$85(ASC)]  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- STREAM_PROJECT  |PARTITIONED|
                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                              -- HYBRID_HASH_JOIN [$$82][$$139]  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$82]  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$85][$$142]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$85]  |PARTITIONED|
                                                   -- STREAM_PROJECT  |UNPARTITIONED|
                                                     -- ASSIGN  |UNPARTITIONED|
                                                       -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
@@ -63,7 +63,7 @@
                                                                               -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                   -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                -- HASH_PARTITION_EXCHANGE [$$139]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$142]  |PARTITIONED|
                                                   -- ASSIGN  |PARTITIONED|
                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                       -- STREAM_SELECT  |PARTITIONED|
@@ -94,7 +94,7 @@
                                                                                                         -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                -- HASH_PARTITION_EXCHANGE [$$147]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$150]  |PARTITIONED|
                                   -- ASSIGN  |PARTITIONED|
                                     -- STREAM_PROJECT  |PARTITIONED|
                                       -- STREAM_SELECT  |PARTITIONED|
@@ -108,7 +108,7 @@
                                                       -- STREAM_SELECT  |PARTITIONED|
                                                         -- ASSIGN  |PARTITIONED|
                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                            -- PRE_CLUSTERED_GROUP_BY[$$148]  |PARTITIONED|
+                                                            -- PRE_CLUSTERED_GROUP_BY[$$151]  |PARTITIONED|
                                                                     {
                                                                       -- AGGREGATE  |LOCAL|
                                                                         -- AGGREGATE  |LOCAL|
@@ -117,12 +117,12 @@
                                                                               -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                                     }
                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- STABLE_SORT [$$148(ASC)]  |PARTITIONED|
+                                                                -- STABLE_SORT [$$151(ASC)]  |PARTITIONED|
                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                     -- STREAM_PROJECT  |PARTITIONED|
                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                        -- HYBRID_HASH_JOIN [$$148][$$149]  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$148]  |PARTITIONED|
+                                                                        -- HYBRID_HASH_JOIN [$$151][$$152]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$151]  |PARTITIONED|
                                                                             -- STREAM_PROJECT  |UNPARTITIONED|
                                                                               -- ASSIGN  |UNPARTITIONED|
                                                                                 -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
@@ -140,7 +140,7 @@
                                                                                                         -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                           -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                                                                             -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                          -- HASH_PARTITION_EXCHANGE [$$149]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$152]  |PARTITIONED|
                                                                             -- ASSIGN  |PARTITIONED|
                                                                               -- STREAM_PROJECT  |PARTITIONED|
                                                                                 -- STREAM_SELECT  |PARTITIONED|
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 2033230..b5e9913 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
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$l_shipmode(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$121]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$122]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$103]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$122]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$104]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -17,12 +17,12 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$103][$$110]  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$104][$$111]  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$112][$$107]  |PARTITIONED|
-                                -- HASH_PARTITION_EXCHANGE [$$112]  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$113][$$108]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$113]  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- STREAM_SELECT  |PARTITIONED|
                                       -- ASSIGN  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping_broadcast.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping_broadcast.plan
index 61d53a7..339e9ce 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping_broadcast.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpch/q12_shipping_broadcast.plan
@@ -3,13 +3,13 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- SORT_MERGE_EXCHANGE [$$l_shipmode(ASC) ]  |PARTITIONED|
-          -- SORT_GROUP_BY[$$121]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$122]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
-            -- HASH_PARTITION_EXCHANGE [$$121]  |PARTITIONED|
-              -- SORT_GROUP_BY[$$103]  |PARTITIONED|
+            -- HASH_PARTITION_EXCHANGE [$$122]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$104]  |PARTITIONED|
                       {
                         -- AGGREGATE  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
@@ -17,11 +17,11 @@
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                   -- STREAM_PROJECT  |PARTITIONED|
                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- HYBRID_HASH_JOIN [$$103][$$110]  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$104][$$111]  |PARTITIONED|
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                           -- STREAM_PROJECT  |PARTITIONED|
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- HYBRID_HASH_JOIN [$$111][$$107]  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$112][$$108]  |PARTITIONED|
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                   -- STREAM_PROJECT  |PARTITIONED|
                                     -- STREAM_SELECT  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast
index 1e0efba..b27701c 100644
--- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast
+++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias.ast
@@ -1,31 +1,6 @@
 Query:
 SELECT [
-FunctionCall asterix.sql-sum@1[
-  (
-    SELECT ELEMENT [
-    OperatorExpr [
-      FieldAccessor [
-        FieldAccessor [
-          Variable [ Name=#3 ]
-          Field=t
-        ]
-        Field=a
-      ]
-      *
-      FieldAccessor [
-        FieldAccessor [
-          Variable [ Name=#3 ]
-          Field=t
-        ]
-        Field=b
-      ]
-    ]
-    ]
-    FROM [      Variable [ Name=#1 ]
-      AS Variable [ Name=#3 ]
-    ]
-  )
-]
+Variable [ Name=#1 ]
 root
 ]
 FROM [  FunctionCall asterix.dataset@1[
@@ -40,19 +15,20 @@
     Variable [ Name=$t ]
     Field=id
   ]
-  GROUP AS Variable [ Name=#1 ]
+  GROUP AS Variable [ Name=#2 ]
   (
     t:=Variable [ Name=$t ]
   )
 
-Orderby
+Let Variable [ Name=#1 ]
+  :=
   FunctionCall asterix.sql-sum@1[
     (
       SELECT ELEMENT [
       OperatorExpr [
         FieldAccessor [
           FieldAccessor [
-            Variable [ Name=#2 ]
+            Variable [ Name=#3 ]
             Field=t
           ]
           Field=a
@@ -60,17 +36,19 @@
         *
         FieldAccessor [
           FieldAccessor [
-            Variable [ Name=#2 ]
+            Variable [ Name=#3 ]
             Field=t
           ]
           Field=b
         ]
       ]
       ]
-      FROM [        Variable [ Name=#1 ]
-        AS Variable [ Name=#2 ]
+      FROM [        Variable [ Name=#2 ]
+        AS Variable [ Name=#3 ]
       ]
     )
   ]
+Orderby
+  Variable [ Name=#1 ]
   ASC
 
diff --git a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast
index 7deb117..65cedd3 100644
--- a/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast
+++ b/asterixdb/asterix-app/src/test/resources/parserts/results_parser_sqlpp/columnalias3.ast
@@ -4,32 +4,7 @@
   (
     LiteralExpr [STRING] [root]
     :
-    FunctionCall asterix.sql-sum@1[
-      (
-        SELECT ELEMENT [
-        OperatorExpr [
-          FieldAccessor [
-            FieldAccessor [
-              Variable [ Name=#3 ]
-              Field=t
-            ]
-            Field=a
-          ]
-          *
-          FieldAccessor [
-            FieldAccessor [
-              Variable [ Name=#3 ]
-              Field=t
-            ]
-            Field=b
-          ]
-        ]
-        ]
-        FROM [          Variable [ Name=#1 ]
-          AS Variable [ Name=#3 ]
-        ]
-      )
-    ]
+    Variable [ Name=#1 ]
   )
 ]
 ]
@@ -45,19 +20,20 @@
     Variable [ Name=$t ]
     Field=id
   ]
-  GROUP AS Variable [ Name=#1 ]
+  GROUP AS Variable [ Name=#2 ]
   (
     t:=Variable [ Name=$t ]
   )
 
-Orderby
+Let Variable [ Name=#1 ]
+  :=
   FunctionCall asterix.sql-sum@1[
     (
       SELECT ELEMENT [
       OperatorExpr [
         FieldAccessor [
           FieldAccessor [
-            Variable [ Name=#2 ]
+            Variable [ Name=#3 ]
             Field=t
           ]
           Field=a
@@ -65,17 +41,19 @@
         *
         FieldAccessor [
           FieldAccessor [
-            Variable [ Name=#2 ]
+            Variable [ Name=#3 ]
             Field=t
           ]
           Field=b
         ]
       ]
       ]
-      FROM [        Variable [ Name=#1 ]
-        AS Variable [ Name=#2 ]
+      FROM [        Variable [ Name=#2 ]
+        AS Variable [ Name=#3 ]
       ]
     )
   ]
+Orderby
+  Variable [ Name=#1 ]
   ASC
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.1.ddl.sqlpp
new file mode 100644
index 0000000..c4bcf0e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.1.ddl.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE TYPE EmpType AS {
+  name : string
+};
+
+CREATE DATASET Employee(EmpType) PRIMARY KEY name;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.2.update.sqlpp
new file mode 100644
index 0000000..8c4212d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.2.update.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+INSERT INTO Employee (
+[
+   { 'name': 'a', 'deptno': 1, 'salary': 2000 },
+   { 'name': 'b', 'deptno': 1, 'salary': 3000 },
+
+   { 'name': 'f', 'deptno': 2, 'salary': 4000 },
+   { 'name': 'g', 'deptno': 2, 'salary': 5000 },
+
+   { 'name': 'k', 'deptno': 3, 'salary': null },
+   { 'name': 'l', 'deptno': 3, 'salary': 6000 },
+   { 'name': 'm', 'deptno': 3                 },
+
+   { 'name': 'q', 'deptno': 4  }
+]
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.3.query.sqlpp
new file mode 100644
index 0000000..879ded7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/flwor/order-by-13/order-by-13.3.query.sqlpp
@@ -0,0 +1,25 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE test;
+
+FROM Employee e
+SELECT to_string(deptno) AS deptno_str
+ORDER BY deptno_str, salary
+LIMIT 4;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-06-distinct/sugar-06-distinct.9.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-06-distinct/sugar-06-distinct.9.query.sqlpp
new file mode 100644
index 0000000..340c590
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/sugar-06-distinct/sugar-06-distinct.9.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE gby;
+
+FROM Employee e
+GROUP BY deptno
+SELECT deptno, MAX(DISTINCT salary) salary_agg
+ORDER BY salary_agg DESC
+LIMIT 3;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm
index cbbd46b..384e8f8 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access/explain_field_access.1.adm
@@ -1,44 +1,44 @@
-distribute result [$$38]
+distribute result [$$40]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   exchange
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$38])
+    project ([$$40])
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$38] <- [{"deptId": $#1, "star_cost": $$41}]
+      assign [$$40] <- [{"deptId": $#1, "star_cost": $$43}]
       -- ASSIGN  |PARTITIONED|
         exchange
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          group by ([$#1 := $$46]) decor ([]) {
-                    aggregate [$$41] <- [agg-sql-sum($$45)]
+          group by ([$#1 := $$48]) decor ([]) {
+                    aggregate [$$43] <- [agg-sql-sum($$47)]
                     -- AGGREGATE  |LOCAL|
                       nested tuple source
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                  }
-          -- SORT_GROUP_BY[$$46]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$48]  |PARTITIONED|
             exchange
-            -- HASH_PARTITION_EXCHANGE [$$46]  |PARTITIONED|
-              group by ([$$46 := $$39]) decor ([]) {
-                        aggregate [$$45] <- [agg-local-sql-sum($$36)]
+            -- HASH_PARTITION_EXCHANGE [$$48]  |PARTITIONED|
+              group by ([$$48 := $$41]) decor ([]) {
+                        aggregate [$$47] <- [agg-local-sql-sum($$38)]
                         -- AGGREGATE  |LOCAL|
                           nested tuple source
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                      }
-              -- SORT_GROUP_BY[$$39]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$41]  |PARTITIONED|
                 exchange
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  project ([$$36, $$39])
+                  project ([$$38, $$41])
                   -- STREAM_PROJECT  |PARTITIONED|
-                    assign [$$39] <- [substring($$43.getField("department_id"), 0)]
+                    assign [$$41] <- [substring($$45.getField("department_id"), 0)]
                     -- ASSIGN  |PARTITIONED|
-                      project ([$$36, $$43])
+                      project ([$$38, $$45])
                       -- STREAM_PROJECT  |PARTITIONED|
-                        assign [$$36, $$43] <- [$$e.getField("salary"), $$e.getField("dept")]
+                        assign [$$38, $$45] <- [$$e.getField("salary"), $$e.getField("dept")]
                         -- ASSIGN  |PARTITIONED|
                           project ([$$e])
                           -- STREAM_PROJECT  |PARTITIONED|
                             exchange
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              data-scan []<-[$$40, $$e] <- gby.Employee
+                              data-scan []<-[$$42, $$e] <- gby.Employee
                               -- DATASOURCE_SCAN  |PARTITIONED|
                                 exchange
                                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm
index 7aec0e1..b82dda7 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/explain/explain_field_access_closed/explain_field_access_closed.1.adm
@@ -1,40 +1,40 @@
-distribute result [$$37]
+distribute result [$$39]
 -- DISTRIBUTE_RESULT  |PARTITIONED|
   exchange
   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-    project ([$$37])
+    project ([$$39])
     -- STREAM_PROJECT  |PARTITIONED|
-      assign [$$37] <- [{"deptId": $#1, "star_cost": $$40}]
+      assign [$$39] <- [{"deptId": $#1, "star_cost": $$42}]
       -- ASSIGN  |PARTITIONED|
         exchange
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          group by ([$#1 := $$44]) decor ([]) {
-                    aggregate [$$40] <- [agg-sql-sum($$43)]
+          group by ([$#1 := $$46]) decor ([]) {
+                    aggregate [$$42] <- [agg-sql-sum($$45)]
                     -- AGGREGATE  |LOCAL|
                       nested tuple source
                       -- NESTED_TUPLE_SOURCE  |LOCAL|
                  }
-          -- SORT_GROUP_BY[$$44]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$46]  |PARTITIONED|
             exchange
-            -- HASH_PARTITION_EXCHANGE [$$44]  |PARTITIONED|
-              group by ([$$44 := $$38]) decor ([]) {
-                        aggregate [$$43] <- [agg-local-sql-sum($$35)]
+            -- HASH_PARTITION_EXCHANGE [$$46]  |PARTITIONED|
+              group by ([$$46 := $$40]) decor ([]) {
+                        aggregate [$$45] <- [agg-local-sql-sum($$37)]
                         -- AGGREGATE  |LOCAL|
                           nested tuple source
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                      }
-              -- SORT_GROUP_BY[$$38]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$40]  |PARTITIONED|
                 exchange
                 -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                  project ([$$35, $$38])
+                  project ([$$37, $$40])
                   -- STREAM_PROJECT  |PARTITIONED|
-                    assign [$$38, $$35] <- [substring($$e.getField(1), 0), $$e.getField(2)]
+                    assign [$$40, $$37] <- [substring($$e.getField(1), 0), $$e.getField(2)]
                     -- ASSIGN  |PARTITIONED|
                       project ([$$e])
                       -- STREAM_PROJECT  |PARTITIONED|
                         exchange
                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                          data-scan []<-[$$39, $$e] <- gby.Employee
+                          data-scan []<-[$$41, $$e] <- gby.Employee
                           -- DATASOURCE_SCAN  |PARTITIONED|
                             exchange
                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/flwor/order-by-13/order-by-13.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/flwor/order-by-13/order-by-13.3.adm
new file mode 100644
index 0000000..f82c642
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/flwor/order-by-13/order-by-13.3.adm
@@ -0,0 +1,4 @@
+{ "deptno_str": "1" }
+{ "deptno_str": "1" }
+{ "deptno_str": "2" }
+{ "deptno_str": "2" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/sugar-06-distinct/sugar-06-distinct.9.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/sugar-06-distinct/sugar-06-distinct.9.adm
new file mode 100644
index 0000000..4f19bae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/sugar-06-distinct/sugar-06-distinct.9.adm
@@ -0,0 +1,3 @@
+{ "deptno": 3, "salary_agg": 6000 }
+{ "deptno": 2, "salary_agg": 5000 }
+{ "deptno": 1, "salary_agg": 3000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast
index 4748a29..a11de35 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/flwor/at06/at06.3.ast
@@ -5,10 +5,7 @@
   (
     LiteralExpr [STRING] [partkey]
     :
-    FieldAccessor [
-      Variable [ Name=$gen0 ]
-      Field=partkey
-    ]
+    Variable [ Name=#1 ]
   )
   (
     LiteralExpr [STRING] [pid]
@@ -18,18 +15,12 @@
   (
     LiteralExpr [STRING] [shipdate]
     :
-    FieldAccessor [
-      Variable [ Name=$j ]
-      Field=l_shipdate
-    ]
+    Variable [ Name=#2 ]
   )
   (
     LiteralExpr [STRING] [orderkey]
     :
-    FieldAccessor [
-      Variable [ Name=$j ]
-      Field=l_orderkey
-    ]
+    Variable [ Name=#3 ]
   )
 ]
 ]
@@ -105,6 +96,24 @@
  AT
 Variable [ Name=$p ]
 ]
+Let Variable [ Name=#1 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$gen0 ]
+    Field=partkey
+  ]
+Let Variable [ Name=#2 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$j ]
+    Field=l_shipdate
+  ]
+Let Variable [ Name=#3 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$j ]
+    Field=l_orderkey
+  ]
 Where
   OperatorExpr [
     Variable [ Name=$p ]
@@ -112,19 +121,10 @@
     LiteralExpr [LONG] [4]
   ]
 Orderby
-  FieldAccessor [
-    Variable [ Name=$gen0 ]
-    Field=partkey
-  ]
+  Variable [ Name=#1 ]
   ASC
-  FieldAccessor [
-    Variable [ Name=$j ]
-    Field=l_shipdate
-  ]
+  Variable [ Name=#2 ]
   ASC
-  FieldAccessor [
-    Variable [ Name=$j ]
-    Field=l_orderkey
-  ]
+  Variable [ Name=#3 ]
   ASC
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.24.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.24.ast
index cdd8a53..92376cd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.24.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite-open/tinysocial-suite.24.ast
@@ -1,23 +1,11 @@
 DataverseUse TinySocial
 Query:
 SELECT [
-FieldAccessor [
-  Variable [ Name=$user ]
-  Field=name
-]
+Variable [ Name=#1 ]
 uname
-FieldAccessor [
-  Variable [ Name=$user ]
-  Field=alias
-]
+Variable [ Name=#2 ]
 alias
-FunctionCall TinySocial.substring@2[
-  FieldAccessor [
-    Variable [ Name=$message ]
-    Field=message
-  ]
-  LiteralExpr [LONG] [29]
-]
+Variable [ Name=#3 ]
 msg
 ]
 FROM [  FunctionCall asterix.dataset@1[
@@ -30,6 +18,27 @@
   ]
   AS Variable [ Name=$user ]
 ]
+Let Variable [ Name=#1 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$user ]
+    Field=name
+  ]
+Let Variable [ Name=#2 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$user ]
+    Field=alias
+  ]
+Let Variable [ Name=#3 ]
+  :=
+  FunctionCall TinySocial.substring@2[
+    FieldAccessor [
+      Variable [ Name=$message ]
+      Field=message
+    ]
+    LiteralExpr [LONG] [29]
+  ]
 Where
   OperatorExpr [
     OperatorExpr [
@@ -63,22 +72,10 @@
     ]
   ]
 Orderby
-  FieldAccessor [
-    Variable [ Name=$user ]
-    Field=name
-  ]
+  Variable [ Name=#1 ]
   ASC
-  FieldAccessor [
-    Variable [ Name=$user ]
-    Field=alias
-  ]
+  Variable [ Name=#2 ]
   ASC
-  FunctionCall TinySocial.substring@2[
-    FieldAccessor [
-      Variable [ Name=$message ]
-      Field=message
-    ]
-    LiteralExpr [LONG] [29]
-  ]
+  Variable [ Name=#3 ]
   ASC
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.24.ast b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.24.ast
index cdd8a53..92376cd 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.24.ast
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results_parser_sqlpp/tinysocial/tinysocial-suite/tinysocial-suite.24.ast
@@ -1,23 +1,11 @@
 DataverseUse TinySocial
 Query:
 SELECT [
-FieldAccessor [
-  Variable [ Name=$user ]
-  Field=name
-]
+Variable [ Name=#1 ]
 uname
-FieldAccessor [
-  Variable [ Name=$user ]
-  Field=alias
-]
+Variable [ Name=#2 ]
 alias
-FunctionCall TinySocial.substring@2[
-  FieldAccessor [
-    Variable [ Name=$message ]
-    Field=message
-  ]
-  LiteralExpr [LONG] [29]
-]
+Variable [ Name=#3 ]
 msg
 ]
 FROM [  FunctionCall asterix.dataset@1[
@@ -30,6 +18,27 @@
   ]
   AS Variable [ Name=$user ]
 ]
+Let Variable [ Name=#1 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$user ]
+    Field=name
+  ]
+Let Variable [ Name=#2 ]
+  :=
+  FieldAccessor [
+    Variable [ Name=$user ]
+    Field=alias
+  ]
+Let Variable [ Name=#3 ]
+  :=
+  FunctionCall TinySocial.substring@2[
+    FieldAccessor [
+      Variable [ Name=$message ]
+      Field=message
+    ]
+    LiteralExpr [LONG] [29]
+  ]
 Where
   OperatorExpr [
     OperatorExpr [
@@ -63,22 +72,10 @@
     ]
   ]
 Orderby
-  FieldAccessor [
-    Variable [ Name=$user ]
-    Field=name
-  ]
+  Variable [ Name=#1 ]
   ASC
-  FieldAccessor [
-    Variable [ Name=$user ]
-    Field=alias
-  ]
+  Variable [ Name=#2 ]
   ASC
-  FunctionCall TinySocial.substring@2[
-    FieldAccessor [
-      Variable [ Name=$message ]
-      Field=message
-    ]
-    LiteralExpr [LONG] [29]
-  ]
+  Variable [ Name=#3 ]
   ASC
 
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index faa621c..67bfb09 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -60,6 +60,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="flwor">
+      <compilation-unit name="order-by-13">
+        <output-dir compare="Text">order-by-13</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="flwor">
       <compilation-unit name="query-issue550">
         <output-dir compare="Text">query-issue550</output-dir>
       </compilation-unit>
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/context/Scope.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/context/Scope.java
index bf46c5d..fa47f5e 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/context/Scope.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/context/Scope.java
@@ -23,6 +23,7 @@
 import java.util.LinkedHashMap;
 import java.util.LinkedHashSet;
 import java.util.Set;
+import java.util.function.Predicate;
 
 import org.apache.asterix.common.functions.FunctionSignature;
 import org.apache.asterix.lang.common.expression.VariableExpr;
@@ -187,12 +188,20 @@
     }
 
     public Set<VariableExpr> getLiveVariables(Scope stopAtExclusive) {
+        return getLiveVariables(stopAtExclusive, null);
+    }
+
+    public Set<VariableExpr> getLiveVariables(Scope stopAtExclusive, Predicate<? super VarIdentifier> excludeFilter) {
         LinkedHashSet<VariableExpr> vars = new LinkedHashSet<>();
         Iterator<Identifier> identifierIterator = liveSymbols(stopAtExclusive);
         while (identifierIterator.hasNext()) {
             Identifier identifier = identifierIterator.next();
             if (identifier instanceof VarIdentifier) {
-                vars.add(new VariableExpr((VarIdentifier) identifier));
+                VarIdentifier varId = (VarIdentifier) identifier;
+                if (excludeFilter != null && excludeFilter.test(varId)) {
+                    continue;
+                }
+                vars.add(new VariableExpr(varId));
             }
         }
         return vars;
diff --git a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
index 4173563..b2fd3a0 100644
--- a/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
+++ b/asterixdb/asterix-lang-common/src/main/java/org/apache/asterix/lang/common/rewrites/LangRewritingContext.java
@@ -19,6 +19,9 @@
 package org.apache.asterix.lang.common.rewrites;
 
 import java.util.HashMap;
+import java.util.HashSet;
+import java.util.Map;
+import java.util.Set;
 
 import org.apache.asterix.lang.common.struct.VarIdentifier;
 import org.apache.hyracks.algebricks.core.algebra.base.Counter;
@@ -26,7 +29,8 @@
 public final class LangRewritingContext {
     private Counter varCounter;
     private int systemVarCounter = 1;
-    private HashMap<Integer, VarIdentifier> oldVarIdToNewVarId = new HashMap<>();
+    private Map<Integer, VarIdentifier> oldVarIdToNewVarId = new HashMap<>();
+    private Set<VarIdentifier> excludedForFieldAccessVars = new HashSet<>();
 
     public LangRewritingContext(int varCounter) {
         this.varCounter = new Counter(varCounter);
@@ -68,4 +72,11 @@
         return varCounter.get();
     }
 
+    public void addExcludedForFieldAccessVar(VarIdentifier varId) {
+        excludedForFieldAccessVars.add(varId);
+    }
+
+    public boolean isExcludedForFieldAccessVar(VarIdentifier varId) {
+        return excludedForFieldAccessVars.contains(varId);
+    }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/SelectBlock.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/SelectBlock.java
index f072fe4..83b03ba 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/SelectBlock.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/clause/SelectBlock.java
@@ -96,7 +96,7 @@
     }
 
     public boolean hasLetClauses() {
-        return letClauses != null && !letClauses.isEmpty();
+        return !letClauses.isEmpty();
     }
 
     public boolean hasWhereClause() {
@@ -108,7 +108,7 @@
     }
 
     public boolean hasLetClausesAfterGroupby() {
-        return letClausesAfterGby != null && !letClausesAfterGby.isEmpty();
+        return !letClausesAfterGby.isEmpty();
     }
 
     public List<LetClause> getLetListAfterGroupby() {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
index 0c0ebd6..fa4018a 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppFunctionBodyRewriter.java
@@ -37,9 +37,6 @@
         // Sets up parameters.
         setup(declaredFunctions, topStatement, metadataProvider, context, externalVars);
 
-        // Inlines column aliases.
-        inlineColumnAlias();
-
         // Generates column names.
         generateColumnNames();
 
@@ -52,6 +49,9 @@
         // Rewrites set operations.
         rewriteSetOperations();
 
+        // Inlines column aliases.
+        inlineColumnAlias();
+
         // Generate ids for variables (considering scopes) and replace global variable access with the dataset function.
         variableCheckAndRewrite();
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
index 41ff3e0..ec8b011 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/SqlppQueryRewriter.java
@@ -100,9 +100,6 @@
         // Sets up parameters.
         setup(declaredFunctions, topStatement, metadataProvider, context, externalVars);
 
-        // Inlines column aliases.
-        inlineColumnAlias();
-
         // Generates column names.
         generateColumnNames();
 
@@ -115,6 +112,9 @@
         // Rewrites set operations.
         rewriteSetOperations();
 
+        // Inlines column aliases.
+        inlineColumnAlias();
+
         // Generate ids for variables (considering scopes) and replace global variable access with the dataset function.
         variableCheckAndRewrite();
 
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/InlineColumnAliasVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/InlineColumnAliasVisitor.java
index 96f0542..a270530 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/InlineColumnAliasVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/InlineColumnAliasVisitor.java
@@ -18,16 +18,22 @@
  */
 package org.apache.asterix.lang.sqlpp.rewrites.visitor;
 
+import java.util.Collection;
 import java.util.Collections;
 import java.util.HashMap;
+import java.util.HashSet;
+import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
+import java.util.Set;
 
 import org.apache.asterix.common.exceptions.CompilationException;
+import org.apache.asterix.common.exceptions.ErrorCode;
 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.Literal;
+import org.apache.asterix.lang.common.clause.LetClause;
 import org.apache.asterix.lang.common.expression.FieldBinding;
 import org.apache.asterix.lang.common.expression.LiteralExpr;
 import org.apache.asterix.lang.common.expression.RecordConstructor;
@@ -43,11 +49,19 @@
 import org.apache.asterix.lang.sqlpp.util.SqlppVariableUtil;
 import org.apache.asterix.lang.sqlpp.visitor.SqlppSubstituteExpressionVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppExpressionScopingVisitor;
+import org.apache.hyracks.api.exceptions.SourceLocation;
 
 /**
- * Syntactic sugar rewriting: inlines column aliases defines in SELECT clause into ORDER BY and LIMIT clauses. <br/>
- * Note: column aliases are not cosidered new variables, but they can be referenced from ORDER BY and LIMIT clauses
- *       because of this rewriting (like in SQL)
+ * Syntactic sugar rewriting: inlines column aliases definitions in SELECT clause into ORDER BY and LIMIT clauses.
+ * <br/>
+ * Notes
+ * <ul>
+ * <li> column aliases are not considered new variables, but they can be referenced from ORDER BY and LIMIT clauses
+ *      because of this rewriting (like in SQL) </li>
+ * <li> if a column alias expression is not a variable or a literal then we introduce a new let clause and replace
+ *      that column expression with the let variable reference. The optimizer will then decide whether to inline that
+ *      expression or not </li>
+ * </ul>
  */
 public class InlineColumnAliasVisitor extends AbstractSqlppExpressionScopingVisitor {
 
@@ -58,44 +72,52 @@
     @Override
     public Expression visit(SelectBlock selectBlock, ILangExpression arg) throws CompilationException {
         // Gets the map from select clause.
-        Map<Expression, Expression> map = getMap(selectBlock.getSelectClause());
+        Map<Expression, ColumnAliasBinding> map = getMap(selectBlock.getSelectClause());
 
         // Removes all FROM/LET binding variables
-        if (selectBlock.hasFromClause()) {
-            map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getFromClause()));
+        if (selectBlock.hasGroupbyClause()) {
+            map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getGroupbyClause()));
+            if (selectBlock.hasLetClausesAfterGroupby()) {
+                map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getLetListAfterGroupby()));
+            }
+        } else {
+            if (selectBlock.hasFromClause()) {
+                map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getFromClause()));
+            }
+            if (selectBlock.hasLetClauses()) {
+                map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getLetList()));
+            }
         }
-        if (selectBlock.hasLetClauses()) {
-            map.keySet().removeAll(SqlppVariableUtil.getBindingVariables(selectBlock.getLetList()));
-        }
-
-        // Creates a substitution visitor.
-        SqlppSubstituteExpressionVisitor visitor = new SubstituteColumnAliasVisitor(context, map);
 
         SelectExpression selectExpression = (SelectExpression) arg;
-
         // For SET operation queries, column aliases will not substitute ORDER BY nor LIMIT expressions.
         if (!selectExpression.getSelectSetOperation().hasRightInputs()) {
+            // Creates a substitution visitor.
+            SubstituteColumnAliasVisitor visitor = new SubstituteColumnAliasVisitor(context, toExpressionMap(map));
             if (selectExpression.hasOrderby()) {
                 selectExpression.getOrderbyClause().accept(visitor, arg);
             }
             if (selectExpression.hasLimit()) {
                 selectExpression.getLimitClause().accept(visitor, arg);
             }
+            if (!visitor.letVarMap.isEmpty()) {
+                introduceLetClauses(visitor.letVarMap, map, selectBlock);
+            }
         }
         return super.visit(selectBlock, arg);
     }
 
-    private Map<Expression, Expression> getMap(SelectClause selectClause) throws CompilationException {
+    private Map<Expression, ColumnAliasBinding> getMap(SelectClause selectClause) {
         if (selectClause.selectElement()) {
             return getMap(selectClause.getSelectElement());
         }
         if (selectClause.selectRegular()) {
             return getMap(selectClause.getSelectRegular());
         }
-        return null;
+        return Collections.emptyMap();
     }
 
-    private Map<Expression, Expression> getMap(SelectElement selectElement) {
+    private Map<Expression, ColumnAliasBinding> getMap(SelectElement selectElement) {
         Expression expr = selectElement.getExpression();
         if (expr.getKind() == Kind.RECORD_CONSTRUCTOR_EXPRESSION) {
             // Rewrite top-level field names (aliases), in order to be consistent with SelectRegular.
@@ -104,12 +126,12 @@
         return Collections.emptyMap();
     }
 
-    private Map<Expression, Expression> getMap(SelectRegular selectRegular) {
+    private Map<Expression, ColumnAliasBinding> getMap(SelectRegular selectRegular) {
         return mapProjections(selectRegular.getProjections());
     }
 
-    private Map<Expression, Expression> mapRecordConstructor(RecordConstructor rc) {
-        Map<Expression, Expression> exprMap = new HashMap<>();
+    private Map<Expression, ColumnAliasBinding> mapRecordConstructor(RecordConstructor rc) {
+        Map<Expression, ColumnAliasBinding> exprMap = new HashMap<>();
         for (FieldBinding binding : rc.getFbList()) {
             Expression leftExpr = binding.getLeftExpr();
             // We only need to deal with the case that the left expression (for a field name) is
@@ -121,30 +143,105 @@
             LiteralExpr literalExpr = (LiteralExpr) leftExpr;
             if (literalExpr.getValue().getLiteralType() == Literal.Type.STRING) {
                 String fieldName = SqlppVariableUtil.toInternalVariableName(literalExpr.getValue().getStringValue());
-                exprMap.put(new VariableExpr(new VarIdentifier(fieldName)), binding.getRightExpr());
+                exprMap.put(new VariableExpr(new VarIdentifier(fieldName)), ColumnAliasBinding.of(binding));
             }
         }
         return exprMap;
     }
 
-    private Map<Expression, Expression> mapProjections(List<Projection> projections) {
-        Map<Expression, Expression> exprMap = new HashMap<>();
+    private Map<Expression, ColumnAliasBinding> mapProjections(List<Projection> projections) {
+        Map<Expression, ColumnAliasBinding> exprMap = new HashMap<>();
         for (Projection projection : projections) {
             if (!projection.star() && !projection.varStar()) {
-                exprMap.put(
-                        new VariableExpr(
-                                new VarIdentifier(SqlppVariableUtil.toInternalVariableName(projection.getName()))),
-                        projection.getExpression());
+                String varName = SqlppVariableUtil.toInternalVariableName(projection.getName());
+                exprMap.put(new VariableExpr(new VarIdentifier(varName)), ColumnAliasBinding.of(projection));
             }
         }
         return exprMap;
     }
 
+    private void introduceLetClauses(Map<Expression, VarIdentifier> letVarMap,
+            Map<Expression, ColumnAliasBinding> aliasBindingMap, SelectBlock selectBlock) throws CompilationException {
+
+        List<LetClause> targetLetClauses =
+                selectBlock.hasGroupbyClause() ? selectBlock.getLetListAfterGroupby() : selectBlock.getLetList();
+
+        for (Map.Entry<Expression, VarIdentifier> me : letVarMap.entrySet()) {
+            Expression columnAliasVarExpr = me.getKey();
+            SourceLocation sourceLoc = columnAliasVarExpr.getSourceLocation();
+            ColumnAliasBinding columnAliasBinding = aliasBindingMap.get(columnAliasVarExpr);
+            if (columnAliasBinding == null) {
+                throw new CompilationException(ErrorCode.COMPILATION_ILLEGAL_STATE, sourceLoc);
+            }
+            VarIdentifier letVarId = me.getValue();
+
+            // add a let clause defining the new variable
+            VariableExpr letVarDefExpr = new VariableExpr(letVarId);
+            letVarDefExpr.setSourceLocation(sourceLoc);
+            LetClause newLetClause = new LetClause(letVarDefExpr, columnAliasBinding.getExpression());
+            newLetClause.setSourceLocation(sourceLoc);
+            targetLetClauses.add(newLetClause);
+
+            // replace original column alias expression with variable reference
+            VariableExpr letVarRefExpr = new VariableExpr(letVarId);
+            letVarRefExpr.setSourceLocation(sourceLoc);
+            columnAliasBinding.setExpression(letVarRefExpr);
+
+            context.addExcludedForFieldAccessVar(letVarId);
+        }
+    }
+
+    private static Map<Expression, Expression> toExpressionMap(Map<Expression, ColumnAliasBinding> bindingMap) {
+        Map<Expression, Expression> exprMap = new HashMap<>();
+        for (Map.Entry<Expression, ColumnAliasBinding> me : bindingMap.entrySet()) {
+            exprMap.put(me.getKey(), me.getValue().getExpression());
+        }
+        return exprMap;
+    }
+
+    private abstract static class ColumnAliasBinding {
+
+        abstract Expression getExpression();
+
+        abstract void setExpression(Expression expr);
+
+        static ColumnAliasBinding of(FieldBinding fieldBinding) {
+            return new ColumnAliasBinding() {
+                @Override
+                Expression getExpression() {
+                    return fieldBinding.getRightExpr();
+                }
+
+                @Override
+                void setExpression(Expression expr) {
+                    fieldBinding.setRightExpr(expr);
+                }
+            };
+        }
+
+        static ColumnAliasBinding of(Projection projection) {
+            return new ColumnAliasBinding() {
+                @Override
+                Expression getExpression() {
+                    return projection.getExpression();
+                }
+
+                @Override
+                void setExpression(Expression expr) {
+                    projection.setExpression(expr);
+                }
+            };
+        }
+    }
+
     /**
      * Dataset access functions have not yet been introduced at this point, so we need to perform substitution
      * on postVisit() to avoid infinite recursion in case of SELECT (SELECT ... FROM dataset_name) AS dataset_name.
      */
-    private class SubstituteColumnAliasVisitor extends SqlppSubstituteExpressionVisitor {
+    private static class SubstituteColumnAliasVisitor extends SqlppSubstituteExpressionVisitor {
+
+        private final Map<Expression, VarIdentifier> letVarMap = new LinkedHashMap<>();
+
         private SubstituteColumnAliasVisitor(LangRewritingContext context, Map<Expression, Expression> exprMap) {
             super(context, exprMap);
         }
@@ -158,5 +255,30 @@
         protected Expression postVisit(Expression expr) throws CompilationException {
             return substitute(expr);
         }
+
+        @Override
+        protected Expression getMappedExpr(Expression expr) throws CompilationException {
+            Expression mappedExpr = super.getMappedExpr(expr);
+            if (mappedExpr == null) {
+                return null;
+            }
+            switch (mappedExpr.getKind()) {
+                case LITERAL_EXPRESSION:
+                case VARIABLE_EXPRESSION:
+                    return mappedExpr;
+                default:
+                    // all other kinds of expressions must be moved out of column alias definitions into separate
+                    // let clauses, so we need to return a variable reference expression here and
+                    // create a new let variable if we're replacing given expression for the first time
+                    VarIdentifier var = letVarMap.get(expr);
+                    if (var == null) {
+                        var = context.newVariable();
+                        letVarMap.put(expr, var);
+                    }
+                    VariableExpr varExpr = new VariableExpr(var);
+                    varExpr.setSourceLocation(expr.getSourceLocation());
+                    return varExpr;
+            }
+        }
     }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SubstituteGroupbyExpressionWithVariableVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SubstituteGroupbyExpressionWithVariableVisitor.java
index f157f4f..9e937d0 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SubstituteGroupbyExpressionWithVariableVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SubstituteGroupbyExpressionWithVariableVisitor.java
@@ -37,8 +37,13 @@
 import org.apache.asterix.lang.sqlpp.visitor.SqlppSubstituteExpressionVisitor;
 import org.apache.asterix.lang.sqlpp.visitor.base.AbstractSqlppExpressionScopingVisitor;
 
-// Replaces expressions that appear in having/select/order-by/limit clause and are identical to some
-// group by key expression with the group by key expression.
+/**
+ * <ul>
+ * <li> Generates group by key variables if they were not specified in the query </li>
+ * <li> Replaces expressions that appear in having/select/order-by/limit clause and are identical to some
+ *      group by key expression with the group by key variable </li>
+ * </ul>
+ */
 public class SubstituteGroupbyExpressionWithVariableVisitor extends AbstractSqlppExpressionScopingVisitor {
 
     public SubstituteGroupbyExpressionWithVariableVisitor(LangRewritingContext context) {
@@ -62,7 +67,7 @@
             // Rewrites LET/HAVING/SELECT clauses.
             if (selectBlock.hasLetClausesAfterGroupby()) {
                 for (LetClause letClause : selectBlock.getLetListAfterGroupby()) {
-                    letClause.accept(this, arg);
+                    letClause.accept(visitor, arg);
                 }
             }
             if (selectBlock.hasHavingClause()) {
@@ -84,21 +89,20 @@
         return super.visit(selectBlock, arg);
     }
 
-}
+    private static class SubstituteGroupbyExpressionVisitor extends SqlppSubstituteExpressionVisitor {
 
-class SubstituteGroupbyExpressionVisitor extends SqlppSubstituteExpressionVisitor {
+        private SubstituteGroupbyExpressionVisitor(LangRewritingContext context, Map<Expression, Expression> exprMap) {
+            super(context, exprMap);
+        }
 
-    public SubstituteGroupbyExpressionVisitor(LangRewritingContext context, Map<Expression, Expression> exprMap) {
-        super(context, exprMap);
-    }
-
-    @Override
-    public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
-        FunctionSignature signature = callExpr.getFunctionSignature();
-        if (FunctionMapUtil.isSql92AggregateFunction(signature)) {
-            return callExpr;
-        } else {
-            return super.visit(callExpr, arg);
+        @Override
+        public Expression visit(CallExpr callExpr, ILangExpression arg) throws CompilationException {
+            FunctionSignature signature = callExpr.getFunctionSignature();
+            if (FunctionMapUtil.isSql92AggregateFunction(signature)) {
+                return callExpr;
+            } else {
+                return super.visit(callExpr, arg);
+            }
         }
     }
 }
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
index b5fd996..8bac4ad 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/VariableCheckAndRewriteVisitor.java
@@ -118,7 +118,8 @@
             return resolveAsDataset(dataverseName, datasetName, sourceLoc);
         }
 
-        Set<VariableExpr> localVars = scopeChecker.getCurrentScope().getLiveVariables(scopeChecker.getPrecedingScope());
+        Set<VariableExpr> localVars = scopeChecker.getCurrentScope().getLiveVariables(scopeChecker.getPrecedingScope(),
+                context::isExcludedForFieldAccessVar);
         switch (localVars.size()) {
             case 0:
                 return resolveAsDataset(dataverseName, datasetName, sourceLoc);
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteExpressionVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteExpressionVisitor.java
index 6db1376..636f8a6 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteExpressionVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/visitor/SqlppSubstituteExpressionVisitor.java
@@ -54,9 +54,15 @@
     }
 
     protected Expression substitute(Expression expr) throws CompilationException {
+        Expression mappedExpr = getMappedExpr(expr);
+        // Makes a deep copy before returning to avoid shared references.
+        return mappedExpr == null ? expr : (Expression) SqlppRewriteUtil.deepCopy(mappedExpr);
+    }
+
+    protected Expression getMappedExpr(Expression expr) throws CompilationException {
         Expression mappedExpr = exprMap.get(expr);
         if (mappedExpr == null) {
-            return expr;
+            return null;
         }
         Collection<VariableExpr> freeVars = SqlppVariableUtil.getFreeVariables(expr);
         for (VariableExpr freeVar : freeVars) {
@@ -64,10 +70,9 @@
             if (currentScope.findSymbol(freeVar.getVar().getValue()) != null) {
                 // If the expression to be substituted uses variables defined in the outer-most expresion
                 // that is being visited, we shouldn't perform the substitution.
-                return expr;
+                return null;
             }
         }
-        // Makes a deep copy before returning to avoid shared references.
-        return (Expression) SqlppRewriteUtil.deepCopy(mappedExpr);
+        return mappedExpr;
     }
 }