content: /*
   * 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.test;
  import org.apache.calcite.jdbc.CalciteSchema;
  import org.apache.calcite.materialize.Lattice;
  import org.apache.calcite.materialize.Lattices;
  import org.apache.calcite.materialize.MaterializationService;
  import org.apache.calcite.plan.RelOptPlanner;
  import org.apache.calcite.plan.RelOptRule;
  import org.apache.calcite.plan.RelOptUtil;
  import org.apache.calcite.rel.rules.materialize.MaterializedViewRules;
  import org.apache.calcite.runtime.Hook;
  import org.apache.calcite.schema.SchemaPlus;
  import org.apache.calcite.sql.SqlDialect;
  import org.apache.calcite.sql.SqlDialect.DatabaseProduct;
  import org.apache.calcite.test.schemata.foodmart.FoodmartSchema;
  import org.apache.calcite.util.ImmutableBitSet;
  import org.apache.calcite.util.TestUtil;
  
  import com.google.common.base.Throwables;
  import com.google.common.collect.ImmutableList;
  
  import org.junit.jupiter.api.Disabled;
  import org.junit.jupiter.api.Tag;
  import org.junit.jupiter.api.Test;
  
  import java.io.IOException;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.Arrays;
  import java.util.List;
  import java.util.Map;
  import java.util.concurrent.atomic.AtomicInteger;
  import java.util.function.Consumer;
  
  import static org.apache.calcite.test.Matchers.containsStringLinux;
  
  import static org.hamcrest.CoreMatchers.anyOf;
  import static org.hamcrest.CoreMatchers.containsString;
  import static org.hamcrest.CoreMatchers.equalTo;
  import static org.hamcrest.CoreMatchers.is;
  import static org.hamcrest.MatcherAssert.assertThat;
  import static org.hamcrest.Matchers.closeTo;
  import static org.hamcrest.Matchers.hasSize;
  import static org.junit.jupiter.api.Assertions.assertNotEquals;
  import static org.junit.jupiter.api.Assumptions.assumeTrue;
  
  import static java.util.Objects.requireNonNull;
  
  /**
   * Unit test for lattices.
   */
  @Tag("slow")
  class LatticeTest {
    private static final String SALES_LATTICE = "{\n"
        + "  name: 'star',\n"
        + "  sql: [\n"
        + "    'select 1 from \"foodmart\".\"sales_fact_1997\" as \"s\"',\n"
        + "    'join \"foodmart\".\"product\" as \"p\" using (\"product_id\")',\n"
        + "    'join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")',\n"
        + "    'join \"foodmart\".\"product_class\" as \"pc\" on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"'\n"
        + "  ],\n"
        + "  auto: false,\n"
        + "  algorithm: true,\n"
        + "  algorithmMaxMillis: 10000,\n"
        + "  rowCountEstimate: 86837,\n"
        + "  defaultMeasures: [ {\n"
        + "    agg: 'count'\n"
        + "  } ],\n"
        + "  tiles: [ {\n"
        + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
        + "   measures: [ {\n"
        + "      agg: 'sum',\n"
        + "      args: 'unit_sales'\n"
        + "    }, {\n"
        + "      agg: 'sum',\n"
        + "      args: 'store_sales'\n"
        + "    }, {\n"
        + "      agg: 'count'\n"
        + "    } ]\n"
        + "  } ]\n"
        + "}\n";
  
    private static final String INVENTORY_LATTICE = "{\n"
        + "  name: 'warehouse',\n"
        + "  sql: [\n"
        + "  'select 1 from \"foodmart\".\"inventory_fact_1997\" as \"s\"',\n"
        + "  'join \"foodmart\".\"product\" as \"p\" using (\"product_id\")',\n"
        + "  'join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")',\n"
        + "  'join \"foodmart\".\"warehouse\" as \"w\" using (\"warehouse_id\")'\n"
        + "  ],\n"
        + "  auto: false,\n"
        + "  algorithm: true,\n"
        + "  algorithmMaxMillis: 10000,\n"
        + "  rowCountEstimate: 4070,\n"
        + "  defaultMeasures: [ {\n"
        + "    agg: 'count'\n"
        + "  } ],\n"
        + "  tiles: [ {\n"
        + "    dimensions: [ 'the_year', 'warehouse_name'],\n"
        + "    measures: [ {\n"
        + "      agg: 'sum',\n"
        + "      args: 'store_invoice'\n"
        + "    }, {\n"
        + "      agg: 'sum',\n"
        + "      args: 'supply_time'\n"
        + "    }, {\n"
        + "      agg: 'sum',\n"
        + "      args: 'warehouse_cost'\n"
        + "    } ]\n"
        + "  } ]\n"
        + "}\n";
  
    private static final String AUTO_LATTICE = "{\n"
        + "  name: 'star',\n"
        + "  sql: [\n"
        + "    'select 1 from \"foodmart\".\"sales_fact_1997\" as \"s\"',\n"
        + "    'join \"foodmart\".\"product\" as \"p\" using (\"product_id\")',\n"
        + "    'join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")',\n"
        + "    'join \"foodmart\".\"product_class\" as \"pc\" on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"'\n"
        + "  ],\n"
        + "  auto: false,\n"
        + "  algorithm: true,\n"
        + "  algorithmMaxMillis: 10000,\n"
        + "  rowCountEstimate: 86837,\n"
        + "  defaultMeasures: [ {\n"
        + "    agg: 'count'\n"
        + "  } ],\n"
        + "  tiles: [ {\n"
        + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
        + "   measures: [ {\n"
        + "      agg: 'sum',\n"
        + "      args: 'unit_sales'\n"
        + "    }, {\n"
        + "      agg: 'sum',\n"
        + "      args: 'store_sales'\n"
        + "    }, {\n"
        + "      agg: 'count'\n"
        + "    } ]\n"
        + "  } ]\n"
        + "}\n";
  
    private static CalciteAssert.AssertThat modelWithLattice(String name,
        String sql, String... extras) {
      final StringBuilder buf = new StringBuilder("{ name: '")
          .append(name)
          .append("', sql: ")
          .append(TestUtil.escapeString(sql));
      for (String extra : extras) {
        buf.append(", ").append(extra);
      }
      buf.append("}");
      return modelWithLattices(buf.toString());
    }
  
    private static CalciteAssert.AssertThat modelWithLattices(
        String... lattices) {
      final Class<JdbcTest.EmpDeptTableFactory> clazz =
          JdbcTest.EmpDeptTableFactory.class;
      return CalciteAssert.model(""
          + "{\n"
          + "  version: '1.0',\n"
          + "   schemas: [\n"
          + FoodmartSchema.FOODMART_SCHEMA
          + ",\n"
          + "     {\n"
          + "       name: 'adhoc',\n"
          + "       tables: [\n"
          + "         {\n"
          + "           name: 'EMPLOYEES',\n"
          + "           type: 'custom',\n"
          + "           factory: '"
          + clazz.getName()
          + "',\n"
          + "           operand: {'foo': true, 'bar': 345}\n"
          + "         }\n"
          + "       ],\n"
          + "       lattices: "
          + Arrays.toString(lattices)
          + "     }\n"
          + "   ]\n"
          + "}").withDefaultSchema("adhoc");
    }
  
    /** Tests that it's OK for a lattice to have the same name as a table in the
     * schema. */
    @Test void testLatticeSql() throws Exception {
      modelWithLattice("EMPLOYEES", "select * from \"foodmart\".\"days\"")
          .doWithConnection(c -> {
            final SchemaPlus schema = c.getRootSchema();
            final SchemaPlus adhoc =
                requireNonNull(schema.subSchemas().get("adhoc"));
            assertThat(adhoc.tables().get("EMPLOYEES") != null, is(true));
            final CalciteSchema adhocSchema =
                requireNonNull(adhoc.unwrap(CalciteSchema.class));
            final Map.Entry<String, CalciteSchema.LatticeEntry> entry =
                adhocSchema.getLatticeMap().firstEntry();
            final Lattice lattice = entry.getValue().getLattice();
            final String sql = "SELECT \"days\".\"day\"\n"
                + "FROM \"foodmart\".\"days\" AS \"days\"\n"
                + "GROUP BY \"days\".\"day\"";
            assertThat(
                lattice.sql(ImmutableBitSet.of(0),
                    ImmutableList.of()), is(sql));
            final String sql2 = "SELECT"
                + " \"days\".\"day\", \"days\".\"week_day\"\n"
                + "FROM \"foodmart\".\"days\" AS \"days\"";
            assertThat(
                lattice.sql(ImmutableBitSet.of(0, 1), false,
                    ImmutableList.of()),
                is(sql2));
          });
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-6603">[CALCITE-6603]
     * Lattice SQL supports generation of specified dialects</a>. */
    @Test void testLatticeSqlWithDialect() throws Exception {
      final SqlDialect dialect = DatabaseProduct.SPARK.getDialect();
      modelWithLattice("EMPLOYEES", "select * from \"foodmart\".\"days\"")
          .doWithConnection(c -> {
            final SchemaPlus schema = c.getRootSchema();
            final SchemaPlus adhoc =
                requireNonNull(schema.subSchemas().get("adhoc"));
            assertThat(adhoc.tables().get("EMPLOYEES") != null, is(true));
            final CalciteSchema adhocSchema =
                requireNonNull(adhoc.unwrap(CalciteSchema.class));
            final Map.Entry<String, CalciteSchema.LatticeEntry> entry =
                adhocSchema.getLatticeMap().firstEntry();
            final Lattice lattice = entry.getValue().getLattice();
            final String sql = "SELECT `days`.`day`\n"
                + "FROM `foodmart`.`days` AS `days`\n"
                + "GROUP BY `days`.`day`";
            assertThat(
                lattice.sql(ImmutableBitSet.of(0), true,
                    ImmutableList.of(), dialect), is(sql));
            final String sql2 = "SELECT `days`.`day`, `days`.`week_day`\n"
                + "FROM `foodmart`.`days` AS `days`";
            assertThat(
                lattice.sql(ImmutableBitSet.of(0, 1), false,
                    ImmutableList.of(), dialect),
                is(sql2));
          });
    }
  
    /** Tests some of the properties of the {@link Lattice} data structure. */
    @Test void testLattice() throws Exception {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"product\" as p using (\"product_id\")\n"
              + "join \"foodmart\".\"time_by_day\" as t on t.\"time_id\" = s.\"time_id\"")
          .doWithConnection(c -> {
            final SchemaPlus schema = c.getRootSchema();
            final SchemaPlus adhoc =
                requireNonNull(schema.subSchemas().get("adhoc"));
            assertThat(adhoc.tables().get("EMPLOYEES") != null, is(true));
            final CalciteSchema adhocSchema =
                requireNonNull(adhoc.unwrap(CalciteSchema.class));
            final Map.Entry<String, CalciteSchema.LatticeEntry> entry =
                adhocSchema.getLatticeMap().firstEntry();
            final Lattice lattice = entry.getValue().getLattice();
            assertThat(lattice.firstColumn("S"), is(10));
            assertThat(lattice.firstColumn("P"), is(18));
            assertThat(lattice.firstColumn("T"), is(0));
            assertThat(lattice.firstColumn("PC"), is(-1));
            assertThat(lattice.defaultMeasures, hasSize(1));
            assertThat(lattice.rootNode.descendants, hasSize(3));
          });
    }
  
    /** Tests that it's OK for a lattice to have the same name as a table in the
     * schema. */
    @Test void testLatticeWithSameNameAsTable() {
      modelWithLattice("EMPLOYEES", "select * from \"foodmart\".\"days\"")
          .query("select count(*) from EMPLOYEES")
          .returnsValue("4");
    }
  
    /** Tests that it's an error to have two lattices with the same name in a
     * schema. */
    @Test void testTwoLatticesWithSameNameFails() {
      modelWithLattices(
          "{name: 'Lattice1', sql: 'select * from \"foodmart\".\"days\"'}",
          "{name: 'Lattice1', sql: 'select * from \"foodmart\".\"time_by_day\"'}")
          .connectThrows("Duplicate lattice 'Lattice1'");
    }
  
    /** Tests a lattice whose SQL is invalid. */
    @Test void testLatticeInvalidSqlFails() {
      modelWithLattice("star", "select foo from nonexistent")
          .connectThrows("Error instantiating JsonLattice(name=star, ")
          .connectThrows("Object 'NONEXISTENT' not found");
    }
  
    /** Tests a lattice whose SQL is invalid because it contains a GROUP BY. */
    @Test void testLatticeSqlWithGroupByFails() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s group by \"product_id\"")
          .connectThrows("Invalid node type LogicalAggregate in lattice query");
    }
  
    /** Tests a lattice whose SQL is invalid because it contains a ORDER BY. */
    @Test void testLatticeSqlWithOrderByFails() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s order by \"product_id\"")
          .connectThrows("Invalid node type LogicalSort in lattice query");
    }
  
    /** Tests a lattice whose SQL is invalid because it contains a UNION ALL. */
    @Test void testLatticeSqlWithUnionFails() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n"
          + "union all\n"
          + "select 1 from \"foodmart\".\"sales_fact_1997\" as s")
          .connectThrows("Invalid node type LogicalUnion in lattice query");
    }
  
    /** Tests a lattice with valid join SQL. */
    @Test void testLatticeSqlWithJoin() {
      foodmartModel()
          .query("values 1")
          .returnsValue("1");
    }
  
    /** Tests a lattice with invalid SQL (for a lattice). */
    @Test void testLatticeInvalidSql() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"product\" as p using (\"product_id\")\n"
              + "join \"foodmart\".\"time_by_day\" as t on s.\"product_id\" = 100")
          .connectThrows("only equi-join of columns allowed: 100");
    }
  
    /** Left join is invalid in a lattice. */
    @Test void testLatticeInvalidSql2() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n"
          + "join \"foodmart\".\"product\" as p using (\"product_id\")\n"
          + "left join \"foodmart\".\"time_by_day\" as t on s.\"product_id\" = p.\"product_id\"")
          .connectThrows("only non nulls-generating join allowed, but got LEFT");
    }
  
    /** Each lattice table must have a parent. */
    @Test void testLatticeInvalidSql3() {
      modelWithLattice("star",
          "select 1 from \"foodmart\".\"sales_fact_1997\" as s\n"
          + "join \"foodmart\".\"product\" as p using (\"product_id\")\n"
          + "join \"foodmart\".\"time_by_day\" as t on s.\"product_id\" = p.\"product_id\"")
          .connectThrows("child node must have precisely one parent");
    }
  
    /** When a lattice is registered, there is a table with the same name.
     * It can be used for explain, but not for queries. */
    @Test void testLatticeStarTable() {
      final AtomicInteger counter = new AtomicInteger();
      try {
        foodmartModel()
            .query("select count(*) from \"adhoc\".\"star\"")
            .convertMatches(
                CalciteAssert.checkRel(""
                    + "LogicalAggregate(group=[{}], EXPR$0=[COUNT()])\n"
                    + "  StarTableScan(table=[[adhoc, star]])\n",
                    counter));
      } catch (Throwable e) {
        assertThat(Throwables.getStackTraceAsString(e),
            containsString("CannotPlanException"));
      }
      assertThat(counter.get(), equalTo(1));
    }
  
    /** Tests that a 2-way join query can be mapped 4-way join lattice. */
    @Test void testLatticeRecognizeJoin() {
      final AtomicInteger counter = new AtomicInteger();
      foodmartModel()
          .query("select s.\"unit_sales\", p.\"brand_name\"\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"product\" as p using (\"product_id\")\n")
          .enableMaterializations(true)
          .substitutionMatches(
              CalciteAssert.checkRel(
                  "LogicalProject(unit_sales=[$7], brand_name=[$10])\n"
                      + "  LogicalProject(product_id=[$0], time_id=[$1], customer_id=[$2], promotion_id=[$3], store_id=[$4], store_sales=[$5], store_cost=[$6], unit_sales=[$7], product_class_id=[$8], product_id0=[$9], brand_name=[$10], product_name=[$11], SKU=[$12], SRP=[$13], gross_weight=[$14], net_weight=[$15], recyclable_package=[$16], low_fat=[$17], units_per_case=[$18], cases_per_pallet=[$19], shelf_width=[$20], shelf_height=[$21], shelf_depth=[$22])\n"
                      + "    StarTableScan(table=[[adhoc, star]])\n",
                  counter));
      assertThat(counter.intValue(), equalTo(1));
    }
  
    /** Tests an aggregate on a 2-way join query can use an aggregate table. */
    @Test void testLatticeRecognizeGroupJoin() {
      final AtomicInteger counter = new AtomicInteger();
      CalciteAssert.AssertQuery that = foodmartModel()
          .query("select distinct p.\"brand_name\", s.\"customer_id\"\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"product\" as p using (\"product_id\")\n")
          .enableMaterializations(true)
          .substitutionMatches(relNode -> {
            counter.incrementAndGet();
            String s = RelOptUtil.toString(relNode);
            assertThat(s,
                anyOf(
                    containsStringLinux(
                        "LogicalProject(brand_name=[$1], customer_id=[$0])\n"
                            + "  LogicalAggregate(group=[{2, 10}])\n"
                            + "    StarTableScan(table=[[adhoc, star]])\n"),
                    containsStringLinux(
                        "LogicalAggregate(group=[{2, 10}])\n"
                            + "  StarTableScan(table=[[adhoc, star]])\n")));
          });
      assertThat(counter.intValue(), equalTo(2));
      that.explainContains(""
          + "EnumerableCalc(expr#0..1=[{inputs}], brand_name=[$t1], customer_id=[$t0])\n"
          + "  EnumerableTableScan(table=[[adhoc, m{2, 10}]])")
          .returnsCount(69203);
  
      // Run the same query again and see whether it uses the same
      // materialization.
      that.withHook(Hook.CREATE_MATERIALIZATION,
          materializationName -> counter.incrementAndGet())
          .returnsCount(69203);
  
      // Ideally the counter would stay at 2. It increments to 3 because
      // CalciteAssert.AssertQuery creates a new schema for every request,
      // and therefore cannot re-use lattices or materializations from the
      // previous request.
      assertThat(counter.intValue(), equalTo(3));
    }
  
    /** Tests a model with pre-defined tiles. */
    @Test void testLatticeWithPreDefinedTiles() {
      foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "    agg: 'count'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .query("select distinct t.\"the_year\", t.\"quarter\"\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
        .enableMaterializations(true)
        .explainContains("EnumerableTableScan(table=[[adhoc, m{32, 36}")
        .returnsCount(4);
    }
  
    /** A query that uses a pre-defined aggregate table, at the same
     * granularity but fewer calls to aggregate functions. */
    @Test void testLatticeWithPreDefinedTilesFewerMeasures() {
      foodmartModelWithOneTile()
          .query("select t.\"the_year\", t.\"quarter\", count(*) as c\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n"
              + "group by t.\"the_year\", t.\"quarter\"")
        .enableMaterializations(true)
        .explainContains(""
            + "EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n"
            + "  EnumerableTableScan(table=[[adhoc, m{32, 36}")
        .returnsUnordered("the_year=1997; quarter=Q1; C=21588",
            "the_year=1997; quarter=Q2; C=20368",
            "the_year=1997; quarter=Q3; C=21453",
            "the_year=1997; quarter=Q4; C=23428")
        .sameResultWithMaterializationsDisabled();
    }
  
    /** Tests a query that uses a pre-defined aggregate table at a lower
     * granularity. Includes a measure computed from a grouping column, a measure
     * based on COUNT rolled up using SUM, and an expression on a measure. */
    @Test void testLatticeWithPreDefinedTilesRollUp() {
      foodmartModelWithOneTile()
          .query("select t.\"the_year\",\n"
              + "  count(*) as c,\n"
              + "  min(\"quarter\") as q,\n"
              + "  sum(\"unit_sales\") * 10 as us\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n"
              + "group by t.\"the_year\"")
          .enableMaterializations(true)
          .explainContains(""
              + "EnumerableCalc(expr#0..3=[{inputs}], expr#4=[10], expr#5=[*($t3, $t4)], proj#0..2=[{exprs}], US=[$t5])\n"
              + "  EnumerableAggregate(group=[{0}], C=[$SUM0($2)], Q=[MIN($1)], agg#2=[$SUM0($4)])\n"
              + "    EnumerableTableScan(table=[[adhoc, m{32, 36}")
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE)
          .returnsUnordered("the_year=1997; C=86837; Q=Q1; US=2667730.0000")
          .sameResultWithMaterializationsDisabled();
    }
  
    /** Tests a model that uses an algorithm to generate an initial set of
     * tiles.
     *
     * <p>Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-428">[CALCITE-428]
     * Use optimization algorithm to suggest which tiles of a lattice to
     * materialize</a>. */
    @Test void testTileAlgorithm() {
      final String explain = "EnumerableAggregate(group=[{2, 3}])\n"
          + "  EnumerableTableScan(table=[[adhoc, m{16, 17, 32, 36, 37}]])";
      checkTileAlgorithm(
          FoodMartLatticeStatisticProvider.class.getCanonicalName() + "#FACTORY",
          explain);
    }
  
    /** As {@link #testTileAlgorithm()}, but uses the
     * {@link Lattices#CACHED_SQL} statistics provider. */
    @Test void testTileAlgorithm2() {
      // Different explain than above, but note that it still selects columns
      // (27, 31).
      final String explain = "EnumerableAggregate(group=[{4, 5}])\n"
          + "  EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]";
      checkTileAlgorithm(Lattices.class.getCanonicalName() + "#CACHED_SQL",
          explain);
    }
  
    /** As {@link #testTileAlgorithm()}, but uses the
     * {@link Lattices#PROFILER} statistics provider. */
    @Test void testTileAlgorithm3() {
      assumeTrue(TestUtil.getJavaMajorVersion() >= 8,
          "Yahoo sketches requires JDK 8 or higher");
      final String explain = "EnumerableAggregate(group=[{4, 5}])\n"
          + "  EnumerableTableScan(table=[[adhoc, m{16, 17, 27, 31, 32, 36, 37}]";
      checkTileAlgorithm(Lattices.class.getCanonicalName() + "#PROFILER",
          explain);
    }
  
    private void checkTileAlgorithm(String statisticProvider,
        String expectedExplain) {
      final RelOptRule[] rules = {
          MaterializedViewRules.PROJECT_FILTER,
          MaterializedViewRules.FILTER,
          MaterializedViewRules.PROJECT_JOIN,
          MaterializedViewRules.JOIN,
          MaterializedViewRules.PROJECT_AGGREGATE,
          MaterializedViewRules.AGGREGATE
      };
      MaterializationService.setThreadLocal();
      MaterializationService.instance().clear();
      foodmartLatticeModel(statisticProvider)
          .query("select distinct t.\"the_year\", t.\"quarter\"\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
          .enableMaterializations(true)
  
      // Disable materialization rules from this test. For some reason, there is
      // a weird interaction between these rules and the lattice rewriting that
      // produces non-deterministic rewriting (even when only lattices are present).
      // For more context, see
      // <a href="https://issues.apache.org/jira/browse/CALCITE-2953">[CALCITE-2953]</a>.
          .withHook(Hook.PLANNER, (Consumer<RelOptPlanner>) planner ->
              Arrays.asList(rules).forEach(planner::removeRule))
  
      // disable for MySQL; times out running star-join query
      // disable for H2; it thinks our generated SQL has invalid syntax
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.MYSQL
              && CalciteAssert.DB != CalciteAssert.DatabaseInstance.H2)
          .explainContains(expectedExplain)
          .returnsUnordered("the_year=1997; quarter=Q1",
              "the_year=1997; quarter=Q2",
              "the_year=1997; quarter=Q3",
              "the_year=1997; quarter=Q4");
    }
  
    private static CalciteAssert.AssertThat foodmartLatticeModel(
        String statisticProvider) {
      return foodmartModel(" auto: false,\n"
          + "  algorithm: true,\n"
          + "  algorithmMaxMillis: -1,\n"
          + "  rowCountEstimate: 87000,\n"
          + "  defaultMeasures: [ {\n"
          + "      agg: 'sum',\n"
          + "      args: 'unit_sales'\n"
          + "    }, {\n"
          + "      agg: 'sum',\n"
          + "      args: 'store_sales'\n"
          + "    }, {\n"
          + "      agg: 'count'\n"
          + "  } ],\n"
          + "  statisticProvider: '"
          + statisticProvider
          + "',\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n");
    }
  
    /** Tests a query that is created within {@link #testTileAlgorithm()}. */
    @Test void testJG() {
      final String sql = ""
          + "SELECT \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\", COUNT(*) AS \"m0\", SUM(\"s\".\"store_sales\") AS \"m1\", SUM(\"s\".\"unit_sales\") AS \"m2\"\n"
          + "FROM \"foodmart\".\"sales_fact_1997\" AS \"s\"\n"
          + "JOIN \"foodmart\".\"product\" AS \"p\" ON \"s\".\"product_id\" = \"p\".\"product_id\"\n"
          + "JOIN \"foodmart\".\"time_by_day\" AS \"t\" ON \"s\".\"time_id\" = \"t\".\"time_id\"\n"
          + "JOIN \"foodmart\".\"product_class\" AS \"pc\" ON \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"\n"
          + "GROUP BY \"s\".\"unit_sales\", \"p\".\"recyclable_package\", \"t\".\"the_day\", \"t\".\"the_year\", \"t\".\"quarter\", \"pc\".\"product_family\"";
      final String explain = "JdbcToEnumerableConverter\n"
          + "  JdbcAggregate(group=[{3, 6, 8, 9, 10, 12}], m0=[COUNT()], m1=[$SUM0($2)], m2=[$SUM0($3)])\n"
          + "    JdbcJoin(condition=[=($4, $11)], joinType=[inner])\n"
          + "      JdbcJoin(condition=[=($1, $7)], joinType=[inner])\n"
          + "        JdbcJoin(condition=[=($0, $5)], joinType=[inner])\n"
          + "          JdbcProject(product_id=[$0], time_id=[$1], store_sales=[$5], unit_sales=[$7])\n"
          + "            JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
          + "          JdbcProject(product_class_id=[$0], product_id=[$1], recyclable_package=[$8])\n"
          + "            JdbcTableScan(table=[[foodmart, product]])\n"
          + "        JdbcProject(time_id=[$0], the_day=[$2], the_year=[$4], quarter=[$8])\n"
          + "          JdbcTableScan(table=[[foodmart, time_by_day]])\n"
          + "      JdbcProject(product_class_id=[$0], product_family=[$4])\n"
          + "        JdbcTableScan(table=[[foodmart, product_class]])";
      CalciteAssert.that().with(CalciteAssert.Config.JDBC_FOODMART)
          .query(sql)
          .explainContains(explain);
    }
  
    /** Tests a query that uses no columns from the fact table. */
    @Test void testGroupByEmpty() {
      foodmartModel()
          .query("select count(*) as c from \"foodmart\".\"sales_fact_1997\"")
          .enableMaterializations(true)
          .returnsUnordered("C=86837");
    }
  
    /** Calls {@link #testDistinctCount()} followed by
     * {@link #testGroupByEmpty()}. */
    @Test void testGroupByEmptyWithPrelude() {
      testDistinctCount();
      testGroupByEmpty();
    }
  
    /** Tests a query that uses no dimension columns and one measure column. */
    @Test void testGroupByEmpty2() {
      foodmartModel()
          .query("select sum(\"unit_sales\") as s\n"
              + "from \"foodmart\".\"sales_fact_1997\"")
          .enableMaterializations(true)
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE)
          .returnsUnordered("S=266773.0000");
    }
  
    /** Tests that two queries of the same dimensionality that use different
     * measures can use the same materialization. */
    @Test void testGroupByEmpty3() {
      final List<String> mats = new ArrayList<>();
      final CalciteAssert.AssertThat that = foodmartModel().pooled();
      that.query("select sum(\"unit_sales\") as s, count(*) as c\n"
              + "from \"foodmart\".\"sales_fact_1997\"")
          .withHook(Hook.CREATE_MATERIALIZATION, (Consumer<String>) mats::add)
          .enableMaterializations(true)
          .explainContains("EnumerableTableScan(table=[[adhoc, m{}]])")
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE)
          .returnsUnordered("S=266773.0000; C=86837");
      assertThat(mats.toString(), mats, hasSize(2));
  
      // A similar query can use the same materialization.
      that.query("select sum(\"unit_sales\") as s\n"
          + "from \"foodmart\".\"sales_fact_1997\"")
          .withHook(Hook.CREATE_MATERIALIZATION, (Consumer<String>) mats::add)
          .enableMaterializations(true)
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE)
          .returnsUnordered("S=266773.0000");
      assertThat(mats.toString(), mats, hasSize(2));
    }
  
    /** Rolling up SUM. */
    @Test void testSum() {
      foodmartModelWithOneTile()
          .query("select sum(\"unit_sales\") as c\n"
              + "from \"foodmart\".\"sales_fact_1997\"\n"
              + "group by \"product_id\"\n"
              + "order by 1 desc limit 1")
          .enableMaterializations(true)
          .enable(CalciteAssert.DB != CalciteAssert.DatabaseInstance.ORACLE)
          .returnsUnordered("C=267.0000");
    }
  
    /** Tests a distinct-count query.
     *
     * <p>We can't just roll up count(distinct ...) as we do count(...), but we
     * can still use the aggregate table if we're smart. */
    @Test void testDistinctCount() {
      foodmartModelWithOneTile()
          .query("select count(distinct \"quarter\") as c\n"
              + "from \"foodmart\".\"sales_fact_1997\"\n"
              + "join \"foodmart\".\"time_by_day\" using (\"time_id\")\n"
              + "group by \"the_year\"")
          .enableMaterializations(true)
          .explainContains("EnumerableCalc(expr#0..1=[{inputs}], C=[$t1])\n"
              + "  EnumerableAggregate(group=[{0}], C=[COUNT($1)])\n"
              + "    EnumerableTableScan(table=[[adhoc, m{32, 36}]])")
          .returnsUnordered("C=4");
    }
  
    @Test void testDistinctCount2() {
      foodmartModelWithOneTile()
          .query("select count(distinct \"the_year\") as c\n"
              + "from \"foodmart\".\"sales_fact_1997\"\n"
              + "join \"foodmart\".\"time_by_day\" using (\"time_id\")\n"
              + "group by \"the_year\"")
          .enableMaterializations(true)
          .explainContains("EnumerableCalc(expr#0=[{inputs}], expr#1=[IS NOT NULL($t0)], "
              + "expr#2=[1:BIGINT], expr#3=[0:BIGINT], expr#4=[CASE($t1, $t2, $t3)], C=[$t4])\n"
              + "  EnumerableAggregate(group=[{0}])\n"
              + "    EnumerableTableScan(table=[[adhoc, m{32, 36}]])")
          .returnsUnordered("C=1");
    }
  
    /** Runs all queries against the Foodmart schema, using a lattice.
     *
     * <p>Disabled for normal runs, because it is slow. */
    @Disabled
    @Test void testAllFoodmartQueries() {
      // Test ids that had bugs in them until recently. Useful for a sanity check.
      final List<Integer> fixed =
          ImmutableList.of(13, 24, 28, 30, 61, 76, 79, 81,
              85, 98, 101, 107, 128, 129, 130, 131);
      // Test ids that still have bugs
      final List<Integer> bad = ImmutableList.of(382, 423);
      for (int i = 1; i < 1000; i++) {
        System.out.println("i=" + i);
        try {
          if (bad.contains(i)) {
            continue;
          }
          check(i);
        } catch (Throwable e) {
          throw new RuntimeException("error in " + i, e);
        }
      }
    }
  
    private void check(int n) throws IOException {
      final FoodMartQuerySet set = FoodMartQuerySet.instance();
      final FoodMartQuerySet.FoodmartQuery query = set.queries.get(n);
      if (query == null) {
        return;
      }
      foodmartModelWithOneTile()
          .withDefaultSchema("foodmart")
          .query(query.sql)
        .sameResultWithMaterializationsDisabled();
    }
  
    /** A tile with no measures should inherit default measure list from the
     * lattice. */
    @Test void testTileWithNoMeasures() {
      foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "    agg: 'count'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .query("select count(t.\"the_year\", t.\"quarter\")\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
          .enableMaterializations(true)
          .explainContains("EnumerableAggregate(group=[{}], EXPR$0=[COUNT($0, $1)])\n"
              + "  EnumerableTableScan(table=[[adhoc, m{32, 36}")
          .returnsCount(1);
    }
  
    /** A lattice with no default measure list should get "count(*)" is its
     * default measure. */
    @Test void testLatticeWithNoMeasures() {
      foodmartModel(" auto: false,\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .query("select count(*)\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"time_by_day\" as t using (\"time_id\")\n")
          .enableMaterializations(true)
          .explainContains("EnumerableAggregate(group=[{}], EXPR$0=[COUNT()])\n"
              + "  EnumerableTableScan(table=[[adhoc, m{32, 36}")
          .returnsCount(1);
    }
  
    @Test void testDimensionIsInvalidColumn() {
      foodmartModel(" auto: false,\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'invalid_column'],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .connectThrows("Unknown lattice column 'invalid_column'");
    }
  
    @Test void testMeasureArgIsInvalidColumn() {
      foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "   agg: 'sum',\n"
          + "   args: 'invalid_column'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .connectThrows("Unknown lattice column 'invalid_column'");
    }
  
    /** It is an error for "time_id" to be a measure arg, because is not a
     * unique alias. Both "s" and "t" have "time_id". */
    @Test void testMeasureArgIsNotUniqueAlias() {
      foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "    agg: 'count',\n"
          + "    args: 'time_id'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .connectThrows("Lattice column alias 'time_id' is not unique");
    }
  
    @Test void testMeasureAggIsInvalid() {
      foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "    agg: 'invalid_count',\n"
          + "    args: 'customer_id'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ ]\n"
          + "  } ]\n")
          .connectThrows("Unknown lattice aggregate function invalid_count");
    }
  
    @Test void testTwoLattices() {
      final AtomicInteger counter = new AtomicInteger();
      // disable for MySQL; times out running star-join query
      // disable for H2; it thinks our generated SQL has invalid syntax
      final boolean enabled =
          CalciteAssert.DB != CalciteAssert.DatabaseInstance.MYSQL
              && CalciteAssert.DB != CalciteAssert.DatabaseInstance.H2;
      modelWithLattices(SALES_LATTICE, INVENTORY_LATTICE)
          .query("select s.\"unit_sales\", p.\"brand_name\"\n"
              + "from \"foodmart\".\"sales_fact_1997\" as s\n"
              + "join \"foodmart\".\"product\" as p using (\"product_id\")\n")
          .enableMaterializations(true)
          .enable(enabled)
          .substitutionMatches(
              CalciteAssert.checkRel(
                  "LogicalProject(unit_sales=[$7], brand_name=[$10])\n"
                      + "  LogicalProject(product_id=[$0], time_id=[$1], customer_id=[$2], promotion_id=[$3], store_id=[$4], store_sales=[$5], store_cost=[$6], unit_sales=[$7], product_class_id=[$8], product_id0=[$9], brand_name=[$10], product_name=[$11], SKU=[$12], SRP=[$13], gross_weight=[$14], net_weight=[$15], recyclable_package=[$16], low_fat=[$17], units_per_case=[$18], cases_per_pallet=[$19], shelf_width=[$20], shelf_height=[$21], shelf_depth=[$22])\n"
                      + "    StarTableScan(table=[[adhoc, star]])\n",
                  counter));
      if (enabled) {
        assertThat(counter.intValue(), is(1));
      }
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-787">[CALCITE-787]
     * Star table wrongly assigned to materialized view</a>. */
    @Test void testOneLatticeOneMV() {
      final AtomicInteger counter = new AtomicInteger();
      final Class<JdbcTest.EmpDeptTableFactory> clazz =
          JdbcTest.EmpDeptTableFactory.class;
  
      final String mv = "       materializations: [\n"
          + "         {\n"
          + "           table: \"m0\",\n"
          + "           view: \"m0v\",\n"
          + "           sql: \"select * from \\\"foodmart\\\".\\\"sales_fact_1997\\\" "
          + "where \\\"product_id\\\" = 10\" "
          + "         }\n"
          + "       ]\n";
  
      final String model = ""
          + "{\n"
          + "  version: '1.0',\n"
          + "   schemas: [\n"
          + FoodmartSchema.FOODMART_SCHEMA
          + ",\n"
          + "     {\n"
          + "       name: 'adhoc',\n"
          + "       tables: [\n"
          + "         {\n"
          + "           name: 'EMPLOYEES',\n"
          + "           type: 'custom',\n"
          + "           factory: '"
          + clazz.getName()
          + "',\n"
          + "           operand: {'foo': true, 'bar': 345}\n"
          + "         }\n"
          + "       ],\n"
          + "       lattices: " + "[" + INVENTORY_LATTICE
          + "       ]\n"
          + "     },\n"
          + "     {\n"
          + "       name: 'mat',\n"
          + mv
          + "     }\n"
          + "   ]\n"
          + "}";
  
      CalciteAssert.model(model)
          .withDefaultSchema("foodmart")
          .query("select * from \"foodmart\".\"sales_fact_1997\" where \"product_id\" = 10")
          .enableMaterializations(true)
          .substitutionMatches(
              CalciteAssert.checkRel(
                  "LogicalTableScan(table=[[mat, m0]])\n",
                  counter));
      assertThat(counter.intValue(), equalTo(1));
    }
  
    /** Test case for
     * <a href="https://issues.apache.org/jira/browse/CALCITE-760">[CALCITE-760]
     * Aggregate recommender blows up if row count estimate is too high</a>. */
    @Disabled
    @Test void testLatticeWithBadRowCountEstimate() {
      final String lattice =
          INVENTORY_LATTICE.replace("rowCountEstimate: 4070,",
              "rowCountEstimate: 4074070,");
      assertNotEquals(lattice, INVENTORY_LATTICE);
      modelWithLattices(lattice)
          .query("values 1\n")
          .returns("EXPR$0=1\n");
    }
  
    @Test void testSuggester() {
      final Class<JdbcTest.EmpDeptTableFactory> clazz =
          JdbcTest.EmpDeptTableFactory.class;
      final String model = ""
          + "{\n"
          + "  version: '1.0',\n"
          + "   schemas: [\n"
          + FoodmartSchema.FOODMART_SCHEMA
          + ",\n"
          + "     {\n"
          + "       name: 'adhoc',\n"
          + "       tables: [\n"
          + "         {\n"
          + "           name: 'EMPLOYEES',\n"
          + "           type: 'custom',\n"
          + "           factory: '" + clazz.getName() + "',\n"
          + "           operand: {'foo': true, 'bar': 345}\n"
          + "         }\n"
          + "       ],\n"
          + "       \"autoLattice\": true"
          + "     }\n"
          + "   ]\n"
          + "}";
      final String sql = "select count(*)\n"
          + "from \"sales_fact_1997\"\n"
          + "join \"time_by_day\" using (\"time_id\")\n";
      final String explain = "PLAN=JdbcToEnumerableConverter\n"
          + "  JdbcAggregate(group=[{}], EXPR$0=[COUNT()])\n"
          + "    JdbcJoin(condition=[=($0, $1)], joinType=[inner])\n"
          + "      JdbcProject(time_id=[$1])\n"
          + "        JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
          + "      JdbcProject(time_id=[$0])\n"
          + "        JdbcTableScan(table=[[foodmart, time_by_day]])\n";
      CalciteAssert.model(model)
          .withDefaultSchema("foodmart")
          .query(sql)
          .returns("EXPR$0=86837\n")
          .explainContains(explain);
    }
  
    private static CalciteAssert.AssertThat foodmartModel(String... extras) {
      final String sql = "select 1\n"
          + "from \"foodmart\".\"sales_fact_1997\" as \"s\"\n"
          + "join \"foodmart\".\"product\" as \"p\" using (\"product_id\")\n"
          + "join \"foodmart\".\"time_by_day\" as \"t\" using (\"time_id\")\n"
          + "join \"foodmart\".\"product_class\" as \"pc\"\n"
          + "  on \"p\".\"product_class_id\" = \"pc\".\"product_class_id\"";
      return modelWithLattice("star", sql, extras);
    }
  
    private CalciteAssert.AssertThat foodmartModelWithOneTile() {
      return foodmartModel(" auto: false,\n"
          + "  defaultMeasures: [ {\n"
          + "    agg: 'count'\n"
          + "  } ],\n"
          + "  tiles: [ {\n"
          + "    dimensions: [ 'the_year', ['t', 'quarter'] ],\n"
          + "    measures: [ {\n"
          + "      agg: 'sum',\n"
          + "      args: 'unit_sales'\n"
          + "    }, {\n"
          + "      agg: 'sum',\n"
          + "      args: 'store_sales'\n"
          + "    }, {\n"
          + "      agg: 'count'\n"
          + "    } ]\n"
          + "  } ]\n");
    }
  
    // Just for debugging.
    private static void runJdbc() throws SQLException {
      final String url = "jdbc:calcite:model="
          + "core/src/test/resources/mysql-foodmart-lattice-model.json";
      final Connection connection = DriverManager.getConnection(url);
      final ResultSet resultSet = connection.createStatement()
          .executeQuery("select * from \"adhoc\".\"m{32, 36}\"");
      System.out.println(CalciteAssert.toString(resultSet));
      connection.close();
    }
  
    /** Unit test for {@link Lattice#getRowCount(double, List)}. */
    @Test void testColumnCount() {
      assertThat(Lattice.getRowCount(10, 2, 3), closeTo(5.03D, 0.01D));
      assertThat(Lattice.getRowCount(10, 9, 8), closeTo(9.4D, 0.01D));
      assertThat(Lattice.getRowCount(100, 9, 8), closeTo(54.2D, 0.1D));
      assertThat(Lattice.getRowCount(1000, 9, 8), closeTo(72D, 0.01D));
      assertThat(Lattice.getRowCount(1000, 1, 1), is(1D));
      assertThat(Lattice.getRowCount(1, 3, 5), closeTo(1D, 0.01D));
      assertThat(Lattice.getRowCount(1, 3, 5, 13, 4831), closeTo(1D, 0.01D));
    }
  }
  