org.apache.calcite.sql.parser.SqlParseException Java Examples

@Test
public void testParamFinder() throws SqlParseException {
    SqlParser sqlParser1 = SqlParser.create("POWER($0, $1) + AVG(LN($3)) + EXP($5)");
    SqlNode sqlPattern = sqlParser1.parseExpression();
    SqlParser sqlParser2 = SqlParser.create("POWER(3, POWER(2, POWER(2, 3))) + AVG(LN(EXP(4))) + EXP(CAST('2018-03-22' AS DATE))");
    SqlNode sqlCall = sqlParser2.parseExpression();

    SqlParamsFinder sqlParamsFinder = new SqlParamsFinder((SqlCall)sqlPattern, (SqlCall)sqlCall);
    Map<Integer, SqlNode> paramNodes =  sqlParamsFinder.getParamNodes();

    Assert.assertEquals("3", paramNodes.get(0).toString());
    Assert.assertEquals("POWER(2, POWER(2, 3))", paramNodes.get(1).toString());
    Assert.assertEquals("EXP(4)", paramNodes.get(3).toString());
    Assert.assertEquals("CAST('2018-03-22' AS DATE)", paramNodes.get(5).toString());

}
 

@Test void testValidateFails() throws SqlParseException {
  Planner planner = getPlanner(null);
  SqlNode parse =
      planner.parse("select * from \"emps\" where \"Xname\" like '%e%'");
  assertThat(Util.toLinux(parse.toString()),
      equalTo("SELECT *\n"
          + "FROM `emps`\n"
          + "WHERE `Xname` LIKE '%e%'"));

  try {
    SqlNode validate = planner.validate(parse);
    fail("expected error, got " + validate);
  } catch (ValidationException e) {
    assertThat(Throwables.getStackTraceAsString(e),
        containsString("Column 'Xname' not found in any table"));
    // ok
  }
}
 

public Parser getParser(String sql, Properties info)
    throws SQLException {
  SqlParser parser = SqlParser.create(sql,
      SqlParser.configBuilder()
          .setQuotedCasing(Casing.UNCHANGED)
          .setUnquotedCasing(Casing.UNCHANGED)
          .setQuoting(Quoting.DOUBLE_QUOTE)
          .setParserFactory(QuarkParserImpl.FACTORY)
          .build());
  SqlNode sqlNode;
  try {
    sqlNode = parser.parseStmt();
  } catch (SqlParseException e) {
    throw new RuntimeException(
        "parse failed: " + e.getMessage(), e);
  }
  if (sqlNode.getKind().equals(SqlKind.OTHER_DDL)) {
    return new DDLParser();
  } else  {
    return getSqlQueryParser(info);
  }
}
 

@Test
public void convertExceptionToQueryErrors() {
  // Fake logger to not pollute logs
  org.slf4j.Logger logger = mock(org.slf4j.Logger.class);

  SqlParseException parseException = new SqlParseException("test message", new SqlParserPos(7, 42, 13, 57), null, null, null);
  UserException userException = SqlExceptionHelper.parseError("SELECT FOO", parseException)
      .build(logger);

  List<QueryError> errors = QueryError.of(userException);

  assertEquals(1, errors.size());

  QueryError error = errors.get(0);
  assertEquals("test message", error.getMessage());
  assertEquals(7, error.getRange().getStartLine());
  assertEquals(42, error.getRange().getStartColumn());
  assertEquals(13, error.getRange().getEndLine());
  assertEquals(57, error.getRange().getEndColumn());
}
 

@Test
public void convertExceptionToFailureInfo() {
  // Fake logger to not pollute logs
  org.slf4j.Logger logger = mock(org.slf4j.Logger.class);

  SqlParseException parseException = new SqlParseException("test message", new SqlParserPos(7, 42, 13, 57), null, null, null);
  UserException userException = SqlExceptionHelper.parseError("SELECT FOO", parseException)
      .build(logger);
  String verboseError = userException.getVerboseMessage(false);

  JobFailureInfo jobFailureInfo = JobsServiceUtil.toFailureInfo(verboseError);
  assertEquals(JobFailureInfo.Type.PARSE, jobFailureInfo.getType());

  assertEquals(1, jobFailureInfo.getErrorsList().size());

  JobFailureInfo.Error error = jobFailureInfo.getErrorsList().get(0);
  assertEquals("test message", error.getMessage());
  assertEquals(7, (int) error.getStartLine());
  assertEquals(42, (int) error.getStartColumn());
  assertEquals(13, (int) error.getEndLine());
  assertEquals(57, (int) error.getEndColumn());
}
 

private void emptyUnions32(UnionMergeRule... extraRules)
    throws SqlParseException, ValidationException, RelConversionException {
  String plan = "EnumerableUnion(all=[true])\n"
      + "  EnumerableValues(tuples=[[{ 1 }]])\n"
      + "  EnumerableValues(tuples=[[{ 2 }]])\n";

  checkUnionPruning("values(1)"
          + " union all values(2)"
          + " union all select * from (values(3)) where false",
      plan, extraRules);

  checkUnionPruning("values(1)"
          + " union all select * from (values(3)) where false"
          + " union all values(2)",
      plan, extraRules);

  checkUnionPruning("select * from (values(2)) where false"
          + " union all values(1)"
          + " union all values(2)",
      plan, extraRules);
}
 

@Test
public void testInvalidNoSourceTable() throws SqlParseException {
	thrown.expect(SqlParseException.class);
	thrown.expectMessage("Encountered \"(\" at line 4, column 6.\n" +
		"Was expecting one of:\n" +
		"    <BRACKET_QUOTED_IDENTIFIER> ...\n" +
		"    <QUOTED_IDENTIFIER> ...\n" +
		"    <BACK_QUOTED_IDENTIFIER> ...\n" +
		"    <IDENTIFIER> ...\n" +
		"    <UNICODE_QUOTED_IDENTIFIER> ...\n");
	createFlinkParser(
		"CREATE TABLE t (\n" +
			"   a STRING\n" +
			")\n" +
			"LIKE (" +
			"   INCLUDING ALL" +
			")")
		.parseStmt();
}
 

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

public static void parse(List<String> sqls, List<String> sources, List<String> udfs, List<String> sinks)
    throws SqlParseException {
    for (String sql : sqls) {
        SqlParser sqlParser = SqlParser.create(sql, CONFIG);
        SqlNode sqlNode = sqlParser.parseStmt();
        SqlKind kind = sqlNode.getKind();
        switch (kind){
            case INSERT:
                SqlInsert sqlInsert = (SqlInsert)sqlNode;
                addSink(sinks, findSinkName(sqlInsert));
                SqlSelect source = (SqlSelect) sqlInsert.getSource();
                parseSource(source, sources, udfs);
                break;
            case SELECT:
                parseSource((SqlSelect) sqlNode, sources, udfs);
                break;
            default:
                throw new IllegalArgumentException("It must be an insert SQL, sql:" + sql);
        }
    }
}
 

protected void checkParseEx(Throwable e, String expectedMsgPattern, String sql) {
  try {
    throw e;
  } catch (SqlParseException spe) {
    String errMessage = spe.getMessage();
    if (expectedMsgPattern == null) {
      throw new RuntimeException("Error while parsing query:" + sql, spe);
    } else if (errMessage == null
        || !errMessage.matches(expectedMsgPattern)) {
      throw new RuntimeException("Error did not match expected ["
          + expectedMsgPattern + "] while parsing query ["
          + sql + "]", spe);
    }
  } catch (Throwable t) {
    throw new RuntimeException("Error while parsing query: " + sql, t);
  }
}
 

private RelNode toRel(RelOptCluster cluster, SchemaPlus rootSchema,
    SchemaPlus defaultSchema, String sql) throws SqlParseException {
  final SqlParser parser = SqlParser.create(sql, SqlParser.Config.DEFAULT);
  final SqlNode parsed = parser.parseStmt();

  final CalciteCatalogReader catalogReader = new CalciteCatalogReader(
      CalciteSchema.from(rootSchema),
      CalciteSchema.from(defaultSchema).path(null),
      new JavaTypeFactoryImpl(), new CalciteConnectionConfigImpl(new Properties()));

  final SqlValidator validator = new ValidatorForTest(SqlStdOperatorTable.instance(),
      catalogReader, new JavaTypeFactoryImpl(), SqlConformanceEnum.DEFAULT);
  final SqlNode validated = validator.validate(parsed);
  final SqlToRelConverter.Config config = SqlToRelConverter.configBuilder()
      .withTrimUnusedFields(true)
      .withExpand(true)
      .withDecorrelationEnabled(true)
      .build();
  final SqlToRelConverter converter = new SqlToRelConverter(
      (rowType, queryString, schemaPath, viewPath) -> {
        throw new UnsupportedOperationException("cannot expand view");
      }, validator, catalogReader, cluster, StandardConvertletTable.INSTANCE, config);
  return converter.convertQuery(validated, false, true).rel;
}
 

@Override public RelRoot expandView(RelDataType rowType, String queryString,
    List<String> schemaPath, List<String> viewPath) {
  expansionDepth++;

  SqlParser parser = prepare.createParser(queryString);
  SqlNode sqlNode;
  try {
    sqlNode = parser.parseQuery();
  } catch (SqlParseException e) {
    throw new RuntimeException("parse failed", e);
  }
  // View may have different schema path than current connection.
  final CatalogReader catalogReader =
      this.catalogReader.withSchemaPath(schemaPath);
  SqlValidator validator = createSqlValidator(catalogReader);
  final SqlToRelConverter.Config config = SqlToRelConverter.configBuilder()
          .withTrimUnusedFields(true).build();
  SqlToRelConverter sqlToRelConverter =
      getSqlToRelConverter(validator, catalogReader, config);
  RelRoot root =
      sqlToRelConverter.convertQuery(sqlNode, true, false);

  --expansionDepth;
  return root;
}
 

/**
 * Attempts to parse a SQL statement and adds to the errorList if any syntax
 * error is found. This implementation uses {@link SqlParser}. Subclass can
 * re-implement this with a different parser implementation
 *
 * @param sql       A user-input sql statement to be parsed
 * @param errorList A {@link List} of error to be added to
 * @return {@link SqlNode } that is root of the parse tree, null if the sql
 * is not valid
 */
protected SqlNode collectParserError(
    String sql,
    List<ValidateErrorInfo> errorList) {
  try {
    return parseQuery(sql);
  } catch (SqlParseException e) {
    ValidateErrorInfo errInfo =
        new ValidateErrorInfo(
            e.getPos(),
            e.getMessage());

    // parser only returns 1 exception now
    errorList.add(errInfo);
    return null;
  }
}
 

public void parser(String query, List<CreateTable> batchTablesList)
{
    Map<String, CreateTable> batchTables = batchTablesList.stream()
            .collect(Collectors.toMap(CreateTable::getName, v -> v));
    CalciteSqlParser sqlParser = new CalciteSqlParser(batchTables.keySet());

    List<Object> plan;
    try {
        plan = sqlParser.getPlan(query, sqlParserConfig);
    }
    catch (SqlParseException e) {
        throw throwsException(e);
    }

    List<String> registerViews = new ArrayList<>();
    try {
        translate(plan, batchTables, registerViews);
    }
    finally {
        //registerViews.forEach(tableName -> tableEnv.sqlQuery("drop table " + tableName));
    }
}
 

public static SqlNode getOnlySelectNode(String sql) {
    SqlNodeList selectList = null;
    try {
        selectList = ((SqlSelect) CalciteParser.parse(sql)).getSelectList();
    } catch (SqlParseException e) {
        throw new RuntimeException(
                "Failed to parse expression \'" + sql + "\', please make sure the expression is valid", e);
    }

    Preconditions.checkArgument(selectList.size() == 1,
            "Expression is invalid because size of select list exceeds one");

    return selectList.get(0);
}
 

List<Lattice> addQuery(String q) throws SqlParseException,
    ValidationException, RelConversionException {
  final Planner planner = new PlannerImpl(config);
  final SqlNode node = planner.parse(q);
  final SqlNode node2 = planner.validate(node);
  final RelRoot root = planner.rel(node2);
  return suggester.addQuery(root.project());
}
 

private RelNode convertSqlToRel(String sql, boolean simplifyRex) {
  final FrameworkConfig config = Frameworks.newConfigBuilder()
          .defaultSchema(CalciteSchema.createRootSchema(false).plus())
          .parserConfig(SqlParser.configBuilder().build())
          .build();
  final Planner planner = Frameworks.getPlanner(config);
  try (Closer closer = new Closer()) {
    closer.add(Hook.REL_BUILDER_SIMPLIFY.addThread(Hook.propertyJ(simplifyRex)));
    final SqlNode parsed = planner.parse(sql);
    final SqlNode validated = planner.validate(parsed);
    return planner.rel(validated).rel;
  } catch (SqlParseException | RelConversionException | ValidationException e) {
    throw TestUtil.rethrow(e);
  }
}
 

/** Populates the table called {@code name} by executing {@code query}. */
protected static void populate(SqlIdentifier name, SqlNode query,
    CalcitePrepare.Context context) {
  // Generate, prepare and execute an "INSERT INTO table query" statement.
  // (It's a bit inefficient that we convert from SqlNode to SQL and back
  // again.)
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .defaultSchema(
          Objects.requireNonNull(
              Schemas.subSchema(context.getRootSchema(),
                  context.getDefaultSchemaPath())).plus())
      .build();
  final Planner planner = Frameworks.getPlanner(config);
  try {
    final StringBuilder buf = new StringBuilder();
    final SqlPrettyWriter w =
        new SqlPrettyWriter(
            SqlPrettyWriter.config()
                .withDialect(CalciteSqlDialect.DEFAULT)
                .withAlwaysUseParentheses(false),
            buf);
    buf.append("INSERT INTO ");
    name.unparse(w, 0, 0);
    buf.append(" ");
    query.unparse(w, 0, 0);
    final String sql = buf.toString();
    final SqlNode query1 = planner.parse(sql);
    final SqlNode query2 = planner.validate(query1);
    final RelRoot r = planner.rel(query2);
    final PreparedStatement prepare = context.getRelRunner().prepare(r.rel);
    int rowCount = prepare.executeUpdate();
    Util.discard(rowCount);
    prepare.close();
  } catch (SqlParseException | ValidationException
      | RelConversionException | SQLException e) {
    throw new RuntimeException(e);
  }
}
 

@Test
public void testSqlParsingOfLimitClauseForRedShift() throws QuarkException, SQLException, SqlParseException {
  String query = "select product_id from product limit 100 offset 10";
  final SqlDialect redshiftDialect =
      SqlDialect.getProduct("REDSHIFT", null).getDialect();
  QuarkTestUtil.checkSqlParsing(
      query,
      info,
      "SELECT \"PRODUCT_ID\" "
          + "FROM \"PRODUCT\" "
          + "LIMIT 100 OFFSET 10",
      redshiftDialect);
}
 

private void executeQuery(FrameworkConfig config,
    @SuppressWarnings("SameParameterValue") String query, boolean debug)
    throws RelConversionException, SqlParseException, ValidationException {
  Planner planner = Frameworks.getPlanner(config);
  if (debug) {
    System.out.println("Query:" + query);
  }
  SqlNode n = planner.parse(query);
  n = planner.validate(n);
  RelNode root = planner.rel(n).project();
  if (debug) {
    System.out.println(
        RelOptUtil.dumpPlan("-- Logical Plan", root, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  RelOptCluster cluster = root.getCluster();
  final RelOptPlanner optPlanner = cluster.getPlanner();

  RelTraitSet desiredTraits  =
      cluster.traitSet().replace(EnumerableConvention.INSTANCE);
  final RelNode newRoot = optPlanner.changeTraits(root, desiredTraits);
  if (debug) {
    System.out.println(
        RelOptUtil.dumpPlan("-- Mid Plan", newRoot, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
  optPlanner.setRoot(newRoot);
  RelNode bestExp = optPlanner.findBestExp();
  if (debug) {
    System.out.println(
        RelOptUtil.dumpPlan("-- Best Plan", bestExp, SqlExplainFormat.TEXT,
            SqlExplainLevel.DIGEST_ATTRIBUTES));
  }
}
 

@Test
public void testWindowCallParams() throws SqlParseException {
    SqlParser sqlParser1 = SqlParser.create("STDDEV_POP($0) OVER($1)");
    SqlNode sqlPattern = sqlParser1.parseExpression();
    SqlParser sqlParser2 = SqlParser.create("STDDEV_POP(C1) OVER (ORDER BY C1)");
    SqlNode sqlCall = sqlParser2.parseExpression();
    SqlParamsFinder sqlParamsFinder = SqlParamsFinder.newInstance((SqlCall)sqlPattern, (SqlCall)sqlCall, true);
    Map<Integer, SqlNode> paramNodes =  sqlParamsFinder.getParamNodes();

    Assert.assertEquals("C1", paramNodes.get(0).toString());
    Assert.assertEquals("(ORDER BY `C1`)", paramNodes.get(1).toString());
    Assert.assertTrue(paramNodes.get(1) instanceof SqlWindow);
}
 

@Override
public boolean isValid(final String query) {
    String checkedQuery = trimQuery(query);
    Boolean result = null;
    try {
        SqlNode parsedNode = SqlParser.create(checkedQuery).parseQuery();
        result = parsedNode.accept(whitelistVisitor);
        if (result == null) {
            result = true;
        }
    } catch (SqlParseException e) {
        return false;
    }
    return result;
}
 

/**
 * Parses a SQL statement into a {@link SqlNode}. The {@link SqlNode} is not yet validated.
 *
 * @param sql a sql string to parse
 * @return a parsed sql node
 * @throws SqlParserException if an exception is thrown when parsing the statement
 */
public SqlNode parse(String sql) {
	try {
		SqlParser parser = SqlParser.create(sql, config);
		return parser.parseStmt();
	} catch (SqlParseException e) {
		throw new SqlParserException("SQL parse failed. " + e.getMessage(), e);
	}
}
 

/**
 * Parses a SQL statement into a {@link SqlNode}. The {@link SqlNode} is not yet validated.
 *
 * @param sql a sql string to parse
 * @return a parsed sql node
 * @throws SqlParserException if an exception is thrown when parsing the statement
 */
public SqlNode parse(String sql) {
	try {
		SqlParser parser = SqlParser.create(sql, config);
		return parser.parseStmt();
	} catch (SqlParseException e) {
		throw new SqlParserException("SQL parse failed. " + e.getMessage(), e);
	}
}
 

/** Parses a query returns its graph. */
LatticeRootNode node(String q) throws SqlParseException,
    ValidationException, RelConversionException {
  final List<Lattice> list = addQuery(q);
  assertThat(list.size(), is(1));
  return list.get(0).rootNode;
}
 

@Test
public void testBasics() throws SQLException, SqlParseException {
    Assert.assertNotNull(connector);
    Assert.assertNotNull(connector.getJdbcDriver());
    Assert.assertNotNull(connector.getJdbcUrl());
    Assert.assertNotNull(connector.getJdbcUser());
    Assert.assertNotNull(connector.getJdbcPassword());

    try (Connection conn = connector.getConnection()) {
        Assert.assertNotNull(conn);
        Assert.assertTrue(!conn.isClosed());
    }

    Assert.assertNotNull(connector.convertSql("select 1"));
    Assert.assertFalse(connector.listDatabases().isEmpty());
    Assert.assertFalse(connector.listDatabases().contains("EDW"));
    Assert.assertTrue(connector.listDatabases().contains("DEFAULT"));
    Assert.assertFalse(connector.listTables("DEFAULT").isEmpty());
    Assert.assertTrue(connector.listColumns("DEFAULT", "TEST_KYLIN_FACT").next());
    Assert.assertNotNull(connector.buildSqlToCreateSchema("NEW_SCHEMA"));
    Assert.assertNotNull(connector.buildSqlToCreateTable("NEW_TABLE", new LinkedHashMap<String, String>()));
    Assert.assertNotNull(connector.buildSqlToCreateView("NEW_VIEW", "select 1"));
    Assert.assertNotNull(connector.buildSqlToLoadDataFromLocal("TABLE", "/tmp"));

    connector.executeUpdate("select 1"); // expected no exceptions

    SqlConverter.IConfigurer configurer = connector.getSqlConverter().getConfigurer();
    Assert.assertTrue(configurer.allowFetchNoRows());
    Assert.assertTrue(configurer.allowNoOffset());
    Assert.assertTrue(configurer.allowNoOrderByWithFetch());
    Assert.assertFalse(configurer.skipHandleDefault());
    Assert.assertFalse(configurer.skipDefaultConvert());

    Assert.assertEquals(Types.DOUBLE, connector.toKylinTypeId("DOUBLE PRECISION", 0));
    Assert.assertEquals(Types.DOUBLE, connector.toKylinTypeId("double PRECISION", 0));
    Assert.assertEquals(Types.VARCHAR, connector.toKylinTypeId("CHARACTER VARYING", 0));
}
 

@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 SqlNode parse(final Reader reader) throws SqlParseException {
    switch (state) {
        case STATE_0_CLOSED:
        case STATE_1_RESET:
            ready();
    }
    ensure(State.STATE_2_READY);
    SqlParser parser = SqlParser.create(reader, parserConfig);
    SqlNode sqlNode = parser.parseStmt();
    state = State.STATE_3_PARSED;
    return sqlNode;
}
 

public void parse() throws SqlParseException {
    switch (state) {
        case STATE_0_CLOSED:
        case STATE_1_RESET:
            ready();
    }
    ensure(State.STATE_2_READY);
    state = State.STATE_3_PARSED;
}
 

@Override
public RelRoot expandView(RelDataType rowType, String queryString,
                          List<String> schemaPath, List<String> viewPath) {
    if (planner == null) {
        ready();
    }
    SqlParser parser = SqlParser.create(queryString, parserConfig);
    SqlNode sqlNode;
    try {
        sqlNode = parser.parseQuery();
    } catch (SqlParseException e) {
        throw new RuntimeException("parse failed", e);
    }

    final CalciteCatalogReader catalogReader =
            createCatalogReader().withSchemaPath(schemaPath);
    final SqlValidator validator = createSqlValidator(catalogReader);

    final RexBuilder rexBuilder = createRexBuilder();
    final RelOptCluster cluster = RelOptCluster.create(planner, rexBuilder);
    final SqlToRelConverter.Config config = SqlToRelConverter
            .configBuilder()
            .withConfig(sqlToRelConverterConfig)
            .withTrimUnusedFields(false)
            .build();
    final SqlToRelConverter sqlToRelConverter =
            new SqlToRelConverter(this, validator,
                    catalogReader, cluster, convertletTable, config);

    final RelRoot root =
            sqlToRelConverter.convertQuery(sqlNode, true, false);
    final RelRoot root2 =
            root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
    final RelBuilder relBuilder =
            config.getRelBuilderFactory().create(cluster, null);
    return root2.withRel(
            RelDecorrelator.decorrelateQuery(root.rel, relBuilder));
}