diff --git a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
index ad50dd0..ce2ca88 100644
--- a/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
+++ b/asterix-algebra/src/main/java/edu/uci/ics/asterix/optimizer/rules/PushAggregateIntoGroupbyRule.java
@@ -43,6 +43,7 @@
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.AggregateOperator;
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.AssignOperator;
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.GroupByOperator;
+import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.SubplanOperator;
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.UnnestOperator;
 import edu.uci.ics.hyracks.algebricks.core.algebra.operators.logical.visitors.VariableUtilities;
@@ -113,7 +114,8 @@
         List<LogicalVariable> used = new LinkedList<LogicalVariable>();
         VariableUtilities.getUsedVariables(op1, used);
         switch (op1.getOperatorTag()) {
-            case ASSIGN: {
+            case ASSIGN:
+            case SELECT: {
                 boolean found = false;
                 // Do some prefiltering: check if the Assign uses any gby vars.
                 for (LogicalVariable v : used) {
@@ -123,8 +125,20 @@
                     }
                 }
                 if (found) {
-                    AssignOperator assign = (AssignOperator) op1;
-                    for (Mutable<ILogicalExpression> exprRef : assign.getExpressions()) {
+                    if (op1.getOperatorTag() == LogicalOperatorTag.ASSIGN) {
+                        AssignOperator assign = (AssignOperator) op1;
+                        for (Mutable<ILogicalExpression> exprRef : assign.getExpressions()) {
+                            Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef,
+                                    gbyWithAgg, aggregateExprToVarExpr, context);
+                            if (p.first) {
+                                change = true;
+                                exprRef.setValue(p.second);
+                            }
+                        }
+                    }
+                    if (op1.getOperatorTag() == LogicalOperatorTag.SELECT) {
+                        SelectOperator select = (SelectOperator) op1;
+                        Mutable<ILogicalExpression> exprRef = select.getCondition();
                         Pair<Boolean, ILogicalExpression> p = extractAggFunctionsFromExpression(exprRef, gbyWithAgg,
                                 aggregateExprToVarExpr, context);
                         if (p.first) {
@@ -133,7 +147,7 @@
                         }
                     }
                     used.clear();
-                    VariableUtilities.getUsedVariables(assign, used);
+                    VariableUtilities.getUsedVariables(op1, used);
                     // increment the count for the ones which are still used
                     for (LogicalVariable v : used) {
                         Integer m = gbyListifyVarsCount.get(v);
diff --git a/asterix-app/src/test/resources/optimizerts/queries/query-issue562.aql b/asterix-app/src/test/resources/optimizerts/queries/query-issue562.aql
new file mode 100644
index 0000000..3e089a8
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/queries/query-issue562.aql
@@ -0,0 +1,99 @@
+/*
+ * Description  : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  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 closed {
+  o_orderkey: int32,
+  o_custkey: int32,
+  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 closed {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+create dataset Orders(OrderType)
+  primary key o_orderkey;
+create dataset Customer(CustomerType) 
+  primary key c_custkey;
+
+declare function q22_customer_tmp() {
+  for $c in dataset('Customer')
+  let $phone_substr := substring($c.c_phone, 1, 2)
+  where $phone_substr = '13'
+    or $phone_substr = '31'
+    or $phone_substr = '23'
+    or $phone_substr = '29'
+    or $phone_substr = '30'
+    or $phone_substr = '18'
+    or $phone_substr = '17'
+  return {
+    "c_acctbal": $c.c_acctbal,
+    "c_custkey": $c.c_custkey,
+    "cntrycode": $phone_substr
+  }
+}
+
+let $avg := avg(
+  for $c in dataset('Customer')
+  let $phone_substr := substring($c.c_phone, 1, 2)
+  where $c.c_acctbal > 0.00
+    and ($phone_substr = '13'
+    or $phone_substr = '31'
+    or $phone_substr = '23'
+    or $phone_substr = '29'
+    or $phone_substr = '30'
+    or $phone_substr = '18'
+    or $phone_substr = '17')
+  return $c.c_acctbal
+)
+
+for $ct in q22_customer_tmp()
+where count(for $o in dataset('Orders') where $ct.c_custkey = $o.o_custkey return $o) = 0
+group by $cntrycode := $ct.cntrycode with $ct
+order by $cntrycode
+return {
+  "cntrycode": $cntrycode,
+  "numcust": count($ct),
+  "totacctbal": sum(for $i in $ct return $i.c_acctbal)
+}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
new file mode 100644
index 0000000..992ce13
--- /dev/null
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue562.plan
@@ -0,0 +1,63 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$7(ASC) ]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$91]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$91(ASC)] HASH:[$$91]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$59]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- PRE_CLUSTERED_GROUP_BY[$$88]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$88(ASC)] HASH:[$$88]  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$68]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- STREAM_SELECT  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STABLE_SORT [$$68(ASC)]  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$68][$$70]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$68]  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- HYBRID_HASH_JOIN [$$86][$$85]  |PARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$86]  |PARTITIONED|
+                                                            -- UNNEST  |UNPARTITIONED|
+                                                              -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                          -- HASH_PARTITION_EXCHANGE [$$85]  |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 [$$70]  |PARTITIONED|
+                                              -- ASSIGN  |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/query-issue785.plan b/asterix-app/src/test/resources/optimizerts/results/query-issue785.plan
index 7b95d4d..ac2c294 100644
--- a/asterix-app/src/test/resources/optimizerts/results/query-issue785.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/query-issue785.plan
@@ -3,68 +3,63 @@
     -- STREAM_PROJECT  |PARTITIONED|
       -- ASSIGN  |PARTITIONED|
         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-          -- PRE_CLUSTERED_GROUP_BY[$$52]  |PARTITIONED|
+          -- PRE_CLUSTERED_GROUP_BY[$$7]  |PARTITIONED|
                   {
                     -- AGGREGATE  |LOCAL|
                       -- STREAM_LIMIT  |LOCAL|
-                        -- IN_MEMORY_STABLE_SORT [$$65(DESC)]  |LOCAL|
+                        -- IN_MEMORY_STABLE_SORT [$$62(DESC)]  |LOCAL|
                           -- NESTED_TUPLE_SOURCE  |LOCAL|
                   }
             -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-              -- STABLE_SORT [$$52(ASC)]  |PARTITIONED|
-                -- HASH_PARTITION_EXCHANGE [$$52]  |PARTITIONED|
-                  -- STREAM_PROJECT  |PARTITIONED|
-                    -- ASSIGN  |PARTITIONED|
-                      -- STREAM_PROJECT  |PARTITIONED|
-                        -- ASSIGN  |PARTITIONED|
-                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                            -- PRE_CLUSTERED_GROUP_BY[$$69, $$70]  |PARTITIONED|
-                                    {
-                                      -- AGGREGATE  |LOCAL|
-                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                    }
-                              -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$69(ASC), $$70(ASC)] HASH:[$$69, $$70]  |PARTITIONED|
-                                -- SORT_GROUP_BY[$$50, $$54]  |PARTITIONED|
-                                        {
-                                          -- AGGREGATE  |LOCAL|
-                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                        }
-                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                    -- STREAM_PROJECT  |PARTITIONED|
-                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                        -- HYBRID_HASH_JOIN [$$56][$$60]  |PARTITIONED|
-                                          -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                -- HYBRID_HASH_JOIN [$$54][$$63]  |PARTITIONED|
-                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- HYBRID_HASH_JOIN [$$54][$$55]  |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 [$$63]  |PARTITIONED|
-                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                      -- ASSIGN  |PARTITIONED|
+              -- STABLE_SORT [$$7(ASC)]  |PARTITIONED|
+                -- HASH_PARTITION_EXCHANGE [$$7]  |PARTITIONED|
+                  -- PRE_CLUSTERED_GROUP_BY[$$69, $$70]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$69(ASC), $$70(ASC)] HASH:[$$69, $$70]  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$50, $$54]  |PARTITIONED|
+                              {
+                                -- AGGREGATE  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              }
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- HYBRID_HASH_JOIN [$$56][$$60]  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$56]  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$54][$$63]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$54][$$55]  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN  |PARTITIONED|
                                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- DATASOURCE_SCAN  |PARTITIONED|
-                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                          -- HASH_PARTITION_EXCHANGE [$$60]  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- STREAM_PROJECT  |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 [$$63]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN  |PARTITIONED|
                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                    -- DATASOURCE_SCAN  |PARTITIONED|
-                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                -- HASH_PARTITION_EXCHANGE [$$60]  |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|
diff --git a/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
index 533a8b1..be634f3 100644
--- a/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
+++ b/asterix-app/src/test/resources/optimizerts/results/split-materialization-above-join.plan
@@ -8,7 +8,7 @@
               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                 -- STREAM_PROJECT  |PARTITIONED|
                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                    -- HYBRID_HASH_JOIN [$$94, $$93][$$100, $$102]  |PARTITIONED|
+                    -- HYBRID_HASH_JOIN [$$94, $$93][$$26, $$25]  |PARTITIONED|
                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                         -- NESTED_LOOP  |PARTITIONED|
                           -- BROADCAST_EXCHANGE  |PARTITIONED|
@@ -24,141 +24,136 @@
                                       -- DATASOURCE_SCAN  |PARTITIONED|
                                         -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                                           -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                      -- HASH_PARTITION_EXCHANGE [$$100]  |PARTITIONED|
-                        -- STREAM_PROJECT  |PARTITIONED|
-                          -- ASSIGN  |PARTITIONED|
-                            -- STREAM_PROJECT  |PARTITIONED|
-                              -- ASSIGN  |PARTITIONED|
-                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                  -- PRE_CLUSTERED_GROUP_BY[$$91, $$92]  |PARTITIONED|
-                                          {
-                                            -- AGGREGATE  |LOCAL|
-                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                          }
-                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                      -- STABLE_SORT [$$91(ASC), $$92(ASC)]  |PARTITIONED|
-                                        -- HASH_PARTITION_EXCHANGE [$$91, $$92]  |PARTITIONED|
-                                          -- STREAM_SELECT  |PARTITIONED|
-                                            -- STREAM_PROJECT  |PARTITIONED|
-                                              -- ASSIGN  |PARTITIONED|
-                                                -- STREAM_SELECT  |PARTITIONED|
-                                                  -- STREAM_PROJECT  |PARTITIONED|
+                      -- HASH_PARTITION_EXCHANGE [$$26]  |PARTITIONED|
+                        -- PRE_CLUSTERED_GROUP_BY[$$91, $$92]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STABLE_SORT [$$91(ASC), $$92(ASC)]  |PARTITIONED|
+                              -- HASH_PARTITION_EXCHANGE [$$91, $$92]  |PARTITIONED|
+                                -- STREAM_SELECT  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_SELECT  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                      -- HYBRID_HASH_JOIN [$$12][$$23]  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- SPLIT  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
                                                           -- STREAM_PROJECT  |PARTITIONED|
-                                                            -- ASSIGN  |PARTITIONED|
-                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                -- SPLIT  |PARTITIONED|
-                                                                  -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
-                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                      -- UNNEST  |PARTITIONED|
-                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                            -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
-                                                                                    {
-                                                                                      -- AGGREGATE  |LOCAL|
-                                                                                        -- STREAM_SELECT  |LOCAL|
-                                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                    }
-                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                -- STABLE_SORT [$$105(ASC), $$21(ASC)]  |PARTITIONED|
-                                                                                  -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
-                                                                                    -- STREAM_PROJECT  |PARTITIONED|
-                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                        -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                                          -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                                            -- UNNEST  |PARTITIONED|
-                                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                                -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                  -- ASSIGN  |PARTITIONED|
-                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                          -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
-                                                                                            -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                -- SORT_MERGE_EXCHANGE [$$107(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                                                  -- STABLE_SORT [$$107(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                      -- PRE_CLUSTERED_GROUP_BY[$$135]  |PARTITIONED|
-                                                                                                              {
-                                                                                                                -- AGGREGATE  |LOCAL|
-                                                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                              }
-                                                                                                        -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$135(ASC)] HASH:[$$135]  |PARTITIONED|
-                                                                                                          -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                                                  {
-                                                                                                                    -- AGGREGATE  |LOCAL|
-                                                                                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                                  }
-                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                -- UNNEST  |PARTITIONED|
-                                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                    -- ASSIGN  |PARTITIONED|
-                                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                          -- SPLIT  |PARTITIONED|
-                                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                              -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                          -- SPLIT  |PARTITIONED|
-                                                            -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                            -- UNNEST  |PARTITIONED|
                                                               -- STREAM_PROJECT  |PARTITIONED|
-                                                                -- UNNEST  |PARTITIONED|
-                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
+                                                                          {
+                                                                            -- AGGREGATE  |LOCAL|
+                                                                              -- STREAM_SELECT  |LOCAL|
+                                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                          }
                                                                     -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                      -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
-                                                                              {
-                                                                                -- AGGREGATE  |LOCAL|
-                                                                                  -- STREAM_SELECT  |LOCAL|
-                                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                              }
-                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                          -- STABLE_SORT [$$105(ASC), $$21(ASC)]  |PARTITIONED|
-                                                                            -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
-                                                                              -- STREAM_PROJECT  |PARTITIONED|
-                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                  -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
-                                                                                    -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
-                                                                                      -- UNNEST  |PARTITIONED|
+                                                                      -- STABLE_SORT [$$105(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                                -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                                  -- UNNEST  |PARTITIONED|
+                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                      -- STREAM_PROJECT  |PARTITIONED|
                                                                                         -- ASSIGN  |PARTITIONED|
-                                                                                          -- STREAM_PROJECT  |PARTITIONED|
-                                                                                            -- ASSIGN  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                               -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
-                                                                                    -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
-                                                                                      -- RUNNING_AGGREGATE  |PARTITIONED|
-                                                                                        -- STREAM_PROJECT  |PARTITIONED|
-                                                                                          -- SORT_MERGE_EXCHANGE [$$107(ASC), $$18(ASC) ]  |PARTITIONED|
-                                                                                            -- STABLE_SORT [$$107(ASC), $$18(ASC)]  |PARTITIONED|
-                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                -- PRE_CLUSTERED_GROUP_BY[$$135]  |PARTITIONED|
+                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
+                                                                                  -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                      -- SORT_MERGE_EXCHANGE [$$107(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                        -- STABLE_SORT [$$107(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- PRE_CLUSTERED_GROUP_BY[$$135]  |PARTITIONED|
+                                                                                                    {
+                                                                                                      -- AGGREGATE  |LOCAL|
+                                                                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                    }
+                                                                                              -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$135(ASC)] HASH:[$$135]  |PARTITIONED|
+                                                                                                -- SORT_GROUP_BY[$$17]  |PARTITIONED|
                                                                                                         {
                                                                                                           -- AGGREGATE  |LOCAL|
                                                                                                             -- NESTED_TUPLE_SOURCE  |LOCAL|
                                                                                                         }
-                                                                                                  -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$135(ASC)] HASH:[$$135]  |PARTITIONED|
-                                                                                                    -- SORT_GROUP_BY[$$17]  |PARTITIONED|
-                                                                                                            {
-                                                                                                              -- AGGREGATE  |LOCAL|
-                                                                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
-                                                                                                            }
-                                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                      -- UNNEST  |PARTITIONED|
                                                                                                         -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                          -- UNNEST  |PARTITIONED|
+                                                                                                          -- ASSIGN  |PARTITIONED|
                                                                                                             -- STREAM_PROJECT  |PARTITIONED|
-                                                                                                              -- ASSIGN  |PARTITIONED|
-                                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                -- SPLIT  |PARTITIONED|
                                                                                                                   -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                    -- SPLIT  |PARTITIONED|
+                                                                                                                    -- DATASOURCE_SCAN  |PARTITIONED|
                                                                                                                       -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                        -- DATASOURCE_SCAN  |PARTITIONED|
-                                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
-                                                                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- SPLIT  |PARTITIONED|
+                                                  -- HASH_PARTITION_EXCHANGE [$$23]  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- UNNEST  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- PRE_CLUSTERED_GROUP_BY[$$105]  |PARTITIONED|
+                                                                    {
+                                                                      -- AGGREGATE  |LOCAL|
+                                                                        -- STREAM_SELECT  |LOCAL|
+                                                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                    }
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- STABLE_SORT [$$105(ASC), $$21(ASC)]  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$105]  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- HYBRID_HASH_JOIN [$$15][$$18]  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$15]  |PARTITIONED|
+                                                                            -- UNNEST  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- DATASOURCE_SCAN  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                          -- HASH_PARTITION_EXCHANGE [$$18]  |PARTITIONED|
+                                                                            -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- SORT_MERGE_EXCHANGE [$$107(ASC), $$18(ASC) ]  |PARTITIONED|
+                                                                                  -- STABLE_SORT [$$107(ASC), $$18(ASC)]  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- PRE_CLUSTERED_GROUP_BY[$$135]  |PARTITIONED|
+                                                                                              {
+                                                                                                -- AGGREGATE  |LOCAL|
+                                                                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                              }
+                                                                                        -- HASH_PARTITION_MERGE_EXCHANGE MERGE:[$$135(ASC)] HASH:[$$135]  |PARTITIONED|
+                                                                                          -- SORT_GROUP_BY[$$17]  |PARTITIONED|
+                                                                                                  {
+                                                                                                    -- AGGREGATE  |LOCAL|
+                                                                                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                                  }
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                -- UNNEST  |PARTITIONED|
+                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- SPLIT  |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/runtimets/queries/tpch/query-issue562/query-issue562.1.ddl.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.1.ddl.aql
new file mode 100644
index 0000000..d5eb6ad
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.1.ddl.aql
@@ -0,0 +1,113 @@
+/*
+ * Description  : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+drop dataverse tpch if exists;
+create dataverse tpch;
+
+use dataverse tpch;
+
+create type LineItemType as closed {
+  l_orderkey: int32,
+  l_partkey: int32,
+  l_suppkey: int32,
+  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 closed {
+  o_orderkey: int32,
+  o_custkey: int32,
+  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 closed {
+  c_custkey: int32,
+  c_name: string,
+  c_address: string,
+  c_nationkey: int32,
+  c_phone: string,
+  c_acctbal: double,
+  c_mktsegment: string,
+  c_comment: string
+}
+
+create type SupplierType as closed {
+  s_suppkey: int32,
+  s_name: string,
+  s_address: string,
+  s_nationkey: int32,
+  s_phone: string,
+  s_acctbal: double,
+  s_comment: string
+}
+
+create type NationType as closed {
+  n_nationkey: int32,
+  n_name: string,
+  n_regionkey: int32,
+  n_comment: string
+}
+
+create type RegionType as closed {
+    r_regionkey: int32,
+    r_name: string,
+    r_comment: string
+}
+
+create type PartType as closed {
+  p_partkey: int32,
+  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 closed {
+  ps_partkey: int32,
+  ps_suppkey: int32,
+  ps_availqty: int32,
+  ps_supplycost: double,
+  ps_comment: string
+}
+
+create dataset LineItem(LineItemType)
+  primary key l_orderkey, l_linenumber;
+create dataset Orders(OrderType)
+  primary key o_orderkey;
+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 dataset Part(PartType)
+  primary key p_partkey;
+create dataset Partsupp(PartSuppType)
+  primary key ps_partkey, ps_suppkey;
+create dataset Customer(CustomerType)
+  primary key c_custkey;
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.2.update.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.2.update.aql
new file mode 100644
index 0000000..76c3ee6
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.2.update.aql
@@ -0,0 +1,41 @@
+/*
+ * Description  : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+use dataverse tpch;
+
+load dataset LineItem
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/lineitem.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Orders
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/orders.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Supplier
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/supplier.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Region
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/region.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Nation
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/nation.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Part
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/part.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Partsupp
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/partsupp.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
+load dataset Customer
+using "edu.uci.ics.asterix.external.dataset.adapter.NCFileSystemAdapter"
+(("path"="nc1://data/tpch0.001/customer.tbl"),("format"="delimited-text"),("delimiter"="|"));
+
diff --git a/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.3.query.aql b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.3.query.aql
new file mode 100644
index 0000000..8e97c62
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/queries/tpch/query-issue562/query-issue562.3.query.aql
@@ -0,0 +1,49 @@
+/*
+ * Description  : This test case is to verify the fix for issue562
+ * https://code.google.com/p/asterixdb/issues/detail?id=562
+ * Expected Res : SUCCESS
+ * Date         : 15th Jan. 2015
+ */
+
+use dataverse tpch;
+
+declare function q22_customer_tmp() {
+  for $c in dataset('Customer')
+  let $phone_substr := substring($c.c_phone, 1, 2)
+  where $phone_substr = '13'
+    or $phone_substr = '31'
+    or $phone_substr = '23'
+    or $phone_substr = '29'
+    or $phone_substr = '30'
+    or $phone_substr = '18'
+    or $phone_substr = '17'
+  return {
+    "c_acctbal": $c.c_acctbal,
+    "c_custkey": $c.c_custkey,
+    "cntrycode": $phone_substr
+  }
+}
+
+let $avg := avg(
+  for $c in dataset('Customer')
+  let $phone_substr := substring($c.c_phone, 1, 2)
+  where $c.c_acctbal > 0.00
+    and ($phone_substr = '13'
+    or $phone_substr = '31'
+    or $phone_substr = '23'
+    or $phone_substr = '29'
+    or $phone_substr = '30'
+    or $phone_substr = '18'
+    or $phone_substr = '17')
+  return $c.c_acctbal
+)
+
+for $ct in q22_customer_tmp()
+where count(for $o in dataset('Orders') where $ct.c_custkey = $o.o_custkey return $o) = 0
+group by $cntrycode := $ct.cntrycode with $ct
+order by $cntrycode
+return {
+  "cntrycode": $cntrycode,
+  "numcust": count($ct),
+  "totacctbal": sum(for $i in $ct return $i.c_acctbal)
+}
\ No newline at end of file
diff --git a/asterix-app/src/test/resources/runtimets/results/tpch/query-issue562/query-issue562.1.adm b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue562/query-issue562.1.adm
new file mode 100644
index 0000000..bd0bb4b
--- /dev/null
+++ b/asterix-app/src/test/resources/runtimets/results/tpch/query-issue562/query-issue562.1.adm
@@ -0,0 +1,8 @@
+[ { "cntrycode": "13", "numcust": 1i64, "totacctbal": 5679.84d }
+, { "cntrycode": "17", "numcust": 2i64, "totacctbal": 11309.79d }
+, { "cntrycode": "18", "numcust": 3i64, "totacctbal": 16076.24d }
+, { "cntrycode": "23", "numcust": 2i64, "totacctbal": 12652.16d }
+, { "cntrycode": "29", "numcust": 2i64, "totacctbal": 17195.08d }
+, { "cntrycode": "30", "numcust": 2i64, "totacctbal": 9662.279999999999d }
+, { "cntrycode": "31", "numcust": 3i64, "totacctbal": 18470.33d }
+ ]
diff --git a/asterix-app/src/test/resources/runtimets/testsuite.xml b/asterix-app/src/test/resources/runtimets/testsuite.xml
index baf330c..06829ad 100644
--- a/asterix-app/src/test/resources/runtimets/testsuite.xml
+++ b/asterix-app/src/test/resources/runtimets/testsuite.xml
@@ -4557,6 +4557,11 @@
             </compilation-unit>
         </test-case>
         <test-case FilePath="tpch">
+            <compilation-unit name="query-issue562">
+                <output-dir compare="Text">query-issue562</output-dir>
+        </compilation-unit>
+        </test-case>
+        <test-case FilePath="tpch">
             <compilation-unit name="query-issue810">
                 <output-dir compare="Text">query-issue810</output-dir>
         </compilation-unit>