org.apache.calcite.tools.Programs Java Examples

/**
 * Converts a relational expression to a form where
 * {@link org.apache.calcite.rel.logical.LogicalJoin}s are
 * as close to leaves as possible.
 */
public static RelNode toLeafJoinForm(RelNode rel) {
  final Program program = Programs.hep(
      ImmutableList.of(
          JoinProjectTransposeRule.RIGHT_PROJECT,
          JoinProjectTransposeRule.LEFT_PROJECT,
          FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN,
          ProjectRemoveRule.INSTANCE,
          ProjectMergeRule.INSTANCE),
      false,
      DefaultRelMetadataProvider.INSTANCE);
  if (CalciteSystemProperty.DEBUG.value()) {
    System.out.println(
        RelOptUtil.dumpPlan("before", rel, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  final RelNode rel2 = program.run(null, rel, null,
      ImmutableList.of(),
      ImmutableList.of());
  if (CalciteSystemProperty.DEBUG.value()) {
    System.out.println(
        RelOptUtil.dumpPlan("after", rel2, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  return rel2;
}
 

@Test void testUseMergeJoin() {
  final String sql = "select /*+ use_merge_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);
  planner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
  Tester tester1 = tester.withDecorrelation(true)
      .withClusterFactory(
          relOptCluster -> RelOptCluster.create(planner, relOptCluster.getRexBuilder()));
  final RelNode rel = tester1.convertSqlToRel(sql).rel;
  RuleSet ruleSet = RuleSets.ofList(
      EnumerableRules.ENUMERABLE_MERGE_JOIN_RULE,
      EnumerableRules.ENUMERABLE_JOIN_RULE,
      EnumerableRules.ENUMERABLE_PROJECT_RULE,
      EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE,
      EnumerableRules.ENUMERABLE_SORT_RULE,
      AbstractConverter.ExpandConversionRule.INSTANCE);
  Program program = Programs.of(ruleSet);
  RelTraitSet toTraits = rel
      .getCluster()
      .traitSet()
      .replace(EnumerableConvention.INSTANCE);

  RelNode relAfter = program.run(planner, rel, toTraits,
      Collections.emptyList(), Collections.emptyList());

  String planAfter = NL + RelOptUtil.toString(relAfter);
  getDiffRepos().assertEquals("planAfter", "${planAfter}", planAfter);
}
 

private static void runProjectQueryWithLex(Lex lex, String sql)
    throws SqlParseException, ValidationException, RelConversionException {
  Config javaLex = SqlParser.configBuilder().setLex(lex).build();
  Planner planner = getPlanner(null, javaLex, Programs.ofRules(Programs.RULE_SET));
  SqlNode parse = planner.parse(sql);
  SqlNode validate = planner.validate(parse);
  RelNode convert = planner.rel(validate).rel;
  assertThat(convert, instanceOf(LogicalProject.class));
  List<RelDataTypeField> fields = convert.getRowType().getFieldList();
  // Get field type from sql text and validate we parsed it after validation.
  assertThat(fields.size(), is(4));
  assertThat(fields.get(0).getType().getSqlTypeName(), is(SqlTypeName.VARCHAR));
  assertThat(fields.get(1).getType().getSqlTypeName(), is(SqlTypeName.TIME));
  assertThat(fields.get(2).getType().getSqlTypeName(), is(SqlTypeName.INTEGER));
  assertThat(fields.get(3).getType().getSqlTypeName(), is(SqlTypeName.TIMESTAMP));
}
 

private static void runProjectQueryWithLex(Lex lex, String sql)
    throws SqlParseException, ValidationException, RelConversionException {
  Config javaLex = SqlParser.configBuilder().setLex(lex).build();
  Planner planner = getPlanner(null, javaLex, Programs.ofRules(Programs.RULE_SET));
  SqlNode parse = planner.parse(sql);
  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(transform, instanceOf(EnumerableProject.class));
  List<String> fieldNames = transform.getRowType().getFieldNames();
  assertThat(fieldNames.size(), is(2));
  if (lex.caseSensitive) {
    assertThat(fieldNames.get(0), is("EMPID"));
    assertThat(fieldNames.get(1), is("empid"));
  } else {
    assertThat(fieldNames.get(0) + "-" + fieldNames.get(1),
        anyOf(is("EMPID-empid0"), is("EMPID0-empid")));
  }
}
 

/**
 * The default schema that is used in these tests provides tables sorted on the primary key. Due
 * to this scan operators always come with a {@link org.apache.calcite.rel.RelCollation} trait.
 */
private RelNode transform(String sql, RuleSet prepareRules) throws Exception {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  final SchemaPlus defSchema = rootSchema.add("hr", new HrClusteredSchema());
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(defSchema)
      .traitDefs(ConventionTraitDef.INSTANCE, RelCollationTraitDef.INSTANCE)
      .programs(
          Programs.of(prepareRules),
          Programs.ofRules(SortRemoveRule.INSTANCE))
      .build();
  Planner planner = Frameworks.getPlanner(config);
  SqlNode parse = planner.parse(sql);
  SqlNode validate = planner.validate(parse);
  RelRoot planRoot = planner.rel(validate);
  RelNode planBefore = planRoot.rel;
  RelTraitSet desiredTraits = planBefore.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelNode planAfter = planner.transform(0, desiredTraits, planBefore);
  return planner.transform(1, desiredTraits, planAfter);
}
 

static HepPlanner createHepPlanner() {
    HepProgramBuilder builder = new HepProgramBuilder();
    // builder.addRuleInstance(FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN);
    // builder.addRuleInstance(FilterJoinRule.JOIN);
    builder.addRuleCollection(Programs.CALC_RULES);
    // builder.addRuleCollection(Programs.RULE_SET);
    // builder.addRuleInstance(ReduceExpressionsRule.PROJECT_INSTANCE); // 加上这个可以把100+100变成200，常量折叠
    // builder.addRuleInstance(ReduceExpressionsRule.FILTER_INSTANCE);
    // builder.addRuleInstance(FilterProjectTransposeRule.INSTANCE);

    // HepMatchOrder order = HepMatchOrder.TOP_DOWN;
    // builder.addMatchOrder(order);
    // builder.addConverters(true);

    HepPlanner hepPlanner = new HepPlanner(builder.build());

    hepPlanner.addRelTraitDef(ConventionTraitDef.INSTANCE);
    hepPlanner.addRelTraitDef(RelCollationTraitDef.INSTANCE);
    return hepPlanner;
}
 

private List<RelNode> optimizePlans(List<RelNode> originalRels,
    List<RelOptRule> rules) {
  final RelOptPlanner planner = originalRels.get(0).getCluster().getPlanner();
  // Remember old rule set of the planner before resetting it with new rules
  final List<RelOptRule> oldRules = planner.getRules();
  resetPlannerRules(planner, rules);
  final Program program = Programs.of(RuleSets.ofList(planner.getRules()));
  final List<RelNode> optimizedPlans = new ArrayList<>();
  for (RelNode rel : originalRels) {
    final RelCollation collation = rel instanceof Sort
        ? ((Sort) rel).collation
        : RelCollations.EMPTY;
    // Apply the planner to obtain the physical plan
    final RelNode physicalPlan = program.run(planner, rel,
        rel.getTraitSet().replace(EnumerableConvention.INSTANCE)
            .replace(collation).simplify(),
        ImmutableList.of(), ImmutableList.of());

    // Then convert the physical plan back to logical plan
    final RelNode logicalPlan = new ToLogicalConverter(builder).visit(physicalPlan);
    optimizedPlans.add(logicalPlan);
  }
  resetPlannerRules(planner, oldRules);
  return optimizedPlans;
}
 

public GremlinCompiler(Graph graph, SchemaConfig schemaConfig) {
    this.graph = graph;
    this.schemaConfig = schemaConfig;

    final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
    final List<RelTraitDef> traitDefs = new ArrayList<>();
    traitDefs.add(ConventionTraitDef.INSTANCE);
    traitDefs.add(RelCollationTraitDef.INSTANCE);
    final SqlParser.Config parserConfig =
            SqlParser.configBuilder().setLex(Lex.MYSQL).build();

    frameworkConfig = Frameworks.newConfigBuilder()
            .parserConfig(parserConfig)
            .defaultSchema(rootSchema.add("gremlin", new GremlinSchema(graph, schemaConfig)))
            .traitDefs(traitDefs)
            .programs(Programs.sequence(Programs.ofRules(Programs.RULE_SET), Programs.CALC_PROGRAM))
            .build();
}
 

public static RelNode toPhysical(RelNode rel, Consumer<RelOptPlanner> setting) {
    final RelOptPlanner planner = rel.getCluster().getPlanner();
    planner.clear();
    setting.accept(planner);
    planner.addRule(new RelOptRule(operand(MycatTransientSQLTableScan.class, none()), RelFactories.LOGICAL_BUILDER, "MycatTransientSQLTableScan") {

        @Override
        public void onMatch(RelOptRuleCall call) {
            final MycatTransientSQLTableScan scan = call.rel(0);
            final RelOptTable table = scan.getTable();
            if (Bindables.BindableTableScan.canHandle(table)) {
                call.transformTo(
                        Bindables.BindableTableScan.create(scan.getCluster(), table));
            }
        }
    });
    final Program program = Programs.of(RuleSets.ofList(planner.getRules()));
    return program.run(planner, rel, rel.getTraitSet().replace(EnumerableConvention.INSTANCE),
            ImmutableList.of(), ImmutableList.of());
}
 

/**
 * Converts a relational expression to a form where
 * {@link org.apache.calcite.rel.logical.LogicalJoin}s are
 * as close to leaves as possible.
 */
public static RelNode toLeafJoinForm(RelNode rel) {
  final Program program = Programs.hep(
      ImmutableList.of(
          JoinProjectTransposeRule.RIGHT_PROJECT,
          JoinProjectTransposeRule.LEFT_PROJECT,
          FilterJoinRule.FilterIntoJoinRule.FILTER_ON_JOIN,
          ProjectRemoveRule.INSTANCE,
          ProjectMergeRule.INSTANCE),
      false,
      DefaultRelMetadataProvider.INSTANCE);
  if (CalciteSystemProperty.DEBUG.value()) {
    System.out.println(
        RelOptUtil.dumpPlan("before", rel, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  final RelNode rel2 = program.run(null, rel, null,
      ImmutableList.of(),
      ImmutableList.of());
  if (CalciteSystemProperty.DEBUG.value()) {
    System.out.println(
        RelOptUtil.dumpPlan("after", rel2, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  return rel2;
}
 

private static Consumer<Holder<Program>> handler(
    final boolean bushy, final int minJoinCount) {
  return holder -> holder.set(
      Programs.sequence(
          Programs.heuristicJoinOrder(Programs.RULE_SET, bushy,
              minJoinCount),
          Programs.CALC_PROGRAM));
}
 

static void testPrograms(RelNode relNode) {
    final RelOptPlanner planner = relNode.getCluster().getPlanner();
    final Program program = Programs.ofRules(ReduceExpressionsRule.PROJECT_INSTANCE);
    relNode = program.run(planner, relNode, relNode.getTraitSet(), ImmutableList.of(), ImmutableList.of());
    String plan = RelOptUtil.toString(relNode);
    System.out.println(plan);
}
 

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

/** Creates a config based on the "scott" schema. */
public static Frameworks.ConfigBuilder config() {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  return Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(
          CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
      .traitDefs((List<RelTraitDef>) null)
      .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
}
 

/** Creates a config based on the "foodmart" schema. */
public static Frameworks.ConfigBuilder config() {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  return Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(
          CalciteAssert.addSchema(rootSchema,
              CalciteAssert.SchemaSpec.JDBC_FOODMART))
      .traitDefs((List<RelTraitDef>) null)
      .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
}
 

protected List<RelNode> optimize(TestConfig testConfig) {
  RelNode queryRel = testConfig.queryRel;
  RelOptPlanner planner = queryRel.getCluster().getPlanner();
  RelTraitSet traitSet = queryRel.getCluster().traitSet()
      .replace(EnumerableConvention.INSTANCE);
  RelOptUtil.registerDefaultRules(planner, true, false);
  return ImmutableList.of(
      Programs.standard().run(
          planner, queryRel, traitSet, testConfig.materializations, ImmutableList.of()));
}
 

@SuppressWarnings("Guava") // Must conform to Calcite's API
public static Function<Holder<Program>, Void> prepend(Program program) {
	return (holder) -> {
		if (holder == null) {
			throw new IllegalStateException("No program holder");
		}
		Program chain = holder.get();
		if (chain == null) {
			chain = Programs.standard();
		}
		holder.set(new SequenceProgram(program, chain));
		return null;
	};
}
 

public static Frameworks.ConfigBuilder config() {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  return Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(
          CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.SCOTT_WITH_TEMPORAL))
      .traitDefs((List<RelTraitDef>) null)
      .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
}
 

private static RelNode toPhysical(RelNode rel) {
  final RelOptPlanner planner = rel.getCluster().getPlanner();
  planner.clear();
  for (RelOptRule rule : RULE_SET) {
    planner.addRule(rule);
  }

  final Program program = Programs.of(RuleSets.ofList(planner.getRules()));
  return program.run(planner, rel, rel.getTraitSet().replace(EnumerableConvention.INSTANCE),
      ImmutableList.of(), ImmutableList.of());
}
 

protected Program getProgram() {
  // Allow a test to override the default program.
  final Holder<Program> holder = Holder.of(null);
  Hook.PROGRAM.run(holder);
  if (holder.get() != null) {
    return holder.get();
  }

  return Programs.standard();
}
 

public Frameworks.ConfigBuilder config() throws Exception {
  final Holder<SchemaPlus> root = Holder.of(null);
  CalciteAssert.model(TPCDS_MODEL)
      .doWithConnection(connection -> {
        root.set(connection.getRootSchema().getSubSchema("TPCDS"));
      });
  return Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(root.get())
      .traitDefs((List<RelTraitDef>) null)
      .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
}
 

private List<Program> getPrograms() {
  ImmutableList.Builder<Program> builder
      = ImmutableList.builder();
  for (RuleSet ruleSet: getRules()) {
    builder.add(Programs.sequence(
        new EnumerableProgram(ruleSet, this.context, this.plannerHolder),
        Programs.CALC_PROGRAM));
  }
  return builder.build();
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2941">[CALCITE-2941]
 * EnumerableLimitRule on Sort with no collation creates EnumerableLimit with
 * wrong traitSet and cluster</a>.
 */
@Test void enumerableLimitOnEmptySort() throws Exception {
  RuleSet prepareRules =
      RuleSets.ofList(
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE,
          EnumerableRules.ENUMERABLE_LIMIT_RULE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
  SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  SchemaPlus defSchema = rootSchema.add("hr", new HrClusteredSchema());
  FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(defSchema)
      .traitDefs(ConventionTraitDef.INSTANCE, RelCollationTraitDef.INSTANCE)
      .programs(Programs.of(prepareRules))
      .build();

  RelBuilder builder = RelBuilder.create(config);
  RelNode planBefore = builder
      .scan("hr", "emps")
      .sort(builder.field(0)) // will produce collation [0] in the plan
      .filter(
          builder.notEquals(
              builder.field(0),
              builder.literal(100)))
      .limit(1, 5) // force a limit inside an "empty" Sort (with no collation)
      .build();

  RelTraitSet desiredTraits = planBefore.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  Program program = Programs.of(prepareRules);
  RelNode planAfter = program.run(planBefore.getCluster().getPlanner(), planBefore,
      desiredTraits, ImmutableList.of(), ImmutableList.of());

  // verify that the collation [0] is not lost in the final plan
  final RelCollation collation =
      planAfter.getTraitSet().getTrait(RelCollationTraitDef.INSTANCE);
  assertThat(collation, notNullValue());
  final List<RelFieldCollation> fieldCollationList =
      collation.getFieldCollations();
  assertThat(fieldCollationList, notNullValue());
  assertThat(fieldCollationList.size(), is(1));
  assertThat(fieldCollationList.get(0).getFieldIndex(), is(0));
}
 

private PlannerResult runPlanner(String defaultTableSpace, String query) throws RelConversionException,
        SqlParseException, ValidationException, MetadataStorageManagerException, StatementExecutionException {
    SchemaPlus subSchema = getSchemaForTableSpace(defaultTableSpace);
    if (subSchema == null) {
        clearCache();
        throw new StatementExecutionException("tablespace " + defaultTableSpace + " is not available");
    }
    Properties props = new Properties();
    props.put(CalciteConnectionProperty.TIME_ZONE.camelName(), TimeZone.getDefault().getID());
    props.put(CalciteConnectionProperty.LOCALE.camelName(), Locale.ROOT.toString());
    final CalciteConnectionConfigImpl calciteRuntimeContextConfig = new CalciteConnectionConfigImpl(props);

    final FrameworkConfig config = Frameworks.newConfigBuilder()
            .parserConfig(SQL_PARSER_CONFIG)
            .defaultSchema(subSchema)
            .traitDefs(TRAITS)
            .context(new Context() {
                @Override
                public <C> C unwrap(Class<C> aClass) {
                    if (aClass == CalciteConnectionConfigImpl.class
                            || aClass == CalciteConnectionConfig.class) {
                        return (C) calciteRuntimeContextConfig;
                    }
                    return null;
                }
            })
            // define the rules you want to apply
            .programs(Programs.ofRules(Programs.RULE_SET))
            .build();
    Planner planner = Frameworks.getPlanner(config);
    if (LOG.isLoggable(Level.FINER)) {
        LOG.log(Level.FINER, "Query: {0}", query);
    }
    try {
        SqlNode n = planner.parse(query);
        n = planner.validate(n);
        RelNode logicalPlan = planner.rel(n).project();
        if (LOG.isLoggable(DUMP_QUERY_LEVEL)) {
            LOG.log(DUMP_QUERY_LEVEL, "Query: {0} {1}", new Object[]{query,
                RelOptUtil.dumpPlan("-- Logical Plan", logicalPlan, SqlExplainFormat.TEXT,
                SqlExplainLevel.ALL_ATTRIBUTES)});
        }
        RelDataType originalRowType = logicalPlan.getRowType();
        RelOptCluster cluster = logicalPlan.getCluster();
        final RelOptPlanner optPlanner = cluster.getPlanner();

        optPlanner.addRule(ReduceExpressionsRule.FILTER_INSTANCE);
        RelTraitSet desiredTraits =
                cluster.traitSet()
                        .replace(EnumerableConvention.INSTANCE);
        final RelCollation collation =
                logicalPlan instanceof Sort
                        ? ((Sort) logicalPlan).collation
                        : null;
        if (collation != null) {
            desiredTraits = desiredTraits.replace(collation);
        }
        final RelNode newRoot = optPlanner.changeTraits(logicalPlan, desiredTraits);
        optPlanner.setRoot(newRoot);
        RelNode bestExp = optPlanner.findBestExp();
        if (LOG.isLoggable(DUMP_QUERY_LEVEL)) {
            LOG.log(DUMP_QUERY_LEVEL, "Query: {0} {1}", new Object[]{query,
                RelOptUtil.dumpPlan("-- Best  Plan", bestExp, SqlExplainFormat.TEXT,
                SqlExplainLevel.ALL_ATTRIBUTES)});
        }

        return new PlannerResult(bestExp, originalRowType, logicalPlan, n);
    } catch (AssertionError err) {
        throw new StatementExecutionException("Internal Calcite error " + err, err);
    }
}