diff options
| author | Jacques Nadeau <jacques@apache.org> | 2014-03-18 10:48:45 -0700 |
|---|---|---|
| committer | Jacques Nadeau <jacques@apache.org> | 2014-03-26 22:46:38 -0700 |
| commit | 129cd775ad8e28e2c6629e774ce2f2049f173557 (patch) | |
| tree | f4135c7a3281c3ad4fc7ec2bd3d4d7d94eb52699 | |
| parent | 721e7c25759eda2f12b17e15e4ca32ab16ec1e1c (diff) | |
- Add TPC-H Data as test dependency (automatically downloads from s3 on first execution)
- Update avg function to divide using double instead of integers
- Update classpath file system to return correct size information (to allow cp use of Parquet files)
- Enhance BaseTestQuery to support all query types (sql, physical logical) and execution from file
- Add TPC-H queries updated to use TPC-H test data (disabled)
- Update field name matching to be case insensitive.
- Treat CHAR literals like VARCHAR in Optiq
- Add clear as part of VectorUtil
- Add hash aggregation physical plan executions using tpch data.
46 files changed, 1579 insertions, 66 deletions
diff --git a/common/src/main/java/org/apache/drill/common/expression/PathSegment.java b/common/src/main/java/org/apache/drill/common/expression/PathSegment.java index 91acdbeb3..ac1aeacce 100644 --- a/common/src/main/java/org/apache/drill/common/expression/PathSegment.java +++ b/common/src/main/java/org/apache/drill/common/expression/PathSegment.java @@ -83,7 +83,7 @@ public abstract class PathSegment{ this.path = n.toString(); } - public CharSequence getPath(){ + public String getPath(){ return path; } @@ -126,7 +126,7 @@ public abstract class PathSegment{ if (path == null) { if (other.path != null) return false; - } else if (!path.equals(other.path)) + } else if (!path.equalsIgnoreCase(other.path)) return false; return true; } diff --git a/contrib/data/pom.xml b/contrib/data/pom.xml new file mode 100644 index 000000000..8fe1dcd07 --- /dev/null +++ b/contrib/data/pom.xml @@ -0,0 +1,37 @@ +<?xml version="1.0"?> +<!-- + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--> +<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>drill-contrib-parent</artifactId> + <groupId>org.apache.drill.contrib</groupId> + <version>1.0.0-m2-incubating-SNAPSHOT</version> + </parent> + + <groupId>org.apache.drill.contrib.data</groupId> + <artifactId>drill-contrib-data-parent</artifactId> + <name>contrib/data/Parent Pom</name> + <packaging>pom</packaging> + + <dependencies> + </dependencies> + + <modules> + <module>tpch-sample-data</module> + </modules> +</project> diff --git a/contrib/data/tpch-sample-data/bin/pom.xml b/contrib/data/tpch-sample-data/bin/pom.xml new file mode 100644 index 000000000..ceddd5c27 --- /dev/null +++ b/contrib/data/tpch-sample-data/bin/pom.xml @@ -0,0 +1,131 @@ +<?xml version="1.0"?> +<!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor + license agreements. See the NOTICE file distributed with this work for additional + information regarding copyright ownership. The ASF licenses this file to + You under the Apache License, Version 2.0 (the "License"); you may not use + this file except in compliance with the License. You may obtain a copy of + the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required + by applicable law or agreed to in writing, software distributed under the + License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS + OF ANY KIND, either express or implied. See the License for the specific + language governing permissions and limitations under the License. --> +<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>drill-contrib-data-parent</artifactId> + <groupId>org.apache.drill.contrib.data</groupId> + <version>1.0.0-m2-incubating-SNAPSHOT</version> + </parent> + + <artifactId>tpch-sample-data</artifactId> + <name>contrib/data/tpch-sample-data</name> + <packaging>jar</packaging> + + <dependencies> + </dependencies> + <build> + <plugins> + <plugin> + <groupId>com.googlecode.maven-download-plugin</groupId> + <artifactId>maven-download-plugin</artifactId> + <version>1.1.0</version> + <executions> + <execution> + <id>install-tgz</id> + <phase>package</phase> + <goals> + <goal>wget</goal> + </goals> + <configuration> + <url>http://apache-drill.s3.amazonaws.com/files/sf-0.01_tpc-h_parquet.tgz</url> + <outputFileName>tpch.tgz</outputFileName> + <unpack>true</unpack> + <outputDirectory>${project.build.directory}</outputDirectory> + </configuration> + </execution> + </executions> + </plugin> + + <plugin> + <groupId>org.codehaus.mojo</groupId> + <artifactId>truezip-maven-plugin</artifactId> + <version>1.0</version> + <executions> + <execution> + <id>copy-out-files</id> + <goals> + <goal>copy</goal> + </goals> + <phase>package</phase> + <configuration> +<!-- <from>${project.build.directory}/tpch.tgz</from> --> +<!-- <to>${project.build.directory}/data/</to> --> + </configuration> + </execution> + + <execution> + <id>copy-out-fileset</id> + <goals> + <goal>copy</goal> + </goals> + <phase>package</phase> + <configuration> + <fileset> + <directory>${project.build.directory}/tpch.tgz</directory> + <outputDirectory>${project.build.directory}/data</outputDirectory> + </fileset> + </configuration> + </execution> + +<!-- <execution> --> +<!-- <id>copy-out-fileset</id> --> +<!-- <goals> --> +<!-- <goal>copy</goal> --> +<!-- </goals> --> +<!-- <phase>package</phase> --> +<!-- <configuration> --> +<!-- <fileset> --> +<!-- <directory>${archive}</directory> --> +<!-- <excludes> --> +<!-- <exclude>**/pom.properties</exclude> --> +<!-- </excludes> --> +<!-- <outputDirectory>${project.build.directory}/copy-fileset</outputDirectory> --> +<!-- </fileset> --> +<!-- </configuration> --> +<!-- </execution> --> + + <!-- <execution> --> + <!-- <id>copy-into</id> --> + <!-- <goals> --> + <!-- <goal>copy</goal> --> + <!-- </goals> --> + <!-- <phase>package</phase> --> + <!-- <configuration> --> + <!-- <fileset> --> + <!-- <directory>${basedir}</directory> --> + <!-- <includes> --> + <!-- <include>*</include> --> + <!-- </includes> --> + <!-- <outputDirectory>${archive}/copy-into</outputDirectory> --> + <!-- </fileset> --> + <!-- </configuration> --> + <!-- </execution> --> + </executions> + </plugin> + </plugins> + </build> + <pluginRepositories> + <pluginRepository> + <id>sonatype-public-repository</id> + <url>https://oss.sonatype.org/content/groups/public</url> + <snapshots> + <enabled>true</enabled> + </snapshots> + <releases> + <enabled>true</enabled> + </releases> + </pluginRepository> + </pluginRepositories> + +</project> diff --git a/contrib/data/tpch-sample-data/pom.xml b/contrib/data/tpch-sample-data/pom.xml new file mode 100644 index 000000000..ab968f22e --- /dev/null +++ b/contrib/data/tpch-sample-data/pom.xml @@ -0,0 +1,64 @@ +<?xml version="1.0"?> +<!-- Licensed to the Apache Software Foundation (ASF) under one or more contributor + license agreements. See the NOTICE file distributed with this work for additional + information regarding copyright ownership. The ASF licenses this file to + You under the Apache License, Version 2.0 (the "License"); you may not use + this file except in compliance with the License. You may obtain a copy of + the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required + by applicable law or agreed to in writing, software distributed under the + License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS + OF ANY KIND, either express or implied. See the License for the specific + language governing permissions and limitations under the License. --> +<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>drill-contrib-data-parent</artifactId> + <groupId>org.apache.drill.contrib.data</groupId> + <version>1.0.0-m2-incubating-SNAPSHOT</version> + </parent> + + <artifactId>tpch-sample-data</artifactId> + <name>contrib/data/tpch-sample-data</name> + <packaging>jar</packaging> + + <dependencies> + </dependencies> + <build> + <plugins> + <plugin> + <groupId>com.googlecode.maven-download-plugin</groupId> + <artifactId>download-maven-plugin</artifactId> + <version>1.2.0-SNAPSHOT</version> + <executions> + <execution> + <id>install-tgz</id> + <phase>prepare-package</phase> + <goals> + <goal>wget</goal> + </goals> + <configuration> + <url>http://apache-drill.s3.amazonaws.com/files/sf-0.01_tpc-h_parquet.tgz</url> + <outputFileName>tpch.tgz</outputFileName> + <unpack>true</unpack> + <outputDirectory>${project.build.directory}/classes/tpch</outputDirectory> + </configuration> + </execution> + </executions> + </plugin> + </plugins> + </build> + <pluginRepositories> + <pluginRepository> + <id>sonatype-public-repository</id> + <url>https://oss.sonatype.org/content/groups/public</url> + <snapshots> + <enabled>true</enabled> + </snapshots> + <releases> + <enabled>true</enabled> + </releases> + </pluginRepository> + </pluginRepositories> + +</project> diff --git a/contrib/pom.xml b/contrib/pom.xml index 578513d92..b54e03cf6 100644 --- a/contrib/pom.xml +++ b/contrib/pom.xml @@ -35,5 +35,6 @@ <module>storage-hbase</module> <module>storage-hive</module> <module>sqlline</module> + <module>data</module> </modules> </project> diff --git a/exec/java-exec/pom.xml b/exec/java-exec/pom.xml index bb044ad03..4f7cc0d80 100644 --- a/exec/java-exec/pom.xml +++ b/exec/java-exec/pom.xml @@ -31,7 +31,13 @@ <artifactId>mondrian-data-foodmart-json</artifactId> <version>0.3.2</version> <scope>test</scope> - </dependency> + </dependency> + <dependency> + <groupId>org.apache.drill.contrib.data</groupId> + <artifactId>tpch-sample-data</artifactId> + <version>${project.version}</version> + <scope>test</scope> + </dependency> <dependency> <groupId>org.ow2.asm</groupId> <artifactId>asm-commons</artifactId> diff --git a/exec/java-exec/src/main/codegen/templates/AggrTypeFunctions2.java b/exec/java-exec/src/main/codegen/templates/AggrTypeFunctions2.java index ca9adb5a6..f2ebbe8fc 100644 --- a/exec/java-exec/src/main/codegen/templates/AggrTypeFunctions2.java +++ b/exec/java-exec/src/main/codegen/templates/AggrTypeFunctions2.java @@ -88,7 +88,7 @@ public static class ${type.inputType}${aggrtype.className} implements DrillAggFu @Override public void output() { - out.value = sum.value/count.value; + out.value = sum.value / ((double) count.value); } @Override diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/client/QuerySubmitter.java b/exec/java-exec/src/main/java/org/apache/drill/exec/client/QuerySubmitter.java index 7c10b88fa..3fb607ccd 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/client/QuerySubmitter.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/client/QuerySubmitter.java @@ -224,6 +224,7 @@ public class QuerySubmitter { VectorUtil.showVectorAccessibleContent(loader, ","); break; } + } if (result.getHeader().getIsLastChunk()) { diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/join/MergeJoinBatch.java b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/join/MergeJoinBatch.java index 0d27a1fe6..986521e5a 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/join/MergeJoinBatch.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/physical/impl/join/MergeJoinBatch.java @@ -119,14 +119,15 @@ public class MergeJoinBatch extends AbstractRecordBatch<MergeJoinPOP> { protected MergeJoinBatch(MergeJoinPOP popConfig, FragmentContext context, RecordBatch left, RecordBatch right) { super(popConfig, context); + // currently only one join condition is supported + assert popConfig.getConditions().size() == 1 : String.format("Merge Join currently only supports joins with a single condition. This join operator was configured with %d condition(s).", popConfig.getConditions().size()); this.left = left; this.right = right; this.status = new JoinStatus(left, right, this); this.batchBuilder = new MergeJoinBatchBuilder(context, status); this.joinType = popConfig.getJoinType(); this.condition = popConfig.getConditions().get(0); - // currently only one join condition is supported - assert popConfig.getConditions().size() == 1; + } @Override diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java index 7398d782a..412914544 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java @@ -168,6 +168,7 @@ public class DrillOptiq { switch(call.getType().getSqlTypeName().getName()){ case "VARCHAR": + case "CHAR": castType = Types.required(MinorType.VARCHAR).toBuilder().setWidth(call.getType().getPrecision()).build(); break; @@ -175,6 +176,7 @@ public class DrillOptiq { case "FLOAT": Types.required(MinorType.FLOAT4); break; case "DOUBLE": Types.required(MinorType.FLOAT8); break; case "DECIMAL": throw new UnsupportedOperationException("Need to add decimal."); + default: castType = Types.required(MinorType.valueOf(call.getType().getSqlTypeName().getName())); } @@ -202,8 +204,9 @@ public class DrillOptiq { return ValueExpressions.getFloat4(f); case INTEGER: case DECIMAL: - int i = ((BigDecimal) literal.getValue()).intValue(); - return ValueExpressions.getInt(i); + double dbl = ((BigDecimal) literal.getValue()).doubleValue(); + logger.warn("Converting exact decimal into approximate decimal. Should be fixed once decimal is implemented."); + return ValueExpressions.getFloat8(dbl); case VARCHAR: return ValueExpressions.getChar(((NlsString)literal.getValue()).getValue()); default: diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/record/MaterializedField.java b/exec/java-exec/src/main/java/org/apache/drill/exec/record/MaterializedField.java index 56e48a844..e658dacea 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/record/MaterializedField.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/record/MaterializedField.java @@ -136,7 +136,7 @@ public class MaterializedField{ if (n.getType() == Type.ARRAY) continue; return false; } else { - if (p.getNameSegment().getPath().equals(n.getName())) continue; + if (p.getNameSegment().getPath().equalsIgnoreCase(n.getName())) continue; return false; } diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ClassPathFileSystem.java b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ClassPathFileSystem.java index e39e1a33a..f7d3c6776 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/store/ClassPathFileSystem.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/store/ClassPathFileSystem.java @@ -72,13 +72,15 @@ public class ClassPathFileSystem extends FileSystem{ @Override public FileStatus getFileStatus(Path arg0) throws IOException { String file = getFileName(arg0); - + try{ URL url = Resources.getResource(file); if(url == null){ throw new IOException(String.format("Unable to find path %s.", arg0.toString())); } - return new FileStatus(1, false, 1, 8096, System.currentTimeMillis(), arg0); + + + return new FileStatus(Resources.asByteSource(url).size(), false, 1, 8096, System.currentTimeMillis(), arg0); }catch(RuntimeException e){ throw new IOException(String.format("Failure trying to load file %s", arg0), e); } diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/util/VectorUtil.java b/exec/java-exec/src/main/java/org/apache/drill/exec/util/VectorUtil.java index a35bb5feb..a9e6f30b4 100644 --- a/exec/java-exec/src/main/java/org/apache/drill/exec/util/VectorUtil.java +++ b/exec/java-exec/src/main/java/org/apache/drill/exec/util/VectorUtil.java @@ -33,7 +33,7 @@ public class VectorUtil { int rows = va.getRecordCount(); List<String> columns = Lists.newArrayList(); - for (VectorWrapper vw : va) { + for (VectorWrapper<?> vw : va) { columns.add(vw.getValueVector().getField().getName()); } @@ -43,7 +43,7 @@ public class VectorUtil { } for (int row = 0; row < rows; row++) { int columnCounter = 0; - for (VectorWrapper vw : va) { + for (VectorWrapper<?> vw : va) { boolean lastColumn = columnCounter == width - 1; Object o = vw.getValueVector().getAccessor().getObject(row); if (o == null) { @@ -61,13 +61,17 @@ public class VectorUtil { columnCounter++; } } + + for (VectorWrapper<?> vw : va) { + vw.clear(); + } } public static void showVectorAccessibleContent(VectorAccessible va) { int rows = va.getRecordCount(); List<String> columns = Lists.newArrayList(); - for (VectorWrapper vw : va) { + for (VectorWrapper<?> vw : va) { columns.add(vw.getValueVector().getField().getName()); } @@ -81,7 +85,7 @@ public class VectorUtil { System.out.printf("|\n"); System.out.println(StringUtils.repeat("-", width*17 + 1)); } - for (VectorWrapper vw : va) { + for (VectorWrapper<?> vw : va) { Object o = vw.getValueVector().getAccessor().getObject(row); if (o == null) { //null value @@ -98,6 +102,10 @@ public class VectorUtil { System.out.printf("|\n"); } + for (VectorWrapper<?> vw : va) { + vw.clear(); + } + if (rows > 0 ) System.out.println(StringUtils.repeat("-", width*17 + 1)); } 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;
@@ -111,6 +111,7 @@ <exclude>**/*.md</exclude> <exclude>sandbox/**</exclude> <exclude>**/*.json</exclude> + <exclude>**/*.sql</exclude> <exclude>**/drill-*.conf</exclude> <exclude>**/.buildpath</exclude> <exclude>**/*.proto</exclude> |