diff options
Diffstat (limited to 'exec/java-exec/src/test')
32 files changed, 1309 insertions, 51 deletions
diff --git a/exec/java-exec/src/test/java/org/apache/drill/BaseTestQuery.java b/exec/java-exec/src/test/java/org/apache/drill/BaseTestQuery.java new file mode 100644 index 000000000..f724ca41e --- /dev/null +++ b/exec/java-exec/src/test/java/org/apache/drill/BaseTestQuery.java @@ -0,0 +1,93 @@ +/** + * 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. + */ +package org.apache.drill; + +import java.io.IOException; +import java.net.URL; + +import org.apache.drill.common.util.TestTools; +import org.apache.drill.exec.client.QuerySubmitter; +import org.apache.drill.exec.store.ResourceInputStream; +import org.junit.Rule; +import org.junit.rules.TestRule; + +import com.google.common.base.Charsets; +import com.google.common.io.Resources; + +public class BaseTestQuery { + static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(BaseTestQuery.class); + + @Rule public final TestRule TIMEOUT = TestTools.getTimeoutRule(10000); + + protected void test(String sql) throws Exception{ + boolean good = false; + sql = sql.replace("[WORKING_PATH]", TestTools.getWorkingPath()); + + try{ + QuerySubmitter s = new QuerySubmitter(); + s.submitQuery(null, sql, "sql", null, true, 1, "tsv"); + good = true; + }finally{ + if(!good) Thread.sleep(2000); + } + } + + protected void testLogical(String logical) throws Exception{ + boolean good = false; + logical = logical.replace("[WORKING_PATH]", TestTools.getWorkingPath()); + + try{ + QuerySubmitter s = new QuerySubmitter(); + s.submitQuery(null, logical, "logical", null, true, 1, "tsv"); + good = true; + }finally{ + if(!good) Thread.sleep(2000); + } + } + + protected void testPhysical(String physical) throws Exception{ + boolean good = false; + physical = physical.replace("[WORKING_PATH]", TestTools.getWorkingPath()); + + try{ + QuerySubmitter s = new QuerySubmitter(); + s.submitQuery(null, physical, "physical", null, true, 1, "tsv"); + good = true; + }finally{ + if(!good) Thread.sleep(2000); + } + } + + protected void testPhysicalFromFile(String file) throws Exception{ + testPhysical(getFile(file)); + } + protected void testLogicalFromFile(String file) throws Exception{ + testLogical(getFile(file)); + } + protected void testSqlFromFile(String file) throws Exception{ + test(getFile(file)); + } + + protected String getFile(String resource) throws IOException{ + URL url = Resources.getResource(resource); + if(url == null){ + throw new IOException(String.format("Unable to find path %s.", resource)); + } + return Resources.toString(url, Charsets.UTF_8); + } +} diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java index be5127634..78958972d 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java +++ b/exec/java-exec/src/test/java/org/apache/drill/TestExampleQueries.java @@ -23,16 +23,18 @@ import org.junit.Rule; import org.junit.Test; import org.junit.rules.TestRule; -public class TestExampleQueries { +public class TestExampleQueries extends BaseTestQuery{ static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestExampleQueries.class); - @Rule public TestRule TIMEOUT = TestTools.getTimeoutRule(10000); @Test public void testSelectWithLimit() throws Exception{ test("select * from cp.`employee.json` limit 5"); } + + + @Test public void testJoin() throws Exception{ test("SELECT\n" + @@ -56,17 +58,4 @@ public class TestExampleQueries { test("select marital_status, COUNT(1) as cnt from cp.`employee.json` group by marital_status"); } - private void test(String sql) throws Exception{ - boolean good = false; - sql = sql.replace("[WORKING_PATH]", TestTools.getWorkingPath()); - - try{ - QuerySubmitter s = new QuerySubmitter(); - s.submitQuery(null, sql, "sql", null, true, 1, "tsv"); - good = true; - }finally{ - if(!good) Thread.sleep(2000); - } - } - } diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestTpchQueries.java b/exec/java-exec/src/test/java/org/apache/drill/TestTpchQueries.java new file mode 100644 index 000000000..346ed56f5 --- /dev/null +++ b/exec/java-exec/src/test/java/org/apache/drill/TestTpchQueries.java @@ -0,0 +1,156 @@ +/** + * 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. + */ +package org.apache.drill; + +import org.junit.Ignore; +import org.junit.Test; + +public class TestTpchQueries extends BaseTestQuery{ + static final org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(TestTpchQueries.class); + + @Test + public void tpch01() throws Exception{ + testSqlFromFile("queries/tpch/01.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch02() throws Exception{ + testSqlFromFile("queries/tpch/02.sql"); + } + + @Test + @Ignore // DRILL-436 + public void tpch03() throws Exception{ + testSqlFromFile("queries/tpch/03.sql"); + } + + @Test + @Ignore // DRILL-437 + public void tpch04() throws Exception{ + testSqlFromFile("queries/tpch/04.sql"); + } + + @Test + @Ignore // DRILL-436 + public void tpch05() throws Exception{ + testSqlFromFile("queries/tpch/05.sql"); + } + + @Test // DRILL-356 + public void tpch06() throws Exception{ + testSqlFromFile("queries/tpch/06.sql"); + } + + @Test + @Ignore // DRILL-439 + public void tpch07() throws Exception{ + testSqlFromFile("queries/tpch/07.sql"); + } + + @Test + @Ignore // DRILL-356 + public void tpch08() throws Exception{ + testSqlFromFile("queries/tpch/08.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch09() throws Exception{ + testSqlFromFile("queries/tpch/09.sql"); + } + + @Test + @Ignore // DRILL-356 + public void tpch10() throws Exception{ + testSqlFromFile("queries/tpch/10.sql"); + } + + @Test + @Ignore // DRILL-436 + public void tpch11() throws Exception{ + testSqlFromFile("queries/tpch/11.sql"); + } + + @Test + @Ignore // DRILL-403 + public void tpch12() throws Exception{ + testSqlFromFile("queries/tpch/12.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch13() throws Exception{ + testSqlFromFile("queries/tpch/13.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch14() throws Exception{ + testSqlFromFile("queries/tpch/14.sql"); + } + + @Test + @Ignore // DRILL-438 + public void tpch15() throws Exception{ + testSqlFromFile("queries/tpch/15.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch16() throws Exception{ + testSqlFromFile("queries/tpch/16.sql"); + } + + @Test + @Ignore // DRILL-440 + public void tpch17() throws Exception{ + testSqlFromFile("queries/tpch/17.sql"); + } + + @Test + @Ignore // DRILL-436 + public void tpch18() throws Exception{ + testSqlFromFile("queries/tpch/18.sql"); + } + + @Test + @Ignore // DRILL-436 + public void tpch19() throws Exception{ + testSqlFromFile("queries/tpch/19.sql"); + } + + @Test + @Ignore // DRILL-435 + public void tpch20() throws Exception{ + testSqlFromFile("queries/tpch/20.sql"); + } + + @Test + @Ignore // DRILL-440 + public void tpch21() throws Exception{ + testSqlFromFile("queries/tpch/21.sql"); + } + + @Test + @Ignore // DRILL-441 + public void tpch22() throws Exception{ + testSqlFromFile("queries/tpch/22.sql"); + } + +} diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/agg/TestHashAggr.java b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/agg/TestHashAggr.java index 1688bed2c..03c8e3f25 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/agg/TestHashAggr.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/agg/TestHashAggr.java @@ -18,44 +18,37 @@ package org.apache.drill.exec.physical.impl.agg; -import static org.junit.Assert.*; - -import org.apache.drill.exec.client.QuerySubmitter; -import org.junit.AfterClass; +import org.apache.drill.BaseTestQuery; import org.junit.Test; -public class TestHashAggr { - - /* - @Test - public void test1() throws Exception { - String[] strArr = {"-f", - // "/Users/asinha/queries/hashaggr/q8.json", - // "/Users/asinha/queries/hashaggr/q8_1.json", - // "/Users/asinha/queries/hashaggr/q8_2.json", - // "/Users/asinha/queries/hashaggr/q8_3.json", - //"/Users/asinha/queries/streamaggr/q8_2.json", - // "/Users/asinha/queries/hashaggr/q5.json", - //"/Users/asinha/queries/hashaggr/q5_1.json", - // "/Users/asinha/queries/hashaggr/q5_2.json", - // "/Users/asinha/queries/streamaggr/q4_1.json", - // "/Users/asinha/queries/streamaggr/q4_3.json", - // "/Users/asinha/queries/hashaggr/q6.json", - // "/Users/asinha/queries/hashaggr/q9.json", - // "/Users/asinha/queries/hashaggr/q9_1.json", - "/Users/asinha/queries/hashaggr/q7_2.json", - "-t", - "physical", - "-local" }; - QuerySubmitter.main(strArr); +public class TestHashAggr extends BaseTestQuery{ - } - - @AfterClass - public static void tearDown() throws Exception{ - // pause to get logger to catch up. - Thread.sleep(1000); - } - */ + + @Test + public void testQ6() throws Exception{ + testPhysicalFromFile("agg/hashagg/q6.json"); + } + + @Test + public void testQ7_1() throws Exception{ + testPhysicalFromFile("agg/hashagg/q7_1.json"); + } + + @Test + public void testQ7_2() throws Exception{ + testPhysicalFromFile("agg/hashagg/q7_2.json"); + } + + + @Test + public void testQ8_1() throws Exception{ + testPhysicalFromFile("agg/hashagg/q8_1.json"); + } + + @Test + public void test8() throws Exception{ + testPhysicalFromFile("agg/hashagg/q8.json"); + } + } diff --git a/exec/java-exec/src/test/resources/agg/hashagg/q6.json b/exec/java-exec/src/test/resources/agg/hashagg/q6.json new file mode 100644 index 000000000..96f62a3e5 --- /dev/null +++ b/exec/java-exec/src/test/resources/agg/hashagg/q6.json @@ -0,0 +1,63 @@ +{ + head : { + version : 1, + generator : { + type : "optiq", + info : "na" + }, + type : "APACHE_DRILL_PHYSICAL" + }, + graph : [ { + "pop" : "parquet-scan", + "@id" : 1, + "entries" : [ { + "path" : "tpch/nation.parquet" + } ], + "storage" : { + "type" : "file", + "connection" : "classpath:///" + }, + "format" : { + "type" : "parquet" + } + }, { + pop : "project", + @id : 2, + exprs : [ { + ref : "output.$f0", + expr : "N_REGIONKEY" + }, { + ref : "output.$f1", + expr : "N_NATIONKEY" + } ], + child : 1 + }, { + pop : "hash-aggregate", + @id : 3, + child : 2, + keys : [ { + ref : "$f0", + expr : "$f0" + } ], + exprs : [ { + ref : "SUM", + expr : "sum($f1) " + }, { + ref : "MIN", + expr : "min($f1) " + }, { + ref : "MAX", + expr : "max($f1) " + }, { + ref : "COUNT", + expr : "count($f1) " + }, { + ref : "AVG", + expr : "avg($f1) " + } ] + }, { + pop : "screen", + @id : 4, + child : 3 + } ] +} diff --git a/exec/java-exec/src/test/resources/agg/hashagg/q7_1.json b/exec/java-exec/src/test/resources/agg/hashagg/q7_1.json new file mode 100644 index 000000000..f2246770a --- /dev/null +++ b/exec/java-exec/src/test/resources/agg/hashagg/q7_1.json @@ -0,0 +1,57 @@ +{ + head : { + version : 1, + generator : { + type : "optiq", + info : "na" + }, + type : "APACHE_DRILL_PHYSICAL" + }, + graph : [ { + "pop" : "parquet-scan", + "@id" : 1, + "entries" : [ { + "path" : "tpch/nation.parquet" + } ], + "storage" : { + "type" : "file", + "connection" : "classpath:///" + }, + "format" : { + "type" : "parquet" + } + }, { + pop : "project", + @id : 2, + exprs : [ { + ref : "output.$f0", + expr : "N_REGIONKEY" + }, { + ref : "output.$f1", + expr : "N_NATIONKEY" + }, { + ref : "output.$f2", + expr : "N_NAME" + } ], + child : 1 + }, { + pop : "hash-aggregate", + @id : 3, + child : 2, + keys : [ { + ref : "$f0", + expr : "$f0" + }, { + ref : "$f2", + expr : "$f2" + } ], + exprs : [ { + ref : "Y", + expr : "sum($f1) " + } ] + }, { + pop : "screen", + @id : 4, + child : 3 + } ] +} diff --git a/exec/java-exec/src/test/resources/agg/hashagg/q7_2.json b/exec/java-exec/src/test/resources/agg/hashagg/q7_2.json new file mode 100644 index 000000000..c03868d3f --- /dev/null +++ b/exec/java-exec/src/test/resources/agg/hashagg/q7_2.json @@ -0,0 +1,57 @@ +{ + head : { + version : 1, + generator : { + type : "optiq", + info : "na" + }, + type : "APACHE_DRILL_PHYSICAL" + }, + graph : [ { + "pop" : "parquet-scan", + "@id" : 1, + "entries" : [ { + "path" : "tpch/nation.parquet" + } ], + "storage" : { + "type" : "file", + "connection" : "classpath:///" + }, + "format" : { + "type" : "parquet" + } + }, { + pop : "project", + @id : 2, + exprs : [ { + ref : "output.$f0", + expr : "N_REGIONKEY" + }, { + ref : "output.$f1", + expr : "N_NAME" + }, { + ref : "output.$f2", + expr : "N_NATIONKEY" + } ], + child : 1 + }, { + pop : "hash-aggregate", + @id : 3, + child : 2, + keys : [ { + ref : "$f0", + expr : "$f0" + }, { + ref : "$f1", + expr : "$f1" + } ], + exprs : [ { + ref : "Y", + expr : "sum($f2) " + } ] + }, { + pop : "screen", + @id : 4, + child : 3 + } ] +} diff --git a/exec/java-exec/src/test/resources/agg/hashagg/q8.json b/exec/java-exec/src/test/resources/agg/hashagg/q8.json new file mode 100644 index 000000000..e097c2257 --- /dev/null +++ b/exec/java-exec/src/test/resources/agg/hashagg/q8.json @@ -0,0 +1,53 @@ +{ + head : { + version : 1, + generator : { + type : "optiq", + info : "na" + }, + type : "APACHE_DRILL_PHYSICAL" + }, + graph : [ { + "pop" : "parquet-scan", + "@id" : 1, + "entries" : [ { + "path" : "tpch/orders.parquet" + } ], + "storage" : { + "type" : "file", + "connection" : "classpath:///", + "workspaces" : null, + "formats" : null + }, + "format" : { + "type" : "parquet" + } + }, { + pop : "project", + @id : 2, + exprs : [ { + ref : "output.$f0", + expr : "O_CUSTKEY" + }, { + ref : "output.$f1", + expr : "O_ORDERKEY" + } ], + child : 1 + }, { + pop : "hash-aggregate", + @id : 3, + child : 2, + keys : [ { + ref : "$f0", + expr : "$f0" + } ], + exprs : [ { + ref : "Y", + expr : "sum($f1) " + } ] + }, { + pop : "screen", + @id : 4, + child : 3 + } ] +} diff --git a/exec/java-exec/src/test/resources/agg/hashagg/q8_1.json b/exec/java-exec/src/test/resources/agg/hashagg/q8_1.json new file mode 100644 index 000000000..7f2ae123b --- /dev/null +++ b/exec/java-exec/src/test/resources/agg/hashagg/q8_1.json @@ -0,0 +1,73 @@ +{ + head : { + version : 1, + generator : { + type : "optiq", + info : "na" + }, + type : "APACHE_DRILL_PHYSICAL" + }, + graph : [ { + "pop" : "parquet-scan", + "@id" : 1, + "entries" : [ { + "path" : "tpch/orders.parquet" + } ], + "storage" : { + "type" : "file", + "connection" : "classpath:///" + }, + "format" : { + "type" : "parquet" + } + }, { + pop : "project", + @id : 2, + exprs : [ { + ref : "output.$f0", + expr : "O_CUSTKEY" + }, { + ref : "output.$f1", + expr : "O_ORDERKEY" + } ], + child : 1 + }, { + pop : "hash-aggregate", + @id : 3, + child : 2, + keys : [ { + ref : "$f0", + expr : "$f0" + } ], + exprs : [ { + ref : "Y", + expr : "sum($f1) " + } ] + }, { + pop : "sort", + @id : 4, + child : 3, + orderings : [ { + order : "ASC", + expr : "$f0" + }, { + order : "ASC", + expr : "Y" + } ], + reverse : false + }, { + pop : "selection-vector-remover", + @id : 5, + child : 4 + }, { + pop: "limit", + @id : 6, + child: 5, + first: 0, + last: 100 + }, { + pop : "screen", + @id : 7, + child : 6 + } ] +} diff --git a/exec/java-exec/src/test/resources/queries/tpch.json b/exec/java-exec/src/test/resources/queries/tpch.json new file mode 100644 index 000000000..59894398e --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch.json @@ -0,0 +1,54 @@ +[ + + tpch1: " + +select + l_returnflag, + l_linestatus, + sum(l_quantity) as sum_qty, + sum(l_extendedprice) as sum_base_price, + sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, + sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, + avg(l_quantity) as avg_qty, + avg(l_extendedprice) as avg_price, + avg(l_discount) as avg_disc, + count(*) as count_order +from + cp.`tpch/lineitem.parquet` +where + l_shipdate <= date '1998-12-01' - interval ':1' day +group by + l_returnflag, + l_linestatus +order by + l_returnflag, + l_linestatus +LIMIT 1; + + ", + tpch1a: " + +select + l_returnflag, + l_linestatus, + sum(l_quantity) as sum_qty, + sum(l_extendedprice) as sum_base_price, + sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, + sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, + avg(l_quantity) as avg_qty, + avg(l_extendedprice) as avg_price, + avg(l_discount) as avg_disc, + count(*) as count_order +from + cp.`tpch/lineitem.parquet` +group by + l_returnflag, + l_linestatus +order by + l_returnflag, + l_linestatus +LIMIT 1; + + " + +]
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/01.sql b/exec/java-exec/src/test/resources/queries/tpch/01.sql new file mode 100644 index 000000000..b10733967 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/01.sql @@ -0,0 +1,24 @@ +-- using 1395599672 as a seed to the RNG
+
+select
+ l_returnflag,
+ l_linestatus,
+ sum(l_quantity) as sum_qty,
+ sum(l_extendedprice) as sum_base_price,
+ sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
+ sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
+ avg(l_quantity) as avg_qty,
+ avg(l_extendedprice) as avg_price,
+ avg(l_discount) as avg_disc,
+ count(*) as count_order
+from
+ cp.`tpch/lineitem.parquet`
+-- where
+-- l_shipdate <= date '1998-12-01' - interval '120' day (3)
+group by
+ l_returnflag,
+ l_linestatus
+
+order by
+ l_returnflag,
+ l_linestatus;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/02.sql b/exec/java-exec/src/test/resources/queries/tpch/02.sql new file mode 100644 index 000000000..d6e234696 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/02.sql @@ -0,0 +1,48 @@ +-- tpch2 using 1395599672 as a seed to the RNG
+select
+ s.s_acctbal,
+ s.s_name,
+ n.n_name,
+ p.p_partkey,
+ p.p_mfgr,
+ s.s_address,
+ s.s_phone,
+ s.s_comment
+from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+where
+ p.p_partkey = ps.ps_partkey
+ and s.s_suppkey = ps.ps_suppkey
+ and p.p_size = 41
+ and p.p_type like '%NICKEL'
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+ and ps.ps_supplycost = (
+
+ select
+ min(ps.ps_supplycost)
+
+ from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+ where
+ p.p_partkey = ps.ps_partkey
+ and s.s_suppkey = ps.ps_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+ )
+
+order by
+ s.s_acctbal desc,
+ n.n_name,
+ s.s_name,
+ p.p_partkey
+limit 100;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/03.sql b/exec/java-exec/src/test/resources/queries/tpch/03.sql new file mode 100644 index 000000000..f856e64af --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/03.sql @@ -0,0 +1,27 @@ +-- tpch3 using 1395599672 as a seed to the RNG
+select
+ l.l_orderkey,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue,
+ o.o_orderdate,
+ o.o_shippriority
+
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+
+where
+ c.c_mktsegment = 'HOUSEHOLD'
+ and c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+-- and o.o_orderdate < date '1995-03-25'
+-- and l.l_shipdate > date '1995-03-25'
+
+group by
+ l.l_orderkey,
+ o.o_orderdate,
+ o.o_shippriority
+order by
+ revenue desc,
+ o.o_orderdate
+limit 10;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/04.sql b/exec/java-exec/src/test/resources/queries/tpch/04.sql new file mode 100644 index 000000000..3c5957209 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/04.sql @@ -0,0 +1,24 @@ +-- tpch4 using 1395599672 as a seed to the RNG
+select
+ o_orderpriority,
+ count(*) as order_count
+from
+ cp.`tpch/orders.parquet`
+
+where
+-- o_orderdate >= date '1996-10-01'
+-- and o_orderdate < date '1996-10-01' + interval '3' month
+-- and
+ exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet`
+ where
+ l_orderkey = o_orderkey
+ and l_commitdate < l_receiptdate
+ )
+group by
+ o_orderpriority
+order by
+ o_orderpriority;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/05.sql b/exec/java-exec/src/test/resources/queries/tpch/05.sql new file mode 100644 index 000000000..ae82d39ef --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/05.sql @@ -0,0 +1,28 @@ +-- tpch5 using 1395599672 as a seed to the RNG
+select
+ n.n_name,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue
+
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n,
+ cp.`tpch/region.parquet` r
+
+where
+ c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+ and l.l_suppkey = s.s_suppkey
+ and c.c_nationkey = s.s_nationkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_regionkey = r.r_regionkey
+ and r.r_name = 'EUROPE'
+-- and o.o_orderdate >= date '1997-01-01'
+-- and o.o_orderdate < date '1997-01-01' + interval '1' year
+group by
+ n.n_name
+
+order by
+ revenue desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/06.sql b/exec/java-exec/src/test/resources/queries/tpch/06.sql new file mode 100644 index 000000000..f1780b63c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/06.sql @@ -0,0 +1,12 @@ +-- tpch6 using 1395599672 as a seed to the RNG
+
+select
+ sum(l_extendedprice * l_discount) as revenue
+from
+ cp.`tpch/lineitem.parquet`
+where
+-- l_shipdate >= date '1997-01-01'
+-- and l_shipdate < date '1997-01-01' + interval '1' year
+-- and
+ l_discount between 0.03 - 0.01 and 0.03 + 0.01
+ and l_quantity < 24;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/07.sql b/exec/java-exec/src/test/resources/queries/tpch/07.sql new file mode 100644 index 000000000..94bd51c35 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/07.sql @@ -0,0 +1,40 @@ +-- tpch7 using 1395599672 as a seed to the RNG
+select
+ supp_nation,
+ cust_nation,
+ l_year,
+ sum(volume) as revenue
+from
+ (
+ select
+ n1.n_name as supp_nation,
+ n2.n_name as cust_nation,
+ extract(year from l.l_shipdate) as l_year,
+ l.l_extendedprice * (1 - l.l_discount) as volume
+ from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/nation.parquet` n1,
+ cp.`tpch/nation.parquet` n2
+ where
+ s.s_suppkey = l.l_suppkey
+ and o.o_orderkey = l.l_orderkey
+ and c.c_custkey = o.o_custkey
+ and s.s_nationkey = n1.n_nationkey
+ and c.c_nationkey = n2.n_nationkey
+ and (
+ (n1.n_name = 'EGYPT' and n2.n_name = 'UNITED STATES')
+ or (n1.n_name = 'UNITED STATES' and n2.n_name = 'EGYPT')
+ )
+-- and l.l_shipdate between date '1995-01-01' and date '1996-12-31'
+ ) as shipping
+group by
+ supp_nation,
+ cust_nation,
+ l_year
+order by
+ supp_nation,
+ cust_nation,
+ l_year;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/08.sql b/exec/java-exec/src/test/resources/queries/tpch/08.sql new file mode 100644 index 000000000..363b5a527 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/08.sql @@ -0,0 +1,38 @@ +-- tpch8 using 1395599672 as a seed to the RNG
+select
+ o_year,
+ sum(case
+ when nation = 'EGYPT' then volume
+ else 0
+ end) / sum(volume) as mkt_share
+from
+ (
+ select
+ extract(year from o.o_orderdate) as o_year,
+ l.l_extendedprice * (1 - l.l_discount) as volume,
+ n2.n_name as nation
+ from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/nation.parquet` n1,
+ cp.`tpch/nation.parquet` n2,
+ cp.`tpch/region.parquet` r
+ where
+ p.p_partkey = l.l_partkey
+ and s.s_suppkey = l.l_suppkey
+ and l.l_orderkey = o.o_orderkey
+ and o.o_custkey = c.c_custkey
+ and c.c_nationkey = n1.n_nationkey
+ and n1.n_regionkey = r.r_regionkey
+ and r.r_name = 'MIDDLE EAST'
+ and s.s_nationkey = n2.n_nationkey
+ and o.o_orderdate between date '1995-01-01' and date '1996-12-31'
+ and p.p_type = 'PROMO BRUSHED COPPER'
+ ) as all_nations
+group by
+ o_year
+order by
+ o_year;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/09.sql b/exec/java-exec/src/test/resources/queries/tpch/09.sql new file mode 100644 index 000000000..84aca9825 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/09.sql @@ -0,0 +1,33 @@ +-- tpch9 using 1395599672 as a seed to the RNG
+select
+ nation,
+ o_year,
+ sum(amount) as sum_profit
+from
+ (
+ select
+ n_name as nation,
+ extract(year from o_orderdate) as o_year,
+ l.l_extendedprice * (1 - l.l_discount) - ps.ps_supplycost * l.l_quantity as amount
+ from
+ cp.`tpch/part.parquet` p,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/nation.parquet` n
+ where
+ s.s_suppkey = l.l_suppkey
+ and ps.ps_suppkey = l.l_suppkey
+ and ps.ps_partkey = l.l_partkey
+ and p.p_partkey = l.l_partkey
+ and o.o_orderkey = l.l_orderkey
+ and s.s_nationkey = n.n_nationkey
+ and p.p_name like '%yellow%'
+ ) as profit
+group by
+ nation,
+ o_year
+order by
+ nation,
+ o_year desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/10.sql b/exec/java-exec/src/test/resources/queries/tpch/10.sql new file mode 100644 index 000000000..dbf1aedbb --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/10.sql @@ -0,0 +1,33 @@ +-- tpch10 using 1395599672 as a seed to the RNG
+select
+ c.c_custkey,
+ c.c_name,
+ sum(l.l_extendedprice * (1 - l.l_discount)) as revenue,
+ c.c_acctbal,
+ n.n_name,
+ c.c_address,
+ c.c_phone,
+ c.c_comment
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/nation.parquet` n
+where
+ c.c_custkey = o.o_custkey
+ and l.l_orderkey = o.o_orderkey
+ and o.o_orderdate >= date '1994-03-01'
+ and o.o_orderdate < date '1994-03-01' + interval '3' month
+ and l.l_returnflag = 'R'
+ and c.c_nationkey = n.n_nationkey
+group by
+ c.c_custkey,
+ c.c_name,
+ c.c_acctbal,
+ c.c_phone,
+ n.n_name,
+ c.c_address,
+ c.c_comment
+order by
+ revenue desc
+limit 20;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/11.sql b/exec/java-exec/src/test/resources/queries/tpch/11.sql new file mode 100644 index 000000000..6064454ad --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/11.sql @@ -0,0 +1,28 @@ +-- tpch11 using 1395599672 as a seed to the RNG
+select
+ ps.ps_partkey,
+ sum(ps.ps_supplycost * ps.ps_availqty) as `value`
+from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+where
+ ps.ps_suppkey = s.s_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'JAPAN'
+group by
+ ps.ps_partkey having
+ sum(ps.ps_supplycost * ps.ps_availqty) > (
+ select
+ sum(ps.ps_supplycost * ps.ps_availqty) * 0.0001000000
+ from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+ where
+ ps.ps_suppkey = s.s_suppkey
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'JAPAN'
+ )
+order by
+ `value` desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/12.sql b/exec/java-exec/src/test/resources/queries/tpch/12.sql new file mode 100644 index 000000000..41bc5dd75 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/12.sql @@ -0,0 +1,29 @@ +-- tpch12 using 1395599672 as a seed to the RNG
+select
+ l.l_shipmode,
+ sum(case
+ when o.o_orderpriority = '1-URGENT'
+ or o.o_orderpriority = '2-HIGH'
+ then 1
+ else 0
+ end) as high_line_count,
+ sum(case
+ when o.o_orderpriority <> '1-URGENT'
+ and o.o_orderpriority <> '2-HIGH'
+ then 1
+ else 0
+ end) as low_line_count
+from
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+where
+ o.o_orderkey = l.l_orderkey
+ and l.l_shipmode in ('TRUCK', 'REG AIR')
+ and l.l_commitdate < l.l_receiptdate
+ and l.l_shipdate < l.l_commitdate
+-- and l.l_receiptdate >= date '1994-01-01'
+-- and l.l_receiptdate < date '1994-01-01' + interval '1' year
+group by
+ l.l_shipmode
+order by
+ l.l_shipmode;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/13.sql b/exec/java-exec/src/test/resources/queries/tpch/13.sql new file mode 100644 index 000000000..ae3f691c9 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/13.sql @@ -0,0 +1,22 @@ +-- tpch13 using 1395599672 as a seed to the RNG
+select
+ c_count,
+ count(*) as custdist
+from
+ (
+ select
+ c.c_custkey,
+ count(o.o_orderkey)
+ from
+ cp.`tpch/customer.parquet` c
+ left outer join cp.`tpch/orders.parquet` o
+ on c.c_custkey = o.o_custkey
+ and o.o_comment not like '%special%requests%'
+ group by
+ c.c_custkey
+ ) as orders (c_custkey, c_count)
+group by
+ c_count
+order by
+ custdist desc,
+ c_count desc;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/14.sql b/exec/java-exec/src/test/resources/queries/tpch/14.sql new file mode 100644 index 000000000..91cd938e3 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/14.sql @@ -0,0 +1,14 @@ +-- tpch14 using 1395599672 as a seed to the RNG
+select
+ 100.00 * sum(case
+ when p.p_type like 'PROMO%'
+ then l.l_extendedprice * (1 - l.l_discount)
+ else 0
+ end) / sum(l.l_extendedprice * (1 - l.l_discount)) as promo_revenue
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ l.l_partkey = p.p_partkey
+ and l.l_shipdate >= date '1994-08-01'
+ and l.l_shipdate < date '1994-08-01' + interval '1' month;
diff --git a/exec/java-exec/src/test/resources/queries/tpch/15.sql b/exec/java-exec/src/test/resources/queries/tpch/15.sql new file mode 100644 index 000000000..251631d74 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/15.sql @@ -0,0 +1,34 @@ +-- tpch15 using 1395599672 as a seed to the RNG
+create view revenue0 (supplier_no, total_revenue) as
+ select
+ l_suppkey,
+ sum(l_extendedprice * (1 - l_discount))
+ from
+ cp.`tpch/lineitem.parquet`
+ where
+ l_shipdate >= date '1993-05-01'
+ and l_shipdate < date '1993-05-01' + interval '3' month
+ group by
+ l_suppkey;
+
+select
+ s.s_suppkey,
+ s.s_name,
+ s.s_address,
+ s.s_phone,
+ r.total_revenue
+from
+ cp.`tpch/supplier.parquet` s,
+ revenue0 r
+where
+ s.s_suppkey = r.supplier_no
+ and r.total_revenue = (
+ select
+ max(total_revenue)
+ from
+ revenue0
+ )
+order by
+ s.s_suppkey;
+
+drop view revenue0;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/16.sql b/exec/java-exec/src/test/resources/queries/tpch/16.sql new file mode 100644 index 000000000..96c5fd40c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/16.sql @@ -0,0 +1,31 @@ +-- tpch16 using 1395599672 as a seed to the RNG
+select
+ p.p_brand,
+ p.p_type,
+ p.p_size,
+ count(distinct ps.ps_suppkey) as supplier_cnt
+from
+ cp.`tpch/partsupp.parquet` ps,
+ cp.`tpch/part.parquet` p
+where
+ p.p_partkey = ps.ps_partkey
+ and p.p_brand <> 'Brand#21'
+ and p.p_type not like 'MEDIUM PLATED%'
+ and p.p_size in (38, 2, 8, 31, 44, 5, 14, 24)
+ and ps.ps_suppkey not in (
+ select
+ s_suppkey
+ from
+ cp.`tpch/supplier.parquet`
+ where
+ s_comment like '%Customer%Complaints%'
+ )
+group by
+ p.p_brand,
+ p.p_type,
+ p.p_size
+order by
+ supplier_cnt desc,
+ p.p_brand,
+ p.p_type,
+ p.p_size;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/17.sql b/exec/java-exec/src/test/resources/queries/tpch/17.sql new file mode 100644 index 000000000..24e2044b1 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/17.sql @@ -0,0 +1,18 @@ +-- tpch17 using 1395599672 as a seed to the RNG
+select
+ sum(l.l_extendedprice) / 7.0 as avg_yearly
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#13'
+ and p.p_container = 'JUMBO CAN'
+ and l.l_quantity < (
+ select
+ 0.2 * avg(l2.l_quantity)
+ from
+ cp.`tpch/lineitem.parquet` l2
+ where
+ l2.l_partkey = p.p_partkey
+ );
diff --git a/exec/java-exec/src/test/resources/queries/tpch/18.sql b/exec/java-exec/src/test/resources/queries/tpch/18.sql new file mode 100644 index 000000000..6fcddeb66 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/18.sql @@ -0,0 +1,34 @@ +-- tpch18 using 1395599672 as a seed to the RNG
+select
+ c.c_name,
+ c.c_custkey,
+ o.o_orderkey,
+ o.o_orderdate,
+ o.o_totalprice,
+ sum(l.l_quantity)
+from
+ cp.`tpch/customer.parquet` c,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/lineitem.parquet` l
+where
+ o.o_orderkey in (
+ select
+ l_orderkey
+ from
+ cp.`tpch/lineitem.parquet`
+ group by
+ l_orderkey having
+ sum(l_quantity) > 313
+ )
+ and c.c_custkey = o.o_custkey
+ and o.o_orderkey = l.l_orderkey
+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;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/19.sql b/exec/java-exec/src/test/resources/queries/tpch/19.sql new file mode 100644 index 000000000..15ceb91a0 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/19.sql @@ -0,0 +1,36 @@ +-- tpch19 using 1395599672 as a seed to the RNG
+select
+ sum(l.l_extendedprice* (1 - l.l_discount)) as revenue
+from
+ cp.`tpch/lineitem.parquet` l,
+ cp.`tpch/part.parquet` p
+where
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#41'
+ and p.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ and l.l_quantity >= 2 and l.l_quantity <= 2 + 10
+ and p.p_size between 1 and 5
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#13'
+ and p.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ and l.l_quantity >= 14 and l.l_quantity <= 14 + 10
+ and p.p_size between 1 and 10
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ or
+ (
+ p.p_partkey = l.l_partkey
+ and p.p_brand = 'Brand#55'
+ and p.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ and l.l_quantity >= 23 and l.l_quantity <= 23 + 10
+ and p.p_size between 1 and 15
+ and l.l_shipmode in ('AIR', 'AIR REG')
+ and l.l_shipinstruct = 'DELIVER IN PERSON'
+ );
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/20.sql b/exec/java-exec/src/test/resources/queries/tpch/20.sql new file mode 100644 index 000000000..e1980340d --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/20.sql @@ -0,0 +1,38 @@ +-- tpch20 using 1395599672 as a seed to the RNG
+select
+ s.s_name,
+ s.s_address
+from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/nation.parquet` n
+where
+ s.s_suppkey in (
+ select
+ ps.ps_suppkey
+ from
+ cp.`tpch/partsupp.parquet` ps
+ where
+ ps. ps_partkey in (
+ select
+ p.p_partkey
+ from
+ cp.`tpch/part.parquet` p
+ where
+ p.p_name like 'antique%'
+ )
+ and ps.ps_availqty > (
+ select
+ 0.5 * sum(l.l_quantity)
+ from
+ cp.`tpch/lineitem.parquet` l
+ where
+ l.l_partkey = ps.ps_partkey
+ and l.l_suppkey = ps.ps_suppkey
+ and l.l_shipdate >= date '1993-01-01'
+ and l.l_shipdate < date '1993-01-01' + interval '1' year
+ )
+ )
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'KENYA'
+order by
+ s.s_name;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/21.sql b/exec/java-exec/src/test/resources/queries/tpch/21.sql new file mode 100644 index 000000000..7b50df64c --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/21.sql @@ -0,0 +1,41 @@ +-- tpch21 using 1395599672 as a seed to the RNG
+select
+ s.s_name,
+ count(*) as numwait
+from
+ cp.`tpch/supplier.parquet` s,
+ cp.`tpch/lineitem.parquet` l1,
+ cp.`tpch/orders.parquet` o,
+ cp.`tpch/nation.parquet` n
+where
+ s.s_suppkey = l1.l_suppkey
+ and o.o_orderkey = l1.l_orderkey
+ and o.o_orderstatus = 'F'
+ and l1.l_receiptdate > l1.l_commitdate
+ and exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet` l2
+ where
+ l2.l_orderkey = l1.l_orderkey
+ and l2.l_suppkey <> l1.l_suppkey
+ )
+ and not exists (
+ select
+ *
+ from
+ cp.`tpch/lineitem.parquet` l3
+ where
+ l3.l_orderkey = l1.l_orderkey
+ and l3.l_suppkey <> l1.l_suppkey
+ and l3.l_receiptdate > l3.l_commitdate
+ )
+ and s.s_nationkey = n.n_nationkey
+ and n.n_name = 'BRAZIL'
+group by
+ s.s_name
+order by
+ numwait desc,
+ s.s_name
+limit 100;
\ No newline at end of file diff --git a/exec/java-exec/src/test/resources/queries/tpch/22.sql b/exec/java-exec/src/test/resources/queries/tpch/22.sql new file mode 100644 index 000000000..052633e41 --- /dev/null +++ b/exec/java-exec/src/test/resources/queries/tpch/22.sql @@ -0,0 +1,38 @@ +-- tpch22 using 1395599672 as a seed to the RNG
+select
+ cntrycode,
+ count(*) as numcust,
+ sum(c_acctbal) as totacctbal
+from
+ (
+ select
+ substring(c_phone from 1 for 2) as cntrycode,
+ c_acctbal
+ from
+ cp.`tpch/customer.parquet` c
+ where
+ substring(c_phone from 1 for 2) in
+ ('24', '31', '11', '16', '21', '20', '34')
+ and c_acctbal > (
+ select
+ avg(c_acctbal)
+ from
+ cp.`tpch/customer.parquet`
+ where
+ c_acctbal > 0.00
+ and substring(c_phone from 1 for 2) in
+ ('24', '31', '11', '16', '21', '20', '34')
+ )
+ and not exists (
+ select
+ *
+ from
+ cp.`tpch/orders.parquet` o
+ where
+ o.o_custkey = c.c_custkey
+ )
+ ) as custsale
+group by
+ cntrycode
+order by
+ cntrycode;
|