diff --git a/asterixdb/asterix-app/data/tpcds/customer.csv b/asterixdb/asterix-app/data/tpcds/customer.csv
new file mode 100644
index 0000000..c7ee8ef
--- /dev/null
+++ b/asterixdb/asterix-app/data/tpcds/customer.csv
@@ -0,0 +1,10 @@
+1|AAAAAAAABAAAAAAA|980124|7135|13513|2452238|2452208|Mr.|Javier|Lewis|Y|9|12|1936|CHILE||Javier.Lewis@VFAxlnZEvOx.org|2452508|
+2|AAAAAAAACAAAAAAA|819667|1461|13514|2452318|2452288|Dr.|Amy|Moses|Y|9|4|1966|TOGO||Amy.Moses@Ovk9KjHH.com|2452318|
+3|AAAAAAAADAAAAAAA|1473522|6247|13515|2449130|2449100|Miss|Latisha|Hamilton|N|18|9|1979|NIUE||Latisha.Hamilton@V.com|2452313|
+4|AAAAAAAAEAAAAAAA|1703214|3986|13516|2450030|2450000|Dr.|Michael|White|N|7|6|1983|MEXICO||Michael.White@i.org|2452361|
+5|AAAAAAAAFAAAAAAA|953372|4470|13517|2449438|2449408|Sir|Robert|Moran|N|8|5|1956|FIJI||Robert.Moran@Hh.edu|2452469|
+6|AAAAAAAAGAAAAAAA|213219|6374|13518|2451883|2451853|Ms.|Brunilda|Sharp|N|4|12|1925|SURINAME||Brunilda.Sharp@T3pylZEUQjm.org|2452430|
+7|AAAAAAAAHAAAAAAA|68377|3219|13519|2451438|2451408|Ms.|Fonda|Wiles|Y|24|4|1985|GAMBIA||Fonda.Wiles@S9KnyEtz9hv.org|2452360|
+8|AAAAAAAAIAAAAAAA|1215897|2471|13520|2449406|2449376|Sir|Ollie|Shipman|N|26|12|1938|KOREA, REPUBLIC OF||Ollie.Shipman@be.org|2452334|
+9|AAAAAAAAJAAAAAAA|1168667|1404|13521|2452275|2452245|Sir|Karl|Gilbert|N|26|10|1966|MONTSERRAT||Karl.Gilbert@Crg5KyP2IxX9C4d6.edu|2452454|
+10|AAAAAAAAKAAAAAAA|1207553|5143|13522|2451353|2451323|Ms.|Albert|Brunson|N|15|10|1973|JORDAN||Albert.Brunson@62.com|2452641|
diff --git a/asterixdb/asterix-app/data/tpcds/customer_address.csv b/asterixdb/asterix-app/data/tpcds/customer_address.csv
new file mode 100644
index 0000000..a52166e
--- /dev/null
+++ b/asterixdb/asterix-app/data/tpcds/customer_address.csv
@@ -0,0 +1,10 @@
+13513|AAAAAAAAJMEDAAAA|545|Meadow |RD|Suite X|Crystal|DeKalb County|MO|65258|United States|-6|condo|
+13514|AAAAAAAAKMEDAAAA|730|Hickory Wilson|Street|Suite 340|Friendship|Hockley County|TX|74536|United States|-6|single family|
+13515|AAAAAAAALMEDAAAA||Lincoln Wilson||||Highlands County|FL|35124||||
+13516|AAAAAAAAMMEDAAAA|114|Wilson |Lane|Suite J|New Hope|Knox County|KY|49431|United States|-5|condo|
+13517|AAAAAAAANMEDAAAA|177|8th Walnut|Ct.|Suite 300|Riverview|Jenkins County|GA|39003|United States|-5|single family|
+13518|AAAAAAAAOMEDAAAA|11|Adams 1st|Cir.|Suite T|Franklin|Essex County|NY|19101|United States|-5|apartment|
+13519|AAAAAAAAPMEDAAAA|261|4th River|ST|Suite T|Green Acres|Sheboygan County|WI|57683|United States|-6|condo|
+13520|AAAAAAAAANEDAAAA|892|Johnson |Pkwy|Suite 320|Fairfield|Mora County|NM|86192|United States|-7|single family|
+13521|AAAAAAAABNEDAAAA|579|Elm |Road|Suite 480|Clifton|Wichita County|KS|68014|United States|-6|single family|
+13522|AAAAAAAACNEDAAAA|139|5th |Wy|Suite P|Springdale|Koochiching County|MN|58883|United States|-6|condo|
diff --git a/asterixdb/asterix-app/data/tpcds/date_dim.csv b/asterixdb/asterix-app/data/tpcds/date_dim.csv
new file mode 100644
index 0000000..3aa8a8f
--- /dev/null
+++ b/asterixdb/asterix-app/data/tpcds/date_dim.csv
@@ -0,0 +1,14 @@
+2415022|AAAAAAAAOKJNECAA|1900-01-02|0|1|1|1900|1|1|2|1|1900|1|1|Monday|1900Q1|N|N|Y|2415021|2415020|2414657|2414930|N|N|N|N|N|
+2415023|AAAAAAAAPKJNECAA|1900-01-03|0|1|1|1900|2|1|3|1|1900|1|1|Tuesday|1900Q1|N|N|N|2415021|2415020|2414658|2414931|N|N|N|N|N|
+2415024|AAAAAAAAALJNECAA|1900-01-04|0|1|1|1900|3|1|4|1|1900|1|1|Wednesday|1900Q1|N|N|N|2415021|2415020|2414659|2414932|N|N|N|N|N|
+2415025|AAAAAAAABLJNECAA|1900-01-05|0|1|1|1900|4|1|5|1|1900|1|1|Thursday|1900Q1|N|N|N|2415021|2415020|2414660|2414933|N|N|N|N|N|
+2415026|AAAAAAAACLJNECAA|1900-01-06|0|1|1|1900|5|1|6|1|1900|1|1|Friday|1900Q1|N|Y|N|2415021|2415020|2414661|2414934|N|N|N|N|N|
+2415027|AAAAAAAADLJNECAA|1900-01-07|0|1|1|1900|6|1|7|1|1900|1|1|Saturday|1900Q1|N|Y|N|2415021|2415020|2414662|2414935|N|N|N|N|N|
+2415028|AAAAAAAAELJNECAA|1900-01-08|0|1|1|1900|0|1|8|1|1900|1|1|Sunday|1900Q1|N|N|N|2415021|2415020|2414663|2414936|N|N|N|N|N|
+2415029|AAAAAAAAFLJNECAA|1900-01-09|0|2|1|1900|1|1|9|1|1900|1|2|Monday|1900Q1|N|N|N|2415021|2415020|2414664|2414937|N|N|N|N|N|
+2415030|AAAAAAAAGLJNECAA|1900-01-10|0|2|1|1900|2|1|10|1|1900|1|2|Tuesday|1900Q1|N|N|N|2415021|2415020|2414665|2414938|N|N|N|N|N|
+2415031|AAAAAAAAHLJNECAA|1900-01-11|0|2|1|1900|3|1|11|1|1900|1|2|Wednesday|1900Q1|N|N|N|2415021|2415020|2414666|2414939|N|N|N|N|N|
+2415032|AAAAAAAAILJNECAA|1900-01-12|0|2|1|1900|4|1|12|1|1900|1|2|Thursday|1900Q1|N|N|N|2415021|2415020|2414667|2414940|N|N|N|N|N|
+2415033|AAAAAAAAJLJNECAA|1900-01-13|0|2|1|1900|5|1|13|1|1900|1|2|Friday|1900Q1|N|Y|N|2415021|2415020|2414668|2414941|N|N|N|N|N|
+2451293|AAAAAAAAKLJNECAA|1900-01-14|0|2|1|1900|6|1|14|1|1900|1|2|Saturday|1900Q1|N|Y|N|2415021|2415020|2414669|2414942|N|N|N|N|N|
+2451176|AAAAAAAALLJNECAA|1900-01-15|0|2|1|1900|0|1|15|1|1900|1|2|Sunday|1900Q1|N|N|N|2415021|2415020|2414670|2414943|N|N|N|N|N|
diff --git a/asterixdb/asterix-app/data/tpcds/item.csv b/asterixdb/asterix-app/data/tpcds/item.csv
new file mode 100644
index 0000000..bdccbcb
--- /dev/null
+++ b/asterixdb/asterix-app/data/tpcds/item.csv
@@ -0,0 +1,20 @@
+1|AAAAAAABAAAAAAA|1997-10-27||Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el|27.02|23.23|5003002|exportischolar #2|3|pop|5|Music|52|ableanti|N/A|3663peru009490160959|spring|Tsp|Unknown|6|ought|
+2|AAAAAAAACAAAAAAA|1997-10-27|2000-10-26|False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, |1.12|0.38|1001001|amalgamalg #1|1|dresses|1|Women|294|esen stable|petite|516steel060826230906|rosy|Bunch|Unknown|98|able|
+3|AAAAAAAACAAAAAAA|2000-10-27||False opportunities would run alone with a views. Early approaches would show inc, european intentions; important, main passages shall know urban, |7.11|0.38|1001001|brandbrand #4|7|decor|7|Home|294|esen stable|N/A|516steel060826230906|sienna|Cup|Unknown|18|pri|
+4|AAAAAAAAEAAAAAAA|1997-10-27|1999-10-27|Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D|1.35|0.85|3002001|importoexporti #1|2|infants|3|Children|479|n stationese|extra large|610157moccasin018327|red|Tbl|Unknown|26|ese|
+5|AAAAAAAAEAAAAAAA|1999-10-28|2001-10-26|Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D|4.00|1.76|2002002|importoimporto #2|2|shirts|2|Men|220|barableable|petite|42214rosy28066558020|pink|Cup|Unknown|27|anti|
+6|AAAAAAAAEAAAAAAA|2001-10-27||Normal systems would join simply different theories. Full, new clothes may eat instead achievements. D|0.85|1.76|2002002|exportiimporto #1|3|pants|2|Men|212|barableable|large|42214rosy28066558020|moccasin|Bundle|Unknown|6|cally|
+7|AAAAAAAAHAAAAAAA|1997-10-27||Anxious accounts must catch also years. Revolutionary, large directors used to embrace then mo|9.94|6.75|3001002|amalgexporti #2|1|newborn|3|Children|214|eseoughtable|petite|6moccasin24027188872|spring|Tsp|Unknown|64|ation|
+8|AAAAAAAAIAAAAAAA|1997-10-27|2000-10-26|F|2.76|0.85|3003001|exportiexporti #1|3|toddlers|3|Children|630|barprically|extra large|35123wheat3256343398|turquoise|Carton|Unknown|25|eing|
+9|AAAAAAAAIAAAAAAA|2000-10-27||F|4.46|0.85|1004002|edu packamalg #2|3|swimwear|1|Women|630|barprically|medium|35123wheat3256343398|wheat|Tbl|Unknown|3|n st|
+10|AAAAAAAAKAAAAAAA|1997-10-27|1999-10-27|Classical services go trousers. However great galleries might say needs. Assumptions change very in favour of the notes. Teeth woul|8.94|4.11|10008011|namelessunivamalg #11|8|scanners|10|Electronics|350|barantipri|N/A|8159007505thistle447|pale|Tsp|Unknown|34|barought|
+11|AAAAAAAAKAAAAAAA|1999-10-28|2001-10-26|Correct, fo|54.87|4.11|10008011|edu packbrand #4|14|estate|6|Jewelry|625|antiablecally|N/A|snow1543775706017405|yellow|Bunch|Unknown|26|oughtought|
+12|AAAAAAAAKAAAAAAA|2001-10-27||Corporate, important facilities claim trying, external sides. Elements used to expect home pr|6.54|4.11|10008011|corpnameless #3|14|furniture|7|Home|264|esecallyable|N/A|968467777sky92069287|royal|Tbl|Unknown|19|ableought|
+13|AAAAAAAANAAAAAAA|1997-10-27||Hard, private departments spoil more quickly possible members; clear troops fail only needs. |8.76|7.62|6012006|importobrand #6|12|costume|6|Jewelry|167|ationcallyought|N/A|883208731996blue7862|olive|Bundle|Unknown|51|priought|
+14|AAAAAAAAOAAAAAAA|1997-10-27|2000-10-26|Teachers carry by the children; old democrats enco|1.85|0.59|8007005|brandnameless #5|7|hockey|8|Sports|460|barcallyese|N/A|1144670162goldenrod2|red|Dram|Unknown|6|eseought|
+15|AAAAAAAAOAAAAAAA|2000-10-27||Teachers carry by the children; old democrats enco|2.57|0.59|5002002|importoscholar #2|2|country|5|Music|86|barcallyese|N/A|1144670162goldenrod2|royal|Pound|Unknown|11|antiought|
+16|AAAAAAAAABAAAAAA|1997-10-27|1999-10-27|Dominant, christian pp. may not raise|0.31|0.14|1002001|importoamalg #1|2|fragrances|1|Women|117|ationoughtought|large|36933056603steel7373|bisque|Lb|Unknown|23|callyought|
+17|AAAAAAAAABAAAAAA|1999-10-28|2001-10-26|Dominant, christian pp. may not raise|6.49|0.14|1002001|amalgimporto #2|1|accessories|2|Men|117|ationoughtought|extra large|452645olive281530722|snow|Dram|Unknown|41|ationought|
+18|AAAAAAAAABAAAAAA|2001-10-27||Twin, particular aspects will accept only on|0.87|0.48|1002001|importoamalg #1|2|fragrances|1|Women|117|ationoughtought|medium|452645olive281530722|rosy|Carton|Unknown|63|eingought|
+19|AAAAAAAADBAAAAAA|1997-10-27||Political parents know right; perfec|10.61|4.77|2004002|edu packimporto #2|4|sports-apparel|2|Men|364|esecallypri|large|85seashell1303417084|smoke|Ton|Unknown|13|n stought|
+20|AAAAAAAAEBAAAAAA|1997-10-27|2000-10-26|Legal, foreign days know losses; briefly equivalent arguments will expect today. New, front grounds look hot, other aspects. Actually national husbands show usually of course other stations; huge, k|29.35|18.78|1003001|exportiamalg #1|3|maternity|1|Women|995|antin stn st|extra large|97245417ivory0043452|tan|Gram|Unknown|21|barable|
diff --git a/asterixdb/asterix-app/data/tpcds/store_sales.csv b/asterixdb/asterix-app/data/tpcds/store_sales.csv
new file mode 100644
index 0000000..29a1f05
--- /dev/null
+++ b/asterixdb/asterix-app/data/tpcds/store_sales.csv
@@ -0,0 +1,19 @@
+2451293|43503|1|1|518725|1359|31593|8|39|239999|20|61.65|61.65|16.64|0.00|332.80|1233.00|1233.00|0.00|0.00|332.80|332.80|-900.20|
+2451293|43503|3|1|518725|1359|31593|8|104|239999|98|55.42|110.84|70.93|0.00|6951.14|5431.16|10862.32|139.02|0.00|6951.14|7090.16|1519.98|
+2451293|43503|5|1|518725|1359|31593|8|294|239999|22|20.66|24.58|9.34|16.43|205.48|454.52|540.76|1.89|16.43|189.05|190.94|-265.47|
+2451293|43503|7|1|518725|1359|31593|8|137|239999|42|12.62|18.29|1.09|0.00|45.78|530.04|768.18|2.28|0.00|45.78|48.06|-484.26|
+2451293||9||518725|||||239999|76||23.04||0.00||1260.08|||0.00|297.16|303.10||
+2451293|43503|11|1|518725|1359|31593|8|256|239999|13|23.87|39.62|5.54|12.96|72.02|310.31|515.06|1.77|12.96|59.06|60.83|-251.25|
+2451293|43503|13|1|518725|1359|31593|8|128|239999|2|88.60|151.50|133.32|0.00|266.64|177.20|303.00|13.33|0.00|266.64|279.97|89.44|
+2451293|43503|15|1|518725|1359|31593|8|266|239999|13|60.52|95.62|13.38|0.00|173.94|786.76|1243.06|12.17|0.00|173.94|186.11|-612.82|
+2451293|43503|17|1|518725|1359|31593|8|179|239999|45|93.14|95.00|4.75|0.00|213.75|4191.30|4275.00|19.23|0.00|213.75|232.98|-3977.55|
+2451176|47181|2|10|1873544|2153|1962|10|92|240000|30|67.43|84.96|37.38|583.12|1121.40|2022.90|2548.80|5.38|583.12|538.28|543.66|-1484.62|
+2451176|47181|4|10|1873544|2153|1962|10|143|240000|14|51.64|66.61|8.65|0.00|121.10|722.96|932.54|8.47|0.00|121.10|129.57|-601.86|
+2451176|47181|6|10|1873544|2153|1962|10|129|240000|64|88.30|174.83|148.60|0.00|9510.40|5651.20|11189.12|855.93|0.00|9510.40|10366.33|3859.20|
+2451176|47181|8|10|1873544|2153|1962|10|71|240000|83|3.29|6.34|5.45|0.00|452.35|273.07|526.22|0.00|0.00|452.35|452.35|179.28|
+2451176|47181|10|10|1873544|2153|1962|10|99|240000|96|8.16|14.44|13.86|0.00|1330.56|783.36|1386.24|79.83|0.00|1330.56|1410.39|547.20|
+2451176|47181|12|10|1873544|2153|1962|10|114|240000|41|18.00|34.02|14.62|0.00|599.42|738.00|1394.82|11.98|0.00|599.42|611.40|-138.58|
+2451176|47181|14|10|1873544|2153|1962|10|15|240000|65|35.22|53.18|35.09|0.00|2280.85|2289.30|3456.70|182.46|0.00|2280.85|2463.31|-8.45|
+2451176|47181|16|10|1873544|2153|1962|10|35|240000|13|61.07|64.12|62.19|0.00|808.47|793.91|833.56|64.67|0.00|808.47|873.14|14.56|
+2451176|47181|18|10|1873544|2153|1962|10|216|240000|86|83.78|150.80|150.80|0.00|12968.80|7205.08|12968.80|0.00|0.00|12968.80|12968.80|5763.72|
+2451176|47181|20|10|1873544|2153|1962|10|59|240000|68|81.95|105.71|1.05|0.00|71.40|5572.60|7188.28|1.42|0.00|71.40|72.82|-5501.20|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1580.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1580.sqlpp
new file mode 100644
index 0000000..a43bee6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/tpcds/query-ASTERIXDB-1580.sqlpp
@@ -0,0 +1,174 @@
+/*
+ * 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 tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.customer_address_type as closed {
+    ca_address_sk:              int64,
+    ca_address_id:              string,
+    ca_street_number:           string?,
+    ca_street_name:             string?,
+    ca_street_type:             string?,
+    ca_suite_number:            string?,
+    ca_city:                    string?,
+    ca_county:                  string?,
+    ca_state:                   string?,
+    ca_zip:                     string?,
+    ca_country:                 string?,
+    ca_gmt_offset:              double?,
+    ca_location_type:           string?
+}
+
+create type tpcds.customer_type as closed {
+    c_customer_sk:             int64,
+    c_customer_id:             string,
+    c_current_cdemo_sk:        int64?,
+    c_current_hdemo_sk:        int64?,
+    c_current_addr_sk:         int64?,
+    c_first_shipto_date_sk:    int64?,
+    c_first_sales_date_sk:     int64?,
+    c_salutation:              string?,
+    c_first_name:              string?,
+    c_last_name:               string?,
+    c_preferred_cust_flag:     string?,
+    c_birth_day:               int64?,
+    c_birth_month:             int64?,
+    c_birth_year:              int64?,
+    c_birth_country:           string?,
+    c_login:                   string?,
+    c_email_address:           string?,
+    c_last_review_date:        string?
+}
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+create type tpcds.date_dim_type as closed {
+    d_date_sk:                 int64,
+    d_date_id:                 string,
+    d_date:                    string? ,
+    d_month_seq:               int64?,
+    d_week_seq:                int64?,
+    d_quarter_seq:             int64?,
+    d_year:                    int64?,
+    d_dow:                     int64?,
+    d_moy:                     int64?,
+    d_dom:                     int64?,
+    d_qoy:                     int64?,
+    d_fy_year:                 int64?,
+    d_fy_quarter_seq:          int64?,
+    d_fy_week_seq:             int64?,
+    d_day_name:                string?,
+    d_quarter_name:            string?,
+    d_holiday:                 string?,
+    d_weekend:                 string?,
+    d_following_holiday:       string?,
+    d_first_dom:               int64?,
+    d_last_dom:                int64?,
+    d_same_day_ly:             int64?,
+    d_same_day_lq:             int64?,
+    d_current_day:             string?,
+    d_current_week:            string?,
+    d_current_month:           string?,
+    d_current_quarter:         string?,
+    d_current_year:            string?
+}
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset customer (customer_type)
+primary key c_customer_sk;
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;
+
+create dataset customer_address(customer_address_type)
+primary key ca_address_sk;
+
+create dataset date_dim(date_dim_type)
+primary key d_date_sk;
+
+select  a.ca_state state, count(a) cnt
+from customer_address a
+     ,customer c
+     ,store_sales s
+     ,date_dim d
+     ,item i
+where  a.ca_address_sk = c.c_current_addr_sk
+    and c.c_customer_sk = s.ss_customer_sk
+    and s.ss_sold_date_sk = d.d_date_sk
+    and s.ss_item_sk = i.i_item_sk
+group by a.ca_state
+having count(a) >= 10
+order by cnt
+limit 100;
+
+drop dataverse tpcds;
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
new file mode 100644
index 0000000..5e73419
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/tpcds/query-ASTERIXDB-1580.plan
@@ -0,0 +1,74 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- STREAM_PROJECT  |PARTITIONED|
+            -- SORT_MERGE_EXCHANGE [$$85(ASC) ]  |PARTITIONED|
+              -- STREAM_LIMIT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [topK: 100] [$$85(ASC)]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- PRE_CLUSTERED_GROUP_BY[$$95]  |PARTITIONED|
+                                    {
+                                      -- AGGREGATE  |LOCAL|
+                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                    }
+                              -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$95(ASC)] HASH:[$$95]  |PARTITIONED|
+                                -- SORT_GROUP_BY[$$73]  |PARTITIONED|
+                                        {
+                                          -- AGGREGATE  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                        }
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$77][$$80]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$77]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- HYBRID_HASH_JOIN [$$86][$$79]  |PARTITIONED|
+                                                  -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- HYBRID_HASH_JOIN [$$76][$$91]  |PARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$76]  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- HYBRID_HASH_JOIN [$$75][$$89]  |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 [$$89]  |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 [$$91]  |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/runtimets/queries_sqlpp/group-by/having-2/having-2.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.1.ddl.sqlpp
new file mode 100644
index 0000000..bf3c7e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.1.ddl.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * 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 EmployeeType AS {
+  id : string
+}
+
+CREATE TYPE IncentiveType AS {
+  job_category: string
+}
+
+CREATE TYPE SuperStarType AS {
+  id : string
+}
+
+CREATE DATASET Employee(EmployeeType) PRIMARY KEY id;
+CREATE DATASET Incentive(IncentiveType) PRIMARY KEY job_category;
+CREATE DATASET SuperStars(SuperStarType) PRIMARY KEY id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.2.update.sqlpp
new file mode 100644
index 0000000..e6545e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.2.update.sqlpp
@@ -0,0 +1,43 @@
+/*
+ * 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;
+
+INSERT INTO Employee (
+   {
+    'id': '123',
+    'job_category': 'Cook',
+    'salary': 2000,
+    'department_id': 'K55'
+   }
+);
+
+INSERT INTO Incentive (
+  { 'job_category': 'Cook',
+    'bonus': 1000
+  }
+)
+
+INSERT INTO SuperStars (
+  {
+    'id': '123'
+  }
+)
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp
new file mode 100644
index 0000000..7522795
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having-2/having-2.3.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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
+    JOIN Incentive i ON e.job_category = i.job_category
+    JOIN SuperStars s ON e.id = s.id
+GROUP BY e.department_id GROUP AS g
+HAVING coll_count( ( SELECT VALUE e FROM g) )>0
+SELECT e.department_id as deptId, SUM(e.salary + i.bonus) AS star_cost;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.1.ddl.sqlpp
new file mode 100644
index 0000000..bf3c7e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.1.ddl.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * 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 EmployeeType AS {
+  id : string
+}
+
+CREATE TYPE IncentiveType AS {
+  job_category: string
+}
+
+CREATE TYPE SuperStarType AS {
+  id : string
+}
+
+CREATE DATASET Employee(EmployeeType) PRIMARY KEY id;
+CREATE DATASET Incentive(IncentiveType) PRIMARY KEY job_category;
+CREATE DATASET SuperStars(SuperStarType) PRIMARY KEY id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.2.update.sqlpp
new file mode 100644
index 0000000..e6545e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.2.update.sqlpp
@@ -0,0 +1,43 @@
+/*
+ * 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;
+
+INSERT INTO Employee (
+   {
+    'id': '123',
+    'job_category': 'Cook',
+    'salary': 2000,
+    'department_id': 'K55'
+   }
+);
+
+INSERT INTO Incentive (
+  { 'job_category': 'Cook',
+    'bonus': 1000
+  }
+)
+
+INSERT INTO SuperStars (
+  {
+    'id': '123'
+  }
+)
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.3.query.sqlpp
new file mode 100644
index 0000000..b6f3d0e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/having/having.3.query.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * 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
+    JOIN Incentive i ON e.job_category = i.job_category
+    JOIN SuperStars s ON e.id = s.id
+GROUP BY e.department_id
+HAVING SUM(e.salary + i.bonus)>0
+SELECT e.department_id as deptId, SUM(e.salary + i.bonus) AS star_cost;
+
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.ddl.sqlpp
new file mode 100644
index 0000000..fb600d2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.ddl.sqlpp
@@ -0,0 +1,157 @@
+/*
+ * 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 tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create type tpcds.customer_address_type as closed {
+    ca_address_sk:              int64,
+    ca_address_id:              string,
+    ca_street_number:           string?,
+    ca_street_name:             string?,
+    ca_street_type:             string?,
+    ca_suite_number:            string?,
+    ca_city:                    string?,
+    ca_county:                  string?,
+    ca_state:                   string?,
+    ca_zip:                     string?,
+    ca_country:                 string?,
+    ca_gmt_offset:              double?,
+    ca_location_type:           string?
+}
+
+create type tpcds.customer_type as closed {
+    c_customer_sk:             int64,
+    c_customer_id:             string,
+    c_current_cdemo_sk:        int64?,
+    c_current_hdemo_sk:        int64?,
+    c_current_addr_sk:         int64?,
+    c_first_shipto_date_sk:    int64?,
+    c_first_sales_date_sk:     int64?,
+    c_salutation:              string?,
+    c_first_name:              string?,
+    c_last_name:               string?,
+    c_preferred_cust_flag:     string?,
+    c_birth_day:               int64?,
+    c_birth_month:             int64?,
+    c_birth_year:              int64?,
+    c_birth_country:           string?,
+    c_login:                   string?,
+    c_email_address:           string?,
+    c_last_review_date:        string?
+}
+
+create type tpcds.store_sales_type as closed {
+    ss_sold_date_sk:           int64?,
+    ss_sold_time_sk:           int64?,
+    ss_item_sk:                int64,
+    ss_customer_sk:            int64?,
+    ss_cdemo_sk:               int64?,
+    ss_hdemo_sk:               int64?,
+    ss_addr_sk:                int64?,
+    ss_store_sk:               int64?,
+    ss_promo_sk:               int64?,
+    ss_ticket_number:          int64,
+    ss_quantity:               int64?,
+    ss_wholesale_cost:         double?,
+    ss_list_price:             double?,
+    ss_sales_price:            double?,
+    ss_ext_discount_amt:       double?,
+    ss_ext_sales_price:        double?,
+    ss_ext_wholesale_cost:     double?,
+    ss_ext_list_price:         double?,
+    ss_ext_tax:                double?,
+    ss_coupon_amt:             double?,
+    ss_net_paid:               double?,
+    ss_net_paid_inc_tax:       double?,
+    ss_net_profit:             double?
+}
+
+create type tpcds.date_dim_type as closed {
+    d_date_sk:                 int64,
+    d_date_id:                 string,
+    d_date:                    string? ,
+    d_month_seq:               int64?,
+    d_week_seq:                int64?,
+    d_quarter_seq:             int64?,
+    d_year:                    int64?,
+    d_dow:                     int64?,
+    d_moy:                     int64?,
+    d_dom:                     int64?,
+    d_qoy:                     int64?,
+    d_fy_year:                 int64?,
+    d_fy_quarter_seq:          int64?,
+    d_fy_week_seq:             int64?,
+    d_day_name:                string?,
+    d_quarter_name:            string?,
+    d_holiday:                 string?,
+    d_weekend:                 string?,
+    d_following_holiday:       string?,
+    d_first_dom:               int64?,
+    d_last_dom:                int64?,
+    d_same_day_ly:             int64?,
+    d_same_day_lq:             int64?,
+    d_current_day:             string?,
+    d_current_week:            string?,
+    d_current_month:           string?,
+    d_current_quarter:         string?,
+    d_current_year:            string?
+}
+
+create type tpcds.item_type as closed {
+    i_item_sk:                 int64,
+    i_item_id:                 string,
+    i_rec_start_date:          string?,
+    i_rec_end_date:            string?,
+    i_item_desc:               string?,
+    i_current_price:           double?,
+    i_wholesale_cost:          double?,
+    i_brand_id:                int64? ,
+    i_brand:                   string?,
+    i_class_id:                int64? ,
+    i_class:                   string?,
+    i_category_id:             int64? ,
+    i_category:                string?,
+    i_manufact_id:             int64? ,
+    i_manufact:                string?,
+    i_size:                    string?,
+    i_formulation:             string?,
+    i_color:                   string?,
+    i_units:                   string?,
+    i_container:               string?,
+    i_manager_id:              int64?,
+    i_product_name:            string?
+}
+
+create dataset customer (customer_type)
+primary key c_customer_sk;
+
+create dataset store_sales (store_sales_type)
+primary key ss_item_sk, ss_ticket_number;
+
+create dataset item (item_type)
+primary key i_item_sk;
+
+create dataset customer_address(customer_address_type)
+primary key ca_address_sk;
+
+create dataset date_dim(date_dim_type)
+primary key d_date_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.2.update.sqlpp
new file mode 100644
index 0000000..602861c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.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 tpcds;
+
+
+load  dataset customer_address using localfs ((`path`=`asterix_nc1://data/tpcds/customer_address.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset customer using localfs ((`path`=`asterix_nc1://data/tpcds/customer.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset store_sales using localfs ((`path`=`asterix_nc1://data/tpcds/store_sales.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset item using localfs ((`path`=`asterix_nc1://data/tpcds/item.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
+
+load  dataset date_dim using localfs ((`path`=`asterix_nc1://data/tpcds/date_dim.csv`),
+(`format`=`delimited-text`), (`delimiter`=`|`));
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.3.query.sqlpp
new file mode 100644
index 0000000..c13f668
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.3.query.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.
+ */
+
+use tpcds;
+
+select  a.ca_state state, count(a) cnt
+from customer_address a
+     ,customer c
+     ,store_sales s
+     ,date_dim d
+     ,item i
+where  a.ca_address_sk = c.c_current_addr_sk
+    and c.c_customer_sk = s.ss_customer_sk
+    and s.ss_sold_date_sk = d.d_date_sk
+    and s.ss_item_sk = i.i_item_sk
+group by a.ca_state group as stategrp
+having coll_count((select value a from stategrp)) >= 10
+order by cnt
+limit 100;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.adm
new file mode 100644
index 0000000..4c6b9f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/tpcds/query-ASTERIXDB-1580/query-ASTERIXDB-1580.1.adm
@@ -0,0 +1 @@
+{ "state": "MN", "cnt": 10 }
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 ffdfe3b..25baa8c 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -2524,6 +2524,16 @@
         <output-dir compare="Text">gby-expr-3</output-dir>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="group-by">
+      <compilation-unit name="having">
+        <output-dir compare="Text">core-02</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="group-by">
+      <compilation-unit name="having-2">
+        <output-dir compare="Text">core-02</output-dir>
+      </compilation-unit>
+    </test-case>
   </test-group>
   <test-group name="index-join">
     <test-case FilePath="index-join">
@@ -5580,6 +5590,13 @@
       </compilation-unit>
     </test-case>
   </test-group>
+  <test-group name="tpcds">
+    <test-case FilePath="tpcds">
+      <compilation-unit name="query-ASTERIXDB-1580">
+        <output-dir compare="Text">query-ASTERIXDB-1580</output-dir>
+      </compilation-unit>
+    </test-case>
+  </test-group>
   <test-group name="tpch">
     <test-case FilePath="tpch">
       <compilation-unit name="distinct_by">