This is an automated email from the ASF dual-hosted git repository.
volodymyr pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
The following commit(s) were added to refs/heads/master by this push:
new f86d5f4 DRILL-7771: Order by and limit transactions in RDBMS cannot
be pushdown
f86d5f4 is described below
commit f86d5f4f4a5f0183e5169e0c79be994c4eaf9719
Author: Volodymyr Vysotskyi <[email protected]>
AuthorDate: Tue Dec 1 22:29:47 2020 +0200
DRILL-7771: Order by and limit transactions in RDBMS cannot be pushdown
---
.../drill/exec/store/jdbc/DrillJdbcConvention.java | 15 +++++--
.../drill/exec/store/jdbc/DrillJdbcRuleBase.java | 44 ++++++++++++++++---
.../drill/exec/store/jdbc/DrillJdbcSort.java | 49 ++++++++++++++++++++++
.../exec/store/jdbc/TestJdbcPluginWithMySQLIT.java | 37 +++++++++++++++-
4 files changed, 135 insertions(+), 10 deletions(-)
diff --git
a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcConvention.java
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcConvention.java
index d8ea6bf..0f416c9 100644
---
a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcConvention.java
+++
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcConvention.java
@@ -25,12 +25,15 @@ import org.apache.calcite.adapter.jdbc.JdbcConvention;
import org.apache.calcite.adapter.jdbc.JdbcRules;
import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcFilterRule;
import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProjectRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcSortRule;
import org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverterRule;
import org.apache.calcite.linq4j.tree.ConstantUntypedNull;
+import org.apache.calcite.plan.Convention;
import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelOptRule;
import org.apache.calcite.sql.SqlDialect;
import org.apache.drill.exec.planner.RuleInstance;
+import org.apache.drill.exec.planner.logical.DrillRel;
import org.apache.drill.exec.planner.logical.DrillRelFactories;
import org.apache.drill.shaded.guava.com.google.common.collect.ImmutableSet;
@@ -43,7 +46,7 @@ class DrillJdbcConvention extends JdbcConvention {
* Unwanted Calcite's JdbcRules are filtered out using this set
*/
private static final Set<Class<? extends RelOptRule>> EXCLUDED_CALCITE_RULES
= ImmutableSet.of(
- JdbcToEnumerableConverterRule.class, JdbcFilterRule.class,
JdbcProjectRule.class);
+ JdbcToEnumerableConverterRule.class, JdbcFilterRule.class,
JdbcProjectRule.class, JdbcSortRule.class);
private final ImmutableSet<RelOptRule> rules;
private final JdbcStoragePlugin plugin;
@@ -58,8 +61,14 @@ class DrillJdbcConvention extends JdbcConvention {
.addAll(calciteJdbcRules)
.add(JdbcIntermediatePrelConverterRule.INSTANCE)
.add(new JdbcDrelConverterRule(this))
- .add(new DrillJdbcRuleBase.DrillJdbcProjectRule(this))
- .add(new DrillJdbcRuleBase.DrillJdbcFilterRule(this))
+ .add(new DrillJdbcRuleBase.DrillJdbcProjectRule(Convention.NONE, this))
+ .add(new
DrillJdbcRuleBase.DrillJdbcProjectRule(DrillRel.DRILL_LOGICAL, this))
+ .add(new DrillJdbcRuleBase.DrillJdbcFilterRule(Convention.NONE, this))
+ .add(new DrillJdbcRuleBase.DrillJdbcFilterRule(DrillRel.DRILL_LOGICAL,
this))
+ .add(new DrillJdbcRuleBase.DrillJdbcSortRule(Convention.NONE, this))
+ .add(new DrillJdbcRuleBase.DrillJdbcSortRule(DrillRel.DRILL_LOGICAL,
this))
+ .add(new DrillJdbcRuleBase.DrillJdbcLimitRule(Convention.NONE, this))
+ .add(new DrillJdbcRuleBase.DrillJdbcLimitRule(DrillRel.DRILL_LOGICAL,
this))
.add(RuleInstance.FILTER_SET_OP_TRANSPOSE_RULE)
.add(RuleInstance.PROJECT_REMOVE_RULE)
.build();
diff --git
a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcRuleBase.java
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcRuleBase.java
index c0b28bf..dca8a99 100644
---
a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcRuleBase.java
+++
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcRuleBase.java
@@ -23,15 +23,17 @@ import java.util.function.Predicate;
import org.apache.calcite.adapter.jdbc.JdbcConvention;
import org.apache.calcite.adapter.jdbc.JdbcRules;
-import org.apache.calcite.plan.Convention;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.plan.RelTrait;
+import org.apache.calcite.rel.RelCollations;
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.rex.RexNode;
+import org.apache.drill.exec.planner.common.DrillLimitRelBase;
import org.apache.drill.shaded.guava.com.google.common.cache.CacheBuilder;
import org.apache.drill.shaded.guava.com.google.common.cache.CacheLoader;
import org.apache.drill.shaded.guava.com.google.common.cache.LoadingCache;
@@ -58,8 +60,8 @@ abstract class DrillJdbcRuleBase extends ConverterRule {
static class DrillJdbcProjectRule extends DrillJdbcRuleBase {
- DrillJdbcProjectRule(JdbcConvention out) {
- super(LogicalProject.class, Convention.NONE, out,
"DrillJdbcProjectRule");
+ DrillJdbcProjectRule(RelTrait in, JdbcConvention out) {
+ super(LogicalProject.class, in, out, "DrillJdbcProjectRule");
}
public RelNode convert(RelNode rel) {
@@ -89,8 +91,8 @@ abstract class DrillJdbcRuleBase extends ConverterRule {
static class DrillJdbcFilterRule extends DrillJdbcRuleBase {
- DrillJdbcFilterRule(JdbcConvention out) {
- super(LogicalFilter.class, Convention.NONE, out, "DrillJdbcFilterRule");
+ DrillJdbcFilterRule(RelTrait in, JdbcConvention out) {
+ super(LogicalFilter.class, in, out, "DrillJdbcFilterRule");
}
public RelNode convert(RelNode rel) {
@@ -117,4 +119,36 @@ abstract class DrillJdbcRuleBase extends ConverterRule {
}
}
}
+
+ static class DrillJdbcSortRule extends DrillJdbcRuleBase {
+
+ DrillJdbcSortRule(RelTrait in, JdbcConvention out) {
+ super(Sort.class, in, out, "DrillJdbcSortRule");
+ }
+
+ @Override
+ public RelNode convert(RelNode rel) {
+ Sort sort = (Sort) rel;
+
+ return new DrillJdbcSort(sort.getCluster(),
sort.getTraitSet().replace(this.out),
+ convert(sort.getInput(),
sort.getInput().getTraitSet().replace(this.out).simplify()),
+ sort.collation, sort.offset, sort.fetch);
+ }
+ }
+
+ static class DrillJdbcLimitRule extends DrillJdbcRuleBase {
+
+ DrillJdbcLimitRule(RelTrait in, JdbcConvention out) {
+ super(DrillLimitRelBase.class, in, out, "DrillJdbcLimitRule");
+ }
+
+ @Override
+ public RelNode convert(RelNode rel) {
+ DrillLimitRelBase limit = (DrillLimitRelBase) rel;
+
+ return new DrillJdbcSort(limit.getCluster(),
limit.getTraitSet().plus(RelCollations.EMPTY).replace(this.out).simplify(),
+ convert(limit.getInput(),
limit.getInput().getTraitSet().replace(this.out).simplify()),
+ RelCollations.EMPTY, limit.getOffset(), limit.getFetch());
+ }
+ }
}
diff --git
a/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcSort.java
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcSort.java
new file mode 100644
index 0000000..447f19d
--- /dev/null
+++
b/contrib/storage-jdbc/src/main/java/org/apache/drill/exec/store/jdbc/DrillJdbcSort.java
@@ -0,0 +1,49 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.drill.exec.store.jdbc;
+
+import org.apache.calcite.adapter.jdbc.JdbcRules;
+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.metadata.RelMetadataQuery;
+import org.apache.calcite.rex.RexNode;
+import org.apache.drill.exec.planner.cost.DrillCostBase;
+
+public class DrillJdbcSort extends JdbcRules.JdbcSort {
+ public DrillJdbcSort(RelOptCluster cluster, RelTraitSet traitSet, RelNode
input, RelCollation collation, RexNode offset, RexNode fetch) {
+ super(cluster, traitSet, input, collation, offset, fetch);
+ }
+
+ @Override
+ public RelOptCost computeSelfCost(RelOptPlanner planner, RelMetadataQuery
mq) {
+ if (collation.getFieldCollations().isEmpty()) {
+ // The case when sort node represents a regular limit without actual
sort.
+ // Drill separates limit from sort, and they have different formulas for
cost calculation.
+ // The cost for the case of the JDBC limit operator should correspond to
the cost of the Drill's one.
+ double numRows = mq.getRowCount(this);
+ double cpuCost = DrillCostBase.COMPARE_CPU_COST * numRows;
+ DrillCostBase.DrillCostFactory costFactory =
(DrillCostBase.DrillCostFactory) planner.getCostFactory();
+ return costFactory.makeCost(numRows, cpuCost, 0, 0);
+ }
+ return super.computeSelfCost(planner, mq);
+ }
+}
diff --git
a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
index 73b8f1c..6b7ae90 100644
---
a/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
+++
b/contrib/storage-jdbc/src/test/java/org/apache/drill/exec/store/jdbc/TestJdbcPluginWithMySQLIT.java
@@ -324,11 +324,44 @@ public class TestJdbcPluginWithMySQLIT extends
ClusterTest {
@Test
public void testLimitPushDown() throws Exception {
- String query = "select person_id from mysql.`drill_mysql_test`.person
limit 10";
+ String query = "select person_id, first_name, last_name from
mysql.`drill_mysql_test`.person limit 100";
queryBuilder()
.sql(query)
.planMatcher()
- .include("Jdbc\\(.*LIMIT 10")
+ .include("Jdbc\\(.*LIMIT 100")
+ .exclude("Limit\\(")
+ .match();
+ }
+
+ @Test
+ public void testLimitPushDownWithOrderBy() throws Exception {
+ String query = "select person_id from mysql.`drill_mysql_test`.person
order by first_name limit 100";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*ORDER BY `first_name`.*LIMIT 100")
+ .exclude("Limit\\(")
+ .match();
+ }
+
+ @Test
+ public void testLimitPushDownWithOffset() throws Exception {
+ String query = "select person_id, first_name from
mysql.`drill_mysql_test`.person limit 100 offset 10";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*LIMIT 100 OFFSET 10")
+ .exclude("Limit\\(")
+ .match();
+ }
+
+ @Test
+ public void testLimitPushDownWithConvertFromJson() throws Exception {
+ String query = "select convert_fromJSON(first_name)['ppid'] from
mysql.`drill_mysql_test`.person LIMIT 100";
+ queryBuilder()
+ .sql(query)
+ .planMatcher()
+ .include("Jdbc\\(.*LIMIT 100")
.exclude("Limit\\(")
.match();
}
