org.apache.calcite.config.Lex Java Examples

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

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

public String checkTpchQuery(String tpchTestQuery) throws Exception {
  final SchemaPlus schema =
      Frameworks.createRootSchema(true).add("tpch",
          new ReflectiveSchema(new TpchSchema()));

  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.configBuilder().setLex(Lex.MYSQL).build())
      .defaultSchema(schema)
      .programs(Programs.ofRules(Programs.RULE_SET))
      .build();
  String plan;
  try (Planner p = Frameworks.getPlanner(config)) {
    SqlNode n = p.parse(tpchTestQuery);
    n = p.validate(n);
    RelNode r = p.rel(n).project();
    plan = RelOptUtil.toString(r);
  }
  return plan;
}
 

/**
 * Returns the SQL parser config for this environment including a custom Calcite configuration.
 */
private SqlParser.Config getSqlParserConfig() {
	return JavaScalaConversionUtil.<SqlParser.Config>toJava(getCalciteConfig(tableConfig).getSqlParserConfig()).orElseGet(
			// we use Java lex because back ticks are easier than double quotes in programming
			// and cases are preserved
			() -> {
				SqlConformance conformance = getSqlConformance();
				return SqlParser
						.configBuilder()
						.setParserFactory(FlinkSqlParserFactories.create(conformance))
						.setConformance(conformance)
						.setLex(Lex.JAVA)
						.setIdentifierMaxLength(256)
						.build();
			}
	);
}
 

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

@WithLex(Lex.SQL_SERVER) @Test void testNestSchemaSqlServer() throws Exception {
  String sql;
  sql = "select * from SALES.N^";
  assertComplete(
      sql,
      "SCHEMA(CATALOG.SALES.NEST)\n",
      "N",
      ImmutableMap.of("SCHEMA(CATALOG.SALES.NEST)", "NEST"));

  sql = "select * from SALES.[n^asfasdf";
  assertComplete(
      sql,
      "SCHEMA(CATALOG.SALES.NEST)\n",
      "[n",
      ImmutableMap.of("SCHEMA(CATALOG.SALES.NEST)", "[NEST]"));

  sql = "select * from SALES.[N^est";
  assertComplete(
      sql,
      "SCHEMA(CATALOG.SALES.NEST)\n",
      "[N",
      ImmutableMap.of("SCHEMA(CATALOG.SALES.NEST)", "[NEST]"));

  sql = "select * from SALES.NU^";
  assertComplete(sql, "", "NU");
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-580">[CALCITE-580]
 * Average aggregation on an Integer column throws ClassCastException</a>. */
@Test void testAvgInt() throws Exception {
  CalciteAssert.that().withSchema("s", CATCHALL).with(Lex.JAVA)
      .query("select primitiveLong, avg(primitiveInt)\n"
          + "from s.everyTypes\n"
          + "group by primitiveLong order by primitiveLong")
      .returns(input -> {
        StringBuilder buf = new StringBuilder();
        try {
          while (input.next()) {
            buf.append(input.getInt(2)).append("\n");
          }
        } catch (SQLException e) {
          throw TestUtil.rethrow(e);
        }
        assertThat(buf.toString(), equalTo("0\n2147483647\n"));
      });
}
 

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

@Test void testCalciteCaseJoinJava()
    throws SqlParseException, ValidationException, RelConversionException {
  String sql = "select t.empid as EMPID, s.empid from\n"
      + "(select * from emps where emps.deptno > 100) t join\n"
      + "(select * from emps where emps.deptno < 200) s on t.empid = s.empid";
  runProjectQueryWithLex(Lex.JAVA, sql);
}
 

public StreamlineParser(String s) {
  this.impl = new StreamlineParserImpl(new StringReader(s));
  this.impl.setTabSize(1);
  this.impl.setQuotedCasing(Lex.ORACLE.quotedCasing);
  this.impl.setUnquotedCasing(Lex.ORACLE.unquotedCasing);
  this.impl.setIdentifierMaxLength(DEFAULT_IDENTIFIER_MAX_LENGTH);
  /*
   *  By default parser uses [ ] for quoting identifiers. Switching to DQID (double quoted identifiers)
   *  is needed for array and map access (m['x'] = 1 or arr[2] = 10 etc) to work.
   */
  this.impl.switchTo("DQID");
}
 

@Test void testCalciteCaseJoinSqlServer()
    throws SqlParseException, ValidationException, RelConversionException {
  String sql = "select t.empid as EMPID, s.empid from\n"
      + "(select * from emps where emps.deptno > 100) t join\n"
      + "(select * from emps where emps.deptno < 200) s on t.empid = s.empid";
  runProjectQueryWithLex(Lex.SQL_SERVER, sql);
}
 

@Test void testCalciteCaseJoinMySqlAnsi()
    throws SqlParseException, ValidationException, RelConversionException {
  String sql = "select t.empid as EMPID, s.empid from\n"
      + "(select * from emps where emps.deptno > 100) t join\n"
      + "(select * from emps where emps.deptno < 200) s on t.empid = s.empid";
  runProjectQueryWithLex(Lex.MYSQL_ANSI, sql);
}
 

@Test void testCalciteCaseJoinMySql()
    throws SqlParseException, ValidationException, RelConversionException {
  String sql = "select t.empid as EMPID, s.empid from\n"
      + "(select * from emps where emps.deptno > 100) t join\n"
      + "(select * from emps where emps.deptno < 200) s on t.empid = s.empid";
  runProjectQueryWithLex(Lex.MYSQL, sql);
}
 

@Test void testCalciteCaseJoinOracle()
    throws SqlParseException, ValidationException, RelConversionException {
  String sql = "select t.\"empid\" as EMPID, s.\"empid\" from\n"
      + "(select * from \"emps\" where \"emps\".\"deptno\" > 100) t join\n"
      + "(select * from \"emps\" where \"emps\".\"deptno\" < 200) s\n"
      + "on t.\"empid\" = s.\"empid\"";
  runProjectQueryWithLex(Lex.ORACLE, sql);
}
 

@WithLex(Lex.SQL_SERVER) @Test void testMssql() {
  String sql =
      "select 1 from [emp] union select 2 from [DEPT] a where ^ and deptno < 5";
  String simplified =
      "SELECT * FROM [DEPT] a WHERE _suggest_ and deptno < 5";
  assertSimplify(sql, simplified);
  assertComplete(sql, EXPR_KEYWORDS, Collections.singletonList("TABLE(a)"), DEPT_COLUMNS);
}
 

@Test void testCalciteCaseJavaException() {
  assertThrows(ValidationException.class, () -> {
    // JAVA is case sensitive, so EMPID should not be found.
    String sql = "select EMPID, empid from\n"
        + " (select empid from emps order by emps.deptno)";
    runProjectQueryWithLex(Lex.JAVA, sql);
  });
}
 

/**
 * Method method build a calcite framework configuration for calcite to parse SQL and generate relational tree
 * out of it.
 * @return FrameworkConfig
 */
private FrameworkConfig buildFrameWorkConfig()
{
  List<SqlOperatorTable> sqlOperatorTables = new ArrayList<>();
  sqlOperatorTables.add(SqlStdOperatorTable.instance());
  sqlOperatorTables
    .add(new CalciteCatalogReader(CalciteSchema.from(schema), false, Collections.<String>emptyList(), typeFactory));
  return Frameworks.newConfigBuilder().defaultSchema(schema)
    .parserConfig(SqlParser.configBuilder().setLex(Lex.MYSQL).build())
    .operatorTable(new ChainedSqlOperatorTable(sqlOperatorTables)).build();
}
 

private CalciteConnection createConnection() {
    final Properties properties = new Properties();
    properties.put(CalciteConnectionProperty.LEX.camelName(), Lex.MYSQL_ANSI.name());

    try {
        final Connection connection = DriverManager.getConnection("jdbc:calcite:", properties);
        return connection.unwrap(CalciteConnection.class);
    } catch (final Exception e) {
        throw new ProcessException(e);
    }
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-569">[CALCITE-569]
 * ArrayIndexOutOfBoundsException when deducing collation</a>. */
@Test void testOrderByNonSelectColumn() throws Exception {
  final SchemaPlus schema = Frameworks.createRootSchema(true)
      .add("tpch", new ReflectiveSchema(new TpchSchema()));

  String query = "select t.psPartkey from\n"
      + "(select ps.psPartkey from `tpch`.`partsupp` ps\n"
      + "order by ps.psPartkey, ps.psSupplyCost) t\n"
      + "order by t.psPartkey";

  List<RelTraitDef> traitDefs = new ArrayList<>();
  traitDefs.add(ConventionTraitDef.INSTANCE);
  traitDefs.add(RelCollationTraitDef.INSTANCE);
  final SqlParser.Config parserConfig =
      SqlParser.configBuilder().setLex(Lex.MYSQL).build();
  FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(parserConfig)
      .defaultSchema(schema)
      .traitDefs(traitDefs)
      .programs(Programs.ofRules(Programs.RULE_SET))
      .build();
  String plan;
  try (Planner p = Frameworks.getPlanner(config)) {
    SqlNode n = p.parse(query);
    n = p.validate(n);
    RelNode r = p.rel(n).project();
    plan = RelOptUtil.toString(r);
    plan = Util.toLinux(plan);
  }
  assertThat(plan,
      equalTo("LogicalSort(sort0=[$0], dir0=[ASC])\n"
      + "  LogicalProject(psPartkey=[$0])\n"
      + "    LogicalTableScan(table=[[tpch, partsupp]])\n"));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2894">[CALCITE-2894]
 * NullPointerException thrown by RelMdPercentageOriginalRows when explaining
 * plan with all attributes</a>. */
@Test void testExplainAllAttributesSemiJoinUnionCorrelate() {
  CalciteAssert.that()
          .with(CalciteConnectionProperty.LEX, Lex.JAVA)
          .with(CalciteConnectionProperty.FORCE_DECORRELATE, false)
          .withSchema("s", new ReflectiveSchema(new JdbcTest.HrSchema()))
          .query(
                  "select deptno, name from depts where deptno in (\n"
                          + " select e.deptno from emps e where exists (select 1 from depts d where d.deptno=e.deptno)\n"
                          + " union select e.deptno from emps e where e.salary > 10000) ")
          .explainMatches("including all attributes ",
                  CalciteAssert.checkResultContains("EnumerableCorrelate"));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-1372">[CALCITE-1372]
 * JDBC adapter generates SQL with wrong field names</a>. */
@Test void testJoinPlan2() {
  final String sql = "SELECT v1.deptno, v2.deptno\n"
      + "FROM Scott.dept v1 LEFT JOIN Scott.emp v2 ON v1.deptno = v2.deptno\n"
      + "WHERE v2.job LIKE 'PRESIDENT'";
  CalciteAssert.model(JdbcTest.SCOTT_MODEL)
      .with(Lex.MYSQL)
      .query(sql).runs()
      .returnsCount(1);
}
 

private CalciteAssert.AssertThat tester(boolean forceDecorrelate,
                                        Object schema) {
  return CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
      .withSchema("s", new ReflectiveSchema(schema));
}
 

private CalciteAssert.AssertThat tester(boolean forceDecorrelate,
    Object schema) {
  return CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
      .withSchema("s", new ReflectiveSchema(schema));
}
 

private CalciteAssert.AssertThat tester(boolean forceDecorrelate,
    Object schema) {
  return CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
      .withSchema("s", new ReflectiveSchema(schema));
}
 

private CalciteAssert.AssertThat tester(boolean forceDecorrelate,
    Object schema) {
  return CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
      .withSchema("s", new ReflectiveSchema(schema));
}
 

private CalciteAssert.AssertThat tester(boolean forceDecorrelate,
    Object schema) {
  return CalciteAssert.that()
      .with(CalciteConnectionProperty.LEX, Lex.JAVA)
      .with(CalciteConnectionProperty.FORCE_DECORRELATE, forceDecorrelate)
      .withSchema("s", new ReflectiveSchema(schema));
}
 

private CalciteConnection createConnection() {
    final Properties properties = new Properties();
    properties.put(CalciteConnectionProperty.LEX.camelName(), Lex.MYSQL_ANSI.name());

    try {
        final Connection connection = DriverManager.getConnection("jdbc:calcite:", properties);
        final CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        return calciteConnection;
    } catch (final Exception e) {
        throw new ProcessException(e);
    }
}
 

@Override
public ValidationResult validate(final String subject, final String input, final ValidationContext context) {
    if (context.isExpressionLanguagePresent(input)) {
        return new ValidationResult.Builder()
            .input(input)
            .subject(subject)
            .valid(true)
            .explanation("Expression Language Present")
            .build();
    }

    final String substituted = context.newPropertyValue(input).evaluateAttributeExpressions().getValue();

    final Config config = SqlParser.configBuilder()
        .setLex(Lex.MYSQL_ANSI)
        .build();

    final SqlParser parser = SqlParser.create(substituted, config);
    try {
        parser.parseStmt();
        return new ValidationResult.Builder()
            .subject(subject)
            .input(input)
            .valid(true)
            .build();
    } catch (final Exception e) {
        return new ValidationResult.Builder()
            .subject(subject)
            .input(input)
            .valid(false)
            .explanation("Not a valid SQL Statement: " + e.getMessage())
            .build();
    }
}
 

@Override
public ValidationResult validate(final String subject, final String input, final ValidationContext context) {
    if (context.isExpressionLanguagePresent(input)) {
        return new ValidationResult.Builder()
                .input(input)
                .subject(subject)
                .valid(true)
                .explanation("Expression Language Present")
                .build();
    }

    final String substituted = context.newPropertyValue(input).evaluateAttributeExpressions().getValue();

    final SqlParser.Config config = SqlParser.configBuilder()
            .setLex(Lex.MYSQL_ANSI)
            .build();

    final SqlParser parser = SqlParser.create(substituted, config);
    try {
        parser.parseStmt();
        return new ValidationResult.Builder()
                .subject(subject)
                .input(input)
                .valid(true)
                .build();
    } catch (final Exception e) {
        return new ValidationResult.Builder()
                .subject(subject)
                .input(input)
                .valid(false)
                .explanation("Not a valid SQL Statement: " + e.getMessage())
                .build();
    }
}
 

public ConfigBuilder setLex(Lex lex) {
  setCaseSensitive(lex.caseSensitive);
  setUnquotedCasing(lex.unquotedCasing);
  setQuotedCasing(lex.quotedCasing);
  setQuoting(lex.quoting);
  return this;
}