You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2020/10/11 23:23:59 UTC
[calcite] 01/01: [CALCITE-4034] InnoDB adapter (neoremind)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit ebefe52f577da5c744ea863480ed3c98b7ef2e99
Author: xu <xu...@gmail.com>
AuthorDate: Fri Jun 12 15:56:35 2020 +0800
[CALCITE-4034] InnoDB adapter (neoremind)
InnoDB is a storage engine for MySQL, but it can also be used
as a standlone file format. This adapter adds a SQL interface
to InnoDB that uses Calcite rather than MySQL.
This adapter handles Sarg by expanding to an OR of ranges. A
better implementation of Sarg is probably possible. The
current implementation can only handle Sargs that result in
an AND, e.g. x >= 10 AND x <= 20). But we ought to handle
Sargs that can result in an OR of ANDs. E.g. the SQL
x BETWEEN 10 AND 20 OR c > 30
becomes a single RexCall
SEARCH(x, Sarg([10, 20], (30, +inf)))
and results in an OR of ANDs, '(x >= 10 AND x <= 20) OR (x > 30)'.
Tweaks (Julian Hyde):
* Add Holder.accept
* Make IndexCondition immutable
* Move computation out of InnodbFilter's constructor
Close apache/calcite#1996
---
bom/build.gradle.kts | 1 +
build.gradle.kts | 6 +-
.../calcite/config/CalciteSystemProperty.java | 6 +
.../java/org/apache/calcite/prepare/Prepare.java | 4 +-
.../main/java/org/apache/calcite/runtime/Hook.java | 3 +
.../main/java/org/apache/calcite/util/Holder.java | 8 +
gradle.properties | 1 +
.../util/Holder.java => innodb/build.gradle.kts | 44 +-
innodb/gradle.properties | 18 +
.../innodb/ColumnTypeToSqlTypeConversionRules.java | 105 ++
.../calcite/adapter/innodb/IndexCondition.java | 357 ++++++
.../calcite/adapter/innodb/InnodbEnumerator.java | 149 +++
.../calcite/adapter/innodb/InnodbFilter.java | 97 ++
.../adapter/innodb/InnodbFilterTranslator.java | 502 ++++++++
.../calcite/adapter/innodb/InnodbMethod.java | 51 +
.../calcite/adapter/innodb/InnodbProject.java | 71 ++
.../apache/calcite/adapter/innodb/InnodbRel.java | 64 +
.../apache/calcite/adapter/innodb/InnodbRules.java | 371 ++++++
.../calcite/adapter/innodb/InnodbSchema.java | 137 +++
.../adapter/innodb/InnodbSchemaFactory.java | 46 +
.../apache/calcite/adapter/innodb/InnodbSort.java | 73 ++
.../apache/calcite/adapter/innodb/InnodbTable.java | 274 +++++
.../calcite/adapter/innodb/InnodbTableScan.java | 153 +++
.../innodb/InnodbToEnumerableConverter.java | 177 +++
.../innodb/InnodbToEnumerableConverterRule.java | 45 +
.../apache/calcite/adapter/innodb/QueryType.java | 53 +
.../calcite/adapter/innodb/package-info.java | 36 +-
.../adapter/innodb/InnodbAdapterDataTypesTest.java | 210 ++++
.../calcite/adapter/innodb/InnodbAdapterTest.java | 1237 ++++++++++++++++++++
innodb/src/test/resources/README.md | 44 +
innodb/src/test/resources/data/DEPT.ibd | Bin 0 -> 98304 bytes
innodb/src/test/resources/data/EMP.ibd | Bin 0 -> 262144 bytes
innodb/src/test/resources/data/test_types.ibd | Bin 0 -> 131072 bytes
innodb/src/test/resources/data_types.sql | 153 +++
.../src/test/resources/model.json | 53 +-
innodb/src/test/resources/scott.sql | 78 ++
settings.gradle.kts | 1 +
site/_docs/adapter.md | 1 +
site/_docs/innodb_adapter.md | 381 ++++++
39 files changed, 4902 insertions(+), 108 deletions(-)
diff --git a/bom/build.gradle.kts b/bom/build.gradle.kts
index 0132f29..510a437 100644
--- a/bom/build.gradle.kts
+++ b/bom/build.gradle.kts
@@ -51,6 +51,7 @@ dependencies {
// runtime means "the dependency is only for runtime, not for compilation"
// In other words, marking dependency as "runtime" would avoid accidental
// dependency on it during compilation
+ apiv("com.alibaba.database:innodb-java-reader")
apiv("com.beust:jcommander")
apiv("com.datastax.cassandra:cassandra-driver-core")
apiv("com.esri.geometry:esri-geometry-api")
diff --git a/build.gradle.kts b/build.gradle.kts
index 50d8750..764c4ef 100644
--- a/build.gradle.kts
+++ b/build.gradle.kts
@@ -166,9 +166,9 @@ val javadocAggregateIncludingTests by tasks.registering(Javadoc::class) {
}
val adaptersForSqlline = listOf(
- ":babel", ":cassandra", ":druid", ":elasticsearch", ":file", ":geode", ":kafka", ":mongodb",
- ":pig", ":piglet", ":plus", ":redis", ":spark", ":splunk"
-)
+ ":babel", ":cassandra", ":druid", ":elasticsearch",
+ ":file", ":geode", ":innodb", ":kafka", ":mongodb",
+ ":pig", ":piglet", ":plus", ":redis", ":spark", ":splunk")
val dataSetsForSqlline = listOf(
"net.hydromatic:foodmart-data-hsqldb",
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
index 48be219..ac27b01 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteSystemProperty.java
@@ -228,6 +228,12 @@ public final class CalciteSystemProperty<T> {
booleanProperty("calcite.test.cassandra", true);
/**
+ * Whether to run InnoDB tests.
+ */
+ public static final CalciteSystemProperty<Boolean> TEST_INNODB =
+ booleanProperty("calcite.test.innodb", true);
+
+ /**
* Whether to run Redis tests.
*/
public static final CalciteSystemProperty<Boolean> TEST_REDIS =
diff --git a/core/src/main/java/org/apache/calcite/prepare/Prepare.java b/core/src/main/java/org/apache/calcite/prepare/Prepare.java
index a5ae7e5..e99b2d3 100644
--- a/core/src/main/java/org/apache/calcite/prepare/Prepare.java
+++ b/core/src/main/java/org/apache/calcite/prepare/Prepare.java
@@ -226,8 +226,10 @@ public abstract class Prepare {
.withTrimUnusedFields(true)
.withExpand(THREAD_EXPAND.get())
.withExplain(sqlQuery.getKind() == SqlKind.EXPLAIN);
+ final Holder<SqlToRelConverter.Config> configHolder = Holder.of(config);
+ Hook.SQL2REL_CONVERTER_CONFIG_BUILDER.run(configHolder);
final SqlToRelConverter sqlToRelConverter =
- getSqlToRelConverter(validator, catalogReader, config);
+ getSqlToRelConverter(validator, catalogReader, configHolder.get());
SqlExplain sqlExplain = null;
if (sqlQuery.getKind() == SqlKind.EXPLAIN) {
diff --git a/core/src/main/java/org/apache/calcite/runtime/Hook.java b/core/src/main/java/org/apache/calcite/runtime/Hook.java
index 2f531db..9f36913 100644
--- a/core/src/main/java/org/apache/calcite/runtime/Hook.java
+++ b/core/src/main/java/org/apache/calcite/runtime/Hook.java
@@ -64,6 +64,9 @@ public enum Hook {
* Janino. */
JAVA_PLAN,
+ /** Called before SqlToRelConverter is built. */
+ SQL2REL_CONVERTER_CONFIG_BUILDER,
+
/** Called with the output of sql-to-rel-converter. */
CONVERTED,
diff --git a/core/src/main/java/org/apache/calcite/util/Holder.java b/core/src/main/java/org/apache/calcite/util/Holder.java
index 504b9d4..df36759 100644
--- a/core/src/main/java/org/apache/calcite/util/Holder.java
+++ b/core/src/main/java/org/apache/calcite/util/Holder.java
@@ -16,6 +16,8 @@
*/
package org.apache.calcite.util;
+import java.util.function.UnaryOperator;
+
/**
* A mutable slot that can contain one object.
*
@@ -46,6 +48,12 @@ public class Holder<E> {
return e;
}
+ /** Applies a transform to the value. */
+ public Holder<E> accept(UnaryOperator<E> transform) {
+ e = transform.apply(e);
+ return this;
+ }
+
/** Creates a holder containing a given value. */
public static <E> Holder<E> of(E e) {
return new Holder<>(e);
diff --git a/gradle.properties b/gradle.properties
index 8cb42a1..69b93bc 100644
--- a/gradle.properties
+++ b/gradle.properties
@@ -97,6 +97,7 @@ hsqldb.version=2.4.1
httpclient.version=4.5.9
httpcore.version=4.4.11
hydromatic.tpcds.version=0.4
+innodb-java-reader.version=1.0.10
jackson-databind.version=2.9.10.1
jackson.version=2.10.0
janino.version=3.0.11
diff --git a/core/src/main/java/org/apache/calcite/util/Holder.java b/innodb/build.gradle.kts
similarity index 51%
copy from core/src/main/java/org/apache/calcite/util/Holder.java
copy to innodb/build.gradle.kts
index 504b9d4..613fe37 100644
--- a/core/src/main/java/org/apache/calcite/util/Holder.java
+++ b/innodb/build.gradle.kts
@@ -14,40 +14,16 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-package org.apache.calcite.util;
+dependencies {
+ api(project(":core"))
+ api(project(":linq4j"))
-/**
- * A mutable slot that can contain one object.
- *
- * <p>A holder is useful for implementing OUT or IN-OUT parameters.</p>
- *
- * <p>It is possible to sub-class to receive events on get or set.</p>
- *
- * @param <E> Element type
- */
-public class Holder<E> {
- private E e;
-
- /** Creates a Holder containing a given value.
- *
- * <p>Call this method from a derived constructor or via the {@link #of}
- * method. */
- protected Holder(E e) {
- this.e = e;
- }
-
- /** Sets the value. */
- public void set(E e) {
- this.e = e;
- }
-
- /** Gets the value. */
- public E get() {
- return e;
- }
+ implementation("com.alibaba.database:innodb-java-reader")
+ implementation("com.google.guava:guava")
+ implementation("org.apache.calcite.avatica:avatica-core")
+ implementation("org.apache.commons:commons-lang3")
+ implementation("org.slf4j:slf4j-api")
- /** Creates a holder containing a given value. */
- public static <E> Holder<E> of(E e) {
- return new Holder<>(e);
- }
+ testImplementation(project(":core", "testClasses"))
+ testRuntimeOnly("org.slf4j:slf4j-log4j12")
}
diff --git a/innodb/gradle.properties b/innodb/gradle.properties
new file mode 100644
index 0000000..a93bc68
--- /dev/null
+++ b/innodb/gradle.properties
@@ -0,0 +1,18 @@
+#
+# 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.
+#
+description=InnoDB adapter for Calcite
+artifact.name=Calcite Innodb
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/ColumnTypeToSqlTypeConversionRules.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/ColumnTypeToSqlTypeConversionRules.java
new file mode 100644
index 0000000..f6b7f16
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/ColumnTypeToSqlTypeConversionRules.java
@@ -0,0 +1,105 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.sql.type.SqlTypeName;
+
+import com.alibaba.innodb.java.reader.column.ColumnType;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.Map;
+
+/**
+ * Mappings from <code>innodb-java-reader</code> column types
+ * to corresponding SQL types.
+ */
+public class ColumnTypeToSqlTypeConversionRules {
+ //~ Static fields/initializers ---------------------------------------------
+
+ private static final ColumnTypeToSqlTypeConversionRules INSTANCE =
+ new ColumnTypeToSqlTypeConversionRules();
+
+ //~ Instance fields --------------------------------------------------------
+
+ private final Map<String, SqlTypeName> rules =
+ ImmutableMap.<String, SqlTypeName>builder()
+ .put(ColumnType.TINYINT, SqlTypeName.TINYINT)
+ .put(ColumnType.SMALLINT, SqlTypeName.SMALLINT)
+ .put(ColumnType.MEDIUMINT, SqlTypeName.INTEGER)
+ .put(ColumnType.INT, SqlTypeName.INTEGER)
+ .put(ColumnType.BIGINT, SqlTypeName.BIGINT)
+ .put(ColumnType.UNSIGNED_TINYINT, SqlTypeName.TINYINT)
+ .put(ColumnType.UNSIGNED_SMALLINT, SqlTypeName.SMALLINT)
+ .put(ColumnType.UNSIGNED_MEDIUMINT, SqlTypeName.INTEGER)
+ .put(ColumnType.UNSIGNED_INT, SqlTypeName.INTEGER)
+ .put(ColumnType.UNSIGNED_BIGINT, SqlTypeName.BIGINT)
+
+ .put(ColumnType.FLOAT, SqlTypeName.REAL)
+ .put(ColumnType.REAL, SqlTypeName.REAL)
+ .put(ColumnType.DOUBLE, SqlTypeName.DOUBLE)
+ .put(ColumnType.DECIMAL, SqlTypeName.DECIMAL)
+ .put(ColumnType.NUMERIC, SqlTypeName.DECIMAL)
+
+ .put(ColumnType.BOOL, SqlTypeName.BOOLEAN)
+ .put(ColumnType.BOOLEAN, SqlTypeName.BOOLEAN)
+
+ .put(ColumnType.CHAR, SqlTypeName.CHAR)
+ .put(ColumnType.VARCHAR, SqlTypeName.VARCHAR)
+ .put(ColumnType.BINARY, SqlTypeName.BINARY)
+ .put(ColumnType.VARBINARY, SqlTypeName.VARBINARY)
+ .put(ColumnType.TINYBLOB, SqlTypeName.VARBINARY)
+ .put(ColumnType.MEDIUMBLOB, SqlTypeName.VARBINARY)
+ .put(ColumnType.BLOB, SqlTypeName.VARBINARY)
+ .put(ColumnType.LONGBLOB, SqlTypeName.VARBINARY)
+ .put(ColumnType.TINYTEXT, SqlTypeName.VARCHAR)
+ .put(ColumnType.MEDIUMTEXT, SqlTypeName.VARCHAR)
+ .put(ColumnType.TEXT, SqlTypeName.VARCHAR)
+ .put(ColumnType.LONGTEXT, SqlTypeName.VARCHAR)
+
+ .put(ColumnType.YEAR, SqlTypeName.SMALLINT)
+ .put(ColumnType.TIME, SqlTypeName.TIME)
+ .put(ColumnType.DATE, SqlTypeName.DATE)
+ .put(ColumnType.DATETIME, SqlTypeName.TIMESTAMP)
+ .put(ColumnType.TIMESTAMP, SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)
+
+ .put(ColumnType.ENUM, SqlTypeName.VARCHAR)
+ .put(ColumnType.SET, SqlTypeName.VARCHAR)
+ .put(ColumnType.BIT, SqlTypeName.VARBINARY)
+
+ .build();
+
+ //~ Methods ----------------------------------------------------------------
+
+ /**
+ * Returns the
+ * {@link org.apache.calcite.util.Glossary#SINGLETON_PATTERN singleton}
+ * instance.
+ */
+ public static ColumnTypeToSqlTypeConversionRules instance() {
+ return INSTANCE;
+ }
+
+ /**
+ * Returns a corresponding {@link SqlTypeName} for a given InnoDB type name.
+ *
+ * @param name the column type name to lookup
+ * @return a corresponding SqlTypeName if found, ANY otherwise
+ */
+ public SqlTypeName lookup(String name) {
+ return rules.getOrDefault(name, SqlTypeName.ANY);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/IndexCondition.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/IndexCondition.java
new file mode 100644
index 0000000..5d5dd63
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/IndexCondition.java
@@ -0,0 +1,357 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.util.Pair;
+
+import org.apache.commons.collections.CollectionUtils;
+
+import com.alibaba.innodb.java.reader.comparator.ComparisonOperator;
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
+
+import static com.google.common.base.Preconditions.checkState;
+
+/**
+ * Index condition.
+ *
+ * <p>Works in the following places:
+ *
+ * <ul>
+ * <li>In {@link InnodbFilterTranslator}, it is the index condition
+ * to push down according to {@link InnodbFilter} by planner rule.
+ *
+ * <li>In {@link InnodbTableScan}, it represents a full scan by a
+ * primary key or a secondary key.
+ *
+ * <li>In code generation, it indicates the storage engine which index
+ * to use and the associated condition if present.
+ * </ul>
+ */
+public class IndexCondition {
+
+ static final IndexCondition EMPTY_CONDITION =
+ create(null, null, null, ComparisonOperator.NOP, ComparisonOperator.NOP,
+ ImmutableList.of(), ImmutableList.of());
+
+ /** Field names per row type. */
+ private final List<String> fieldNames;
+ private final String indexName;
+ private final List<String> indexColumnNames;
+ private final RelCollation implicitCollation;
+ private final List<RexNode> pushDownConditions;
+ private final List<RexNode> remainderConditions;
+
+ private final QueryType queryType;
+ private final List<Object> pointQueryKey;
+ private final ComparisonOperator rangeQueryLowerOp;
+ private final ComparisonOperator rangeQueryUpperOp;
+ private final List<Object> rangeQueryLowerKey;
+ private final List<Object> rangeQueryUpperKey;
+
+ /** Constructor that assigns all fields. All other constructors call this. */
+ private IndexCondition(
+ List<String> fieldNames,
+ String indexName,
+ List<String> indexColumnNames,
+ RelCollation implicitCollation,
+ List<RexNode> pushDownConditions,
+ List<RexNode> remainderConditions,
+ QueryType queryType,
+ List<Object> pointQueryKey,
+ ComparisonOperator rangeQueryLowerOp,
+ ComparisonOperator rangeQueryUpperOp,
+ List<Object> rangeQueryLowerKey,
+ List<Object> rangeQueryUpperKey) {
+ this.fieldNames = fieldNames;
+ this.indexName = indexName;
+ this.indexColumnNames = indexColumnNames;
+ this.implicitCollation =
+ implicitCollation != null ? implicitCollation
+ : deduceImplicitCollation(fieldNames, indexColumnNames);
+ this.pushDownConditions =
+ pushDownConditions == null ? ImmutableList.of()
+ : ImmutableList.copyOf(pushDownConditions);
+ this.remainderConditions =
+ remainderConditions == null ? ImmutableList.of()
+ : ImmutableList.copyOf(remainderConditions);
+ this.queryType = queryType;
+ this.pointQueryKey = pointQueryKey;
+ this.rangeQueryLowerOp = Objects.requireNonNull(rangeQueryLowerOp);
+ this.rangeQueryUpperOp = Objects.requireNonNull(rangeQueryUpperOp);
+ this.rangeQueryLowerKey = ImmutableList.copyOf(rangeQueryLowerKey);
+ this.rangeQueryUpperKey = ImmutableList.copyOf(rangeQueryUpperKey);
+ }
+
+ static IndexCondition create(
+ List<String> fieldNames,
+ String indexName,
+ List<String> indexColumnNames,
+ QueryType queryType) {
+ return new IndexCondition(fieldNames, indexName, indexColumnNames, null,
+ null, null, queryType, null, ComparisonOperator.NOP,
+ ComparisonOperator.NOP, ImmutableList.of(), ImmutableList.of());
+ }
+
+ /**
+ * Creates a new instance for {@link InnodbFilterTranslator} to build
+ * index condition which can be pushed down.
+ */
+ static IndexCondition create(
+ List<String> fieldNames,
+ String indexName,
+ List<String> indexColumnNames,
+ List<RexNode> pushDownConditions,
+ List<RexNode> remainderConditions) {
+ return new IndexCondition(fieldNames, indexName, indexColumnNames, null,
+ pushDownConditions, remainderConditions, null, null,
+ ComparisonOperator.NOP, ComparisonOperator.NOP, ImmutableList.of(),
+ ImmutableList.of());
+ }
+
+ /**
+ * Creates a new instance for code generation to build query parameters
+ * for underlying storage engine <code>Innodb-java-reader</code>.
+ */
+ public static IndexCondition create(
+ String indexName,
+ QueryType queryType,
+ List<Object> pointQueryKey,
+ ComparisonOperator rangeQueryLowerOp,
+ ComparisonOperator rangeQueryUpperOp,
+ List<Object> rangeQueryLowerKey,
+ List<Object> rangeQueryUpperKey) {
+ return new IndexCondition(ImmutableList.of(), indexName, ImmutableList.of(),
+ null, null, null, queryType, pointQueryKey, rangeQueryLowerOp,
+ rangeQueryUpperOp, rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ /** Returns whether there are any push down conditions. */
+ boolean canPushDown() {
+ return !pushDownConditions.isEmpty();
+ }
+
+ public RelCollation getImplicitCollation() {
+ return implicitCollation;
+ }
+
+ /**
+ * Infers the implicit correlation from the index.
+ *
+ * @param indexColumnNames index column names
+ * @return the collation of the filtered results
+ */
+ private static RelCollation deduceImplicitCollation(List<String> fieldNames,
+ List<String> indexColumnNames) {
+ checkState(fieldNames != null, "field names cannot be null");
+ List<RelFieldCollation> keyCollations = new ArrayList<>(indexColumnNames.size());
+ for (String keyColumnName : indexColumnNames) {
+ int fieldIndex = fieldNames.indexOf(keyColumnName);
+ keyCollations.add(
+ new RelFieldCollation(fieldIndex, RelFieldCollation.Direction.ASCENDING));
+ }
+ return RelCollations.of(keyCollations);
+ }
+
+ public IndexCondition withFieldNames(List<String> fieldNames) {
+ if (Objects.equals(fieldNames, this.fieldNames)) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public String getIndexName() {
+ return indexName;
+ }
+
+ public IndexCondition withIndexName(String indexName) {
+ if (Objects.equals(indexName, this.indexName)) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public IndexCondition withIndexColumnNames(List<String> indexColumnNames) {
+ if (Objects.equals(indexColumnNames, this.indexColumnNames)) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public List<RexNode> getPushDownConditions() {
+ return pushDownConditions;
+ }
+
+ public IndexCondition withPushDownConditions(List<RexNode> pushDownConditions) {
+ if (Objects.equals(pushDownConditions, this.pushDownConditions)) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public List<RexNode> getRemainderConditions() {
+ return remainderConditions;
+ }
+
+ public IndexCondition withRemainderConditions(List<RexNode> remainderConditions) {
+ if (Objects.equals(remainderConditions, this.remainderConditions)) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public QueryType getQueryType() {
+ return queryType;
+ }
+
+ public IndexCondition withQueryType(QueryType queryType) {
+ if (queryType == this.queryType) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public List<Object> getPointQueryKey() {
+ return pointQueryKey;
+ }
+
+ public IndexCondition withPointQueryKey(List<Object> pointQueryKey) {
+ if (pointQueryKey == this.pointQueryKey) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public ComparisonOperator getRangeQueryLowerOp() {
+ return rangeQueryLowerOp;
+ }
+
+ public IndexCondition withRangeQueryLowerOp(ComparisonOperator rangeQueryLowerOp) {
+ if (rangeQueryLowerOp == this.rangeQueryLowerOp) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public ComparisonOperator getRangeQueryUpperOp() {
+ return rangeQueryUpperOp;
+ }
+
+ public IndexCondition withRangeQueryUpperOp(ComparisonOperator rangeQueryUpperOp) {
+ if (rangeQueryUpperOp == this.rangeQueryUpperOp) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public List<Object> getRangeQueryLowerKey() {
+ return rangeQueryLowerKey;
+ }
+
+ public IndexCondition withRangeQueryLowerKey(List<Object> rangeQueryLowerKey) {
+ if (rangeQueryLowerKey == this.rangeQueryLowerKey) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public List<Object> getRangeQueryUpperKey() {
+ return rangeQueryUpperKey;
+ }
+
+ public IndexCondition withRangeQueryUpperKey(List<Object> rangeQueryUpperKey) {
+ if (rangeQueryUpperKey == this.rangeQueryUpperKey) {
+ return this;
+ }
+ return new IndexCondition(fieldNames, indexName, indexColumnNames,
+ implicitCollation, pushDownConditions, remainderConditions,
+ queryType, pointQueryKey, rangeQueryLowerOp, rangeQueryUpperOp,
+ rangeQueryLowerKey, rangeQueryUpperKey);
+ }
+
+ public boolean nameMatch(String name) {
+ return name != null && name.equalsIgnoreCase(indexName);
+ }
+
+ @Override public String toString() {
+ final StringBuilder builder = new StringBuilder("(");
+ builder.append(queryType).append(", index=").append(indexName);
+ if (queryType == QueryType.PK_POINT_QUERY
+ || queryType == QueryType.SK_POINT_QUERY) {
+ checkState(pointQueryKey.size() == indexColumnNames.size());
+ append(builder, indexColumnNames, pointQueryKey, "=");
+ } else {
+ if (CollectionUtils.isNotEmpty(rangeQueryLowerKey)) {
+ append(builder, indexColumnNames, rangeQueryLowerKey, rangeQueryLowerOp.value());
+ }
+ if (CollectionUtils.isNotEmpty(rangeQueryUpperKey)) {
+ append(builder, indexColumnNames, rangeQueryUpperKey, rangeQueryUpperOp.value());
+ }
+ }
+ builder.append(")");
+ return builder.toString();
+ }
+
+ private void append(StringBuilder builder, List<String> keyColumnNames,
+ List<Object> key, String op) {
+ builder.append(", ");
+ for (Pair<String, Object> value : Pair.zip(keyColumnNames, key)) {
+ builder.append(value.getKey());
+ builder.append(op);
+ builder.append(value.getValue());
+ builder.append(",");
+ }
+ builder.deleteCharAt(builder.length() - 1);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbEnumerator.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbEnumerator.java
new file mode 100644
index 0000000..1f4e59e
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbEnumerator.java
@@ -0,0 +1,149 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.avatica.util.ByteString;
+import org.apache.calcite.avatica.util.DateTimeUtils;
+import org.apache.calcite.linq4j.Enumerator;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+import com.alibaba.innodb.java.reader.page.index.GenericRecord;
+import com.alibaba.innodb.java.reader.util.Utils;
+
+import java.sql.Date;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.time.LocalDate;
+import java.util.Iterator;
+import java.util.List;
+import java.util.TimeZone;
+
+/**
+ * Enumerator that reads from InnoDB data file.
+ */
+class InnodbEnumerator implements Enumerator<Object> {
+ private final Iterator<GenericRecord> iterator;
+ private GenericRecord current;
+ private final List<RelDataTypeField> fieldTypes;
+
+ /**
+ * Creates an InnodbEnumerator.
+ *
+ * @param resultIterator result iterator
+ * @param rowType the type of resulting rows
+ */
+ InnodbEnumerator(Iterator<GenericRecord> resultIterator, RelDataType rowType) {
+ this.iterator = resultIterator;
+ this.current = null;
+ this.fieldTypes = rowType.getFieldList();
+ }
+
+ /**
+ * Produces the next row from the results.
+ *
+ * @return a new row from the results
+ */
+ @Override public Object current() {
+ if (fieldTypes.size() == 1) {
+ // If we just have one field, produce it directly
+ return currentRowField(fieldTypes.get(0));
+ } else {
+ // Build an array with all fields in this row
+ Object[] row = new Object[fieldTypes.size()];
+ for (int i = 0; i < fieldTypes.size(); i++) {
+ row[i] = currentRowField(fieldTypes.get(i));
+ }
+ return row;
+ }
+ }
+
+ /**
+ * Get a field for the current row from the underlying object.
+ */
+ private Object currentRowField(RelDataTypeField relDataTypeField) {
+ final Object o = current.get(relDataTypeField.getName());
+ return convertToEnumeratorObject(o, relDataTypeField.getType());
+ }
+
+ /**
+ * Convert an object into the expected internal representation.
+ *
+ * @param obj object to convert, if needed
+ * @param relDataType data type
+ */
+ private Object convertToEnumeratorObject(Object obj, RelDataType relDataType) {
+ if (obj == null) {
+ return null;
+ }
+ SqlTypeName sqlTypeName = relDataType.getSqlTypeName();
+ switch (sqlTypeName) {
+ case BINARY:
+ case VARBINARY:
+ return new ByteString((byte[]) obj);
+ case TIMESTAMP:
+ Timestamp timestamp = Utils.convertDateTime((String) obj,
+ relDataType.getPrecision());
+ return shift(timestamp).getTime();
+ case TIME:
+ Time time = Utils.convertTime((String) obj,
+ relDataType.getPrecision());
+ return shift(time).getTime();
+ case DATE:
+ Date date = Date.valueOf(LocalDate.parse((String) obj));
+ return DateTimeUtils.dateStringToUnixDate(date.toString());
+ default:
+ return obj;
+ }
+ }
+
+ @Override public boolean moveNext() {
+ if (iterator.hasNext()) {
+ current = iterator.next();
+ return true;
+ } else {
+ return false;
+ }
+ }
+
+ @Override public void reset() {
+ throw new UnsupportedOperationException();
+ }
+
+ @Override public void close() {
+ // Nothing to do here
+ }
+
+ private static Timestamp shift(Timestamp v) {
+ if (v == null) {
+ return null;
+ }
+ long time = v.getTime();
+ int offset = TimeZone.getDefault().getOffset(time);
+ return new Timestamp(time + offset);
+ }
+
+ private static Time shift(Time v) {
+ if (v == null) {
+ return null;
+ }
+ long time = v.getTime();
+ int offset = TimeZone.getDefault().getOffset(time);
+ return new Time((time + offset) % DateTimeUtils.MILLIS_PER_DAY);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilter.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilter.java
new file mode 100644
index 0000000..85d997d
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilter.java
@@ -0,0 +1,97 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.RelWriter;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rex.RexNode;
+
+import com.alibaba.innodb.java.reader.schema.TableDef;
+
+import java.util.Objects;
+import javax.annotation.Nullable;
+
+/**
+ * Implementation of a {@link org.apache.calcite.rel.core.Filter}
+ * relational expression for an InnoDB data source.
+ */
+public class InnodbFilter extends Filter implements InnodbRel {
+ private final TableDef tableDef;
+ public final IndexCondition indexCondition;
+ private final @Nullable String forceIndexName;
+
+ /** Creates an InnodbFilter; but use {@link #create} if possible. */
+ private InnodbFilter(RelOptCluster cluster, RelTraitSet traitSet,
+ RelNode input, RexNode condition, IndexCondition indexCondition,
+ TableDef tableDef, @Nullable String forceIndexName) {
+ super(cluster, traitSet, input, condition);
+
+ this.tableDef = Objects.requireNonNull(tableDef);
+ this.indexCondition = Objects.requireNonNull(indexCondition);
+ this.forceIndexName = forceIndexName;
+
+ assert getConvention() == InnodbRel.CONVENTION;
+ assert getConvention() == input.getConvention();
+ }
+
+ /** Creates an InnodbFilter. */
+ public static InnodbFilter create(RelOptCluster cluster, RelTraitSet traitSet,
+ RelNode input, RexNode condition, IndexCondition indexCondition,
+ TableDef tableDef, @Nullable String forceIndexName) {
+ return new InnodbFilter(cluster, traitSet, input, condition, indexCondition,
+ tableDef, forceIndexName);
+ }
+
+ @Override public RelOptCost computeSelfCost(RelOptPlanner planner,
+ RelMetadataQuery mq) {
+ return super.computeSelfCost(planner, mq).multiplyBy(0.1);
+ }
+
+ @Override public InnodbFilter copy(RelTraitSet traitSet, RelNode input,
+ RexNode condition) {
+ return new InnodbFilter(getCluster(), traitSet, input, condition,
+ indexCondition, tableDef, forceIndexName);
+ }
+
+ @Override public void implement(Implementor implementor) {
+ implementor.visitChild(0, getInput());
+ implementor.setIndexCondition(indexCondition);
+ }
+
+ @Override public RelWriter explainTerms(RelWriter pw) {
+ pw.input("input", getInput());
+ pw.itemIf("condition", indexCondition, indexCondition.canPushDown());
+ return pw;
+ }
+
+ /**
+ * Returns the resulting collation by the primary or secondary
+ * indexes after filtering.
+ *
+ * @return the implicit collation based on the natural sorting by specific index
+ */
+ public RelCollation getImplicitCollation() {
+ return indexCondition.getImplicitCollation();
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilterTranslator.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilterTranslator.java
new file mode 100644
index 0000000..c79072b
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbFilterTranslator.java
@@ -0,0 +1,502 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexLiteral;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.util.DateString;
+import org.apache.calcite.util.TimeString;
+import org.apache.calcite.util.TimestampString;
+
+import org.apache.commons.collections.CollectionUtils;
+
+import com.alibaba.innodb.java.reader.comparator.ComparisonOperator;
+import com.alibaba.innodb.java.reader.schema.KeyMeta;
+import com.alibaba.innodb.java.reader.schema.TableDef;
+import com.google.common.collect.HashMultimap;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Multimap;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Optional;
+import java.util.stream.Collectors;
+import java.util.stream.Stream;
+import javax.annotation.Nullable;
+
+/**
+ * Translates {@link RexNode} expressions into {@link IndexCondition}
+ * which might be pushed down to an InnoDB data source.
+ */
+class InnodbFilterTranslator {
+ private final RexBuilder rexBuilder;
+ /** Field names per row type. */
+ private final List<String> fieldNames;
+ /** Primary key metadata. */
+ private final KeyMeta pkMeta;
+ /** Secondary key metadata. */
+ private final List<KeyMeta> skMetaList;
+ /** If not null, force to use one specific index from hint. */
+ private final @Nullable String forceIndexName;
+
+ InnodbFilterTranslator(RexBuilder rexBuilder, RelDataType rowType,
+ TableDef tableDef, @Nullable String forceIndexName) {
+ this.rexBuilder = rexBuilder;
+ this.fieldNames = InnodbRules.innodbFieldNames(rowType);
+ this.pkMeta = tableDef.getPrimaryKeyMeta();
+ this.skMetaList = tableDef.getSecondaryKeyMetaList();
+ this.forceIndexName = forceIndexName;
+ }
+
+ /**
+ * Produces the push down condition for the given
+ * relational expression condition.
+ *
+ * @param condition condition to translate
+ * @return push down condition
+ */
+ public IndexCondition translateMatch(RexNode condition) {
+ // does not support disjunctions
+ List<RexNode> disjunctions = RelOptUtil.disjunctions(condition);
+ if (disjunctions.size() == 1) {
+ return translateAnd(disjunctions.get(0));
+ } else {
+ throw new AssertionError("cannot translate " + condition);
+ }
+ }
+
+ /**
+ * Translates a conjunctive predicate to a push down condition.
+ *
+ * @param condition a conjunctive predicate
+ * @return push down condition
+ */
+ private IndexCondition translateAnd(RexNode condition) {
+ // expand calls to SEARCH(..., Sarg()) to >, =, etc.
+ final RexNode condition2 =
+ RexUtil.expandSearch(rexBuilder, null, condition);
+ // decompose condition by AND, flatten row expression
+ List<RexNode> rexNodeList = RelOptUtil.conjunctions(condition2);
+
+ List<IndexCondition> indexConditions = new ArrayList<>();
+
+ // try to push down filter by primary key
+ if (pkMeta != null) {
+ IndexCondition pkPushDownCond = findPushDownCondition(rexNodeList, pkMeta);
+ indexConditions.add(pkPushDownCond);
+ }
+
+ // try to push down filter by secondary keys
+ if (CollectionUtils.isNotEmpty(skMetaList)) {
+ for (KeyMeta skMeta : skMetaList) {
+ indexConditions.add(findPushDownCondition(rexNodeList, skMeta));
+ }
+ }
+
+ // a collection of all possible push down conditions, see if it can
+ // be pushed down, filter by forcing index name, then sort by comparator
+ Stream<IndexCondition> pushDownConditions = indexConditions.stream()
+ .filter(IndexCondition::canPushDown)
+ .filter(this::nonForceIndexOrMatchForceIndexName)
+ .sorted(new IndexConditionComparator());
+
+ return pushDownConditions.findFirst().orElse(IndexCondition.EMPTY_CONDITION);
+ }
+
+ /**
+ * Tries to translate a conjunctive predicate to push down condition.
+ *
+ * @param rexNodeList original field expressions
+ * @param keyMeta index metadata
+ * @return push down condition
+ */
+ private IndexCondition findPushDownCondition(List<RexNode> rexNodeList, KeyMeta keyMeta) {
+ // find field expressions matching index columns and specific operators
+ List<InternalRexNode> matchedRexNodeList = analyzePrefixMatches(rexNodeList, keyMeta);
+
+ // none of the conditions can be pushed down
+ if (CollectionUtils.isEmpty(matchedRexNodeList)) {
+ return IndexCondition.EMPTY_CONDITION;
+ }
+
+ // a collection that maps ordinal in index column list
+ // to multiple field expressions
+ Multimap<Integer, InternalRexNode> keyOrdToNodesMap = HashMultimap.create();
+ for (InternalRexNode node : matchedRexNodeList) {
+ keyOrdToNodesMap.put(node.ordinalInKey, node);
+ }
+
+ // left-prefix index rule not match
+ Collection<InternalRexNode> leftMostKeyNodes = keyOrdToNodesMap.get(0);
+ if (CollectionUtils.isEmpty(leftMostKeyNodes)) {
+ return IndexCondition.EMPTY_CONDITION;
+ }
+
+ // create result which might have conditions to push down
+ List<String> indexColumnNames = keyMeta.getKeyColumnNames();
+ List<RexNode> pushDownRexNodeList = new ArrayList<>();
+ List<RexNode> remainderRexNodeList = new ArrayList<>(rexNodeList);
+ IndexCondition condition =
+ IndexCondition.create(fieldNames, keyMeta.getName(), indexColumnNames,
+ pushDownRexNodeList, remainderRexNodeList);
+
+ // handle point query if possible
+ condition = handlePointQuery(condition, keyMeta, leftMostKeyNodes,
+ keyOrdToNodesMap, pushDownRexNodeList, remainderRexNodeList);
+ if (condition.canPushDown()) {
+ return condition;
+ }
+
+ // handle range query
+ condition = handleRangeQuery(condition, keyMeta, leftMostKeyNodes,
+ pushDownRexNodeList, remainderRexNodeList, ">=", ">");
+ condition = handleRangeQuery(condition, keyMeta, leftMostKeyNodes,
+ pushDownRexNodeList, remainderRexNodeList, "<=", "<");
+
+ return condition;
+ }
+
+ /**
+ * Analyzes from the first to the subsequent field expression following the
+ * left-prefix rule, this will based on a specific index
+ * (<code>KeyMeta</code>), check the column and its corresponding operation,
+ * see if it can be translated into a push down condition.
+ *
+ * <p>The result is a collection of matched field expressions.
+ *
+ * @param rexNodeList Field expressions
+ * @param keyMeta Index metadata
+ * @return a collection of matched field expressions
+ */
+ private List<InternalRexNode> analyzePrefixMatches(List<RexNode> rexNodeList, KeyMeta keyMeta) {
+ return rexNodeList.stream()
+ .map(rexNode -> translateMatch2(rexNode, keyMeta))
+ .filter(Optional::isPresent)
+ .map(Optional::get)
+ .collect(Collectors.toList());
+ }
+
+ /**
+ * Handles point query push down. The operation of the leftmost nodes
+ * should be "=", then we try to find as many "=" operations as
+ * possible, if "=" operation found on all index columns, then it is a
+ * point query on key (both primary key or composite key), else it will
+ * transform to a range query.
+ *
+ * <p>If conditions can be pushed down, for range query, we only remove
+ * first node from field expression list (<code>rexNodeList</code>),
+ * because Innodb-java-reader only support range query, not fully
+ * index condition pushdown; for point query, we can remove them all.
+ */
+ private IndexCondition handlePointQuery(IndexCondition condition,
+ KeyMeta keyMeta, Collection<InternalRexNode> leftMostKeyNodes,
+ Multimap<Integer, InternalRexNode> keyOrdToNodesMap,
+ List<RexNode> pushDownRexNodeList,
+ List<RexNode> remainderRexNodeList) {
+ Optional<InternalRexNode> leftMostEqOpNode = findFirstOp(leftMostKeyNodes, "=");
+ if (leftMostEqOpNode.isPresent()) {
+ InternalRexNode node = leftMostEqOpNode.get();
+
+ List<InternalRexNode> matchNodes = Lists.newArrayList(node);
+ findSubsequentMatches(matchNodes, keyMeta.getNumOfColumns(), keyOrdToNodesMap, "=");
+ List<Object> key = createKey(matchNodes);
+ pushDownRexNodeList.add(node.node);
+ remainderRexNodeList.remove(node.node);
+
+ if (matchNodes.size() != keyMeta.getNumOfColumns()) {
+ // "=" operation does not apply on all index columns
+ return condition
+ .withQueryType(QueryType.getRangeQuery(keyMeta.isSecondaryKey()))
+ .withRangeQueryLowerOp(ComparisonOperator.GTE)
+ .withRangeQueryLowerKey(key)
+ .withRangeQueryUpperOp(ComparisonOperator.LTE)
+ .withRangeQueryUpperKey(key)
+ .withPushDownConditions(pushDownRexNodeList)
+ .withRemainderConditions(remainderRexNodeList);
+ } else {
+ for (InternalRexNode n : matchNodes) {
+ pushDownRexNodeList.add(n.node);
+ remainderRexNodeList.remove(n.node);
+ }
+ return condition
+ .withQueryType(QueryType.getPointQuery(keyMeta.isSecondaryKey()))
+ .withPointQueryKey(key)
+ .withPushDownConditions(pushDownRexNodeList)
+ .withRemainderConditions(remainderRexNodeList);
+ }
+ }
+ return condition;
+ }
+
+ /**
+ * Handles range query push down. We try to find operation of GTE, GT, LT
+ * or LTE in the left most key.
+ *
+ * <p>We only push down partial condition since Innodb-java-reader only
+ * supports range query with lower and upper bound, not fully index condition
+ * push down.
+ *
+ * <p>For example, given the following 7 rows with (a,b) as secondary key.
+ * <pre>
+ * a=100,b=200
+ * a=100,b=300
+ * a=100,b=500
+ * a=200,b=100
+ * a=200,b=400
+ * a=300,b=300
+ * a=500,b=600
+ * </pre>
+ *
+ * <p>If condition is <code>a>200 AND b>300</code>,
+ * the lower bound should be
+ * <code>a=300,b=300</code>, we can only push down one condition
+ * <code>a>200</code> as lower bound condition, we cannot push
+ * <code>a>200 AND b>300</code> because it will include
+ * <code>a=200,b=400</code> as well which is incorrect.
+ *
+ * <p>If conditions can be pushed down, we will first node from field
+ * expression list (<code>rexNodeList</code>).
+ */
+ private IndexCondition handleRangeQuery(IndexCondition condition,
+ KeyMeta keyMeta, Collection<InternalRexNode> leftMostKeyNodes,
+ List<RexNode> pushDownRexNodeList,
+ List<RexNode> remainderRexNodeList,
+ String... opList) {
+ Optional<InternalRexNode> node = findFirstOp(leftMostKeyNodes, opList);
+ if (node.isPresent()) {
+ pushDownRexNodeList.add(node.get().node);
+ remainderRexNodeList.remove(node.get().node);
+ List<Object> key = createKey(Lists.newArrayList(node.get()));
+ ComparisonOperator op = ComparisonOperator.parse(node.get().op);
+ if (ComparisonOperator.isLowerBoundOp(opList)) {
+ return condition
+ .withQueryType(QueryType.getRangeQuery(keyMeta.isSecondaryKey()))
+ .withRangeQueryLowerOp(op)
+ .withRangeQueryLowerKey(key)
+ .withPushDownConditions(pushDownRexNodeList)
+ .withRemainderConditions(remainderRexNodeList);
+ } else if (ComparisonOperator.isUpperBoundOp(opList)) {
+ return condition
+ .withQueryType(QueryType.getRangeQuery(keyMeta.isSecondaryKey()))
+ .withRangeQueryUpperOp(op)
+ .withRangeQueryUpperKey(key)
+ .withPushDownConditions(pushDownRexNodeList)
+ .withRemainderConditions(remainderRexNodeList);
+ } else {
+ throw new AssertionError("comparison operation is invalid " + op);
+ }
+ }
+ return condition;
+ }
+
+ /**
+ * Translates a binary relation.
+ */
+ private Optional<InternalRexNode> translateMatch2(RexNode node, KeyMeta keyMeta) {
+ switch (node.getKind()) {
+ case EQUALS:
+ return translateBinary("=", "=", (RexCall) node, keyMeta);
+ case LESS_THAN:
+ return translateBinary("<", ">", (RexCall) node, keyMeta);
+ case LESS_THAN_OR_EQUAL:
+ return translateBinary("<=", ">=", (RexCall) node, keyMeta);
+ case GREATER_THAN:
+ return translateBinary(">", "<", (RexCall) node, keyMeta);
+ case GREATER_THAN_OR_EQUAL:
+ return translateBinary(">=", "<=", (RexCall) node, keyMeta);
+ default:
+ return Optional.empty();
+ }
+ }
+
+ /**
+ * Translates a call to a binary operator, reversing arguments if
+ * necessary.
+ */
+ private Optional<InternalRexNode> translateBinary(String op, String rop,
+ RexCall call, KeyMeta keyMeta) {
+ final RexNode left = call.operands.get(0);
+ final RexNode right = call.operands.get(1);
+ Optional<InternalRexNode> expression =
+ translateBinary2(op, left, right, call, keyMeta);
+ if (expression.isPresent()) {
+ return expression;
+ }
+ expression = translateBinary2(rop, right, left, call, keyMeta);
+ return expression;
+ }
+
+ /**
+ * Translates a call to a binary operator. Returns null on failure.
+ */
+ private Optional<InternalRexNode> translateBinary2(String op, RexNode left,
+ RexNode right, RexNode originNode, KeyMeta keyMeta) {
+ RexLiteral rightLiteral;
+ if (right.isA(SqlKind.LITERAL)) {
+ rightLiteral = (RexLiteral) right;
+ } else {
+ // because MySQL's TIMESTAMP is mapped to TIMESTAMP_WITH_TIME_ZONE sql type,
+ // we should cast the value to literal.
+ if (right.isA(SqlKind.CAST)
+ && isSqlTypeMatch((RexCall) right, SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)) {
+ rightLiteral = (RexLiteral) ((RexCall) right).operands.get(0);
+ } else {
+ return Optional.empty();
+ }
+ }
+ switch (left.getKind()) {
+ case INPUT_REF:
+ final RexInputRef left1 = (RexInputRef) left;
+ String name = fieldNames.get(left1.getIndex());
+ // filter out field does not show in index column
+ if (!keyMeta.getKeyColumnNames().contains(name)) {
+ return Optional.empty();
+ }
+ return translateOp2(op, name, rightLiteral, originNode, keyMeta);
+ case CAST:
+ return translateBinary2(op, ((RexCall) left).operands.get(0), right,
+ originNode, keyMeta);
+ default:
+ return Optional.empty();
+ }
+ }
+
+ /**
+ * Combines a field name, operator, and literal to produce a predicate string.
+ */
+ private Optional<InternalRexNode> translateOp2(String op, String name,
+ RexLiteral right, RexNode originNode, KeyMeta keyMeta) {
+ String value = literalValue(right);
+ InternalRexNode node = new InternalRexNode();
+ node.node = originNode;
+ node.ordinalInKey = keyMeta.getKeyColumnNames().indexOf(name);
+ // For variable length column, Innodb-java-reader have a limitation,
+ // left-prefix index length should be less than search value literal.
+ // For example, we cannot leverage index of EMAIL(3) upon search value
+ // `someone@apache.org`, because the value length is longer than 3.
+ if (keyMeta.getVarLen(name).isPresent()
+ && keyMeta.getVarLen(name).get() < value.length()) {
+ return Optional.empty();
+ }
+ node.fieldName = name;
+ node.op = op;
+ node.right = value;
+ return Optional.of(node);
+ }
+
+ /**
+ * Converts the value of a literal to a string.
+ *
+ * @param literal Literal to translate
+ * @return String representation of the literal
+ */
+ private static String literalValue(RexLiteral literal) {
+ switch (literal.getTypeName()) {
+ case DATE:
+ return String.valueOf(literal.getValueAs(DateString.class));
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return String.valueOf(literal.getValueAs(TimestampString.class));
+ case TIME:
+ case TIME_WITH_LOCAL_TIME_ZONE:
+ return String.valueOf(literal.getValueAs(TimeString.class));
+ case DECIMAL:
+ return String.valueOf(literal.getValue());
+ default:
+ return String.valueOf(literal.getValue2());
+ }
+ }
+
+ private void findSubsequentMatches(List<InternalRexNode> nodes, int numOfKeyColumns,
+ Multimap<Integer, InternalRexNode> keyOrdToNodesMap, String op) {
+ for (int i = nodes.size(); i < numOfKeyColumns; i++) {
+ Optional<InternalRexNode> eqOpNode = findFirstOp(keyOrdToNodesMap.get(i), op);
+ if (eqOpNode.isPresent()) {
+ nodes.add(eqOpNode.get());
+ } else {
+ break;
+ }
+ }
+ }
+
+ private List<Object> createKey(List<InternalRexNode> nodes) {
+ return nodes.stream().map(n -> n.right).collect(Collectors.toList());
+ }
+
+ /**
+ * Finds first node from field expression nodes which match specific
+ * operations.
+ *
+ * <p>If not found, result is {@link Optional#empty()}.
+ */
+ private Optional<InternalRexNode> findFirstOp(Collection<InternalRexNode> nodes,
+ String... opList) {
+ if (CollectionUtils.isEmpty(nodes)) {
+ return Optional.empty();
+ }
+ for (InternalRexNode node : nodes) {
+ for (String op : opList) {
+ if (op.equals(node.op)) {
+ return Optional.of(node);
+ }
+ }
+ }
+ return Optional.empty();
+ }
+
+ private boolean nonForceIndexOrMatchForceIndexName(IndexCondition indexCondition) {
+ return Optional.ofNullable(forceIndexName)
+ .map(indexCondition::nameMatch).orElse(true);
+ }
+
+ /** Internal representation of a row expression. */
+ private static class InternalRexNode {
+ /** Relation expression node. */
+ RexNode node;
+ /** Field ordinal in indexes. */
+ int ordinalInKey;
+ /** Field name. */
+ String fieldName;
+ /** Binary operation like =, >=, <=, > or <.*/
+ String op;
+ /** Binary operation right literal value. */
+ Object right;
+ }
+
+ /** Index condition comparator. */
+ static class IndexConditionComparator implements Comparator<IndexCondition> {
+
+ @Override public int compare(IndexCondition o1, IndexCondition o2) {
+ return Integer.compare(o1.getQueryType().priority(), o2.getQueryType().priority());
+ }
+ }
+
+ private boolean isSqlTypeMatch(RexCall rexCall, SqlTypeName sqlTypeName) {
+ assert rexCall != null;
+ return rexCall.type.getSqlTypeName() == sqlTypeName;
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbMethod.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbMethod.java
new file mode 100644
index 0000000..25f0b8c
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbMethod.java
@@ -0,0 +1,51 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.innodb;
+
+import org.apache.calcite.linq4j.tree.Types;
+
+import com.google.common.collect.ImmutableMap;
+
+import java.lang.reflect.Method;
+import java.util.List;
+
+/**
+ * Builtin methods in InnoDB data source.
+ */
+public enum InnodbMethod {
+ /** Method signature to call for code generation. */
+ INNODB_QUERYABLE_QUERY(InnodbTable.InnodbQueryable.class, "query",
+ List.class, List.class, IndexCondition.class, Boolean.class);
+
+ @SuppressWarnings("ImmutableEnumChecker")
+ public final Method method;
+
+ public static final ImmutableMap<Method, InnodbMethod> MAP;
+
+ static {
+ final ImmutableMap.Builder<Method, InnodbMethod> builder =
+ ImmutableMap.builder();
+ for (InnodbMethod value : InnodbMethod.values()) {
+ builder.put(value.method, value);
+ }
+ MAP = builder.build();
+ }
+
+ InnodbMethod(Class clazz, String methodName, Class... argumentTypes) {
+ this.method = Types.lookupMethod(clazz, methodName, argumentTypes);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbProject.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbProject.java
new file mode 100644
index 0000000..b9430bc
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbProject.java
@@ -0,0 +1,71 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.util.Pair;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Implementation of {@link org.apache.calcite.rel.core.Project}
+ * relational expression for an InnoDB data source.
+ */
+public class InnodbProject extends Project implements InnodbRel {
+ InnodbProject(RelOptCluster cluster, RelTraitSet traitSet,
+ RelNode input, List<? extends RexNode> projects, RelDataType rowType) {
+ super(cluster, traitSet, ImmutableList.of(), input, projects, rowType);
+ assert getConvention() == InnodbRel.CONVENTION;
+ assert getConvention() == input.getConvention();
+ }
+
+ @Override public Project copy(RelTraitSet traitSet, RelNode input,
+ List<RexNode> projects, RelDataType rowType) {
+ return new InnodbProject(getCluster(), traitSet, input, projects, rowType);
+ }
+
+ @Override public RelOptCost computeSelfCost(RelOptPlanner planner,
+ RelMetadataQuery mq) {
+ return super.computeSelfCost(planner, mq).multiplyBy(0.1);
+ }
+
+ @Override public void implement(Implementor implementor) {
+ implementor.visitChild(0, getInput());
+ final InnodbRules.RexToInnodbTranslator translator =
+ new InnodbRules.RexToInnodbTranslator(
+ InnodbRules.innodbFieldNames(getInput().getRowType()));
+ final Map<String, String> fields = new LinkedHashMap<>();
+ for (Pair<RexNode, String> pair : getNamedProjects()) {
+ final String name = pair.right;
+ final String originalName = pair.left.accept(translator);
+ fields.put(originalName, name);
+ }
+ implementor.addSelectFields(fields);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRel.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRel.java
new file mode 100644
index 0000000..4cf1ca2
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRel.java
@@ -0,0 +1,64 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.Convention;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.rel.RelNode;
+
+import java.util.LinkedHashMap;
+import java.util.Map;
+
+/**
+ * Relational expression that uses InnoDB calling convention.
+ */
+public interface InnodbRel extends RelNode {
+ void implement(Implementor implementor);
+
+ /** Calling convention for relational operations that occur in InnoDB. */
+ Convention CONVENTION = new Convention.Impl("INNODB", InnodbRel.class);
+
+ /** Callback for the implementation process that converts a tree of
+ * {@link InnodbRel} nodes into an InnoDB direct call query. */
+ class Implementor {
+ final Map<String, String> selectFields = new LinkedHashMap<>();
+ IndexCondition indexCondition = IndexCondition.EMPTY_CONDITION;
+ boolean ascOrder = true;
+
+ RelOptTable table;
+ InnodbTable innodbTable;
+
+ public void addSelectFields(Map<String, String> fields) {
+ if (fields != null) {
+ selectFields.putAll(fields);
+ }
+ }
+
+ public void setIndexCondition(IndexCondition indexCondition) {
+ this.indexCondition = indexCondition;
+ }
+
+ public void setAscOrder(boolean ascOrder) {
+ this.ascOrder = ascOrder;
+ }
+
+ public void visitChild(int ordinal, RelNode input) {
+ assert ordinal == 0;
+ ((InnodbRel) input).implement(this);
+ }
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRules.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRules.java
new file mode 100644
index 0000000..a15ab24
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbRules.java
@@ -0,0 +1,371 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.adapter.enumerable.EnumerableConvention;
+import org.apache.calcite.plan.Convention;
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.convert.ConverterRule;
+import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.logical.LogicalFilter;
+import org.apache.calcite.rel.logical.LogicalProject;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.rex.RexVisitorImpl;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
+
+import com.alibaba.innodb.java.reader.schema.TableDef;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * Rules and relational operators for {@link InnodbRel#CONVENTION}
+ * calling convention.
+ */
+public class InnodbRules {
+ private InnodbRules() {
+ }
+
+ /** Rule to convert a relational expression from
+ * {@link InnodbRel#CONVENTION} to {@link EnumerableConvention}. */
+ public static final InnodbToEnumerableConverterRule TO_ENUMERABLE =
+ InnodbToEnumerableConverterRule.DEFAULT_CONFIG
+ .toRule(InnodbToEnumerableConverterRule.class);
+
+ /** Rule to convert a {@link org.apache.calcite.rel.logical.LogicalProject}
+ * to a {@link InnodbProject}. */
+ public static final InnodbProjectRule PROJECT =
+ InnodbProjectRule.DEFAULT_CONFIG.toRule(InnodbProjectRule.class);
+
+ /** Rule to convert a {@link org.apache.calcite.rel.logical.LogicalFilter} to
+ * a {@link InnodbFilter}. */
+ public static final InnodbFilterRule FILTER =
+ InnodbFilterRule.Config.DEFAULT.toRule();
+
+ /** Rule to convert a {@link org.apache.calcite.rel.core.Sort} with a
+ * {@link org.apache.calcite.rel.core.Filter} to a
+ * {@link InnodbSort}. */
+ public static final InnodbSortFilterRule SORT_FILTER =
+ InnodbSortFilterRule.Config.DEFAULT.toRule();
+
+ /** Rule to convert a {@link org.apache.calcite.rel.core.Sort} to a
+ * {@link InnodbSort} based on InnoDB table clustering index. */
+ public static final InnodbSortTableScanRule SORT_SCAN =
+ InnodbSortTableScanRule.Config.DEFAULT.toRule();
+
+ public static final List<RelOptRule> RULES =
+ ImmutableList.of(PROJECT,
+ FILTER,
+ SORT_FILTER,
+ SORT_SCAN);
+
+ static List<String> innodbFieldNames(final RelDataType rowType) {
+ return SqlValidatorUtil.uniquify(rowType.getFieldNames(),
+ SqlValidatorUtil.EXPR_SUGGESTER, true);
+ }
+
+ /** Translator from {@link RexNode} to strings in InnoDB's expression
+ * language. */
+ static class RexToInnodbTranslator extends RexVisitorImpl<String> {
+ private final List<String> inFields;
+
+ protected RexToInnodbTranslator(List<String> inFields) {
+ super(true);
+ this.inFields = inFields;
+ }
+
+ @Override public String visitInputRef(RexInputRef inputRef) {
+ return inFields.get(inputRef.getIndex());
+ }
+ }
+
+ /**
+ * Base class for planner rules that convert a relational expression to
+ * Innodb calling convention.
+ */
+ abstract static class InnodbConverterRule extends ConverterRule {
+ InnodbConverterRule(Config config) {
+ super(config);
+ }
+ }
+
+ /**
+ * Rule to convert a {@link org.apache.calcite.rel.logical.LogicalProject}
+ * to a {@link InnodbProject}.
+ *
+ * @see #PROJECT
+ */
+ public static class InnodbProjectRule extends InnodbConverterRule {
+ /** Default configuration. */
+ private static final Config DEFAULT_CONFIG = Config.INSTANCE
+ .withConversion(LogicalProject.class, Convention.NONE,
+ InnodbRel.CONVENTION, "InnodbProjectRule")
+ .withRuleFactory(InnodbProjectRule::new);
+
+ protected InnodbProjectRule(Config config) {
+ super(config);
+ }
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ LogicalProject project = call.rel(0);
+ for (RexNode e : project.getProjects()) {
+ if (!(e instanceof RexInputRef)) {
+ return false;
+ }
+ }
+
+ return true;
+ }
+
+ @Override public RelNode convert(RelNode rel) {
+ final LogicalProject project = (LogicalProject) rel;
+ final RelTraitSet traitSet = project.getTraitSet().replace(out);
+ return new InnodbProject(project.getCluster(), traitSet,
+ convert(project.getInput(), out), project.getProjects(),
+ project.getRowType());
+ }
+ }
+
+ /**
+ * Rule to convert a {@link org.apache.calcite.rel.logical.LogicalFilter} to a
+ * {@link InnodbFilter}.
+ *
+ * @see #FILTER
+ */
+ public static class InnodbFilterRule extends RelRule<InnodbFilterRule.Config> {
+ /** Creates a InnodbFilterRule. */
+ protected InnodbFilterRule(Config config) {
+ super(config);
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ LogicalFilter filter = call.rel(0);
+ InnodbTableScan scan = call.rel(1);
+ if (filter.getTraitSet().contains(Convention.NONE)) {
+ final RelNode converted = convert(filter, scan);
+ if (converted != null) {
+ call.transformTo(converted);
+ }
+ }
+ }
+
+ RelNode convert(LogicalFilter filter, InnodbTableScan scan) {
+ final RelTraitSet traitSet = filter.getTraitSet().replace(InnodbRel.CONVENTION);
+
+ final TableDef tableDef = scan.innodbTable.getTableDef();
+ final RelOptCluster cluster = filter.getCluster();
+ final InnodbFilterTranslator translator =
+ new InnodbFilterTranslator(cluster.getRexBuilder(),
+ filter.getRowType(), tableDef, scan.getForceIndexName());
+ final IndexCondition indexCondition =
+ translator.translateMatch(filter.getCondition());
+
+ InnodbFilter innodbFilter =
+ InnodbFilter.create(cluster, traitSet,
+ convert(filter.getInput(), InnodbRel.CONVENTION),
+ filter.getCondition(), indexCondition, tableDef,
+ scan.getForceIndexName());
+
+ // if some conditions can be pushed down, we left the remainder conditions
+ // in the original filter and create a subsidiary filter
+ if (innodbFilter.indexCondition.canPushDown()) {
+ return LogicalFilter.create(innodbFilter,
+ RexUtil.composeConjunction(cluster.getRexBuilder(),
+ indexCondition.getRemainderConditions()));
+ }
+ return filter;
+ }
+
+ /** Rule configuration. */
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = EMPTY
+ .withOperandSupplier(b0 ->
+ b0.operand(LogicalFilter.class)
+ .oneInput(b1 -> b1.operand(InnodbTableScan.class)
+ .noInputs()))
+ .as(Config.class);
+
+ @Override default InnodbFilterRule toRule() {
+ return new InnodbFilterRule(this);
+ }
+ }
+ }
+
+ /**
+ * Rule to convert a {@link org.apache.calcite.rel.core.Sort} to a
+ * {@link InnodbSort}.
+ *
+ * @param <C> The rule configuration type.
+ */
+ private static class AbstractInnodbSortRule<C extends RelRule.Config>
+ extends RelRule<C> {
+
+ AbstractInnodbSortRule(C config) {
+ super(config);
+ }
+
+ RelNode convert(Sort sort) {
+ final RelTraitSet traitSet =
+ sort.getTraitSet().replace(InnodbRel.CONVENTION)
+ .replace(sort.getCollation());
+ return new InnodbSort(sort.getCluster(), traitSet,
+ convert(sort.getInput(), traitSet.replace(RelCollations.EMPTY)),
+ sort.getCollation());
+ }
+
+ /**
+ * Check if it is possible to exploit sorting for a given collation.
+ *
+ * @return true if it is possible to achieve this sort in Innodb data source
+ */
+ protected boolean collationsCompatible(RelCollation sortCollation,
+ RelCollation implicitCollation) {
+ List<RelFieldCollation> sortFieldCollations = sortCollation.getFieldCollations();
+ List<RelFieldCollation> implicitFieldCollations = implicitCollation.getFieldCollations();
+
+ if (sortFieldCollations.size() > implicitFieldCollations.size()) {
+ return false;
+ }
+ if (sortFieldCollations.size() == 0) {
+ return true;
+ }
+
+ // check if we need to reverse the order of the implicit collation
+ boolean reversed = sortFieldCollations.get(0).getDirection().reverse().lax()
+ == implicitFieldCollations.get(0).getDirection();
+
+ for (int i = 0; i < sortFieldCollations.size(); i++) {
+ RelFieldCollation sorted = sortFieldCollations.get(i);
+ RelFieldCollation implied = implicitFieldCollations.get(i);
+
+ // check that the fields being sorted match
+ if (sorted.getFieldIndex() != implied.getFieldIndex()) {
+ return false;
+ }
+
+ // either all fields must be sorted in the same direction
+ // or the opposite direction based on whether we decided
+ // if the sort direction should be reversed above
+ RelFieldCollation.Direction sortDirection = sorted.getDirection();
+ RelFieldCollation.Direction implicitDirection = implied.getDirection();
+ if ((!reversed && sortDirection != implicitDirection)
+ || (reversed && sortDirection.reverse().lax() != implicitDirection)) {
+ return false;
+ }
+ }
+
+ return true;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Sort sort = call.rel(0);
+ final RelNode converted = convert(sort);
+ if (converted != null) {
+ call.transformTo(converted);
+ }
+ }
+ }
+
+ /**
+ * Rule to convert a {@link org.apache.calcite.rel.core.Sort} to a
+ * {@link InnodbSort}.
+ *
+ * @see #SORT_FILTER
+ */
+ public static class InnodbSortFilterRule
+ extends AbstractInnodbSortRule<InnodbSortFilterRule.Config> {
+ /** Creates a InnodbSortFilterRule. */
+ protected InnodbSortFilterRule(Config config) {
+ super(config);
+ }
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ final Sort sort = call.rel(0);
+ final InnodbFilter filter = call.rel(2);
+ return collationsCompatible(sort.getCollation(), filter.getImplicitCollation());
+ }
+
+ /** Rule configuration. */
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = EMPTY
+ .withOperandSupplier(b0 ->
+ b0.operand(Sort.class)
+ .predicate(sort -> true)
+ .oneInput(b1 ->
+ b1.operand(InnodbToEnumerableConverter.class)
+ .oneInput(b2 ->
+ b2.operand(InnodbFilter.class)
+ .predicate(innodbFilter -> true)
+ .anyInputs())))
+ .as(Config.class);
+
+ @Override default InnodbSortFilterRule toRule() {
+ return new InnodbSortFilterRule(this);
+ }
+ }
+ }
+
+ /**
+ * Rule to convert a {@link org.apache.calcite.rel.core.Sort} to a
+ * {@link InnodbSort} based on InnoDB table clustering index.
+ *
+ * @see #SORT_SCAN
+ */
+ public static class InnodbSortTableScanRule
+ extends AbstractInnodbSortRule<InnodbSortTableScanRule.Config> {
+ /** Creates a InnodbSortTableScanRule. */
+ protected InnodbSortTableScanRule(Config config) {
+ super(config);
+ }
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ final Sort sort = call.rel(0);
+ final InnodbTableScan tableScan = call.rel(2);
+ return collationsCompatible(sort.getCollation(), tableScan.getImplicitCollation());
+ }
+
+ /** Rule configuration. */
+ public interface Config extends RelRule.Config {
+ InnodbSortTableScanRule.Config DEFAULT = EMPTY
+ .withOperandSupplier(b0 ->
+ b0.operand(Sort.class)
+ .predicate(sort -> true)
+ .oneInput(b1 ->
+ b1.operand(InnodbToEnumerableConverter.class)
+ .oneInput(b2 ->
+ b2.operand(InnodbTableScan.class)
+ .predicate(tableScan -> true)
+ .anyInputs())))
+ .as(InnodbSortTableScanRule.Config.class);
+
+ @Override default InnodbSortTableScanRule toRule() {
+ return new InnodbSortTableScanRule(this);
+ }
+ }
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchema.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchema.java
new file mode 100644
index 0000000..0074949
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchema.java
@@ -0,0 +1,137 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeImpl;
+import org.apache.calcite.rel.type.RelDataTypeSystem;
+import org.apache.calcite.rel.type.RelProtoDataType;
+import org.apache.calcite.schema.Table;
+import org.apache.calcite.schema.impl.AbstractSchema;
+import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
+import org.apache.calcite.sql.type.SqlTypeName;
+
+import org.apache.commons.collections.CollectionUtils;
+import org.apache.commons.lang3.StringUtils;
+
+import com.alibaba.innodb.java.reader.TableReaderFactory;
+import com.alibaba.innodb.java.reader.column.ColumnType;
+import com.alibaba.innodb.java.reader.schema.Column;
+import com.alibaba.innodb.java.reader.schema.TableDef;
+import com.alibaba.innodb.java.reader.schema.provider.TableDefProvider;
+import com.alibaba.innodb.java.reader.schema.provider.impl.SqlFileTableDefProvider;
+import com.google.common.collect.ImmutableMap;
+
+import java.util.List;
+import java.util.Map;
+
+import static com.google.common.base.Preconditions.checkArgument;
+
+import static java.util.stream.Collectors.toList;
+
+/**
+ * Schema for an InnoDB data source.
+ */
+public class InnodbSchema extends AbstractSchema {
+ final List<String> sqlFilePathList;
+ final String ibdDataFileBasePath;
+ final TableReaderFactory tableReaderFactory;
+
+ static final ColumnTypeToSqlTypeConversionRules COLUMN_TYPE_TO_SQL_TYPE =
+ ColumnTypeToSqlTypeConversionRules.instance();
+
+ public InnodbSchema(List<String> sqlFilePathList,
+ String ibdDataFileBasePath) {
+ checkArgument(CollectionUtils.isNotEmpty(sqlFilePathList),
+ "SQL file path list cannot be empty");
+ checkArgument(StringUtils.isNotEmpty(ibdDataFileBasePath),
+ "InnoDB data file with ibd suffix cannot be empty");
+ this.sqlFilePathList = sqlFilePathList;
+ this.ibdDataFileBasePath = ibdDataFileBasePath;
+
+ List<TableDefProvider> tableDefProviderList = sqlFilePathList.stream()
+ .map(SqlFileTableDefProvider::new).collect(toList());
+ this.tableReaderFactory = TableReaderFactory.builder()
+ .withProviders(tableDefProviderList)
+ .withDataFileBasePath(ibdDataFileBasePath)
+ .build();
+ }
+
+ RelProtoDataType getRelDataType(String tableName) {
+ // Temporary type factory, just for the duration of this method. Allowable
+ // because we're creating a proto-type, not a type; before being used, the
+ // proto-type will be copied into a real type factory.
+ final RelDataTypeFactory typeFactory =
+ new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
+ final RelDataTypeFactory.Builder fieldInfo = typeFactory.builder();
+ if (!tableReaderFactory.existTableDef(tableName)) {
+ throw new RuntimeException("Table definition " + tableName
+ + " not found");
+ }
+ TableDef tableDef = tableReaderFactory.getTableDef(tableName);
+ for (Column column : tableDef.getColumnList()) {
+ final SqlTypeName sqlTypeName =
+ COLUMN_TYPE_TO_SQL_TYPE.lookup(column.getType());
+ final int precision;
+ final int scale;
+ switch (column.getType()) {
+ case ColumnType.TIMESTAMP:
+ case ColumnType.TIME:
+ case ColumnType.DATETIME:
+ precision = column.getPrecision();
+ scale = 0;
+ break;
+ default:
+ precision = column.getPrecision();
+ scale = column.getScale();
+ break;
+ }
+ if (sqlTypeName.allowsPrecScale(true, true)
+ && column.getPrecision() >= 0
+ && column.getScale() >= 0) {
+ fieldInfo.add(column.getName(), sqlTypeName, precision, scale);
+ } else if (sqlTypeName.allowsPrecNoScale() && precision >= 0) {
+ fieldInfo.add(column.getName(), sqlTypeName, precision);
+ } else {
+ assert sqlTypeName.allowsNoPrecNoScale();
+ fieldInfo.add(column.getName(), sqlTypeName);
+ }
+ fieldInfo.nullable(column.isNullable());
+ }
+ return RelDataTypeImpl.proto(fieldInfo.build());
+ }
+
+ /**
+ * Return table definition.
+ */
+ public TableDef getTableDef(String tableName) {
+ if (!tableReaderFactory.existTableDef(tableName)) {
+ throw new RuntimeException("cannot find table definition for " + tableName);
+ }
+ return tableReaderFactory.getTableDef(tableName);
+ }
+
+ @Override protected Map<String, Table> getTableMap() {
+ final ImmutableMap.Builder<String, Table> builder = ImmutableMap.builder();
+ Map<String, TableDef> map = tableReaderFactory.getTableNameToDefMap();
+ for (Map.Entry<String, TableDef> entry : map.entrySet()) {
+ String tableName = entry.getKey();
+ builder.put(tableName, new InnodbTable(this, tableName));
+ }
+ return builder.build();
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchemaFactory.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchemaFactory.java
new file mode 100644
index 0000000..7d9599b
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSchemaFactory.java
@@ -0,0 +1,46 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.innodb;
+
+import org.apache.calcite.schema.Schema;
+import org.apache.calcite.schema.SchemaFactory;
+import org.apache.calcite.schema.SchemaPlus;
+
+import org.apache.commons.lang3.StringUtils;
+
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Factory that creates a {@link InnodbSchema}.
+ */
+public class InnodbSchemaFactory implements SchemaFactory {
+ public InnodbSchemaFactory() {
+ }
+
+ @Override public Schema create(SchemaPlus parentSchema, String name,
+ Map<String, Object> operand) {
+ final List<String> sqlFilePathList = (List<String>) operand.get("sqlFilePath");
+ final String ibdDataFileBasePath = (String) operand.get("ibdDataFileBasePath");
+ final String timeZone = (String) operand.get("timeZone");
+ if (StringUtils.isNotEmpty(timeZone)) {
+ System.setProperty("innodb.java.reader.server.timezone", timeZone);
+ }
+
+ return new InnodbSchema(sqlFilePathList, ibdDataFileBasePath);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSort.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSort.java
new file mode 100644
index 0000000..1961cba
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbSort.java
@@ -0,0 +1,73 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rex.RexNode;
+
+import java.util.List;
+
+import static com.google.common.base.Preconditions.checkState;
+
+/**
+ * Implementation of {@link org.apache.calcite.rel.core.Sort}
+ * relational expression for an InnoDB data source.
+ */
+public class InnodbSort extends Sort implements InnodbRel {
+ InnodbSort(RelOptCluster cluster, RelTraitSet traitSet,
+ RelNode input, RelCollation collation) {
+ super(cluster, traitSet, input, collation, null, null);
+
+ assert getConvention() == InnodbRel.CONVENTION;
+ assert getConvention() == input.getConvention();
+ }
+
+ @Override public RelOptCost computeSelfCost(RelOptPlanner planner,
+ RelMetadataQuery mq) {
+ RelOptCost cost = super.computeSelfCost(planner, mq);
+ if (!collation.getFieldCollations().isEmpty()) {
+ return cost.multiplyBy(0.05);
+ } else {
+ return cost;
+ }
+ }
+
+ @Override public Sort copy(RelTraitSet traitSet, RelNode input,
+ RelCollation newCollation, RexNode offset, RexNode fetch) {
+ return new InnodbSort(getCluster(), traitSet, input, collation);
+ }
+
+ @Override public void implement(Implementor implementor) {
+ implementor.visitChild(0, getInput());
+
+ List<RelFieldCollation> sortCollations = collation.getFieldCollations();
+ boolean allDesc = sortCollations.stream().allMatch(r -> r.direction.isDescending());
+ boolean allNonDesc = sortCollations.stream().noneMatch(r -> r.direction.isDescending());
+ // field collation should be in a series of ascending or descending collations
+ checkState(allDesc || allNonDesc, "ordering should be in a "
+ + "series of ascending or descending collations " + sortCollations);
+ implementor.setAscOrder(!allDesc);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTable.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTable.java
new file mode 100644
index 0000000..14c6188
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTable.java
@@ -0,0 +1,274 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.adapter.java.AbstractQueryableTable;
+import org.apache.calcite.linq4j.AbstractEnumerable;
+import org.apache.calcite.linq4j.Enumerable;
+import org.apache.calcite.linq4j.Enumerator;
+import org.apache.calcite.linq4j.QueryProvider;
+import org.apache.calcite.linq4j.Queryable;
+import org.apache.calcite.linq4j.function.Function1;
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeImpl;
+import org.apache.calcite.rel.type.RelDataTypeSystem;
+import org.apache.calcite.rel.type.RelProtoDataType;
+import org.apache.calcite.schema.SchemaPlus;
+import org.apache.calcite.schema.TranslatableTable;
+import org.apache.calcite.schema.impl.AbstractTableQueryable;
+import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
+
+import com.alibaba.innodb.java.reader.Constants;
+import com.alibaba.innodb.java.reader.TableReader;
+import com.alibaba.innodb.java.reader.TableReaderFactory;
+import com.alibaba.innodb.java.reader.comparator.ComparisonOperator;
+import com.alibaba.innodb.java.reader.page.index.GenericRecord;
+import com.alibaba.innodb.java.reader.schema.KeyMeta;
+import com.alibaba.innodb.java.reader.schema.TableDef;
+import com.alibaba.innodb.java.reader.service.impl.RecordIterator;
+import com.google.common.base.Suppliers;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+import java.util.function.Supplier;
+import java.util.stream.Collectors;
+
+/**
+ * Table based on an InnoDB data file.
+ */
+public class InnodbTable extends AbstractQueryableTable
+ implements TranslatableTable {
+ private static final Logger LOGGER = LoggerFactory.getLogger(InnodbTable.class);
+
+ private final InnodbSchema schema;
+ private final String tableName;
+ private final Supplier<RelProtoDataType> protoRowTypeSupplier =
+ Suppliers.memoize(this::supplyProto);
+ private final Supplier<TableDef> tableDefSupplier =
+ Suppliers.memoize(this::supplyTableDef);
+
+ public InnodbTable(InnodbSchema schema, String tableName) {
+ super(Object[].class);
+ this.schema = schema;
+ this.tableName = tableName;
+ }
+
+ @Override public String toString() {
+ return "InnodbTable {" + tableName + "}";
+ }
+
+ private RelProtoDataType supplyProto() {
+ return schema.getRelDataType(tableName);
+ }
+
+ @Override public RelDataType getRowType(RelDataTypeFactory typeFactory) {
+ return protoRowTypeSupplier.get().apply(typeFactory);
+ }
+
+ public TableDef getTableDef() {
+ return tableDefSupplier.get();
+ }
+
+ private TableDef supplyTableDef() {
+ return schema.getTableDef(tableName);
+ }
+
+ /**
+ * Get index name set.
+ *
+ * @return set of index names
+ */
+ public Set<String> getIndexesNameSet() {
+ return ImmutableSet.<String>builder()
+ .add(Constants.PRIMARY_KEY_NAME)
+ .addAll(getTableDef().getSecondaryKeyMetaList().stream()
+ .map(KeyMeta::getName).collect(Collectors.toList()))
+ .build();
+ }
+
+ public Enumerable<Object> query(final TableReaderFactory tableReaderFactory) {
+ return query(tableReaderFactory, ImmutableList.of(), ImmutableList.of(),
+ IndexCondition.EMPTY_CONDITION, true);
+ }
+
+ /**
+ * Executes a query on the underlying InnoDB table.
+ *
+ * @param tableReaderFactory InnoDB Java table reader factory
+ * @param fields list of fields
+ * @param selectFields list of fields to project
+ * @param condition push down index condition
+ * @param ascOrder if scan ordering is ascending
+ * @return Enumerator of results
+ */
+ public Enumerable<Object> query(
+ final TableReaderFactory tableReaderFactory,
+ final List<Map.Entry<String, Class>> fields,
+ final List<Map.Entry<String, String>> selectFields,
+ final IndexCondition condition,
+ final Boolean ascOrder) {
+ final QueryType queryType = condition.getQueryType();
+ final List<Object> pointQueryKey = condition.getPointQueryKey();
+ final ComparisonOperator rangeQueryLowerOp = condition.getRangeQueryLowerOp();
+ final List<Object> rangeQueryLowerKey = condition.getRangeQueryLowerKey();
+ final ComparisonOperator rangeQueryUpperOp = condition.getRangeQueryUpperOp();
+ final List<Object> rangeQueryUpperKey = condition.getRangeQueryUpperKey();
+ final String indexName = condition.getIndexName();
+
+ // Build the type of the resulting row based on the provided fields
+ final RelDataTypeFactory typeFactory =
+ new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
+ final RelDataTypeFactory.Builder fieldInfo = typeFactory.builder();
+ final RelDataType rowType = getRowType(typeFactory);
+
+ Function1<String, Void> addField = fieldName -> {
+ RelDataType relDataType =
+ rowType.getField(fieldName, true, false).getType();
+ fieldInfo.add(fieldName, relDataType).nullable(relDataType.isNullable());
+ return null;
+ };
+
+ List<String> selectedColumnNames = new ArrayList<>(selectFields.size());
+ if (selectFields.isEmpty()) {
+ for (Map.Entry<String, Class> field : fields) {
+ addField.apply(field.getKey());
+ }
+ } else {
+ for (Map.Entry<String, String> field : selectFields) {
+ addField.apply(field.getKey());
+ selectedColumnNames.add(field.getKey());
+ }
+ }
+
+ final RelProtoDataType resultRowType = RelDataTypeImpl.proto(fieldInfo.build());
+
+ TableReader tableReader = tableReaderFactory.createTableReader(tableName);
+ tableReader.open();
+ return new AbstractEnumerable<Object>() {
+ @Override public Enumerator<Object> enumerator() {
+ Iterator<GenericRecord> resultIterator;
+ LOGGER.debug("Create query iterator, queryType={}, indexName={}, "
+ + "pointQueryKey={}, projection={}, rangeQueryKey={}{} AND {}{}, "
+ + "ascOrder={}", queryType, indexName, pointQueryKey,
+ selectedColumnNames, rangeQueryLowerKey, rangeQueryLowerOp,
+ rangeQueryUpperKey, rangeQueryUpperOp, ascOrder);
+ switch (queryType) {
+ case PK_POINT_QUERY:
+ resultIterator =
+ RecordIterator.create(tableReader
+ .queryByPrimaryKey(pointQueryKey, selectedColumnNames));
+ break;
+ case PK_RANGE_QUERY:
+ resultIterator = tableReader.getRangeQueryIterator(
+ rangeQueryLowerKey, rangeQueryLowerOp, rangeQueryUpperKey, rangeQueryUpperOp,
+ selectedColumnNames, ascOrder);
+ break;
+ case SK_POINT_QUERY:
+ resultIterator = tableReader.getRecordIteratorBySk(indexName,
+ pointQueryKey, ComparisonOperator.GTE, pointQueryKey, ComparisonOperator.LTE,
+ selectedColumnNames, ascOrder);
+ break;
+ case SK_RANGE_QUERY:
+ case SK_FULL_SCAN:
+ resultIterator = tableReader.getRecordIteratorBySk(indexName,
+ rangeQueryLowerKey, rangeQueryLowerOp, rangeQueryUpperKey, rangeQueryUpperOp,
+ selectedColumnNames, ascOrder);
+ break;
+ case PK_FULL_SCAN:
+ resultIterator =
+ tableReader.getQueryAllIterator(selectedColumnNames, ascOrder);
+ break;
+ default:
+ throw new AssertionError("query type is invalid");
+ }
+
+ RelDataType rowType = resultRowType.apply(typeFactory);
+ return new InnodbEnumerator(resultIterator, rowType) {
+ @Override public void close() {
+ super.close();
+ tableReader.close();
+ }
+ };
+ }
+ };
+ }
+
+ @Override public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
+ SchemaPlus schema, String tableName) {
+ return new InnodbQueryable<>(queryProvider, schema, this, tableName);
+ }
+
+ @Override public RelNode toRel(RelOptTable.ToRelContext context,
+ RelOptTable relOptTable) {
+ final RelOptCluster cluster = context.getCluster();
+ return new InnodbTableScan(cluster, cluster.traitSetOf(InnodbRel.CONVENTION),
+ relOptTable, this, null, context.getTableHints());
+ }
+
+ /**
+ * Implementation of {@link org.apache.calcite.linq4j.Queryable} based on
+ * a {@link org.apache.calcite.adapter.innodb.InnodbTable}.
+ *
+ * @param <T> element type
+ */
+ public static class InnodbQueryable<T> extends AbstractTableQueryable<T> {
+ public InnodbQueryable(QueryProvider queryProvider, SchemaPlus schema,
+ InnodbTable table, String tableName) {
+ super(queryProvider, schema, table, tableName);
+ }
+
+ @Override public Enumerator<T> enumerator() {
+ //noinspection unchecked
+ final Enumerable<T> enumerable =
+ (Enumerable<T>) getTable().query(getTableReaderFactory());
+ return enumerable.enumerator();
+ }
+
+ private InnodbTable getTable() {
+ return (InnodbTable) table;
+ }
+
+ private TableReaderFactory getTableReaderFactory() {
+ return schema.unwrap(InnodbSchema.class).tableReaderFactory;
+ }
+
+ /**
+ * Called via code-generation.
+ *
+ * @see org.apache.calcite.adapter.innodb.InnodbMethod#INNODB_QUERYABLE_QUERY
+ */
+ @SuppressWarnings("UnusedDeclaration")
+ public Enumerable<Object> query(List<Map.Entry<String, Class>> fields,
+ List<Map.Entry<String, String>> selectFields,
+ IndexCondition condition, Boolean ascOrder) {
+ return getTable().query(getTableReaderFactory(), fields, selectFields,
+ condition, ascOrder);
+ }
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTableScan.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTableScan.java
new file mode 100644
index 0000000..6c1ea81
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbTableScan.java
@@ -0,0 +1,153 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.RelWriter;
+import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.hint.HintPredicates;
+import org.apache.calcite.rel.hint.HintStrategyTable;
+import org.apache.calcite.rel.hint.RelHint;
+import org.apache.calcite.rel.type.RelDataType;
+
+import org.apache.commons.collections.CollectionUtils;
+
+import com.alibaba.innodb.java.reader.Constants;
+import com.alibaba.innodb.java.reader.schema.KeyMeta;
+
+import java.util.List;
+import java.util.Optional;
+import java.util.Set;
+import javax.annotation.Nullable;
+
+/**
+ * Relational expression representing a scan of an InnoDB data source.
+ */
+public class InnodbTableScan extends TableScan implements InnodbRel {
+ final InnodbTable innodbTable;
+ final RelDataType projectRowType;
+ /** Force to use one specific index from hint. */
+ private final @Nullable String forceIndexName;
+ /** This contains index to scan table and optional condition. */
+ private final IndexCondition indexCondition;
+
+ protected InnodbTableScan(RelOptCluster cluster, RelTraitSet traitSet,
+ RelOptTable table, InnodbTable innodbTable, RelDataType projectRowType,
+ List<RelHint> hints) {
+ super(cluster, traitSet, hints, table);
+ this.innodbTable = innodbTable;
+ this.projectRowType = projectRowType;
+ this.forceIndexName = getForceIndexName(hints).orElse(null);
+ this.indexCondition = getIndexCondition();
+ assert innodbTable != null;
+ assert getConvention() == InnodbRel.CONVENTION;
+ }
+
+ @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) {
+ assert inputs.isEmpty();
+ return this;
+ }
+
+ @Override public RelDataType deriveRowType() {
+ return projectRowType != null ? projectRowType : super.deriveRowType();
+ }
+
+ @Override public void register(RelOptPlanner planner) {
+ HintStrategyTable strategies = HintStrategyTable.builder()
+ .hintStrategy("index", HintPredicates.TABLE_SCAN)
+ .build();
+ getCluster().setHintStrategies(strategies);
+
+ planner.addRule(InnodbRules.TO_ENUMERABLE);
+ for (RelOptRule rule : InnodbRules.RULES) {
+ planner.addRule(rule);
+ }
+ }
+
+ @Override public void implement(Implementor implementor) {
+ implementor.innodbTable = innodbTable;
+ implementor.table = table;
+ implementor.setIndexCondition(indexCondition);
+ }
+
+ @Override public RelWriter explainTerms(RelWriter pw) {
+ return super.explainTerms(pw)
+ .itemIf("forceIndex", forceIndexName, forceIndexName != null);
+ }
+
+ /**
+ * Infer the implicit collation from index.
+ *
+ * @return the implicit collation based on the natural ordering of an index
+ */
+ public RelCollation getImplicitCollation() {
+ return indexCondition.getImplicitCollation();
+ }
+
+ private Optional<String> getForceIndexName(final List<RelHint> hints) {
+ if (CollectionUtils.isEmpty(hints)) {
+ return Optional.empty();
+ }
+ for (RelHint hint : hints) {
+ if ("index".equalsIgnoreCase(hint.hintName)) {
+ if (CollectionUtils.isNotEmpty(hint.listOptions)) {
+ Set<String> indexesNameSet = innodbTable.getIndexesNameSet();
+ Optional<String> forceIndexName = hint.listOptions.stream().findFirst();
+ if (!forceIndexName.isPresent()) {
+ return Optional.empty();
+ }
+ for (String indexName : indexesNameSet) {
+ if (indexName != null && indexName.equalsIgnoreCase(forceIndexName.get())) {
+ return Optional.of(indexName);
+ }
+ }
+ }
+ }
+ }
+ return Optional.empty();
+ }
+
+ public String getForceIndexName() {
+ return forceIndexName;
+ }
+
+ private IndexCondition getIndexCondition() {
+ // force to use a secondary index to scan table if present
+ if (forceIndexName != null
+ && !forceIndexName.equalsIgnoreCase(Constants.PRIMARY_KEY_NAME)) {
+ KeyMeta skMeta = innodbTable.getTableDef()
+ .getSecondaryKeyMetaMap().get(forceIndexName);
+ if (skMeta == null) {
+ throw new AssertionError("secondary index not found " + forceIndexName);
+ }
+ return IndexCondition.create(InnodbRules.innodbFieldNames(getRowType()),
+ forceIndexName, skMeta.getKeyColumnNames(),
+ QueryType.SK_FULL_SCAN);
+ }
+ // by default clustering index will be used to scan table
+ return IndexCondition.create(InnodbRules.innodbFieldNames(getRowType()),
+ Constants.PRIMARY_KEY_NAME,
+ innodbTable.getTableDef().getPrimaryKeyColumnNames(),
+ QueryType.PK_FULL_SCAN);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverter.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverter.java
new file mode 100644
index 0000000..1570cc8
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverter.java
@@ -0,0 +1,177 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.adapter.enumerable.EnumerableRel;
+import org.apache.calcite.adapter.enumerable.EnumerableRelImplementor;
+import org.apache.calcite.adapter.enumerable.JavaRowFormat;
+import org.apache.calcite.adapter.enumerable.PhysType;
+import org.apache.calcite.adapter.enumerable.PhysTypeImpl;
+import org.apache.calcite.config.CalciteSystemProperty;
+import org.apache.calcite.linq4j.tree.BlockBuilder;
+import org.apache.calcite.linq4j.tree.Expression;
+import org.apache.calcite.linq4j.tree.Expressions;
+import org.apache.calcite.plan.ConventionTraitDef;
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
+import org.apache.calcite.plan.RelOptPlanner;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.convert.ConverterImpl;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
+import org.apache.calcite.util.BuiltInMethod;
+import org.apache.calcite.util.Pair;
+import org.apache.calcite.util.Util;
+
+import org.apache.commons.collections.CollectionUtils;
+
+import com.alibaba.innodb.java.reader.comparator.ComparisonOperator;
+
+import java.util.AbstractList;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * Relational expression representing a scan of a table
+ * in InnoDB data source.
+ */
+public class InnodbToEnumerableConverter extends ConverterImpl
+ implements EnumerableRel {
+ protected InnodbToEnumerableConverter(
+ RelOptCluster cluster,
+ RelTraitSet traits,
+ RelNode input) {
+ super(cluster, ConventionTraitDef.INSTANCE, traits, input);
+ }
+
+ @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) {
+ return new InnodbToEnumerableConverter(
+ getCluster(), traitSet, sole(inputs));
+ }
+
+ @Override public RelOptCost computeSelfCost(RelOptPlanner planner,
+ RelMetadataQuery mq) {
+ return super.computeSelfCost(planner, mq).multiplyBy(.1);
+ }
+
+ static List<String> innodbFieldNames(final RelDataType rowType) {
+ return SqlValidatorUtil.uniquify(rowType.getFieldNames(),
+ SqlValidatorUtil.EXPR_SUGGESTER, true);
+ }
+
+ @Override public Result implement(EnumerableRelImplementor implementor, Prefer pref) {
+ final BlockBuilder list = new BlockBuilder();
+ final InnodbRel.Implementor innodbImplementor = new InnodbRel.Implementor();
+ innodbImplementor.visitChild(0, getInput());
+ final RelDataType rowType = getRowType();
+ final PhysType physType =
+ PhysTypeImpl.of(
+ implementor.getTypeFactory(), rowType,
+ pref.prefer(JavaRowFormat.ARRAY));
+ final Expression fields =
+ list.append("fields",
+ constantArrayList(
+ Pair.zip(InnodbToEnumerableConverter.innodbFieldNames(rowType),
+ new AbstractList<Class>() {
+ @Override public Class get(int index) {
+ return physType.fieldClass(index);
+ }
+
+ @Override public int size() {
+ return rowType.getFieldCount();
+ }
+ }),
+ Pair.class));
+ List<Map.Entry<String, String>> selectList = new ArrayList<>();
+ for (Map.Entry<String, String> entry
+ : Pair.zip(innodbImplementor.selectFields.keySet(),
+ innodbImplementor.selectFields.values())) {
+ selectList.add(entry);
+ }
+ final Expression selectFields =
+ list.append("selectFields", constantArrayList(selectList, Pair.class));
+ final Expression table =
+ list.append("table",
+ innodbImplementor.table.getExpression(
+ InnodbTable.InnodbQueryable.class));
+ IndexCondition condition = innodbImplementor.indexCondition;
+ final Expression indexName =
+ list.append("indexName",
+ Expressions.constant(condition.getIndexName(), String.class));
+ final Expression queryType =
+ list.append("queryType",
+ Expressions.constant(condition.getQueryType(), QueryType.class));
+ final Expression pointQueryKey =
+ list.append("pointQueryKey",
+ constantArrayList(condition.getPointQueryKey(), Object.class));
+ final Expression rangeQueryLowerOp =
+ list.append("rangeQueryLowerOp",
+ Expressions.constant(condition.getRangeQueryLowerOp(), ComparisonOperator.class));
+ final Expression rangeQueryLowerKey =
+ list.append("rangeQueryLowerKey",
+ constantArrayList(condition.getRangeQueryLowerKey(), Object.class));
+ final Expression rangeQueryUpperOp =
+ list.append("rangeQueryUpperOp",
+ Expressions.constant(condition.getRangeQueryUpperOp(), ComparisonOperator.class));
+ final Expression rangeQueryUpperKey =
+ list.append("rangeQueryUpperKey",
+ constantArrayList(condition.getRangeQueryUpperKey(), Object.class));
+ final Expression cond =
+ list.append("condition",
+ Expressions.call(
+ IndexCondition.class,
+ "create", indexName, queryType, pointQueryKey,
+ rangeQueryLowerOp, rangeQueryUpperOp, rangeQueryLowerKey, rangeQueryUpperKey));
+ final Expression ascOrder = Expressions.constant(
+ innodbImplementor.ascOrder);
+ Expression enumerable =
+ list.append("enumerable",
+ Expressions.call(table,
+ InnodbMethod.INNODB_QUERYABLE_QUERY.method, fields,
+ selectFields, cond, ascOrder));
+ if (CalciteSystemProperty.DEBUG.value()) {
+ System.out.println("Innodb: " + Expressions.toString(enumerable));
+ }
+ list.add(Expressions.return_(null, enumerable));
+ return implementor.result(physType, list.toBlock());
+ }
+
+ /**
+ * E.g. {@code constantArrayList("x", "y")} returns
+ * "Arrays.asList('x', 'y')".
+ */
+ private static <T> Expression constantArrayList(List<T> values, Class clazz) {
+ return Expressions.call(
+ BuiltInMethod.ARRAYS_AS_LIST.method,
+ Expressions.newArrayInit(clazz, constantList(values)));
+ }
+
+ /**
+ * E.g. {@code constantList("x", "y")} returns
+ * {@code {ConstantExpression("x"), ConstantExpression("y")}}.
+ */
+ private static <T> List<Expression> constantList(List<T> values) {
+ if (CollectionUtils.isEmpty(values)) {
+ return Collections.emptyList();
+ }
+ return Util.transform(values, Expressions::constant);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverterRule.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverterRule.java
new file mode 100644
index 0000000..0b12406
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/InnodbToEnumerableConverterRule.java
@@ -0,0 +1,45 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.adapter.innodb;
+
+import org.apache.calcite.adapter.enumerable.EnumerableConvention;
+import org.apache.calcite.plan.RelTraitSet;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.convert.ConverterRule;
+
+/**
+ * Rule to convert a relational expression from
+ * {@link InnodbRel#CONVENTION} to {@link EnumerableConvention}.
+ */
+public class InnodbToEnumerableConverterRule extends ConverterRule {
+
+ /** Default configuration. */
+ public static final Config DEFAULT_CONFIG = Config.INSTANCE
+ .withConversion(RelNode.class, InnodbRel.CONVENTION,
+ EnumerableConvention.INSTANCE, "InnodbToEnumerableConverterRule")
+ .withRuleFactory(InnodbToEnumerableConverterRule::new);
+
+ /** Creates a InnodbToEnumerableConverterRule. */
+ protected InnodbToEnumerableConverterRule(Config config) {
+ super(config);
+ }
+
+ @Override public RelNode convert(RelNode rel) {
+ RelTraitSet newTraitSet = rel.getTraitSet().replace(getOutConvention());
+ return new InnodbToEnumerableConverter(rel.getCluster(), newTraitSet, rel);
+ }
+}
diff --git a/innodb/src/main/java/org/apache/calcite/adapter/innodb/QueryType.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/QueryType.java
new file mode 100644
index 0000000..2eeaf0b
--- /dev/null
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/QueryType.java
@@ -0,0 +1,53 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+/**
+ * Query type of a push down condition in InnoDB data source.
+ */
+public enum QueryType {
+ /** Primary key point query. */
+ PK_POINT_QUERY(0),
+ /** Secondary key point query. */
+ SK_POINT_QUERY(1),
+ /** Primary key range query with lower and upper bound. */
+ PK_RANGE_QUERY(2),
+ /** Secondary key range query with lower and upper bound. */
+ SK_RANGE_QUERY(3),
+ /** Scanning table fully with primary key. */
+ PK_FULL_SCAN(4),
+ /** Scanning table fully with secondary key. */
+ SK_FULL_SCAN(5);
+
+ private final int priority;
+
+ static QueryType getPointQuery(boolean isSk) {
+ return isSk ? SK_POINT_QUERY : PK_POINT_QUERY;
+ }
+
+ static QueryType getRangeQuery(boolean isSk) {
+ return isSk ? SK_RANGE_QUERY : PK_RANGE_QUERY;
+ }
+
+ QueryType(int priority) {
+ this.priority = priority;
+ }
+
+ int priority() {
+ return priority;
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/util/Holder.java b/innodb/src/main/java/org/apache/calcite/adapter/innodb/package-info.java
similarity index 51%
copy from core/src/main/java/org/apache/calcite/util/Holder.java
copy to innodb/src/main/java/org/apache/calcite/adapter/innodb/package-info.java
index 504b9d4..298f958 100644
--- a/core/src/main/java/org/apache/calcite/util/Holder.java
+++ b/innodb/src/main/java/org/apache/calcite/adapter/innodb/package-info.java
@@ -14,40 +14,8 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-package org.apache.calcite.util;
/**
- * A mutable slot that can contain one object.
- *
- * <p>A holder is useful for implementing OUT or IN-OUT parameters.</p>
- *
- * <p>It is possible to sub-class to receive events on get or set.</p>
- *
- * @param <E> Element type
+ * InnoDB query provider.
*/
-public class Holder<E> {
- private E e;
-
- /** Creates a Holder containing a given value.
- *
- * <p>Call this method from a derived constructor or via the {@link #of}
- * method. */
- protected Holder(E e) {
- this.e = e;
- }
-
- /** Sets the value. */
- public void set(E e) {
- this.e = e;
- }
-
- /** Gets the value. */
- public E get() {
- return e;
- }
-
- /** Creates a holder containing a given value. */
- public static <E> Holder<E> of(E e) {
- return new Holder<>(e);
- }
-}
+package org.apache.calcite.adapter.innodb;
diff --git a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterDataTypesTest.java b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterDataTypesTest.java
new file mode 100644
index 0000000..bf48ab2
--- /dev/null
+++ b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterDataTypesTest.java
@@ -0,0 +1,210 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.test.CalciteAssert;
+import org.apache.calcite.util.Sources;
+
+import org.apache.commons.lang3.StringUtils;
+
+import com.alibaba.innodb.java.reader.util.Utils;
+import com.google.common.collect.ImmutableMap;
+
+import org.junit.jupiter.api.Test;
+
+import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.OffsetDateTime;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
+import java.time.zone.ZoneRules;
+
+/**
+ * Tests for the {@code org.apache.calcite.adapter.innodb} package related to data types.
+ *
+ * <p>Will read InnoDB data file {@code test_types.ibd}.
+ */
+public class InnodbAdapterDataTypesTest {
+
+ private static final ImmutableMap<String, String> INNODB_MODEL = ImmutableMap.of("model",
+ Sources.of(InnodbAdapterTest.class.getResource("/model.json"))
+ .file().getAbsolutePath());
+
+ @Test void testTypesRowType() {
+ CalciteAssert.that()
+ .with(INNODB_MODEL)
+ .query("select * from \"test_types\"")
+ .typeIs("[id INTEGER NOT NULL, "
+ + "f_tinyint TINYINT NOT NULL, "
+ + "f_smallint SMALLINT NOT NULL, "
+ + "f_mediumint INTEGER NOT NULL, "
+ + "f_int INTEGER NOT NULL, "
+ + "f_bigint BIGINT NOT NULL, "
+ + "f_datetime TIMESTAMP NOT NULL, "
+ + "f_timestamp TIMESTAMP_WITH_LOCAL_TIME_ZONE NOT NULL, "
+ + "f_time TIME NOT NULL, "
+ + "f_year SMALLINT NOT NULL, "
+ + "f_date DATE NOT NULL, "
+ + "f_float REAL NOT NULL, "
+ + "f_double DOUBLE NOT NULL, "
+ + "f_decimal1 DECIMAL NOT NULL, "
+ + "f_decimal2 DECIMAL NOT NULL, "
+ + "f_decimal3 DECIMAL NOT NULL, "
+ + "f_decimal4 DECIMAL NOT NULL, "
+ + "f_decimal5 DECIMAL NOT NULL, "
+ + "f_decimal6 DECIMAL, "
+ + "f_varchar VARCHAR NOT NULL, "
+ + "f_varchar_overflow VARCHAR NOT NULL, "
+ + "f_varchar_null VARCHAR, "
+ + "f_char_32 CHAR NOT NULL, "
+ + "f_char_255 CHAR NOT NULL, "
+ + "f_char_null CHAR, "
+ + "f_boolean BOOLEAN NOT NULL, "
+ + "f_bool BOOLEAN NOT NULL, "
+ + "f_tinytext VARCHAR NOT NULL, "
+ + "f_text VARCHAR NOT NULL, "
+ + "f_mediumtext VARCHAR NOT NULL, "
+ + "f_longtext VARCHAR NOT NULL, "
+ + "f_tinyblob VARBINARY NOT NULL, "
+ + "f_blob VARBINARY NOT NULL, "
+ + "f_mediumblob VARBINARY NOT NULL, "
+ + "f_longblob VARBINARY NOT NULL, "
+ + "f_varbinary VARBINARY NOT NULL, "
+ + "f_varbinary_overflow VARBINARY NOT NULL, "
+ + "f_enum VARCHAR NOT NULL, "
+ + "f_set VARCHAR NOT NULL]");
+ }
+
+ @Test void testTypesValues() {
+ CalciteAssert.that()
+ .with(INNODB_MODEL)
+ .query("select * from \"test_types\"")
+ .returnsOrdered(
+ "id=1; "
+ + "f_tinyint=100; "
+ + "f_smallint=10000; "
+ + "f_mediumint=1000000; "
+ + "f_int=10000000; "
+ + "f_bigint=100000000000; "
+ + "f_datetime=2019-10-02 10:59:59; "
+ + "f_timestamp=" + expectedLocalTime("1988-11-23 22:10:08") + "; "
+ + "f_time=00:36:52; "
+ + "f_year=2012; "
+ + "f_date=2020-01-29; "
+ + "f_float=0.9876543; "
+ + "f_double=1.23456789012345E9; "
+ + "f_decimal1=123456; "
+ + "f_decimal2=12345.67890; "
+ + "f_decimal3=12345678901; "
+ + "f_decimal4=123.100; "
+ + "f_decimal5=12346; "
+ + "f_decimal6=12345.1234567890123456789012345; "
+ + "f_varchar=c" + StringUtils.repeat('x', 31) + "; "
+ + "f_varchar_overflow=c" + StringUtils.repeat("データ", 300) + "; "
+ + "f_varchar_null=null; "
+ + "f_char_32=c" + StringUtils.repeat("данные", 2) + "; "
+ + "f_char_255=c" + StringUtils.repeat("数据", 100) + "; "
+ + "f_char_null=null; "
+ + "f_boolean=false; "
+ + "f_bool=true; "
+ + "f_tinytext=c" + StringUtils.repeat("Data", 50) + "; "
+ + "f_text=c" + StringUtils.repeat("Daten", 200) + "; "
+ + "f_mediumtext=c" + StringUtils.repeat("Datos", 200) + "; "
+ + "f_longtext=c" + StringUtils.repeat("Les données", 800) + "; "
+ + "f_tinyblob="
+ + genByteArrayString("63", (byte) 0x0a, 100) + "; "
+ + "f_blob="
+ + genByteArrayString("63", (byte) 0x0b, 400) + "; "
+ + "f_mediumblob="
+ + genByteArrayString("63", (byte) 0x0c, 800) + "; "
+ + "f_longblob="
+ + genByteArrayString("63", (byte) 0x0d, 1000) + "; "
+ + "f_varbinary="
+ + genByteArrayString("63", (byte) 0x0e, 8) + "; "
+ + "f_varbinary_overflow="
+ + genByteArrayString("63", (byte) 0xff, 100) + "; "
+ + "f_enum=MYSQL; "
+ + "f_set=z",
+ "id=2; "
+ + "f_tinyint=-100; "
+ + "f_smallint=-10000; "
+ + "f_mediumint=-1000000; "
+ + "f_int=-10000000; "
+ + "f_bigint=-9223372036854775807; "
+ + "f_datetime=2255-01-01 12:12:12; "
+ + "f_timestamp=" + expectedLocalTime("2020-01-01 00:00:00") + "; "
+ + "f_time=23:11:00; "
+ + "f_year=0; "
+ + "f_date=1970-01-01; "
+ + "f_float=-1.2345678E7; "
+ + "f_double=-1.234567890123456E9; "
+ + "f_decimal1=9; "
+ + "f_decimal2=-567.89100; "
+ + "f_decimal3=987654321; "
+ + "f_decimal4=456.000; "
+ + "f_decimal5=0; "
+ + "f_decimal6=-0.0123456789012345678912345; "
+ + "f_varchar=d" + StringUtils.repeat('y', 31) + "; "
+ + "f_varchar_overflow=d" + StringUtils.repeat("データ", 300) + "; "
+ + "f_varchar_null=null; "
+ + "f_char_32=d" + StringUtils.repeat("данные", 2) + "; "
+ + "f_char_255=d" + StringUtils.repeat("数据", 100) + "; "
+ + "f_char_null=null; "
+ + "f_boolean=false; "
+ + "f_bool=true; "
+ + "f_tinytext=d" + StringUtils.repeat("Data", 50) + "; "
+ + "f_text=d" + StringUtils.repeat("Daten", 200) + "; "
+ + "f_mediumtext=d" + StringUtils.repeat("Datos", 200) + "; "
+ + "f_longtext=d" + StringUtils.repeat("Les données", 800) + "; "
+ + "f_tinyblob="
+ + genByteArrayString("64", (byte) 0x0a, 100) + "; "
+ + "f_blob="
+ + genByteArrayString("64", (byte) 0x0b, 400) + "; "
+ + "f_mediumblob="
+ + genByteArrayString("64", (byte) 0x0c, 800) + "; "
+ + "f_longblob="
+ + genByteArrayString("64", (byte) 0x0d, 1000) + "; "
+ + "f_varbinary="
+ + genByteArrayString("64", (byte) 0x0e, 8) + "; "
+ + "f_varbinary_overflow="
+ + genByteArrayString("64", (byte) 0xff, 100) + "; "
+ + "f_enum=Hello; "
+ + "f_set=a,e,i,o,u");
+ }
+
+ private String genByteArrayString(String prefix, byte b, int repeat) {
+ StringBuilder str = new StringBuilder();
+ str.append(prefix);
+ for (int i = 0; i < repeat; i++) {
+ String hexString = Integer.toHexString(b & 0xFF);
+ if (hexString.length() < 2) {
+ str.append("0");
+ }
+ str.append(hexString);
+ }
+ return str.toString();
+ }
+
+ private static String expectedLocalTime(String dateTime) {
+ ZoneRules rules = ZoneId.systemDefault().getRules();
+ LocalDateTime ldt = Utils.parseDateTimeText(dateTime);
+ Instant instant = ldt.toInstant(ZoneOffset.of("+00:00"));
+ ZoneOffset standardOffset = rules.getOffset(instant);
+ OffsetDateTime odt = instant.atOffset(standardOffset);
+ return odt.toLocalDateTime().format(Utils.TIME_FORMAT_TIMESTAMP[0]);
+ }
+}
diff --git a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
new file mode 100644
index 0000000..f6da8dc
--- /dev/null
+++ b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
@@ -0,0 +1,1237 @@
+/*
+ * 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.calcite.adapter.innodb;
+
+import org.apache.calcite.config.CalciteSystemProperty;
+import org.apache.calcite.rel.hint.HintPredicates;
+import org.apache.calcite.rel.hint.HintStrategyTable;
+import org.apache.calcite.runtime.Hook;
+import org.apache.calcite.sql2rel.SqlToRelConverter;
+import org.apache.calcite.test.CalciteAssert;
+import org.apache.calcite.util.Holder;
+import org.apache.calcite.util.Pair;
+import org.apache.calcite.util.Sources;
+
+import org.apache.commons.collections.CollectionUtils;
+
+import com.alibaba.innodb.java.reader.util.Utils;
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.Lists;
+
+import org.junit.jupiter.api.AfterEach;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.OffsetDateTime;
+import java.time.ZoneId;
+import java.time.ZoneOffset;
+import java.time.zone.ZoneRules;
+import java.util.Arrays;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+import static java.util.stream.Collectors.toList;
+
+/**
+ * Tests for the {@code org.apache.calcite.adapter.innodb} package.
+ *
+ * <p>Will read InnoDB data file {@code emp.ibd} and {@code dept.ibd}.
+ */
+public class InnodbAdapterTest {
+
+ private static final ImmutableMap<String, String> INNODB_MODEL = ImmutableMap.of("model",
+ Sources.of(InnodbAdapterTest.class.getResource("/model.json"))
+ .file().getAbsolutePath());
+
+ @Test void testSelectCount() {
+ sql("SELECT * FROM \"EMP\"")
+ .returnsCount(14);
+ }
+
+ @Test void testSelectAll() {
+ sql("SELECT * FROM \"EMP\"")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(all());
+ }
+
+ @Test void testSelectAll2() {
+ sql("SELECT * FROM \"DEPT\"")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, DEPT]])\n")
+ .returns("DEPTNO=10; DNAME=ACCOUNTING; LOC=NEW YORK\n"
+ + "DEPTNO=20; DNAME=RESEARCH; LOC=DALLAS\n"
+ + "DEPTNO=30; DNAME=SALES; LOC=CHICAGO\n"
+ + "DEPTNO=40; DNAME=OPERATIONS; LOC=BOSTON\n");
+ }
+
+ @Test void testSelectAllProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\"")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7369; ENAME=SMITH\n"
+ + "EMPNO=7499; ENAME=ALLEN\n"
+ + "EMPNO=7521; ENAME=WARD\n"
+ + "EMPNO=7566; ENAME=JONES\n"
+ + "EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7900; ENAME=JAMES\n"
+ + "EMPNO=7902; ENAME=FORD\n"
+ + "EMPNO=7934; ENAME=MILLER\n");
+ }
+
+ @Test void testSelectAllOrderByAsc() {
+ sql("SELECT * FROM \"EMP\" ORDER BY EMPNO ASC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbSort(sort0=[$0], dir0=[ASC])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(all());
+ }
+
+ @Test void testSelectAllOrderByDesc() {
+ sql("SELECT * FROM \"EMP\" ORDER BY EMPNO DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbSort(sort0=[$0], dir0=[DESC])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(allReversed());
+ }
+
+ @Test void testSelectAllProjectSomeFieldsOrderByDesc() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" ORDER BY EMPNO DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbSort(sort0=[$0], dir0=[DESC])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7934; ENAME=MILLER\n"
+ + "EMPNO=7902; ENAME=FORD\n"
+ + "EMPNO=7900; ENAME=JAMES\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7566; ENAME=JONES\n"
+ + "EMPNO=7521; ENAME=WARD\n"
+ + "EMPNO=7499; ENAME=ALLEN\n"
+ + "EMPNO=7369; ENAME=SMITH\n");
+ }
+
+ @Test void testSelectByPrimaryKey() {
+ for (Integer empno : empnoMap.keySet()) {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO = " + empno)
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, EMPNO="
+ + empno + ")])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(empno));
+ }
+ }
+
+ @Test void testSelectByPrimaryKeyNothing() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO = 0")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, EMPNO=0)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyProjectAllFields() {
+ sql("SELECT ENAME,EMPNO,JOB,AGE,MGR,HIREDATE,SAL,COMM,DEPTNO,EMAIL,"
+ + "CREATE_DATETIME,CREATE_TIME,UPSERT_TIME FROM \"EMP\" WHERE EMPNO = 7499")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(ENAME=[$1], EMPNO=[$0], JOB=[$2], AGE=[$3], MGR=[$4], "
+ + "HIREDATE=[$5], SAL=[$6], COMM=[$7], DEPTNO=[$8], EMAIL=[$9], "
+ + "CREATE_DATETIME=[$10], CREATE_TIME=[$11], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, EMPNO=7499)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("ENAME=ALLEN; EMPNO=7499; JOB=SALESMAN; AGE=24; MGR=7698; HIREDATE=1981-02-20; "
+ + "SAL=1600.00; COMM=300.00; DEPTNO=30; EMAIL=allen@calcite; "
+ + "CREATE_DATETIME=2018-04-09 09:00:00; CREATE_TIME=09:00:00; "
+ + "UPSERT_TIME=" + expectedLocalTime("2018-04-09 09:00:00") + "\n");
+ }
+
+ @Test void testSelectByPrimaryKeyProjectSomeFields() {
+ sql("SELECT EMPNO,AGE,HIREDATE FROM \"EMP\" WHERE EMPNO = 7902")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], AGE=[$3], HIREDATE=[$5])\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, EMPNO=7902)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7902; AGE=28; HIREDATE=1981-12-03\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGt() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO > 7600")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>7600)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoGt(7600));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGt2() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO > 7654")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>7654)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoGt(7654));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtNothing() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO > 10000")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>10000)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGte() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO >= 7600")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>=7600)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoGte(7600));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGte2() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO >= 7654")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>=7654)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoGte(7654));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGte3() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO >= 10000")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO>=10000)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLt() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO < 7800")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<7800)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoLt(7800));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLt2() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO < 7839")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<7839)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoLt(7839));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLtNothing() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO < 5000")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<5000)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLte() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO <= 7800")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<=7800)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoLte(7800));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLte2() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO <= 7839")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<=7839)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(someEmpnoLte(7839));
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryLteNothing() {
+ sql("SELECT * FROM \"EMP\" WHERE EMPNO <= 5000")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, EMPNO<=5000)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtLtProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO > 7600 AND EMPNO < 7900")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>7600, EMPNO<7900)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtLteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO > 7600 AND EMPNO <= 7900")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>7600, EMPNO<=7900)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7900; ENAME=JAMES\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGteLtProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO >= 7369 AND EMPNO < 7900")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>=7369, EMPNO<7900)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7369; ENAME=SMITH\n"
+ + "EMPNO=7499; ENAME=ALLEN\n"
+ + "EMPNO=7521; ENAME=WARD\n"
+ + "EMPNO=7566; ENAME=JONES\n"
+ + "EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGteLteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO >= 7788 AND EMPNO <= 7900")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>=7788, EMPNO<=7900)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7900; ENAME=JAMES\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtLtNothing() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO > 7370 AND EMPNO < 7400")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>7370, EMPNO<7400)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGteLteEqualsProjectSomeFields() {
+ for (Integer empno : empnoMap.keySet()) {
+ sql("SELECT EMPNO FROM \"EMP\" WHERE EMPNO >= " + empno
+ + " AND EMPNO <= " + empno)
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0])\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, EMPNO="
+ + empno + ")])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=" + empno + "\n");
+ }
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtProjectSomeFieldsOrderByAsc() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO > 7600 ORDER BY EMPNO ASC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbSort(sort0=[$0], dir0=[ASC])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>7600)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; ENAME=MARTIN\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7900; ENAME=JAMES\n"
+ + "EMPNO=7902; ENAME=FORD\n"
+ + "EMPNO=7934; ENAME=MILLER\n");
+ }
+
+ @Test void testSelectByPrimaryKeyRangeQueryGtProjectSomeFieldsOrderByDesc() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE EMPNO > 7600 ORDER BY EMPNO DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbSort(sort0=[$0], dir0=[DESC])\n"
+ + " InnodbFilter(condition=[(PK_RANGE_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO>7600)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7934; ENAME=MILLER\n"
+ + "EMPNO=7902; ENAME=FORD\n"
+ + "EMPNO=7900; ENAME=JAMES\n"
+ + "EMPNO=7876; ENAME=ADAMS\n"
+ + "EMPNO=7844; ENAME=TURNER\n"
+ + "EMPNO=7839; ENAME=KING\n"
+ + "EMPNO=7788; ENAME=SCOTT\n"
+ + "EMPNO=7782; ENAME=CLARK\n"
+ + "EMPNO=7698; ENAME=BLAKE\n"
+ + "EMPNO=7654; ENAME=MARTIN\n");
+ }
+
+ @Test void testSelectBySkVarchar() {
+ sql("SELECT * FROM \"EMP\" WHERE ENAME = 'JONES'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=ENAME_KEY, ENAME=JONES)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7566));
+ }
+
+ @Test void testSelectBySkVarcharNotExists() {
+ sql("SELECT * FROM \"EMP\" WHERE ENAME = 'NOT_EXISTS'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=ENAME_KEY, "
+ + "ENAME=NOT_EXISTS)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkVarcharInTransformToPointQuery() {
+ sql("SELECT * FROM \"EMP\" WHERE ENAME IN ('FORD')")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=ENAME_KEY, ENAME=FORD)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7902));
+ }
+
+ @Test void testSelectBySkVarcharCaseInsensitive() {
+ sql("SELECT * FROM \"EMP\" WHERE ENAME = 'miller'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=ENAME_KEY, ENAME=miller)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7934));
+ }
+
+ @Test void testSelectBySkVarcharProjectSomeFields() {
+ sql("SELECT ENAME,UPSERT_TIME FROM \"EMP\" WHERE ENAME = 'BLAKE'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=ENAME_KEY, ENAME=BLAKE)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("ENAME=BLAKE; UPSERT_TIME=" + expectedLocalTime("2018-06-01 14:45:00") + "\n");
+ }
+
+ @Test void testSelectBySkVarcharRangeQueryCoveringIndexOrderByDesc() {
+ sql("SELECT ENAME FROM \"EMP\" WHERE ENAME >= 'CLARK' AND ENAME < 'SMITHY' ORDER BY ENAME DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(ENAME=[$1])\n"
+ + " InnodbSort(sort0=[$1], dir0=[DESC])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=ENAME_KEY, "
+ + "ENAME>=CLARK, ENAME<SMITHY)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("ENAME=SMITH\n"
+ + "ENAME=SCOTT\n"
+ + "ENAME=MILLER\n"
+ + "ENAME=MARTIN\n"
+ + "ENAME=KING\n"
+ + "ENAME=JONES\n"
+ + "ENAME=JAMES\n"
+ + "ENAME=FORD\n"
+ + "ENAME=CLARK\n");
+ }
+
+ @Test void testSelectBySkVarcharRangeQueryGtProjectSomeFieldsOrderByNonSkAsc() {
+ sql("SELECT EMPNO,ENAME,AGE FROM \"EMP\" WHERE ENAME > 'MILLER' ORDER BY AGE ASC")
+ .explainContains("PLAN=EnumerableSort(sort0=[$2], dir0=[ASC])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], AGE=[$3])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=ENAME_KEY, "
+ + "ENAME>MILLER)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7369; ENAME=SMITH; AGE=30\n"
+ + "EMPNO=7521; ENAME=WARD; AGE=41\n"
+ + "EMPNO=7788; ENAME=SCOTT; AGE=45\n"
+ + "EMPNO=7844; ENAME=TURNER; AGE=54\n");
+ }
+
+ @Test void testSelectBySkVarcharRangeQueryGtProjectSomeFieldsOrderByNonSkDesc() {
+ sql("SELECT EMPNO,ENAME,SAL FROM \"EMP\" WHERE ENAME > 'MILLER' ORDER BY SAL DESC")
+ .explainContains("PLAN=EnumerableSort(sort0=[$2], dir0=[DESC])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], SAL=[$6])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=ENAME_KEY, "
+ + "ENAME>MILLER)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; ENAME=SCOTT; SAL=3000.00\n"
+ + "EMPNO=7844; ENAME=TURNER; SAL=1500.00\n"
+ + "EMPNO=7521; ENAME=WARD; SAL=1250.00\n"
+ + "EMPNO=7369; ENAME=SMITH; SAL=800.00\n");
+ }
+
+ @Test void testSelectBySkDateProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,HIREDATE FROM \"EMP\" WHERE HIREDATE = '1980-12-17'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], HIREDATE=[$5])\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=HIREDATE_KEY, "
+ + "HIREDATE=1980-12-17)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7369; ENAME=SMITH; HIREDATE=1980-12-17\n");
+ }
+
+ @Test void testSelectBySkDateRangeQueryGtProjectSomeFields() {
+ sql("SELECT DEPTNO,ENAME,HIREDATE FROM \"EMP\" WHERE HIREDATE > '1970-01-01'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$8], ENAME=[$1], HIREDATE=[$5])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=HIREDATE_KEY, "
+ + "HIREDATE>1970-01-01)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("DEPTNO=20; ENAME=SMITH; HIREDATE=1980-12-17\n"
+ + "DEPTNO=30; ENAME=BLAKE; HIREDATE=1981-01-05\n"
+ + "DEPTNO=20; ENAME=JONES; HIREDATE=1981-02-04\n"
+ + "DEPTNO=30; ENAME=ALLEN; HIREDATE=1981-02-20\n"
+ + "DEPTNO=30; ENAME=WARD; HIREDATE=1981-02-22\n"
+ + "DEPTNO=10; ENAME=CLARK; HIREDATE=1981-06-09\n"
+ + "DEPTNO=30; ENAME=TURNER; HIREDATE=1981-09-08\n"
+ + "DEPTNO=30; ENAME=MARTIN; HIREDATE=1981-09-28\n"
+ + "DEPTNO=10; ENAME=KING; HIREDATE=1981-11-17\n"
+ + "DEPTNO=30; ENAME=JAMES; HIREDATE=1981-12-03\n"
+ + "DEPTNO=20; ENAME=FORD; HIREDATE=1981-12-03\n"
+ + "DEPTNO=10; ENAME=MILLER; HIREDATE=1982-01-23\n"
+ + "DEPTNO=20; ENAME=SCOTT; HIREDATE=1987-04-19\n"
+ + "DEPTNO=20; ENAME=ADAMS; HIREDATE=1987-05-23\n")
+ .returnsCount(14);
+ }
+
+ @Test void testSelectBySkDateRangeQueryLtProjectSomeFieldsOrderByDesc() {
+ sql("SELECT DEPTNO,ENAME,HIREDATE FROM \"EMP\" WHERE HIREDATE < '2020-01-01' "
+ + "ORDER BY HIREDATE DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$8], ENAME=[$1], HIREDATE=[$5])\n"
+ + " InnodbSort(sort0=[$5], dir0=[DESC])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=HIREDATE_KEY, "
+ + "HIREDATE<2020-01-01)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("DEPTNO=20; ENAME=ADAMS; HIREDATE=1987-05-23\n"
+ + "DEPTNO=20; ENAME=SCOTT; HIREDATE=1987-04-19\n"
+ + "DEPTNO=10; ENAME=MILLER; HIREDATE=1982-01-23\n"
+ + "DEPTNO=20; ENAME=FORD; HIREDATE=1981-12-03\n"
+ + "DEPTNO=30; ENAME=JAMES; HIREDATE=1981-12-03\n"
+ + "DEPTNO=10; ENAME=KING; HIREDATE=1981-11-17\n"
+ + "DEPTNO=30; ENAME=MARTIN; HIREDATE=1981-09-28\n"
+ + "DEPTNO=30; ENAME=TURNER; HIREDATE=1981-09-08\n"
+ + "DEPTNO=10; ENAME=CLARK; HIREDATE=1981-06-09\n"
+ + "DEPTNO=30; ENAME=WARD; HIREDATE=1981-02-22\n"
+ + "DEPTNO=30; ENAME=ALLEN; HIREDATE=1981-02-20\n"
+ + "DEPTNO=20; ENAME=JONES; HIREDATE=1981-02-04\n"
+ + "DEPTNO=30; ENAME=BLAKE; HIREDATE=1981-01-05\n"
+ + "DEPTNO=20; ENAME=SMITH; HIREDATE=1980-12-17\n")
+ .returnsCount(14);
+ }
+
+ @Test void testSelectBySkDateRangeQueryGtLteProjectSomeFields() {
+ sql("SELECT DEPTNO,ENAME,HIREDATE FROM \"EMP\" WHERE HIREDATE < '1981-12-03' "
+ + "AND HIREDATE >= '1981-06-09'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$8], ENAME=[$1], HIREDATE=[$5])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=HIREDATE_KEY, "
+ + "HIREDATE>=1981-06-09, HIREDATE<1981-12-03)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("DEPTNO=10; ENAME=CLARK; HIREDATE=1981-06-09\n"
+ + "DEPTNO=30; ENAME=TURNER; HIREDATE=1981-09-08\n"
+ + "DEPTNO=30; ENAME=MARTIN; HIREDATE=1981-09-28\n"
+ + "DEPTNO=10; ENAME=KING; HIREDATE=1981-11-17\n");
+ }
+
+ @Test void testSelectBySkDateRangeQueryNothing() {
+ sql("SELECT DEPTNO,ENAME,HIREDATE FROM \"EMP\" WHERE HIREDATE < '1981-12-03' "
+ + "AND HIREDATE > '1981-12-01'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$8], ENAME=[$1], HIREDATE=[$5])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=HIREDATE_KEY, "
+ + "HIREDATE>1981-12-01, "
+ + "HIREDATE<1981-12-03)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkTime() {
+ sql("SELECT * FROM \"EMP\" WHERE CREATE_TIME = '12:12:56'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=CREATE_TIME_KEY, "
+ + "CREATE_TIME=12:12:56)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7654));
+ }
+
+ @Test void testSelectBySkTimeRangeQueryGtLteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,CREATE_TIME FROM \"EMP\" WHERE CREATE_TIME > '12:00:00' "
+ + "AND CREATE_TIME <= '18:00:00'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], CREATE_TIME=[$11])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=CREATE_TIME_KEY, "
+ + "CREATE_TIME>12:00:00, CREATE_TIME<=18:00:00)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; ENAME=SCOTT; CREATE_TIME=12:12:12\n"
+ + "EMPNO=7654; ENAME=MARTIN; CREATE_TIME=12:12:56\n"
+ + "EMPNO=7900; ENAME=JAMES; CREATE_TIME=12:19:00\n"
+ + "EMPNO=7698; ENAME=BLAKE; CREATE_TIME=14:45:00\n");
+ }
+
+ @Test void testSelectBySkTimeRangeQueryNothing() {
+ sql("SELECT EMPNO,ENAME,UPSERT_TIME FROM \"EMP\" WHERE CREATE_TIME > '23:50:00' "
+ + "AND CREATE_TIME < '23:59:00'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=CREATE_TIME_KEY, "
+ + "CREATE_TIME>23:50:00, "
+ + "CREATE_TIME<23:59:00)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkTimestamp() {
+ sql("SELECT * FROM \"EMP\" WHERE UPSERT_TIME = '"
+ + expectedLocalTime("2018-09-02 12:12:56") + "'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=UPSERT_TIME_KEY, UPSERT_TIME="
+ + expectedLocalTime("2018-09-02 12:12:56") + ")])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7654));
+ }
+
+ @Test void testSelectBySkTimestampRangeQueryGteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,UPSERT_TIME FROM \"EMP\" WHERE UPSERT_TIME >= '2000-01-01 00:00:00'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=UPSERT_TIME_KEY, "
+ + "UPSERT_TIME>=2000-01-01 00:00:00)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7566; ENAME=JONES; UPSERT_TIME="
+ + expectedLocalTime("2015-03-09 22:16:30") + "\n"
+ + "EMPNO=7934; ENAME=MILLER; UPSERT_TIME="
+ + expectedLocalTime("2016-09-02 23:15:01") + "\n"
+ + "EMPNO=7844; ENAME=TURNER; UPSERT_TIME="
+ + expectedLocalTime("2017-08-17 22:01:37") + "\n"
+ + "EMPNO=7876; ENAME=ADAMS; UPSERT_TIME="
+ + expectedLocalTime("2017-08-18 23:11:06") + "\n"
+ + "EMPNO=7499; ENAME=ALLEN; UPSERT_TIME="
+ + expectedLocalTime("2018-04-09 09:00:00") + "\n"
+ + "EMPNO=7698; ENAME=BLAKE; UPSERT_TIME="
+ + expectedLocalTime("2018-06-01 14:45:00") + "\n"
+ + "EMPNO=7654; ENAME=MARTIN; UPSERT_TIME="
+ + expectedLocalTime("2018-09-02 12:12:56") + "\n"
+ + "EMPNO=7902; ENAME=FORD; UPSERT_TIME="
+ + expectedLocalTime("2019-05-29 00:00:00") + "\n"
+ + "EMPNO=7839; ENAME=KING; UPSERT_TIME="
+ + expectedLocalTime("2019-06-08 15:15:15") + "\n"
+ + "EMPNO=7788; ENAME=SCOTT; UPSERT_TIME="
+ + expectedLocalTime("2019-07-28 12:12:12") + "\n"
+ + "EMPNO=7782; ENAME=CLARK; UPSERT_TIME="
+ + expectedLocalTime("2019-09-30 02:14:56") + "\n"
+ + "EMPNO=7521; ENAME=WARD; UPSERT_TIME="
+ + expectedLocalTime("2019-11-16 10:26:40") + "\n"
+ + "EMPNO=7369; ENAME=SMITH; UPSERT_TIME="
+ + expectedLocalTime("2020-01-01 18:35:40") + "\n"
+ + "EMPNO=7900; ENAME=JAMES; UPSERT_TIME="
+ + expectedLocalTime("2020-01-02 12:19:00") + "\n");
+ }
+
+ @Test void testSelectBySkTimestampRangeQueryLteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,UPSERT_TIME FROM \"EMP\" WHERE UPSERT_TIME <= '2018-09-04 12:12:56'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=UPSERT_TIME_KEY, "
+ + "UPSERT_TIME<=2018-09-04 12:12:56)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7566; ENAME=JONES; UPSERT_TIME="
+ + expectedLocalTime("2015-03-09 22:16:30") + "\n"
+ + "EMPNO=7934; ENAME=MILLER; UPSERT_TIME="
+ + expectedLocalTime("2016-09-02 23:15:01") + "\n"
+ + "EMPNO=7844; ENAME=TURNER; UPSERT_TIME="
+ + expectedLocalTime("2017-08-17 22:01:37") + "\n"
+ + "EMPNO=7876; ENAME=ADAMS; UPSERT_TIME="
+ + expectedLocalTime("2017-08-18 23:11:06") + "\n"
+ + "EMPNO=7499; ENAME=ALLEN; UPSERT_TIME="
+ + expectedLocalTime("2018-04-09 09:00:00") + "\n"
+ + "EMPNO=7698; ENAME=BLAKE; UPSERT_TIME="
+ + expectedLocalTime("2018-06-01 14:45:00") + "\n"
+ + "EMPNO=7654; ENAME=MARTIN; UPSERT_TIME="
+ + expectedLocalTime("2018-09-02 12:12:56") + "\n");
+ }
+
+ @Test void testSelectBySkTimestampRangeQueryGtLteProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,UPSERT_TIME FROM \"EMP\" WHERE UPSERT_TIME > '"
+ + expectedLocalTime("2017-08-18 23:11:06")
+ + "' AND UPSERT_TIME <= '" + expectedLocalTime("2018-09-02 12:12:56") + "'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=UPSERT_TIME_KEY, UPSERT_TIME>"
+ + expectedLocalTime("2017-08-18 23:11:06") + ", UPSERT_TIME<="
+ + expectedLocalTime("2018-09-02 12:12:56") + ")])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7499; ENAME=ALLEN; UPSERT_TIME="
+ + expectedLocalTime("2018-04-09 09:00:00") + "\n"
+ + "EMPNO=7698; ENAME=BLAKE; UPSERT_TIME="
+ + expectedLocalTime("2018-06-01 14:45:00") + "\n"
+ + "EMPNO=7654; ENAME=MARTIN; UPSERT_TIME="
+ + expectedLocalTime("2018-09-02 12:12:56") + "\n");
+ }
+
+ @Test void testSelectBySkTimestampRangeQueryNothing() {
+ sql("SELECT EMPNO,ENAME,UPSERT_TIME FROM \"EMP\" WHERE UPSERT_TIME > '2020-08-18 13:11:06' "
+ + "AND UPSERT_TIME <= '2020-08-18 23:11:06'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], UPSERT_TIME=[$12])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=UPSERT_TIME_KEY, "
+ + "UPSERT_TIME>2020-08-18 13:11:06, UPSERT_TIME<=2020-08-18 23:11:06)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkSmallint() {
+ sql("SELECT * FROM \"EMP\" WHERE AGE = 30")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=AGE_KEY, AGE=30)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7369));
+ }
+
+ @Test void testSelectBySkSmallint2() {
+ sql("SELECT * FROM \"EMP\" WHERE AGE = 32")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=AGE_KEY, AGE=32)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7782, 7934));
+ }
+
+ @Test void testSelectBySmallintRangeQueryLtProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,AGE FROM \"EMP\" WHERE AGE < 30")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], AGE=[$3])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE<30)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7839; ENAME=KING; AGE=22\n"
+ + "EMPNO=7499; ENAME=ALLEN; AGE=24\n"
+ + "EMPNO=7654; ENAME=MARTIN; AGE=27\n"
+ + "EMPNO=7566; ENAME=JONES; AGE=28\n"
+ + "EMPNO=7902; ENAME=FORD; AGE=28\n");
+ }
+
+ @Test void testSelectBySkSmallintRangeQueryGtProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,AGE FROM \"EMP\" WHERE AGE > 30")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], AGE=[$3])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE>30)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7782; ENAME=CLARK; AGE=32\n"
+ + "EMPNO=7934; ENAME=MILLER; AGE=32\n"
+ + "EMPNO=7876; ENAME=ADAMS; AGE=35\n"
+ + "EMPNO=7698; ENAME=BLAKE; AGE=38\n"
+ + "EMPNO=7900; ENAME=JAMES; AGE=40\n"
+ + "EMPNO=7521; ENAME=WARD; AGE=41\n"
+ + "EMPNO=7788; ENAME=SCOTT; AGE=45\n"
+ + "EMPNO=7844; ENAME=TURNER; AGE=54\n");
+ }
+
+ @Test void testSelectBySkSmallintRangeQueryGtLtProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME,AGE FROM \"EMP\" WHERE AGE > 30 AND AGE < 35")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], AGE=[$3])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE>30, AGE<35)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7782; ENAME=CLARK; AGE=32\n"
+ + "EMPNO=7934; ENAME=MILLER; AGE=32\n");
+ }
+
+ @Test void testSelectBySkSmallintNothing() {
+ sql("SELECT * FROM \"EMP\" WHERE AGE = 100")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=AGE_KEY, AGE=100)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkSmallintRangeQueryNoting() {
+ sql("SELECT * FROM \"EMP\" WHERE AGE > 80")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE>80)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("");
+ }
+
+ @Test void testSelectBySkSmallintRangeQueryLtProjectSomeFieldsOrderByDesc() {
+ sql("SELECT EMPNO,ENAME,AGE FROM \"EMP\" WHERE AGE < 32 ORDER BY AGE DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], AGE=[$3])\n"
+ + " InnodbSort(sort0=[$3], dir0=[DESC])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE<32)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7369; ENAME=SMITH; AGE=30\n"
+ + "EMPNO=7902; ENAME=FORD; AGE=28\n"
+ + "EMPNO=7566; ENAME=JONES; AGE=28\n"
+ + "EMPNO=7654; ENAME=MARTIN; AGE=27\n"
+ + "EMPNO=7499; ENAME=ALLEN; AGE=24\n"
+ + "EMPNO=7839; ENAME=KING; AGE=22\n");
+ }
+
+ @Test void testSelectBySkLimitedLengthVarcharConditionNotPushDown() {
+ sql("SELECT * FROM \"EMP\" WHERE EMAIL = 'king@calcite'")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n");
+ }
+
+ @Test void testSelectBySkLimitedLengthVarcharConditionPushDown() {
+ sql("SELECT EMPNO,ENAME,EMAIL FROM \"EMP\" WHERE EMAIL > 'kkk'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1], EMAIL=[$9])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=EMAIL_KEY, EMAIL>kkk)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; ENAME=MARTIN; EMAIL=martin@calcite\n"
+ + "EMPNO=7788; ENAME=SCOTT; EMAIL=scott@calcite\n"
+ + "EMPNO=7369; ENAME=SMITH; EMAIL=smith@calcite\n"
+ + "EMPNO=7844; ENAME=TURNER; EMAIL=turner@calcite\n"
+ + "EMPNO=7521; ENAME=WARD; EMAIL=ward@calcite\n");
+ }
+
+ @Test void testSelectByMultipleSkDateTimeRangeQuery() {
+ sql("SELECT EMPNO,CREATE_DATETIME,JOB FROM \"EMP\" WHERE "
+ + "CREATE_DATETIME >= '2018-09-02 12:12:56'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], CREATE_DATETIME=[$10], JOB=[$2])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=CREATE_DATETIME_JOB_KEY, "
+ + "CREATE_DATETIME>=2018-09-02 12:12:56)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; CREATE_DATETIME=2018-09-02 12:12:56; JOB=SALESMAN\n"
+ + "EMPNO=7902; CREATE_DATETIME=2019-05-29 00:00:00; JOB=ANALYST\n"
+ + "EMPNO=7839; CREATE_DATETIME=2019-06-08 15:15:15; JOB=PRESIDENT\n"
+ + "EMPNO=7788; CREATE_DATETIME=2019-07-28 12:12:12; JOB=ANALYST\n"
+ + "EMPNO=7782; CREATE_DATETIME=2019-09-30 02:14:56; JOB=MANAGER\n"
+ + "EMPNO=7521; CREATE_DATETIME=2019-11-16 10:26:40; JOB=SALESMAN\n"
+ + "EMPNO=7369; CREATE_DATETIME=2020-01-01 18:35:40; JOB=CLERK\n"
+ + "EMPNO=7900; CREATE_DATETIME=2020-01-02 12:19:00; JOB=CLERK\n");
+ }
+
+ @Test void testSelectByMultipleSkDateTimeVarcharPointQueryCoveringIndex() {
+ sql("SELECT EMPNO,CREATE_DATETIME,JOB FROM \"EMP\" WHERE "
+ + "CREATE_DATETIME = '2018-09-02 12:12:56' AND JOB = 'SALESMAN'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], CREATE_DATETIME=[$10], JOB=[$2])\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=CREATE_DATETIME_JOB_KEY, "
+ + "CREATE_DATETIME=2018-09-02 12:12:56,JOB=SALESMAN)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7654; CREATE_DATETIME=2018-09-02 12:12:56; JOB=SALESMAN\n");
+ }
+
+ @Test void testSelectByMultipleSkTinyIntVarcharPointQueryCoveringIndex() {
+ sql("SELECT EMPNO,DEPTNO,JOB FROM \"EMP\" WHERE DEPTNO = 20 AND JOB = 'ANALYST'")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], DEPTNO=[$8], JOB=[$2])\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO=20,JOB=ANALYST)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; DEPTNO=20; JOB=ANALYST\n"
+ + "EMPNO=7902; DEPTNO=20; JOB=ANALYST\n");
+ }
+
+ @Test void testSelectByMultipleSkRangeQueryPushDownPartialCondition() {
+ sql("SELECT EMPNO,DEPTNO,ENAME FROM \"EMP\" WHERE DEPTNO = 20")
+ .explainContains("InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>=20, DEPTNO<=20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; DEPTNO=20; ENAME=SCOTT\n"
+ + "EMPNO=7902; DEPTNO=20; ENAME=FORD\n"
+ + "EMPNO=7369; DEPTNO=20; ENAME=SMITH\n"
+ + "EMPNO=7876; DEPTNO=20; ENAME=ADAMS\n"
+ + "EMPNO=7566; DEPTNO=20; ENAME=JONES\n");
+ }
+
+ @Test void testSelectByMultipleSkRangeQueryPushDownPartialCondition2() {
+ sql("SELECT EMPNO,DEPTNO,JOB FROM \"EMP\" WHERE JOB = 'SALESMAN' AND DEPTNO > 20")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7499; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7521; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7654; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7844; DEPTNO=30; JOB=SALESMAN\n");
+ }
+
+ @Test void testSelectByMultipleSkRangeQueryPushDownPartialCondition3() {
+ sql("SELECT EMPNO,DEPTNO,JOB FROM \"EMP\" WHERE JOB >= 'SALE' AND DEPTNO >= 20")
+ .explainContains("PLAN=EnumerableCalc(expr#0..12=[{inputs}], expr#13=['SALE'], "
+ + "expr#14=[>=($t2, $t13)], "
+ + "EMPNO=[$t0], DEPTNO=[$t8], JOB=[$t2], $condition=[$t14])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>=20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7499; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7521; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7654; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7844; DEPTNO=30; JOB=SALESMAN\n");
+ }
+
+ @Test void testSelectByMultipleSkBreakLeftPrefixRuleConditionNotPushDown() {
+ sql("SELECT * FROM \"EMP\" WHERE JOB = 'CLERK'")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n");
+ }
+
+ @Test void testSelectByMultipleSkTinyIntDecimalDecimalPointQueryProjectAllFields() {
+ sql("SELECT * FROM \"EMP\" WHERE DEPTNO = 30 AND SAL = 1250 AND COMM = 500.00")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=DEPTNO_SAL_COMM_KEY, "
+ + "DEPTNO=30,SAL=1250,COMM=500.00)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns(some(7521));
+ }
+
+ @Test void testSelectByMultipleSkTinyIntDecimalDecimalPointQueryProjectSomeFields() {
+ sql("SELECT EMPNO,ENAME FROM \"EMP\" WHERE DEPTNO = 30 AND SAL = 1250 AND COMM = 500.00")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ + " InnodbFilter(condition=[(SK_POINT_QUERY, index=DEPTNO_SAL_COMM_KEY, "
+ + "DEPTNO=30,SAL=1250,COMM=500.00)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])")
+ .returns("EMPNO=7521; ENAME=WARD\n");
+ }
+
+ @Test void testSelectByMultipleSkWithSameLeftPrefixChooseOneIndex() {
+ sql("SELECT EMPNO,DEPTNO,ENAME FROM \"EMP\" WHERE DEPTNO = 20 AND SAL > 30")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>=20, DEPTNO<=20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("EMPNO=7788; DEPTNO=20; ENAME=SCOTT\n"
+ + "EMPNO=7902; DEPTNO=20; ENAME=FORD\n"
+ + "EMPNO=7369; DEPTNO=20; ENAME=SMITH\n"
+ + "EMPNO=7876; DEPTNO=20; ENAME=ADAMS\n"
+ + "EMPNO=7566; DEPTNO=20; ENAME=JONES\n");
+ }
+
+ @Test void testSelectByMultipleSkForceIndexAsPrimaryKey() {
+ sql("SELECT EMPNO,DEPTNO,ENAME FROM \"EMP\"/*+ index(PRIMARY_KEY) */ WHERE "
+ + "DEPTNO = 10 AND SAL > 500")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]], forceIndex=[PRIMARY_KEY])\n")
+ .returns("EMPNO=7782; DEPTNO=10; ENAME=CLARK\n"
+ + "EMPNO=7839; DEPTNO=10; ENAME=KING\n"
+ + "EMPNO=7934; DEPTNO=10; ENAME=MILLER\n");
+ }
+
+ @Test void testSelectByMultipleSkWithSameLeftPrefixForceIndex() {
+ sql("SELECT EMPNO,DEPTNO,ENAME FROM \"EMP\"/*+ index(DEPTNO_SAL_COMM_KEY) */ WHERE "
+ + "DEPTNO = 20 AND SAL > 30")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_SAL_COMM_KEY, "
+ + "DEPTNO>=20, DEPTNO<=20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]], forceIndex=[DEPTNO_SAL_COMM_KEY])\n")
+ .returns("EMPNO=7369; DEPTNO=20; ENAME=SMITH\n"
+ + "EMPNO=7876; DEPTNO=20; ENAME=ADAMS\n"
+ + "EMPNO=7566; DEPTNO=20; ENAME=JONES\n"
+ + "EMPNO=7788; DEPTNO=20; ENAME=SCOTT\n"
+ + "EMPNO=7902; DEPTNO=20; ENAME=FORD\n");
+ }
+
+ @Test void testSelectByMultipleSkWithSameLeftPrefixForceIndexCoveringIndex() {
+ sql("SELECT EMPNO,DEPTNO,MGR FROM \"EMP\"/*+ index(DEPTNO_MGR_KEY) */ WHERE DEPTNO > 0")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], DEPTNO=[$8], MGR=[$4])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_MGR_KEY, DEPTNO>0)])\n"
+ + " InnodbTableScan(table=[[test, EMP]], forceIndex=[DEPTNO_MGR_KEY])\n")
+ .returns("EMPNO=7839; DEPTNO=10; MGR=null\n"
+ + "EMPNO=7934; DEPTNO=10; MGR=7782\n"
+ + "EMPNO=7782; DEPTNO=10; MGR=7839\n"
+ + "EMPNO=7788; DEPTNO=20; MGR=7566\n"
+ + "EMPNO=7902; DEPTNO=20; MGR=7566\n"
+ + "EMPNO=7876; DEPTNO=20; MGR=7788\n"
+ + "EMPNO=7566; DEPTNO=20; MGR=7839\n"
+ + "EMPNO=7369; DEPTNO=20; MGR=7902\n"
+ + "EMPNO=7499; DEPTNO=30; MGR=7698\n"
+ + "EMPNO=7521; DEPTNO=30; MGR=7698\n"
+ + "EMPNO=7654; DEPTNO=30; MGR=7698\n"
+ + "EMPNO=7844; DEPTNO=30; MGR=7698\n"
+ + "EMPNO=7900; DEPTNO=30; MGR=7698\n"
+ + "EMPNO=7698; DEPTNO=30; MGR=7839\n");
+ }
+
+ @Test void testGroupByFilterPushDown() {
+ sql("SELECT DEPTNO,SUM(SAL) AS TOTAL_SAL FROM EMP WHERE AGE > 30 GROUP BY DEPTNO")
+ .explainContains("PLAN=EnumerableAggregate(group=[{8}], TOTAL_SAL=[$SUM0($6)])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=AGE_KEY, AGE>30)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n")
+ .returns("DEPTNO=20; TOTAL_SAL=4100.00\n"
+ + "DEPTNO=10; TOTAL_SAL=3750.00\n"
+ + "DEPTNO=30; TOTAL_SAL=6550.00\n");
+ }
+
+ @Test void testJoinProjectAndFilterPushDown() {
+ sql("SELECT EMPNO,EMP.DEPTNO,JOB,DNAME FROM \"EMP\" JOIN \"DEPT\" "
+ + "ON EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO = 20")
+ .explainContains("EnumerableHashJoin(condition=[=($2, $3)], joinType=[inner])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], JOB=[$2], DEPTNO=[$8])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>=20, DEPTNO<=20)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " InnodbTableScan(table=[[test, DEPT]])\n")
+ .returns("EMPNO=7788; DEPTNO=20; JOB=ANALYST; DNAME=RESEARCH\n"
+ + "EMPNO=7902; DEPTNO=20; JOB=ANALYST; DNAME=RESEARCH\n"
+ + "EMPNO=7369; DEPTNO=20; JOB=CLERK; DNAME=RESEARCH\n"
+ + "EMPNO=7876; DEPTNO=20; JOB=CLERK; DNAME=RESEARCH\n"
+ + "EMPNO=7566; DEPTNO=20; JOB=MANAGER; DNAME=RESEARCH\n");
+ }
+
+ @Test void testJoinProjectAndFilterPushDown2() {
+ sql("SELECT EMPNO,EMP.DEPTNO,JOB,DNAME FROM \"EMP\" JOIN \"DEPT\" "
+ + "ON EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = 7900")
+ .explainContains("EnumerableHashJoin(condition=[=($2, $3)], joinType=[inner])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], JOB=[$2], DEPTNO=[$8])\n"
+ + " InnodbFilter(condition=[(PK_POINT_QUERY, index=PRIMARY_KEY, "
+ + "EMPNO=7900)])\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n"
+ + " InnodbToEnumerableConverter\n"
+ + " InnodbProject(DEPTNO=[$0], DNAME=[$1])\n"
+ + " InnodbTableScan(table=[[test, DEPT]])\n")
+ .returns("EMPNO=7900; DEPTNO=30; JOB=CLERK; DNAME=SALES\n");
+ }
+
+ @Test void testSelectFilterNoIndex() {
+ sql("SELECT * FROM \"EMP\" WHERE MGR = 7839")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n");
+ }
+
+ @Test void testSelectForceIndexCoveringIndex() {
+ sql("SELECT EMPNO,MGR FROM \"EMP\"/*+ index(DEPTNO_MGR_KEY) */ WHERE MGR = 7839")
+ .explainContains(" InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], MGR=[$4])\n"
+ + " InnodbTableScan(table=[[test, EMP]], forceIndex=[DEPTNO_MGR_KEY])\n");
+ }
+
+ @Test void testSelectForceIndexIncorrectIndexName() {
+ sql("SELECT * FROM \"EMP\"/*+ index(NOT_EXISTS) */ WHERE MGR = 7839")
+ .explainContains("InnodbToEnumerableConverter\n"
+ + " InnodbTableScan(table=[[test, EMP]])\n");
+ }
+
+ @Test void testSelectByMultipleSkForceIndexOrderByDesc() {
+ sql("SELECT EMPNO,DEPTNO,JOB FROM \"EMP\"/*+ index(DEPTNO_JOB_KEY) */ WHERE DEPTNO > 10 "
+ + "ORDER BY DEPTNO DESC,JOB DESC")
+ .explainContains("PLAN=InnodbToEnumerableConverter\n"
+ + " InnodbProject(EMPNO=[$0], DEPTNO=[$8], JOB=[$2])\n"
+ + " InnodbSort(sort0=[$8], sort1=[$2], dir0=[DESC], dir1=[DESC])\n"
+ + " InnodbFilter(condition=[(SK_RANGE_QUERY, index=DEPTNO_JOB_KEY, "
+ + "DEPTNO>10)])\n"
+ + " InnodbTableScan(table=[[test, EMP]], forceIndex=[DEPTNO_JOB_KEY])\n")
+ .returns("EMPNO=7844; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7654; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7521; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7499; DEPTNO=30; JOB=SALESMAN\n"
+ + "EMPNO=7698; DEPTNO=30; JOB=MANAGER\n"
+ + "EMPNO=7900; DEPTNO=30; JOB=CLERK\n"
+ + "EMPNO=7566; DEPTNO=20; JOB=MANAGER\n"
+ + "EMPNO=7876; DEPTNO=20; JOB=CLERK\n"
+ + "EMPNO=7369; DEPTNO=20; JOB=CLERK\n"
+ + "EMPNO=7902; DEPTNO=20; JOB=ANALYST\n"
+ + "EMPNO=7788; DEPTNO=20; JOB=ANALYST\n");
+ }
+
+ @Test void testSelectNotExistTable() {
+ sql("SELECT * FROM \"NOT_EXIST\"")
+ .failsAtValidation("Object 'NOT_EXIST' not found");
+ }
+
+ static List<Pair<Integer, String>> rows = Lists.newArrayList(
+ Pair.of(7369, "EMPNO=7369; ENAME=SMITH; JOB=CLERK; AGE=30; MGR=7902; "
+ + "HIREDATE=1980-12-17; SAL=800.00; COMM=null; DEPTNO=20; EMAIL=smith@calcite; "
+ + "CREATE_DATETIME=2020-01-01 18:35:40; CREATE_TIME=18:35:40; UPSERT_TIME="
+ + expectedLocalTime("2020-01-01 18:35:40")),
+ Pair.of(7499, "EMPNO=7499; ENAME=ALLEN; JOB=SALESMAN; AGE=24; MGR=7698; "
+ + "HIREDATE=1981-02-20; SAL=1600.00; COMM=300.00; DEPTNO=30; EMAIL=allen@calcite; "
+ + "CREATE_DATETIME=2018-04-09 09:00:00; CREATE_TIME=09:00:00; UPSERT_TIME="
+ + expectedLocalTime("2018-04-09 09:00:00")),
+ Pair.of(7521, "EMPNO=7521; ENAME=WARD; JOB=SALESMAN; AGE=41; MGR=7698; "
+ + "HIREDATE=1981-02-22; SAL=1250.00; COMM=500.00; DEPTNO=30; EMAIL=ward@calcite; "
+ + "CREATE_DATETIME=2019-11-16 10:26:40; CREATE_TIME=10:26:40; UPSERT_TIME="
+ + expectedLocalTime("2019-11-16 10:26:40")),
+ Pair.of(7566, "EMPNO=7566; ENAME=JONES; JOB=MANAGER; AGE=28; MGR=7839; "
+ + "HIREDATE=1981-02-04; SAL=2975.00; COMM=null; DEPTNO=20; EMAIL=jones@calcite; "
+ + "CREATE_DATETIME=2015-03-09 22:16:30; CREATE_TIME=22:16:30; UPSERT_TIME="
+ + expectedLocalTime("2015-03-09 22:16:30")),
+ Pair.of(7654, "EMPNO=7654; ENAME=MARTIN; JOB=SALESMAN; AGE=27; MGR=7698; "
+ + "HIREDATE=1981-09-28; SAL=1250.00; COMM=1400.00; DEPTNO=30; EMAIL=martin@calcite; "
+ + "CREATE_DATETIME=2018-09-02 12:12:56; CREATE_TIME=12:12:56; UPSERT_TIME="
+ + expectedLocalTime("2018-09-02 12:12:56")),
+ Pair.of(7698, "EMPNO=7698; ENAME=BLAKE; JOB=MANAGER; AGE=38; MGR=7839; "
+ + "HIREDATE=1981-01-05; SAL=2850.00; COMM=null; DEPTNO=30; EMAIL=blake@calcite; "
+ + "CREATE_DATETIME=2018-06-01 14:45:00; CREATE_TIME=14:45:00; UPSERT_TIME="
+ + expectedLocalTime("2018-06-01 14:45:00")),
+ Pair.of(7782, "EMPNO=7782; ENAME=CLARK; JOB=MANAGER; AGE=32; MGR=7839; "
+ + "HIREDATE=1981-06-09; SAL=2450.00; COMM=null; DEPTNO=10; EMAIL=null; "
+ + "CREATE_DATETIME=2019-09-30 02:14:56; CREATE_TIME=02:14:56; UPSERT_TIME="
+ + expectedLocalTime("2019-09-30 02:14:56")),
+ Pair.of(7788, "EMPNO=7788; ENAME=SCOTT; JOB=ANALYST; AGE=45; MGR=7566; "
+ + "HIREDATE=1987-04-19; SAL=3000.00; COMM=null; DEPTNO=20; EMAIL=scott@calcite; "
+ + "CREATE_DATETIME=2019-07-28 12:12:12; CREATE_TIME=12:12:12; UPSERT_TIME="
+ + expectedLocalTime("2019-07-28 12:12:12")),
+ Pair.of(7839, "EMPNO=7839; ENAME=KING; JOB=PRESIDENT; AGE=22; MGR=null; "
+ + "HIREDATE=1981-11-17; SAL=5000.00; COMM=null; DEPTNO=10; EMAIL=king@calcite; "
+ + "CREATE_DATETIME=2019-06-08 15:15:15; CREATE_TIME=null; UPSERT_TIME="
+ + expectedLocalTime("2019-06-08 15:15:15")),
+ Pair.of(7844, "EMPNO=7844; ENAME=TURNER; JOB=SALESMAN; AGE=54; MGR=7698; "
+ + "HIREDATE=1981-09-08; SAL=1500.00; COMM=0.00; DEPTNO=30; EMAIL=turner@calcite; "
+ + "CREATE_DATETIME=2017-08-17 22:01:37; CREATE_TIME=22:01:37; UPSERT_TIME="
+ + expectedLocalTime("2017-08-17 22:01:37")),
+ Pair.of(7876, "EMPNO=7876; ENAME=ADAMS; JOB=CLERK; AGE=35; MGR=7788; "
+ + "HIREDATE=1987-05-23; SAL=1100.00; COMM=null; DEPTNO=20; EMAIL=adams@calcite; "
+ + "CREATE_DATETIME=null; CREATE_TIME=23:11:06; UPSERT_TIME="
+ + expectedLocalTime("2017-08-18 23:11:06")),
+ Pair.of(7900, "EMPNO=7900; ENAME=JAMES; JOB=CLERK; AGE=40; MGR=7698; "
+ + "HIREDATE=1981-12-03; SAL=950.00; COMM=null; DEPTNO=30; EMAIL=james@calcite; "
+ + "CREATE_DATETIME=2020-01-02 12:19:00; CREATE_TIME=12:19:00; UPSERT_TIME="
+ + expectedLocalTime("2020-01-02 12:19:00")),
+ Pair.of(7902, "EMPNO=7902; ENAME=FORD; JOB=ANALYST; AGE=28; MGR=7566; "
+ + "HIREDATE=1981-12-03; SAL=3000.00; COMM=null; DEPTNO=20; EMAIL=ford@calcite; "
+ + "CREATE_DATETIME=2019-05-29 00:00:00; CREATE_TIME=null; UPSERT_TIME="
+ + expectedLocalTime("2019-05-29 00:00:00")),
+ Pair.of(7934, "EMPNO=7934; ENAME=MILLER; JOB=CLERK; AGE=32; MGR=7782; "
+ + "HIREDATE=1982-01-23; SAL=1300.00; COMM=null; DEPTNO=10; EMAIL=null; "
+ + "CREATE_DATETIME=2016-09-02 23:15:01; CREATE_TIME=23:15:01; UPSERT_TIME="
+ + expectedLocalTime("2016-09-02 23:15:01"))
+ );
+
+ static List<Pair<Integer, String>> reversedRows = rows.stream()
+ .sorted(Comparator.reverseOrder()).collect(toList());
+
+ static Map<Integer, String> empnoMap = rows.stream()
+ .collect(Collectors.toMap(Pair::getKey, Pair::getValue));
+
+ /**
+ * Whether to run this test.
+ */
+ private boolean enabled() {
+ return CalciteSystemProperty.TEST_INNODB.value();
+ }
+
+ private CalciteAssert.AssertQuery sql(String sql) {
+ return CalciteAssert.that()
+ .with(INNODB_MODEL)
+ .enable(enabled())
+ .query(sql);
+ }
+
+ Hook.Closeable closeable;
+
+ @BeforeEach
+ public void before() {
+ this.closeable =
+ Hook.SQL2REL_CONVERTER_CONFIG_BUILDER.addThread(
+ InnodbAdapterTest::assignHints);
+ }
+
+ @AfterEach
+ public void after() {
+ if (this.closeable != null) {
+ this.closeable.close();
+ this.closeable = null;
+ }
+ }
+
+ static void assignHints(Holder<SqlToRelConverter.Config> configHolder) {
+ HintStrategyTable strategies = HintStrategyTable.builder()
+ .hintStrategy("index", HintPredicates.TABLE_SCAN)
+ .build();
+ configHolder.accept(config -> config.withHintStrategyTable(strategies));
+ }
+
+ private static String expectedLocalTime(String dateTime) {
+ ZoneRules rules = ZoneId.systemDefault().getRules();
+ LocalDateTime ldt = Utils.parseDateTimeText(dateTime);
+ Instant instant = ldt.toInstant(ZoneOffset.of("+00:00"));
+ ZoneOffset standardOffset = rules.getOffset(instant);
+ OffsetDateTime odt = instant.atOffset(standardOffset);
+ return odt.toLocalDateTime().format(Utils.TIME_FORMAT_TIMESTAMP[0]);
+ }
+
+ private static String all() {
+ return String.join("\n", Pair.right(rows)) + "\n";
+ }
+
+ private static String allReversed() {
+ return String.join("\n", Pair.right(reversedRows)) + "\n";
+ }
+
+ private static String someEmpnoGt(int empno) {
+ return some(rows.stream().map(Pair::getKey).filter(i -> i > empno).collect(toList()));
+ }
+
+ private static String someEmpnoGte(int empno) {
+ return some(rows.stream().map(Pair::getKey).filter(i -> i >= empno).collect(toList()));
+ }
+
+ private static String someEmpnoLt(int empno) {
+ return some(rows.stream().map(Pair::getKey).filter(i -> i < empno).collect(toList()));
+ }
+
+ private static String someEmpnoLte(int empno) {
+ return some(rows.stream().map(Pair::getKey).filter(i -> i <= empno).collect(toList()));
+ }
+
+ private static String some(int... empnos) {
+ return some(Arrays.stream(empnos).boxed().collect(toList()));
+ }
+
+ private static String some(List<Integer> empnos) {
+ if (empnos == null) {
+ return "";
+ }
+ List<String> result = empnos.stream()
+ .map(empno -> empnoMap.get(empno)).collect(toList());
+ return join(result);
+ }
+
+ private static String join(List<String> empList) {
+ if (CollectionUtils.isEmpty(empList)) {
+ return "";
+ }
+ return String.join("\n", empList) + "\n";
+ }
+}
diff --git a/innodb/src/test/resources/README.md b/innodb/src/test/resources/README.md
new file mode 100644
index 0000000..90b4225
--- /dev/null
+++ b/innodb/src/test/resources/README.md
@@ -0,0 +1,44 @@
+<!--
+{% comment %}
+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.
+{% endcomment %}
+-->
+
+# How to generate binary files for test
+
+There are binary files (*.ibd) for test in `innodb/src/test/resources/data`, they are innodb data files generated by MySQL server. Unit tests are based on reading the binary files to check `innodb-adapter` functionality. Because it is too hard to generate those files manually, so we have to include the binary files in the source code.
+
+These binary files are:
+
+```
+innodb/src/test/resources/data/DEPT.ibd
+innodb/src/test/resources/data/EMP.ibd
+innodb/src/test/resources/data/test_types.ibd
+```
+
+You can recreate `DEPT.ibd` and `EMP.ibd` by the following steps.
+1. Make sure a MySQL server is running, follow the [Prerequisites](#Prerequisites). Note that MySQL version should be 5.7, 8.0 or higher.
+2. Run `mysql -u<username> -p<password> -h<hostname> -P<port> <dbname> < innodb/src/test/resources/scott.sql`.
+3. Copy `DEPT.ibd` and `EMP.ibd` from MySQL data directory (for example, `/usr/local/mysql/data/<dbname>`).
+
+You can recreate `test_types.ibd` by the following steps.
+1. Make sure a MySQL server is running, follow the [Prerequisites](#Prerequisites). Note that MySQL version should be 5.7, please do not use 8.0 since there is a limitation in `innodb-java-reader`, which does not support TEXT/BLOB yet (will be supported in the future plan).
+2. Run `mysql -u<username> -p<password> -h<hostname> -P<port> <dbname> < innodb/src/test/resources/data_types.sql`.
+3. Copy `test_types.ibd` from MySQL data directory (for example, `/usr/local/mysql/data/<dbname>`).
+
+# Prerequisites
+* `innodb_file_per_table` should set to `ON`, `innodb_file_per_table` is enabled by default in MySQL 5.6 and higher.
+* Page size should set to `16K` which is also the default value.
diff --git a/innodb/src/test/resources/data/DEPT.ibd b/innodb/src/test/resources/data/DEPT.ibd
new file mode 100644
index 0000000..0c51f7c
Binary files /dev/null and b/innodb/src/test/resources/data/DEPT.ibd differ
diff --git a/innodb/src/test/resources/data/EMP.ibd b/innodb/src/test/resources/data/EMP.ibd
new file mode 100644
index 0000000..2a3259e
Binary files /dev/null and b/innodb/src/test/resources/data/EMP.ibd differ
diff --git a/innodb/src/test/resources/data/test_types.ibd b/innodb/src/test/resources/data/test_types.ibd
new file mode 100644
index 0000000..db29fe6
Binary files /dev/null and b/innodb/src/test/resources/data/test_types.ibd differ
diff --git a/innodb/src/test/resources/data_types.sql b/innodb/src/test/resources/data_types.sql
new file mode 100644
index 0000000..e3f4d3c
--- /dev/null
+++ b/innodb/src/test/resources/data_types.sql
@@ -0,0 +1,153 @@
+# 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.
+#
+
+--
+-- Testing data file is generated in MySQL 5.7.27
+--
+
+SET @@session.time_zone = "+00:00";
+
+DROP TABLE IF EXISTS `test_types`;
+CREATE TABLE `test_types`
+(`id` int(11) NOT NULL AUTO_INCREMENT,
+`f_tinyint` TINYINT NOT NULL,
+`f_smallint` SMALLINT NOT NULL,
+`f_mediumint` MEDIUMINT NOT NULL,
+`f_int` INT(11) NOT NULL,
+`f_bigint` BIGINT(20) NOT NULL,
+`f_datetime` DATETIME NOT NULL,
+`f_timestamp` TIMESTAMP NOT NULL,
+`f_time` TIME NOT NULL,
+`f_year` YEAR NOT NULL,
+`f_date` DATE NOT NULL,
+`f_float` FLOAT NOT NULL ,
+`f_double` DOUBLE NOT NULL,
+`f_decimal1` DECIMAL(6) NOT NULL,
+`f_decimal2` DECIMAL(10, 5) NOT NULL,
+`f_decimal3` DECIMAL(12, 0) NOT NULL,
+`f_decimal4` DECIMAL(6, 3) NOT NULL,
+`f_decimal5` DECIMAL NOT NULL,
+`f_decimal6` DECIMAL(30,25),
+`f_varchar` VARCHAR(32) NOT NULL,
+`f_varchar_overflow` VARCHAR(5000) NOT NULL,
+`f_varchar_null` VARCHAR(15),
+`f_char_32` CHAR(32) NOT NULL,
+`f_char_255` CHAR(255) NOT NULL,
+`f_char_null` CHAR(1),
+`f_boolean` BOOLEAN NOT NULL,
+`f_bool` BOOL NOT NULL,
+`f_tinytext` TINYTEXT NOT NULL,
+`f_text` TEXT NOT NULL,
+`f_mediumtext` MEDIUMTEXT NOT NULL,
+`f_longtext` LONGTEXT NOT NULL,
+`f_tinyblob` TINYBLOB NOT NULL,
+`f_blob` BLOB NOT NULL,
+`f_mediumblob` MEDIUMBLOB NOT NULL,
+`f_longblob` LONGBLOB NOT NULL,
+`f_varbinary` VARBINARY(32) NOT NULL,
+`f_varbinary_overflow` VARBINARY(5000) NOT NULL,
+`f_enum` ENUM('MYSQL','Hello','world','computer') NOT NULL,
+`f_set` SET ('a','b','c','d','e','f','g','h','i','j','k','l', 'm','n','o','p','q','r','s','t','u','v','w','x', 'y','z') NOT NULL,
+PRIMARY KEY (`id`))
+ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+INSERT INTO `test_types` VALUES
+(
+1,
+100,
+10000,
+1000000,
+10000000,
+100000000000,
+'2019-10-02 10:59:59',
+'1988-11-23 22:10:08',
+'00:36:52',
+2012,
+'2020-01-29',
+0.987654321,
+1234567890.12345,
+123456.123456789,
+12345.67890,
+12345678901,
+123.10,
+12345.678,
+12345.1234567890123456789012345,
+concat(char(97+(2 % 26)), REPEAT('x', 31)),
+concat(char(97+(2 % 26)), REPEAT('データ', 300)),
+NULL,
+concat(char(97+(2 % 26)), REPEAT('данные', 2)),
+concat(char(97+(2 % 26)), REPEAT('数据', 100)),
+NULL,
+FALSE,
+TRUE,
+concat(char(97+(2 % 26)), REPEAT('Data', 50)),
+concat(char(97+(2 % 26)), REPEAT('Daten', 200)),
+concat(char(97+(2 % 26)), REPEAT('Datos', 200)),
+concat(char(97+(2 % 26)), REPEAT('Les données', 800)),
+concat(char(97+(2 % 26)), REPEAT(0x0a, 100)),
+concat(char(97+(2 % 26)), REPEAT(0x0b, 400)),
+concat(char(97+(2 % 26)), REPEAT(0x0c, 800)),
+concat(char(97+(2 % 26)), REPEAT(0x0d, 1000)),
+concat(char(97+(2 % 26)), REPEAT(0x0e, 8)),
+concat(char(97+(2 % 26)), REPEAT(0xff, 100)),
+'MYSQL',
+'z'
+);
+
+INSERT INTO `test_types` VALUES
+(
+2,
+-100,
+-10000,
+-1000000,
+-10000000,
+-9223372036854775807,
+'2255-01-01 12:12:12',
+'2020-01-01 00:00:00',
+'23:11:00',
+0000,
+'1970-01-01',
+-12345678.1234,
+-1234567890.123456,
+9.12345678,
+-567.8910,
+987654321.05,
+456.000,
+0.000,
+-0.0123456789012345678912345,
+concat(char(97+(3 % 26)), REPEAT('y', 31)),
+concat(char(97+(3 % 26)), REPEAT('データ', 300)),
+NULL,
+concat(char(97+(3 % 26)), REPEAT('данные', 2)),
+concat(char(97+(3 % 26)), REPEAT('数据', 100)),
+NULL,
+FALSE,
+TRUE,
+concat(char(97+(3 % 26)), REPEAT('Data', 50)),
+concat(char(97+(3 % 26)), REPEAT('Daten', 200)),
+concat(char(97+(3 % 26)), REPEAT('Datos', 200)),
+concat(char(97+(3 % 26)), REPEAT('Les données', 800)),
+concat(char(97+(3 % 26)), REPEAT(0x0a, 100)),
+concat(char(97+(3 % 26)), REPEAT(0x0b, 400)),
+concat(char(97+(3 % 26)), REPEAT(0x0c, 800)),
+concat(char(97+(3 % 26)), REPEAT(0x0d, 1000)),
+concat(char(97+(3 % 26)), REPEAT(0x0e, 8)),
+concat(char(97+(3 % 26)), REPEAT(0xff, 100)),
+2,
+'a,e,i,o,u'
+);
+
+# End data_types.sql
diff --git a/core/src/main/java/org/apache/calcite/util/Holder.java b/innodb/src/test/resources/model.json
similarity index 51%
copy from core/src/main/java/org/apache/calcite/util/Holder.java
copy to innodb/src/test/resources/model.json
index 504b9d4..ff68538 100644
--- a/core/src/main/java/org/apache/calcite/util/Holder.java
+++ b/innodb/src/test/resources/model.json
@@ -14,40 +14,21 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-package org.apache.calcite.util;
-
-/**
- * A mutable slot that can contain one object.
- *
- * <p>A holder is useful for implementing OUT or IN-OUT parameters.</p>
- *
- * <p>It is possible to sub-class to receive events on get or set.</p>
- *
- * @param <E> Element type
- */
-public class Holder<E> {
- private E e;
-
- /** Creates a Holder containing a given value.
- *
- * <p>Call this method from a derived constructor or via the {@link #of}
- * method. */
- protected Holder(E e) {
- this.e = e;
- }
-
- /** Sets the value. */
- public void set(E e) {
- this.e = e;
- }
-
- /** Gets the value. */
- public E get() {
- return e;
- }
-
- /** Creates a holder containing a given value. */
- public static <E> Holder<E> of(E e) {
- return new Holder<>(e);
- }
+{
+ "version": "1.0",
+ "defaultSchema": "test",
+ "schemas": [
+ {
+ "name": "test",
+ "type": "custom",
+ "factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
+ "operand": {
+ "sqlFilePath": [
+ "src/test/resources/data_types.sql",
+ "src/test/resources/scott.sql"
+ ],
+ "ibdDataFileBasePath": "src/test/resources/data"
+ }
+ }
+ ]
}
diff --git a/innodb/src/test/resources/scott.sql b/innodb/src/test/resources/scott.sql
new file mode 100644
index 0000000..feb6ea9
--- /dev/null
+++ b/innodb/src/test/resources/scott.sql
@@ -0,0 +1,78 @@
+# 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.
+#
+--
+-- Testing data file is generated in MySQL 5.7.27
+--
+
+SET @@session.time_zone = "+00:00";
+
+DROP TABLE IF EXISTS `DEPT`;
+CREATE TABLE `DEPT`(
+ `DEPTNO` TINYINT NOT NULL,
+ `DNAME` VARCHAR(50) NOT NULL,
+ `LOC` VARCHAR(20),
+ UNIQUE KEY `DEPT_PK` (`DEPTNO`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+DROP TABLE IF EXISTS `EMP`;
+CREATE TABLE `EMP`(
+ `EMPNO` INT(11) NOT NULL,
+ `ENAME` VARCHAR(100) NOT NULL,
+ `JOB` VARCHAR(15) NOT NULL,
+ `AGE` SMALLINT,
+ `MGR` BIGINT,
+ `HIREDATE` DATE,
+ `SAL` DECIMAL(8,2) NOT NULL,
+ `COMM` DECIMAL(6,2),
+ `DEPTNO` TINYINT,
+ `EMAIL` VARCHAR(100) DEFAULT NULL,
+ `CREATE_DATETIME` DATETIME,
+ `CREATE_TIME` TIME,
+ `UPSERT_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`EMPNO`),
+ KEY `ENAME_KEY` (`ENAME`),
+ KEY `HIREDATE_KEY` (`HIREDATE`),
+ KEY `CREATE_DATETIME_JOB_KEY` (`CREATE_DATETIME`, `JOB`),
+ KEY `CREATE_TIME_KEY` (`CREATE_TIME`),
+ KEY `UPSERT_TIME_KEY` (`UPSERT_TIME`),
+ KEY `DEPTNO_JOB_KEY` (`DEPTNO`, `JOB`),
+ KEY `DEPTNO_SAL_COMM_KEY` (`DEPTNO`, `SAL`, `COMM`),
+ KEY `DEPTNO_MGR_KEY` (`DEPTNO`, `MGR`),
+ KEY `AGE_KEY` (`AGE`),
+ KEY `EMAIL_KEY` (`EMAIL`(3))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+INSERT INTO `DEPT` VALUES(10,'ACCOUNTING','NEW YORK');
+INSERT INTO `DEPT` VALUES(20,'RESEARCH','DALLAS');
+INSERT INTO `DEPT` VALUES(30,'SALES','CHICAGO');
+INSERT INTO `DEPT` VALUES(40,'OPERATIONS','BOSTON');
+
+INSERT INTO `EMP` VALUES(7369,'SMITH','CLERK',30,7902,'1980-12-17',800,NULL,20,'smith@calcite','2020-01-01 18:35:40','18:35:40','2020-01-01 18:35:40');
+INSERT INTO `EMP` VALUES(7499,'ALLEN','SALESMAN',24,7698,'1981-02-20',1600,300,30,'allen@calcite','2018-04-09 09:00:00','09:00:00','2018-04-09 09:00:00');
+INSERT INTO `EMP` VALUES(7521,'WARD','SALESMAN',41,7698,'1981-02-22',1250,500,30,'ward@calcite','2019-11-16 10:26:40','10:26:40','2019-11-16 10:26:40');
+INSERT INTO `EMP` VALUES(7566,'JONES','MANAGER',28,7839,'1981-02-04',2975,NULL,20,'jones@calcite','2015-03-09 22:16:30','22:16:30','2015-03-09 22:16:30');
+INSERT INTO `EMP` VALUES(7654,'MARTIN','SALESMAN',27,7698,'1981-09-28',1250,1400,30,'martin@calcite','2018-09-02 12:12:56','12:12:56','2018-09-02 12:12:56');
+INSERT INTO `EMP` VALUES(7698,'BLAKE','MANAGER',38,7839,'1981-01-05',2850,NULL,30,'blake@calcite','2018-06-01 14:45:00','14:45:00','2018-06-01 14:45:00');
+INSERT INTO `EMP` VALUES(7782,'CLARK','MANAGER',32,7839,'1981-06-09',2450,NULL,10,NULL,'2019-09-30 02:14:56','02:14:56','2019-09-30 02:14:56');
+INSERT INTO `EMP` VALUES(7788,'SCOTT','ANALYST',45,7566,'1987-04-19',3000,NULL,20,'scott@calcite','2019-07-28 12:12:12','12:12:12','2019-07-28 12:12:12');
+INSERT INTO `EMP` VALUES(7839,'KING','PRESIDENT',22,NULL,'1981-11-17',5000,NULL,10,'king@calcite','2019-06-08 15:15:15',NULL,'2019-06-08 15:15:15');
+INSERT INTO `EMP` VALUES(7844,'TURNER','SALESMAN',54,7698,'1981-09-08',1500,0,30,'turner@calcite','2017-08-17 22:01:37','22:01:37','2017-08-17 22:01:37');
+INSERT INTO `EMP` VALUES(7876,'ADAMS','CLERK',35,7788,'1987-05-23',1100,NULL,20,'adams@calcite',NULL,'23:11:06','2017-08-18 23:11:06');
+INSERT INTO `EMP` VALUES(7900,'JAMES','CLERK',40,7698,'1981-12-03',950,NULL,30,'james@calcite','2020-01-02 12:19:00','12:19:00','2020-01-02 12:19:00');
+INSERT INTO `EMP` VALUES(7902,'FORD','ANALYST',28,7566,'1981-12-03',3000,NULL,20,'ford@calcite','2019-05-29 00:00:00',NULL,'2019-05-29 00:00:00');
+INSERT INTO `EMP` VALUES(7934,'MILLER','CLERK',32,7782,'1982-01-23',1300,NULL,10,NULL,'2016-09-02 23:15:01','23:15:01','2016-09-02 23:15:01');
+
+# End scott.sql
diff --git a/settings.gradle.kts b/settings.gradle.kts
index 3bf9c9f..127f0c5 100644
--- a/settings.gradle.kts
+++ b/settings.gradle.kts
@@ -58,6 +58,7 @@ include(
"example:function",
"file",
"geode",
+ "innodb",
"kafka",
"linq4j",
"mongodb",
diff --git a/site/_docs/adapter.md b/site/_docs/adapter.md
index 00e4ce3..247c214 100644
--- a/site/_docs/adapter.md
+++ b/site/_docs/adapter.md
@@ -34,6 +34,7 @@ presenting the data as tables within a schema.
(<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/elasticsearch/package-summary.html">calcite-elasticsearch</a>)
* [File adapter](file_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/file/package-summary.html">calcite-file</a>)
* [Geode adapter](geode_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/geode/package-summary.html">calcite-geode</a>)
+* [InnoDB adapter](innodb_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/innodb/package-summary.html">calcite-innodb</a>)
* JDBC adapter (part of <a href="{{ site.apiRoot }}/org/apache/calcite/adapter/jdbc/package-summary.html">calcite-core</a>)
* MongoDB adapter (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/mongodb/package-summary.html">calcite-mongodb</a>)
* [OS adapter](os_adapter.html) (<a href="{{ site.apiRoot }}/org/apache/calcite/adapter/os/package-summary.html">calcite-os</a>)
diff --git a/site/_docs/innodb_adapter.md b/site/_docs/innodb_adapter.md
new file mode 100644
index 0000000..9569e50
--- /dev/null
+++ b/site/_docs/innodb_adapter.md
@@ -0,0 +1,381 @@
+---
+layout: docs
+title: InnoDB adapter
+permalink: /docs/innodb_adapter.html
+
+---
+
+<!--
+{% comment %}
+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.
+{% endcomment %}
+-->
+
+[MySQL](https://www.mysql.com/) is the most popular Open Source SQL
+database management system, is developed, distributed, and supported
+by Oracle Corporation. InnoDB is a general-purpose storage engine that
+balances high reliability and high performance in MySQL, since 5.6
+InnoDB has become the default MySQL storage engine.
+
+Calcite's InnoDB adapter allows you to query the data based on InnoDB
+data files directly as illustrated below, data files are also known as
+`.ibd` files. It leverages the
+[innodb-java-reader](https://github.com/alibaba/innodb-java-reader). This
+adapter is different from JDBC adapter which maps a schema in a JDBC
+data source and requires a MySQL server to serve response.
+
+With `.ibd` files and the corresponding DDLs, the InnoDB adapter acts
+as a simple "MySQL server": it accepts SQL queries and attempts to
+compile each query based on InnoDB file access APIs provided by
+[innodb-java-reader](https://github.com/alibaba/innodb-java-reader).
+It projects, filters and sorts directly in the InnoDB data files where
+possible.
+
+{% highlight json %}
+ SQL query
+ | |
+ / \
+ --------- ---------
+ | |
+ v v
++-------------------------+ +------------------------+
+| MySQL Server | | Calcite InnoDB Adapter |
+| | +------------------------+
+| +---------------------+ | +--------------------+
+| |InnoDB Storage Engine| | | innodb-java-reader |
+| +---------------------+ | +--------------------+
++-------------------------+
+
+-------------------- File System --------------------
+
+ +------------+ +-----+
+ | .ibd files | ... | | InnoDB Data files
+ +------------+ +-----+
+
+{% endhighlight %}
+
+What's more, with DDL statements, the adapter is "index aware". It
+leverages rules to choose the appropriate index to scan, for example,
+using primary key or secondary keys to look up data, then it tries to
+push down some conditions into storage engine. The adapter also
+supports hints, so that users can tell the optimizer to use a
+particular index.
+
+A basic example of a model file is given below, this schema reads from
+a MySQL "scott" database:
+
+{% highlight json %}
+{
+ "version": "1.0",
+ "defaultSchema": "scott",
+ "schemas": [
+ {
+ "name": "scott",
+ "type": "custom",
+ "factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
+ "operand": {
+ "sqlFilePath": [ "/path/scott.sql" ],
+ "ibdDataFileBasePath": "/usr/local/mysql/data/scott"
+ }
+ }
+ ]
+}
+{% endhighlight %}
+
+`sqlFilePath` is a list of DDL files, you can generate table
+definitions by executing `mysqldump -d -u<username> -p<password> -h
+<hostname> <dbname>` in command-line.
+
+The file content of `/path/scott.sql` is as follows:
+
+{% highlight bash %}
+CREATE TABLE `DEPT`(
+ `DEPTNO` TINYINT NOT NULL,
+ `DNAME` VARCHAR(50) NOT NULL,
+ `LOC` VARCHAR(20),
+ UNIQUE KEY `DEPT_PK` (`DEPTNO`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE `EMP`(
+ `EMPNO` INT(11) NOT NULL,
+ `ENAME` VARCHAR(100) NOT NULL,
+ `JOB` VARCHAR(15) NOT NULL,
+ `AGE` SMALLINT,
+ `MGR` BIGINT,
+ `HIREDATE` DATE,
+ `SAL` DECIMAL(8,2) NOT NULL,
+ `COMM` DECIMAL(6,2),
+ `DEPTNO` TINYINT,
+ `EMAIL` VARCHAR(100) DEFAULT NULL,
+ `CREATE_DATETIME` DATETIME,
+ `CREATE_TIME` TIME,
+ `UPSERT_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`EMPNO`),
+ KEY `ENAME_KEY` (`ENAME`),
+ KEY `HIREDATE_KEY` (`HIREDATE`),
+ KEY `CREATE_DATETIME_JOB_KEY` (`CREATE_DATETIME`, `JOB`),
+ KEY `CREATE_TIME_KEY` (`CREATE_TIME`),
+ KEY `UPSERT_TIME_KEY` (`UPSERT_TIME`),
+ KEY `DEPTNO_JOB_KEY` (`DEPTNO`, `JOB`),
+ KEY `DEPTNO_SAL_COMM_KEY` (`DEPTNO`, `SAL`, `COMM`),
+ KEY `DEPTNO_MGR_KEY` (`DEPTNO`, `MGR`),
+ KEY `AGE_KEY` (`AGE`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+{% endhighlight %}
+
+ibdDataFileBasePath is the parent file path of `.ibd` files.
+
+Assuming the model file is stored as `model.json`, you can connect to
+InnoDB data file to perform query via
+[sqlline](https://github.com/julianhyde/sqlline) as follows:
+
+{% highlight bash %}
+sqlline> !connect jdbc:calcite:model=model.json admin admin
+{% endhighlight %}
+
+We can query all employees by writing standard SQL:
+
+{% highlight bash %}
+sqlline> select empno, ename, job, age, mgr from "EMP";
++-------+--------+-----------+-----+------+
+| EMPNO | ENAME | JOB | AGE | MGR |
++-------+--------+-----------+-----+------+
+| 7369 | SMITH | CLERK | 30 | 7902 |
+| 7499 | ALLEN | SALESMAN | 24 | 7698 |
+| 7521 | WARD | SALESMAN | 41 | 7698 |
+| 7566 | JONES | MANAGER | 28 | 7839 |
+| 7654 | MARTIN | SALESMAN | 27 | 7698 |
+| 7698 | BLAKE | MANAGER | 38 | 7839 |
+| 7782 | CLARK | MANAGER | 32 | 7839 |
+| 7788 | SCOTT | ANALYST | 45 | 7566 |
+| 7839 | KING | PRESIDENT | 22 | null |
+| 7844 | TURNER | SALESMAN | 54 | 7698 |
+| 7876 | ADAMS | CLERK | 35 | 7788 |
+| 7900 | JAMES | CLERK | 40 | 7698 |
+| 7902 | FORD | ANALYST | 28 | 7566 |
+| 7934 | MILLER | CLERK | 32 | 7782 |
++-------+--------+-----------+-----+------+
+{% endhighlight %}
+
+While executing this query, the InnoDB adapter scans the InnoDB data
+file `EMP.ibd` using primary key, also known as clustering B+ tree
+index in MySQL, and is able to push down projection to underlying
+storage engine. Projection can reduce the size of data fetched from
+the storage engine.
+
+We can look up one employee by filtering. The InnoDB adapter retrieves
+all indexes through DDL file provided in `model.json`.
+
+{% highlight bash %}
+sqlline> select empno, ename, job, age, mgr from "EMP" where empno = 7782;
++-------+-------+---------+-----+------+
+| EMPNO | ENAME | JOB | AGE | MGR |
++-------+-------+---------+-----+------+
+| 7782 | CLARK | MANAGER | 32 | 7839 |
++-------+-------+---------+-----+------+
+{% endhighlight %}
+
+The InnoDB adapter recognizes that `empno` is the primary key and
+performs a point-lookup by using the clustering index instead of a
+full table scan.
+
+We can also do range queries on the primary key:
+
+{% highlight bash %}
+sqlline> select empno, ename, job, age, mgr from "EMP" where empno > 7782 and empno < 7900;
+{% endhighlight %}
+
+Note that such query with acceptable range is usually efficient in
+MySQL with InnoDB storage engine, because for clustering B+ tree
+index, records close in index are close in data file, which is good
+for scanning.
+
+We can look up employee by secondary key. For example, in the
+following query, the filtering condition is a field `ename` of type
+`VARCHAR`.
+
+{% highlight bash %}
+sqlline> select empno, ename, job, age, mgr from "EMP" where ename = 'smith';
++-------+-------+-------+-----+------+
+| EMPNO | ENAME | JOB | AGE | MGR |
++-------+-------+-------+-----+------+
+| 7369 | SMITH | CLERK | 30 | 7902 |
++-------+-------+-------+-----+------+
+{% endhighlight %}
+
+The InnoDB adapter works well on almost all the commonly used data
+types in MySQL, for more information on supported data types, please
+refer to
+[innodb-java-reader](https://github.com/alibaba/innodb-java-reader#3-features).
+
+We can query by composite key. For example, given secondary index of
+`DEPTNO_MGR_KEY`.
+
+{% highlight bash %}
+sqlline> select empno, ename, job, age, mgr from "EMP" where deptno = 20 and mgr = 7566;
++-------+-------+---------+-----+------+
+| EMPNO | ENAME | JOB | AGE | MGR |
++-------+-------+---------+-----+------+
+| 7788 | SCOTT | ANALYST | 45 | 7566 |
+| 7902 | FORD | ANALYST | 28 | 7566 |
++-------+-------+---------+-----+------+
+{% endhighlight %}
+
+The InnoDB adapter leverages the matched key `DEPTNO_MGR_KEY` to push
+down filtering condition of `deptno = 20 and mgr = 7566`.
+
+In some cases, only part of the conditions can be pushed down since
+there is a limitation in the underlying storage engine API; other
+conditions remain in the rest of the plan. Given the following SQL,
+only `deptno = 20` is pushed down.
+
+{% highlight bash %}
+select empno, ename, job, age, mgr from "EMP" where deptno = 20 and upsert_time > '2018-01-01 00:00:00';
+{% endhighlight %}
+
+`innodb-java-reader` only supports range queries with lower and upper
+bound using an index, not fully `Index Condition Pushdown (ICP)`. The
+storage engine returns a range of rows and Calcite evaluates the rest
+of `WHERE` condition from the rows fetched.
+
+For the following SQL, there are multiple indexes satisfying the
+left-prefix index rule: the possible indexes are `DEPTNO_JOB_KEY`,
+`DEPTNO_SAL_COMM_KEY` and `DEPTNO_MGR_KEY`. The InnoDB adapter chooses
+one of them according to the ordinal defined in DDL; only the `deptno
+= 20` condition is pushed down, leaving the rest of `WHERE` condition
+handled by Calcite's built-in execution engine.
+
+{% highlight bash %}
+sqlline> select empno, deptno, sal from "EMP" where deptno = 20 and sal > 2000;
++-------+--------+---------+
+| EMPNO | DEPTNO | SAL |
++-------+--------+---------+
+| 7788 | 20 | 3000.00 |
+| 7902 | 20 | 3000.00 |
+| 7566 | 20 | 2975.00 |
++-------+--------+---------+
+{% endhighlight %}
+
+Accessing rows through secondary key requires scanning by secondary
+index and retrieving records back to clustering index in InnoDB, for a
+"big" scan, that would introduce many random I/O operations, so
+performance is usually not good enough. Note that the query above can
+be more performant by using `EPTNO_SAL_COMM_KEY` index, because
+covering index does not need to retrieve back to clustering index. We
+can force using `DEPTNO_SAL_COMM_KEY` index by hint as follows.
+
+{% highlight bash %}
+sqlline> select empno, deptno, sal from "EMP"/*+ index(DEPTNO_SAL_COMM_KEY) */ where deptno = 20 and sal > 2000;
+{% endhighlight %}
+
+Hint can be configured in `SqlToRelConverter`, to enable hint, you
+should register `index` HintStrategy on `TableScan` in
+`SqlToRelConverter.ConfigBuilder`. Index hint takes effect on the base
+`TableScan` relational node, if there are conditions matching the
+index, index condition can be pushed down as well. For the following SQL,
+although none of the indexes can be used, but by leveraging covering
+index, the performance is better than full table scan, we can force to
+use `DEPTNO_MGR_KEY` to scan in secondary index.
+
+{% highlight bash %}
+sqlline> select empno,mgr from "EMP"/*+ index(DEPTNO_MGR_KEY) */ where mgr = 7839;
+{% endhighlight %}
+
+Ordering can be pushed down if it matches the natural collation of the index used.
+
+{% highlight bash %}
+sqlline> select deptno,ename,hiredate from "EMP" where hiredate < '2020-01-01' order by hiredate desc;
++--------+--------+------------+
+| DEPTNO | ENAME | HIREDATE |
++--------+--------+------------+
+| 20 | ADAMS | 1987-05-23 |
+| 20 | SCOTT | 1987-04-19 |
+| 10 | MILLER | 1982-01-23 |
+| 20 | FORD | 1981-12-03 |
+| 30 | JAMES | 1981-12-03 |
+| 10 | KING | 1981-11-17 |
+| 30 | MARTIN | 1981-09-28 |
+| 30 | TURNER | 1981-09-08 |
+| 10 | CLARK | 1981-06-09 |
+| 30 | WARD | 1981-02-22 |
+| 30 | ALLEN | 1981-02-20 |
+| 20 | JONES | 1981-02-04 |
+| 30 | BLAKE | 1981-01-05 |
+| 20 | SMITH | 1980-12-17 |
++--------+--------+------------+
+{% endhighlight %}
+
+## About time zone
+
+MySQL converts `TIMESTAMP` values from the current time zone to UTC
+for storage, and back from UTC to the current time zone for
+retrieval. So in this adapter, MySQL's `TIMESTAMP` is mapped to
+Calcite's `TIMESTAMP WITH LOCAL TIME ZONE`. The per-session time zone
+setting can be configured in Calcite connection config `timeZone`,
+which tells the MySQL server which time zone the `TIMESTAMP` value was
+in. Currently the InnoDB adapter cannot pass the property to the
+underlying storage engine, but you can specify `timeZone` in
+`model.json` like below. Note that you only need to specify the
+property if `timeZone` is set in connection config and it is different
+from system default time zone where the InnoDB adapter runs.
+
+{% highlight bash %}
+{
+ "version": "1.0",
+ "defaultSchema": "test",
+ "schemas": [
+ {
+ "name": "test",
+ "type": "custom",
+ "factory": "org.apache.calcite.adapter.innodb.InnodbSchemaFactory",
+ "operand": {
+ "sqlFilePath": ["src/test/resources/data_types.sql"],
+ "ibdDataFileBasePath": "src/test/resources/data",
+ "timeZone": "America/Los_Angeles"
+ }
+ }
+ ]
+}
+{% endhighlight %}
+
+## Limitations
+
+`innodb-java-reader` has some prerequisites for `.ibd` files.
+
+* The `COMPACT` and `DYNAMIC` row formats are supported. `COMPRESSED`,
+ `REDUNDANT` and `FIXED` are not supported.
+* `innodb_file_per_table` should set to `ON`, `innodb_file_per_table`
+ is enabled by default in MySQL 5.6 and higher.
+* Page size should set to `16K` which is also the default value.
+
+For more information, please refer to
+[prerequisites](https://github.com/alibaba/innodb-java-reader#2-prerequisites).
+
+In terms of data consistency, you can think of the adapter as a simple
+MySQL server, with the ability to query directly through InnoDB data
+file, dump data by offloading from MySQL. If pages are not flushed
+from InnoDB Buffer Pool to disk, then the result may be inconsistent
+(the LSN in `.ibd` file might smaller than in-memory pages). InnoDB
+leverages write ahead log in terms of performance, so there is no
+command available to flush all dirty pages. Only internal mechanism
+manages when and where to persist pages to disk, like Page Cleaner
+thread, adaptive flushing, etc.
+
+Currently the InnoDB adapter is not aware of row count and cardinality
+of a `.ibd` data file, so it relies on simple rules to perform
+optimization. If, in future, the underlying storage engine can provide
+such metrics and metadata, this could be integrated into Calcite by
+leveraging cost based optimization.