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

@Test void testSortExchangeFieldTrimmerWithSingletonDistribution() {
  final RelBuilder builder = RelBuilder.create(config().build());
  final RelNode root =
      builder.scan("EMP")
          .project(builder.field("EMPNO"), builder.field("ENAME"), builder.field("DEPTNO"))
          .sortExchange(RelDistributions.SINGLETON, RelCollations.of(0))
          .project(builder.field("EMPNO"), builder.field("ENAME"))
          .build();

  RelFieldTrimmer fieldTrimmer = new RelFieldTrimmer(null, builder);
  RelNode trimmed = fieldTrimmer.trim(root);

  final String expected = ""
      + "LogicalSortExchange(distribution=[single], collation=[[0]])\n"
      + "  LogicalProject(EMPNO=[$0], ENAME=[$1])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(trimmed, hasTree(expected));
}
 

/**
 * Ensures that relational algebra ({@link RelBuilder}) works with SQL views.
 *
 * <p>This test currently fails (thus ignored).
 */
@Test void testExpandViewInRelBuilder() throws SQLException {
  try (Connection connection = DriverManager.getConnection("jdbc:calcite:")) {
    final Frameworks.ConfigBuilder configBuilder =
        expandingConfig(connection);
    final RelOptTable.ViewExpander viewExpander =
        (RelOptTable.ViewExpander) Frameworks.getPlanner(configBuilder.build());
    configBuilder.context(Contexts.of(viewExpander));
    final RelBuilder builder = RelBuilder.create(configBuilder.build());
    RelNode node = builder.scan("MYVIEW").build();

    int count = 0;
    try (PreparedStatement statement =
             connection.unwrap(RelRunner.class).prepare(node);
         ResultSet resultSet = statement.executeQuery()) {
      while (resultSet.next()) {
        count++;
      }
    }

    assertTrue(count > 1);
  }
}
 

@Test void testGetPredicatesForJoin() throws Exception {
  final FrameworkConfig config = RelBuilderTest.config().build();
  final RelBuilder builder = RelBuilder.create(config);
  RelNode join = builder
      .scan("EMP")
      .scan("DEPT")
      .join(JoinRelType.INNER, builder.call(NONDETERMINISTIC_OP))
      .build();
  RelMetadataQuery mq = join.getCluster().getMetadataQuery();
  assertTrue(mq.getPulledUpPredicates(join).pulledUpPredicates.isEmpty());

  RelNode join1 = builder
      .scan("EMP")
      .scan("DEPT")
      .join(JoinRelType.INNER,
        builder.call(SqlStdOperatorTable.EQUALS,
          builder.field(2, 0, 0),
          builder.field(2, 1, 0)))
      .build();
  assertThat(mq.getPulledUpPredicates(join1)
      .pulledUpPredicates
      .get(0)
      .toString(),
      is("=($0, $8)"));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2192">[CALCITE-2192]
 * RelBuilder wrongly skips creation of Aggregate that prunes columns if input
 * is unique</a>. */
@Test void testAggregate3() {
  // Equivalent SQL:
  //   SELECT DISTINCT deptno FROM (
  //     SELECT deptno, COUNT(*)
  //     FROM emp
  //     GROUP BY deptno)
  final RelBuilder builder = RelBuilder.create(config().build());
  RelNode root =
      builder.scan("EMP")
          .aggregate(builder.groupKey(builder.field(1)),
              builder.count().as("C"))
          .aggregate(builder.groupKey(builder.field(0)))
          .build();
  final String expected = ""
      + "LogicalProject(ENAME=[$0])\n"
      + "  LogicalAggregate(group=[{1}], C=[COUNT()])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root, hasTree(expected));
}
 

/** Tests that table aliases are propagated and are available to a filter,
 * even when there is a project on top of a project. (Aliases tend to get lost
 * when projects are merged). */
@Test void testAliasFilter() {
  final RelBuilder builder = RelBuilder.create(config().build());
  RelNode root =
      builder.scan("EMP")
          .as("EMP_alias")
          .project(builder.field("DEPTNO"),
              builder.literal(20))
          .project(builder.field(1), // literal 20
              builder.literal(10),
              builder.field(0)) // DEPTNO
          .filter(
              builder.call(SqlStdOperatorTable.GREATER_THAN,
                  builder.field(1),
                  builder.field("EMP_alias", "DEPTNO")))
          .build();
  final String expected = ""
      + "LogicalFilter(condition=[>($1, $2)])\n"
      + "  LogicalProject($f1=[20], $f2=[10], DEPTNO=[$7])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root, hasTree(expected));
}
 

@Test void testConvertRename() {
  final RelBuilder builder = RelBuilder.create(config().build());
  RelDataType rowType =
      builder.getTypeFactory().builder()
          .add("a", SqlTypeName.BIGINT)
          .add("b", SqlTypeName.VARCHAR, 10)
          .add("c", SqlTypeName.VARCHAR, 10)
          .build();
  RelNode root =
      builder.scan("DEPT")
          .convert(rowType, true)
          .build();
  final String expected = ""
      + "LogicalProject(a=[CAST($0):BIGINT NOT NULL], b=[CAST($1):VARCHAR(10) NOT NULL], c=[CAST($2):VARCHAR(10) NOT NULL])\n"
      + "  LogicalTableScan(table=[[scott, DEPT]])\n";
  assertThat(root, hasTree(expected));
}
 

@Test void testSortExchange() {
  final RelBuilder builder = RelBuilder.create(config().build());
  final RelNode root =
      builder.scan("EMP")
          .sortExchange(RelDistributions.hash(Lists.newArrayList(0)),
              RelCollations.of(0))
          .build();
  final String expected =
      "LogicalSortExchange(distribution=[hash[0]], collation=[[0]])\n"
          + "  LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(root, hasTree(expected));
}
 

@Test void testValuesBadNullFieldNames() {
  try {
    final RelBuilder builder = RelBuilder.create(config().build());
    RelBuilder root = builder.values((String[]) null, "a", "b");
    fail("expected error, got " + root);
  } catch (IllegalArgumentException e) {
    assertThat(e.getMessage(),
        is("Value count must be a positive multiple of field count"));
  }
}
 

/** Tests filter builder with correlation variables */
@Test void testFilterWithCorrelationVariables() {
  final RelBuilder builder = RelBuilder.create(config().build());
  final Holder<RexCorrelVariable> v = Holder.of(null);
  RelNode root = builder.scan("EMP")
      .variable(v)
      .scan("DEPT")
      .filter(Collections.singletonList(v.get().id),
          builder.call(SqlStdOperatorTable.OR,
              builder.call(SqlStdOperatorTable.AND,
                  builder.call(SqlStdOperatorTable.LESS_THAN,
                      builder.field(v.get(), "DEPTNO"),
                      builder.literal(30)),
                  builder.call(SqlStdOperatorTable.GREATER_THAN,
                      builder.field(v.get(), "DEPTNO"),
                      builder.literal(20))),
              builder.isNull(builder.field(2))))
      .join(JoinRelType.LEFT,
          builder.equals(builder.field(2, 0, "SAL"),
              builder.literal(1000)),
          ImmutableSet.of(v.get().id))
      .build();

  final String expected = ""
      + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{7}])\n"
      + "  LogicalTableScan(table=[[scott, EMP]])\n"
      + "  LogicalFilter(condition=[=($cor0.SAL, 1000)])\n"
      + "    LogicalFilter(condition=[OR(AND(<($cor0.DEPTNO, 30), >($cor0.DEPTNO, 20)), "
      + "IS NULL($2))], variablesSet=[[$cor0]])\n"
      + "      LogicalTableScan(table=[[scott, DEPT]])\n";

  assertThat(root, hasTree(expected));
}
 

/** As {@link #testAliasPastTop()}. */
@Test void testAliasPastTop2() {
  // Equivalent SQL:
  //   SELECT t1.EMPNO, t2.EMPNO, t3.DEPTNO
  //   FROM emp t1
  //   INNER JOIN emp t2 ON t1.EMPNO = t2.EMPNO
  //   INNER JOIN dept t3 ON t1.DEPTNO = t3.DEPTNO
  //     AND t2.JOB != t3.LOC
  final RelBuilder builder = RelBuilder.create(config().build());
  RelNode root =
      builder.scan("EMP").as("t1")
          .scan("EMP").as("t2")
          .join(JoinRelType.INNER,
              builder.equals(builder.field(2, "t1", "EMPNO"),
                  builder.field(2, "t2", "EMPNO")))
          .scan("DEPT").as("t3")
          .join(JoinRelType.INNER,
              builder.equals(builder.field(2, "t1", "DEPTNO"),
                  builder.field(2, "t3", "DEPTNO")),
              builder.not(
                  builder.equals(builder.field(2, "t2", "JOB"),
                      builder.field(2, "t3", "LOC"))))
          .build();
  // Cols:
  // 0-7   EMP as t1
  // 8-15  EMP as t2
  // 16-18 DEPT as t3
  final String expected = ""
      + "LogicalJoin(condition=[AND(=($7, $16), <>($10, $18))], joinType=[inner])\n"
      + "  LogicalJoin(condition=[=($0, $8)], joinType=[inner])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n"
      + "  LogicalTableScan(table=[[scott, DEPT]])\n";
  assertThat(root, hasTree(expected));
}
 

@Test void testScanFilterTriviallyFalse() {
  // Equivalent SQL:
  //   SELECT *
  //   FROM emp
  //   WHERE 1 = 2
  final RelBuilder builder = RelBuilder.create(config().build());
  RelNode root =
      builder.scan("EMP")
          .filter(builder.equals(builder.literal(1), builder.literal(2)))
          .build();
  assertThat(root,
      hasTree("LogicalValues(tuples=[[]])\n"));
}
 

/** 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 void testAlias2() {
  // Equivalent SQL:
  //   SELECT *
  //   FROM emp AS e, emp as m, dept
  //   WHERE e.deptno = dept.deptno
  //   AND m.empno = e.mgr
  final RelBuilder builder = RelBuilder.create(config().build());
  RelNode root =
      builder.scan("EMP")
          .as("e")
          .scan("EMP")
          .as("m")
          .scan("DEPT")
          .join(JoinRelType.INNER)
          .join(JoinRelType.INNER)
          .filter(
              builder.equals(builder.field("e", "DEPTNO"),
                  builder.field("DEPT", "DEPTNO")),
              builder.equals(builder.field("m", "EMPNO"),
                  builder.field("e", "MGR")))
          .build();
  final String expected = ""
      + "LogicalFilter(condition=[AND(=($7, $16), =($3, $8))])\n"
      + "  LogicalJoin(condition=[true], joinType=[inner])\n"
      + "    LogicalTableScan(table=[[scott, EMP]])\n"
      + "    LogicalJoin(condition=[true], joinType=[inner])\n"
      + "      LogicalTableScan(table=[[scott, EMP]])\n"
      + "      LogicalTableScan(table=[[scott, DEPT]])\n";
  assertThat(root, hasTree(expected));
}
 

@Test void testCalcFieldTrimmer2() {
  final RelBuilder builder = RelBuilder.create(config().build());
  final RelNode root =
      builder.scan("EMP")
          .project(builder.field("EMPNO"), builder.field("ENAME"), builder.field("DEPTNO"))
          .exchange(RelDistributions.SINGLETON)
          .filter(
              builder.call(SqlStdOperatorTable.GREATER_THAN,
                  builder.field("EMPNO"), builder.literal(100)))
          .project(builder.field("EMPNO"), builder.field("ENAME"))
          .build();

  final HepProgram hepProgram = new HepProgramBuilder()
      .addRuleInstance(ProjectToCalcRule.INSTANCE)
      .addRuleInstance(FilterToCalcRule.INSTANCE)
      .addRuleInstance(CalcMergeRule.INSTANCE).build();

  final HepPlanner hepPlanner = new HepPlanner(hepProgram);
  hepPlanner.setRoot(root);
  final RelNode relNode = hepPlanner.findBestExp();
  final RelFieldTrimmer fieldTrimmer = new RelFieldTrimmer(null, builder);
  final RelNode trimmed = fieldTrimmer.trim(relNode);

  final String expected = ""
      + "LogicalCalc(expr#0..1=[{inputs}], expr#2=[100], expr#3=[>($t0, $t2)], proj#0."
      + ".1=[{exprs}], $condition=[$t3])\n"
      + "  LogicalExchange(distribution=[single])\n"
      + "    LogicalCalc(expr#0..1=[{inputs}], proj#0..1=[{exprs}])\n"
      + "      LogicalProject(EMPNO=[$0], ENAME=[$1])\n"
      + "        LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(trimmed, hasTree(expected));
}
 

@Test void testAggregateGroupingKeyOutOfRangeFails() {
  final RelBuilder builder = RelBuilder.create(config().build());
  try {
    RelNode root =
        builder.scan("EMP")
            .aggregate(builder.groupKey(ImmutableBitSet.of(17)))
            .build();
    fail("expected error, got " + root);
  } catch (IllegalArgumentException e) {
    assertThat(e.getMessage(), is("out of bounds: {17}"));
  }
}
 

@Test void testBadFieldOrdinal() {
  final RelBuilder builder = RelBuilder.create(config().build());
  try {
    RexInputRef ref = builder.scan("DEPT").field(20);
    fail("expected error, got " + ref);
  } catch (IllegalArgumentException e) {
    assertThat(e.getMessage(),
        is("field ordinal [20] out of range; "
                + "input fields are: [DEPTNO, DNAME, LOC]"));
  }
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-2941">[CALCITE-2941]
 * EnumerableLimitRule on Sort with no collation creates EnumerableLimit with
 * wrong traitSet and cluster</a>.
 */
@Test void enumerableLimitOnEmptySort() throws Exception {
  RuleSet prepareRules =
      RuleSets.ofList(
          EnumerableRules.ENUMERABLE_FILTER_RULE,
          EnumerableRules.ENUMERABLE_SORT_RULE,
          EnumerableRules.ENUMERABLE_LIMIT_RULE,
          EnumerableRules.ENUMERABLE_TABLE_SCAN_RULE);
  SchemaPlus rootSchema = Frameworks.createRootSchema(true);
  SchemaPlus defSchema = rootSchema.add("hr", new HrClusteredSchema());
  FrameworkConfig config = Frameworks.newConfigBuilder()
      .parserConfig(SqlParser.Config.DEFAULT)
      .defaultSchema(defSchema)
      .traitDefs(ConventionTraitDef.INSTANCE, RelCollationTraitDef.INSTANCE)
      .programs(Programs.of(prepareRules))
      .build();

  RelBuilder builder = RelBuilder.create(config);
  RelNode planBefore = builder
      .scan("hr", "emps")
      .sort(builder.field(0)) // will produce collation [0] in the plan
      .filter(
          builder.notEquals(
              builder.field(0),
              builder.literal(100)))
      .limit(1, 5) // force a limit inside an "empty" Sort (with no collation)
      .build();

  RelTraitSet desiredTraits = planBefore.getTraitSet()
      .replace(EnumerableConvention.INSTANCE);
  Program program = Programs.of(prepareRules);
  RelNode planAfter = program.run(planBefore.getCluster().getPlanner(), planBefore,
      desiredTraits, ImmutableList.of(), ImmutableList.of());

  // verify that the collation [0] is not lost in the final plan
  final RelCollation collation =
      planAfter.getTraitSet().getTrait(RelCollationTraitDef.INSTANCE);
  assertThat(collation, notNullValue());
  final List<RelFieldCollation> fieldCollationList =
      collation.getFieldCollations();
  assertThat(fieldCollationList, notNullValue());
  assertThat(fieldCollationList.size(), is(1));
  assertThat(fieldCollationList.get(0).getFieldIndex(), is(0));
}
 

@Test void testTableModifyMerge() {
  final FrameworkConfig config = RelBuilderTest.config().build();
  final RelBuilder builder = RelBuilder.create(config);
  RelNode deptScan = builder.scan("DEPT").build();
  RelNode empScan = builder.scan("EMP").build();
  builder.push(deptScan);
  builder.push(empScan);
  RelNode project = builder
      .join(JoinRelType.LEFT,
          builder.call(
              SqlStdOperatorTable.EQUALS,
              builder.field(2, 0, "DEPTNO"),
              builder.field(2, 1, "DEPTNO")))
      .project(
          builder.literal(0),
          builder.literal("x"),
          builder.literal("x"),
          builder.literal(0),
          builder.literal("20200501 10:00:00"),
          builder.literal(0),
          builder.literal(0),
          builder.literal(0),
          builder.literal("false"),
          builder.field(1, 0, 2),
          builder.field(1, 0, 3),
          builder.field(1, 0, 4),
          builder.field(1, 0, 5),
          builder.field(1, 0, 6),
          builder.field(1, 0, 7),
          builder.field(1, 0, 8),
          builder.field(1, 0, 9),
          builder.field(1, 0, 10),
          builder.literal("a"))
      .build();
  // for sql:
  // merge into emp using dept on emp.deptno = dept.deptno
  // when matched then update set job = 'a'
  // when not matched then insert values(0, 'x', 'x', 0, '20200501 10:00:00', 0, 0, 0, 0)
  LogicalTableModify modify = LogicalTableModify.create(
      empScan.getTable(),
      (Prepare.CatalogReader) empScan.getTable().getRelOptSchema(),
      project,
      TableModify.Operation.MERGE,
      ImmutableList.of("ENAME"),
      null,
      false);
  String relJson = RelOptUtil.dumpPlan("", modify,
      SqlExplainFormat.JSON, SqlExplainLevel.EXPPLAN_ATTRIBUTES);
  String s = deserializeAndDumpToTextFormat(getSchema(modify), relJson);
  final String expected = ""
      + "LogicalTableModify(table=[[scott, EMP]], operation=[MERGE], "
      + "updateColumnList=[[ENAME]], flattened=[false])\n"
      + "  LogicalProject($f0=[0], $f1=['x'], $f2=['x'], $f3=[0], $f4=['20200501 10:00:00'], "
      + "$f5=[0], $f6=[0], $f7=[0], $f8=['false'], LOC=[$2], EMPNO=[$3], ENAME=[$4], JOB=[$5], "
      + "MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9], DEPTNO=[$10], $f18=['a'])\n"
      + "    LogicalJoin(condition=[=($0, $10)], joinType=[left])\n"
      + "      LogicalTableScan(table=[[scott, DEPT]])\n"
      + "      LogicalTableScan(table=[[scott, EMP]])\n";
  assertThat(s, isLinux(expected));
}
 

/** Test case for
 * <a href="https://issues.apache.org/jira/browse/CALCITE-3172">[CALCITE-3172]
 * RelBuilder#empty does not keep aliases</a>. */
@Test void testEmptyWithAlias() {
  final RelBuilder builder = RelBuilder.create(config().build());
  final String expected =
      "LogicalProject(DEPTNO=[$0], DNAME=[$1])\n  LogicalValues(tuples=[[]])\n";
  final String expectedType =
      "RecordType(TINYINT NOT NULL DEPTNO, VARCHAR(14) DNAME) NOT NULL";

  // Scan + Empty + Project (without alias)
  RelNode root =
      builder.scan("DEPT")
          .empty()
          .project(
              builder.field("DEPTNO"),
              builder.field("DNAME"))
          .build();
  assertThat(root, hasTree(expected));
  assertThat(root.getRowType().getFullTypeString(), is(expectedType));

  // Scan + Empty + Project (with alias)
  root =
      builder.scan("DEPT").as("d")
          .empty()
          .project(
              builder.field(1, "d", "DEPTNO"),
              builder.field(1, "d", "DNAME"))
          .build();
  assertThat(root, hasTree(expected));
  assertThat(root.getRowType().getFullTypeString(), is(expectedType));

  // Scan + Filter false (implicitly converted into Empty) + Project (with alias)
  root =
      builder.scan("DEPT").as("d")
          .filter(builder.literal(false))
          .project(
              builder.field(1, "d", "DEPTNO"),
              builder.field(1, "d", "DNAME"))
          .build();
  assertThat(root, hasTree(expected));
  assertThat(root.getRowType().getFullTypeString(), is(expectedType));
}
 

/**
 * Builder query 27 using {@link RelBuilder}.
 *
 * <blockquote><pre>
 *   select  i_item_id,
 *         s_state, grouping(s_state) g_state,
 *         avg(ss_quantity) agg1,
 *         avg(ss_list_price) agg2,
 *         avg(ss_coupon_amt) agg3,
 *         avg(ss_sales_price) agg4
 * from store_sales, customer_demographics, date_dim, store, item
 * where ss_sold_date_sk = d_date_sk and
 *        ss_item_sk = i_item_sk and
 *        ss_store_sk = s_store_sk and
 *        ss_cdemo_sk = cd_demo_sk and
 *        cd_gender = 'dist(gender, 1, 1)' and
 *        cd_marital_status = 'dist(marital_status, 1, 1)' and
 *        cd_education_status = 'dist(education, 1, 1)' and
 *        d_year = 1998 and
 *        s_state in ('distmember(fips_county,[STATENUMBER.1], 3)',
 *              'distmember(fips_county,[STATENUMBER.2], 3)',
 *              'distmember(fips_county,[STATENUMBER.3], 3)',
 *              'distmember(fips_county,[STATENUMBER.4], 3)',
 *              'distmember(fips_county,[STATENUMBER.5], 3)',
 *              'distmember(fips_county,[STATENUMBER.6], 3)')
 *  group by rollup (i_item_id, s_state)
 *  order by i_item_id
 *          ,s_state
 *  LIMIT 100
 * </pre></blockquote>
 */
@Test void testQuery27Builder() throws Exception {
  final RelBuilder builder = RelBuilder.create(config().build());
  final RelNode root =
      builder.scan("STORE_SALES")
          .scan("CUSTOMER_DEMOGRAPHICS")
          .scan("DATE_DIM")
          .scan("STORE")
          .scan("ITEM")
          .join(JoinRelType.INNER)
          .join(JoinRelType.INNER)
          .join(JoinRelType.INNER)
          .join(JoinRelType.INNER)
          .filter(
              builder.equals(builder.field("SS_SOLD_DATE_SK"), builder.field("D_DATE_SK")),
              builder.equals(builder.field("SS_ITEM_SK"), builder.field("I_ITEM_SK")),
              builder.equals(builder.field("SS_STORE_SK"), builder.field("S_STORE_SK")),
              builder.equals(builder.field("SS_CDEMO_SK"), builder.field("CD_DEMO_SK")),
              builder.equals(builder.field("CD_GENDER"), builder.literal("M")),
              builder.equals(builder.field("CD_MARITAL_STATUS"), builder.literal("S")),
              builder.equals(builder.field("CD_EDUCATION_STATUS"),
                  builder.literal("HIGH SCHOOL")),
              builder.equals(builder.field("D_YEAR"), builder.literal(1998)),
              builder.call(SqlStdOperatorTable.IN,
                  builder.field("S_STATE"),
                  builder.call(SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
                      builder.literal("CA"),
                      builder.literal("OR"),
                      builder.literal("WA"),
                      builder.literal("TX"),
                      builder.literal("OK"),
                      builder.literal("MD"))))
          .aggregate(builder.groupKey("I_ITEM_ID", "S_STATE"),
              builder.avg(false, "AGG1", builder.field("SS_QUANTITY")),
              builder.avg(false, "AGG2", builder.field("SS_LIST_PRICE")),
              builder.avg(false, "AGG3", builder.field("SS_COUPON_AMT")),
              builder.avg(false, "AGG4", builder.field("SS_SALES_PRICE")))
          .sortLimit(0, 100, builder.field("I_ITEM_ID"), builder.field("S_STATE"))
          .build();
  String expectResult = ""
      + "LogicalSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC], fetch=[100])\n"
      + "  LogicalAggregate(group=[{84, 90}], AGG1=[AVG($10)], AGG2=[AVG($12)], AGG3=[AVG($19)], AGG4=[AVG($13)])\n"
      + "    LogicalFilter(condition=[AND(=($0, $32), =($2, $89), =($7, $60), =($4, $23), =($24, 'M'), =($25, 'S'), =($26, 'HIGH SCHOOL'), =($38, 1998), IN($84, ARRAY('CA', 'OR', 'WA', 'TX', 'OK', 'MD')))])\n"
      + "      LogicalJoin(condition=[true], joinType=[inner])\n"
      + "        LogicalTableScan(table=[[TPCDS, STORE_SALES]])\n"
      + "        LogicalJoin(condition=[true], joinType=[inner])\n"
      + "          LogicalTableScan(table=[[TPCDS, CUSTOMER_DEMOGRAPHICS]])\n"
      + "          LogicalJoin(condition=[true], joinType=[inner])\n"
      + "            LogicalTableScan(table=[[TPCDS, DATE_DIM]])\n"
      + "            LogicalJoin(condition=[true], joinType=[inner])\n"
      + "              LogicalTableScan(table=[[TPCDS, STORE]])\n"
      + "              LogicalTableScan(table=[[TPCDS, ITEM]])\n";
  assertThat(root, hasTree(expectResult));
}