asterixdb-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From buyin...@apache.org
Subject [6/6] asterixdb git commit: Add cluster performance testing scripts.
Date Thu, 16 Feb 2017 20:53:10 GMT
Add cluster performance testing scripts.

- Wait SSH instead of sleep in the AWS installation script;
- Remove Java code and generate config files in Ansible;
- TPC-H is one benchmark, and more benchmarks could be added into the framework.

Change-Id: Ie17f20dc87694a865aebc561d8eded064c66f5bb
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1504
Sonar-Qube: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Reviewed-by: Till Westmann <tillw@apache.org>


Project: http://git-wip-us.apache.org/repos/asf/asterixdb/repo
Commit: http://git-wip-us.apache.org/repos/asf/asterixdb/commit/73715d87
Tree: http://git-wip-us.apache.org/repos/asf/asterixdb/tree/73715d87
Diff: http://git-wip-us.apache.org/repos/asf/asterixdb/diff/73715d87

Branch: refs/heads/master
Commit: 73715d87802b6da9916aa094b06d66bb40b47ced
Parents: 2898a01
Author: Yingyi Bu <yingyi@couchbase.com>
Authored: Wed Feb 15 11:16:52 2017 -0800
Committer: Yingyi Bu <buyingyi@gmail.com>
Committed: Thu Feb 16 12:49:25 2017 -0800

----------------------------------------------------------------------
 asterixdb/asterix-benchmark/pom.xml             |    51 +
 .../src/main/assembly/binary-assembly.xml       |    46 +
 .../src/main/resources/ansible/runquery.yml     |    61 +
 .../resources/benchmarks/tpch/gen/compile.yml   |    47 +
 .../main/resources/benchmarks/tpch/gen/gen.yml  |    45 +
 .../resources/benchmarks/tpch/gen/gendata.yml   |    36 +
 .../resources/benchmarks/tpch/gen/genscript.yml |    36 +
 .../resources/benchmarks/tpch/gen/settings.yml  |    45 +
 .../benchmarks/tpch/load/gendml_centralized.yml |    41 +
 .../benchmarks/tpch/load/gendml_partitioned.yml |    54 +
 .../resources/benchmarks/tpch/load/load.yml     |    35 +
 .../resources/benchmarks/tpch/load/loadgen.yml  |    41 +
 .../resources/benchmarks/tpch/load/settings.yml |    37 +
 .../benchmarks/tpch/load/template.sqlpp         |    18 +
 .../resources/benchmarks/tpch/queries/q1.sqlpp  |    37 +
 .../resources/benchmarks/tpch/queries/q10.sqlpp |    42 +
 .../resources/benchmarks/tpch/queries/q11.sqlpp |    47 +
 .../resources/benchmarks/tpch/queries/q12.sqlpp |    32 +
 .../resources/benchmarks/tpch/queries/q13.sqlpp |    40 +
 .../resources/benchmarks/tpch/queries/q14.sqlpp |    28 +
 .../resources/benchmarks/tpch/queries/q15.sqlpp |    39 +
 .../resources/benchmarks/tpch/queries/q16.sqlpp |    46 +
 .../resources/benchmarks/tpch/queries/q17.sqlpp |    36 +
 .../benchmarks/tpch/queries/q18.sqlpp.dis       |    41 +
 .../resources/benchmarks/tpch/queries/q19.sqlpp |    59 +
 .../benchmarks/tpch/queries/q2.sqlpp.dis        |    51 +
 .../benchmarks/tpch/queries/q20.sqlpp.dis       |    64 +
 .../benchmarks/tpch/queries/q21.sqlpp.dis       |    71 +
 .../benchmarks/tpch/queries/q22.sqlpp.dis       |    43 +
 .../resources/benchmarks/tpch/queries/q3.sqlpp  |    31 +
 .../resources/benchmarks/tpch/queries/q4.sqlpp  |    36 +
 .../benchmarks/tpch/queries/q5.sqlpp.dis        |    45 +
 .../resources/benchmarks/tpch/queries/q6.sqlpp  |    29 +
 .../benchmarks/tpch/queries/q7.sqlpp.dis        |    58 +
 .../benchmarks/tpch/queries/q8.sqlpp.dis        |    67 +
 .../benchmarks/tpch/queries/q9.sqlpp.dis        |    47 +
 .../benchmarks/tpch/setup/create.sqlpp          |   125 +
 .../resources/benchmarks/tpch/setup/setup.yml   |    27 +
 .../benchmarks/tpch/teardown/drop.sqlpp         |    16 +
 .../benchmarks/tpch/teardown/teardown.yml       |    27 +
 .../src/main/resources/bin/runall.sh            |    79 +
 .../main/resources/conf/benchmark_setting.yml   |    33 +
 .../q01_pricing_summary_report_nt.3.query.aql   |    40 -
 .../q02_minimum_cost_supplier.3.query.aql       |   135 -
 .../q03_shipping_priority_nt.3.query.aql        |    44 -
 .../queries/q04_order_priority.3.query.aql      |    39 -
 .../q05_local_supplier_volume.3.query.aql       |    75 -
 .../q06_forecast_revenue_change.3.query.aql     |    31 -
 .../queries/q07_volume_shipping.3.query.aql     |    81 -
 .../q08_national_market_share.3.query.aql       |    91 -
 .../q09_product_type_profit_nt.3.query.aql      |    85 -
 .../tpc-h/queries/q10_returned_item.3.query.aql |    73 -
 .../queries/q11_important_stock.3.query.aql     |    54 -
 .../tpc-h/queries/q12_shipping.3.query.aql      |    45 -
 .../q13_customer_distribution.3.query.aql       |    47 -
 .../queries/q14_promotion_effect.3.query.aql    |    38 -
 .../tpc-h/queries/q15_top_supplier.3.query.aql  |    45 -
 .../q16_parts_supplier_relationship.3.query.aql |    66 -
 ...q17_small_quantity_order_revenue.3.query.aql |    41 -
 .../q18_large_volume_customer.3.query.aql       |    47 -
 .../queries/q19_discounted_revenue.3.query.aql  |    51 -
 .../q20_potential_part_promotion.3.query.aql    |    68 -
 ...uppliers_who_kept_orders_waiting.3.query.aql |   114 -
 ...pliers_who_kept_orders_waiting.3.query_2.aql |    49 -
 .../q22_global_sales_opportunity.3.query.aql    |    51 -
 .../q01_pricing_summary_report_nt.3.query.adm   |     4 -
 .../q02_minimum_cost_supplier.3.query.adm       |   100 -
 .../q03_shipping_priority_nt.3.query.adm        |    10 -
 .../results/q04_order_priority.3.query.adm      |     5 -
 .../q06_forecast_revenue_change.3.query.adm     |     1 -
 .../results/q07_volume_shipping.3.query.adm     |     4 -
 .../tpc-h/results/q10_returned_item.3.query.adm |    20 -
 .../results/q11_important_stock.3.query.adm     |  1048 -
 .../tpc-h/results/q12_shipping.3.query.adm      |     2 -
 .../q13_customer_distribution.3.query.adm       |    42 -
 .../tpc-h/results/q15_top_supplier.3.query.adm  |     1 -
 .../q16_parts_supplier_relationship.3.query.adm | 18314 -----------------
 ...q17_small_quantity_order_revenue.3.query.adm |     1 -
 .../q20_potential_part_promotion.3.query.adm    |   186 -
 asterixdb/asterix-server/pom.xml                |     4 -
 .../java/org/apache/asterixdb/aws/AwsNode.java  |    44 -
 .../apache/asterixdb/aws/ConfigGenerator.java   |   136 -
 .../asterix-server/src/main/opt/aws/README      |     1 +
 .../src/main/opt/aws/ansible/aws_start.yml      |    68 +-
 .../src/main/opt/aws/ansible/instance_start.yml |    14 +-
 .../src/main/opt/aws/bin/start.sh               |     6 -
 .../src/main/opt/aws/conf/aws_settings.yml      |     8 +-
 .../src/main/opt/aws/conf/instance_settings.yml |     2 +-
 asterixdb/pom.xml                               |     1 +
 89 files changed, 1974 insertions(+), 21347 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/pom.xml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/pom.xml b/asterixdb/asterix-benchmark/pom.xml
new file mode 100644
index 0000000..2feaeb9
--- /dev/null
+++ b/asterixdb/asterix-benchmark/pom.xml
@@ -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.
+  -->
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
+  <modelVersion>4.0.0</modelVersion>
+  <parent>
+    <artifactId>apache-asterixdb</artifactId>
+    <groupId>org.apache.asterix</groupId>
+    <version>0.9.1-SNAPSHOT</version>
+  </parent>
+  <artifactId>asterix-benchmark</artifactId>
+
+  <build>
+    <plugins>
+      <plugin>
+        <artifactId>maven-assembly-plugin</artifactId>
+        <version>2.6</version>
+        <executions>
+          <execution>
+            <configuration>
+              <attach>true</attach>
+              <descriptors>
+                <descriptor>${project.basedir}/src/main/assembly/binary-assembly.xml</descriptor>
+              </descriptors>
+            </configuration>
+            <phase>package</phase>
+            <goals>
+              <goal>single</goal>
+            </goals>
+          </execution>
+        </executions>
+      </plugin>
+    </plugins>
+  </build>
+
+</project>

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml b/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml
new file mode 100644
index 0000000..387d1d1
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/assembly/binary-assembly.xml
@@ -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.
+ !-->
+<assembly xmlns="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.3"
+          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+          xsi:schemaLocation="http://maven.apache.org/plugins/maven-assembly-plugin/assembly/1.1.3 http://maven.apache.org/xsd/assembly-1.1.3.xsd">
+  <id>binary-assembly</id>
+  <formats>
+    <format>zip</format>
+    <format>dir</format>
+  </formats>
+  <includeBaseDirectory>false</includeBaseDirectory>
+  <fileSets>
+    <fileSet>
+      <directory>src/main/resources</directory>
+      <outputDirectory>.</outputDirectory>
+      <excludes>
+        <exclude>**/*.sh</exclude>
+      </excludes>
+    </fileSet>
+    <fileSet>
+      <directory>src/main/resources</directory>
+      <outputDirectory>.</outputDirectory>
+      <includes>
+        <include>**/*.sh</include>
+      </includes>
+      <filtered>true</filtered>
+      <fileMode>0755</fileMode>
+    </fileSet>
+  </fileSets>
+</assembly>

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml b/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml
new file mode 100644
index 0000000..ef3c2a5
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/ansible/runquery.yml
@@ -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.
+# ------------------------------------------------------------
+
+# This script requires extra variables if the caller wants to report numbers:
+# -- result_url
+# -- metric
+
+- hosts: [localhost,]
+  vars:
+     service: "http://{{ groups['cc'][0] }}:{{ service_port }}/{{ service_endpoint }}"
+     temp_file: "/tmp/query_var.yml"
+
+  tasks:
+    - include_vars: ../conf/benchmark_setting.yml
+
+    - name: Execute query {{ query_file }}
+      uri:
+        url: "{{ service }}"
+        method: POST
+        body: "{{ lookup('file', query_file ) }}"
+        timeout: 3600
+      register: response
+
+    - name: Extract execution time
+      shell: printf "exec_time{{':'}} {{ response.json.metrics.elapsedTime | regex_replace('m*s', '') | int }}\n" > "{{ temp_file }}"
+      when: report
+
+    - include_vars: "{{ temp_file }}"
+      when: report
+
+    - name: Report execution time
+      uri:
+          url: "{{ result_url }}"
+          method: POST
+          body: "{\"group\": \"{{ query_file | basename }}\", \"metric\": \"{{ metric }}\", \"value\": {{ exec_time }} }"
+          body_format: json
+          timeout: 3600
+          status_code: 201
+      when: report
+
+    - name: Delete the temporary file
+      file:
+        path: "{{ temp_file }}"
+        state: absent
+      when: report
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml
new file mode 100644
index 0000000..a0aa8d3
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/compile.yml
@@ -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.
+# ------------------------------------------------------------
+
+- include_vars: settings.yml
+
+- name: Install gcc
+  shell: sudo yum install -y gcc
+
+- name: Clean up data generator
+  file:
+    path: "{{ binary_dir }}"
+    state: absent
+
+- name: Clean up leftover file
+  file:
+    path: "{{ generatorzip }}"
+    state: absent
+
+- name: Download TPC-H data generator
+  get_url:
+    url: "{{ tpch_url }}"
+    dest: "{{ home_dir }}"
+    force: yes
+
+- name: Unzip data generator
+  shell: unzip "{{ generatorzip }}"
+
+- name: Compile dbgen
+  command: make
+  args:
+    chdir: "{{ binary_dir }}"

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml
new file mode 100644
index 0000000..536111e
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gen.yml
@@ -0,0 +1,45 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+
+# Compiles data generation source code.
+- hosts: ncs
+  tasks:
+    - name: Compile binary
+      include: compile.yml
+
+# Generates a script for each host.
+- hosts: [localhost,]
+  vars:
+      partitions: "{{ groups['ncs'] | length }}"
+  tasks:
+    - include_vars:  ../../../conf/benchmark_setting.yml
+
+    - name: Generate host-dependent script
+      shell: "ansible-playbook -i {{ node.1}}, genscript.yml \
+              --extra-vars=\"partition={{ node.0 }} partitions={{ partitions }} sf={{ partitions|float * scale }} ansible_ssh_user=ec2-user\""
+      with_indexed_items:  "{{ groups['ncs'] }}"
+      loop_control:
+          loop_var: node
+
+# Generates data and copy them to the data directory.
+- hosts: ncs
+  tasks:
+    - name: Generate data
+      include: gendata.yml

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml
new file mode 100644
index 0000000..389b973
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/gendata.yml
@@ -0,0 +1,36 @@
+# ------------------------------------------------------------
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+# ------------------------------------------------------------
+
+- include_vars: settings.yml
+
+- name: Generate data
+  command: "sh {{ localgencmd }}"
+  args:
+     chdir: "{{ binary_dir }}"
+
+- name: Ensure data directory exits
+  file:
+    path: "{{ data_dir }}"
+    state: directory
+
+- name: Cleanup data directory
+  shell: "rm -rf {{ data_dir }}/*"
+
+- name: Move generated files
+  shell: "mv {{ binary_dir }}/*.tbl* {{ data_dir }}/"

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml
new file mode 100644
index 0000000..30cf6e7
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/genscript.yml
@@ -0,0 +1,36 @@
+# ------------------------------------------------------------
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+# ------------------------------------------------------------
+
+- hosts: all
+  tasks:
+   - include_vars: settings.yml
+
+   - name: Ensure data directory exits
+     file:
+        path: "{{ data_dir }}"
+        state: directory
+
+   - name: Generate host-dependent data generation script
+     shell: echo "./{{ generator }} -s {{ sf }} -S {{ partition | int + 1 }} -C {{ partitions }}" > "{{ localgen }}"
+
+   - name: Change the permission for data generation script
+     file:
+       path: "{{ localgen }}"
+       owner: "{{ user }}"
+       mode: 0755

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml
new file mode 100644
index 0000000..11ba53c
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/gen/settings.yml
@@ -0,0 +1,45 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+# Data generator executable name.
+generator: dbgen
+
+# The zip contains all files for the generator.
+generatorzip: "{{ generator }}.zip"
+
+# The user name for data generation.
+user: ec2-user
+
+# The home directory.
+home_dir: "/home/{{ user }}"
+
+# The location for generated data.
+data_dir: "{{ home_dir }}/tpch"
+
+# The location for data generator binary.
+binary_dir: "{{ home_dir }}/{{ generator }}"
+
+# The location for each local generator script on each machine.
+localgen: "{{ binary_dir }}/gen.sh"
+
+# The local data generation command.
+localgencmd: "gen.sh"
+
+# The url for the TPC-H benchmark.
+tpch_url: <to be filed>
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml
new file mode 100644
index 0000000..ef12ec8
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_centralized.yml
@@ -0,0 +1,41 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+# Generates a loading statement for a given dataset with a single file.
+# This script assumes there is an input variable: {{ dataset }}.
+
+- include_vars: settings.yml
+- include_vars: ../gen/settings.yml
+
+- name: Set target DML file name
+  set_fact:
+     target_file: "{{ dml_dir }}/load{{ dataset }}.sqlpp"
+
+- name: Set file path for dataset "{{ dataset }}"
+  set_fact:
+     file_path: "1://{{ data_dir }}/{{ dataset|lower }}.tbl"
+
+- name: Create DML for dataset {{ dataset }}
+  shell: cp template.sqlpp "{{ target_file }}"
+
+- name: Set dataset name to "{{ dataset }}"
+  shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<DATASET>|{{ dataset }}|g'
+
+- name: Set file paths for dataset "{{ dataset }}"
+  shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<FILES>|{{ file_path }}|g'

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml
new file mode 100644
index 0000000..118a7c9
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/gendml_partitioned.yml
@@ -0,0 +1,54 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+# Generates a loading statement for a given dataset with partitioned files.
+# This script assumes there is an input variable: {{ dataset }}.
+
+- include_vars: settings.yml
+- include_vars: ../gen/settings.yml
+
+- name: Create path file
+  shell: printf "" > "{{ path_file }}"
+
+- name: Generate partition-dependent file path
+  shell: echo "{{ inner_item.0 + 1  }}://{{ data_dir }}/{{ dataset|lower }}.tbl.{{ inner_item.0 + 1  }}" >> "{{ path_file }}"
+  with_indexed_items: "{{ groups['ncs'] }}"
+  loop_control:
+     loop_var: inner_item
+
+- name: Put together file paths
+  set_fact:
+     contents: "{{ lookup('file', path_file ) }}"
+
+- name: Concatenate file paths
+  set_fact:
+     paths: "{{ contents.split('\n')|join(',') }}"
+
+- name: Set target DML file name
+  set_fact:
+     target_file: "{{ dml_dir }}/load{{ dataset }}.sqlpp"
+
+- name: Create DML for dataset {{ dataset }}
+  shell: cp template.sqlpp "{{ target_file }}"
+
+- name: Set dataset name to "{{ dataset }}"
+  shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<DATASET>|{{ dataset }}|g'
+
+- name: Set file paths for dataset "{{ dataset }}"
+  shell: find -P "{{ target_file }}" | xargs perl -pi -e 's|<FILES>|{{ paths }}|g'

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml
new file mode 100644
index 0000000..ff22a7d
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/load.yml
@@ -0,0 +1,35 @@
+# ------------------------------------------------------------
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements.  See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership.  The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License.  You may obtain a copy of the License at
+#
+#   http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied.  See the License for the
+# specific language governing permissions and limitations
+# under the License.
+# ------------------------------------------------------------
+
+- hosts: [localhost,]
+  vars:
+    runquery: ../../../ansible/runquery.yml
+  tasks:
+    - include_vars: settings.yml
+    - include_vars: ../gen/settings.yml
+
+    - name: Generate loading DML statements
+      include: loadgen.yml
+
+    - name: Load all datasets
+      shell: "ansible-playbook -i {{ inventory }} {{ runquery }} --extra-vars=\"query_file={{ dml }} report=false\""
+      with_fileglob:
+          - "{{ dml_dir }}/*"
+      loop_control:
+          loop_var: dml

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml
new file mode 100644
index 0000000..468b7f2
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/loadgen.yml
@@ -0,0 +1,41 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+- name: Clear DML directory
+  file:
+    path: "{{ dml_dir }}"
+    state: absent
+
+- name: Ensure DML directory exits
+  file:
+    path: "{{ dml_dir }}"
+    state: directory
+
+- name: Generate DML for datasets with partitioned files
+  include: gendml_partitioned.yml dataset="{{ outer_item }}"
+  with_items: "{{ partitioned_datasets }}"
+  loop_control:
+      loop_var: outer_item
+
+- name: Generate DML for datasets with a single file
+  include: gendml_centralized.yml dataset="{{ outer_item }}"
+  with_items: "{{ centralized_datasets }}"
+  loop_control:
+      loop_var: outer_item
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml
new file mode 100644
index 0000000..17a3638
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/settings.yml
@@ -0,0 +1,37 @@
+# ------------------------------------------------------------
+# 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.
+# ------------------------------------------------------------
+
+# Datasets with partitioned files.
+partitioned_datasets: ["LineItem", "Orders", "Customer", "Part", "Partsupp", "Supplier"]
+
+# Datasets with a single file.
+centralized_datasets: ["Region", "Nation"]
+
+# Temp directory.
+temp_dir: "/tmp/asterixdb"
+
+# Temp file to store all data paths.
+path_file:  "{{ temp_dir }}/paths"
+
+# Temp directory for generated DDLs.
+dml_dir: "{{ temp_dir }}/dmls"
+
+# The prefix for ddl files.
+ddl_prefix: "create"
+

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp
new file mode 100644
index 0000000..1b5d867
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/load/template.sqlpp
@@ -0,0 +1,18 @@
+/*
+ * Copyright by The Regents of the University of California
+ * Licensed 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 from
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+use tpch;
+
+load dataset <DATASET> using localfs ((`path`=`<FILES>`),(`format`=`delimited-text`),(`delimiter`=`|`));

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp
new file mode 100644
index 0000000..2695bf6
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q1.sqlpp
@@ -0,0 +1,37 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT  l.l_returnflag,
+        l.l_linestatus,
+        sum(l.l_quantity) AS sum_qty,
+        sum(l.l_extendedprice) AS sum_base_price,
+        sum(l.l_extendedprice * (1 - l.l_discount)) AS sum_disc_price,
+        sum(l.l_extendedprice * (1 - l.l_discount) * (1 + l.l_tax)) AS sum_charge,
+        avg(l.l_quantity) AS ave_qty,
+        avg(l.l_extendedprice) AS ave_price,
+        avg(l.l_discount) AS ave_disc,
+        count(l) AS count_order
+FROM  LineItem AS l
+WHERE l.l_shipdate /*+ skip-index */ <= '1998-09-02'
+/* +hash */
+GROUP BY l.l_returnflag, l.l_linestatus
+ORDER BY l.l_returnflag, l.l_linestatus
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp
new file mode 100644
index 0000000..a07ad2e
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q10.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT c_custkey, c_name,
+       sum(locn.l_extendedprice * (1 - locn.l_discount)) AS revenue,
+       c_acctbal, n_name, c_address, c_phone, c_comment
+FROM  (
+        SELECT ocn.c_custkey, ocn.c_name, ocn.c_acctbal, ocn.n_name, ocn.c_address, ocn.c_phone, ocn.c_comment, l.l_extendedprice,
+               l.l_discount
+        FROM  LineItem AS l,
+              (
+                SELECT  c.c_custkey, c.c_name, c.c_acctbal, n.n_name, c.c_address, c.c_phone, c.c_comment, o.o_orderkey
+                FROM  Orders as o,
+                      Customer as c,
+                      Nation as n
+                WHERE c.c_custkey = o.o_custkey AND o.o_orderdate >= '1993-10-01'
+                  AND o.o_orderdate < '1994-01-01' AND c.c_nationkey = n.n_nationkey
+              ) AS ocn
+        WHERE l.l_orderkey = ocn.o_orderkey and l.l_returnflag = 'R'
+      ) AS locn
+GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
+ORDER BY revenue DESC
+LIMIT 20
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.sqlpp
new file mode 100644
index 0000000..a6c0308
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q11.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.
+ */
+
+USE tpch;
+
+WITH sum AS (
+      SELECT VALUE SUM(ps.ps_supplycost * ps.ps_availqty)
+      FROM  Partsupp AS ps,
+            (
+                SELECT s.s_suppkey
+                FROM  Supplier as s,
+                      Nation as n
+                WHERE s.s_nationkey = n.n_nationkey AND n.n_name = 'GERMANY'
+            ) AS sn
+      WHERE ps.ps_suppkey = sn.s_suppkey
+)[0]
+
+
+SELECT ps_partkey, SUM(ps.ps_supplycost * ps.ps_availqty) AS part_value
+FROM Partsupp ps,
+     (
+        SELECT s.s_suppkey
+        FROM  Supplier AS s,
+              Nation as n
+        WHERE s.s_nationkey = n.n_nationkey and n.n_name = 'GERMANY'
+    ) sn
+WHERE ps.ps_suppkey = sn.s_suppkey
+GROUP BY ps.ps_partkey
+HAVING part_value > sum * 0.0001000
+ORDER BY part_value DESC
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp
new file mode 100644
index 0000000..f289c79
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q12.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT l.l_shipmode,
+       sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 1 ELSE 0 END) high_line_count,
+       sum(CASE WHEN o.o_orderpriority = '1-URGENT' or o.o_orderpriority = '2-HIGH' THEN 0 ELSE 1 END) low_line_count
+FROM  LineItem l,
+      Orders o
+WHERE o.o_orderkey = l.l_orderkey AND l.l_commitdate < l.l_receiptdate AND
+      l.l_shipdate < l.l_commitdate AND l.l_receiptdate >= '1994-01-01' AND
+      l.l_receiptdate < '1995-01-01' AND (l.l_shipmode = 'MAIL' OR l.l_shipmode = 'SHIP')
+GROUP BY l.l_shipmode
+ORDER BY l.l_shipmode
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp
new file mode 100644
index 0000000..fd6c657
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q13.sqlpp
@@ -0,0 +1,40 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT c_count, COUNT(gco) AS custdist
+FROM  (
+        SELECT c_custkey, SUM(o_orderkey_count) AS c_count
+        FROM  (
+                SELECT c.c_custkey,
+                       COLL_COUNT(
+                           (
+                            select element o.o_orderkey
+                            from  Orders o
+                            where c.c_custkey = o.o_custkey and o.o_comment NOT LIKE '%special%requests%'
+                           )
+                         ) AS o_orderkey_count
+                from  Customer c
+        ) co
+        GROUP BY c_custkey
+) gco
+GROUP BY c_count
+ORDER BY custdist DESC,c_count DESC
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp
new file mode 100644
index 0000000..f7b1214
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q14.sqlpp
@@ -0,0 +1,28 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT 100.0 *
+     SUM( CASE WHEN p.p_type LIKE 'PROMO%' THEN l.l_extendedprice * (1 - l.l_discount) ELSE 0.0 END )
+     / SUM(l.l_extendedprice * (1 - l.l_discount))
+FROM  LineItem l,
+      Part p
+WHERE l.l_partkey = p.p_partkey AND l.l_shipdate >= '1995-09-01' AND l.l_shipdate < '1995-10-01'
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp
new file mode 100644
index 0000000..7955a0b
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q15.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+WITH revenue AS (
+    SELECT l_suppkey AS supplier_no,
+           sum(l.l_extendedprice * (1 - l.l_discount)) AS total_revenue
+    FROM  LineItem l
+    WHERE l.l_shipdate >= '1996-01-01' and l.l_shipdate < '1996-04-01'
+    GROUP BY l.l_suppkey l_suppkey
+),
+m AS (
+      SELECT ELEMENT max(r2.total_revenue)
+      FROM revenue r2
+)[0]
+
+SELECT s.s_suppkey, s.s_name, s.s_address, s.s_phone, r.total_revenue
+FROM  Supplier s,
+      revenue r
+WHERE s.s_suppkey = r.supplier_no AND r.total_revenue < m + 0.000000001
+      AND r.total_revenue > m - 0.000000001
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.sqlpp
new file mode 100644
index 0000000..266b2c3
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q16.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.
+ */
+
+USE tpch;
+
+WITH tmp AS
+(
+    SELECT psp.p_brand, psp.p_type, psp.p_size, psp.ps_suppkey
+    FROM  (
+            SELECT p.p_brand, p.p_type, p.p_size, ps.ps_suppkey
+            FROM  Partsupp ps,
+                  Part p
+            WHERE p.p_partkey = ps.ps_partkey AND p.p_brand != 'Brand#45' AND
+                  p.p_type NOT LIKE 'MEDIUM POLISHED%'
+           ) AS psp,
+           Supplier s
+    WHERE psp.ps_suppkey = s.s_suppkey AND s.s_comment NOT LIKE '%Customer%Complaints%'
+)
+
+SELECT p_brand, p_type, p_size, count(ps_suppkey) supplier_cnt
+FROM  (
+    SELECT p_brand, p_type, p_size, ps_suppkey
+    FROM  tmp
+    WHERE p_size = 49 OR p_size = 14 OR p_size = 23 OR p_size = 45 OR p_size = 19
+          OR p_size = 3 OR p_size = 36 OR p_size = 9
+    GROUP BY p_brand, p_type, p_size, ps_suppkey
+) AS t2
+GROUP BY p_brand, p_type, p_size
+ORDER BY supplier_cnt DESC, p_brand, p_type, p_size
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp
new file mode 100644
index 0000000..46b79b5
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q17.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+
+WITH tmp AS
+(
+    SELECT l_partkey t_partkey, 0.2 * avg(l_quantity) t_avg_quantity
+    FROM LineItem
+    GROUP BY l_partkey
+)
+
+SELECT SUM(l.l_extendedprice) / 7.0
+FROM  tmp t,
+      LineItem l,
+      Part p
+WHERE p.p_partkey = l.l_partkey AND p.p_container = 'MED BOX' AND p.p_brand = 'Brand#23'
+      AND l.l_partkey = t.t_partkey AND l.l_quantity < t.t_avg_quantity
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis
new file mode 100644
index 0000000..5c4f9cb
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q18.sqlpp.dis
@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+// Error: premature file / JVM crash
+
+USE tpch;
+
+WITH tmp AS
+(
+    SELECT l_orderkey, SUM(l_quantity) t_sum_quantity
+    FROM  LineItem
+    GROUP BY l_orderkey
+)
+
+SELECT c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice,
+       SUM(l.l_quantity) sum_quantity
+FROM  Customer c
+JOIN  Orders o ON c.c_custkey = o.o_custkey
+JOIN  tmp t ON o.o_orderkey = t.l_orderkey
+JOIN  LineItem l ON t.l_orderkey = l.l_orderkey
+WHERE t.t_sum_quantity > 30
+GROUP BY c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice
+ORDER BY o.o_totalprice DESC, o.o_orderdate
+LIMIT 100
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q19.sqlpp
new file mode 100644
index 0000000..3eb3571
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/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.
+ */
+
+USE tpch;
+
+WITH tmp AS
+(
+SELECT
+    l_partkey AS lpkey,
+    l_quantity AS quantity,
+    l_extendedprice AS extndprice,
+    l_discount AS discount
+FROM LineItem
+WHERE (l_shipmode = 'AIR' or l_shipmode = 'AIR REG')
+AND l_shipinstruct = 'DELIVER IN PERSON'
+)
+
+SELECT SUM(l.extndprice * (1 - l.discount))
+FROM tmp l
+JOIN Part p
+ON p.p_partkey = l.lpkey
+WHERE
+  (
+    p.p_brand = 'Brand#12'
+    AND regexp_contains(p.p_container, 'SM CASE|SM BOX|SM PACK|SM PKG')
+    AND l.quantity >= 1 and l.quantity <= 11
+    AND p.p_size >= 1 and p.p_size <= 5
+  )
+  OR
+  (
+    p.p_brand = 'Brand#23'
+    AND regexp_contains(p.p_container, 'MED BAG|MED BOX|MED PKG|MED PACK')
+    AND l.quantity >= 10 and l.quantity <= 20
+    AND p.p_size >= 1 and p.p_size <= 10
+  )
+  OR
+  (
+    p.p_brand = 'Brand#34'
+    AND regexp_contains(p.p_container, 'LG CASE|LG BOX|LG PACK|LG PKG')
+    AND l.quantity >= 20 and l.quantity <= 30
+    AND p.p_size >= 1 and p.p_size <= 15
+  )
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis
new file mode 100644
index 0000000..ec11738
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q2.sqlpp.dis
@@ -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.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+WITH q2_minimum_cost_supplier_tmp1 AS
+(
+ SELECT s.s_acctbal, s.s_name, n.n_name, p.p_partkey, ps.ps_supplycost, p.p_mfgr, s.s_address, s.s_phone, s.s_comment
+ FROM
+  Nation n JOIN Region r
+  ON n.n_regionkey = r.r_regionkey and r.r_name = 'EUROPE'
+  JOIN Supplier s
+  ON s.s_nationkey = n.n_nationkey
+  JOIN Partsupp ps
+  ON s.s_suppkey = ps.ps_suppkey
+  JOIN Part p
+  ON p.p_partkey = ps.ps_partkey AND p.p_type LIKE '%BRASS' AND p.p_size = 15
+),
+q2_minimum_cost_supplier_tmp2 AS
+(
+ SELECT p.p_partkey, min(p.ps_supplycost) AS ps_min_supplycost
+ FROM
+  q2_minimum_cost_supplier_tmp1 p
+ GROUP BY p.p_partkey
+)
+
+SELECT t1.s_acctbal, t1.s_name, t1.n_name, t1.p_partkey, t1.p_mfgr AS p_mfgr, t1.s_address, t1.s_phone, t1.s_comment
+FROM
+  q2_minimum_cost_supplier_tmp1 t1 JOIN q2_minimum_cost_supplier_tmp2 t2
+ON
+  t1.p_partkey = t2.p_partkey AND t1.ps_supplycost=t2.ps_min_supplycost
+ORDER BY t1.s_acctbal DESC, t1.n_name, t1.s_name, t1.p_partkey
+LIMIT 100;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
new file mode 100644
index 0000000..45c4740
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q20.sqlpp.dis
@@ -0,0 +1,64 @@
+/*
+ * 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.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+WITH q20_tmp1 AS
+(
+  SELECT DISTINCT p_partkey
+  FROM Part
+  WHERE p_name LIKE 'forest%'
+)
+,
+q20_tmp2 AS
+(
+  SELECT l_partkey, l_suppkey, 0.5 * sum(l_quantity) AS sum_quantity
+  FROM LineItem
+  WHERE l_shipdate >= '1994-01-01' and l_shipdate < '1995-01-01'
+  GROUP BY l_partkey l_partkey, l_suppkey l_suppkey
+)
+,
+q20_tmp3 AS
+(
+  SELECT ps_suppkey, ps_availqty, t2.sum_quantity
+  FROM Partsupp
+  JOIN q20_tmp1 t1
+  ON ps_partkey = t1.p_partkey
+  JOIN q20_tmp2 t2
+  ON ps_partkey = t2.l_partkey and ps_suppkey = t2.l_suppkey
+)
+,
+q20_tmp4 AS
+(
+  SELECT ps_suppkey
+  FROM q20_tmp3
+  WHERE ps_availqty > sum_quantity
+  GROUP BY ps_suppkey
+)
+
+SELECT s.s_name, s.s_address
+FROM Supplier s
+JOIN Nation n
+ON s.s_nationkey = n.n_nationkey
+JOIN q20_tmp4 t4
+ON s.s_suppkey = t4.ps_suppkey
+WHERE n.n_name = 'CANADA'
+ORDER BY s.s_name;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
new file mode 100644
index 0000000..768ad47
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q21.sqlpp.dis
@@ -0,0 +1,71 @@
+/*
+ * 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.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+WITH tmp1 AS
+(
+    SELECT l_orderkey,
+           COUNT(l_suppkey) AS count_suppkey,
+           MAX(l_suppkey) AS max_suppkey
+    FROM  (
+            SELECT l_orderkey, l_suppkey
+            from  LineItem l
+            GROUP BY l_orderkey, l_suppkey
+          ) AS l2
+    GROUP BY l_orderkey
+),
+tmp2 AS
+(
+    SELECT l2.l_orderkey,
+           COUNT(l_suppkey) AS count_suppkey,
+           MAX(l_suppkey) AS max_suppkey
+    FROM  (
+            SELECT l_orderkey, l_suppkey
+            FROM LineItem l
+            WHERE l_receiptdate > l_commitdate
+            GROUP BY l_orderkey, l_suppkey
+          ) AS l2
+    GROUP BY l_orderkey
+)
+
+SELECT t4.s_name, COUNT(*) AS numwait
+FROM  (
+    SELECT t3.s_name, l_suppkey, t2.l_orderkey, count_suppkey, max_suppkey
+    FROM  (
+            SELECT ns.s_name, t1.l_orderkey, t1.l_suppkey
+            FROM  LineItem l,
+                  (
+                        SELECT s.s_name, s.s_suppkey
+                        FROM Nation n, Supplier s
+                        WHERE s.s_nationkey = n.n_nationkey AND n.n_name='SAUDI ARABIA'
+                   ) AS ns,
+                   Orders o,
+                   tmp1 AS t1
+            WHERE ns.s_suppkey = l.l_suppkey AND l.l_receiptdate > l.l_commitdate
+                  AND o.o_orderkey = t1.l_orderkey AND l.l_orderkey = t1.l_orderkey
+                  AND o.o_orderstatus = 'F'
+          ) AS t3
+     JOIN tmp2 AS t2 ON count_suppkey >= 0 AND t3.l_orderkey = t2.l_orderkey
+) AS t4
+GROUP BY t4.s_name
+ORDER BY numwait DESC, t4.s_name
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis
new file mode 100644
index 0000000..196caae
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q22.sqlpp.dis
@@ -0,0 +1,43 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+WITH q22_customer_tmp AS
+(
+    SELECT c_acctbal, c_custkey, cntrycode
+    FROM  Customer
+    LET cntrycode = substring(c_phone,1,2)
+    WHERE  cntrycode = '13' OR cntrycode = '31' OR cntrycode = '23' OR cntrycode = '29' OR cntrycode = '30' OR cntrycode = '18' OR cntrycode = '17'
+),
+avg AS (
+        SELECT ELEMENT AVG(c_acctbal)
+        FROM  Customer
+        LET cntrycode = substring(c_phone,1,2)
+        WHERE c_acctbal > 0.0 AND (cntrycode = '13' OR cntrycode = '31' OR cntrycode = '23' OR cntrycode = '29' OR cntrycode = '30' OR cntrycode = '18' OR cntrycode = '17')
+)[0]
+
+SELECT  cntrycode, count(ct) AS numcust, SUM(c_acctbal) AS totacctbal
+FROM  q22_customer_tmp AS ct
+WHERE ct.c_acctbal > avg AND EXISTS ( SELECT * FROM  Orders as o WHERE ct.c_custkey = o.o_custkey )
+GROUP BY cntrycode
+ORDER BY cntrycode
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp
new file mode 100644
index 0000000..82e5ef8
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q3.sqlpp
@@ -0,0 +1,31 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT l.l_orderkey, sum(l.l_extendedprice * (1 - l.l_discount)) AS revenue, o.o_orderdate, o.o_shippriority
+FROM  Customer AS c,
+      Orders AS o,
+      LineItem AS l
+where c.c_mktsegment = 'BUILDING' AND c.c_custkey = o.o_custkey AND l.l_orderkey = o.o_orderkey
+      AND o.o_orderdate < '1995-03-15' AND l.l_shipdate > '1995-03-15'
+GROUP BY l.l_orderkey, o.o_orderdate, o.o_shippriority
+ORDER BY revenue DESC,o_orderdate
+LIMIT 10
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp
new file mode 100644
index 0000000..d01b502
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q4.sqlpp
@@ -0,0 +1,36 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+WITH tmp AS
+(
+    SELECT l.l_orderkey AS o_orderkey
+    FROM  LineItem AS l
+    WHERE l.l_commitdate < l.l_receiptdate
+)
+
+SELECT o.o_orderpriority, count(o) AS count
+FROM Orders AS o
+JOIN tmp AS t
+ON o.o_orderkey = t.o_orderkey
+WHERE o.o_orderdate >= '1993-07-01' AND o.o_orderdate < '1993-10-01'
+GROUP BY o.o_orderpriority
+ORDER BY o_orderpriority
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis
new file mode 100644
index 0000000..3b9e60d
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q5.sqlpp.dis
@@ -0,0 +1,45 @@
+/*
+ * 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.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+SELECT n_name, sum(o1.l_extendedprice * (1 - o1.l_discount)) AS revenue
+FROM
+  Customer c JOIN
+    ( SELECT l1.n_name, l1.l_extendedprice, l1.l_discount, l1.s_nationkey, o.o_custkey
+      FROM Orders o JOIN
+      ( SELECT s1.n_name, l.l_extendedprice, l.l_discount, l.l_orderkey, s1.s_nationkey
+        FROM LineItem l JOIN
+            (   SELECT  n1.n_name, s.s_suppkey, s.s_nationkey
+                FROM Supplier s JOIN
+                (   SELECT n.n_name, n.n_nationkey
+                    FROM Nation n JOIN Region r
+                    ON n.n_regionkey = r.r_regionkey
+                        AND r.r_name = 'ASIA'
+                ) n1 ON s.s_nationkey = n1.n_nationkey
+            ) s1
+        ON l.l_suppkey = s1.s_suppkey
+      ) l1 ON l1.l_orderkey = o.o_orderkey AND o.o_orderdate >= '1994-01-01'
+              AND o.o_orderdate < '1995-01-01'
+   ) o1
+ON c.c_nationkey = o1.s_nationkey AND c.c_custkey = o1.o_custkey
+GROUP BY o1.n_name AS n_name
+ORDER BY revenue DESC;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp
new file mode 100644
index 0000000..2124bf2
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q6.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+USE tpch;
+
+SELECT SUM(l.l_extendedprice * l.l_discount)
+FROM  LineItem AS l
+WHERE l.l_shipdate >= '1994-01-01'
+      AND l.l_shipdate < '1995-01-01'
+      AND l.l_discount >= 0.05
+      AND l.l_discount <= 0.07
+      AND l.l_quantity < 24
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis
new file mode 100644
index 0000000..bc6a7a6
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q7.sqlpp.dis
@@ -0,0 +1,58 @@
+/*
+ * 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.
+ */
+
+// Error: sporadically dead node.
+
+USE tpch;
+
+WITH q7_volume_shipping_tmp AS
+(
+    SELECT n1.n_name AS supp_nation,
+           n2.n_name AS cust_nation,
+           n1.n_nationkey AS s_nationkey,
+           n2.n_nationkey AS c_nationkey
+    FROM  Nation as n1,
+          Nation as n2
+    WHERE (n1.n_name='FRANCE' AND n2.n_name='GERMANY') OR (n1.n_name='GERMANY' AND n2.n_name='FRANCE')
+)
+
+SELECT supp_nation, cust_nation, l_year, sum(volume) AS revenue
+FROM
+  (
+    SELECT t.supp_nation, t.cust_nation, GET_YEAR(l3.l_shipdate) AS l_year,
+           l3.l_extendedprice * (1 - l3.l_discount) AS volume
+    FROM (
+           SELECT l2.l_shipdate, l2.l_extendedprice, l2.l_discount, l2.c_nationkey, s.s_nationkey
+           FROM Supplier s JOIN
+             (
+              SELECT l1.l_shipdate, l1.l_extendedprice, l1.l_discount, l1.l_suppkey, c.c_nationkey
+              FROM Customer c JOIN
+                 (
+                   SELECT l.l_shipdate, l.l_extendedprice, l.l_discount, l.l_suppkey, o.o_custkey
+                   FROM Orders o
+                   JOIN LineItem l ON o.o_orderkey = l.l_orderkey AND l.l_shipdate >= '1995-01-01'
+                        AND l.l_shipdate <= '1996-12-31'
+               ) l1 ON c.c_custkey = l1.o_custkey
+            ) l2 ON s.s_suppkey = l2.l_suppkey
+          ) l3
+          JOIN q7_volume_shipping_tmp t
+          ON t.c_nationkey = l3.c_nationkey AND t.s_nationkey = l3.s_nationkey
+   ) shipping
+GROUP BY supp_nation, cust_nation, l_year
+ORDER BY supp_nation, cust_nation, l_year;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis
new file mode 100644
index 0000000..60b399a
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q8.sqlpp.dis
@@ -0,0 +1,67 @@
+/*
+ * 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.
+ */
+
+// Error: budget exceeds
+
+USE tpch;
+
+SELECT  year,
+        SUM( CASE t.s_name = 'BRAZIL' WHEN true THEN t.revenue ELSE 0.0 END ) / SUM(t.revenue) AS mkt_share
+FROM  (
+        SELECT o_year AS year,
+               slnrcop.l_extendedprice * (1 - slnrcop.l_discount) AS revenue,
+               n2.n_name AS s_name
+        FROM  (
+                SELECT lnrcop.o_orderdate, lnrcop.l_discount, lnrcop.l_extendedprice, lnrcop.l_suppkey, s.s_nationkey
+                FROM  Supplier s,
+                (
+                  SELECT lnrco.o_orderdate, lnrco.l_discount, lnrco.l_extendedprice, lnrco.l_suppkey
+                  FROM  (
+                         SELECT nrco.o_orderdate, l.l_partkey, l.l_discount, l.l_extendedprice, l.l_suppkey
+                         FROM  LineItem l,
+                               (
+                                SELECT o.o_orderdate, o.o_orderkey
+                                FROM  Orders o,
+                                      (
+                                        SELECT c.c_custkey
+                                        FROM  Customer c,
+                                              (
+                                                SELECT n.n_nationkey
+                                                FROM  Nation n,
+                                                      Region r
+                                                WHERE n.n_regionkey = r.r_regionkey AND r.r_name = 'AMERICA'
+                                                ) AS nr
+                                         WHERE c.c_nationkey = nr.n_nationkey
+                                       ) AS nrc
+                                WHERE nrc.c_custkey = o.o_custkey
+                                ) AS nrco
+                        WHERE l.l_orderkey = nrco.o_orderkey and nrco.o_orderdate >= '1995-01-01' and nrco.o_orderdate < '1996-12-31'
+                        ) AS lnrco,
+                        Part p
+                        WHERE p.p_partkey = lnrco.l_partkey and p.p_type = 'ECONOMY ANODIZED STEEL'
+                    ) AS lnrcop
+                WHERE s.s_suppkey = lnrcop.l_suppkey
+                ) AS slnrcop,
+                Nation n2
+                LET o_year = `get-year`(slnrcop.o_orderdate)
+                WHERE slnrcop.s_nationkey = n2.n_nationkey
+             ) as t
+GROUP BY year
+ORDER BY year
+;

http://git-wip-us.apache.org/repos/asf/asterixdb/blob/73715d87/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis
----------------------------------------------------------------------
diff --git a/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis
new file mode 100644
index 0000000..80a37f4
--- /dev/null
+++ b/asterixdb/asterix-benchmark/src/main/resources/benchmarks/tpch/queries/q9.sqlpp.dis
@@ -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.
+ */
+
+// Error: no space left
+
+USE tpch;
+
+SELECT nation, o_year, SUM(amount) AS sum_profit
+FROM
+  (
+    SELECT l3.n_name AS nation,
+           GET_YEAR(o.o_orderdate) AS o_year,
+           l3.l_extendedprice * (1 - l3.l_discount) -  l3.ps_supplycost * l3.l_quantity AS amount
+    FROM
+      Orders o JOIN
+      (
+       SELECT l2.l_extendedprice, l2.l_discount, l2.l_quantity, l2.l_orderkey, l2.n_name,l2. ps_supplycost
+       FROM Part p JOIN
+         (SELECT l1.l_extendedprice, l1.l_discount, l1.l_quantity, l1.l_partkey, l1.l_orderkey, l1.n_name, ps.ps_supplycost
+          FROM Partsupp ps join
+            (SELECT l.l_suppkey, l.l_extendedprice, l.l_discount, l.l_quantity, l.l_partkey, l.l_orderkey, s1.n_name
+             FROM
+               (SELECT s.s_suppkey, n.n_name
+                FROM Nation n JOIN Supplier s ON n.n_nationkey = s.s_nationkey
+               ) s1 JOIN LineItem l ON s1.s_suppkey = l.l_suppkey
+            ) l1 ON ps.ps_suppkey = l1.l_suppkey AND ps.ps_partkey = l1.l_partkey
+         ) l2 ON contains(p.p_name,'green') AND p.p_partkey = l2.l_partkey
+     ) l3 ON o.o_orderkey = l3.l_orderkey
+  ) profit
+GROUP BY nation, o_year
+ORDER BY nation, o_year desc;


Mime
View raw message