[NO ISSUE][TEST] Add CH2 queries to optimizerts
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Add CH2 queries to optimizer testsuite
Change-Id: Ic5d7362f4ca5a20f2591933de862a072a06c7b12
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/15183
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Dmitry Lychagin <dmitry.lychagin@couchbase.com>
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|