org.apache.calcite.adapter.enumerable.EnumerableRules Java Examples

The following examples show how to use org.apache.calcite.adapter.enumerable.EnumerableRules. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: EnumerableSortedAggregateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void sortedAggNullValueInSortedGroupByKeys() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select commission, "
              + "count(deptno) as num_dept "
              + "from emps group by commission")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
      })
      .explainContains(
          "EnumerableSortedAggregate(group=[{4}], num_dept=[COUNT()])\n"
              + "  EnumerableSort(sort0=[$4], dir0=[ASC])\n"
              + "    EnumerableTableScan(table=[[s, emps]])")
      .returnsOrdered(
          "commission=250; num_dept=1",
          "commission=500; num_dept=1",
          "commission=1000; num_dept=1",
          "commission=null; num_dept=1");
}
 
Example #2
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Unit test that calls {@link Planner#transform} twice. */
@Test void testPlanTransformTwice() throws Exception {
  RuleSet ruleSet =
      RuleSets.ofList(
          FilterMergeRule.INSTANCE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE);
  Planner planner = getPlanner(null, Programs.of(ruleSet));
  SqlNode parse = planner.parse("select * from \"emps\"");
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).project();
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode transform = planner.transform(0, traitSet, convert);
  RelNode transform2 = planner.transform(0, traitSet, transform);
  assertThat(toString(transform2),
      equalTo(
          "EnumerableProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
          + "  EnumerableTableScan(table=[[hr, emps]])\n"));
}
 
Example #3
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Unit test that parses, validates, converts and plans. Planner is
 * provided with a list of RelTraitDefs to register. */
@Test void testPlanWithExplicitTraitDefs() throws Exception {
  RuleSet ruleSet =
      RuleSets.ofList(
          FilterMergeRule.INSTANCE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE);
  final List<RelTraitDef> traitDefs = new ArrayList<>();
  traitDefs.add(ConventionTraitDef.INSTANCE);
  traitDefs.add(RelCollationTraitDef.INSTANCE);

  Planner planner = getPlanner(traitDefs, Programs.of(ruleSet));

  SqlNode parse = planner.parse("select * from \"emps\"");
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).project();
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode transform = planner.transform(0, traitSet, convert);
  assertThat(toString(transform),
      equalTo(
          "EnumerableProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
          + "  EnumerableTableScan(table=[[hr, emps]])\n"));
}
 
Example #4
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Unit test that parses, validates, converts and
 * plans for query using two duplicate order by.*/
@Test void testDuplicateSortPlanWORemoveSortRule() throws Exception {
  RuleSet ruleSet =
      RuleSets.ofList(
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE);
  Planner planner = getPlanner(null, Programs.of(ruleSet));
  SqlNode parse = planner.parse(
      "select \"empid\" from ( "
          + "select * "
          + "from \"emps\" "
          + "order by \"emps\".\"deptno\") "
          + "order by \"deptno\"");
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).rel;
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode transform = planner.transform(0, traitSet, convert);
  assertThat(toString(transform),
      equalTo("EnumerableSort(sort0=[$1], dir0=[ASC])\n"
          + "  EnumerableProject(empid=[$0], deptno=[$1])\n"
          + "    EnumerableTableScan(table=[[hr, emps]])\n"));
}
 
Example #5
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
private void runDuplicateSortCheck(String sql, String plan) throws Exception {
  RuleSet ruleSet =
      RuleSets.ofList(
          SortRemoveRule.INSTANCE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE,
          EnumerableRules.ENUMERABLE_WINDOW_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE,
          ProjectToWindowRule.PROJECT);
  Planner planner = getPlanner(null,
      SqlParser.configBuilder().setLex(Lex.JAVA).build(),
      Programs.of(ruleSet));
  SqlNode parse = planner.parse(sql);
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).rel;
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  if (traitSet.getTrait(RelCollationTraitDef.INSTANCE) == null) {
    // SortRemoveRule can only work if collation trait is enabled.
    return;
  }
  RelNode transform = planner.transform(0, traitSet, convert);
  assertThat(toString(transform), equalTo(plan));
}
 
Example #6
Source File: EnumerableSortedAggregateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void sortedAgg() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select deptno, "
          + "max(salary) as max_salary, count(name) as num_employee "
          + "from emps group by deptno")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
      })
      .explainContains(
          "EnumerableSortedAggregate(group=[{1}], max_salary=[MAX($3)], num_employee=[COUNT($2)])\n"
          + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
          + "    EnumerableTableScan(table=[[s, emps]])")
      .returnsOrdered(
          "deptno=10; max_salary=11500.0; num_employee=3",
          "deptno=20; max_salary=8000.0; num_employee=1");
}
 
Example #7
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Unit test that parses, validates, converts and
 * plans for query using order by */
@Test void testSortPlan() throws Exception {
  RuleSet ruleSet =
      RuleSets.ofList(
          SortRemoveRule.INSTANCE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE);
  Planner planner = getPlanner(null, Programs.of(ruleSet));
  SqlNode parse = planner.parse(
      "select * from \"emps\" "
          + "order by \"emps\".\"deptno\"");
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).project();
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode transform = planner.transform(0, traitSet, convert);
  assertThat(toString(transform),
      equalTo("EnumerableSort(sort0=[$1], dir0=[ASC])\n"
          + "  EnumerableProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
          + "    EnumerableTableScan(table=[[hr, emps]])\n"));
}
 
Example #8
Source File: EnumerableCorrelateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2621">[CALCITE-2621]
 * Add rule to execute semi joins with correlation</a> */
@Test void semiJoinCorrelate() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select empid, name from emps e where e.deptno in (select d.deptno from depts d)")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        // force the semijoin to run via EnumerableCorrelate
        // instead of EnumerableHashJoin(SEMI)
        planner.addRule(JoinToCorrelateRule.INSTANCE);
        planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
        planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
      })
      .explainContains(""
          + "EnumerableCalc(expr#0..3=[{inputs}], empid=[$t1], name=[$t3])\n"
          + "  EnumerableCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}])\n"
          + "    EnumerableAggregate(group=[{0}])\n"
          + "      EnumerableTableScan(table=[[s, depts]])\n"
          + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=[$cor1], expr#6=[$t5.deptno], expr#7=[=($t6, $t1)], proj#0..2=[{exprs}], $condition=[$t7])\n"
          + "      EnumerableTableScan(table=[[s, emps]])")
      .returnsUnordered(
          "empid=100; name=Bill",
          "empid=110; name=Theodore",
          "empid=150; name=Sebastian");
}
 
Example #9
Source File: EnumerableSortedAggregateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void sortedAggGroupbyXOrderbyX() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select deptno, "
              + "max(salary) as max_salary, count(name) as num_employee "
              + "from emps group by deptno order by deptno")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_AGGREGATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_SORTED_AGGREGATE_RULE);
      })
      .explainContains(
          "EnumerableSortedAggregate(group=[{1}], max_salary=[MAX($3)], num_employee=[COUNT($2)])\n"
          + "  EnumerableSort(sort0=[$1], dir0=[ASC])\n"
          + "    EnumerableTableScan(table=[[s, emps]])")
      .returnsOrdered(
          "deptno=10; max_salary=11500.0; num_employee=3",
          "deptno=20; max_salary=8000.0; num_employee=1");
}
 
Example #10
Source File: PlannerTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Unit test that parses, validates, converts and plans. */
@Test void testPlan() throws Exception {
  Program program =
      Programs.ofRules(
          FilterMergeRule.INSTANCE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE);
  Planner planner = getPlanner(null, program);
  SqlNode parse = planner.parse("select * from \"emps\"");
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).project();
  RelTraitSet traitSet = convert.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode transform = planner.transform(0, traitSet, convert);
  assertThat(toString(transform),
      equalTo(
          "EnumerableProject(empid=[$0], deptno=[$1], name=[$2], salary=[$3], commission=[$4])\n"
          + "  EnumerableTableScan(table=[[hr, emps]])\n"));
}
 
Example #11
Source File: SortRemoveRuleTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2554">[CALCITE-2554]
 * Enrich enumerable join operators with order preserving information</a>.
 *
 * <p>Since join inputs are sorted, and this join preserves the order of the
 * left input, there shouldn't be any sort operator above the join.
 */
@Test void removeSortOverEnumerableSemiJoin() throws Exception {
  RuleSet prepareRules =
      RuleSets.ofList(
          SortProjectTransposeRule.INSTANCE,
          SemiJoinRule.PROJECT,
          SemiJoinRule.JOIN,
          EnumerableRules.ENUMERABLE_PROJECT_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE,
          EnumerableRules.ENUMERABLE_JOIN_RULE,
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
  String sql =
      "select e.\"deptno\" from \"hr\".\"emps\" e\n"
          + " where e.\"deptno\" in (select d.\"deptno\" from \"hr\".\"depts\" d)\n"
          + " order by e.\"empid\"";
  RelNode actualPlan = transform(sql, prepareRules);
  assertThat(
      toString(actualPlan),
      allOf(
          containsString("EnumerableHashJoin"),
          not(containsString("EnumerableSort"))));
}
 
Example #12
Source File: SortRemoveRuleTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2554">[CALCITE-2554]
 * Enrich enumerable join operators with order preserving information</a>.
 *
 * <p>Since join inputs are sorted, and this join preserves the order of the
 * left input, there shouldn't be any sort operator above the join.
 *
 * <p>Until CALCITE-2018 is fixed we can add back EnumerableRules.ENUMERABLE_SORT_RULE
 */
@Test void removeSortOverEnumerableCorrelate() throws Exception {
  RuleSet prepareRules =
      RuleSets.ofList(
          SortProjectTransposeRule.INSTANCE,
          JoinToCorrelateRule.INSTANCE,
          EnumerableRules.ENUMERABLE_PROJECT_RULE,
          EnumerableRules.ENUMERABLE_CORRELATE_RULE,
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
  for (String joinType : Arrays.asList("left", "inner")) {
    String sql =
        "select e.\"deptno\" from \"hr\".\"emps\" e "
            + joinType + " join \"hr\".\"depts\" d "
            + " on e.\"deptno\" = d.\"deptno\" "
            + "order by e.\"empid\" ";
    RelNode actualPlan = transform(sql, prepareRules);
    assertThat(
        toString(actualPlan),
        allOf(
            containsString("EnumerableCorrelate"),
            not(containsString("EnumerableSort"))));
  }
}
 
Example #13
Source File: EnumerableHashJoinTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void innerJoin() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select e.empid, e.name, d.name as dept from emps e join depts "
              + "d on e.deptno=d.deptno")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
      planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE))
      .explainContains("EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], "
          + "name=[$t2], dept=[$t4])\n"
          + "  EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])\n"
          + "    EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n"
          + "      EnumerableTableScan(table=[[s, emps]])\n"
          + "    EnumerableCalc(expr#0..3=[{inputs}], proj#0..1=[{exprs}])\n"
          + "      EnumerableTableScan(table=[[s, depts]])\n")
      .returnsUnordered(
          "empid=100; name=Bill; dept=Sales",
          "empid=110; name=Theodore; dept=Sales",
          "empid=150; name=Sebastian; dept=Sales");
}
 
Example #14
Source File: EnumerableCorrelateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2605">[CALCITE-2605]
 * NullPointerException when left outer join implemented with EnumerableCorrelate</a> */
@Test void leftOuterJoinCorrelate() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select e.empid, e.name, d.name as dept from emps e left outer join depts d on e.deptno=d.deptno")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        // force the left outer join to run via EnumerableCorrelate
        // instead of EnumerableHashJoin
        planner.addRule(JoinToCorrelateRule.INSTANCE);
        planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
      })
      .explainContains(""
          + "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2], dept=[$t4])\n"
          + "  EnumerableCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])\n"
          + "    EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n"
          + "      EnumerableTableScan(table=[[s, emps]])\n"
          + "    EnumerableCalc(expr#0..3=[{inputs}], expr#4=[$cor0], expr#5=[$t4.deptno], expr#6=[=($t5, $t0)], proj#0..1=[{exprs}], $condition=[$t6])\n"
          + "      EnumerableTableScan(table=[[s, depts]])")
      .returnsUnordered(
          "empid=100; name=Bill; dept=Sales",
          "empid=110; name=Theodore; dept=Sales",
          "empid=150; name=Sebastian; dept=Sales",
          "empid=200; name=Eric; dept=null");
}
 
Example #15
Source File: EnumerableBatchNestedLoopJoinTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void simpleInnerBatchJoinTestBuilder() {
  tester(false, new JdbcTest.HrSchema())
      .query("?")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE);
      })
      .withRel(
          builder -> builder
              .scan("s", "depts").as("d")
              .scan("s", "emps").as("e")
              .join(JoinRelType.INNER,
                  builder.equals(
                      builder.field(2, "d", "deptno"),
                      builder.field(2, "e", "deptno")))
              .project(
                  builder.field("deptno"))
              .build())
      .returnsUnordered(
          "deptno=10",
          "deptno=10",
          "deptno=10");
}
 
Example #16
Source File: PigAdapterTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
@Test void testImplWithJoin() throws Exception {
  CalciteAssert.that()
      .with(MODEL)
      .query("select * from \"t\" join \"s\" on \"tc1\"=\"sc0\"")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
          planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE))
      .explainContains("PigToEnumerableConverter\n"
          + "  PigJoin(condition=[=($1, $2)], joinType=[inner])\n"
          + "    PigTableScan(table=[[PIG, t]])\n"
          + "    PigTableScan(table=[[PIG, s]])")
      .runs()
      .queryContains(
          pigScriptChecker("t = LOAD '"
              + getFullPathForTestDataFile("data.txt")
              + "' USING PigStorage() AS (tc0:chararray, tc1:chararray);\n"
              + "s = LOAD '" + getFullPathForTestDataFile("data2.txt")
              + "' USING PigStorage() AS (sc0:chararray, sc1:chararray);\n"
              + "t = JOIN t BY tc1 , s BY sc0;"));
}
 
Example #17
Source File: EnumerableCorrelateTest.java    From calcite with Apache License 2.0 6 votes vote down vote up
/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2930">[CALCITE-2930]
 * FilterCorrelateRule on a Correlate with SemiJoinType SEMI (or ANTI)
 * throws IllegalStateException</a> */
@Test void semiJoinCorrelateWithFilterCorrelateRule() {
  tester(false, new JdbcTest.HrSchema())
      .query(
          "select empid, name from emps e where e.deptno in (select d.deptno from depts d) and e.empid > 100")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        // force the semijoin to run via EnumerableCorrelate
        // instead of EnumerableHashJoin(SEMI),
        // and push the 'empid > 100' filter into the Correlate
        planner.addRule(JoinToCorrelateRule.INSTANCE);
        planner.addRule(FilterCorrelateRule.INSTANCE);
        planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
        planner.removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE);
      })
      .explainContains(""
          + "EnumerableCalc(expr#0..3=[{inputs}], empid=[$t1], name=[$t3])\n"
          + "  EnumerableCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{0}])\n"
          + "    EnumerableAggregate(group=[{0}])\n"
          + "      EnumerableTableScan(table=[[s, depts]])\n"
          + "    EnumerableCalc(expr#0..4=[{inputs}], expr#5=[$cor1], expr#6=[$t5.deptno], expr#7=[=($t6, $t1)], expr#8=[100], expr#9=[>($t0, $t8)], expr#10=[AND($t7, $t9)], proj#0..2=[{exprs}], $condition=[$t10])\n"
          + "      EnumerableTableScan(table=[[s, emps]])")
      .returnsUnordered(
          "empid=110; name=Theodore",
          "empid=150; name=Sebastian");
}
 
Example #18
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinLeftOuterJoinPushDownSort() {
  final String sql = "select * from\n"
      + "(select contactno, email from customer.contact_peek) r left outer join\n"
      + "(select acctno, type from customer.account) s\n"
      + "on r.contactno=s.acctno and r.email=s.type\n"
      + "order by r.contactno desc, r.email desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #19
Source File: SqlHintsConverterTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHintsPropagationInVolcanoPlannerRules() {
  final String sql = "select /*+ use_hash_join(r, s), use_hash_join(emp, dept) */\n"
      + "ename, job, sal, dept.name\n"
      + "from emp join dept on emp.deptno = dept.deptno";
  RelOptPlanner planner = new VolcanoPlanner();
  planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
  Tester tester1 = tester.withDecorrelation(true)
      .withClusterFactory(
        relOptCluster -> RelOptCluster.create(planner, relOptCluster.getRexBuilder()));
  final RelNode rel = tester1.convertSqlToRel(sql).rel;
  final RelHint hint = RelHint.builder("USE_HASH_JOIN")
      .inheritPath(0)
      .hintOption("EMP")
      .hintOption("DEPT")
      .build();
  // Validate Volcano planner.
  RuleSet ruleSet = RuleSets.ofList(
      new MockEnumerableJoinRule(hint), // Rule to validate the hint.
      FilterProjectTransposeRule.INSTANCE,
      FilterMergeRule.INSTANCE,
      ProjectMergeRule.INSTANCE,
      EnumerableRules.ENUMERABLE_JOIN_RULE,
      EnumerableRules.ENUMERABLE_PROJECT_RULE,
      EnumerableRules.ENUMERABLE_FILTER_RULE,
      EnumerableRules.ENUMERABLE_SORT_RULE,
      EnumerableRules.ENUMERABLE_LIMIT_RULE,
      EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
  Program program = Programs.of(ruleSet);
  RelTraitSet toTraits = rel
      .getCluster()
      .traitSet()
      .replace(EnumerableConvention.INSTANCE);

  program.run(planner, rel, toTraits,
      Collections.emptyList(), Collections.emptyList());
}
 
Example #20
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testNestedLoopJoinLeftOuterJoinPushDownSort() {
  final String sql = "select * from\n"
      + " customer.contact_peek r left outer join\n"
      + "customer.account s\n"
      + "on r.contactno>s.acctno and r.email<s.type\n"
      + "order by r.contactno desc, r.email desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #21
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinLeftOuterJoinPushDownSort2() {
  final String sql = "select * from\n"
      + "customer.contact_peek r left outer join\n"
      + "customer.account s\n"
      + "on r.contactno=s.acctno and r.email=s.type\n"
      + "order by r.fname desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #22
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinInnerJoinPushDownSort() {
  final String sql = "select * from\n"
      + "(select contactno, email from customer.contact_peek) r inner join\n"
      + "(select acctno, type from customer.account) s\n"
      + "on r.contactno=s.acctno and r.email=s.type\n"
      + "order by r.contactno desc, r.email desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #23
Source File: EnumerableBatchNestedLoopJoinTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void innerBatchJoinTestSQL() {
  tester(false, new JdbcTest.HrSchemaBig())
      .query(
          "select count(e.name) from emps e join depts d on d.deptno = e.deptno")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE);
      })
      .returnsUnordered("EXPR$0=46");
}
 
Example #24
Source File: EnumerableCorrelateTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void nonEquiAntiJoinCorrelate() {
  tester(false, new JdbcTest.HrSchema())
      .query("?")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        // force the antijoin to run via EnumerableCorrelate
        // instead of EnumerableNestedLoopJoin
        planner.addRule(JoinToCorrelateRule.INSTANCE);
        planner.removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE);
      })
      .withRel(
          // Retrieve employees with the top salary in their department. Equivalent SQL:
          //   SELECT e.name, e.salary FROM emps e
          //   WHERE NOT EXISTS (
          //     SELECT 1 FROM emps e2
          //     WHERE e.deptno = e2.deptno AND e2.salary > e.salary)
          builder -> builder
              .scan("s", "emps").as("e")
              .scan("s", "emps").as("e2")
              .antiJoin(
                  builder.and(
                      builder.equals(
                          builder.field(2, "e", "deptno"),
                          builder.field(2, "e2", "deptno")),
                      builder.call(
                          SqlStdOperatorTable.GREATER_THAN,
                          builder.field(2, "e2", "salary"),
                          builder.field(2, "e", "salary"))))
              .project(
                  builder.field("name"),
                  builder.field("salary"))
              .build())
      .returnsUnordered(
          "name=Theodore; salary=11500.0",
          "name=Eric; salary=8000.0");
}
 
Example #25
Source File: EnumerableBatchNestedLoopJoinTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void innerBatchJoinTestSQL2() {
  tester(false, new JdbcTest.HrSchemaBig())
      .query(
          "select count(e.name) from emps e join depts d on d.deptno = e.empid")
      .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner -> {
        planner.removeRule(EnumerableRules.ENUMERABLE_CORRELATE_RULE);
        planner.addRule(EnumerableRules.ENUMERABLE_BATCH_NESTED_LOOP_JOIN_RULE);
      })
      .returnsUnordered("EXPR$0=4");
}
 
Example #26
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinTraitDerivation() {
  final String sql = "select * from\n"
      + "(select ename, job, mgr from sales.emp order by ename desc, job desc, mgr limit 10) r\n"
      + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
      + "order by r.ename desc, r.job desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #27
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinTraitDerivation2() {
  final String sql = "select * from\n"
      + "(select ename, job, mgr from sales.emp order by mgr desc limit 10) r\n"
      + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
      + "order by r.mgr desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #28
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testHashJoinTraitDerivationNegativeCase() {
  final String sql = "select * from\n"
      + "(select ename, job, mgr from sales.emp order by mgr desc limit 10) r\n"
      + "join sales.bonus s on r.ename=s.ename and r.job=s.job\n"
      + "order by r.mgr";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #29
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testNestedLoopJoinTraitDerivation() {
  final String sql = "select * from\n"
      + "(select ename, job, mgr from sales.emp order by ename desc, job desc, mgr limit 10) r\n"
      + "join sales.bonus s on r.ename>s.ename and r.job<s.job\n"
      + "order by r.ename desc, r.job desc";

  Query.create(sql)
      .removeRule(EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .check();
}
 
Example #30
Source File: TopDownOptTest.java    From calcite with Apache License 2.0 5 votes vote down vote up
@Test void testSortCalcDerive3() {
  final String sql = "select * from\n"
      + "(select ename, cast(job as varchar) as job, sal + 1 from\n"
      + "(select ename, job, sal from sales.emp limit 100) t) r\n"
      + "join sales.bonus s on r.job=s.job and r.ename=s.ename";
  Query.create(sql)
      .addRule(ProjectToCalcRule.INSTANCE)
      .addRule(EnumerableRules.ENUMERABLE_CALC_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_SORT_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_PROJECT_RULE)
      .removeRule(EnumerableRules.ENUMERABLE_JOIN_RULE)
      .check();
}