ASTERIXDB-1127: regression tests.

Change-Id: Ie2d14acd87269ed2f8c28350d13f5510c68058ab
Reviewed-on: https://asterix-gerrit.ics.uci.edu/692
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Pouria Pirzadeh <pouria.pirzadeh@gmail.com>
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1127.aql b/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1127.aql
new file mode 100644
index 0000000..f957889
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-ASTERIXDB-1127.aql
@@ -0,0 +1,89 @@
+/*
+ * 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 tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as
+{ l_orderkey: int64, l_partkey: int64, l_suppkey: int64, l_linenumber: int32, l_quantity: int32, l_extendedprice: double, l_discount: double, l_tax: double, l_returnflag: string, l_linestatus: string, l_shipdate: string, l_commitdate: string, l_receiptdate: string, l_shipinstruct: string, l_shipmode: string, l_comment: string }
+
+create type OrderType as
+{ o_orderkey: int64, o_custkey: int64, o_orderstatus: string, o_totalprice: double, o_orderdate: string, o_orderpriority: string, o_clerk: string, o_shippriority: int32, o_comment: string }
+
+create type CustomerType as
+{ c_custkey: int64, c_name: string, c_address: string, c_nationkey: int32, c_phone: string, c_acctbal: double, c_mktsegment: string, c_comment: string }
+
+create type PartType as
+{ p_partkey: int64, p_name: string, p_mfgr: string, p_brand: string, p_type: string, p_size: int32, p_container: string, p_retailprice: double, p_comment: string }
+
+create type PartSuppType as
+{ ps_partkey: int64, ps_suppkey: int64, ps_availqty: int32, ps_supplycost: double, ps_comment: string }
+
+create type SupplierType as
+{ s_suppkey: int64, s_name: string, s_address: string, s_nationkey: int32, s_phone: string, s_acctbal: double, s_comment: string }
+
+create type NationType as
+{ n_nationkey: int32, n_name: string, n_regionkey: int32, n_comment: string }
+
+create type RegionType as
+{ r_regionkey: int32, r_name: string, r_comment: string }
+
+create dataset LineItem(LineItemType) primary key l_orderkey, l_linenumber;
+create dataset Orders(OrderType) primary key o_orderkey;
+create dataset Customer(CustomerType) primary key c_custkey;
+create dataset Part(PartType) primary key p_partkey;
+create dataset Partsupp(PartSuppType) primary key ps_partkey, ps_suppkey;
+create dataset Supplier(SupplierType) primary key s_suppkey;
+create dataset Region(RegionType) primary key r_regionkey;
+create dataset Nation(NationType) primary key n_nationkey;
+
+
+create index nation_fk_region on Nation(n_regionkey);
+create index supplier_fk_nation on Supplier (s_nationkey);
+create index partsupp_fk_part on Partsupp (ps_partkey);
+create index partsupp_fk_supplier on Partsupp (ps_suppkey);
+create index customer_fk_nation on Customer (c_nationkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+
+
+
+for $c in dataset('Customer')
+for $o in dataset('Orders')
+    where $c.c_mktsegment = 'BUILDING' and $c.c_custkey = $o.o_custkey
+    for $l in dataset('LineItem')
+    where $l.l_orderkey = $o.o_orderkey and $o.o_orderdate < '1995-03-15' and $l.l_shipdate > '1995-03-15'
+/*+ hash */
+group by $l_orderkey := $l.l_orderkey, $o_orderdate := $o.o_orderdate, $o_shippriority := $o.o_shippriority
+with $l
+let $revenue := sum (
+    for $i in $l
+    return $i.l_extendedprice * (1 - $i.l_discount)
+)
+order by $revenue desc, $o_orderdate
+limit 10
+return
+{ "l_orderkey": $l_orderkey, "revenue": $revenue, "o_orderdate": $o_orderdate, "o_shippriority": $o_shippriority }
+
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1127.plan b/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1127.plan
new file mode 100644
index 0000000..79707eb
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-ASTERIXDB-1127.plan
@@ -0,0 +1,74 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- STREAM_LIMIT  |UNPARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$49(DESC), $$4(ASC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 10] [$$49(DESC), $$4(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- EXTERNAL_GROUP_BY[$$55, $$56, $$57]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- HASH_PARTITION_EXCHANGE [$$55, $$56, $$57]  |PARTITIONED|
+                        -- EXTERNAL_GROUP_BY[$$44, $$41, $$39]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$43][$$44]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$43]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$42][$$47]  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$47]  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- BTREE_SEARCH  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STABLE_SORT [$$60(ASC)]  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- BTREE_SEARCH  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- SPLIT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$44]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- STREAM_SELECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- BTREE_SEARCH  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STABLE_SORT [$$63(ASC), $$64(ASC)]  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- BTREE_SEARCH  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- SPLIT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue827-2.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue827-2.plan
index 0d68333..d9826e5 100644
--- a/asterix-app/src/test/resources/optimizerts/results/query-issue827-2.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue827-2.plan
@@ -84,24 +84,23 @@
                       -- AGGREGATE  |UNPARTITIONED|
                         -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
                           -- AGGREGATE  |PARTITIONED|
-                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                              -- STREAM_PROJECT  |PARTITIONED|
-                                -- ASSIGN  |PARTITIONED|
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- SPLIT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- STREAM_PROJECT  |PARTITIONED|
-                                          -- ASSIGN  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                  -- SPLIT  |PARTITIONED|
-                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- DATASOURCE_SCAN  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- SPLIT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- SPLIT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
                 -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
                   -- AGGREGATE  |UNPARTITIONED|
                     -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
@@ -122,14 +121,13 @@
               -- AGGREGATE  |UNPARTITIONED|
                 -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
                   -- AGGREGATE  |PARTITIONED|
-                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- ASSIGN  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- SPLIT  |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|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- SPLIT  |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/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029.plan b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029.plan
index 2e66169..5964fbd 100644
--- a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029.plan
@@ -62,13 +62,14 @@
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- BTREE_SEARCH  |PARTITIONED|
-                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- ASSIGN  |PARTITIONED|
-                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- SPLIT  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- ASSIGN  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- SPLIT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
               -- AGGREGATE  |UNPARTITIONED|
                 -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029_2.plan b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029_2.plan
index 2e66169..5964fbd 100644
--- a/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029_2.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/udfs/query-ASTERIXDB-1029_2.plan
@@ -62,13 +62,14 @@
                                                           -- STREAM_PROJECT  |PARTITIONED|
                                                             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                                               -- BTREE_SEARCH  |PARTITIONED|
-                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                  -- ASSIGN  |PARTITIONED|
-                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- SPLIT  |PARTITIONED|
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- ASSIGN  |PARTITIONED|
-                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- SPLIT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
             -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
               -- AGGREGATE  |UNPARTITIONED|
                 -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.ddl.aql
new file mode 100644
index 0000000..d6d80bf
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.ddl.aql
@@ -0,0 +1,70 @@
+/*
+ * 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 tpch if exists;
+create dataverse tpch;
+use dataverse tpch;
+
+create type LineItemType as
+{ l_orderkey: int64, l_partkey: int64, l_suppkey: int64, l_linenumber: int32, l_quantity: int32, l_extendedprice: double, l_discount: double, l_tax: double, l_returnflag: string, l_linestatus: string, l_shipdate: string, l_commitdate: string, l_receiptdate: string, l_shipinstruct: string, l_shipmode: string, l_comment: string }
+
+create type OrderType as
+{ o_orderkey: int64, o_custkey: int64, o_orderstatus: string, o_totalprice: double, o_orderdate: string, o_orderpriority: string, o_clerk: string, o_shippriority: int32, o_comment: string }
+
+create type CustomerType as
+{ c_custkey: int64, c_name: string, c_address: string, c_nationkey: int32, c_phone: string, c_acctbal: double, c_mktsegment: string, c_comment: string }
+
+create type PartType as
+{ p_partkey: int64, p_name: string, p_mfgr: string, p_brand: string, p_type: string, p_size: int32, p_container: string, p_retailprice: double, p_comment: string }
+
+create type PartSuppType as
+{ ps_partkey: int64, ps_suppkey: int64, ps_availqty: int32, ps_supplycost: double, ps_comment: string }
+
+create type SupplierType as
+{ s_suppkey: int64, s_name: string, s_address: string, s_nationkey: int32, s_phone: string, s_acctbal: double, s_comment: string }
+
+create type NationType as
+{ n_nationkey: int32, n_name: string, n_regionkey: int32, n_comment: string }
+
+create type RegionType as
+{ r_regionkey: int32, r_name: string, r_comment: string }
+
+create dataset LineItem(LineItemType) primary key l_orderkey, l_linenumber;
+create dataset Orders(OrderType) primary key o_orderkey;
+create dataset Customer(CustomerType) primary key c_custkey;
+create dataset Part(PartType) primary key p_partkey;
+create dataset Partsupp(PartSuppType) primary key ps_partkey, ps_suppkey;
+create dataset Supplier(SupplierType) primary key s_suppkey;
+create dataset Region(RegionType) primary key r_regionkey;
+create dataset Nation(NationType) primary key n_nationkey;
+
+create index nation_fk_region on Nation(n_regionkey);
+create index supplier_fk_nation on Supplier (s_nationkey);
+create index partsupp_fk_part on Partsupp (ps_partkey);
+create index partsupp_fk_supplier on Partsupp (ps_suppkey);
+create index customer_fk_nation on Customer (c_nationkey);
+create index orders_fk_customer on Orders (o_custkey);
+create index lineitem_fk_orders on LineItem (l_orderkey);
+create index lineitem_fk_part on LineItem (l_partkey);
+create index lineitem_fk_supplier on LineItem (l_suppkey);
+create index orders_orderdateIx on Orders (o_orderdate);
+create index lineitem_shipdateIx on LineItem (l_shipdate);
+create index lineitem_receiptdateIx on LineItem (l_receiptdate);
+
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.2.update.aql
new file mode 100644
index 0000000..ee1ab58
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.2.update.aql
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+use dataverse tpch;
+
+
+load dataset LineItem
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Orders
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Supplier
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Region
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Nation
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Part
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Partsupp
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Customer
+using "org.apache.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="asterix_nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.3.query.aql
new file mode 100644
index 0000000..2c48a49
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.3.query.aql
@@ -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 dataverse tpch;
+
+for $c in dataset('Customer')
+for $o in dataset('Orders')
+    where $c.c_mktsegment = 'BUILDING' and $c.c_custkey = $o.o_custkey
+    for $l in dataset('LineItem')
+    where $l.l_orderkey = $o.o_orderkey and $o.o_orderdate < '1995-03-15' and $l.l_shipdate > '1995-03-15'
+/*+ hash */
+group by $l_orderkey := $l.l_orderkey, $o_orderdate := $o.o_orderdate, $o_shippriority := $o.o_shippriority
+with $l
+let $revenue := sum (
+    for $i in $l
+    return $i.l_extendedprice * (1 - $i.l_discount)
+)
+order by $revenue desc, $o_orderdate
+limit 10
+return
+{ "l_orderkey": $l_orderkey, "revenue": $revenue, "o_orderdate": $o_orderdate, "o_shippriority": $o_shippriority }
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.adm
new file mode 100644
index 0000000..6407653
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-ASTERIXDB-1127/query-ASTERIXDB-1127.1.adm
@@ -0,0 +1,8 @@
+{ "l_orderkey": 1637, "revenue": 164224.9253d, "o_orderdate": "1995-02-08", "o_shippriority": 0i32 }
+{ "l_orderkey": 5191, "revenue": 49378.309400000006d, "o_orderdate": "1994-12-11", "o_shippriority": 0i32 }
+{ "l_orderkey": 742, "revenue": 43728.048d, "o_orderdate": "1994-12-23", "o_shippriority": 0i32 }
+{ "l_orderkey": 3492, "revenue": 43716.072400000005d, "o_orderdate": "1994-11-24", "o_shippriority": 0i32 }
+{ "l_orderkey": 2883, "revenue": 36666.9612d, "o_orderdate": "1995-01-23", "o_shippriority": 0i32 }
+{ "l_orderkey": 998, "revenue": 11785.548600000002d, "o_orderdate": "1994-11-26", "o_shippriority": 0i32 }
+{ "l_orderkey": 3430, "revenue": 4726.6775d, "o_orderdate": "1994-12-12", "o_shippriority": 0i32 }
+{ "l_orderkey": 4423, "revenue": 3055.9365d, "o_orderdate": "1995-02-17", "o_shippriority": 0i32 }
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index adcb8cd..67db6af 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -5591,6 +5591,11 @@
                 <output-dir compare="Text">query-issue827-2</output-dir>
             </compilation-unit>
         </test-case>
+        <test-case FilePath="tpch">
+            <compilation-unit name="query-ASTERIXDB-1127">
+                <output-dir compare="Text">query-ASTERIXDB-1127</output-dir>
+            </compilation-unit>
+        </test-case>
     </test-group>
     <test-group name="tpch-sql-like">
         <test-case FilePath="tpch-sql-like">