/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to you under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.calcite.adapter.tpcds;

import org.apache.calcite.plan.RelTraitDef;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.runtime.Hook;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.test.CalciteAssert;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Program;
import org.apache.calcite.tools.Programs;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.Bug;
import org.apache.calcite.util.Holder;

import net.hydromatic.tpcds.query.Query;

import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Tag;
import org.junit.jupiter.api.Test;

import java.util.List;
import java.util.Random;
import java.util.function.Consumer;

import static org.apache.calcite.test.Matchers.hasTree;

import static org.hamcrest.MatcherAssert.assertThat;

/** Unit test for {@link org.apache.calcite.adapter.tpcds.TpcdsSchema}.
 *
 * <p>Only runs as part of slow test suite.</p>
 */
@Tag("slow")
class TpcdsTest {
  private static Consumer<Holder<Program>> handler(
      final boolean bushy, final int minJoinCount) {
    return holder -> holder.set(
        Programs.sequence(
            Programs.heuristicJoinOrder(Programs.RULE_SET, bushy,
                minJoinCount),
            Programs.CALC_PROGRAM));
  }

  private static String schema(String name, String scaleFactor) {
    return "     {\n"
        + "       type: 'custom',\n"
        + "       name: '" + name + "',\n"
        + "       factory: 'org.apache.calcite.adapter.tpcds.TpcdsSchemaFactory',\n"
        + "       operand: {\n"
        + "         columnPrefix: true,\n"
        + "         scale: " + scaleFactor + "\n"
        + "       }\n"
        + "     }";
  }

  public static final String TPCDS_MODEL = "{\n"
      + "  version: '1.0',\n"
      + "  defaultSchema: 'TPCDS',\n"
      + "   schemas: [\n"
      + schema("TPCDS", "1.0") + ",\n"
      + schema("TPCDS_01", "0.01") + ",\n"
      + schema("TPCDS_5", "5.0") + "\n"
      + "   ]\n"
      + "}";

  private CalciteAssert.AssertThat with() {
    return CalciteAssert.model(TPCDS_MODEL);
  }

  @Test void testCallCenter() {
    final String[] strings = {
        "CC_CALL_CENTER_SK=1; CC_CALL_CENTER_ID=AAAAAAAABAAAAAAA; CC_REC_START_DATE=1998-01-01;"
            + " CC_REC_END_DATE=null; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2450952;"
            + " CC_NAME=NY Metro; CC_CLASS=large; CC_EMPLOYEES=2; CC_SQ_FT=1138;"
            + " CC_HOURS=8AM-4PM             ; CC_MANAGER=Bob Belcher; CC_MKT_ID=6;"
            + " CC_MKT_CLASS=More than other authori                           ;"
            + " CC_MKT_DESC=Shared others could not count fully dollars. New members ca;"
            + " CC_MARKET_MANAGER=Julius Tran; CC_DIVISION=3; CC_DIVISION_NAME=pri;"
            + " CC_COMPANY=6; CC_COMPANY_NAME=cally                                             ;"
            + " CC_STREET_NUMBER=730       ; CC_STREET_NAME=Ash Hill;"
            + " CC_STREET_TYPE=Boulevard      ; CC_SUITE_NUMBER=Suite 0   ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.11",
        "CC_CALL_CENTER_SK=2; CC_CALL_CENTER_ID=AAAAAAAACAAAAAAA; CC_REC_START_DATE=1998-01-01;"
            + " CC_REC_END_DATE=2000-12-31; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2450806;"
            + " CC_NAME=Mid Atlantic; CC_CLASS=medium; CC_EMPLOYEES=6; CC_SQ_FT=2268;"
            + " CC_HOURS=8AM-8AM             ; CC_MANAGER=Felipe Perkins; CC_MKT_ID=2;"
            + " CC_MKT_CLASS=A bit narrow forms matter animals. Consist        ;"
            + " CC_MKT_DESC=Largely blank years put substantially deaf, new others. Question;"
            + " CC_MARKET_MANAGER=Julius Durham; CC_DIVISION=5; CC_DIVISION_NAME=anti;"
            + " CC_COMPANY=1; CC_COMPANY_NAME=ought                                             ;"
            + " CC_STREET_NUMBER=984       ; CC_STREET_NAME=Center Hill;"
            + " CC_STREET_TYPE=Way            ; CC_SUITE_NUMBER=Suite 70  ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.12",
        "CC_CALL_CENTER_SK=3; CC_CALL_CENTER_ID=AAAAAAAACAAAAAAA; CC_REC_START_DATE=2001-01-01;"
            + " CC_REC_END_DATE=null; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2450806;"
            + " CC_NAME=Mid Atlantic; CC_CLASS=medium; CC_EMPLOYEES=6; CC_SQ_FT=4134;"
            + " CC_HOURS=8AM-4PM             ; CC_MANAGER=Mark Hightower; CC_MKT_ID=2;"
            + " CC_MKT_CLASS=Wrong troops shall work sometimes in a opti       ;"
            + " CC_MKT_DESC=Largely blank years put substantially deaf, new others. Question;"
            + " CC_MARKET_MANAGER=Julius Durham; CC_DIVISION=1; CC_DIVISION_NAME=ought;"
            + " CC_COMPANY=2; CC_COMPANY_NAME=able                                              ;"
            + " CC_STREET_NUMBER=984       ; CC_STREET_NAME=Center Hill;"
            + " CC_STREET_TYPE=Way            ; CC_SUITE_NUMBER=Suite 70  ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.01",
        "CC_CALL_CENTER_SK=4; CC_CALL_CENTER_ID=AAAAAAAAEAAAAAAA; CC_REC_START_DATE=1998-01-01;"
            + " CC_REC_END_DATE=2000-01-01; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2451063;"
            + " CC_NAME=North Midwest; CC_CLASS=medium; CC_EMPLOYEES=1; CC_SQ_FT=649;"
            + " CC_HOURS=8AM-4PM             ; CC_MANAGER=Larry Mccray; CC_MKT_ID=2;"
            + " CC_MKT_CLASS=Dealers make most historical, direct students     ;"
            + " CC_MKT_DESC=Rich groups catch longer other fears; future,;"
            + " CC_MARKET_MANAGER=Matthew Clifton; CC_DIVISION=4; CC_DIVISION_NAME=ese;"
            + " CC_COMPANY=3; CC_COMPANY_NAME=pri                                               ;"
            + " CC_STREET_NUMBER=463       ; CC_STREET_NAME=Pine Ridge;"
            + " CC_STREET_TYPE=RD             ; CC_SUITE_NUMBER=Suite U   ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.05",
        "CC_CALL_CENTER_SK=5; CC_CALL_CENTER_ID=AAAAAAAAEAAAAAAA; CC_REC_START_DATE=2000-01-02;"
            + " CC_REC_END_DATE=2001-12-31; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2451063;"
            + " CC_NAME=North Midwest; CC_CLASS=small; CC_EMPLOYEES=3; CC_SQ_FT=795;"
            + " CC_HOURS=8AM-8AM             ; CC_MANAGER=Larry Mccray; CC_MKT_ID=2;"
            + " CC_MKT_CLASS=Dealers make most historical, direct students     ;"
            + " CC_MKT_DESC=Blue, due beds come. Politicians would not make far thoughts. "
            + "Specifically new horses partic;"
            + " CC_MARKET_MANAGER=Gary Colburn; CC_DIVISION=4; CC_DIVISION_NAME=ese;"
            + " CC_COMPANY=3; CC_COMPANY_NAME=pri                                               ;"
            + " CC_STREET_NUMBER=463       ; CC_STREET_NAME=Pine Ridge;"
            + " CC_STREET_TYPE=RD             ; CC_SUITE_NUMBER=Suite U   ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.12",
        "CC_CALL_CENTER_SK=6; CC_CALL_CENTER_ID=AAAAAAAAEAAAAAAA; CC_REC_START_DATE=2002-01-01;"
            + " CC_REC_END_DATE=null; CC_CLOSED_DATE_SK=null; CC_OPEN_DATE_SK=2451063;"
            + " CC_NAME=North Midwest; CC_CLASS=medium; CC_EMPLOYEES=7; CC_SQ_FT=3514;"
            + " CC_HOURS=8AM-4PM             ; CC_MANAGER=Larry Mccray; CC_MKT_ID=5;"
            + " CC_MKT_CLASS=Silly particles could pro                         ;"
            + " CC_MKT_DESC=Blue, due beds come. Politicians would not make far thoughts. "
            + "Specifically new horses partic;"
            + " CC_MARKET_MANAGER=Gary Colburn; CC_DIVISION=5; CC_DIVISION_NAME=anti;"
            + " CC_COMPANY=3; CC_COMPANY_NAME=pri                                               ;"
            + " CC_STREET_NUMBER=463       ; CC_STREET_NAME=Pine Ridge;"
            + " CC_STREET_TYPE=RD             ; CC_SUITE_NUMBER=Suite U   ; CC_CITY=Midway;"
            + " CC_COUNTY=Williamson County; CC_STATE=TN; CC_ZIP=31904     ;"
            + " CC_COUNTRY=United States; CC_GMT_OFFSET=-5; CC_TAX_PERCENTAGE=0.11"};
    with().query("select * from tpcds.call_center").returnsUnordered(strings);
  }

  @Disabled("it's wasting time to count each table")
  @Test void testTableCount() {
    final CalciteAssert.AssertThat with = with();
    foo(with, "CALL_CENTER", 6);
    foo(with, "CATALOG_PAGE", 11_718);
    foo(with, "CATALOG_RETURNS", 144_067);
    foo(with, "CATALOG_SALES", 1_441_548);
    foo(with, "CUSTOMER", 100_000);
    foo(with, "CUSTOMER_ADDRESS", 50_000);
    foo(with, "CUSTOMER_DEMOGRAPHICS", 1_920_800);
    foo(with, "DATE_DIM", 73_049);
    foo(with, "HOUSEHOLD_DEMOGRAPHICS", 7_200);
    foo(with, "INCOME_BAND", 20);
    foo(with, "INVENTORY", 11_745_000);
    foo(with, "ITEM", 18_000);
    foo(with, "PROMOTION", 300);
    foo(with, "REASON", 35);
    foo(with, "SHIP_MODE", 20);
    foo(with, "STORE", 12);
    foo(with, "STORE_RETURNS", 287_514);
    foo(with, "STORE_SALES", 2_880_404);
    foo(with, "TIME_DIM", 86_400);
    foo(with, "WAREHOUSE", 5);
    foo(with, "WEB_PAGE", 60);
    foo(with, "WEB_RETURNS", 71_763);
    foo(with, "WEB_SALES", 719_384);
    foo(with, "WEB_SITE", 30);
    foo(with, "DBGEN_VERSION", 1);
  }

  protected void foo(CalciteAssert.AssertThat with, String tableName,
      int expectedCount) {
    final String sql = "select * from tpcds." + tableName;
    with.query(sql).returnsCount(expectedCount);
  }

  /** Tests the customer table with scale factor 5. */
  @Disabled("add tests like this that count each table")
  @Test void testCustomer5() {
    with()
        .query("select * from tpcds_5.customer")
        .returnsCount(750000);
  }

  @Disabled("throws 'RuntimeException: Cannot convert null to long'")
  @Test void testQuery01() {
    checkQuery(1).runs();
  }

  @Test void testQuery17Plan() {
    //noinspection unchecked
    checkQuery(17)
        .withHook(Hook.PROGRAM, handler(true, 2))
        .explainMatches("including all attributes ",
            CalciteAssert.checkMaskedResultContains(""
                + "EnumerableCalc(expr#0..9=[{inputs}], expr#10=[/($t4, $t3)], expr#11=[CAST($t10):INTEGER NOT NULL], expr#12=[*($t4, $t4)], expr#13=[/($t12, $t3)], expr#14=[-($t5, $t13)], expr#15=[1], expr#16=[=($t3, $t15)], expr#17=[null:BIGINT], expr#18=[-($t3, $t15)], expr#19=[CASE($t16, $t17, $t18)], expr#20=[/($t14, $t19)], expr#21=[0.5:DECIMAL(2, 1)], expr#22=[POWER($t20, $t21)], expr#23=[CAST($t22):INTEGER NOT NULL], expr#24=[/($t23, $t11)], expr#25=[/($t6, $t3)], expr#26=[CAST($t25):INTEGER NOT NULL], expr#27=[*($t6, $t6)], expr#28=[/($t27, $t3)], expr#29=[-($t7, $t28)], expr#30=[/($t29, $t19)], expr#31=[POWER($t30, $t21)], expr#32=[CAST($t31):INTEGER NOT NULL], expr#33=[/($t32, $t26)], expr#34=[/($t8, $t3)], expr#35=[CAST($t34):INTEGER NOT NULL], expr#36=[*($t8, $t8)], expr#37=[/($t36, $t3)], expr#38=[-($t9, $t37)], expr#39=[/($t38, $t19)], expr#40=[POWER($t39, $t21)], expr#41=[CAST($t40):INTEGER NOT NULL], expr#42=[/($t41, $t35)], proj#0..3=[{exprs}], STORE_SALES_QUANTITYAVE=[$t11], STORE_SALES_QUANTITYSTDEV=[$t23], STORE_SALES_QUANTITYCOV=[$t24], AS_STORE_RETURNS_QUANTITYCOUNT=[$t3], AS_STORE_RETURNS_QUANTITYAVE=[$t26], AS_STORE_RETURNS_QUANTITYSTDEV=[$t32], STORE_RETURNS_QUANTITYCOV=[$t33], CATALOG_SALES_QUANTITYCOUNT=[$t3], CATALOG_SALES_QUANTITYAVE=[$t35], CATALOG_SALES_QUANTITYSTDEV=[$t42], CATALOG_SALES_QUANTITYCOV=[$t42]): rowcount = 100.0, cumulative cost = {2.0726292349546726E28 rows, 4.277331418269512E30 cpu, 0.0 io}\n"
                + "  EnumerableLimit(fetch=[100]): rowcount = 100.0, cumulative cost = {2.0726292349546726E28 rows, 4.277331418269512E30 cpu, 0.0 io}\n"
                + "    EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 9.056715031420328E26, cumulative cost = {2.0726292349546726E28 rows, 4.277331418269512E30 cpu, 0.0 io}\n"
                + "      EnumerableAggregate(group=[{0, 1, 2}], STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[$SUM0($3)], agg#2=[$SUM0($6)], agg#3=[$SUM0($4)], agg#4=[$SUM0($7)], agg#5=[$SUM0($5)], agg#6=[$SUM0($8)]): rowcount = 9.056715031420328E26, cumulative cost = {1.982062084640469E28 rows, 2.0287041670381534E30 cpu, 0.0 io}\n"
                + "        EnumerableCalc(expr#0..211=[{inputs}], expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196, $t196)], I_ITEM_ID=[$t30], I_ITEM_DESC=[$t33], S_STATE=[$t24], SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196], $f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 9.056715031420328E27, cumulative cost = {1.8122486778013382E28 rows, 2.0287041670381534E30 cpu, 0.0 io}\n"
                + "          EnumerableHashJoin(condition=[AND(=($82, $133), =($81, $132), =($88, $139))], joinType=[inner]): rowcount = 9.056715031420328E27, cumulative cost = {9.065771746593055E27 rows, 1.7776306E7 cpu, 0.0 io}\n"
                + "            EnumerableHashJoin(condition=[=($0, $86)], joinType=[inner]): rowcount = 3.8347337644821E13, cumulative cost = {8.097127760662506E13 rows, 7281360.0 cpu, 0.0 io}\n"
                + "              EnumerableTableScan(table=[[TPCDS, STORE]]): rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
                + "              EnumerableHashJoin(condition=[=($0, $52)], joinType=[inner]): rowcount = 2.1304076469345E13, cumulative cost = {2.1319863492416234E13 rows, 7281347.0 cpu, 0.0 io}\n"
                + "                EnumerableTableScan(table=[[TPCDS, ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0 io}\n"
                + "                EnumerableHashJoin(condition=[=($0, $28)], joinType=[inner]): rowcount = 7.890398692349999E9, cumulative cost = {7.896430011596686E9 rows, 7263346.0 cpu, 0.0 io}\n"
                + "                  EnumerableCalc(expr#0..27=[{inputs}], expr#28=[Sarg['1998Q1']:CHAR(6)], expr#29=[SEARCH($t15, $t28)], proj#0..27=[{exprs}], $condition=[$t29]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 4382941.0 cpu, 0.0 io}\n"
                + "                    EnumerableTableScan(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
                + "                  EnumerableTableScan(table=[[TPCDS, STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 2880405.0 cpu, 0.0 io}\n"
                + "            EnumerableHashJoin(condition=[AND(=($31, $79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative cost = {7.0048032234040472E16 rows, 1.0494946E7 cpu, 0.0 io}\n"
                + "              EnumerableHashJoin(condition=[=($0, $28)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost = {7.884434212216867E8 rows, 4670456.0 cpu, 0.0 io}\n"
                + "                EnumerableCalc(expr#0..27=[{inputs}], expr#28=[Sarg['1998Q1', '1998Q2', '1998Q3']:CHAR(6)], expr#29=[SEARCH($t15, $t28)], proj#0..27=[{exprs}], $condition=[$t29]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 4382941.0 cpu, 0.0 io}\n"
                + "                  EnumerableTableScan(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
                + "                EnumerableTableScan(table=[[TPCDS, STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows, 287515.0 cpu, 0.0 io}\n"
                + "              EnumerableHashJoin(condition=[=($0, $28)], joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost = {3.9520401026966867E9 rows, 5824490.0 cpu, 0.0 io}\n"
                + "                EnumerableCalc(expr#0..27=[{inputs}], expr#28=[Sarg['1998Q1', '1998Q2', '1998Q3']:CHAR(6)], expr#29=[SEARCH($t15, $t28)], proj#0..27=[{exprs}], $condition=[$t29]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 4382941.0 cpu, 0.0 io}\n"
                + "                  EnumerableTableScan(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
                + "                EnumerableTableScan(table=[[TPCDS, CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows, 1441549.0 cpu, 0.0 io}\n"));
  }

  @Disabled("throws 'RuntimeException: Cannot convert null to long'")
  @Test void testQuery27() {
    checkQuery(27).runs();
  }

  @Disabled("throws 'RuntimeException: Cannot convert null to long'")
  @Test void testQuery58() {
    checkQuery(58).explainContains("PLAN").runs();
  }

  @Disabled("takes too long to optimize")
  @Test void testQuery72() {
    checkQuery(72).runs();
  }

  @Disabled("work in progress")
  @Test void testQuery72Plan() {
    checkQuery(72)
        .withHook(Hook.PROGRAM, handler(true, 2))
        .planContains("xx");
  }

  @Disabled("throws 'java.lang.AssertionError: type mismatch'")
  @Test void testQuery95() {
    checkQuery(95)
        .withHook(Hook.PROGRAM, handler(false, 6))
        .runs();
  }

  private CalciteAssert.AssertQuery checkQuery(int i) {
    final Query query = Query.of(i);
    String sql = query.sql(new Random(0));
    switch (i) {
    case 58:
      if (Bug.upgrade("new TPC-DS generator")) {
        // Work around bug: Support '<DATE>  = <character literal>'.
        sql = sql.replace(" = '", " = DATE '");
      } else {
        // Until TPC-DS generator can handle date(...).
        sql = sql.replace("'date([YEAR]+\"-01-01\",[YEAR]+\"-07-24\",sales)'",
            "DATE '1998-08-18'");
      }
      break;
    case 72:
      // Work around CALCITE-304: Support '<DATE> + <INTEGER>'.
      sql = sql.replace("+ 5", "+ interval '5' day");
      break;
    case 95:
      sql = sql.replace("60 days", "interval '60' day");
      sql = sql.replace("d_date between '", "d_date between date '");
      break;
    }
    return with()
        .query(sql.replace("tpcds.", "tpcds_01."));
  }

  public Frameworks.ConfigBuilder config() throws Exception {
    final Holder<SchemaPlus> root = Holder.of(null);
    CalciteAssert.model(TPCDS_MODEL)
        .doWithConnection(connection -> {
          root.set(connection.getRootSchema().getSubSchema("TPCDS"));
        });
    return Frameworks.newConfigBuilder()
        .parserConfig(SqlParser.Config.DEFAULT)
        .defaultSchema(root.get())
        .traitDefs((List<RelTraitDef>) null)
        .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
  }

  /**
   * 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.in(builder.field("S_STATE"),
                    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), SEARCH($24, Sarg['M']:CHAR(1)), "
        + "SEARCH($25, Sarg['S']:CHAR(1)), "
        + "SEARCH($26, Sarg['HIGH SCHOOL']:CHAR(11)), "
        + "SEARCH($38, Sarg[1998]), SEARCH($84, Sarg['CA', 'MD', 'OK', 'OR', "
        + "'TX', 'WA']:CHAR(2)))])\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));
  }
}
