This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new 0a8bf83 Bad plan for table-lookup-lookup join with filter on first
lookup and outer limit (#9773)
0a8bf83 is described below
commit 0a8bf83bc589fb899a7d96e426682708c636f0eb
Author: Maytas Monsereenusorn <[email protected]>
AuthorDate: Thu May 14 13:56:40 2020 -1000
Bad plan for table-lookup-lookup join with filter on first lookup and outer
limit (#9773)
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* Bad plan for table-lookup-lookup join with filter on first lookup and
outer limit
* address comments
* address comments
* fix checkstyle
* address comments
* address comments
---
docs/querying/datasource.md | 5 +
docs/querying/sql.md | 11 +-
.../apache/druid/sql/calcite/planner/Rules.java | 6 +-
.../druid/sql/calcite/rule/DruidJoinRule.java | 21 +-
.../rule/FilterJoinExcludePushToChildRule.java | 295 +++++++++++++++
.../apache/druid/sql/calcite/CalciteQueryTest.java | 403 ++++++++++++++++++++-
6 files changed, 730 insertions(+), 11 deletions(-)
diff --git a/docs/querying/datasource.md b/docs/querying/datasource.md
index c522f5f..66cc905 100644
--- a/docs/querying/datasource.md
+++ b/docs/querying/datasource.md
@@ -332,12 +332,17 @@ perform best if `d.field` is a string.
4. As of Druid {{DRUIDVERSION}}, the join operator must evaluate the condition
for each row. In the future, we expect
to implement both early and deferred condition evaluation, which we expect to
improve performance considerably for
common use cases.
+5. Currently, Druid does not support pushing down predicates (condition and
filter) past a Join (i.e. into
+Join's children). Druid only supports pushing predicates into the join if they
originated from
+above the join. Hence, the location of predicates and filters in your Druid
SQL is very important.
+Also, as a result of this, comma joins should be avoided.
#### Future work for joins
Joins are an area of active development in Druid. The following features are
missing today but may appear in
future versions:
+- Reordering of predicates and filters (pushing up and/or pushing down) to get
the most performant plan.
- Preloaded dimension tables that are wider than lookups (i.e. supporting more
than a single key and single value).
- RIGHT OUTER and FULL OUTER joins. Currently, they are partially implemented.
Queries will run but results will not
always be correct.
diff --git a/docs/querying/sql.md b/docs/querying/sql.md
index d7d2c78..80f8a39 100644
--- a/docs/querying/sql.md
+++ b/docs/querying/sql.md
@@ -528,13 +528,18 @@ the way you write the filter.
subqueries generated by conditions on mismatched types, and implicit
subqueries generated by conditions that use
expressions to refer to the right-hand side.
-3. Read through the [Query execution](query-execution.md) page to understand
how various types of native queries
+3. Currently, Druid does not support pushing down predicates (condition and
filter) past a Join (i.e. into
+Join's children). Druid only supports pushing predicates into the join if they
originated from
+above the join. Hence, the location of predicates and filters in your Druid
SQL is very important.
+Also, as a result of this, comma joins should be avoided.
+
+4. Read through the [Query execution](query-execution.md) page to understand
how various types of native queries
will be executed.
-4. Be careful when interpreting EXPLAIN PLAN output, and use request logging
if in doubt. Request logs will show the
+5. Be careful when interpreting EXPLAIN PLAN output, and use request logging
if in doubt. Request logs will show the
exact native query that was run. See the [next
section](#interpreting-explain-plan-output) for more details.
-5. If you encounter a query that could be planned better, feel free to
+6. If you encounter a query that could be planned better, feel free to
[raise an issue on GitHub](https://github.com/apache/druid/issues/new/choose).
A reproducible test case is always
appreciated.
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/planner/Rules.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/Rules.java
index 3ac79aa..acc27c7 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/planner/Rules.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/planner/Rules.java
@@ -42,7 +42,6 @@ import org.apache.calcite.rel.rules.AggregateValuesRule;
import org.apache.calcite.rel.rules.CalcRemoveRule;
import org.apache.calcite.rel.rules.ExchangeRemoveConstantKeysRule;
import org.apache.calcite.rel.rules.FilterAggregateTransposeRule;
-import org.apache.calcite.rel.rules.FilterJoinRule;
import org.apache.calcite.rel.rules.FilterMergeRule;
import org.apache.calcite.rel.rules.FilterProjectTransposeRule;
import org.apache.calcite.rel.rules.FilterTableScanRule;
@@ -75,6 +74,7 @@ import org.apache.druid.sql.calcite.rel.QueryMaker;
import org.apache.druid.sql.calcite.rule.DruidRelToDruidRule;
import org.apache.druid.sql.calcite.rule.DruidRules;
import org.apache.druid.sql.calcite.rule.DruidTableScanRule;
+import org.apache.druid.sql.calcite.rule.FilterJoinExcludePushToChildRule;
import org.apache.druid.sql.calcite.rule.ProjectAggregatePruneUnusedCallRule;
import org.apache.druid.sql.calcite.rule.SortCollapseRule;
@@ -102,7 +102,6 @@ public class Rules
FilterTableScanRule.INSTANCE,
ProjectFilterTransposeRule.INSTANCE,
FilterProjectTransposeRule.INSTANCE,
- FilterJoinRule.FILTER_ON_JOIN,
JoinPushExpressionsRule.INSTANCE,
AggregateCaseToFilterRule.INSTANCE,
FilterAggregateTransposeRule.INSTANCE,
@@ -169,8 +168,6 @@ public class Rules
// 3) JoinCommuteRule (we don't support reordering joins yet).
private static final List<RelOptRule> ABSTRACT_RELATIONAL_RULES =
ImmutableList.of(
- FilterJoinRule.FILTER_ON_JOIN,
- FilterJoinRule.JOIN,
AbstractConverter.ExpandConversionRule.INSTANCE,
AggregateRemoveRule.INSTANCE,
UnionToDistinctRule.INSTANCE,
@@ -243,6 +240,7 @@ public class Rules
}
// Rules that we wrote.
+
rules.add(FilterJoinExcludePushToChildRule.FILTER_ON_JOIN_EXCLUDE_PUSH_TO_CHILD);
rules.add(SortCollapseRule.instance());
rules.add(ProjectAggregatePruneUnusedCallRule.instance());
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/rule/DruidJoinRule.java
b/sql/src/main/java/org/apache/druid/sql/calcite/rule/DruidJoinRule.java
index 380d1e9..cb7fc05 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/rule/DruidJoinRule.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/rule/DruidJoinRule.java
@@ -193,9 +193,24 @@ public class DruidJoinRule extends RelOptRule
final int numLeftFields = leftRowType.getFieldCount();
for (RexNode subCondition : subConditions) {
- if (subCondition.isA(SqlKind.LITERAL)) {
- // Literals are always OK.
- literalSubConditions.add((RexLiteral) subCondition);
+ if (RexUtil.isLiteral(subCondition, true)) {
+ if (subCondition.isA(SqlKind.CAST)) {
+ // This is CAST(literal) which is always OK.
+ // We know that this is CAST(literal) as it passed the check from
RexUtil.isLiteral
+ RexCall call = (RexCall) subCondition;
+ // We have to verify the types of the cast here, because if the
underlying literal and the cast output type
+ // are different, then skipping the cast might change the meaning of
the subcondition.
+ if
(call.getType().getSqlTypeName().equals(call.getOperands().get(0).getType().getSqlTypeName()))
{
+ // If the types are the same, unwrap the cast and use the
underlying literal.
+ literalSubConditions.add((RexLiteral) call.getOperands().get(0));
+ } else {
+ // If the types are not the same, return Optional.empty()
indicating the condition is not supported.
+ return Optional.empty();
+ }
+ } else {
+ // Literals are always OK.
+ literalSubConditions.add((RexLiteral) subCondition);
+ }
continue;
}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/rule/FilterJoinExcludePushToChildRule.java
b/sql/src/main/java/org/apache/druid/sql/calcite/rule/FilterJoinExcludePushToChildRule.java
new file mode 100644
index 0000000..ca9ed46
--- /dev/null
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/rule/FilterJoinExcludePushToChildRule.java
@@ -0,0 +1,295 @@
+/*
+ * 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.druid.sql.calcite.rule;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Sets;
+import org.apache.calcite.adapter.enumerable.EnumerableConvention;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelOptRuleOperand;
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.Join;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.rules.FilterJoinRule;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.tools.RelBuilderFactory;
+
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * This class is a copy (with modification) of {@link FilterJoinRule}.
Specifically, this class contains a
+ * subset of code from {@link FilterJoinRule} for the codepath involving
{@link FilterJoinRule#FILTER_ON_JOIN}
+ * Everything has been keep as-is from {@link FilterJoinRule} except for the
modification
+ * of {@link #classifyFilters(List, JoinRelType, boolean, List)} method called
in the
+ * {@link #perform(RelOptRuleCall, Filter, Join)} method of this class.
+ * The {@link #classifyFilters(List, JoinRelType, boolean, List)} method is
based of {@link RelOptUtil#classifyFilters}.
+ * The difference is that the modfied method use in thsi class will not not
push filters to the children.
+ * Hence, filters will either stay where they are or are pushed to the join
(if they originated from above the join).
+ *
+ * This modification is needed due to the bug described in
https://github.com/apache/druid/pull/9773
+ * This class and it's modification can be removed, switching back to the
default Rule provided in Calcite's
+ * {@link FilterJoinRule} when https://github.com/apache/druid/issues/9843 is
resolved.
+ */
+
+public abstract class FilterJoinExcludePushToChildRule extends FilterJoinRule
+{
+ /** Copied from {@link FilterJoinRule#NOT_ENUMERABLE} */
+ private static final Predicate NOT_ENUMERABLE = (join, joinType, exp) ->
+ join.getConvention() != EnumerableConvention.INSTANCE;
+
+ /**
+ * Rule that pushes predicates from a Filter into the Join below them.
+ * Similar to {@link FilterJoinRule#FILTER_ON_JOIN} but does not push
predicate to the child
+ */
+ public static final FilterJoinRule FILTER_ON_JOIN_EXCLUDE_PUSH_TO_CHILD =
+ new FilterIntoJoinExcludePushToChildRule(RelFactories.LOGICAL_BUILDER,
NOT_ENUMERABLE);
+
+ FilterJoinExcludePushToChildRule(RelOptRuleOperand operand,
+ String id,
+ boolean smart,
+ RelBuilderFactory relBuilderFactory,
+ Predicate predicate)
+ {
+ super(operand, id, smart, relBuilderFactory, predicate);
+ }
+
+ /**
+ * Rule that tries to push filter expressions into a join
+ * condition. Exlucde pushing into the inputs (child) of the join.
+ */
+ public static class FilterIntoJoinExcludePushToChildRule extends
FilterJoinExcludePushToChildRule
+ {
+ public FilterIntoJoinExcludePushToChildRule(RelBuilderFactory
relBuilderFactory, Predicate predicate)
+ {
+ super(
+ operand(Filter.class,
+ operand(Join.class, RelOptRule.any())),
+ "FilterJoinExcludePushToChildRule:filter", true, relBuilderFactory,
+ predicate);
+ }
+
+ @Override
+ public void onMatch(RelOptRuleCall call)
+ {
+ Filter filter = call.rel(0);
+ Join join = call.rel(1);
+ perform(call, filter, join);
+ }
+ }
+
+ /**
+ * Copied from {@link FilterJoinRule#perform}
+ * The difference is that this method will not not push filters to the
children in classifyFilters
+ */
+ @Override
+ protected void perform(RelOptRuleCall call, Filter filter, Join join)
+ {
+ final List<RexNode> joinFilters =
+ RelOptUtil.conjunctions(join.getCondition());
+ final List<RexNode> origJoinFilters = ImmutableList.copyOf(joinFilters);
+ // If there is only the joinRel,
+ // make sure it does not match a cartesian product joinRel
+ // (with "true" condition), otherwise this rule will be applied
+ // again on the new cartesian product joinRel.
+ if (filter == null && joinFilters.isEmpty()) {
+ return;
+ }
+
+ final List<RexNode> aboveFilters =
+ filter != null
+ ? getConjunctions(filter)
+ : new ArrayList<>();
+ final ImmutableList<RexNode> origAboveFilters =
+ ImmutableList.copyOf(aboveFilters);
+
+ // Simplify Outer Joins
+ JoinRelType joinType = join.getJoinType();
+ if (!origAboveFilters.isEmpty() && join.getJoinType() !=
JoinRelType.INNER) {
+ joinType = RelOptUtil.simplifyJoin(join, origAboveFilters, joinType);
+ }
+
+ final List<RexNode> leftFilters = new ArrayList<>();
+ final List<RexNode> rightFilters = new ArrayList<>();
+
+ // TODO - add logic to derive additional filters. E.g., from
+ // (t1.a = 1 AND t2.a = 2) OR (t1.b = 3 AND t2.b = 4), you can
+ // derive table filters:
+ // (t1.a = 1 OR t1.b = 3)
+ // (t2.a = 2 OR t2.b = 4)
+
+ // Try to push down above filters. These are typically where clause
+ // filters. They can be pushed down if they are not on the NULL
+ // generating side.
+ boolean filterPushed = false;
+ if (classifyFilters(aboveFilters, joinType, true, joinFilters)) {
+ filterPushed = true;
+ }
+
+ // Move join filters up if needed
+ validateJoinFilters(aboveFilters, joinFilters, join, joinType);
+
+ // If no filter got pushed after validate, reset filterPushed flag
+ if (joinFilters.size() == origJoinFilters.size()) {
+ if (Sets.newHashSet(joinFilters)
+ .equals(Sets.newHashSet(origJoinFilters))) {
+ filterPushed = false;
+ }
+ }
+
+ // Try to push down filters in ON clause. A ON clause filter can only be
+ // pushed down if it does not affect the non-matching set, i.e. it is
+ // not on the side which is preserved.
+
+ // Anti-join on conditions can not be pushed into left or right, e.g. for
plan:
+ //
+ // Join(condition=[AND(cond1, $2)], joinType=[anti])
+ // : - prj(f0=[$0], f1=[$1], f2=[$2])
+ // : - prj(f0=[$0])
+ //
+ // The semantic would change if join condition $2 is pushed into left,
+ // that is, the result set may be smaller. The right can not be pushed
+ // into for the same reason.
+ if (joinType != JoinRelType.ANTI && classifyFilters(joinFilters, joinType,
false, joinFilters)) {
+ filterPushed = true;
+ }
+
+ // if nothing actually got pushed and there is nothing leftover,
+ // then this rule is a no-op
+ if ((!filterPushed && joinType == join.getJoinType()) ||
joinFilters.isEmpty()) {
+ return;
+ }
+
+ // create Filters on top of the children if any filters were
+ // pushed to them
+ final RexBuilder rexBuilder = join.getCluster().getRexBuilder();
+ final RelBuilder relBuilder = call.builder();
+ final RelNode leftRel =
+ relBuilder.push(join.getLeft()).filter(leftFilters).build();
+ final RelNode rightRel =
+ relBuilder.push(join.getRight()).filter(rightFilters).build();
+
+ // create the new join node referencing the new children and
+ // containing its new join filters (if there are any)
+ final ImmutableList<RelDataType> fieldTypes =
+ ImmutableList.<RelDataType>builder()
+ .addAll(RelOptUtil.getFieldTypeList(leftRel.getRowType()))
+
.addAll(RelOptUtil.getFieldTypeList(rightRel.getRowType())).build();
+ final RexNode joinFilter =
+ RexUtil.composeConjunction(rexBuilder,
+ RexUtil.fixUp(rexBuilder, joinFilters,
fieldTypes));
+
+ // If nothing actually got pushed and there is nothing leftover,
+ // then this rule is a no-op
+ if (joinFilter.isAlwaysTrue()
+ && leftFilters.isEmpty()
+ && rightFilters.isEmpty()
+ && joinType == join.getJoinType()) {
+ return;
+ }
+
+ RelNode newJoinRel =
+ join.copy(
+ join.getTraitSet(),
+ joinFilter,
+ leftRel,
+ rightRel,
+ joinType,
+ join.isSemiJoinDone());
+ call.getPlanner().onCopy(join, newJoinRel);
+ if (!leftFilters.isEmpty()) {
+ call.getPlanner().onCopy(filter, leftRel);
+ }
+ if (!rightFilters.isEmpty()) {
+ call.getPlanner().onCopy(filter, rightRel);
+ }
+
+ relBuilder.push(newJoinRel);
+
+ // Create a project on top of the join if some of the columns have become
+ // NOT NULL due to the join-type getting stricter.
+ relBuilder.convert(join.getRowType(), false);
+
+ // create a FilterRel on top of the join if needed
+ relBuilder.filter(
+ RexUtil.fixUp(rexBuilder, aboveFilters,
+
RelOptUtil.getFieldTypeList(relBuilder.peek().getRowType())));
+ call.transformTo(relBuilder.build());
+ }
+
+ /**
+ * Copied from {@link FilterJoinRule#getConjunctions}. Method is exactly the
same as original.
+ */
+ private List<RexNode> getConjunctions(Filter filter)
+ {
+ List<RexNode> conjunctions =
RelOptUtil.conjunctions(filter.getCondition());
+ RexBuilder rexBuilder = filter.getCluster().getRexBuilder();
+ for (int i = 0; i < conjunctions.size(); i++) {
+ RexNode node = conjunctions.get(i);
+ if (node instanceof RexCall) {
+ conjunctions.set(i,
+
RelOptUtil.collapseExpandedIsNotDistinctFromExpr((RexCall) node, rexBuilder));
+ }
+ }
+ return conjunctions;
+ }
+
+ /**
+ * Copied from {@link RelOptUtil#classifyFilters}
+ * The difference is that this method will not not push filters to the
children.
+ * Hence, filters will either stay where they are or are pushed to the join
(if they originated
+ * from above the join).
+ */
+ private static boolean classifyFilters(List<RexNode> filters,
+ JoinRelType joinType,
+ boolean pushInto,
+ List<RexNode> joinFilters)
+ {
+ final List<RexNode> filtersToRemove = new ArrayList<>();
+ for (RexNode filter : filters) {
+ // Skip pushing the filter to either child. However, if the join
+ // is an inner join, push them to the join if they originated
+ // from above the join
+ if (!joinType.isOuterJoin() && pushInto) {
+ if (!joinFilters.contains(filter)) {
+ joinFilters.add(filter);
+ }
+ filtersToRemove.add(filter);
+ }
+ }
+
+ // Remove filters after the loop, to prevent concurrent modification.
+ if (!filtersToRemove.isEmpty()) {
+ filters.removeAll(filtersToRemove);
+ }
+
+ // Did anything change?
+ return !filtersToRemove.isEmpty();
+ }
+}
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
index e15424e..81cf68d 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
@@ -8387,6 +8387,324 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
}
@Test
+ public void testInnerJoinTableLookupLookupWithFilterWithOuterLimit() throws
Exception
+ {
+ testQuery(
+ "SELECT dim1\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.dim2 = l.k\n"
+ + "INNER JOIN lookup.lookyloo l2 ON foo.dim2 = l2.k\n"
+ + "WHERE l.v = 'xa'\n"
+ + "LIMIT 100\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new LookupDataSource("lookyloo"),
+ "j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_j0.",
+ equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_j0.k")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .limit(100)
+ .filters(selector("j0.v", "xa", null))
+ .columns("dim1")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{""},
+ new Object[]{"1"}
+ )
+ );
+ }
+
+ @Test
+ public void testInnerJoinTableLookupLookupWithFilterWithoutLimit() throws
Exception
+ {
+ testQuery(
+ "SELECT dim1\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.dim2 = l.k\n"
+ + "INNER JOIN lookup.lookyloo l2 ON foo.dim2 = l2.k\n"
+ + "WHERE l.v = 'xa'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new LookupDataSource("lookyloo"),
+ "j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_j0.",
+ equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_j0.k")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(selector("j0.v", "xa", null))
+ .columns("dim1")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{""},
+ new Object[]{"1"}
+ )
+ );
+ }
+
+ @Test
+ public void
testInnerJoinTableLookupLookupWithFilterWithOuterLimitWithAllColumns() throws
Exception
+ {
+ testQuery(
+ "SELECT __time, cnt, dim1, dim2, dim3, m1, m2, unique_dim1\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.dim2 = l.k\n"
+ + "INNER JOIN lookup.lookyloo l2 ON foo.dim2 = l2.k\n"
+ + "WHERE l.v = 'xa'\n"
+ + "LIMIT 100\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new LookupDataSource("lookyloo"),
+ "j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_j0.",
+ equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_j0.k")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .limit(100)
+ .filters(selector("j0.v", "xa", null))
+ .columns("__time", "cnt", "dim1", "dim2", "dim3", "m1", "m2",
"unique_dim1")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{946684800000L, 1L, "", "a", "[\"a\",\"b\"]", 1.0F,
1.0, "\"AQAAAEAAAA==\""},
+ new Object[]{978307200000L, 1L, "1", "a", "", 4.0F, 4.0,
"\"AQAAAQAAAAFREA==\""}
+ )
+ );
+ }
+
+ @Test
+ public void
testInnerJoinTableLookupLookupWithFilterWithoutLimitWithAllColumns() throws
Exception
+ {
+ testQuery(
+ "SELECT __time, cnt, dim1, dim2, dim3, m1, m2, unique_dim1\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.dim2 = l.k\n"
+ + "INNER JOIN lookup.lookyloo l2 ON foo.dim2 = l2.k\n"
+ + "WHERE l.v = 'xa'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new LookupDataSource("lookyloo"),
+ "j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_j0.",
+ equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_j0.k")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(selector("j0.v", "xa", null))
+ .columns("__time", "cnt", "dim1", "dim2", "dim3", "m1", "m2",
"unique_dim1")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{946684800000L, 1L, "", "a", "[\"a\",\"b\"]", 1.0F,
1.0, "\"AQAAAEAAAA==\""},
+ new Object[]{978307200000L, 1L, "1", "a", "", 4.0F, 4.0,
"\"AQAAAQAAAAFREA==\""}
+ )
+ );
+ }
+
+ @Test
+ public void testManyManyInnerJoinOnManyManyLookup() throws Exception
+ {
+ testQuery(
+ "SELECT dim1\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.dim2 = l.k\n"
+ + "INNER JOIN lookup.lookyloo l2 ON foo.dim2 = l2.k\n"
+ + "INNER JOIN lookup.lookyloo l3 ON foo.dim2 = l3.k\n"
+ + "INNER JOIN lookup.lookyloo l4 ON foo.dim2 = l4.k\n"
+ + "INNER JOIN lookup.lookyloo l5 ON foo.dim2 = l5.k\n"
+ + "INNER JOIN lookup.lookyloo l6 ON foo.dim2 = l6.k\n"
+ + "INNER JOIN lookup.lookyloo l7 ON foo.dim2 = l7.k\n"
+ + "INNER JOIN lookup.lookyloo l8 ON foo.dim2 = l8.k\n"
+ + "INNER JOIN lookup.lookyloo l9 ON foo.dim2 = l9.k\n"
+ + "INNER JOIN lookup.lookyloo l10 ON foo.dim2 = l10.k\n"
+ + "INNER JOIN lookup.lookyloo l11 ON foo.dim2 = l11.k\n"
+ + "INNER JOIN lookup.lookyloo l12 ON foo.dim2 = l12.k\n"
+ + "INNER JOIN lookup.lookyloo l13 ON foo.dim2 = l13.k\n"
+ + "INNER JOIN lookup.lookyloo l14 ON foo.dim2 = l14.k\n"
+ + "INNER JOIN lookup.lookyloo l15 ON foo.dim2 = l15.k\n"
+ + "INNER JOIN lookup.lookyloo l16 ON foo.dim2 = l16.k\n"
+ + "INNER JOIN lookup.lookyloo l17 ON foo.dim2 = l17.k\n"
+ + "INNER JOIN lookup.lookyloo l18 ON foo.dim2 = l18.k\n"
+ + "INNER JOIN lookup.lookyloo l19 ON foo.dim2 = l19.k\n"
+ + "WHERE l.v = 'xa'\n",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+ join(
+
join(
+
join(
+
join(
+
join(
+
join(
+
new TableDataSource(CalciteTests.DATASOURCE1),
+
new LookupDataSource("lookyloo"),
+
"j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("j0.k")),
+
JoinType.INNER
+
),
+
new LookupDataSource("lookyloo"),
+
"_j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_j0.k")),
+
JoinType.INNER
+
),
+
new LookupDataSource("lookyloo"),
+
"__j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("__j0.k")),
+
JoinType.INNER
+
),
+
new LookupDataSource("lookyloo"),
+
"___j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("___j0.k")),
+
JoinType.INNER
+
),
+
new LookupDataSource("lookyloo"),
+
"____j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("____j0.k")),
+
JoinType.INNER
+ ),
+
new LookupDataSource("lookyloo"),
+
"_____j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_____j0.k")),
+
JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+
"______j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("______j0.k")),
+
JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+
"_______j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_______j0.k")),
+
JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+ "________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("________j0.k")),
+ JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+ "_________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_________j0.k")),
+ JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+ "__________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("__________j0.k")),
+ JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+ "___________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("___________j0.k")),
+ JoinType.INNER
+ ),
+ new
LookupDataSource("lookyloo"),
+ "____________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("____________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_____________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_____________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "______________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("______________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_______________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_______________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "________________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("________________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "_________________j0.",
+
equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("_________________j0.k")),
+ JoinType.INNER
+ ),
+ new LookupDataSource("lookyloo"),
+ "__________________j0.",
+ equalsCondition(DruidExpression.fromColumn("dim2"),
DruidExpression.fromColumn("__________________j0.k")),
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(selector("j0.v", "xa", null))
+ .columns("dim1")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{""},
+ new Object[]{"1"}
+ )
+ );
+ }
+
+ @Test
public void testInnerJoinQueryOfLookup() throws Exception
{
// Cannot vectorize the subquery.
@@ -8747,10 +9065,40 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
public void testWhereInSelectNullFromLookup() throws Exception
{
// Regression test for https://github.com/apache/druid/issues/9646.
+ cannotVectorize();
testQuery(
"SELECT * FROM foo where dim1 IN (SELECT NULL FROM lookup.lookyloo)",
- ImmutableList.of(),
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
+ new QueryDataSource(
+ GroupByQuery.builder()
+ .setDataSource(new
LookupDataSource("lookyloo"))
+
.setInterval(querySegmentSpec(Filtration.eternity()))
+ .setVirtualColumns(
+ expressionVirtualColumn("v0",
"null", ValueType.STRING)
+ )
+ .setGranularity(Granularities.ALL)
+ .setDimensions(dimensions(new
DefaultDimensionSpec("v0", "d0")))
+ .setContext(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "j0.",
+ "0",
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "null", ValueType.STRING)
+ )
+ .columns("__time", "cnt", "dim2", "dim3", "m1", "m2",
"unique_dim1", "v0")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
ImmutableList.of()
);
}
@@ -8789,6 +9137,9 @@ public class CalciteQueryTest extends BaseCalciteQueryTest
);
}
+ // This SQL currently does not result in an optimum plan.
+ // Unfortunately, we have disabled pushing down predicates (conditions and
filters) due to https://github.com/apache/druid/pull/9773
+ // Hence, comma join will result in a cross join with filter on outermost
@Test
public void testCommaJoinTableLookupTableMismatchedTypes() throws Exception
{
@@ -8807,6 +9158,56 @@ public class CalciteQueryTest extends
BaseCalciteQueryTest
join(
join(
new TableDataSource(CalciteTests.DATASOURCE1),
+ new LookupDataSource("lookyloo"),
+ "j0.",
+ "1",
+ JoinType.INNER
+ ),
+ new QueryDataSource(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE3)
+
.intervals(querySegmentSpec(Filtration.eternity()))
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .columns("cnt")
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ "_j0.",
+ "1",
+ JoinType.INNER
+ )
+ )
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .granularity(Granularities.ALL)
+ .aggregators(new CountAggregatorFactory("a0"))
+ .filters(and(
+ expressionFilter("(\"cnt\" == CAST(\"j0.k\", 'LONG'))"),
+ expressionFilter("(CAST(\"j0.k\", 'LONG') ==
\"_j0.cnt\")")
+ ))
+ .context(TIMESERIES_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of()
+ );
+ }
+
+ @Test
+ public void testJoinTableLookupTableMismatchedTypesWithoutComma() throws
Exception
+ {
+ // Cannot vectorize JOIN operator.
+ cannotVectorize();
+
+ testQuery(
+ "SELECT COUNT(*)\n"
+ + "FROM foo\n"
+ + "INNER JOIN lookup.lookyloo l ON foo.cnt = l.k\n"
+ + "INNER JOIN numfoo ON l.k = numfoo.cnt\n",
+ ImmutableList.of(
+ Druids.newTimeseriesQueryBuilder()
+ .dataSource(
+ join(
+ join(
+ new TableDataSource(CalciteTests.DATASOURCE1),
new QueryDataSource(
newScanQueryBuilder()
.dataSource(new
LookupDataSource("lookyloo"))
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
