org.apache.calcite.sql.validate.SqlConformanceEnum Java Examples

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

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2323">[CALCITE-2323]
 * Validator should allow alternative nullCollations for ORDER BY in
 * OVER</a>. */
@Test void testUserDefinedOrderByOver() {
  String sql = "select deptno,\n"
      + "  rank() over(partition by empno order by deptno)\n"
      + "from emp\n"
      + "order by row_number() over(partition by empno order by deptno)";
  Properties properties = new Properties();
  properties.setProperty(
      CalciteConnectionProperty.DEFAULT_NULL_COLLATION.camelName(),
      NullCollation.LOW.name());
  CalciteConnectionConfigImpl connectionConfig =
      new CalciteConnectionConfigImpl(properties);
  TesterImpl tester = new TesterImpl(getDiffRepos(), false, false, true, false, true,
      null, null, SqlToRelConverter.Config.DEFAULT,
      SqlConformanceEnum.DEFAULT, Contexts.of(connectionConfig));
  sql(sql).with(tester).ok();
}
 

@Test void testCrossApply() {
  final String q1 = "select *\n"
      + "from (values 2, 5) as t (c)\n"
      + "cross apply table(\"s\".\"fibonacci2\"(c))";
  final String q2 = "select *\n"
      + "from (values 2, 5) as t (c)\n"
      + "cross apply table(\"s\".\"fibonacci2\"(t.c))";
  for (String q : new String[] {q1, q2}) {
    with()
        .with(CalciteConnectionProperty.CONFORMANCE,
            SqlConformanceEnum.LENIENT)
        .query(q)
        .returnsUnordered("C=2; N=1",
            "C=2; N=1",
            "C=2; N=2",
            "C=5; N=1",
            "C=5; N=1",
            "C=5; N=2",
            "C=5; N=3",
            "C=5; N=5");
  }
}
 

/**
 * Creates a TesterImpl.
 *
 * @param diffRepos Diff repository
 * @param enableDecorrelate Whether to decorrelate
 * @param enableTrim Whether to trim unused fields
 * @param enableExpand Whether to expand sub-queries
 * @param catalogReaderFactory Function to create catalog reader, or null
 * @param clusterFactory Called after a cluster has been created
 */
protected TesterImpl(DiffRepository diffRepos, boolean enableDecorrelate,
    boolean enableTrim, boolean enableExpand,
    boolean enableLateDecorrelate,
    boolean enableTypeCoercion,
    SqlTestFactory.MockCatalogReaderFactory
        catalogReaderFactory,
    Function<RelOptCluster, RelOptCluster> clusterFactory) {
  this(diffRepos, enableDecorrelate, enableTrim, enableExpand,
      enableLateDecorrelate,
      enableTypeCoercion,
      catalogReaderFactory,
      clusterFactory,
      SqlToRelConverter.Config.DEFAULT,
      SqlConformanceEnum.DEFAULT,
      Contexts.empty());
}
 

Tester tpcds() {
  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  final double scaleFactor = 0.01d;
  final SchemaPlus schema =
      rootSchema.add("tpcds", new TpcdsSchema(scaleFactor));
  final FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .context(
          Contexts.of(
              new CalciteConnectionConfigImpl(new Properties())
                  .set(CalciteConnectionProperty.CONFORMANCE,
                      SqlConformanceEnum.LENIENT.name())))
      .defaultSchema(schema)
      .build();
  return withConfig(config);
}
 

private static Object convertDefault(Object defaultValue, String propertyName,
    Class<?> propertyType) {
  if (propertyType.equals(SqlConformance.class)) {
    // Workaround for SqlConformance because it is actually not a Enum.
    propertyType = SqlConformanceEnum.class;
  }
  if (defaultValue == null || !propertyType.isEnum()) {
    return defaultValue;
  }
  for (Object enumConstant : propertyType.getEnumConstants()) {
    if (((Enum) enumConstant).name().equals(defaultValue)) {
      return enumConstant;
    }
  }
  throw new IllegalArgumentException("property '" + propertyName
      + "' is an enum but its default value " + defaultValue
      + " is not a valid enum constant");
}
 

/** Returns the {@link SqlConformance} that matches this dialect.
 *
 * <p>The base implementation returns its best guess, based upon
 * {@link #databaseProduct}; sub-classes may override. */
@Nonnull public SqlConformance getConformance() {
  switch (databaseProduct) {
  case UNKNOWN:
  case CALCITE:
    return SqlConformanceEnum.DEFAULT;
  case BIG_QUERY:
    return SqlConformanceEnum.BIG_QUERY;
  case MYSQL:
    return SqlConformanceEnum.MYSQL_5;
  case ORACLE:
    return SqlConformanceEnum.ORACLE_10;
  case MSSQL:
    return SqlConformanceEnum.SQL_SERVER_2008;
  default:
    return SqlConformanceEnum.PRAGMATIC_2003;
  }
}
 

private static SqlParser getSqlParser(String sql) {
  // TODO: Check if this can be converted to static or thread local.
  SqlParser.ConfigBuilder parserBuilder = SqlParser.configBuilder();
  parserBuilder.setLex(PINOT_LEX);

  // BABEL is a very liberal conformance value that allows anything supported by any dialect
  parserBuilder.setConformance(SqlConformanceEnum.BABEL);
  parserBuilder.setParserFactory(SqlBabelParserImpl.FACTORY);

  return SqlParser.create(sql, parserBuilder.build());
}
 

static Pair<SqlNode, SqlValidator> testSqlValidator() throws Exception {
    String sql = "select * from my_schema.test where f1=1 or f2=2 order by f3 limit 2";
    sql = "select * from test";
    sql = "select * from my_schema2.test2";
    sql = "select sum(f1),max(f2) from test";

    sql = "select t1.f1,sum(Distinct f1) as sumf1 from test as t1 "
            + "where f2>20 group by f1 having f1>10 order by f1 limit 2";
    // sql = "insert into test(f1,f2,f3) values(1,2,3)";
    // sql = "update test set f1=100 where f2>10";
    // sql = "delete from test where f2>10";
    SqlNode sqlNode = parse(sql);

    SqlOperatorTable opTab = SqlStdOperatorTable.instance();
    RelDataTypeFactory typeFactory = createJavaTypeFactory();
    SqlValidatorCatalogReader catalogReader = createCalciteCatalogReader(typeFactory);
    SqlConformance conformance = SqlConformanceEnum.DEFAULT;

    List<String> names = new ArrayList<>();
    names.add("my_schema");
    names.add("test");
    catalogReader.getTable(names);

    SqlValidator sqlValidator = SqlValidatorUtil.newValidator(opTab, catalogReader, typeFactory, conformance);
    sqlNode = sqlValidator.validate(sqlNode);
    // System.out.println(sqlNode);

    sql = "insert into test(f1,f2,f3) values(1,2,3)";
    // sqlNode = parse(sql);
    // sqlNode = sqlValidator.validate(sqlNode);

    return new Pair<>(sqlNode, sqlValidator);
}
 

public AssertThat with(Config config) {
  switch (config) {
  case EMPTY:
    return EMPTY;
  case REGULAR:
    return with(SchemaSpec.HR, SchemaSpec.REFLECTIVE_FOODMART,
        SchemaSpec.POST);
  case REGULAR_PLUS_METADATA:
    return with(SchemaSpec.HR, SchemaSpec.REFLECTIVE_FOODMART);
  case GEO:
    return with(SchemaSpec.GEO)
        .with(CalciteConnectionProperty.CONFORMANCE,
            SqlConformanceEnum.LENIENT);
  case LINGUAL:
    return with(SchemaSpec.LINGUAL);
  case JDBC_FOODMART:
    return with(CalciteAssert.SchemaSpec.JDBC_FOODMART);
  case FOODMART_CLONE:
    return with(SchemaSpec.CLONE_FOODMART);
  case JDBC_FOODMART_WITH_LATTICE:
    return with(SchemaSpec.JDBC_FOODMART_WITH_LATTICE);
  case JDBC_SCOTT:
    return with(SchemaSpec.JDBC_SCOTT);
  case SCOTT:
    return with(SchemaSpec.SCOTT);
  case SPARK:
    return with(CalciteConnectionProperty.SPARK, true);
  case AUX:
    return with(SchemaSpec.AUX, SchemaSpec.POST);
  default:
    throw Util.unexpected(config);
  }
}
 

@Test void testWindowAndGroupByWithDynamicStar() {
  final String sql = "SELECT\n"
      + "n_regionkey,\n"
      + "MAX(MIN(n_nationkey)) OVER (PARTITION BY n_regionkey)\n"
      + "FROM (SELECT * FROM SALES.NATION)\n"
      + "GROUP BY n_regionkey";

  sql(sql).conformance(new SqlDelegatingConformance(SqlConformanceEnum.DEFAULT) {
    @Override public boolean isGroupByAlias() {
      return true;
    }
  }).with(getTesterWithDynamicTable()).ok();
}
 

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-3789">[CALCITE-3789]
 * Support validation of UNNEST multiple array columns like Presto</a>.
 */
@Test public void testAliasUnnestArrayPlanWithDoubleColumn() {
  final String sql = "select d.deptno, e, k.empno\n"
      + "from dept_nested_expanded as d CROSS JOIN\n"
      + " UNNEST(d.admins, d.employees) as t(e, k)";
  sql(sql).conformance(SqlConformanceEnum.PRESTO).ok();
}
 

/**
 * Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-3789">[CALCITE-3789]
 * Support validation of UNNEST multiple array columns like Presto</a>.
 */
@Test public void testAliasUnnestArrayPlanWithSingleColumn() {
  final String sql = "select d.deptno, employee.empno\n"
      + "from dept_nested_expanded as d,\n"
      + " UNNEST(d.employees) as t(employee)";
  sql(sql).conformance(SqlConformanceEnum.PRESTO).ok();
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2004">[CALCITE-2004]
 * Wrong plan generated for left outer apply with table function</a>. */
@Test void testLeftOuterApply() {
  final String sql = "select *\n"
      + "from (values 4) as t (c)\n"
      + "left join lateral table(\"s\".\"fibonacci2\"(c)) as R(n) on c=n";
  with()
      .with(CalciteConnectionProperty.CONFORMANCE,
          SqlConformanceEnum.LENIENT)
      .query(sql)
      .returnsUnordered("C=4; N=null");
}
 

public SqlTester withConformance(SqlConformance conformance) {
  if (conformance == null) {
    conformance = SqlConformanceEnum.DEFAULT;
  }
  final SqlTester tester = with("conformance", conformance);
  if (conformance instanceof SqlConformanceEnum) {
    return tester
        .withConnectionFactory(
            CalciteAssert.EMPTY_CONNECTION_FACTORY
                .with(CalciteConnectionProperty.CONFORMANCE, conformance));
  } else {
    return tester;
  }
}
 

private static String compile(RexBuilder rexBuilder, List<RexNode> constExps,
    RexToLixTranslator.InputGetter getter, RelDataType rowType) {
  final RexProgramBuilder programBuilder =
      new RexProgramBuilder(rowType, rexBuilder);
  for (RexNode node : constExps) {
    programBuilder.addProject(
        node, "c" + programBuilder.getProjectList().size());
  }
  final JavaTypeFactoryImpl javaTypeFactory =
      new JavaTypeFactoryImpl(rexBuilder.getTypeFactory().getTypeSystem());
  final BlockBuilder blockBuilder = new BlockBuilder();
  final ParameterExpression root0_ =
      Expressions.parameter(Object.class, "root0");
  final ParameterExpression root_ = DataContext.ROOT;
  blockBuilder.add(
      Expressions.declare(
          Modifier.FINAL, root_,
          Expressions.convert_(root0_, DataContext.class)));
  final SqlConformance conformance = SqlConformanceEnum.DEFAULT;
  final RexProgram program = programBuilder.getProgram();
  final List<Expression> expressions =
      RexToLixTranslator.translateProjects(program, javaTypeFactory,
          conformance, blockBuilder, null, root_, getter, null);
  blockBuilder.add(
      Expressions.return_(null,
          Expressions.newArrayInit(Object[].class, expressions)));
  final MethodDeclaration methodDecl =
      Expressions.methodDecl(Modifier.PUBLIC, Object[].class,
          BuiltInMethod.FUNCTION1_APPLY.method.getName(),
          ImmutableList.of(root0_), blockBuilder.toBlock());
  String code = Expressions.toString(methodDecl);
  if (CalciteSystemProperty.DEBUG.value()) {
    Util.debugCode(System.out, code);
  }
  return code;
}
 

private Result tvfImplementorBasedImplement(
    EnumerableRelImplementor implementor, Prefer pref) {
  final JavaTypeFactory typeFactory = implementor.getTypeFactory();
  final BlockBuilder builder = new BlockBuilder();
  final EnumerableRel child = (EnumerableRel) getInputs().get(0);
  final Result result =
      implementor.visitChild(this, 0, child, pref);
  final PhysType physType = PhysTypeImpl.of(
      typeFactory, getRowType(), pref.prefer(result.format));
  final Expression inputEnumerable = builder.append(
      "_input", result.block, false);
  final SqlConformance conformance =
      (SqlConformance) implementor.map.getOrDefault("_conformance",
          SqlConformanceEnum.DEFAULT);

  builder.add(
      RexToLixTranslator.translateTableFunction(
          typeFactory,
          conformance,
          builder,
          DataContext.ROOT,
          (RexCall) getCall(),
          inputEnumerable,
          result.physType,
          physType
      )
  );

  return implementor.result(physType, builder.toBlock());
}
 

private static Map<String, Object> buildDefaultOptions() {
	final Map<String, Object> m = new HashMap<>();
	m.put("quoting", Quoting.BACK_TICK);
	m.put("quotedCasing", Casing.UNCHANGED);
	m.put("unquotedCasing", Casing.UNCHANGED);
	m.put("caseSensitive", true);
	m.put("enableTypeCoercion", false);
	m.put("conformance", SqlConformanceEnum.DEFAULT);
	m.put("operatorTable", SqlStdOperatorTable.instance());
	m.put("parserFactory", FlinkSqlParserImpl.FACTORY);
	return Collections.unmodifiableMap(m);
}
 

public static TableEnv getTableEnv() {
    TableConfig tableConfig = new TableConfig();
    tableConfig.setSqlOperatorTable(
        ChainedSqlOperatorTable.of(HiveSqlOperatorTable.instance(),
            SqlStdOperatorTable.instance()));
    tableConfig.setSqlParserConfig(SqlParser
        .configBuilder()
        .setLex(Lex.JAVA).setCaseSensitive(false).setConformance(
            SqlConformanceEnum.HIVE)
        .setParserFactory(HiveSqlParserImpl.FACTORY)
        .build());
//    tableConfig.setRelDataTypeSystem(new HiveTypeSystemImpl());
    Properties prop = new Properties();
    prop.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
        String.valueOf(tableConfig.getSqlParserConfig().caseSensitive()));
    tableConfig.setCalciteConnectionConfig(
        new CalciteConnectionConfigImpl(prop));
    tableConfig.setConvertletTable(new HiveConvertletTable());
    RexExecutor rexExecutor = new HiveRexExecutorImpl(
        Schemas.createDataContext(null, null));
    tableConfig.setRexExecutor(rexExecutor);
    TableEnv tableEnv = new HiveTableEnv(tableConfig);
    //add table functions
    tableEnv.addFunction("", "explode",
        "org.apache.calcite.adapter.hive.udtf.UDTFExplode", "eval");
    return tableEnv;
  }
 

private String compile(RexBuilder rexBuilder, List<RexNode> constExps,
    RexToLixTranslator.InputGetter getter, RelDataType rowType) {
  final RexProgramBuilder programBuilder =
      new RexProgramBuilder(rowType, rexBuilder);
  for (RexNode node : constExps) {
    programBuilder.addProject(
        node, "c" + programBuilder.getProjectList().size());
  }
  final JavaTypeFactoryImpl javaTypeFactory =
      new JavaTypeFactoryImpl(rexBuilder.getTypeFactory().getTypeSystem());
  final BlockBuilder blockBuilder = new BlockBuilder();
  final ParameterExpression root0_ =
      Expressions.parameter(Object.class, "root0");
  final ParameterExpression root_ = DataContext.ROOT;
  blockBuilder.add(
      Expressions.declare(
          Modifier.FINAL, root_,
          Expressions.convert_(root0_, DataContext.class)));
  final SqlConformance conformance = SqlConformanceEnum.HIVE;
  final RexProgram program = programBuilder.getProgram();
  final List<Expression> expressions =
      RexToLixTranslator.translateProjects(program, javaTypeFactory,
          conformance, blockBuilder, null, root_, getter, null);
  blockBuilder.add(
      Expressions.return_(null,
          Expressions.newArrayInit(Object[].class, expressions)));
  final MethodDeclaration methodDecl =
      Expressions.methodDecl(Modifier.PUBLIC, Object[].class,
          BuiltInMethod.FUNCTION1_APPLY.method.getName(),
          ImmutableList.of(root0_), blockBuilder.toBlock());
  String code = Expressions.toString(methodDecl);
  if (CalcitePrepareImpl.DEBUG) {
    Util.debugCode(System.out, code);
  }
  return code;
}
 

/** Creates an empty context. Use {@link #EMPTY_CONTEXT} to reference the instance. */
private static Context emptyContext() {
  return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1,
      "'", "''", null,
      Casing.UNCHANGED, Casing.TO_UPPER, true, SqlConformanceEnum.DEFAULT,
      NullCollation.HIGH, RelDataTypeSystemImpl.DEFAULT,
      JethroDataSqlDialect.JethroInfo.EMPTY);
}
 

public DrillConformance(SqlConformanceEnum flavor) {
  super(flavor);
}
 

public DrillConformance() {
  super(SqlConformanceEnum.DEFAULT);
}
 

@Test void testInsertBindSubsetModifiableView() {
  final String sql = "insert into EMP_MODIFIABLEVIEW"
      + " values (?, ?)";
  sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003)
      .with(getExtendedTester()).ok();
}
 

public SqlConformance conformance() {
  return CalciteConnectionProperty.CONFORMANCE.wrap(properties)
      .getEnum(SqlConformanceEnum.class);
}
 

@Test void testInsertSubsetModifiableView() {
  final String sql = "insert into EMP_MODIFIABLEVIEW "
      + "values (10, 'Fred')";
  sql(sql).with(getExtendedTester())
      .conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
}
 

@Test void testInsertSubsetView() {
  final String sql = "insert into empnullables_20\n"
      + "values (10, 'Fred')";
  sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
}
 

@Test void testInsertBindSubsetWithCustomInitializerExpressionFactory() {
  final String sql = "insert into empdefaults values (?)";
  sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
}
 

@Test void testInsertBindSubset() {
  final String sql = "insert into empnullables\n"
      + "values (?, ?)";
  sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
}
 

@Test void testInsertSubsetWithCustomInitializerExpressionFactory() {
  final String sql = "insert into empdefaults values (100)";
  sql(sql).conformance(SqlConformanceEnum.PRAGMATIC_2003).ok();
}