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

@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");
}
 

/** 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"));
}
 

/** 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"));
}
 

/** 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"));
}
 

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));
}
 

@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");
}
 

/** 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"));
}
 

/** 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");
}
 

@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");
}
 

/** 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"));
}
 

/** 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"))));
}
 

/** 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"))));
  }
}
 

@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");
}
 

/** 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");
}
 

@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");
}
 

@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;"));
}
 

/** 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");
}
 

@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();
}
 

@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());
}
 

@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();
}
 

@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();
}
 

@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();
}
 

@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");
}
 

@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");
}
 

@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");
}
 

@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();
}
 

@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();
}
 

@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();
}
 

@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();
}
 

@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();
}