diff --git a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/common/TestExecutor.java b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/common/TestExecutor.java
index e2f50d3..14784c5 100644
--- a/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/common/TestExecutor.java
+++ b/asterixdb/asterix-app/src/test/java/org/apache/asterix/test/common/TestExecutor.java
@@ -130,10 +130,16 @@
 import org.apache.commons.lang3.StringUtils;
 import org.apache.commons.lang3.mutable.MutableInt;
 import org.apache.http.Consts;
+import org.apache.http.HttpException;
+import org.apache.http.HttpHost;
+import org.apache.http.HttpRequest;
+import org.apache.http.HttpRequestInterceptor;
 import org.apache.http.HttpResponse;
 import org.apache.http.HttpStatus;
 import org.apache.http.NameValuePair;
 import org.apache.http.auth.AuthScope;
+import org.apache.http.auth.AuthState;
+import org.apache.http.auth.Credentials;
 import org.apache.http.auth.UsernamePasswordCredentials;
 import org.apache.http.client.AuthCache;
 import org.apache.http.client.CredentialsProvider;
@@ -157,6 +163,7 @@
 import org.apache.http.impl.client.StandardHttpRequestRetryHandler;
 import org.apache.http.message.BasicNameValuePair;
 import org.apache.http.protocol.HttpContext;
+import org.apache.http.protocol.HttpCoreContext;
 import org.apache.http.util.EntityUtils;
 import org.apache.hyracks.algebricks.common.utils.Pair;
 import org.apache.hyracks.http.server.utils.HttpUtil;
@@ -673,8 +680,8 @@
     protected HttpResponse executeHttpRequest(HttpUriRequest method) throws Exception {
         // https://issues.apache.org/jira/browse/ASTERIXDB-2315
         ExecutorService executor = Executors.newSingleThreadExecutor();
-        CloseableHttpClient client =
-                HttpClients.custom().setRetryHandler(StandardHttpRequestRetryHandler.INSTANCE).build();
+        CloseableHttpClient client = HttpClients.custom().addInterceptorFirst(new PreemptiveAuthInterceptor())
+                .setRetryHandler(StandardHttpRequestRetryHandler.INSTANCE).build();
         Future<HttpResponse> future = executor.submit(() -> {
             try {
                 return client.execute(method, getHttpContext());
@@ -2902,4 +2909,25 @@
     private static boolean containsPort(String endPoint) {
         return StringUtils.contains(endPoint, ':');
     }
+
+    // adapted from https://stackoverflow.com/questions/2014700/preemptive-basic-authentication-with-apache-httpclient-4
+    static class PreemptiveAuthInterceptor implements HttpRequestInterceptor {
+
+        public void process(final HttpRequest request, final HttpContext context) throws HttpException, IOException {
+            AuthState authState = (AuthState) context.getAttribute(HttpClientContext.TARGET_AUTH_STATE);
+            // if no auth scheme available yet, try to initialize it preemptively
+            if (authState.getAuthScheme() == null) {
+                CredentialsProvider credsProvider =
+                        (CredentialsProvider) context.getAttribute(HttpClientContext.CREDS_PROVIDER);
+                if (credsProvider != null) {
+                    HttpHost targetHost = (HttpHost) context.getAttribute(HttpCoreContext.HTTP_TARGET_HOST);
+                    Credentials creds =
+                            credsProvider.getCredentials(new AuthScope(targetHost.getHostName(), targetHost.getPort()));
+                    if (creds != null) {
+                        authState.update(new BasicScheme(), creds);
+                    }
+                }
+            }
+        }
+    }
 }
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q1.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q1.sqlpp
new file mode 100644
index 0000000..f411a1b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q1.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q1
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT ol.ol_number,
+       SUM(ol.ol_quantity) as sum_qty,
+       SUM(ol.ol_amount) as sum_amount,
+       AVG(ol.ol_quantity) as avg_qty,
+       AVG(ol.ol_amount) as avg_amount,
+       COUNT(*) as COUNT_order
+FROM   orders o, o.o_orderline ol
+WHERE  ol.ol_delivery_d > '2014-07-01 00:00:00'
+GROUP BY ol.ol_number
+ORDER BY ol.ol_number;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q10.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q10.sqlpp
new file mode 100644
index 0000000..76e1d6b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q10.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q10
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT c.c_id, c.c_last, SUM(ol.ol_amount) as revenue, c.c_city, c.c_phone, n.n_name
+FROM nation n, customer c, orders o, o.o_orderline ol
+WHERE  c.c_id = o.o_c_id
+  AND  c.c_w_id = o.o_w_id
+  AND  c.c_d_id = o.o_d_id
+  AND  o.o_entry_d >= '2015-10-01 00:00:00.000000'
+  AND o.o_entry_d < '2016-01-01 00:00:00.000000'
+  AND  n.n_nationkey = string_to_codepoint(c.c_state)[0]
+GROUP BY c.c_id, c.c_last, c.c_city, c.c_phone, n.n_name
+ORDER BY revenue DESC
+LIMIT 20;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q11.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q11.sqlpp
new file mode 100644
index 0000000..2181670
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q11.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q11
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT s.s_i_id, SUM(s.s_order_cnt) as ordercount
+FROM   nation n, supplier su, stock s
+WHERE  s.s_w_id * s.s_i_id MOD 10000 = su.su_suppkey
+  AND  su.su_nationkey = n.n_nationkey
+  AND  n.n_name = 'Germany'
+GROUP BY s.s_i_id
+HAVING SUM(s.s_order_cnt) >
+       (SELECT VALUE SUM(s1.s_order_cnt) * 0.00005
+        FROM nation n1, supplier su1, stock s1
+        WHERE s1.s_w_id * s1.s_i_id MOD 10000 = su1.su_suppkey
+          AND su1.su_nationkey = n1.n_nationkey
+          AND n1.n_name = 'Germany')[0]
+ORDER BY ordercount DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q12.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q12.sqlpp
new file mode 100644
index 0000000..c066cde
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q12.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q12
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT o.o_ol_cnt,
+       SUM (case WHEN o.o_carrier_id = 1 or o.o_carrier_id = 2
+                 THEN 1 ELSE 0 END) AS high_line_COUNT,
+       SUM (case WHEN o.o_carrier_id <> 1 AND o.o_carrier_id <> 2
+                 THEN 1 ELSE 0 END) AS low_line_COUNT
+FROM orders o, o.o_orderline ol
+WHERE  o.o_entry_d <= ol.ol_delivery_d
+  AND  ol.ol_delivery_d >= '2016-01-01 00:00:00.000000' AND  ol.ol_delivery_d < '2017-01-01 00:00:00.000000'
+GROUP BY o.o_ol_cnt
+ORDER BY o.o_ol_cnt;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q13.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q13.sqlpp
new file mode 100644
index 0000000..5397caa
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q13.sqlpp
@@ -0,0 +1,49 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q13
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT c_orders.c_count, COUNT(*) as custdist
+FROM   (SELECT c.c_id, COUNT(o.o_id) as c_count
+        FROM customer c LEFT OUTER JOIN orders o ON (
+             c.c_w_id = o.o_w_id
+             AND c.c_d_id = o.o_d_id
+             AND c.c_id = o.o_c_id
+             AND o.o_carrier_id > 8)
+        GROUP BY c.c_id) as c_orders
+GROUP BY c_orders.c_count
+ORDER BY custdist DESC, c_orders.c_count DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q14.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q14.sqlpp
new file mode 100644
index 0000000..b8d67e1
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q14.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q14
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT 100.00 * SUM(CASE WHEN i.i_data LIKE 'pr%'
+                         THEN ol.ol_amount ELSE 0 END) / (1+SUM(ol.ol_amount)) AS promo_revenue
+FROM item i, orders o, o.o_orderline ol
+WHERE ol.ol_i_id = i.i_id
+  AND ol.ol_delivery_d >= '2017-09-01 00:00:00.000000' AND ol.ol_delivery_d < '2017-10-01 00:00:00.000000';
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q15.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q15.sqlpp
new file mode 100644
index 0000000..c24cb2f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q15.sqlpp
@@ -0,0 +1,51 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q15
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+WITH revenue AS (
+     SELECT s.s_w_id * s.s_i_id MOD 10000 as supplier_no, SUM(ol.ol_amount) AS total_rev
+     FROM   stock s, orders o, o.o_orderline ol
+     WHERE ol.ol_i_id = s.s_i_id
+       AND ol.ol_supply_w_id = s.s_w_id
+       AND ol.ol_delivery_d >= '2018-01-01 00:00:00.000000' AND ol.ol_delivery_d < '2018-04-01 00:00:00.000000'
+GROUP BY s.s_w_id * s.s_i_id MOD 10000)
+SELECT su.su_suppkey, su.su_name, su.su_address, su.su_phone, r.total_revenue
+FROM revenue r,  supplier su
+WHERE  su.su_suppkey = r.supplier_no
+  AND  r.total_revenue = (SELECT VALUE max(r1.total_revenue) FROM revenue r1)[0]
+ORDER BY su.su_suppkey;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q16.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q16.sqlpp
new file mode 100644
index 0000000..0e07216
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q16.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q16
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT i.i_name, SUBSTR1(i.i_data, 1, 3) AS brand, i.i_price,
+       COUNT(DISTINCT (s.s_w_id * s.s_i_id MOD 10000)) AS supplier_cnt
+FROM stock s, item i
+WHERE i.i_id = s.s_i_id
+  AND i.i_data not LIKE 'zz%'
+  AND (s.s_w_id * s.s_i_id MOD 10000 NOT IN
+              (SELECT VALUE su.su_suppkey
+               FROM supplier su
+               WHERE su.su_comment LIKE '%Customer%Complaints%'))
+GROUP BY i.i_name, SUBSTR1(i.i_data, 1, 3), i.i_price
+ORDER BY supplier_cnt DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q17.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q17.sqlpp
new file mode 100644
index 0000000..95b60f5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q17.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q17
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT SUM(ol.ol_amount) / 2.0 AS AVG_yearly
+FROM   (SELECT i.i_id, AVG(ol1.ol_quantity) AS a
+        FROM   item i, orders o1, o1.o_orderline ol1
+        WHERE  i.i_data LIKE '%b'
+          AND  ol1.ol_i_id = i.i_id
+        GROUP BY i.i_id) t, orders o, o.o_orderline ol
+WHERE ol.ol_i_id = t.i_id
+  AND ol.ol_quantity < t.a;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q18.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q18.sqlpp
new file mode 100644
index 0000000..1a82ecf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q18.sqlpp
@@ -0,0 +1,46 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q18
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT c.c_last, c.c_id o_id, o.o_entry_d, o.o_ol_cnt, SUM(ol.ol_amount)
+FROM orders o, o.o_orderline ol, customer c
+WHERE  c.c_id = o.o_c_id AND  c.c_w_id = o.o_w_id AND  c.c_d_id = o.o_d_id
+GROUP BY o.o_id, o.o_w_id, o.o_d_id, c.c_id, c.c_last, o.o_entry_d, o.o_ol_cnt
+HAVING SUM(ol.ol_amount) > 200
+ORDER BY SUM(ol.ol_amount) DESC, o.o_entry_d
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q19.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q19.sqlpp
new file mode 100644
index 0000000..cca4bff
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q19.sqlpp
@@ -0,0 +1,59 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q19
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT SUM(ol.ol_amount) AS revenue
+FROM orders o, o.o_orderline ol, item i
+WHERE  ((
+         i.i_data LIKE '%h'
+         AND ol.ol_quantity >= 7 AND ol.ol_quantity <= 17
+         AND i.i_price between 1 AND 5
+         AND o.o_w_id IN [37, 29, 70]
+         ) OR (
+         i.i_data LIKE '%t'
+         AND ol.ol_quantity >= 16 AND ol.ol_quantity <= 26
+         AND i.i_price between 1 AND 10
+         AND o.o_w_id IN [78, 17, 6]
+         ) OR (
+         i.i_data LIKE '%m'
+         AND ol.ol_quantity >= 24 AND ol.ol_quantity <= 34
+         AND i.i_price between 1 AND 15
+         AND  o.o_w_id IN [91, 95, 15]
+         ))
+         AND ol.ol_i_id = i.i_id
+         AND i.i_price between 1 AND 15;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q2.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q2.sqlpp
new file mode 100644
index 0000000..3cbd465
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q2.sqlpp
@@ -0,0 +1,60 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q2
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT su.su_suppkey, su.su_name, n.n_name, i.i_id, i.i_name, su.su_address, su.su_phone, su.su_comment
+FROM (SELECT s1.s_i_id as m_i_id, MIN(s1.s_quantity) as m_s_quantity
+      FROM   stock s1,
+             (SELECT su1.su_suppkey
+              FROM   supplier su1, (SELECT n1.n_nationkey
+                                    FROM nation n1, region r1
+                                    WHERE n1.n_regionkey=r1.r_regionkey
+                                      AND r1.r_name LIKE 'Europ%') t1
+              WHERE su1.su_nationkey=t1.n_nationkey) t2
+      WHERE s1.s_w_id*s1.s_i_id MOD 10000 = t2.su_suppkey
+      GROUP BY s1.s_i_id) m,  item i, stock s, supplier su, nation n, region r
+WHERE i.i_id = s.s_i_id
+  AND s.s_w_id * s.s_i_id MOD 10000 = su.su_suppkey
+  AND su.su_nationkey = n.n_nationkey
+  AND n.n_regionkey = r.r_regionkey
+  AND i.i_data LIKE '%b'
+  AND r.r_name LIKE 'Europ%'
+  AND i.i_id=m.m_i_id
+  AND s.s_quantity = m.m_s_quantity
+ORDER BY n.n_name, su.su_name, i.i_id
+LIMIT 100;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q20.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q20.sqlpp
new file mode 100644
index 0000000..57c5280
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q20.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q20
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT su.su_name, su.su_address
+FROM   supplier su, nation n
+WHERE  su.su_suppkey IN
+       (SELECT VALUE s.s_i_id * s.s_w_id MOD 10000
+        FROM   stock s, orders o, o.o_orderline ol
+        WHERE  s.s_i_id IN
+               (SELECT VALUE i.i_id
+                FROM item i
+                WHERE i.i_data LIKE 'co%')
+          AND ol.ol_i_id=s.s_i_id
+          AND ol.ol_delivery_d >= '2016-01-01 12:00:00'
+          AND ol.ol_delivery_d < '2017-01-01 12:00:00'
+        GROUP BY s.s_i_id, s.s_w_id, s.s_quantity
+        HAVING 20*s.s_quantity > SUM(ol.ol_quantity))
+  AND su.su_nationkey = n.n_nationkey
+  AND n.n_name = 'Germany'
+ORDER BY su.su_name;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q21.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q21.sqlpp
new file mode 100644
index 0000000..5f8ae7f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q21.sqlpp
@@ -0,0 +1,61 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q21
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT z.su_name, count (*) AS numwait
+FROM (SELECT x.su_name
+      FROM (SELECT o1.o_id, o1.o_w_id, o1.o_d_id, ol1.ol_delivery_d,
+                   n.n_nationkey, su.su_suppkey, s.s_w_id, s.s_i_id, su.su_name
+            FROM nation n, supplier su, stock s, orders o1, o1.o_orderline ol1
+            WHERE  o1.o_w_id = s.s_w_id
+              AND ol1.ol_i_id = s.s_i_id
+              AND s.s_w_id * s.s_i_id MOD 10000 = su.su_suppkey
+              AND ol1.ol_delivery_d > STRING(DATE(o1.o_entry_d) + duration("P150D"))
+              AND o1.o_entry_d between '2017-12-01 00:00:00' and '2017-12-31 00:00:00'
+              AND su.su_nationkey = n.n_nationkey
+              AND n.n_name = 'Peru') x
+              LEFT OUTER JOIN
+              (SELECT o2.o_id, o2.o_w_id, o2.o_d_id, ol2.ol_delivery_d
+               FROM orders o2, o2.o_orderline ol2
+               WHERE o2.o_entry_d BETWEEN '2017-12-01 00:00:00' AND '2017-12-31 00:00:00') y
+            ON y.o_id = x.o_id AND y.o_w_id = x.o_w_id AND y.o_d_id = x.o_d_id
+            AND y.ol_delivery_d > x.ol_delivery_d
+            GROUP BY x.o_w_id, x.o_d_id, x.o_id, x.n_nationkey, x.su_suppkey, x.s_w_id, x.s_i_id, x.su_name
+            HAVING COUNT (y.o_id) = 0) z
+GROUP BY z.su_name
+LIMIT 100;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q22.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q22.sqlpp
new file mode 100644
index 0000000..0c37142
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q22.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q22
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT SUBSTR1(c.c_state,1,1) AS country, COUNT(*) AS numcust, SUM(c.c_balance) AS totacctbal
+FROM customer c
+WHERE SUBSTR1(c.c_phone,1,1) IN ['1','2','3','4','5','6','7']
+  AND c.c_balance > (SELECT VALUE AVG(c1.c_balance)
+                     FROM customer c1
+                     WHERE c1.c_balance > 0.00
+                       AND SUBSTR1(c1.c_phone,1,1) IN ['1','2','3','4','5','6','7'])[0]
+  AND NOT EXISTS (SELECT VALUE 1
+                  FROM orders o
+                  WHERE o.o_c_id = c.c_id AND o.o_w_id = c.c_w_id AND o.o_d_id = c.c_d_id
+                    AND o.o_entry_d BETWEEN '2013-12-01 00:00:00' AND '2013-12-31 00:00:00')
+GROUP BY SUBSTR1(c.c_state,1,1)
+ORDER BY SUBSTR1(c.c_state,1,1);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q3.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q3.sqlpp
new file mode 100644
index 0000000..14ba005
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q3.sqlpp
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q3
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+WITH co as
+     (SELECT o.o_id, o.o_w_id, o.o_d_id, o.o_entry_d, o.o_orderline
+      FROM orders o, customer c
+      WHERE  c.c_state LIKE 'A%'
+        AND c.c_id = o.o_c_id AND c.c_w_id = o.o_w_id AND c.c_d_id = o.o_d_id
+        AND o.o_entry_d < '2017-03-15 00:00:00.000000')
+SELECT co.o_id, co.o_w_id, co.o_d_id, SUM(ol.ol_amount) as revenue, co.o_entry_d
+FROM   co, co.o_orderline ol, neworder no
+WHERE no.no_w_id = co.o_w_id AND no.no_d_id = co.o_d_id AND no.no_o_id = co.o_id
+GROUP BY co.o_id, co.o_w_id, co.o_d_id, co.o_entry_d
+ORDER BY revenue DESC, co.o_entry_d;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q4.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q4.sqlpp
new file mode 100644
index 0000000..e5f82f3
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q4.sqlpp
@@ -0,0 +1,47 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q4
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT o.o_ol_cnt, COUNT(*) as order_COUNT
+FROM   orders o
+WHERE  o.o_entry_d >= '2015-07-01 00:00:00.000000' AND o.o_entry_d < '2015-10-01 00:00:00.000000'
+  AND EXISTS (SELECT VALUE 1
+              FROM o.o_orderline ol
+              WHERE ol.ol_delivery_d >= STRING(DATE(o.o_entry_d) + duration("P7D")))
+GROUP BY o.o_ol_cnt
+ORDER BY o.o_ol_cnt;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q5.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q5.sqlpp
new file mode 100644
index 0000000..670ce79
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q5.sqlpp
@@ -0,0 +1,56 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q5
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT cnros.n_name, ROUND(sum (cnros.ol_amount),2) as revenue
+FROM (SELECT cnro.ol_amount, cnro.n_name, cnro.n_nationkey, s.s_w_id, s.s_i_id
+      FROM stock s JOIN
+           (SELECT o.o_w_id, ol.ol_amount, ol.ol_i_id, cnr.n_name, cnr.n_nationkey
+            FROM orders o, o.o_orderline ol JOIN
+                 (SELECT c.c_id, c.c_w_id, c.c_d_id, nr.n_name, nr.n_nationkey
+                  FROM customer c JOIN
+                       (SELECT n.n_nationkey, n.n_name
+                        FROM nation n, region r
+                        WHERE n.n_regionkey = r.r_regionkey AND r.r_name = 'Asia') nr
+                  ON string_to_codepoint(c.c_state)[0] = nr.n_nationkey) cnr
+            ON o.o_entry_d >= '2016-01-01 00:00:00.000000' AND o.o_entry_d < '2017-01-01 00:00:00.000000'
+               AND cnr.c_id = o.o_c_id AND cnr.c_w_id = o.o_w_id AND cnr.c_d_id = o.o_d_id) cnro
+      ON cnro.o_w_id = s.s_w_id AND cnro.ol_i_id = s.s_i_id) cnros JOIN supplier su
+                       ON cnros.s_w_id * cnros.s_i_id MOD 10000 = su.su_suppkey AND su.su_nationkey = cnros.n_nationkey
+GROUP BY cnros.n_name
+ORDER BY revenue DESC;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q6.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q6.sqlpp
new file mode 100644
index 0000000..455d6ee
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q6.sqlpp
@@ -0,0 +1,44 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q6
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT SUM(ol.ol_amount) as revenue
+FROM   orders o, o.o_orderline ol
+WHERE  ol.ol_delivery_d >= '2016-01-01 00:00:00.000000'
+  AND  ol.ol_delivery_d < '2017-01-01 00:00:00.000000'
+  AND  ol.ol_amount > 600;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q7.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q7.sqlpp
new file mode 100644
index 0000000..37c4eac
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q7.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q7
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT su.su_nationkey as supp_nation, SUBSTR1(n1n2cools.c_state,1,1) as cust_nation, GET_YEAR(DATE(n1n2cools.o_entry_d)) as l_year, ROUND(SUM(n1n2cools.ol_amount),2) as revenue
+FROM (SELECT n1n2cool.c_state, n1n2cool.o_entry_d, n1n2cool.ol_amount, n1n2cool.n1key, s.s_w_id, s.s_i_id
+      FROM stock s JOIN
+           (SELECT o.o_entry_d, ol.ol_supply_w_id, ol.ol_i_id, n1n2c.c_state, ol.ol_amount, n1n2c.n1key
+            FROM orders o, o.o_orderline ol JOIN
+                 (SELECT c.c_id, c.c_w_id, c.c_d_id, c.c_state, n1n2.n1key
+                  FROM customer c JOIN
+                       (SELECT n1.n_nationkey n1key, n2.n_nationkey n2key
+                        FROM nation n1, nation n2
+                        WHERE (n1.n_name = 'Germany' AND n2.n_name = 'Cambodia') OR (n1.n_name = 'Cambodia' AND n2.n_name = 'Germany')
+                        )n1n2
+                  ON string_to_codepoint(c.c_state)[0] = n1n2.n2key) n1n2c
+            ON n1n2c.c_id = o.o_c_id AND n1n2c.c_w_id = o.o_w_id AND n1n2c.c_d_id = o.o_d_id
+              AND ol.ol_delivery_d BETWEEN '2017-01-01 00:00:00.000000' AND '2018-12-31 00:00:00.000000') n1n2cool
+      ON n1n2cool.ol_supply_w_id = s.s_w_id AND n1n2cool.ol_i_id = s.s_i_id)  n1n2cools JOIN supplier su
+              ON n1n2cools.s_w_id * n1n2cools.s_i_id MOD 10000 = su.su_suppkey AND su.su_nationkey = n1n2cools.n1key
+GROUP BY su.su_nationkey, SUBSTR1(n1n2cools.c_state,1,1), GET_YEAR(DATE(n1n2cools.o_entry_d))
+ORDER BY su.su_nationkey, cust_nation, l_year;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8.sqlpp
new file mode 100644
index 0000000..439be0e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8.sqlpp
@@ -0,0 +1,63 @@
+/*
+ * 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.
+ */
+
+/*
+ * Test plan for CH2 Q8
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT GET_YEAR(DATE(rn1coolis.o_entry_d)) as l_year,
+       ROUND((SUM(case when sun2.n_name = 'Germany' then rn1coolis.ol_amount else 0 end) / SUM(rn1coolis.ol_amount)),2) as mkt_share
+FROM (SELECT rn1cooli.o_entry_d,  rn1cooli.ol_amount, s.s_w_id, s.s_i_id
+      FROM stock s JOIN
+           (SELECT o.o_entry_d, ol.ol_i_id, ol.ol_amount, ol.ol_supply_w_id
+            FROM orders o, o.o_orderline ol, item i JOIN
+                 (SELECT c.c_id, c.c_w_id, c.c_d_id
+                  FROM customer c JOIN
+                       (SELECT n1.n_nationkey
+                        FROM nation n1, region r
+                        WHERE n1.n_regionkey = r.r_regionkey AND r.r_name = 'Europe') nr
+                  ON nr.n_nationkey = string_to_codepoint(c.c_state)[0]) cnr
+            ON cnr.c_id = o.o_c_id AND cnr.c_w_id = o.o_w_id AND cnr.c_d_id = o.o_d_id
+            AND i.i_data LIKE '%b' AND i.i_id = ol.ol_i_id
+            AND ol.ol_i_id < 1000
+            AND o.o_entry_d BETWEEN '2017-01-01 00:00:00.000000' AND '2018-12-31 00:00:00.000000') rn1cooli
+      ON rn1cooli.ol_i_id = s.s_i_id
+      AND rn1cooli.ol_supply_w_id = s.s_w_id) rn1coolis JOIN
+            (SELECT su.su_suppkey, n2.n_name
+             FROM supplier su, nation n2
+             WHERE su.su_nationkey = n2.n_nationkey) sun2
+          ON rn1coolis.s_w_id * rn1coolis.s_i_id MOD 10000 = sun2.su_suppkey
+GROUP BY GET_YEAR(DATE(rn1coolis.o_entry_d))
+ORDER BY l_year;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8_subquery.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8_subquery.sqlpp
deleted file mode 100644
index 3b31cf7..0000000
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q8_subquery.sqlpp
+++ /dev/null
@@ -1,66 +0,0 @@
-/*
- * 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.
- */
-
-/*
- * Test plan for CH2 Q8
- */
-
-drop dataverse test if exists;
-create dataverse test;
-use test;
-
-create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
-create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
-
-SELECT
-  GET_YEAR(DATE(rn1coolis.o_entry_d)) AS l_year,
-  ROUND((SUM(CASE WHEN sun2.n_name = 'Germany' THEN rn1coolis.ol_amount ELSE 0 END) / SUM(rn1coolis.ol_amount)),2)
-  AS mkt_share
-FROM (
-  SELECT rn1cooli.o_entry_d, rn1cooli.ol_amount, s.s_w_id, s.s_i_id
-  FROM stock s
-  JOIN (
-    SELECT o.o_entry_d, ol.ol_i_id, ol.ol_amount, ol.ol_supply_w_id
-    FROM orders o, o.o_orderline ol, item i
-    JOIN (
-      SELECT c.c_id,c.c_w_id, c.c_d_id
-      FROM customer c
-      JOIN (
-        SELECT n1.n_nationkey
-        FROM nation n1, region r
-        WHERE n1.n_regionkey = r.r_regionkey AND r.r_name = 'Europe'
-      ) nr ON nr.n_nationkey = string_to_codepoint(c.c_state)[0]
-    ) cnr ON cnr.c_id = o.o_c_id
-          AND cnr.c_w_id = o.o_w_id AND cnr.c_d_id = o.o_d_id AND i.i_data LIKE '%b'
-          AND i.i_id = ol.ol_i_id AND ol.ol_i_id < 1000
-          AND o.o_entry_d BETWEEN '2017-01-01 00:00:00.000000' AND '2018-12-31 00:00:00.000000'
-  ) rn1cooli ON rn1cooli.ol_i_id = s.s_i_id AND rn1cooli.ol_supply_w_id = s.s_w_id
-) rn1coolis
-JOIN (
-  SELECT su.su_suppkey, n2.n_name
-  FROM supplier su, nation n2
-  WHERE su.su_nationkey = n2.n_nationkey
-) sun2 ON rn1coolis.s_w_id * rn1coolis.s_i_id MOD 10000 = sun2.su_suppkey
-GROUP BY get_year(date(rn1coolis.o_entry_d))
-ORDER BY l_year;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q9.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q9.sqlpp
new file mode 100644
index 0000000..256c905
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/ch2/ch2_q9.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+/*
+ * Test plan for CH2 Q9
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset warehouse(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset district(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset customer(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset history(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset neworder(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset stock(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+SELECT sun.n_name, GET_YEAR(DATE(oolis.o_entry_d)) as l_year, round (SUM(oolis.ol_amount), 2) as SUM_profit
+FROM (SELECT s.s_w_id, s.s_i_id, ooli.o_entry_d, ooli.ol_amount
+      FROM stock s JOIN
+           (SELECT ol.ol_i_id, ol.ol_supply_w_id, ol.ol_amount, o.o_entry_d
+            FROM orders o,  o.o_orderline ol, item i
+            WHERE  i.i_data LIKE '%bb' and ol.ol_i_id = i.i_id) ooli
+      ON ooli.ol_i_id = s.s_i_id and ooli.ol_supply_w_id = s.s_w_id) oolis JOIN
+         (SELECT su.su_suppkey, n.n_name
+          FROM supplier su, nation n
+          WHERE su.su_nationkey = n.n_nationkey) sun
+ON oolis.s_w_id * oolis.s_i_id MOD 10000 = sun.su_suppkey
+GROUP BY sun.n_name, GET_YEAR(DATE(oolis.o_entry_d))
+ORDER BY sun.n_name, l_year DESC;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q1.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q1.plan
new file mode 100644
index 0000000..5eee969
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q1.plan
@@ -0,0 +1,29 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$ol_number(ASC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$107]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$107]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$92]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- ASSIGN  |PARTITIONED|
+                        -- STREAM_PROJECT  |PARTITIONED|
+                          -- UNNEST  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q10.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q10.plan
new file mode 100644
index 0000000..7cc19d7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q10.plan
@@ -0,0 +1,59 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$164(DESC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 20] [$$164(DESC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- SORT_GROUP_BY[$$171, $$172, $$173, $$174, $$175]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- HASH_PARTITION_EXCHANGE [$$171, $$172, $$173, $$174, $$175]  |PARTITIONED|
+                        -- SORT_GROUP_BY[$$155, $$150, $$151, $$152, $$153]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$155, $$160, $$162][$$159, $$161, $$163]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$155, $$160, $$162]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$165][$$166]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$165]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$166]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$159, $$161, $$163]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- UNNEST  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q11.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q11.plan
new file mode 100644
index 0000000..5133ed2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q11.plan
@@ -0,0 +1,119 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$178(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$178(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- NESTED_LOOP  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$204]  |PARTITIONED|
+                              {
+                                -- AGGREGATE  |LOCAL|
+                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                              }
+                        -- HASH_PARTITION_EXCHANGE [$$204]  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$168]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$176][$$175]  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$176]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$188][$$187]  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$193]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- REPLICATE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$192]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$175]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- REPLICATE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- BROADCAST_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |UNPARTITIONED|
+                        -- ASSIGN  |UNPARTITIONED|
+                          -- AGGREGATE  |UNPARTITIONED|
+                            -- STREAM_PROJECT  |UNPARTITIONED|
+                              -- ASSIGN  |UNPARTITIONED|
+                                -- AGGREGATE  |UNPARTITIONED|
+                                  -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                                    -- AGGREGATE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$191][$$190]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$191]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- HYBRID_HASH_JOIN [$$193][$$192]  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$193]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$192]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$190]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- REPLICATE  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q12.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q12.plan
new file mode 100644
index 0000000..1c561d0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q12.plan
@@ -0,0 +1,29 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$o_ol_cnt(ASC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$96]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$96]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$85]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- UNNEST  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q13.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q13.plan
new file mode 100644
index 0000000..b33dc89
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q13.plan
@@ -0,0 +1,53 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$125(DESC), $$c_count(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$125(DESC), $$c_count(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- SORT_GROUP_BY[$$139]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- HASH_PARTITION_EXCHANGE [$$139]  |PARTITIONED|
+                  -- SORT_GROUP_BY[$$124]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- SORT_GROUP_BY[$$137]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$126]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$126, $$128, $$130][$$127, $$129, $$131]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$126, $$128, $$130]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$127, $$129, $$131]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q14.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q14.plan
new file mode 100644
index 0000000..6dda602
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q14.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$84][$$83]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$84]  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$83]  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- STREAM_SELECT  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- UNNEST  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q15.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q15.plan
new file mode 100644
index 0000000..8da830c
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q15.plan
@@ -0,0 +1,129 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$220(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$220(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$#1][$$220]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$#1]  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$221][$$258]  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$221]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- SORT_GROUP_BY[$$270]  |PARTITIONED|
+                                            {
+                                              -- AGGREGATE  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                            }
+                                      -- HASH_PARTITION_EXCHANGE [$$270]  |PARTITIONED|
+                                        -- SORT_GROUP_BY[$$215]  |PARTITIONED|
+                                                {
+                                                  -- AGGREGATE  |LOCAL|
+                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                }
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$219, $$218][$$252, $$253]  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- REPLICATE  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$241, $$240]  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$252, $$253]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- REPLICATE  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- UNNEST  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$258]  |PARTITIONED|
+                              -- STREAM_PROJECT  |UNPARTITIONED|
+                                -- ASSIGN  |UNPARTITIONED|
+                                  -- AGGREGATE  |UNPARTITIONED|
+                                    -- AGGREGATE  |UNPARTITIONED|
+                                      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                                        -- AGGREGATE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- SORT_GROUP_BY[$$273]  |PARTITIONED|
+                                                        {
+                                                          -- AGGREGATE  |LOCAL|
+                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                        }
+                                                  -- HASH_PARTITION_EXCHANGE [$$273]  |PARTITIONED|
+                                                    -- SORT_GROUP_BY[$$239]  |PARTITIONED|
+                                                            {
+                                                              -- AGGREGATE  |LOCAL|
+                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                            }
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- HYBRID_HASH_JOIN [$$241, $$240][$$256, $$257]  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- REPLICATE  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$241, $$240]  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ASSIGN  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                  -- HASH_PARTITION_EXCHANGE [$$256, $$257]  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- REPLICATE  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- UNNEST  |PARTITIONED|
+                                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$220]  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q16.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q16.plan
new file mode 100644
index 0000000..6abcb63
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q16.plan
@@ -0,0 +1,65 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$131(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$131(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- PRE_CLUSTERED_GROUP_BY[$$121, $$122, $$123]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- MICRO_PRE_SORTED_DISTINCT_BY  |LOCAL|
+                            -- MICRO_STABLE_SORT [$$142(ASC)]  |LOCAL|
+                              -- ASSIGN  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [$$121(ASC), $$122(ASC), $$123(ASC)]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$121, $$122, $$123]  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ASSIGN  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- STREAM_SELECT  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$125, $$126]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- STREAM_SELECT  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STABLE_SORT [$$125(ASC), $$126(ASC)]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$125, $$126]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- NESTED_LOOP  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$130][$$129]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$130]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$129]  |PARTITIONED|
+                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q17.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q17.plan
new file mode 100644
index 0000000..8896e10
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q17.plan
@@ -0,0 +1,69 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$i_id][$$143]  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- PRE_CLUSTERED_GROUP_BY[$$154]  |PARTITIONED|
+                                  {
+                                    -- AGGREGATE  |LOCAL|
+                                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                  }
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$137]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$137][$$147]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$137]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- STREAM_SELECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$147]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- REPLICATE  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- UNNEST  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- REPLICATE  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- UNNEST  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q18.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q18.plan
new file mode 100644
index 0000000..ad40849
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q18.plan
@@ -0,0 +1,50 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- STREAM_PROJECT  |PARTITIONED|
+            -- SORT_MERGE_EXCHANGE [$$182(DESC), $$o_entry_d(ASC) ]  |PARTITIONED|
+              -- STREAM_LIMIT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STABLE_SORT [topK: 100] [$$182(DESC), $$o_entry_d(ASC)]  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$188, $$189, $$190, $$191, $$192, $$193, $$194]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- HASH_PARTITION_EXCHANGE [$$188, $$189, $$190, $$191, $$192, $$193, $$194]  |PARTITIONED|
+                                  -- SORT_GROUP_BY[$$164, $$172, $$173, $$174, $$168, $$169, $$170]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$177, $$172, $$173][$$174, $$178, $$179]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$177, $$172, $$173]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- UNNEST  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$174, $$178, $$179]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q19.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q19.plan
new file mode 100644
index 0000000..4030e67
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q19.plan
@@ -0,0 +1,34 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- HYBRID_HASH_JOIN [$$122][$$123]  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$122]  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- UNNEST  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ASSIGN  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                        -- HASH_PARTITION_EXCHANGE [$$123]  |PARTITIONED|
+                          -- STREAM_SELECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q2.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q2.plan
new file mode 100644
index 0000000..84f5c1d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q2.plan
@@ -0,0 +1,162 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- ASSIGN  |PARTITIONED|
+          -- SORT_MERGE_EXCHANGE [$$260(ASC), $$261(ASC), $$226(ASC) ]  |PARTITIONED|
+            -- STREAM_LIMIT  |PARTITIONED|
+              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                -- STABLE_SORT [topK: 100] [$$260(ASC), $$261(ASC), $$226(ASC)]  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- STREAM_PROJECT  |PARTITIONED|
+                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                        -- HYBRID_HASH_JOIN [$$238][$$239]  |PARTITIONED|
+                          -- HASH_PARTITION_EXCHANGE [$$238]  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$245][$$246]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$245]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- HYBRID_HASH_JOIN [$$251][$$228]  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$251]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$226, $$240][$$227, $$249]  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$226, $$240]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- HYBRID_HASH_JOIN [$$s_i_id][$$226]  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- SORT_GROUP_BY[$$271]  |PARTITIONED|
+                                                                        {
+                                                                          -- AGGREGATE  |LOCAL|
+                                                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                        }
+                                                                  -- HASH_PARTITION_EXCHANGE [$$271]  |PARTITIONED|
+                                                                    -- SORT_GROUP_BY[$$225]  |PARTITIONED|
+                                                                            {
+                                                                              -- AGGREGATE  |LOCAL|
+                                                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                            }
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- HYBRID_HASH_JOIN [$$247][$$256]  |PARTITIONED|
+                                                                              -- HASH_PARTITION_EXCHANGE [$$247]  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                    -- ASSIGN  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- REPLICATE  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                              -- HASH_PARTITION_EXCHANGE [$$256]  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- HYBRID_HASH_JOIN [$$243][$$255]  |PARTITIONED|
+                                                                                      -- HASH_PARTITION_EXCHANGE [$$243]  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- ASSIGN  |PARTITIONED|
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                      -- HASH_PARTITION_EXCHANGE [$$255]  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- HYBRID_HASH_JOIN [$$241][$$242]  |PARTITIONED|
+                                                                                              -- HASH_PARTITION_EXCHANGE [$$241]  |PARTITIONED|
+                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- REPLICATE  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                              -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                                  -- HASH_PARTITION_EXCHANGE [$$242]  |PARTITIONED|
+                                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                                                        -- ASSIGN  |PARTITIONED|
+                                                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                              -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$226]  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- STREAM_SELECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$227, $$249]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- REPLICATE  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                          -- HASH_PARTITION_EXCHANGE [$$228]  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ASSIGN  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- REPLICATE  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$246]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- REPLICATE  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- REPLICATE  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$242]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- STREAM_SELECT  |PARTITIONED|
+                                          -- ASSIGN  |PARTITIONED|
+                                            -- STREAM_PROJECT  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q20.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q20.plan
new file mode 100644
index 0000000..3b04b56
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q20.plan
@@ -0,0 +1,112 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$178(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$178(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- HYBRID_HASH_JOIN [$$172][$$173]  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$172]  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- PRE_CLUSTERED_GROUP_BY[$$164]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- STREAM_SELECT  |LOCAL|
+                                            -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- STABLE_SORT [$$164(ASC)]  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$164]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$171][$$143]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$171]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$143]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ASSIGN  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- SORT_GROUP_BY[$$187, $$188, $$189]  |PARTITIONED|
+                                                                {
+                                                                  -- AGGREGATE  |LOCAL|
+                                                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                }
+                                                          -- HASH_PARTITION_EXCHANGE [$$187, $$188, $$189]  |PARTITIONED|
+                                                            -- SORT_GROUP_BY[$$170, $$160, $$161]  |PARTITIONED|
+                                                                    {
+                                                                      -- AGGREGATE  |LOCAL|
+                                                                        -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                    }
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- HYBRID_HASH_JOIN [$$170][$$174]  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$170]  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- PRE_CLUSTERED_GROUP_BY[$$166]  |PARTITIONED|
+                                                                                        {
+                                                                                          -- AGGREGATE  |LOCAL|
+                                                                                            -- STREAM_SELECT  |LOCAL|
+                                                                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                                                                        }
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- STABLE_SORT [$$166(ASC)]  |PARTITIONED|
+                                                                                      -- HASH_PARTITION_EXCHANGE [$$166]  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- HYBRID_HASH_JOIN [$$170][$$117]  |PARTITIONED|
+                                                                                              -- HASH_PARTITION_EXCHANGE [$$170]  |PARTITIONED|
+                                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                                              -- HASH_PARTITION_EXCHANGE [$$117]  |PARTITIONED|
+                                                                                                -- ASSIGN  |PARTITIONED|
+                                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                            -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$174]  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- UNNEST  |PARTITIONED|
+                                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                            -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                    -- HASH_PARTITION_EXCHANGE [$$173]  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- ASSIGN  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan
new file mode 100644
index 0000000..6c845e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan
@@ -0,0 +1,116 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_LIMIT  |UNPARTITIONED|
+      -- STREAM_PROJECT  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$su_name(ASC) ]  |PARTITIONED|
+          -- STREAM_PROJECT  |PARTITIONED|
+            -- ASSIGN  |PARTITIONED|
+              -- STREAM_LIMIT  |PARTITIONED|
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- SORT_GROUP_BY[$$377]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- HASH_PARTITION_EXCHANGE [$$377]  |PARTITIONED|
+                      -- SORT_GROUP_BY[$$su_name]  |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|
+                                  -- SORT_GROUP_BY[$$368, $$369, $$370, $$371, $$372, $$373, $$374, $$375]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                    -- HASH_PARTITION_EXCHANGE [$$368, $$369, $$370, $$371, $$372, $$373, $$374, $$375]  |PARTITIONED|
+                                      -- SORT_GROUP_BY[$$330, $$356, $$355, $$332, $$333, $$329, $$328, $$357]  |PARTITIONED|
+                                              {
+                                                -- AGGREGATE  |LOCAL|
+                                                  -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                              }
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- NESTED_LOOP  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$329, $$328][$$330, $$340]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$329, $$328]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- HYBRID_HASH_JOIN [$$333][$$344]  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$333]  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- HYBRID_HASH_JOIN [$$332][$$343]  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$332]  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                        -- HASH_PARTITION_EXCHANGE [$$343]  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- HASH_PARTITION_EXCHANGE [$$344]  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ASSIGN  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$330, $$340]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- STREAM_SELECT  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- UNNEST  |PARTITIONED|
+                                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ASSIGN  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ASSIGN  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- UNNEST  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- STREAM_SELECT  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ASSIGN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- REPLICATE  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q22.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q22.plan
new file mode 100644
index 0000000..19a82f7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q22.plan
@@ -0,0 +1,110 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$#1(ASC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$168]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$168]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$136]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- STREAM_SELECT  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                              -- SORT_GROUP_BY[$$165]  |PARTITIONED|
+                                      {
+                                        -- AGGREGATE  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                      }
+                                -- HASH_PARTITION_EXCHANGE [$$165]  |PARTITIONED|
+                                  -- PRE_CLUSTERED_GROUP_BY[$$160]  |PARTITIONED|
+                                          {
+                                            -- AGGREGATE  |LOCAL|
+                                              -- STREAM_SELECT  |LOCAL|
+                                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                          }
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- STABLE_SORT [$$160(ASC)]  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$149, $$151, $$153][$$148, $$150, $$152]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$149, $$151, $$153]  |PARTITIONED|
+                                                  -- RUNNING_AGGREGATE  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- NESTED_LOOP  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- HYBRID_HASH_JOIN [$$79][$$141]  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ASSIGN  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ASSIGN  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- REPLICATE  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ASSIGN  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- REPLICATE  |PARTITIONED|
+                                                                            -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                              -- UNNEST  |UNPARTITIONED|
+                                                                                -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                          -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |UNPARTITIONED|
+                                                              -- ASSIGN  |UNPARTITIONED|
+                                                                -- AGGREGATE  |UNPARTITIONED|
+                                                                  -- AGGREGATE  |UNPARTITIONED|
+                                                                    -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+                                                                      -- AGGREGATE  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- HYBRID_HASH_JOIN [$$87][$$142]  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ASSIGN  |PARTITIONED|
+                                                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                                                      -- ASSIGN  |PARTITIONED|
+                                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                          -- REPLICATE  |PARTITIONED|
+                                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                  -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- REPLICATE  |PARTITIONED|
+                                                                                  -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                                    -- UNNEST  |UNPARTITIONED|
+                                                                                      -- EMPTY_TUPLE_SOURCE  |UNPARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$148, $$150, $$152]  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- STREAM_SELECT  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q3.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q3.plan
new file mode 100644
index 0000000..a2ffaea
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q3.plan
@@ -0,0 +1,56 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$204(DESC), $$o_entry_d(ASC) ]  |PARTITIONED|
+          -- STABLE_SORT [$$204(DESC), $$o_entry_d(ASC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- SORT_GROUP_BY[$$211, $$212, $$213, $$214]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- HASH_PARTITION_EXCHANGE [$$211, $$212, $$213, $$214]  |PARTITIONED|
+                  -- SORT_GROUP_BY[$$206, $$188, $$189, $$190]  |PARTITIONED|
+                          {
+                            -- AGGREGATE  |LOCAL|
+                              -- NESTED_TUPLE_SOURCE  |LOCAL|
+                          }
+                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$188, $$189, $$206][$$201, $$202, $$203]  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$188, $$189, $$206]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- UNNEST  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- HYBRID_HASH_JOIN [$$198, $$188, $$189][$$197, $$199, $$200]  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$198, $$188, $$189]  |PARTITIONED|
+                                              -- STREAM_SELECT  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                            -- HASH_PARTITION_EXCHANGE [$$197, $$199, $$200]  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- STREAM_SELECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$201, $$202, $$203]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (test.neworder)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q4.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q4.plan
new file mode 100644
index 0000000..5818a43
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q4.plan
@@ -0,0 +1,36 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$o_ol_cnt(ASC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$77]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$77]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$68]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- STREAM_SELECT  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- SUBPLAN  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- STREAM_SELECT  |LOCAL|
+                                      -- ASSIGN  |LOCAL|
+                                        -- UNNEST  |LOCAL|
+                                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- STREAM_SELECT  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ASSIGN  |PARTITIONED|
+                                -- STREAM_PROJECT  |PARTITIONED|
+                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                    -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q5.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q5.plan
new file mode 100644
index 0000000..30ae23f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q5.plan
@@ -0,0 +1,98 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$#2(DESC) ]  |PARTITIONED|
+          -- STABLE_SORT [$#2(DESC)]  |PARTITIONED|
+            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- ASSIGN  |PARTITIONED|
+                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                    -- SORT_GROUP_BY[$$277]  |PARTITIONED|
+                            {
+                              -- AGGREGATE  |LOCAL|
+                                -- NESTED_TUPLE_SOURCE  |LOCAL|
+                            }
+                      -- HASH_PARTITION_EXCHANGE [$$277]  |PARTITIONED|
+                        -- SORT_GROUP_BY[$$261]  |PARTITIONED|
+                                {
+                                  -- AGGREGATE  |LOCAL|
+                                    -- NESTED_TUPLE_SOURCE  |LOCAL|
+                                }
+                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                            -- STREAM_PROJECT  |PARTITIONED|
+                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                -- HYBRID_HASH_JOIN [$$260, $$254][$$252, $$255]  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$260, $$254]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- HYBRID_HASH_JOIN [$$231, $$232][$$240, $$267]  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$231, $$232]  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ASSIGN  |PARTITIONED|
+                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                        -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                              -- HASH_PARTITION_EXCHANGE [$$240, $$267]  |PARTITIONED|
+                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                    -- HYBRID_HASH_JOIN [$$246, $$240, $$249][$$262, $$263, $$264]  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$246, $$240, $$249]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- UNNEST  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- STREAM_SELECT  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ASSIGN  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                      -- HASH_PARTITION_EXCHANGE [$$262, $$263, $$264]  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- HYBRID_HASH_JOIN [$$244][$$260]  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$244]  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ASSIGN  |PARTITIONED|
+                                                                    -- STREAM_PROJECT  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                              -- HASH_PARTITION_EXCHANGE [$$260]  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- HYBRID_HASH_JOIN [$$242][$$243]  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$242]  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- ASSIGN  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                      -- HASH_PARTITION_EXCHANGE [$$243]  |PARTITIONED|
+                                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                                          -- STREAM_SELECT  |PARTITIONED|
+                                                                            -- ASSIGN  |PARTITIONED|
+                                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- DATASOURCE_SCAN (test.region)  |PARTITIONED|
+                                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                  -- HASH_PARTITION_EXCHANGE [$$252, $$255]  |PARTITIONED|
+                                    -- STREAM_PROJECT  |PARTITIONED|
+                                      -- ASSIGN  |PARTITIONED|
+                                        -- STREAM_PROJECT  |PARTITIONED|
+                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                            -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q6.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q6.plan
new file mode 100644
index 0000000..3b05b97
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q6.plan
@@ -0,0 +1,20 @@
+-- DISTRIBUTE_RESULT  |UNPARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
+    -- STREAM_PROJECT  |UNPARTITIONED|
+      -- ASSIGN  |UNPARTITIONED|
+        -- AGGREGATE  |UNPARTITIONED|
+          -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
+            -- AGGREGATE  |PARTITIONED|
+              -- STREAM_PROJECT  |PARTITIONED|
+                -- STREAM_SELECT  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- UNNEST  |PARTITIONED|
+                          -- STREAM_PROJECT  |PARTITIONED|
+                            -- ASSIGN  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q7.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q7.plan
new file mode 100644
index 0000000..4572727
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q7.plan
@@ -0,0 +1,102 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$su_nationkey(ASC), $#1(ASC), $#2(ASC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$310, $$311, $$312]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$310, $$311, $$312]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$262, $$258, $$259]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$288, $$284][$$262, $$285]  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$288, $$284]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$260, $$261][$$295, $$296]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$260, $$261]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$295, $$296]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$276, $$278, $$280][$$290, $$291, $$292]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$276, $$278, $$280]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- ASSIGN  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- UNNEST  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ASSIGN  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$290, $$291, $$292]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- HYBRID_HASH_JOIN [$$273][$$289]  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$273]  |PARTITIONED|
+                                                          -- ASSIGN  |PARTITIONED|
+                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                              -- ASSIGN  |PARTITIONED|
+                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                    -- DATASOURCE_SCAN (test.customer)  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                        -- HASH_PARTITION_EXCHANGE [$$289]  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                              -- NESTED_LOOP  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                                    -- ASSIGN  |PARTITIONED|
+                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                        -- REPLICATE  |PARTITIONED|
+                                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                            -- STREAM_PROJECT  |PARTITIONED|
+                                                                              -- ASSIGN  |PARTITIONED|
+                                                                                -- STREAM_PROJECT  |PARTITIONED|
+                                                                                  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                    -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                      -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                        -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                                -- BROADCAST_EXCHANGE  |PARTITIONED|
+                                                                  -- REPLICATE  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                                        -- ASSIGN  |PARTITIONED|
+                                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                              -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$262, $$285]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q8_subquery.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q8.plan
similarity index 100%
rename from asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q8_subquery.plan
rename to asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q8.plan
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q9.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q9.plan
new file mode 100644
index 0000000..97037d6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q9.plan
@@ -0,0 +1,81 @@
+-- DISTRIBUTE_RESULT  |PARTITIONED|
+  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+    -- STREAM_PROJECT  |PARTITIONED|
+      -- ASSIGN  |PARTITIONED|
+        -- SORT_MERGE_EXCHANGE [$$n_name(ASC), $#1(DESC) ]  |PARTITIONED|
+          -- SORT_GROUP_BY[$$217, $$218]  |PARTITIONED|
+                  {
+                    -- AGGREGATE  |LOCAL|
+                      -- NESTED_TUPLE_SOURCE  |LOCAL|
+                  }
+            -- HASH_PARTITION_EXCHANGE [$$217, $$218]  |PARTITIONED|
+              -- SORT_GROUP_BY[$$213, $$186]  |PARTITIONED|
+                      {
+                        -- AGGREGATE  |LOCAL|
+                          -- NESTED_TUPLE_SOURCE  |LOCAL|
+                      }
+                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                  -- STREAM_PROJECT  |PARTITIONED|
+                    -- ASSIGN  |PARTITIONED|
+                      -- STREAM_PROJECT  |PARTITIONED|
+                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                          -- HYBRID_HASH_JOIN [$$198][$$212]  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$198]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ASSIGN  |PARTITIONED|
+                                  -- STREAM_PROJECT  |PARTITIONED|
+                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                      -- HYBRID_HASH_JOIN [$$188, $$187][$$194, $$205]  |PARTITIONED|
+                                        -- HASH_PARTITION_EXCHANGE [$$188]  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ASSIGN  |PARTITIONED|
+                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- DATASOURCE_SCAN (test.stock)  |PARTITIONED|
+                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- HYBRID_HASH_JOIN [$$194][$$195]  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$194]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- ASSIGN  |PARTITIONED|
+                                                      -- STREAM_PROJECT  |PARTITIONED|
+                                                        -- UNNEST  |PARTITIONED|
+                                                          -- STREAM_PROJECT  |PARTITIONED|
+                                                            -- ASSIGN  |PARTITIONED|
+                                                              -- STREAM_PROJECT  |PARTITIONED|
+                                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                  -- DATASOURCE_SCAN (test.orders)  |PARTITIONED|
+                                                                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                                -- HASH_PARTITION_EXCHANGE [$$195]  |PARTITIONED|
+                                                  -- STREAM_PROJECT  |PARTITIONED|
+                                                    -- STREAM_SELECT  |PARTITIONED|
+                                                      -- ASSIGN  |PARTITIONED|
+                                                        -- STREAM_PROJECT  |PARTITIONED|
+                                                          -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                            -- DATASOURCE_SCAN (test.item)  |PARTITIONED|
+                                                              -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                                -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                            -- HASH_PARTITION_EXCHANGE [$$212]  |PARTITIONED|
+                              -- STREAM_PROJECT  |PARTITIONED|
+                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                  -- HYBRID_HASH_JOIN [$$201][$$202]  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$201]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN (test.supplier)  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
+                                    -- HASH_PARTITION_EXCHANGE [$$202]  |PARTITIONED|
+                                      -- STREAM_PROJECT  |PARTITIONED|
+                                        -- ASSIGN  |PARTITIONED|
+                                          -- STREAM_PROJECT  |PARTITIONED|
+                                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                              -- DATASOURCE_SCAN (test.nation)  |PARTITIONED|
+                                                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
+                                                  -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.1.ddl.sqlpp
new file mode 100644
index 0000000..b09662f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.1.ddl.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.
+ */
+/*
+ * Description  : Run avg over a list of mixed types (ASTERIXDB-3015)
+ */
+
+drop  dataverse test if exists;
+create  dataverse test;
+
+use test;
+
+create dataset d1(id bigint) open type primary key id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.2.update.sqlpp
new file mode 100644
index 0000000..a5943c9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.2.update.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+/*
+ * Description  : Run avg over a list of mixed types (ASTERIXDB-3015)
+ */
+
+use test;
+
+insert into d1 (
+  [
+    { "id": 1, "i": 1, "v": [ "a", 1, 2, 3 ] },
+    { "id": 2, "i": 2, "v": [ "b", 4, 5, 6 ] }
+  ]
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.3.query.sqlpp
new file mode 100644
index 0000000..eff95e0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.3.query.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+/*
+* Description  : Run avg over a list of mixed types (ASTERIXDB-3015)
+* Expected Res : Failure
+*/
+
+-- param max-warnings:json=100
+
+select avg(x) a from ["a", 1, 2, 3] x;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.4.query.sqlpp
new file mode 100644
index 0000000..756194b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.4.query.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Run avg over a list of mixed types (ASTERIXDB-3015)
+* Expected Res : Failure
+*/
+
+-- param max-warnings:json=100
+
+use test;
+
+select d1.id, avg(v) a
+from d1, d1.v
+group by d1.id
+order by d1.id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.5.query.sqlpp
new file mode 100644
index 0000000..71b22e7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/aggregate-sql-sugar/avg_mixed/avg_mixed.5.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+/*
+* Description  : Run avg over a list of mixed types (ASTERIXDB-3015)
+* Expected Res : Failure
+*/
+
+-- param max-warnings:json=100
+
+use test;
+
+
+select d1.id, avg(v) a
+from d1, d1.v
+/* +hash */ group by d1.id
+order by d1.id;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.1.ddl.sqlpp
new file mode 100644
index 0000000..fdfee88
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.1.ddl.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.
+ */
+
+drop dataverse tpcds if exists;
+create dataverse tpcds;
+
+use tpcds;
+
+create dataset item(i_item_sk string not unknown) open type primary key i_item_sk;
+
+create dataset inventory(inv_date_sk string not unknown, inv_item_sk string not unknown,
+  inv_warehouse_sk string not unknown) open type primary key inv_date_sk, inv_item_sk, inv_warehouse_sk;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.2.update.sqlpp
new file mode 100644
index 0000000..f8fe178
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.2.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+use tpcds;
+
+set `import-private-functions` `true`;
+
+insert into item (select value object_remove(t, "table_name") from tpcds_datagen("item", 0.5) t);
+
+insert into inventory (select value object_remove(t, "table_name") from tpcds_datagen("inventory", 0.5) t);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.query.sqlpp
new file mode 100644
index 0000000..01158b9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 ROUND(AVG(inv.inv_quantity_on_hand), 1) qoh, i.i_product_name
+FROM  inventory inv, item i
+WHERE inv.inv_item_sk /*+hash-bcast*/ = i.i_item_sk
+/*+ hash */ GROUP BY i.i_product_name
+ORDER BY qoh, i.i_product_name
+LIMIT 1;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/select_star_01/select_star_01.2.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/select_star_01/select_star_01.2.query.sqlpp
new file mode 100644
index 0000000..8cc7762
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/sql-compat/select_star_01/select_star_01.2.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.
+ */
+/*
+ * Test SELECT * in sql-compat mode (ASTERIXDB-3020)
+ */
+
+// requesttype=application/json
+// param sql-compat:json=true
+
+SELECT * FROM (
+  SELECT VALUE {string(v):v} FROM range(1, 2) v WHERE v > 1
+) t LIMIT 1;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.3.adm
new file mode 100644
index 0000000..53bdaa0
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.3.adm
@@ -0,0 +1 @@
+{ "a": 2.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.4.adm
new file mode 100644
index 0000000..35fa557
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.4.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "a": 2.0 }
+{ "id": 2, "a": 5.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.5.adm
new file mode 100644
index 0000000..35fa557
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/aggregate-sql-sugar/avg_mixed/avg_mixed.5.adm
@@ -0,0 +1,2 @@
+{ "id": 1, "a": 2.0 }
+{ "id": 2, "a": 5.0 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.adm
new file mode 100644
index 0000000..96494f4
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/group-by/query-ASTERIXDB-3016/query-ASTERIXDB-3016.3.adm
@@ -0,0 +1 @@
+{ "qoh": 402.0, "i_product_name": "ableoughtn st" }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/select_star_01/select_star_01.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/select_star_01/select_star_01.2.adm
new file mode 100644
index 0000000..26cb874
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/sql-compat/select_star_01/select_star_01.2.adm
@@ -0,0 +1 @@
+{ "2": 2 }
\ No newline at end of file
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 bc9353b..14322f2 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -2883,6 +2883,14 @@
         <expected-error>ASX1079: Compilation error: arrayagg is a SQL-92 aggregate function. The SQL++ core aggregate function strict_arrayagg could potentially express the intent.</expected-error>
       </compilation-unit>
     </test-case>
+    <test-case FilePath="aggregate-sql-sugar" check-warnings="true">
+      <compilation-unit name="avg_mixed">
+        <output-dir compare="Text">avg_mixed</output-dir>
+        <expected-warn>ASX0004: Unsupported type: agg-avg cannot process input type string (in line 26, at column 12)</expected-warn>
+        <expected-warn>ASX0004: Unsupported type: agg-avg cannot process input type string (in line 28, at column 19)</expected-warn>
+        <expected-warn>ASX0004: Unsupported type: agg-avg cannot process input type string (in line 29, at column 19)</expected-warn>
+      </compilation-unit>
+    </test-case>
     <test-case FilePath="aggregate-sql-sugar">
       <compilation-unit name="distinct_mixed">
         <output-dir compare="Text">distinct_mixed</output-dir>
@@ -6091,6 +6099,11 @@
         <output-dir compare="Text">hash-group-by-decor</output-dir>
       </compilation-unit>
     </test-case>
+    <!--test-case FilePath="group-by">
+      <compilation-unit name="query-ASTERIXDB-3016">
+        <output-dir compare="Text">query-ASTERIXDB-3016</output-dir>
+      </compilation-unit>
+    </test-case-->
   </test-group>
   <test-group name="index-join">
     <test-case FilePath="index-join">
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/config/StorageProperties.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/config/StorageProperties.java
index 5b10777..12c9c68 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/config/StorageProperties.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/config/StorageProperties.java
@@ -54,7 +54,7 @@
         STORAGE_FILTERED_MEMORYCOMPONENT_MAX_SIZE(LONG_BYTE_UNIT, 0L),
         STORAGE_LSM_BLOOMFILTER_FALSEPOSITIVERATE(DOUBLE, 0.01d),
         STORAGE_COMPRESSION_BLOCK(STRING, "snappy"),
-        STORAGE_DISK_FORCE_BYTES(LONG_BYTE_UNIT, StorageUtil.getLongSizeInBytes(1, MEGABYTE)),
+        STORAGE_DISK_FORCE_BYTES(LONG_BYTE_UNIT, StorageUtil.getLongSizeInBytes(16, MEGABYTE)),
         STORAGE_IO_SCHEDULER(STRING, "greedy"),
         STORAGE_WRITE_RATE_LIMIT(LONG_BYTE_UNIT, 0l),
         STORAGE_MAX_CONCURRENT_FLUSHES_PER_PARTITION(NONNEGATIVE_INTEGER, 2),
diff --git a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/BaseOperationTracker.java b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/BaseOperationTracker.java
index f8a81e4..5964bb4 100644
--- a/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/BaseOperationTracker.java
+++ b/asterixdb/asterix-common/src/main/java/org/apache/asterix/common/context/BaseOperationTracker.java
@@ -25,9 +25,12 @@
 import org.apache.hyracks.storage.am.lsm.common.api.LSMOperationType;
 import org.apache.hyracks.storage.common.IModificationOperationCallback;
 import org.apache.hyracks.storage.common.ISearchOperationCallback;
+import org.apache.logging.log4j.LogManager;
+import org.apache.logging.log4j.Logger;
 
 public class BaseOperationTracker implements ITransactionOperationTracker {
 
+    private static final Logger LOGGER = LogManager.getLogger();
     protected final int datasetID;
     protected final DatasetInfo dsInfo;
 
@@ -67,13 +70,23 @@
          * from being evicted/dropped until the transaction completes
          */
         dsInfo.touch();
-        dsInfo.getIndexes().get(resourceId).touch();
+        IndexInfo indexInfo = dsInfo.getIndexes().get(resourceId);
+        if (indexInfo == null) {
+            LOGGER.error("could not find resource id {} in dataset {}; registered indexes {}", resourceId, dsInfo,
+                    dsInfo.getIndexes());
+            throw new IllegalStateException("could not find resource id " + resourceId + " in dataset " + dsInfo);
+        }
+        indexInfo.touch();
     }
 
     @Override
     public void afterTransaction(long resourceId) {
         dsInfo.untouch();
-        dsInfo.getIndexes().get(resourceId).untouch();
+        IndexInfo indexInfo = dsInfo.getIndexes().get(resourceId);
+        if (indexInfo != null) {
+            // only untouch if the touch in beforeTransaction succeeded
+            indexInfo.untouch();
+        }
     }
 
     public DatasetInfo getDatasetInfo() {
diff --git a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
index 9b91bc0..b98151b 100644
--- a/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
+++ b/asterixdb/asterix-lang-sqlpp/src/main/java/org/apache/asterix/lang/sqlpp/rewrites/visitor/SqlCompatRewriteVisitor.java
@@ -567,8 +567,6 @@
 
     private void rewriteSelectBlock(SelectBlock selectBlock, SqlCompatSelectExpressionCoercionAnnotation ann)
             throws CompilationException {
-        SelectClause selectClause = selectBlock.getSelectClause();
-        List<Projection> projectList = selectClause.getSelectRegular().getProjections();
         SqlCompatSelectCoercionKind typeCoercion = ann.typeCoercion;
         switch (typeCoercion) {
             case SCALAR:
@@ -577,6 +575,8 @@
                  * SELECT x, y -> ERROR
                  * SELECT * -> ERROR
                  */
+                SelectClause selectClause = selectBlock.getSelectClause();
+                List<Projection> projectList = selectClause.getSelectRegular().getProjections();
                 if (projectList.size() > 1) {
                     throw new CompilationException(ErrorCode.COMPILATION_SUBQUERY_COERCION_ERROR,
                             projectList.get(1).getSourceLocation(), "Subquery returns more than one field");
@@ -599,6 +599,8 @@
                  *            (or SELECT x, y, {{x, y}} AS $new_unique_field) -- for MULTISET case
                  * SELECT * -> ERROR
                  */
+                selectClause = selectBlock.getSelectClause();
+                projectList = selectClause.getSelectRegular().getProjections();
                 List<Expression> exprList = new ArrayList<>(projectList.size());
                 for (Projection p : projectList) {
                     if (p.getKind() != Projection.Kind.NAMED_EXPR) {
diff --git a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/std/AbstractAvgAggregateFunction.java b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/std/AbstractAvgAggregateFunction.java
index d2367f9..fc668d2 100644
--- a/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/std/AbstractAvgAggregateFunction.java
+++ b/asterixdb/asterix-runtime/src/main/java/org/apache/asterix/runtime/aggregates/std/AbstractAvgAggregateFunction.java
@@ -136,12 +136,13 @@
         int offset = inputVal.getStartOffset();
 
         ATypeTag typeTag = EnumDeserializer.ATYPETAGDESERIALIZER.deserialize(data[offset]);
+        ATypeTag aggTypeTag = aggType;
         if (typeTag == ATypeTag.MISSING || typeTag == ATypeTag.NULL) {
             processNull();
             return;
-        } else if (aggType == ATypeTag.SYSTEM_NULL) {
-            aggType = typeTag;
-        } else if (typeTag != ATypeTag.SYSTEM_NULL && !ATypeHierarchy.isCompatible(typeTag, aggType)) {
+        } else if (aggTypeTag == ATypeTag.SYSTEM_NULL) {
+            aggTypeTag = typeTag;
+        } else if (typeTag != ATypeTag.SYSTEM_NULL && !ATypeHierarchy.isCompatible(typeTag, aggTypeTag)) {
             // Issue warning only once and treat current tuple as null
             if (!isWarned) {
                 isWarned = true;
@@ -149,8 +150,8 @@
             }
             processNull();
             return;
-        } else if (ATypeHierarchy.canPromote(aggType, typeTag)) {
-            aggType = typeTag;
+        } else if (ATypeHierarchy.canPromote(aggTypeTag, typeTag)) {
+            aggTypeTag = typeTag;
         }
 
         switch (typeTag) {
@@ -195,6 +196,7 @@
             }
         }
         count++;
+        aggType = aggTypeTag;
     }
 
     protected void finishPartialResults(IPointable result) throws HyracksDataException {
diff --git a/hyracks-fullstack/hyracks/hyracks-control/hyracks-control-common/src/main/java/org/apache/hyracks/control/common/config/ConfigManager.java b/hyracks-fullstack/hyracks/hyracks-control/hyracks-control-common/src/main/java/org/apache/hyracks/control/common/config/ConfigManager.java
index 0ba9090..85661fe 100644
--- a/hyracks-fullstack/hyracks/hyracks-control/hyracks-control-common/src/main/java/org/apache/hyracks/control/common/config/ConfigManager.java
+++ b/hyracks-fullstack/hyracks/hyracks-control/hyracks-control-common/src/main/java/org/apache/hyracks/control/common/config/ConfigManager.java
@@ -618,7 +618,6 @@
         @Override
         public void resolveCollision(CompositeMap<IOption, Object> composite, Map<IOption, Object> existing,
                 Map<IOption, Object> added, Collection<IOption> intersect) {
-            LOGGER.debug("resolveCollision: {}, {}, {}, {}", composite, existing, added, intersect);
             // no-op
         }
     }
diff --git a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/HashSpillableTableFactory.java b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/HashSpillableTableFactory.java
index 4f0c304..1e5c121 100644
--- a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/HashSpillableTableFactory.java
+++ b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/HashSpillableTableFactory.java
@@ -177,16 +177,21 @@
                 }
 
                 // Checks whether the garbage collection is required and conducts a garbage collection if so.
-                if (hashTableForTuplePointer.isGarbageCollectionNeeded()) {
+                collectGarbageInHashTableForTuplePointer(false);
+                bufferManager.clearPartition(partition);
+            }
+
+            private boolean collectGarbageInHashTableForTuplePointer(boolean force) throws HyracksDataException {
+                if (force || hashTableForTuplePointer.isGarbageCollectionNeeded()) {
                     int numberOfFramesReclaimed =
                             hashTableForTuplePointer.collectGarbage(bufferAccessor, tpcIntermediate);
                     if (LOGGER.isDebugEnabled()) {
                         LOGGER.debug("Garbage Collection on Hash table is done. Deallocated frames:"
                                 + numberOfFramesReclaimed);
                     }
+                    return numberOfFramesReclaimed != -1;
                 }
-
-                bufferManager.clearPartition(partition);
+                return false;
             }
 
             private int getPartition(int entryInHashTable) {
@@ -234,11 +239,18 @@
                 }
 
                 // Insertion to the hash table
-                if (!hashTableForTuplePointer.insert(entryInHashTable, pointer)) {
-                    // To preserve the atomicity of this method, we need to undo the effect
-                    // of the above bufferManager.insertTuple() call since the given insertion has failed.
-                    bufferManager.cancelInsertTuple(pid);
-                    return false;
+                boolean inserted = hashTableForTuplePointer.insert(entryInHashTable, pointer);
+                if (!inserted) {
+                    // Force garbage collection on the hash table and attempt to insert again
+                    if (collectGarbageInHashTableForTuplePointer(true)) {
+                        inserted = hashTableForTuplePointer.insert(entryInHashTable, pointer);
+                    }
+                    if (!inserted) {
+                        // To preserve the atomicity of this method, we need to undo the effect
+                        // of the above bufferManager.insertTuple() call since the given insertion has failed.
+                        bufferManager.cancelInsertTuple(pid);
+                        return false;
+                    }
                 }
 
                 return true;
diff --git a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/external/ExternalGroupWriteOperatorNodePushable.java b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/external/ExternalGroupWriteOperatorNodePushable.java
index 1a6f4ef..8618528 100644
--- a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/external/ExternalGroupWriteOperatorNodePushable.java
+++ b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/group/external/ExternalGroupWriteOperatorNodePushable.java
@@ -142,11 +142,15 @@
                         runs[i].getFileSize(), gbyFields, fdFields, groupByComparators, nmkComputer,
                         mergeAggregatorFactory, partialAggRecordDesc, outRecordDesc, frameLimit, level);
                 RunFileWriter[] runFileWriters = new RunFileWriter[partitionTable.getNumPartitions()];
-                int[] sizeInTuplesNextLevel =
-                        buildGroup(runs[i].createDeleteOnCloseReader(), partitionTable, runFileWriters);
-                for (int idFile = 0; idFile < runFileWriters.length; idFile++) {
-                    if (runFileWriters[idFile] != null) {
-                        generatedRuns.add(runFileWriters[idFile]);
+                int[] sizeInTuplesNextLevel;
+                try {
+                    sizeInTuplesNextLevel =
+                            buildGroup(runs[i].createDeleteOnCloseReader(), partitionTable, runFileWriters);
+                } finally {
+                    for (int idFile = 0; idFile < runFileWriters.length; idFile++) {
+                        if (runFileWriters[idFile] != null) {
+                            generatedRuns.add(runFileWriters[idFile]);
+                        }
                     }
                 }
 
diff --git a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/structures/ISerializableTable.java b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/structures/ISerializableTable.java
index 51f9984..58ad213 100644
--- a/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/structures/ISerializableTable.java
+++ b/hyracks-fullstack/hyracks/hyracks-dataflow-std/src/main/java/org/apache/hyracks/dataflow/std/structures/ISerializableTable.java
@@ -54,7 +54,7 @@
      *            required to access the real tuple to calculate the original hash value
      * @param tpc:
      *            hash function
-     * @return the number of frames that are reclaimed.
+     * @return the number of frames that are reclaimed. The value -1 is returned when no compaction was happened.
      * @throws HyracksDataException
      */
     int collectGarbage(ITuplePointerAccessor bufferAccessor, ITuplePartitionComputer tpc) throws HyracksDataException;
diff --git a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-common/src/main/java/org/apache/hyracks/storage/am/lsm/common/impls/LSMIndexPageWriteCallback.java b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-common/src/main/java/org/apache/hyracks/storage/am/lsm/common/impls/LSMIndexPageWriteCallback.java
index 0ad7033..85a2d39 100644
--- a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-common/src/main/java/org/apache/hyracks/storage/am/lsm/common/impls/LSMIndexPageWriteCallback.java
+++ b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-common/src/main/java/org/apache/hyracks/storage/am/lsm/common/impls/LSMIndexPageWriteCallback.java
@@ -43,7 +43,7 @@
     }
 
     public void beforeWrite(ICachedPage page) throws HyracksDataException {
-        rateLimiter.request(page.getFrameSizeMultiplier());
+        rateLimiter.request(page.getPageSize() * page.getFrameSizeMultiplier());
     }
 
     @Override