This is an automated email from the ASF dual-hosted git repository.
korlov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 0c2d2128b7 [IGNITE-18748] Sql. Planner optimization for JOIN.
Benchmarks. (#1667)
0c2d2128b7 is described below
commit 0c2d2128b73d6304f22439e129687e31ce2814c7
Author: Max Zhuravkov <[email protected]>
AuthorDate: Thu Feb 16 20:10:53 2023 +0400
[IGNITE-18748] Sql. Planner optimization for JOIN. Benchmarks. (#1667)
---
.../RepeatedRandomRowDataProviderFactory.java | 95 ++++++++++++
.../sql/engine/benchmarks/TpchBenchmark.java | 111 ++++++++++++++
.../sql/engine/benchmarks/TpchQueries.java | 80 ++++++++++
.../internal/sql/engine/benchmarks/TpchSchema.java | 165 +++++++++++++++++++++
.../sql/engine/framework/TestBuilders.java | 42 +++++-
.../sql/engine/framework/TestClusterTest.java | 70 +++++++++
.../internal/sql/engine/framework/TestNode.java | 99 ++++++++++++-
modules/sql-engine/src/test/resources/tpch/q1.sql | 24 +++
modules/sql-engine/src/test/resources/tpch/q10.sql | 35 +++++
modules/sql-engine/src/test/resources/tpch/q11.sql | 30 ++++
modules/sql-engine/src/test/resources/tpch/q12.sql | 31 ++++
modules/sql-engine/src/test/resources/tpch/q13.sql | 23 +++
modules/sql-engine/src/test/resources/tpch/q14.sql | 15 ++
modules/sql-engine/src/test/resources/tpch/q15.sql | 34 +++++
modules/sql-engine/src/test/resources/tpch/q16.sql | 32 ++++
modules/sql-engine/src/test/resources/tpch/q17.sql | 18 +++
modules/sql-engine/src/test/resources/tpch/q18.sql | 36 +++++
modules/sql-engine/src/test/resources/tpch/q19.sql | 37 +++++
modules/sql-engine/src/test/resources/tpch/q2.sql | 44 ++++++
modules/sql-engine/src/test/resources/tpch/q20.sql | 35 +++++
modules/sql-engine/src/test/resources/tpch/q21.sql | 41 +++++
modules/sql-engine/src/test/resources/tpch/q22.sql | 37 +++++
modules/sql-engine/src/test/resources/tpch/q3.sql | 26 ++++
modules/sql-engine/src/test/resources/tpch/q4.sql | 21 +++
modules/sql-engine/src/test/resources/tpch/q5.sql | 27 ++++
modules/sql-engine/src/test/resources/tpch/q6.sql | 11 ++
modules/sql-engine/src/test/resources/tpch/q7.sql | 41 +++++
modules/sql-engine/src/test/resources/tpch/q8.sql | 40 +++++
modules/sql-engine/src/test/resources/tpch/q9.sql | 34 +++++
.../src/test/resources/tpch/variant_q12.sql | 25 ++++
.../src/test/resources/tpch/variant_q14.sql | 16 ++
.../src/test/resources/tpch/variant_q8.sql | 38 +++++
32 files changed, 1411 insertions(+), 2 deletions(-)
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/RepeatedRandomRowDataProviderFactory.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/RepeatedRandomRowDataProviderFactory.java
new file mode 100644
index 0000000000..4c643f441f
--- /dev/null
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/RepeatedRandomRowDataProviderFactory.java
@@ -0,0 +1,95 @@
+/*
+ * 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.ignite.internal.sql.engine.benchmarks;
+
+import java.math.BigDecimal;
+import java.math.BigInteger;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.util.List;
+import java.util.Objects;
+import java.util.UUID;
+import org.apache.ignite.internal.schema.NativeTypeSpec;
+import org.apache.ignite.internal.sql.engine.framework.DataProvider;
+import
org.apache.ignite.internal.sql.engine.framework.TestBuilders.DataProviderFactory;
+import org.apache.ignite.internal.sql.engine.schema.ColumnDescriptor;
+
+/**
+ * {@link DataProviderFactory} that creates {@link DataProvider}s that
generates a row of pseudo random data based on table column types
+ * and then returns the same row multiple times.
+ */
+final class RepeatedRandomRowDataProviderFactory implements
DataProviderFactory {
+
+ private final int dataSize;
+
+ RepeatedRandomRowDataProviderFactory(int dataSize) {
+ this.dataSize = dataSize;
+ }
+
+ /** {@inheritDoc} **/
+ @Override
+ public DataProvider<Object[]> createDataProvider(String tableName,
List<ColumnDescriptor> columns) {
+ Object[] row = columns.stream().map(c -> generateValueByType(1,
c.physicalType().spec())).toArray();
+
+ return DataProvider.fromRow(row, dataSize);
+ }
+
+ private static Object generateValueByType(int i, NativeTypeSpec type) {
+ switch (type) {
+ case INT8:
+ return (byte) i;
+ case INT16:
+ return (short) i;
+ case INT32:
+ return i;
+ case INT64:
+ return (long) i;
+ case FLOAT:
+ return (float) i + ((float) i / 1000);
+ case DOUBLE:
+ return (double) i + ((double) i / 1000);
+ case STRING:
+ return "str_" + i;
+ case BYTES:
+ return new byte[]{(byte) i, (byte) (i + 1), (byte) (i + 2)};
+ case DECIMAL:
+ return BigDecimal.valueOf((double) i + ((double) i / 1000));
+ case NUMBER:
+ return BigInteger.valueOf(i);
+ case UUID:
+ return new UUID(i, i);
+ case BITMASK:
+ return new byte[]{(byte) i};
+ case DATETIME:
+ return LocalDateTime.of(
+ (LocalDate)
Objects.requireNonNull(generateValueByType(i, NativeTypeSpec.DATE)),
+ (LocalTime)
Objects.requireNonNull(generateValueByType(i, NativeTypeSpec.TIME))
+ );
+ case TIMESTAMP:
+ return Instant.from((LocalDateTime)
Objects.requireNonNull(generateValueByType(i, NativeTypeSpec.DATETIME)));
+ case DATE:
+ return LocalDate.of(2022, 01, 01).plusDays(i);
+ case TIME:
+ return LocalTime.of(0, 00, 00).plusSeconds(i);
+ default:
+ throw new IllegalArgumentException("unsupported type " + type);
+ }
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchBenchmark.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchBenchmark.java
new file mode 100644
index 0000000000..bb39637c6b
--- /dev/null
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchBenchmark.java
@@ -0,0 +1,111 @@
+/*
+ * 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.ignite.internal.sql.engine.benchmarks;
+
+import java.util.concurrent.TimeUnit;
+import org.apache.ignite.internal.sql.engine.framework.TestBuilders;
+import org.apache.ignite.internal.sql.engine.framework.TestCluster;
+import org.apache.ignite.internal.sql.engine.framework.TestNode;
+import org.openjdk.jmh.annotations.Benchmark;
+import org.openjdk.jmh.annotations.BenchmarkMode;
+import org.openjdk.jmh.annotations.Fork;
+import org.openjdk.jmh.annotations.Measurement;
+import org.openjdk.jmh.annotations.Mode;
+import org.openjdk.jmh.annotations.OutputTimeUnit;
+import org.openjdk.jmh.annotations.Param;
+import org.openjdk.jmh.annotations.Scope;
+import org.openjdk.jmh.annotations.Setup;
+import org.openjdk.jmh.annotations.State;
+import org.openjdk.jmh.annotations.TearDown;
+import org.openjdk.jmh.annotations.Warmup;
+import org.openjdk.jmh.infra.Blackhole;
+import org.openjdk.jmh.runner.Runner;
+import org.openjdk.jmh.runner.options.Options;
+import org.openjdk.jmh.runner.options.OptionsBuilder;
+
+/**
+ * Benchmarks derived from <a href="http://www.tpc.org/tpch/";>TPC-H</a>.
+ */
+@Warmup(iterations = 20, time = 1, timeUnit = TimeUnit.SECONDS)
+@Measurement(iterations = 20, time = 1, timeUnit = TimeUnit.SECONDS)
+@BenchmarkMode(Mode.Throughput)
+@OutputTimeUnit(TimeUnit.SECONDS)
+@Fork(3)
+@State(Scope.Benchmark)
+public class TpchBenchmark {
+
+ /**
+ * Identifiers of TPC-H queries. See {@link TpchQueries#getQuery(String)}.
+ */
+ @Param({
+ "1", "2", "3", "4", "5", "6", "7", "8", "8v", "9", "10", "11",
"12", "12v",
+ "13", "14", "14v", "15", "16", "17", "18", "19", "20", "21", "22"
+ })
+ private String queryId;
+
+ private TestCluster testCluster;
+
+ private TestNode gatewayNode;
+
+ private String queryString;
+
+ /** Starts the cluster and prepares the plan of the query. */
+ @Setup
+ public void setUp() {
+ var clusterBuilder = TestBuilders.cluster().nodes("N1");
+ TpchSchema.registerTables(clusterBuilder, 1, 10);
+
+ testCluster = clusterBuilder.build();
+
+ testCluster.start();
+ gatewayNode = testCluster.node("N1");
+
+ queryString = TpchQueries.getQuery(queryId);
+ }
+
+ /** Stops the cluster. */
+ @TearDown
+ public void tearDown() throws Exception {
+ testCluster.stop();
+ }
+
+ /**
+ * Benchmark that measures the time it takes to prepare a {@code TPC-H
query}.
+ *
+ * <p>The result includes the time to complete the following stages:
parsing, validation, and planning.
+ */
+ @Benchmark
+ public void prepareQuery(Blackhole bh) {
+ bh.consume(gatewayNode.prepare(queryString));
+ }
+
+ /**
+ * Runs the benchmark.
+ *
+ * @param args args
+ * @throws Exception if something goes wrong
+ */
+ public static void main(String[] args) throws Exception {
+ Options build = new OptionsBuilder()
+ //.addProfiler("gc")
+ .include(TpchBenchmark.class.getName())
+ .build();
+
+ new Runner(build).run();
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchQueries.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchQueries.java
new file mode 100644
index 0000000000..88dda7205d
--- /dev/null
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchQueries.java
@@ -0,0 +1,80 @@
+/*
+ * 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.ignite.internal.sql.engine.benchmarks;
+
+import com.google.common.io.CharStreams;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.io.UncheckedIOException;
+import java.nio.charset.StandardCharsets;
+
+/**
+ * Provides utility methods to work with queries defined by the TPC-H
benchmark.
+ */
+public final class TpchQueries {
+
+ private TpchQueries() {
+
+ }
+
+ /**
+ * Loads a TPC-H query given a query identifier. Query identifier can be
in the following forms:
+ * <ul>
+ * <li>query id - a number, e.g. {@code 14}. Queries are stored in
files named q{id}.sql.</li>
+ * <li>query variant id, a number and 'v' suffix - {@code 12v}. Query
variants are stored in files
+ * named variant_q{numeric_id}.sql.</li>
+ * </ul>
+ *
+ * @param queryId The identifier of a query.
+ * @return An SQL query.
+ */
+ public static String getQuery(String queryId) {
+ // variant query ends with "v"
+ boolean variant = queryId.endsWith("v");
+ int numericId;
+
+ if (variant) {
+ String idString = queryId.substring(0, queryId.length() - 1);
+ numericId = Integer.parseInt(idString);
+ } else {
+ numericId = Integer.parseInt(queryId);
+ }
+
+ if (variant) {
+ var variantQueryFile = String.format("tpch/variant_q%d.sql",
numericId);
+ return loadFromResource(variantQueryFile);
+ } else {
+ var queryFile = String.format("tpch/q%s.sql", numericId);
+ return loadFromResource(queryFile);
+ }
+ }
+
+ private static String loadFromResource(String resource) {
+ try (InputStream is =
TpchBenchmark.class.getClassLoader().getResourceAsStream(resource)) {
+ if (is == null) {
+ throw new IllegalArgumentException("Resource does not exist: "
+ resource);
+ }
+ try (InputStreamReader reader = new InputStreamReader(is,
StandardCharsets.UTF_8)) {
+ return CharStreams.toString(reader);
+ }
+ } catch (IOException e) {
+ throw new UncheckedIOException("I/O operation failed: " +
resource, e);
+ }
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchSchema.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchSchema.java
new file mode 100644
index 0000000000..f7cd9bc398
--- /dev/null
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/benchmarks/TpchSchema.java
@@ -0,0 +1,165 @@
+/*
+ * 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.ignite.internal.sql.engine.benchmarks;
+
+import java.util.List;
+import org.apache.ignite.internal.schema.NativeTypes;
+import
org.apache.ignite.internal.sql.engine.framework.TestBuilders.ClusterBuilder;
+import org.apache.ignite.internal.sql.engine.trait.IgniteDistributions;
+
+/**
+ * Provides utility methods to register tables described by the TPC-H
benchmark in a {@link ClusterBuilder}.
+ */
+public final class TpchSchema {
+
+ private static final int PART_SIZE = 200_000;
+ private static final int SUPPLIER_SIZE = 10_000;
+ // TPC-H 4.2.5.1 Table 3: Estimated Database Size
+ private static final int PARTSUPP_SIZE = 80 * SUPPLIER_SIZE;
+ private static final int ORDERS_SIZE = 1_500_000;
+ // TPC-H 4.2.5.1 Table 3: Estimated Database Size
+ private static final int LINEITEM_SIZE = 4 * ORDERS_SIZE;
+ private static final int CUSTOMER_SIZE = 150_000;
+ private static final int NATION_SIZE = 25;
+ private static final int REGION_SIZE = 5;
+
+ private TpchSchema() {
+
+ }
+
+ /**
+ * Registers tables from the TPC-H benchmark in the given {@link
ClusterBuilder cluster builder} with the scaling factor of {@code 1}.
+ *
+ * @param clusterBuilder A cluster builder.
+ * @param dataSize The number of rows data provider is going to produce
for each table.
+ */
+ public static void registerTables(ClusterBuilder clusterBuilder, int
dataSize) {
+ registerTables(clusterBuilder, 1, dataSize);
+ }
+
+ /**
+ * Registers tables from the TPC-H benchmark in the given cluster with the
given scaling factor.
+ *
+ * @param clusterBuilder A cluster builder.
+ * @param scalingFactor Scaling factor.
+ * @param dataSize The number of rows data provider is going to produce
for each table.
+ */
+ public static void registerTables(ClusterBuilder clusterBuilder, int
scalingFactor, int dataSize) {
+ // Register default data provider factory that is going to generate
pseudo random data data.
+ clusterBuilder.defaultDataProviderFactory(new
RepeatedRandomRowDataProviderFactory(dataSize));
+
+ clusterBuilder.addTable().name("PART")
+ .addColumn("P_PARTKEY", NativeTypes.INT64)
+ .addColumn("P_NAME", NativeTypes.stringOf(55))
+ .addColumn("P_MFGR", NativeTypes.stringOf(25))
+ .addColumn("P_BRAND", NativeTypes.stringOf(10))
+ .addColumn("P_TYPE", NativeTypes.stringOf(25))
+ .addColumn("P_SIZE", NativeTypes.INT32)
+ .addColumn("P_CONTAINER", NativeTypes.stringOf(10))
+ .addColumn("P_RETAILPRICE", NativeTypes.decimalOf(15, 2))
+ .addColumn("P_COMMENT", NativeTypes.stringOf(23))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * PART_SIZE).end();
+
+ clusterBuilder.addTable().name("SUPPLIER")
+ .addColumn("S_SUPPKEY", NativeTypes.INT64)
+ .addColumn("S_NAME", NativeTypes.stringOf(25))
+ .addColumn("S_ADDRESS", NativeTypes.stringOf(40))
+ .addColumn("S_NATIONKEY", NativeTypes.INT64)
+ .addColumn("S_PHONE", NativeTypes.stringOf(15))
+ .addColumn("S_ACCTBAL", NativeTypes.decimalOf(15, 2))
+ .addColumn("S_COMMENT", NativeTypes.stringOf(101))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * SUPPLIER_SIZE).end();
+
+ clusterBuilder.addTable().name("PARTSUPP")
+ .addColumn("PS_PARTKEY", NativeTypes.INT64)
+ .addColumn("PS_SUPPKEY", NativeTypes.INT64)
+ .addColumn("PS_AVAILQTY", NativeTypes.INT32)
+ .addColumn("PS_SUPPLYCOST", NativeTypes.decimalOf(15, 2))
+ .addColumn("PS_COMMENT", NativeTypes.stringOf(199))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * PARTSUPP_SIZE).end();
+
+ clusterBuilder.addTable().name("CUSTOMER")
+ .addColumn("C_CUSTKEY", NativeTypes.INT64)
+ .addColumn("C_NAME", NativeTypes.stringOf(25))
+ .addColumn("C_ADDRESS", NativeTypes.stringOf(40))
+ .addColumn("C_NATIONKEY", NativeTypes.INT64)
+ .addColumn("C_PHONE", NativeTypes.stringOf(15))
+ .addColumn("C_ACCTBAL", NativeTypes.decimalOf(15, 2))
+ .addColumn("C_MKTSEGMENT", NativeTypes.stringOf(10))
+ .addColumn("C_COMMENT", NativeTypes.stringOf(117))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * CUSTOMER_SIZE)
+ .end();
+
+ clusterBuilder.addTable().name("ORDERS")
+ .addColumn("O_ORDERKEY", NativeTypes.INT64)
+ .addColumn("O_CUSTKEY", NativeTypes.INT64)
+ .addColumn("O_ORDERSTATUS", NativeTypes.stringOf(1))
+ .addColumn("O_TOTALPRICE", NativeTypes.decimalOf(15, 2))
+ .addColumn("O_ORDERDATE", NativeTypes.datetime())
+ .addColumn("O_ORDERPRIORITY", NativeTypes.stringOf(15))
+ .addColumn("O_CLERK", NativeTypes.stringOf(15))
+ .addColumn("O_SHIPPRIORITY", NativeTypes.INT32)
+ .addColumn("O_COMMENT", NativeTypes.stringOf(79))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * ORDERS_SIZE)
+ .end();
+
+ clusterBuilder.addTable().name("LINEITEM")
+ .addColumn("L_ORDERKEY", NativeTypes.INT64)
+ .addColumn("L_PARTKEY", NativeTypes.INT64)
+ .addColumn("L_SUPPKEY", NativeTypes.INT64)
+ .addColumn("L_LINENUMBER", NativeTypes.INT32)
+ .addColumn("L_QUANTITY", NativeTypes.decimalOf(15, 2))
+ .addColumn("L_EXTENDEDPRICE", NativeTypes.decimalOf(15, 2))
+ .addColumn("L_DISCOUNT", NativeTypes.decimalOf(15, 2))
+ .addColumn("L_TAX", NativeTypes.decimalOf(15, 2))
+ .addColumn("L_RETURNFLAG", NativeTypes.stringOf(1))
+ .addColumn("L_LINESTATUS", NativeTypes.stringOf(1))
+ .addColumn("L_SHIPDATE", NativeTypes.datetime())
+ .addColumn("L_COMMITDATE", NativeTypes.datetime())
+ .addColumn("L_RECEIPTDATE", NativeTypes.datetime())
+ .addColumn("L_SHIPINSTRUCT", NativeTypes.stringOf(25))
+ .addColumn("L_SHIPMODE", NativeTypes.stringOf(10))
+ .addColumn("L_COMMENT", NativeTypes.stringOf(44))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(scalingFactor * LINEITEM_SIZE)
+ .end();
+
+ clusterBuilder.addTable().name("NATION")
+ .addColumn("N_NATIONKEY", NativeTypes.INT64)
+ .addColumn("N_NAME", NativeTypes.stringOf(25))
+ .addColumn("N_REGIONKEY", NativeTypes.INT64)
+ .addColumn("N_COMMENT", NativeTypes.stringOf(152))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(NATION_SIZE)
+ .end();
+
+ clusterBuilder.addTable().name("REGION")
+ .addColumn("R_REGIONKEY", NativeTypes.INT64)
+ .addColumn("R_NAME", NativeTypes.stringOf(25))
+ .addColumn("N_COMMENT", NativeTypes.stringOf(152))
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .size(REGION_SIZE)
+ .end();
+
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestBuilders.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestBuilders.java
index 02cb0772d1..01099be428 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestBuilders.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestBuilders.java
@@ -93,6 +93,17 @@ public class TestBuilders {
*/
ClusterTableBuilder addTable();
+ /**
+ * When specified the given factory is used to create instances of
+ * {@link ClusterTableBuilder#defaultDataProvider(DataProvider)
default data providers} for tables
+ * that have no {@link
ClusterTableBuilder#defaultDataProvider(DataProvider) default data provider}
set.
+ *
+ * <p>Note: when a table has default data provider this method has no
effect.
+ *
+ * @return {@code this} for chaining.
+ */
+ ClusterBuilder defaultDataProviderFactory(DataProviderFactory
dataProviderFactory);
+
/**
* Builds the cluster object.
*
@@ -213,6 +224,7 @@ public class TestBuilders {
private static class ClusterBuilderImpl implements ClusterBuilder {
private final List<ClusterTableBuilderImpl> tableBuilders = new
ArrayList<>();
+ private DataProviderFactory dataProviderFactory;
private List<String> nodeNames;
/** {@inheritDoc} */
@@ -232,6 +244,13 @@ public class TestBuilders {
return new ClusterTableBuilderImpl(this);
}
+ /** {@inheritDoc} */
+ @Override
+ public ClusterBuilder defaultDataProviderFactory(DataProviderFactory
dataProviderFactory) {
+ this.dataProviderFactory = dataProviderFactory;
+ return this;
+ }
+
/** {@inheritDoc} */
@Override
public TestCluster build() {
@@ -268,7 +287,11 @@ public class TestBuilders {
private void injectDataProvidersIfNeeded(ClusterTableBuilderImpl
tableBuilder) {
if (tableBuilder.defaultDataProvider == null) {
- return;
+ if (dataProviderFactory != null) {
+ tableBuilder.defaultDataProvider =
dataProviderFactory.createDataProvider(tableBuilder.name, tableBuilder.columns);
+ } else {
+ return;
+ }
}
Set<String> nodesWithoutDataProvider = new HashSet<>(nodeNames);
@@ -335,6 +358,23 @@ public class TestBuilders {
}
}
+ /**
+ * A factory that creates {@link DataProvider data providers}.
+ */
+ @FunctionalInterface
+ public interface DataProviderFactory {
+
+ /**
+ * Creates a {@link DataProvider} for the given table.
+ *
+ * @param tableName a table name.
+ * @param columns a list of columns.
+ *
+ * @return an instance of {@link DataProvider}.
+ */
+ DataProvider<Object[]> createDataProvider(String tableName,
List<ColumnDescriptor> columns);
+ }
+
private abstract static class AbstractTableBuilderImpl<ChildT> implements
TableBuilderBase<ChildT> {
protected final List<ColumnDescriptor> columns = new ArrayList<>();
protected final Map<String, DataProvider<?>> dataProviders = new
HashMap<>();
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestClusterTest.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestClusterTest.java
new file mode 100644
index 0000000000..ceed84c608
--- /dev/null
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestClusterTest.java
@@ -0,0 +1,70 @@
+/*
+ * 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.ignite.internal.sql.engine.framework;
+
+import static org.apache.ignite.internal.testframework.IgniteTestUtils.await;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+
+import java.util.List;
+import java.util.UUID;
+import org.apache.ignite.internal.schema.NativeTypes;
+import org.apache.ignite.internal.sql.engine.trait.IgniteDistributions;
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Tests for test execution runtime used in benchmarking.
+ */
+public class TestClusterTest {
+
+ private final DataProvider<Object[]> dataProvider = DataProvider.fromRow(
+ new Object[]{42, UUID.randomUUID().toString()}, 3_333
+ );
+
+ // @formatter:off
+ private final TestCluster cluster = TestBuilders.cluster().nodes("N1")
+ .addTable()
+ .name("T1")
+ .distribution(IgniteDistributions.hash(List.of(0)))
+ .addColumn("ID", NativeTypes.INT32)
+ .addColumn("VAL", NativeTypes.stringOf(64))
+ .defaultDataProvider(dataProvider)
+ .end()
+ .build();
+ // @formatter:on
+
+ @AfterEach
+ public void stopCluster() throws Exception {
+ cluster.stop();
+ }
+
+ /**
+ * Runs a simple SELECT query.
+ */
+ @Test
+ public void testSimpleQuery() {
+ cluster.start();
+
+ var gatewayNode = cluster.node("N1");
+ var plan = gatewayNode.prepare("SELECT * FROM t1");
+
+ for (var row :
await(gatewayNode.executePlan(plan).requestNextAsync(10_000)).items()) {
+ assertNotNull(row);
+ }
+ }
+}
diff --git
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestNode.java
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestNode.java
index 94277e04a4..db9a15531c 100644
---
a/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestNode.java
+++
b/modules/sql-engine/src/test/java/org/apache/ignite/internal/sql/engine/framework/TestNode.java
@@ -23,13 +23,18 @@ import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.hasSize;
import static org.mockito.Mockito.mock;
+import java.net.InetSocketAddress;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
+import java.util.UUID;
+import java.util.concurrent.CompletableFuture;
import java.util.stream.Collectors;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.tools.Frameworks;
+import org.apache.ignite.internal.hlc.HybridTimestamp;
+import org.apache.ignite.internal.replicator.ReplicationGroupId;
import org.apache.ignite.internal.sql.engine.AsyncCursor;
import org.apache.ignite.internal.sql.engine.QueryCancel;
import org.apache.ignite.internal.sql.engine.exec.ArrayRowHandler;
@@ -59,11 +64,18 @@ import
org.apache.ignite.internal.sql.engine.schema.SqlSchemaManager;
import org.apache.ignite.internal.sql.engine.util.BaseQueryContext;
import org.apache.ignite.internal.sql.engine.util.Commons;
import org.apache.ignite.internal.sql.engine.util.HashFunctionFactoryImpl;
+import org.apache.ignite.internal.tx.InternalTransaction;
+import org.apache.ignite.internal.tx.TxState;
import org.apache.ignite.internal.util.IgniteSpinBusyLock;
import org.apache.ignite.internal.util.IgniteUtils;
+import org.apache.ignite.lang.IgniteBiTuple;
+import org.apache.ignite.network.ClusterNode;
import org.apache.ignite.network.ClusterService;
import org.apache.ignite.network.MessagingService;
+import org.apache.ignite.network.NetworkAddress;
import org.apache.ignite.network.TopologyService;
+import org.apache.ignite.tx.TransactionException;
+import org.jetbrains.annotations.NotNull;
/**
* An object representing a node in test cluster.
@@ -188,7 +200,7 @@ public class TestNode implements LifecycleAware {
Frameworks.newConfigBuilder(FRAMEWORK_CONFIG)
.defaultSchema(schema)
.build()
- )
+ ).transaction(new TestInternalTransaction(nodeName))
.build();
}
@@ -197,4 +209,89 @@ public class TestNode implements LifecycleAware {
return service;
}
+
+ private static final class TestInternalTransaction implements
InternalTransaction {
+
+ private final UUID id = UUID.randomUUID();
+
+ private final HybridTimestamp hybridTimestamp = new HybridTimestamp(1,
1);
+
+ private final IgniteBiTuple<ClusterNode, Long> tuple;
+
+ private final ReplicationGroupId groupId = new ReplicationGroupId() {
+
+ private static final long serialVersionUID = -6498147568339477517L;
+ };
+
+ public TestInternalTransaction(String name) {
+ var networkAddress = NetworkAddress.from(new
InetSocketAddress("localhost", 1234));
+ tuple = new IgniteBiTuple<>(new ClusterNode(name, name,
networkAddress), 1L);
+ }
+
+ @Override
+ public void commit() throws TransactionException {
+
+ }
+
+ @Override
+ public CompletableFuture<Void> commitAsync() {
+ return CompletableFuture.completedFuture(null);
+ }
+
+ @Override
+ public void rollback() throws TransactionException {
+
+ }
+
+ @Override
+ public CompletableFuture<Void> rollbackAsync() {
+ return CompletableFuture.completedFuture(null);
+ }
+
+ @Override
+ public boolean isReadOnly() {
+ return true;
+ }
+
+ @Override
+ public HybridTimestamp readTimestamp() {
+ return hybridTimestamp;
+ }
+
+ @Override
+ public @NotNull UUID id() {
+ return id;
+ }
+
+ @Override
+ public IgniteBiTuple<ClusterNode, Long>
enlistedNodeAndTerm(ReplicationGroupId replicationGroupId) {
+ return tuple;
+ }
+
+ @Override
+ public TxState state() {
+ return TxState.COMMITED;
+ }
+
+ @Override
+ public boolean assignCommitPartition(ReplicationGroupId
replicationGroupId) {
+ return true;
+ }
+
+ @Override
+ public ReplicationGroupId commitPartition() {
+ return groupId;
+ }
+
+ @Override
+ public IgniteBiTuple<ClusterNode, Long> enlist(ReplicationGroupId
replicationGroupId,
+ IgniteBiTuple<ClusterNode, Long> nodeAndTerm) {
+ return nodeAndTerm;
+ }
+
+ @Override
+ public void enlistResultFuture(CompletableFuture<?> resultFuture) {
+ resultFuture.complete(null);
+ }
+ }
}
diff --git a/modules/sql-engine/src/test/resources/tpch/q1.sql
b/modules/sql-engine/src/test/resources/tpch/q1.sql
new file mode 100644
index 0000000000..22480738d6
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q1.sql
@@ -0,0 +1,24 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+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
+ lineitem
+WHERE
+ l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
+GROUP BY
+ l_returnflag,
+ l_linestatus
+ORDER BY
+ l_returnflag,
+ l_linestatus
diff --git a/modules/sql-engine/src/test/resources/tpch/q10.sql
b/modules/sql-engine/src/test/resources/tpch/q10.sql
new file mode 100644
index 0000000000..0ba0bb540a
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q10.sql
@@ -0,0 +1,35 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ c_custkey,
+ c_name,
+ sum(l_extendedprice * (1 - l_discount)) AS revenue,
+ c_acctbal,
+ n_name,
+ c_address,
+ c_phone,
+ c_comment
+FROM
+ customer,
+ orders,
+ lineitem,
+ nation
+WHERE
+ c_custkey = o_custkey
+ AND l_orderkey = o_orderkey
+ AND o_orderdate >= DATE '1993-10-01'
+ AND o_orderdate < DATE '1993-10-01' + INTERVAL '3' MONTH
+ AND l_returnflag = 'R'
+ AND c_nationkey = n_nationkey
+GROUP BY
+ c_custkey,
+ c_name,
+ c_acctbal,
+ c_phone,
+ n_name,
+ c_address,
+ c_comment
+ORDER BY
+ revenue DESC
+ LIMIT 20
diff --git a/modules/sql-engine/src/test/resources/tpch/q11.sql
b/modules/sql-engine/src/test/resources/tpch/q11.sql
new file mode 100644
index 0000000000..29c4b7bc0c
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q11.sql
@@ -0,0 +1,30 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ ps_partkey,
+ sum(ps_supplycost * ps_availqty) AS value
+FROM
+ partsupp,
+ supplier,
+ nation
+WHERE
+ ps_suppkey = s_suppkey
+ AND s_nationkey = n_nationkey
+ AND n_name = 'GERMANY'
+GROUP BY
+ ps_partkey
+HAVING
+ sum(ps_supplycost * ps_availqty) > (
+ SELECT sum(ps_supplycost * ps_availqty) * 0.0001
+ FROM
+ partsupp,
+ supplier,
+ nation
+ WHERE
+ ps_suppkey = s_suppkey
+ AND s_nationkey = n_nationkey
+ AND n_name = 'GERMANY'
+ )
+ORDER BY
+ value DESC
diff --git a/modules/sql-engine/src/test/resources/tpch/q12.sql
b/modules/sql-engine/src/test/resources/tpch/q12.sql
new file mode 100644
index 0000000000..31a40ce589
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q12.sql
@@ -0,0 +1,31 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ l_shipmode,
+ sum(CASE
+ WHEN o_orderpriority = '1-URGENT'
+ OR o_orderpriority = '2-HIGH'
+ THEN 1
+ ELSE 0
+ END) AS high_line_count,
+ sum(CASE
+ WHEN o_orderpriority <> '1-URGENT'
+ AND o_orderpriority <> '2-HIGH'
+ THEN 1
+ ELSE 0
+ END) AS low_line_count
+FROM
+ orders,
+ lineitem
+WHERE
+ o_orderkey = l_orderkey
+ AND l_shipmode IN ('MAIL', 'SHIP')
+ AND l_commitdate < l_receiptdate
+ AND l_shipdate < l_commitdate
+ AND l_receiptdate >= DATE '1994-01-01'
+ AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+GROUP BY
+ l_shipmode
+ORDER BY
+ l_shipmode
diff --git a/modules/sql-engine/src/test/resources/tpch/q13.sql
b/modules/sql-engine/src/test/resources/tpch/q13.sql
new file mode 100644
index 0000000000..c3ab108c66
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q13.sql
@@ -0,0 +1,23 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ c_count,
+ count(*) AS custdist
+FROM (
+ SELECT
+ c_custkey,
+ count(o_orderkey)
+ FROM
+ customer
+ LEFT OUTER JOIN orders ON
+ c_custkey = o_custkey
+ AND o_comment NOT LIKE '%special%requests%'
+ GROUP BY
+ c_custkey
+ ) AS c_orders (c_custkey, c_count)
+GROUP BY
+ c_count
+ORDER BY
+ custdist DESC,
+ c_count DESC
diff --git a/modules/sql-engine/src/test/resources/tpch/q14.sql
b/modules/sql-engine/src/test/resources/tpch/q14.sql
new file mode 100644
index 0000000000..4971ae7c7d
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q14.sql
@@ -0,0 +1,15 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT 100.00 * sum(CASE
+ WHEN p_type LIKE 'PROMO%'
+ THEN l_extendedprice * (1 - l_discount)
+ ELSE 0
+ END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue
+FROM
+ lineitem,
+ part
+WHERE
+ l_partkey = p_partkey
+ AND l_shipdate >= DATE '1995-09-01'
+ AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH
diff --git a/modules/sql-engine/src/test/resources/tpch/q15.sql
b/modules/sql-engine/src/test/resources/tpch/q15.sql
new file mode 100644
index 0000000000..0bc233adf7
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q15.sql
@@ -0,0 +1,34 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+WITH revenue (supplier_no, total_revenue) as (
+ SELECT
+ l_suppkey,
+ sum(l_extendedprice * (1-l_discount))
+ FROM
+ lineitem
+ WHERE
+ l_shipdate >= DATE '1996-01-01'
+ AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
+ GROUP BY
+ l_suppkey
+)
+SELECT
+ s_suppkey,
+ s_name,
+ s_address,
+ s_phone,
+ total_revenue
+FROM
+ supplier,
+ revenue
+WHERE
+ s_suppkey = supplier_no
+ AND total_revenue = (
+ SELECT
+ max(total_revenue)
+ FROM
+ revenue
+)
+ORDER BY
+ s_suppkey
diff --git a/modules/sql-engine/src/test/resources/tpch/q16.sql
b/modules/sql-engine/src/test/resources/tpch/q16.sql
new file mode 100644
index 0000000000..9598a8c683
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q16.sql
@@ -0,0 +1,32 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ p_brand,
+ p_type,
+ p_size,
+ count(DISTINCT ps_suppkey) AS supplier_cnt
+FROM
+ partsupp,
+ part
+WHERE
+ p_partkey = ps_partkey
+ AND p_brand <> 'Brand#45'
+ AND p_type NOT LIKE 'MEDIUM POLISHED%'
+ AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)
+ AND ps_suppkey NOT IN (
+ SELECT s_suppkey
+ FROM
+ supplier
+ WHERE
+ s_comment LIKE '%Customer%Complaints%'
+)
+GROUP BY
+ p_brand,
+ p_type,
+ p_size
+ORDER BY
+ supplier_cnt DESC,
+ p_brand,
+ p_type,
+ p_size
diff --git a/modules/sql-engine/src/test/resources/tpch/q17.sql
b/modules/sql-engine/src/test/resources/tpch/q17.sql
new file mode 100644
index 0000000000..cd594a9178
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q17.sql
@@ -0,0 +1,18 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT sum(l_extendedprice) / 7.0 AS avg_yearly
+FROM
+ lineitem,
+ part
+WHERE
+ p_partkey = l_partkey
+ AND p_brand = 'Brand#23'
+ AND p_container = 'MED BOX'
+ AND l_quantity < (
+ SELECT 0.2 * avg(l_quantity)
+ FROM
+ lineitem
+ WHERE
+ l_partkey = p_partkey
+)
diff --git a/modules/sql-engine/src/test/resources/tpch/q18.sql
b/modules/sql-engine/src/test/resources/tpch/q18.sql
new file mode 100644
index 0000000000..5b0d0d4f44
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q18.sql
@@ -0,0 +1,36 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice,
+ sum(l_quantity)
+FROM
+ customer,
+ orders,
+ lineitem
+WHERE
+ o_orderkey IN (
+ SELECT l_orderkey
+ FROM
+ lineitem
+ GROUP BY
+ l_orderkey
+ HAVING
+ sum(l_quantity) > 300
+ )
+ AND c_custkey = o_custkey
+ AND o_orderkey = l_orderkey
+GROUP BY
+ c_name,
+ c_custkey,
+ o_orderkey,
+ o_orderdate,
+ o_totalprice
+ORDER BY
+ o_totalprice DESC,
+ o_orderdate
+ LIMIT 100
diff --git a/modules/sql-engine/src/test/resources/tpch/q19.sql
b/modules/sql-engine/src/test/resources/tpch/q19.sql
new file mode 100644
index 0000000000..5945c5f7cd
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q19.sql
@@ -0,0 +1,37 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT sum(l_extendedprice * (1 - l_discount)) AS revenue
+FROM
+ lineitem,
+ part
+WHERE
+ (
+ p_partkey = l_partkey
+ AND p_brand = 'Brand#12'
+ AND p_container IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
+ AND l_quantity >= 1 AND l_quantity <= 1 + 10
+ AND p_size BETWEEN 1 AND 5
+ AND l_shipmode IN ('AIR', 'AIR REG')
+ AND l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ OR
+ (
+ p_partkey = l_partkey
+ AND p_brand = 'Brand#23'
+ AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
+ AND l_quantity >= 10 AND l_quantity <= 10 + 10
+ AND p_size BETWEEN 1 AND 10
+ AND l_shipmode IN ('AIR', 'AIR REG')
+ AND l_shipinstruct = 'DELIVER IN PERSON'
+ )
+ OR
+ (
+ p_partkey = l_partkey
+ AND p_brand = 'Brand#34'
+ AND p_container IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
+ AND l_quantity >= 20 AND l_quantity <= 20 + 10
+ AND p_size BETWEEN 1 AND 15
+ AND l_shipmode IN ('AIR', 'AIR REG')
+ AND l_shipinstruct = 'DELIVER IN PERSON'
+ )
diff --git a/modules/sql-engine/src/test/resources/tpch/q2.sql
b/modules/sql-engine/src/test/resources/tpch/q2.sql
new file mode 100644
index 0000000000..b82bd046aa
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q2.sql
@@ -0,0 +1,44 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ s_acctbal,
+ s_name,
+ n_name,
+ p_partkey,
+ p_mfgr,
+ s_address,
+ s_phone,
+ s_comment
+FROM
+ part,
+ supplier,
+ partsupp,
+ nation,
+ region
+WHERE
+ p_partkey = ps_partkey
+ AND s_suppkey = ps_suppkey
+ AND p_size = 15
+ AND p_type LIKE '%BRASS'
+ AND s_nationkey = n_nationkey
+ AND n_regionkey = r_regionkey
+ AND r_name = 'EUROPE'
+ AND ps_supplycost = (
+ SELECT min(ps_supplycost)
+ FROM
+ partsupp, supplier,
+ nation, region
+ WHERE
+ p_partkey = ps_partkey
+ AND s_suppkey = ps_suppkey
+ AND s_nationkey = n_nationkey
+ AND n_regionkey = r_regionkey
+ AND r_name = 'EUROPE'
+)
+ORDER BY
+ s_acctbal DESC,
+ n_name,
+ s_name,
+ p_partkey
+ LIMIT 100
diff --git a/modules/sql-engine/src/test/resources/tpch/q20.sql
b/modules/sql-engine/src/test/resources/tpch/q20.sql
new file mode 100644
index 0000000000..41ce83f04d
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q20.sql
@@ -0,0 +1,35 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ s_name,
+ s_address
+FROM
+ supplier, nation
+WHERE
+ s_suppkey IN (
+ SELECT ps_suppkey
+ FROM
+ partsupp
+ WHERE
+ ps_partkey IN (
+ SELECT p_partkey
+ FROM
+ part
+ WHERE
+ p_name LIKE 'forest%'
+ )
+ AND ps_availqty > (
+ SELECT 0.5 * sum(l_quantity)
+ FROM
+ lineitem
+ WHERE
+ l_partkey = ps_partkey
+ AND l_suppkey = ps_suppkey
+ AND l_shipdate >= date('1994-01-01')
+ AND l_shipdate < date('1994-01-01') + interval '1' YEAR
+ )
+ )
+ AND s_nationkey = n_nationkey
+ AND n_name = 'CANADA'
+ORDER BY s_name
diff --git a/modules/sql-engine/src/test/resources/tpch/q21.sql
b/modules/sql-engine/src/test/resources/tpch/q21.sql
new file mode 100644
index 0000000000..0373124f1a
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q21.sql
@@ -0,0 +1,41 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ s_name,
+ count(*) AS numwait
+FROM
+ supplier,
+ lineitem l1,
+ orders,
+ nation
+WHERE
+ s_suppkey = l1.l_suppkey
+ AND o_orderkey = l1.l_orderkey
+ AND o_orderstatus = 'F'
+ AND l1.l_receiptdate > l1.l_commitdate
+ AND exists(
+ SELECT *
+ FROM
+ lineitem l2
+ WHERE
+ l2.l_orderkey = l1.l_orderkey
+ AND l2.l_suppkey <> l1.l_suppkey
+ )
+ AND NOT exists(
+ SELECT *
+ FROM
+ lineitem l3
+ WHERE
+ l3.l_orderkey = l1.l_orderkey
+ AND l3.l_suppkey <> l1.l_suppkey
+ AND l3.l_receiptdate > l3.l_commitdate
+ )
+ AND s_nationkey = n_nationkey
+ AND n_name = 'SAUDI ARABIA'
+GROUP BY
+ s_name
+ORDER BY
+ numwait DESC,
+ s_name
+ LIMIT 100
diff --git a/modules/sql-engine/src/test/resources/tpch/q22.sql
b/modules/sql-engine/src/test/resources/tpch/q22.sql
new file mode 100644
index 0000000000..d24b936bd4
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q22.sql
@@ -0,0 +1,37 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ cntrycode,
+ count(*) AS numcust,
+ sum(c_acctbal) AS totacctbal
+FROM (
+ SELECT
+ substr(c_phone, 1, 2) AS cntrycode,
+ c_acctbal
+ FROM
+ customer
+ WHERE
+ substr(c_phone, 1, 2) IN
+ ('13', '31', '23', '29', '30', '18', '17')
+ AND c_acctbal > (
+ SELECT avg(c_acctbal)
+ FROM
+ customer
+ WHERE
+ c_acctbal > 0.00
+ AND substr(c_phone, 1, 2) IN
+ ('13', '31', '23', '29', '30', '18', '17')
+ )
+ AND NOT exists(
+ SELECT *
+ FROM
+ orders
+ WHERE
+ o_custkey = c_custkey
+ )
+ ) AS custsale
+GROUP BY
+ cntrycode
+ORDER BY
+ cntrycode
diff --git a/modules/sql-engine/src/test/resources/tpch/q3.sql
b/modules/sql-engine/src/test/resources/tpch/q3.sql
new file mode 100644
index 0000000000..8a6eab5ce9
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q3.sql
@@ -0,0 +1,26 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ l_orderkey,
+ sum(l_extendedprice * (1 - l_discount)) AS revenue,
+ o_orderdate,
+ o_shippriority
+FROM
+ customer,
+ orders,
+ lineitem
+WHERE
+ c_mktsegment = 'BUILDING'
+ AND c_custkey = o_custkey
+ AND l_orderkey = o_orderkey
+ AND o_orderdate < DATE '1995-03-15'
+ AND l_shipdate > DATE '1995-03-15'
+GROUP BY
+ l_orderkey,
+ o_orderdate,
+ o_shippriority
+ORDER BY
+ revenue DESC,
+ o_orderdate
+ LIMIT 10
diff --git a/modules/sql-engine/src/test/resources/tpch/q4.sql
b/modules/sql-engine/src/test/resources/tpch/q4.sql
new file mode 100644
index 0000000000..18ca1817e0
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q4.sql
@@ -0,0 +1,21 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ o_orderpriority,
+ count(*) AS order_count
+FROM orders
+WHERE
+ o_orderdate >= DATE '1993-07-01'
+ AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
+ AND EXISTS (
+ SELECT *
+ FROM lineitem
+ WHERE
+ l_orderkey = o_orderkey
+ AND l_commitdate < l_receiptdate
+ )
+GROUP BY
+ o_orderpriority
+ORDER BY
+ o_orderpriority
diff --git a/modules/sql-engine/src/test/resources/tpch/q5.sql
b/modules/sql-engine/src/test/resources/tpch/q5.sql
new file mode 100644
index 0000000000..2a07e3a5de
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q5.sql
@@ -0,0 +1,27 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ n_name,
+ sum(l_extendedprice * (1 - l_discount)) AS revenue
+FROM
+ customer,
+ orders,
+ lineitem,
+ supplier,
+ nation,
+ region
+WHERE
+ c_custkey = o_custkey
+ AND l_orderkey = o_orderkey
+ AND l_suppkey = s_suppkey
+ AND c_nationkey = s_nationkey
+ AND s_nationkey = n_nationkey
+ AND n_regionkey = r_regionkey
+ AND r_name = 'ASIA'
+ AND o_orderdate >= DATE '1994-01-01'
+ AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+GROUP BY
+ n_name
+ORDER BY
+ revenue DESC
diff --git a/modules/sql-engine/src/test/resources/tpch/q6.sql
b/modules/sql-engine/src/test/resources/tpch/q6.sql
new file mode 100644
index 0000000000..075cf6bd1b
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q6.sql
@@ -0,0 +1,11 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT sum(l_extendedprice * l_discount) AS revenue
+FROM
+ lineitem
+WHERE
+ l_shipdate >= DATE '1994-01-01'
+ AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+ AND l_discount BETWEEN decimal '0.06' - decimal '0.01' AND decimal '0.06' +
decimal '0.01'
+ AND l_quantity < 24
diff --git a/modules/sql-engine/src/test/resources/tpch/q7.sql
b/modules/sql-engine/src/test/resources/tpch/q7.sql
new file mode 100644
index 0000000000..a98318a1f3
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q7.sql
@@ -0,0 +1,41 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+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_shipdate) AS l_year,
+ l_extendedprice * (1 - l_discount) AS volume
+ FROM
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2
+ WHERE
+ s_suppkey = l_suppkey
+ AND o_orderkey = l_orderkey
+ AND c_custkey = o_custkey
+ AND s_nationkey = n1.n_nationkey
+ AND c_nationkey = n2.n_nationkey
+ AND (
+ (n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
+ OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
+ )
+ AND 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
diff --git a/modules/sql-engine/src/test/resources/tpch/q8.sql
b/modules/sql-engine/src/test/resources/tpch/q8.sql
new file mode 100644
index 0000000000..617d098c1a
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q8.sql
@@ -0,0 +1,40 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ o_year,
+ sum(CASE
+ WHEN nation = 'BRAZIL'
+ THEN volume
+ ELSE 0
+ END) / sum(volume) AS mkt_share
+FROM (
+ SELECT
+ extract(YEAR FROM o_orderdate) AS o_year,
+ l_extendedprice * (1 - l_discount) AS volume,
+ n2.n_name AS nation
+ FROM
+ part,
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2,
+ region
+ WHERE
+ p_partkey = l_partkey
+ AND s_suppkey = l_suppkey
+ AND l_orderkey = o_orderkey
+ AND o_custkey = c_custkey
+ AND c_nationkey = n1.n_nationkey
+ AND n1.n_regionkey = r_regionkey
+ AND r_name = 'AMERICA'
+ AND s_nationkey = n2.n_nationkey
+ AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
+ AND p_type = 'ECONOMY ANODIZED STEEL'
+ ) AS all_nations
+GROUP BY
+ o_year
+ORDER BY
+ o_year
diff --git a/modules/sql-engine/src/test/resources/tpch/q9.sql
b/modules/sql-engine/src/test/resources/tpch/q9.sql
new file mode 100644
index 0000000000..0fdc07f1bf
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/q9.sql
@@ -0,0 +1,34 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+SELECT
+ nation,
+ o_year,
+ sum(amount) AS sum_profit
+FROM (
+ SELECT
+ n_name
AS nation,
+ extract(YEAR FROM o_orderdate)
AS o_year,
+ l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity
AS amount
+ FROM
+ part,
+ supplier,
+ lineitem,
+ partsupp,
+ orders,
+ nation
+ WHERE
+ s_suppkey = l_suppkey
+ AND ps_suppkey = l_suppkey
+ AND ps_partkey = l_partkey
+ AND p_partkey = l_partkey
+ AND o_orderkey = l_orderkey
+ AND s_nationkey = n_nationkey
+ AND p_name LIKE '%green%'
+ ) AS profit
+GROUP BY
+ nation,
+ o_year
+ORDER BY
+ nation,
+ o_year DESC
diff --git a/modules/sql-engine/src/test/resources/tpch/variant_q12.sql
b/modules/sql-engine/src/test/resources/tpch/variant_q12.sql
new file mode 100644
index 0000000000..02bd28364b
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/variant_q12.sql
@@ -0,0 +1,25 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+-- This variant replaces the CASE statement from the Functional Query
Definition with equivalent DECODE() syntax
+
+SELECT
+ l_shipmode,
+ sum(decode(o_orderpriority, '1-URGENT', 1, '2-HIGH', 1, 0)) as
+ high_line_count,
+ sum(decode(o_orderpriority, '1-URGENT', 0, '2-HIGH', 0, 1)) as
+ low_line_count
+FROM
+ orders,
+ lineitem
+WHERE
+ o_orderkey = l_orderkey
+ AND l_shipmode IN ('MAIL', 'SHIP')
+ AND l_commitdate < l_receiptdate
+ AND l_shipdate < l_commitdate
+ AND l_receiptdate >= DATE '1994-01-01'
+ AND l_receiptdate < DATE '1994-01-01' + INTERVAL '1' YEAR
+GROUP BY
+ l_shipmode
+ORDER BY
+ l_shipmode
diff --git a/modules/sql-engine/src/test/resources/tpch/variant_q14.sql
b/modules/sql-engine/src/test/resources/tpch/variant_q14.sql
new file mode 100644
index 0000000000..4738c3b13b
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/variant_q14.sql
@@ -0,0 +1,16 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+-- -- This variant replaces the CASE statement from the Functional Query
Definition with equivalent DECODE() syntax
+
+SELECT
+ 100.00 * sum(decode(substring(p_type from 1 for 5), 'PROMO',
+ l_extendedprice * (1-l_discount), 0)) /
+ sum(l_extendedprice * (1-l_discount)) as promo_revenue
+FROM
+ lineitem,
+ part
+WHERE
+ l_partkey = p_partkey
+ AND l_shipdate >= DATE '1995-09-01'
+ AND l_shipdate < DATE '1995-09-01' + INTERVAL '1' MONTH
diff --git a/modules/sql-engine/src/test/resources/tpch/variant_q8.sql
b/modules/sql-engine/src/test/resources/tpch/variant_q8.sql
new file mode 100644
index 0000000000..a93f0b73d0
--- /dev/null
+++ b/modules/sql-engine/src/test/resources/tpch/variant_q8.sql
@@ -0,0 +1,38 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+
+-- This variant replaces the CASE statement from the Functional Query
Definition with equivalent DECODE() syntax
+
+SELECT
+ o_year,
+ sum(decode(nation, 'BRAZIL', volume, 0)) / sum(volume) as mkt_share
+FROM (
+ SELECT
+ extract(YEAR FROM o_orderdate) AS o_year,
+ l_extendedprice * (1 - l_discount) AS volume,
+ n2.n_name AS nation
+ FROM
+ part,
+ supplier,
+ lineitem,
+ orders,
+ customer,
+ nation n1,
+ nation n2,
+ region
+ WHERE
+ p_partkey = l_partkey
+ AND s_suppkey = l_suppkey
+ AND l_orderkey = o_orderkey
+ AND o_custkey = c_custkey
+ AND c_nationkey = n1.n_nationkey
+ AND n1.n_regionkey = r_regionkey
+ AND r_name = 'AMERICA'
+ AND s_nationkey = n2.n_nationkey
+ AND o_orderdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
+ AND p_type = 'ECONOMY ANODIZED STEEL'
+ ) AS all_nations
+GROUP BY
+ o_year
+ORDER BY
+ o_year
