Java Code Examples for org.apache.calcite.tools.RelBuilder#scan()

@Test void testRelBuilderToString() {
  final RelBuilder builder = RelBuilder.create(config().build());
  builder.scan("EMP");

  // One entry on the stack, a single-node tree
  final String expected1 = "LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(Util.toLinux(builder.toString()), is(expected1));

  // One entry on the stack, a two-node tree
  builder.filter(builder.equals(builder.field(2), builder.literal(3)));
  final String expected2 = "LogicalFilter(condition=[=($2, 3)])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(Util.toLinux(builder.toString()), is(expected2));

  // Two entries on the stack
  builder.scan("DEPT");
  final String expected3 = "LogicalTableScan(table=[[scott, DEPT]])\n"
      + "LogicalFilter(condition=[=($2, 3)])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(Util.toLinux(builder.toString()), is(expected3));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2730">[CALCITE-2730]
 * RelBuilder incorrectly simplifies a filter with duplicate conjunction to
 * empty</a>. */
@Test void testScanFilterDuplicateAnd() {
  // Equivalent SQL:
  //   SELECT *
  //   FROM emp
  //   WHERE deptno > 20 AND deptno > 20 AND deptno > 20
  final RelBuilder builder = RelBuilder.create(config().build());
  builder.scan("EMP");
  final RexNode condition = builder.call(SqlStdOperatorTable.GREATER_THAN,
      builder.field("DEPTNO"),
      builder.literal(20));
  final RexNode condition2 = builder.call(SqlStdOperatorTable.LESS_THAN,
      builder.field("DEPTNO"),
      builder.literal(30));
  final RelNode root = builder.filter(condition, condition, condition)
      .build();
  final String expected = "LogicalFilter(condition=[>($7, 20)])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root, hasTree(expected));

  // Equivalent SQL:
  //   SELECT *
  //   FROM emp
  //   WHERE deptno > 20 AND deptno < 30 AND deptno > 20
  final RelNode root2 = builder.scan("EMP")
      .filter(condition, condition2, condition, condition)
      .build();
  final String expected2 = ""
      + "LogicalFilter(condition=[AND(>($7, 20), <($7, 30))])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root2, hasTree(expected2));
}
 

@Test void testBadType() {
  final RelBuilder builder = RelBuilder.create(config().build());
  try {
    builder.scan("EMP");
    RexNode call = builder.call(SqlStdOperatorTable.PLUS,
        builder.field(1),
        builder.field(3));
    fail("expected error, got " + call);
  } catch (IllegalArgumentException e) {
    assertThat(e.getMessage(),
        is("Cannot infer return type for +; "
            + "operand types: [VARCHAR(10), SMALLINT]"));
  }
}
 

/** Tests that the {@link RelBuilder#alias(RexNode, String)} function is
 * idempotent. */
@Test void testScanAlias() {
  final RelBuilder builder = RelBuilder.create(config().build());
  builder.scan("EMP");

  // Simplify "emp.deptno as d as d" to "emp.deptno as d".
  final RexNode e0 =
      builder.alias(builder.alias(builder.field("DEPTNO"), "D"), "D");
  assertThat(e0.toString(), is("AS($7, 'D')"));

  // It would be nice if RelBuilder could simplify
  // "emp.deptno as deptno" to "emp.deptno", but there is not
  // enough information in RexInputRef.
  final RexNode e1 = builder.alias(builder.field("DEPTNO"), "DEPTNO");
  assertThat(e1.toString(), is("AS($7, 'DEPTNO')"));

  // The intervening alias 'DEPTNO' is removed
  final RexNode e2 =
      builder.alias(builder.alias(builder.field("DEPTNO"), "DEPTNO"), "D1");
  assertThat(e2.toString(), is("AS($7, 'D1')"));

  // Simplify "emp.deptno as d2 as d3" to "emp.deptno as d3"
  // because "d3" alias overrides "d2".
  final RexNode e3 =
      builder.alias(builder.alias(builder.field("DEPTNO"), "D2"), "D3");
  assertThat(e3.toString(), is("AS($7, 'D3')"));

  final RelNode root = builder.project(e0, e1, e2, e3).build();
  final String expected = ""
      + "LogicalProject(D=[$7], DEPTNO=[$7], D1=[$7], D3=[$7])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root, hasTree(expected));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-3462">[CALCITE-3462]
 * Add projectExcept method in RelBuilder for projecting out expressions</a>. */
@Test void testProjectExceptWithMissingField() {
  final RelBuilder builder = RelBuilder.create(config().build());
  builder.scan("EMP");
  RexNode deptnoField = builder.field("DEPTNO");
  IllegalArgumentException ex = assertThrows(IllegalArgumentException.class, () -> {
    builder.project(
          builder.field("EMPNO"),
          builder.field("ENAME"))
        .projectExcept(deptnoField);
  }, "Project should fail since we are trying to remove a field that does not exist.");
  assertThat(ex.getMessage(), allOf(containsString("Expression"), containsString("not found")));
}
 

/**
 * Mock a {@link RelNode} for sql:
 * select count(*) over (partition by {@code partitionKeyNames}
 * order by {@code orderKeyNames}) from {@code table}
 * @param table Table name
 * @param partitionKeyNames Partition by column names, may empty, can not be null
 * @param orderKeyNames Order by column names, may empty, can not be null
 * @return RelNode for the sql
 */
private RelNode mockCountOver(String table,
    List<String> partitionKeyNames, List<String> orderKeyNames) {

  final FrameworkConfig config = RelBuilderTest.config().build();
  final RelBuilder builder = RelBuilder.create(config);
  final RexBuilder rexBuilder = builder.getRexBuilder();
  final RelDataType type = rexBuilder.getTypeFactory().createSqlType(SqlTypeName.BIGINT);
  List<RexNode> partitionKeys = new ArrayList<>(partitionKeyNames.size());
  builder.scan(table);
  for (String partitionkeyName: partitionKeyNames) {
    partitionKeys.add(builder.field(partitionkeyName));
  }
  List<RexFieldCollation> orderKeys = new ArrayList<>(orderKeyNames.size());
  for (String orderKeyName: orderKeyNames) {
    orderKeys.add(new RexFieldCollation(builder.field(orderKeyName), ImmutableSet.of()));
  }
  final RelNode rel = builder
      .project(
          rexBuilder.makeOver(
              type,
              SqlStdOperatorTable.COUNT,
              ImmutableList.of(),
              partitionKeys,
              ImmutableList.copyOf(orderKeys),
              RexWindowBounds.UNBOUNDED_PRECEDING,
              RexWindowBounds.CURRENT_ROW,
              true, true, false, false, false))
      .build();
  return rel;
}
 

/**
 * Tests the ElasticSearch match query. The match query is translated from
 * CONTAINS query which is build using RelBuilder, RexBuilder because the
 * normal SQL query assumes CONTAINS query is for date/period range.
 *
 * <p>Equivalent SQL query:
 *
 * <blockquote>
 * <code>select * from zips where city contains 'waltham'</code>
 * </blockquote>
 *
 * <p>ElasticSearch query for it:
 *
 * <blockquote><code>
 * {"query":{"constant_score":{"filter":{"match":{"city":"waltham"}}}}}
 * </code></blockquote>
 */
@Test void testMatchQuery() throws Exception {

  CalciteConnection con = (CalciteConnection) newConnectionFactory()
      .createConnection();
  SchemaPlus postSchema = con.getRootSchema().getSubSchema("elastic");

  FrameworkConfig postConfig = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(postSchema)
      .build();

  final RelBuilder builder = RelBuilder.create(postConfig);
  builder.scan(ZIPS);

  final RelDataTypeFactory typeFactory =
      new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
  final RexBuilder rexBuilder = new RexBuilder(typeFactory);

  RexNode nameRexNode = rexBuilder.makeCall(SqlStdOperatorTable.ITEM,
      rexBuilder.makeInputRef(typeFactory.createSqlType(SqlTypeName.ANY), 0),
      rexBuilder.makeCharLiteral(
          new NlsString("city", typeFactory.getDefaultCharset().name(),
              SqlCollation.COERCIBLE)));

  RelDataType mapType = typeFactory.createMapType(
      typeFactory.createSqlType(SqlTypeName.VARCHAR),
      typeFactory.createTypeWithNullability(
          typeFactory.createSqlType(SqlTypeName.ANY), true));

  List<RexNode> namedList =
      ImmutableList.of(rexBuilder.makeInputRef(mapType, 0),
          nameRexNode);

  // Add fields in builder stack so it is accessible while filter preparation
  builder.projectNamed(namedList, Arrays.asList("_MAP", "city"), true);

  RexNode filterRexNode = builder
      .call(SqlStdOperatorTable.CONTAINS, builder.field("city"),
          builder.literal("waltham"));
  builder.filter(filterRexNode);

  String builderExpected = ""
      + "LogicalFilter(condition=[CONTAINS($1, 'waltham')])\n"
      + "  LogicalProject(_MAP=[$0], city=[ITEM($0, 'city')])\n"
      + "    ElasticsearchTableScan(table=[[elastic, " + ZIPS + "]])\n";

  RelNode root = builder.build();

  RelRunner ru = (RelRunner) con.unwrap(Class.forName("org.apache.calcite.tools.RelRunner"));
  try (PreparedStatement preparedStatement = ru.prepare(root)) {

    String s = CalciteAssert.toString(preparedStatement.executeQuery());
    final String result = ""
        + "_MAP={id=02154, city=NORTH WALTHAM, loc=[-71.236497, 42.382492], "
        + "pop=57871, state=MA}; city=NORTH WALTHAM\n";

    // Validate query prepared
    assertThat(root, hasTree(builderExpected));

    // Validate result returned from ES
    assertThat(s, is(result));
  }
}
 

/**
 * Creates a relational expression for a table scan.
 * It is equivalent to
 *
 * <blockquote><pre>SELECT *
 * FROM emp</pre></blockquote>
 */
private RelBuilder example1(RelBuilder builder) {
  return builder
      .scan("EMP");
}