org.apache.calcite.tools.ValidationException Java Examples

/**
 * Converts sql query string into query physical plan.
 * In case of any errors (that might occur due to missing function implementation),
 * checks if local function registry should be synchronized with remote function registry.
 * If sync took place, reloads drill operator table
 * (since functions were added to / removed from local function registry)
 * and attempts to converts sql query string into query physical plan one more time.
 *
 * @param context query context
 * @param sql sql query
 * @param textPlan text plan
 * @return query physical plan
 */
private static PhysicalPlan convertPlan(QueryContext context, String sql, Pointer<String> textPlan)
    throws ForemanSetupException, RelConversionException, IOException, ValidationException {
  Pointer<String> textPlanCopy = textPlan == null ? null : new Pointer<>(textPlan.value);
  try {
    return getQueryPlan(context, sql, textPlan);
  } catch (Exception e) {
    logger.trace("There was an error during conversion into physical plan. " +
        "Will sync remote and local function registries if needed and retry " +
        "in case if issue was due to missing function implementation.", e);
    if (context.getFunctionRegistry().syncWithRemoteRegistry(
        context.getDrillOperatorTable().getFunctionRegistryVersion())) {
      context.reloadDrillOperatorTable();
      logger.trace("Local function registry was synchronized with remote. Trying to find function one more time.");
      return getQueryPlan(context, sql, textPlanCopy);
    }
    throw e;
  }
}
 

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

public static UserException.Builder validationError(String query, ValidationException ex) {
  Throwable cause = ex;
  if (ex.getCause() != null) {
    // ValidationException generally wraps the "real" cause that we are interested in
    cause = ex.getCause();
  }
  UserException.Builder b = UserException.validationError(cause)
    .addContext(SQL_QUERY_CONTEXT, query);

  // CalciteContextException alters the error message including the start/end positions
  // we need to extract the original error message and add the remaining information as context
  if (cause instanceof CalciteContextException && cause.getCause() != null) {
    CalciteContextException cce = (CalciteContextException) cause;
    b.message(cce.getCause().getMessage())
            .addContext(START_LINE_CONTEXT, cce.getPosLine())
            .addContext(START_COLUMN_CONTEXT, cce.getPosColumn())
            .addContext(END_LINE_CONTEXT, cce.getEndPosLine())
            .addContext(END_COLUMN_CONTEXT, cce.getEndPosColumn());
  }
  return b;
}
 

@Override
public PhysicalPlan getPlan(SqlNode sqlNode) throws ValidationException, RelConversionException, IOException, ForemanSetupException {
  final ConvertedRelNode convertedRelNode = validateAndConvert(sqlNode);
  final RelDataType validatedRowType = convertedRelNode.getValidatedRowType();
  final RelNode queryRelNode = convertedRelNode.getConvertedNode();

  log("Calcite", queryRelNode, logger, null);
  DrillRel drel = convertToDrel(queryRelNode);

  if (mode == ResultMode.LOGICAL) {
    LogicalExplain logicalResult = new LogicalExplain(drel, level, context);
    return DirectPlan.createDirectPlan(context, logicalResult);
  }

  Prel prel = convertToPrel(drel, validatedRowType);
  logAndSetTextPlan("Drill Physical", prel, logger);
  PhysicalOperator pop = convertToPop(prel);
  PhysicalPlan plan = convertToPlan(pop);
  log("Drill Plan", plan, logger);
  PhysicalExplain physicalResult = new PhysicalExplain(prel, plan, level, context);
  return DirectPlan.createDirectPlan(context, physicalResult);
}
 

public static Exception coerceException(Logger logger, String sql, Exception e, boolean coerceToPlan){
  if(e instanceof UserException){
    return e;
  } else if(e instanceof ValidationException){
    throw validationError(sql, (ValidationException) e).build(logger);
  } else if (e instanceof AccessControlException){
  throw UserException.permissionError(e)
      .addContext(SQL_QUERY_CONTEXT, sql)
      .build(logger);
  } else if (e instanceof SqlUnsupportedException){
  throw UserException.unsupportedError(e)
      .addContext(SQL_QUERY_CONTEXT, sql)
      .build(logger);
  } else if (e instanceof IOException || e instanceof RelConversionException){
    return new QueryInputException("Failure handling SQL.", e);
  } else if (coerceToPlan){
    throw planError(sql, e).build(logger);
  }
  return e;
}
 

private RelNode getPlan(SqlHandlerConfig sqlHandlerConfig, List<String> refreshTablePath, PlanNormalizer planNormalizer) {
  SqlSelect select = new SqlSelect(
    SqlParserPos.ZERO,
    new SqlNodeList(SqlParserPos.ZERO),
    new SqlNodeList(ImmutableList.<SqlNode>of(SqlIdentifier.star(SqlParserPos.ZERO)), SqlParserPos.ZERO),
    new SqlIdentifier(refreshTablePath, SqlParserPos.ZERO),
    null,
    null,
    null,
    null,
    null,
    null,
    null
  );

  try {
    ConvertedRelNode converted = PrelTransformer.validateAndConvert(sqlHandlerConfig, select, planNormalizer);

    return converted.getConvertedNode();
  } catch (ForemanSetupException | RelConversionException | ValidationException e) {
    throw Throwables.propagate(SqlExceptionHelper.coerceException(logger, select.toString(), e, false));
  }
}
 

protected Pair<SqlNode, RelDataType> validateNode(SqlNode sqlNode) throws ValidationException, RelConversionException, ForemanSetupException {
  final SqlNode sqlNodeValidated = config.getConverter().validate(sqlNode);
  final Pair<SqlNode, RelDataType> typedSqlNode = new Pair<>(sqlNodeValidated, config.getConverter().getOutputType(
      sqlNodeValidated));

  // Check if the unsupported functionality is used
  UnsupportedOperatorsVisitor visitor = UnsupportedOperatorsVisitor.createVisitor(context);
  try {
    sqlNodeValidated.accept(visitor);
  } catch (UnsupportedOperationException ex) {
    // If the exception due to the unsupported functionalities
    visitor.convertException();

    // If it is not, let this exception move forward to higher logic
    throw ex;
  }

  return typedSqlNode;
}
 

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

/**
 * Update the schema path for the session.
 * @param newDefaultSchemaPath New default schema path to set. It could be relative to the current default schema or
 *                             absolute schema.
 * @param currentDefaultSchema Current default schema.
 * @throws ValidationException If the given default schema path is invalid in current schema tree.
 */
public void setDefaultSchemaPath(String newDefaultSchemaPath, SchemaPlus currentDefaultSchema)
    throws ValidationException {
  final List<String> newDefaultPathAsList = SchemaUtilites.getSchemaPathAsList(newDefaultSchemaPath);
  SchemaPlus newDefault;

  // First try to find the given schema relative to the current default schema.
  newDefault = SchemaUtilites.findSchema(currentDefaultSchema, newDefaultPathAsList);

  if (newDefault == null) {
    // If we fail to find the schema relative to current default schema, consider the given new default schema path as
    // absolute schema path.
    newDefault = SchemaUtilites.findSchema(currentDefaultSchema, newDefaultPathAsList);
  }

  if (newDefault == null) {
    SchemaUtilites.throwSchemaNotFoundException(currentDefaultSchema, newDefaultSchemaPath);
  }

  properties.setProperty(DrillProperties.SCHEMA, SchemaUtilites.getSchemaPath(newDefault));
}
 

public static RelNode resolveNewTableRel(boolean isNewTableView, List<String> tableFieldNames,
                                         RelDataType validatedRowtype, RelNode queryRelNode,
                                         boolean allowDuplicatesInSelect) throws ValidationException {

  validateRowType(isNewTableView, tableFieldNames, validatedRowtype);
  if (tableFieldNames.size() > 0) {
    return MoreRelOptUtil.createRename(queryRelNode, tableFieldNames);
  }

  if (!allowDuplicatesInSelect) {
    ensureNoDuplicateColumnNames(validatedRowtype.getFieldNames());
  }

  return queryRelNode;
}
 

private static void ensureNoDuplicateColumnNames(List<String> fieldNames) throws ValidationException {
  final HashSet<String> fieldHashSet = Sets.newHashSetWithExpectedSize(fieldNames.size());
  for(String field : fieldNames) {
    if (fieldHashSet.contains(field.toLowerCase())) {
      throw new ValidationException(String.format("Duplicate column name [%s]", field));
    }
    fieldHashSet.add(field.toLowerCase());
  }
}
 

protected ConvertedRelNode validateAndConvert(SqlNode sqlNode) throws ForemanSetupException, RelConversionException, ValidationException {
  final SqlNode rewrittenSqlNode = rewrite(sqlNode);
  final Pair<SqlNode, RelDataType> validatedTypedSqlNode = validateNode(rewrittenSqlNode);
  final SqlNode validated = validatedTypedSqlNode.getKey();

  RelNode rel = convertToRel(validated);
  rel = preprocessNode(rel);

  return new ConvertedRelNode(rel, validatedTypedSqlNode.getValue());
}
 

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

public static CalciteState sqlOverDummyTable(String sql)
        throws RelConversionException, ValidationException, SqlParseException {
    SchemaPlus schema = Frameworks.createRootSchema(true);
    JavaTypeFactory typeFactory = new JavaTypeFactoryImpl
            (RelDataTypeSystem.DEFAULT);
    StreamableTable streamableTable = new CompilerUtil.TableBuilderInfo(typeFactory)
            .field("ID", SqlTypeName.INTEGER)
            .field("NAME", typeFactory.createType(String.class))
            .field("ADDR", typeFactory.createType(String.class))
            .build();
    Table table = streamableTable.stream();
    schema.add("FOO", table);
    schema.add("BAR", table);
    schema.add("MYPLUS", ScalarFunctionImpl.create(MyPlus.class, "eval"));

    List<SqlOperatorTable> sqlOperatorTables = new ArrayList<>();
    sqlOperatorTables.add(SqlStdOperatorTable.instance());
    sqlOperatorTables.add(new CalciteCatalogReader(CalciteSchema.from(schema),
            false,
            Collections.<String>emptyList(), typeFactory));
    SqlOperatorTable chainedSqlOperatorTable = new ChainedSqlOperatorTable(sqlOperatorTables);
    FrameworkConfig config = Frameworks.newConfigBuilder().defaultSchema(
            schema).operatorTable(chainedSqlOperatorTable).build();
    Planner planner = Frameworks.getPlanner(config);
    SqlNode parse = planner.parse(sql);
    SqlNode validate = planner.validate(parse);
    RelNode tree = planner.convert(validate);
    System.out.println(RelOptUtil.toString(tree, SqlExplainLevel.ALL_ATTRIBUTES));
    return new CalciteState(schema, tree);
}
 

@Override
public PhysicalPlan getPlan(SqlNode sqlNode) throws ValidationException, RelConversionException, IOException, ForemanSetupException {
  SqlCreateView createView = unwrap(sqlNode, SqlCreateView.class);

  final String newViewName = FileSelection.removeLeadingSlash(createView.getName());

  // Disallow temporary tables usage in view definition
  config.getConverter().disallowTemporaryTables();
  // Store the viewSql as view def SqlNode is modified as part of the resolving the new table definition below.
  final String viewSql = createView.getQuery().toString();
  final ConvertedRelNode convertedRelNode = validateAndConvert(createView.getQuery());
  final RelDataType validatedRowType = convertedRelNode.getValidatedRowType();
  final RelNode queryRelNode = convertedRelNode.getConvertedNode();

  final RelNode newViewRelNode = SqlHandlerUtil.resolveNewTableRel(true, createView.getFieldNames(), validatedRowType, queryRelNode);

  final SchemaPlus defaultSchema = context.getNewDefaultSchema();
  final AbstractSchema drillSchema = SchemaUtilites.resolveToMutableDrillSchema(defaultSchema, createView.getSchemaPath());

  final View view = new View(newViewName, viewSql, newViewRelNode.getRowType(),
      SchemaUtilites.getSchemaPathAsList(defaultSchema));
  final String schemaPath = drillSchema.getFullSchemaName();

  // check view creation possibility
  if(!checkViewCreationPossibility(drillSchema, createView, context)) {
    return DirectPlan
      .createDirectPlan(context, false, String.format("A table or view with given name [%s] already exists in schema [%s]", view.getName(), schemaPath));
  }

  final boolean replaced = drillSchema.createView(view);
  final String summary = String.format("View '%s' %s successfully in '%s' schema",
      newViewName, replaced ? "replaced" : "created", drillSchema.getFullSchemaName());

  return DirectPlan.createDirectPlan(context, true, summary);
}
 

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

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

/** Populates the table called {@code name} by executing {@code query}. */
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(context.getRootSchema().plus())
      .build();
  final Planner planner = Frameworks.getPlanner(config);
  try {
    final StringBuilder buf = new StringBuilder();
    final SqlWriterConfig writerConfig =
        SqlPrettyWriter.config().withAlwaysUseParentheses(false);
    final SqlPrettyWriter w = new SqlPrettyWriter(writerConfig, 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);
  }
}
 

public SqlNode validate(SqlNode sqlNode) throws ValidationException {
  ensure(State.STATE_3_PARSED);
  this.validator = createSqlValidator(createCatalogReader());
  try {
    validatedSqlNode = validator.validate(sqlNode);
  } catch (RuntimeException e) {
    throw new ValidationException(e);
  }
  state = State.STATE_4_VALIDATED;
  return validatedSqlNode;
}
 

public Pair<SqlNode, RelDataType> validateAndGetType(SqlNode sqlNode)
    throws ValidationException {
  final SqlNode validatedNode = this.validate(sqlNode);
  final RelDataType type =
      this.validator.getValidatedNodeType(validatedNode);
  return Pair.of(validatedNode, type);
}
 

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 s.addQuery(root.project());
}
 

/** 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 void testCalciteCaseOracleException() {
  assertThrows(ValidationException.class, () -> {
    // Oracle 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.ORACLE, sql);
  });
}
 

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

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

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

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