This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 906183a [CALCITE-2914] Add a new statistic provider, to improve how
LatticeSuggester deduces foreign keys
906183a is described below
commit 906183a35b6355b3f5e394ce838f5ea1dad09ad2
Author: Julian Hyde <[email protected]>
AuthorDate: Fri Mar 15 09:43:18 2019 -0700
[CALCITE-2914] Add a new statistic provider, to improve how
LatticeSuggester deduces foreign keys
Statistic provider now generates SQL statements to look at a join
condition and figure out whether either side is a unique key, and
whether the other side is a foreign key (i.e. does an anti-join to
verify referential integrity).
Create new package org.apache.calcite.statistic, and move some
existing classes such as MapSqlStatisticProvider into it.
In JDBC adapter, when generating SQL for JDBC tables, use the foreign
catalog, schema and table name.
In Frameworks, use a query provider with a 30 minute, 1,000 element
cache, rather than map provider as default provider.
In LatticeSuggesterTest we continue to use a MapSqlStatisticProvider,
for performance reasons.
Fix deprecated calls to AggregateCall.create added in [CALCITE-1172].
Close apache/calcite#1141
---
.../org/apache/calcite/adapter/jdbc/JdbcRules.java | 124 ++++++++++++
.../org/apache/calcite/adapter/jdbc/JdbcTable.java | 31 +--
.../org/apache/calcite/materialize/Lattice.java | 1 +
.../materialize/MapSqlStatisticProvider.java | 88 --------
.../calcite/materialize/SqlStatisticProvider.java | 36 +++-
.../java/org/apache/calcite/materialize/Step.java | 37 +++-
.../org/apache/calcite/rel/core/AggregateCall.java | 4 +-
.../org/apache/calcite/rel/core/RelFactories.java | 26 +--
.../calcite/rel/rel2sql/RelToSqlConverter.java | 13 +-
.../calcite/sql/SqlSplittableAggFunction.java | 21 +-
.../statistic/CachingSqlStatisticProvider.java | 86 ++++++++
.../calcite/statistic/MapSqlStatisticProvider.java | 168 ++++++++++++++++
.../statistic/QuerySqlStatisticProvider.java | 222 +++++++++++++++++++++
.../package-info.java} | 17 +-
.../java/org/apache/calcite/tools/Frameworks.java | 4 +-
.../calcite/materialize/LatticeSuggesterTest.java | 25 +--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 14 +-
.../java/org/apache/calcite/test/CalciteSuite.java | 1 +
.../calcite/test/SqlStatisticProviderTest.java | 135 +++++++++++++
19 files changed, 884 insertions(+), 169 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
index 9f152b4..1672e54 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
@@ -18,6 +18,7 @@ package org.apache.calcite.adapter.jdbc;
import org.apache.calcite.linq4j.Queryable;
import org.apache.calcite.linq4j.tree.Expression;
+import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.Convention;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptCost;
@@ -60,12 +61,15 @@ import org.apache.calcite.rex.RexMultisetUtil;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexOver;
import org.apache.calcite.rex.RexProgram;
+import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.schema.ModifiableTable;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.validate.SqlValidatorUtil;
+import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilderFactory;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Util;
@@ -91,6 +95,126 @@ public class JdbcRules {
protected static final Logger LOGGER = CalciteTrace.getPlannerTracer();
+ static final RelFactories.ProjectFactory PROJECT_FACTORY =
+ (input, projects, fieldNames) -> {
+ final RelOptCluster cluster = input.getCluster();
+ final RelDataType rowType =
+ RexUtil.createStructType(cluster.getTypeFactory(), projects,
+ fieldNames, SqlValidatorUtil.F_SUGGESTER);
+ return new JdbcProject(cluster, input.getTraitSet(), input, projects,
+ rowType);
+ };
+
+ static final RelFactories.FilterFactory FILTER_FACTORY =
+ (input, condition) -> new JdbcRules.JdbcFilter(input.getCluster(),
+ input.getTraitSet(), input, condition);
+
+ static final RelFactories.JoinFactory JOIN_FACTORY =
+ (left, right, condition, variablesSet, joinType, semiJoinDone) -> {
+ final RelOptCluster cluster = left.getCluster();
+ final RelTraitSet traitSet = cluster.traitSetOf(left.getConvention());
+ try {
+ return new JdbcJoin(cluster, traitSet, left, right, condition,
+ variablesSet, joinType);
+ } catch (InvalidRelException e) {
+ throw new AssertionError(e);
+ }
+ };
+
+ static final RelFactories.CorrelateFactory CORRELATE_FACTORY =
+ (left, right, correlationId, requiredColumns, joinType) -> {
+ throw new UnsupportedOperationException("JdbcCorrelate");
+ };
+
+ public static final RelFactories.SemiJoinFactory SEMI_JOIN_FACTORY =
+ (left, right, condition) -> {
+ throw new UnsupportedOperationException("JdbcSemiJoin");
+ };
+
+ public static final RelFactories.SortFactory SORT_FACTORY =
+ (input, collation, offset, fetch) -> {
+ throw new UnsupportedOperationException("JdbcSort");
+ };
+
+ public static final RelFactories.ExchangeFactory EXCHANGE_FACTORY =
+ (input, distribution) -> {
+ throw new UnsupportedOperationException("JdbcExchange");
+ };
+
+ public static final RelFactories.SortExchangeFactory SORT_EXCHANGE_FACTORY =
+ (input, distribution, collation) -> {
+ throw new UnsupportedOperationException("JdbcSortExchange");
+ };
+
+ public static final RelFactories.AggregateFactory AGGREGATE_FACTORY =
+ (input, indicator, groupSet, groupSets, aggCalls) -> {
+ final RelOptCluster cluster = input.getCluster();
+ final RelTraitSet traitSet = cluster.traitSetOf(input.getConvention());
+ try {
+ return new JdbcAggregate(cluster, traitSet, input, false, groupSet,
+ groupSets, aggCalls);
+ } catch (InvalidRelException e) {
+ throw new AssertionError(e);
+ }
+ };
+
+ public static final RelFactories.MatchFactory MATCH_FACTORY =
+ (input, pattern, rowType, strictStart, strictEnd, patternDefinitions,
+ measures, after, subsets, allRows, partitionKeys, orderKeys,
+ interval) -> {
+ throw new UnsupportedOperationException("JdbcMatch");
+ };
+
+ public static final RelFactories.SetOpFactory SET_OP_FACTORY =
+ (kind, inputs, all) -> {
+ RelNode input = inputs.get(0);
+ RelOptCluster cluster = input.getCluster();
+ final RelTraitSet traitSet = cluster.traitSetOf(input.getConvention());
+ switch (kind) {
+ case UNION:
+ return new JdbcUnion(cluster, traitSet, inputs, all);
+ case INTERSECT:
+ return new JdbcIntersect(cluster, traitSet, inputs, all);
+ case EXCEPT:
+ return new JdbcMinus(cluster, traitSet, inputs, all);
+ default:
+ throw new AssertionError("unknown: " + kind);
+ }
+ };
+
+ public static final RelFactories.ValuesFactory VALUES_FACTORY =
+ (cluster, rowType, tuples) -> {
+ throw new UnsupportedOperationException();
+ };
+
+ public static final RelFactories.TableScanFactory TABLE_SCAN_FACTORY =
+ (cluster, table) -> {
+ throw new UnsupportedOperationException();
+ };
+
+ public static final RelFactories.SnapshotFactory SNAPSHOT_FACTORY =
+ (input, period) -> {
+ throw new UnsupportedOperationException();
+ };
+
+ /** A {@link RelBuilderFactory} that creates a {@link RelBuilder} that will
+ * create JDBC relational expressions for everything. */
+ public static final RelBuilderFactory JDBC_BUILDER =
+ RelBuilder.proto(
+ Contexts.of(PROJECT_FACTORY,
+ FILTER_FACTORY,
+ JOIN_FACTORY,
+ SEMI_JOIN_FACTORY,
+ SORT_FACTORY,
+ EXCHANGE_FACTORY,
+ SORT_EXCHANGE_FACTORY,
+ AGGREGATE_FACTORY,
+ MATCH_FACTORY,
+ SET_OP_FACTORY,
+ VALUES_FACTORY,
+ TABLE_SCAN_FACTORY,
+ SNAPSHOT_FACTORY));
+
public static List<RelOptRule> rules(JdbcConvention out) {
return rules(out, RelFactories.LOGICAL_BUILDER);
}
diff --git a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTable.java
b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTable.java
index 7a356fa..6285501 100644
--- a/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcTable.java
@@ -75,19 +75,20 @@ import java.util.Objects;
public class JdbcTable extends AbstractQueryableTable
implements TranslatableTable, ScannableTable, ModifiableTable {
private RelProtoDataType protoRowType;
- private final JdbcSchema jdbcSchema;
- private final String jdbcCatalogName;
- private final String jdbcSchemaName;
- private final String jdbcTableName;
- private final Schema.TableType jdbcTableType;
+ public final JdbcSchema jdbcSchema;
+ public final String jdbcCatalogName;
+ public final String jdbcSchemaName;
+ public final String jdbcTableName;
+ public final Schema.TableType jdbcTableType;
JdbcTable(JdbcSchema jdbcSchema, String jdbcCatalogName,
- String jdbcSchemaName, String tableName, Schema.TableType jdbcTableType)
{
+ String jdbcSchemaName, String jdbcTableName,
+ Schema.TableType jdbcTableType) {
super(Object[].class);
- this.jdbcSchema = jdbcSchema;
+ this.jdbcSchema = Objects.requireNonNull(jdbcSchema);
this.jdbcCatalogName = jdbcCatalogName;
this.jdbcSchemaName = jdbcSchemaName;
- this.jdbcTableName = tableName;
+ this.jdbcTableName = Objects.requireNonNull(jdbcTableName);
this.jdbcTableType = Objects.requireNonNull(jdbcTableType);
}
@@ -142,16 +143,18 @@ public class JdbcTable extends AbstractQueryableTable
return writer.toSqlString();
}
- SqlIdentifier tableName() {
- final List<String> strings = new ArrayList<>();
+ /** Returns the table name, qualified with catalog and schema name if
+ * applicable, as a parse tree node ({@link SqlIdentifier}). */
+ public SqlIdentifier tableName() {
+ final List<String> names = new ArrayList<>(3);
if (jdbcSchema.catalog != null) {
- strings.add(jdbcSchema.catalog);
+ names.add(jdbcSchema.catalog);
}
if (jdbcSchema.schema != null) {
- strings.add(jdbcSchema.schema);
+ names.add(jdbcSchema.schema);
}
- strings.add(jdbcTableName);
- return new SqlIdentifier(strings, SqlParserPos.ZERO);
+ names.add(jdbcTableName);
+ return new SqlIdentifier(names, SqlParserPos.ZERO);
}
public RelNode toRel(RelOptTable.ToRelContext context,
diff --git a/core/src/main/java/org/apache/calcite/materialize/Lattice.java
b/core/src/main/java/org/apache/calcite/materialize/Lattice.java
index b12bcf7..99e0af3 100644
--- a/core/src/main/java/org/apache/calcite/materialize/Lattice.java
+++ b/core/src/main/java/org/apache/calcite/materialize/Lattice.java
@@ -47,6 +47,7 @@ import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
+import org.apache.calcite.statistic.MapSqlStatisticProvider;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Litmus;
import org.apache.calcite.util.Pair;
diff --git
a/core/src/main/java/org/apache/calcite/materialize/MapSqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/materialize/MapSqlStatisticProvider.java
deleted file mode 100644
index 92dac0d..0000000
---
a/core/src/main/java/org/apache/calcite/materialize/MapSqlStatisticProvider.java
+++ /dev/null
@@ -1,88 +0,0 @@
-/*
- * 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.materialize;
-
-import com.google.common.collect.ImmutableMap;
-
-import java.util.List;
-import java.util.Map;
-
-/**
- * Implementation of {@link SqlStatisticProvider} that looks up values in a
- * table.
- *
- * <p>Only for testing.
- */
-public enum MapSqlStatisticProvider implements SqlStatisticProvider {
- INSTANCE;
-
- private static final Map<String, Double> CARDINALITY_MAP =
- ImmutableMap.<String, Double>builder()
- .put("[foodmart, agg_c_14_sales_fact_1997]", 86_805d)
- .put("[foodmart, customer]", 10_281d)
- .put("[foodmart, employee]", 1_155d)
- .put("[foodmart, employee_closure]", 7_179d)
- .put("[foodmart, department]", 10_281d)
- .put("[foodmart, inventory_fact_1997]", 4_070d)
- .put("[foodmart, position]", 18d)
- .put("[foodmart, product]", 1560d)
- .put("[foodmart, product_class]", 110d)
- .put("[foodmart, promotion]", 1_864d)
- // region really has 110 rows; made it smaller than store to trick FK
- .put("[foodmart, region]", 24d)
- .put("[foodmart, salary]", 21_252d)
- .put("[foodmart, sales_fact_1997]", 86_837d)
- .put("[foodmart, store]", 25d)
- .put("[foodmart, store_ragged]", 25d)
- .put("[foodmart, time_by_day]", 730d)
- .put("[foodmart, warehouse]", 24d)
- .put("[foodmart, warehouse_class]", 6d)
- .put("[scott, EMP]", 10d)
- .put("[scott, DEPT]", 4d)
- .put("[tpcds, CALL_CENTER]", 8d)
- .put("[tpcds, CATALOG_PAGE]", 11_718d)
- .put("[tpcds, CATALOG_RETURNS]", 144_067d)
- .put("[tpcds, CATALOG_SALES]", 1_441_548d)
- .put("[tpcds, CUSTOMER]", 100_000d)
- .put("[tpcds, CUSTOMER_ADDRESS]", 50_000d)
- .put("[tpcds, CUSTOMER_DEMOGRAPHICS]", 1_920_800d)
- .put("[tpcds, DATE_DIM]", 73049d)
- .put("[tpcds, DBGEN_VERSION]", 1d)
- .put("[tpcds, HOUSEHOLD_DEMOGRAPHICS]", 7200d)
- .put("[tpcds, INCOME_BAND]", 20d)
- .put("[tpcds, INVENTORY]", 11_745_000d)
- .put("[tpcds, ITEM]", 18_000d)
- .put("[tpcds, PROMOTION]", 300d)
- .put("[tpcds, REASON]", 35d)
- .put("[tpcds, SHIP_MODE]", 20d)
- .put("[tpcds, STORE]", 12d)
- .put("[tpcds, STORE_RETURNS]", 287_514d)
- .put("[tpcds, STORE_SALES]", 2_880_404d)
- .put("[tpcds, TIME_DIM]", 86_400d)
- .put("[tpcds, WAREHOUSE]", 5d)
- .put("[tpcds, WEB_PAGE]", 60d)
- .put("[tpcds, WEB_RETURNS]", 71_763d)
- .put("[tpcds, WEB_SALES]", 719_384d)
- .put("[tpcds, WEB_SITE]", 1d)
- .build();
-
- public double tableCardinality(List<String> qualifiedTableName) {
- return CARDINALITY_MAP.get(qualifiedTableName.toString());
- }
-}
-
-// End MapSqlStatisticProvider.java
diff --git
a/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
index 8d045e1..c18900f 100644
---
a/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
+++
b/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
@@ -16,16 +16,48 @@
*/
package org.apache.calcite.materialize;
+import org.apache.calcite.plan.RelOptTable;
+
import java.util.List;
/**
- * Estimates row counts for tables and columns.
+ * Estimates row counts for tables and columns, and whether combinations of
+ * columns form primary/unique and foreign keys.
*
* <p>Unlike {@link LatticeStatisticProvider}, works on raw tables and columns
* and does not need a {@link Lattice}.
+ *
+ * <p>It uses {@link org.apache.calcite.plan.RelOptTable} because that contains
+ * enough information to generate and execute SQL, while not being tied to a
+ * lattice.
+ *
+ * <p>The main implementation,
+ * {@link org.apache.calcite.statistic.QuerySqlStatisticProvider}, executes
+ * queries on a populated database. Implementations that use database
statistics
+ * (from {@code ANALYZE TABLE}, etc.) and catalog information (e.g. primary and
+ * foreign key constraints) would also be possible.
*/
public interface SqlStatisticProvider {
- double tableCardinality(List<String> qualifiedTableName);
+ /** Returns an estimate of the number of rows in {@code table}. */
+ double tableCardinality(RelOptTable table);
+
+ /** Returns whether a join is a foreign key; that is, whether every row in
+ * the referencing table is matched by at least one row in the referenced
+ * table.
+ *
+ * <p>For example, {@code isForeignKey(EMP, [DEPTNO], DEPT, [DEPTNO])}
+ * returns true.
+ *
+ * <p>To change "at least one" to "exactly one", you also need to call
+ * {@link #isKey}. */
+ boolean isForeignKey(RelOptTable fromTable, List<Integer> fromColumns,
+ RelOptTable toTable, List<Integer> toColumns);
+
+ /** Returns whether a collection of columns is a unique (or primary) key.
+ *
+ * <p>For example, {@code isKey(EMP, [DEPTNO]} returns true;
+ * <p>For example, {@code isKey(DEPT, [DEPTNO]} returns false. */
+ boolean isKey(RelOptTable table, List<Integer> columns);
}
// End SqlStatisticProvider.java
diff --git a/core/src/main/java/org/apache/calcite/materialize/Step.java
b/core/src/main/java/org/apache/calcite/materialize/Step.java
index ea4cf79..d28a8f9 100644
--- a/core/src/main/java/org/apache/calcite/materialize/Step.java
+++ b/core/src/main/java/org/apache/calcite/materialize/Step.java
@@ -16,11 +16,13 @@
*/
package org.apache.calcite.materialize;
+import org.apache.calcite.plan.RelOptTable;
import org.apache.calcite.util.graph.AttributedDirectedGraph;
import org.apache.calcite.util.graph.DefaultEdge;
import org.apache.calcite.util.mapping.IntPair;
import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Ordering;
import java.util.List;
import java.util.Objects;
@@ -81,17 +83,42 @@ class Step extends DefaultEdge {
}
boolean isBackwards(SqlStatisticProvider statisticProvider) {
- return source == target
- ? keys.get(0).source < keys.get(0).target // want PK on the right
- : cardinality(statisticProvider, source())
- < cardinality(statisticProvider, target());
+ final RelOptTable sourceTable = source().t;
+ final List<Integer> sourceColumns = IntPair.left(keys);
+ final RelOptTable targetTable = target().t;
+ final List<Integer> targetColumns = IntPair.right(keys);
+ final boolean forwardForeignKey =
+ statisticProvider.isForeignKey(sourceTable, sourceColumns, targetTable,
+ targetColumns)
+ && statisticProvider.isKey(targetTable, targetColumns);
+ final boolean backwardForeignKey =
+ statisticProvider.isForeignKey(targetTable, targetColumns, sourceTable,
+ sourceColumns)
+ && statisticProvider.isKey(sourceTable, sourceColumns);
+ if (backwardForeignKey != forwardForeignKey) {
+ return backwardForeignKey;
+ }
+ // Tie-break if it's a foreign key in neither or both directions
+ return compare(sourceTable, sourceColumns, targetTable, targetColumns) < 0;
+ }
+
+ /** Arbitrarily compares (table, columns). */
+ private static int compare(RelOptTable table1, List<Integer> columns1,
+ RelOptTable table2, List<Integer> columns2) {
+ int c = Ordering.natural().<String>lexicographical()
+ .compare(table1.getQualifiedName(), table2.getQualifiedName());
+ if (c == 0) {
+ c = Ordering.natural().<Integer>lexicographical()
+ .compare(columns1, columns2);
+ }
+ return c;
}
/** Temporary method. We should use (inferred) primary keys to figure out
* the direction of steps. */
private double cardinality(SqlStatisticProvider statisticProvider,
LatticeTable table) {
- return statisticProvider.tableCardinality(table.t.getQualifiedName());
+ return statisticProvider.tableCardinality(table.t);
}
/** Creates {@link Step} instances. */
diff --git a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
index c3bf08f..e95f46a 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/AggregateCall.java
@@ -178,8 +178,8 @@ public class AggregateCall {
public static AggregateCall create(SqlAggFunction aggFunction,
boolean distinct, boolean approximate, List<Integer> argList,
int filterArg, RelCollation collation, RelDataType type, String name) {
- return new AggregateCall(aggFunction, distinct, approximate, false,
argList,
- filterArg, collation, type, name);
+ return create(aggFunction, distinct, approximate, false, argList,
filterArg,
+ collation, type, name);
}
/** Creates an AggregateCall. */
diff --git a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
index 681c690..7346a44 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/RelFactories.java
@@ -162,8 +162,10 @@ public class RelFactories {
RexNode fetch);
@Deprecated // to be removed before 2.0
- RelNode createSort(RelTraitSet traits, RelNode input,
- RelCollation collation, RexNode offset, RexNode fetch);
+ default RelNode createSort(RelTraitSet traitSet, RelNode input,
+ RelCollation collation, RexNode offset, RexNode fetch) {
+ return createSort(input, collation, offset, fetch);
+ }
}
/**
@@ -175,12 +177,6 @@ public class RelFactories {
RexNode offset, RexNode fetch) {
return LogicalSort.create(input, collation, offset, fetch);
}
-
- @Deprecated // to be removed before 2.0
- public RelNode createSort(RelTraitSet traits, RelNode input,
- RelCollation collation, RexNode offset, RexNode fetch) {
- return createSort(input, collation, offset, fetch);
- }
}
/**
@@ -331,9 +327,12 @@ public class RelFactories {
boolean semiJoinDone);
@Deprecated // to be removed before 2.0
- RelNode createJoin(RelNode left, RelNode right, RexNode condition,
+ default RelNode createJoin(RelNode left, RelNode right, RexNode condition,
JoinRelType joinType, Set<String> variablesStopped,
- boolean semiJoinDone);
+ boolean semiJoinDone) {
+ return createJoin(left, right, condition,
+ CorrelationId.setOf(variablesStopped), joinType, semiJoinDone);
+ }
}
/**
@@ -347,13 +346,6 @@ public class RelFactories {
return LogicalJoin.create(left, right, condition, variablesSet, joinType,
semiJoinDone, ImmutableList.of());
}
-
- public RelNode createJoin(RelNode left, RelNode right, RexNode condition,
- JoinRelType joinType, Set<String> variablesStopped,
- boolean semiJoinDone) {
- return createJoin(left, right, condition,
- CorrelationId.setOf(variablesStopped), joinType, semiJoinDone);
- }
}
/**
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 496a65c..5abef41 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.rel.rel2sql;
+import org.apache.calcite.adapter.jdbc.JdbcTable;
import org.apache.calcite.linq4j.tree.Expressions;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollations;
@@ -291,8 +292,16 @@ public class RelToSqlConverter extends SqlImplementor
/** @see #dispatch */
public Result visit(TableScan e) {
- final SqlIdentifier identifier =
- new SqlIdentifier(e.getTable().getQualifiedName(), SqlParserPos.ZERO);
+ final SqlIdentifier identifier;
+ final JdbcTable jdbcTable = e.getTable().unwrap(JdbcTable.class);
+ if (jdbcTable != null) {
+ // Use the foreign catalog, schema and table names, if they exist,
+ // rather than the qualified name of the shadow table in Calcite.
+ identifier = jdbcTable.tableName();
+ } else {
+ final List<String> qualifiedName = e.getTable().getQualifiedName();
+ identifier = new SqlIdentifier(qualifiedName, SqlParserPos.ZERO);
+ }
return result(identifier, ImmutableList.of(Clause.FROM), e, null);
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
index 213c646..43457e2 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSplittableAggFunction.java
@@ -198,9 +198,10 @@ public interface SqlSplittableAggFunction {
public AggregateCall merge(AggregateCall top, AggregateCall bottom) {
if (bottom.getAggregation().getKind() == SqlKind.COUNT
&& top.getAggregation().getKind() == SqlKind.SUM) {
- return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
- bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
- bottom.getCollation(), bottom.getType(), top.getName());
+ return AggregateCall.create(bottom.getAggregation(),
+ bottom.isDistinct(), bottom.isApproximate(), false,
+ bottom.getArgList(), bottom.filterArg, bottom.getCollation(),
+ bottom.getType(), top.getName());
} else {
return null;
}
@@ -241,9 +242,10 @@ public interface SqlSplittableAggFunction {
public AggregateCall merge(AggregateCall top, AggregateCall bottom) {
if (top.getAggregation().getKind() == bottom.getAggregation().getKind())
{
- return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
- bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
- bottom.getCollation(), bottom.getType(), top.getName());
+ return AggregateCall.create(bottom.getAggregation(),
+ bottom.isDistinct(), bottom.isApproximate(), false,
+ bottom.getArgList(), bottom.filterArg, bottom.getCollation(),
+ bottom.getType(), top.getName());
} else {
return null;
}
@@ -309,9 +311,10 @@ public interface SqlSplittableAggFunction {
if (topKind == bottom.getAggregation().getKind()
&& (topKind == SqlKind.SUM
|| topKind == SqlKind.SUM0)) {
- return AggregateCall.create(bottom.getAggregation(),
bottom.isDistinct(),
- bottom.isApproximate(), bottom.getArgList(), bottom.filterArg,
- bottom.getCollation(), bottom.getType(), top.getName());
+ return AggregateCall.create(bottom.getAggregation(),
+ bottom.isDistinct(), bottom.isApproximate(), false,
+ bottom.getArgList(), bottom.filterArg, bottom.getCollation(),
+ bottom.getType(), top.getName());
} else {
return null;
}
diff --git
a/core/src/main/java/org/apache/calcite/statistic/CachingSqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/statistic/CachingSqlStatisticProvider.java
new file mode 100644
index 0000000..6d6b6e9
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/statistic/CachingSqlStatisticProvider.java
@@ -0,0 +1,86 @@
+/*
+ * 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.statistic;
+
+import org.apache.calcite.materialize.SqlStatisticProvider;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.util.ImmutableIntList;
+
+import com.google.common.base.Throwables;
+import com.google.common.cache.Cache;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+import java.util.concurrent.ExecutionException;
+
+/**
+ * Implementation of {@link SqlStatisticProvider} that reads and writes a
+ * cache.
+ */
+public class CachingSqlStatisticProvider implements SqlStatisticProvider {
+ private final SqlStatisticProvider provider;
+ private final Cache<List, Object> cache;
+
+ public CachingSqlStatisticProvider(SqlStatisticProvider provider,
+ Cache<List, Object> cache) {
+ super();
+ this.provider = provider;
+ this.cache = cache;
+ }
+
+ public double tableCardinality(RelOptTable table) {
+ try {
+ final ImmutableList<Object> key =
+ ImmutableList.of("tableCardinality",
+ table.getQualifiedName());
+ return (Double) cache.get(key,
+ () -> provider.tableCardinality(table));
+ } catch (ExecutionException e) {
+ throw Throwables.propagate(e.getCause());
+ }
+ }
+
+ public boolean isForeignKey(RelOptTable fromTable, List<Integer> fromColumns,
+ RelOptTable toTable, List<Integer> toColumns) {
+ try {
+ final ImmutableList<Object> key =
+ ImmutableList.of("isForeignKey",
+ fromTable.getQualifiedName(),
+ ImmutableIntList.copyOf(fromColumns),
+ toTable.getQualifiedName(),
+ ImmutableIntList.copyOf(toColumns));
+ return (Boolean) cache.get(key,
+ () -> provider.isForeignKey(fromTable, fromColumns, toTable,
+ toColumns));
+ } catch (ExecutionException e) {
+ throw Throwables.propagate(e.getCause());
+ }
+ }
+
+ public boolean isKey(RelOptTable table, List<Integer> columns) {
+ try {
+ final ImmutableList<Object> key =
+ ImmutableList.of("isKey", table.getQualifiedName(),
+ ImmutableIntList.copyOf(columns));
+ return (Boolean) cache.get(key, () -> provider.isKey(table, columns));
+ } catch (ExecutionException e) {
+ throw Throwables.propagate(e.getCause());
+ }
+ }
+}
+
+// End CachingSqlStatisticProvider.java
diff --git
a/core/src/main/java/org/apache/calcite/statistic/MapSqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/statistic/MapSqlStatisticProvider.java
new file mode 100644
index 0000000..5a37856
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/statistic/MapSqlStatisticProvider.java
@@ -0,0 +1,168 @@
+/*
+ * 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.statistic;
+
+import org.apache.calcite.adapter.jdbc.JdbcTable;
+import org.apache.calcite.materialize.SqlStatisticProvider;
+import org.apache.calcite.plan.RelOptTable;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableMultimap;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.Map;
+import java.util.stream.Collectors;
+
+/**
+ * Implementation of {@link SqlStatisticProvider} that looks up values in a
+ * table.
+ *
+ * <p>Only for testing.
+ */
+public enum MapSqlStatisticProvider implements SqlStatisticProvider {
+ INSTANCE;
+
+ private final Map<String, Double> cardinalityMap;
+
+ private final ImmutableMultimap<String, List<String>> keyMap;
+
+ MapSqlStatisticProvider() {
+ final Initializer initializer = new Initializer()
+ .put("foodmart", "agg_c_14_sales_fact_1997", 86_805, "id")
+ .put("foodmart", "account", 11, "account_id")
+ .put("foodmart", "category", 4, "category_id")
+ .put("foodmart", "currency", 10_281, "currency_id")
+ .put("foodmart", "customer", 10_281, "customer_id")
+ .put("foodmart", "days", 7, "day")
+ .put("foodmart", "employee", 1_155, "employee_id")
+ .put("foodmart", "employee_closure", 7_179)
+ .put("foodmart", "department", 10_281, "department_id")
+ .put("foodmart", "inventory_fact_1997", 4_070)
+ .put("foodmart", "position", 18, "position_id")
+ .put("foodmart", "product", 1560, "product_id")
+ .put("foodmart", "product_class", 110, "product_class_id")
+ .put("foodmart", "promotion", 1_864, "promotion_id")
+ // region really has 110 rows; made it smaller than store to trick FK
+ .put("foodmart", "region", 24, "region_id")
+ .put("foodmart", "salary", 21_252)
+ .put("foodmart", "sales_fact_1997", 86_837)
+ .put("foodmart", "store", 25, "store_id")
+ .put("foodmart", "store_ragged", 25, "store_id")
+ .put("foodmart", "time_by_day", 730, "time_id", "the_date") // 2 keys
+ .put("foodmart", "warehouse", 24, "warehouse_id")
+ .put("foodmart", "warehouse_class", 6, "warehouse_class_id")
+ .put("scott", "EMP", 10, "EMPNO")
+ .put("scott", "DEPT", 4, "DEPTNO")
+ .put("tpcds", "CALL_CENTER", 8, "id")
+ .put("tpcds", "CATALOG_PAGE", 11_718, "id")
+ .put("tpcds", "CATALOG_RETURNS", 144_067, "id")
+ .put("tpcds", "CATALOG_SALES", 1_441_548, "id")
+ .put("tpcds", "CUSTOMER", 100_000, "id")
+ .put("tpcds", "CUSTOMER_ADDRESS", 50_000, "id")
+ .put("tpcds", "CUSTOMER_DEMOGRAPHICS", 1_920_800, "id")
+ .put("tpcds", "DATE_DIM", 73049, "id")
+ .put("tpcds", "DBGEN_VERSION", 1, "id")
+ .put("tpcds", "HOUSEHOLD_DEMOGRAPHICS", 7200, "id")
+ .put("tpcds", "INCOME_BAND", 20, "id")
+ .put("tpcds", "INVENTORY", 11_745_000, "id")
+ .put("tpcds", "ITEM", 18_000, "id")
+ .put("tpcds", "PROMOTION", 300, "id")
+ .put("tpcds", "REASON", 35, "id")
+ .put("tpcds", "SHIP_MODE", 20, "id")
+ .put("tpcds", "STORE", 12, "id")
+ .put("tpcds", "STORE_RETURNS", 287_514, "id")
+ .put("tpcds", "STORE_SALES", 2_880_404, "id")
+ .put("tpcds", "TIME_DIM", 86_400, "id")
+ .put("tpcds", "WAREHOUSE", 5, "id")
+ .put("tpcds", "WEB_PAGE", 60, "id")
+ .put("tpcds", "WEB_RETURNS", 71_763, "id")
+ .put("tpcds", "WEB_SALES", 719_384, "id")
+ .put("tpcds", "WEB_SITE", 1, "id");
+ cardinalityMap = initializer.cardinalityMapBuilder.build();
+ keyMap = initializer.keyMapBuilder.build();
+ }
+
+ public double tableCardinality(RelOptTable table) {
+ final JdbcTable jdbcTable = table.unwrap(JdbcTable.class);
+ final List<String> qualifiedName;
+ if (jdbcTable != null) {
+ qualifiedName = Arrays.asList(jdbcTable.jdbcSchemaName,
+ jdbcTable.jdbcTableName);
+ } else {
+ qualifiedName = table.getQualifiedName();
+ }
+ return cardinalityMap.get(qualifiedName.toString());
+ }
+
+ public boolean isForeignKey(RelOptTable fromTable, List<Integer> fromColumns,
+ RelOptTable toTable, List<Integer> toColumns) {
+ // Assume that anything that references a primary key is a foreign key.
+ // It's wrong but it's enough for our current test cases.
+ return isKey(toTable, toColumns)
+ // supervisor_id contains one 0 value, which does not match any
+ // employee_id, therefore it is not a foreign key
+ && !"[foodmart, employee].[supervisor_id]"
+ .equals(fromTable.getQualifiedName() + "."
+ + columnNames(fromTable, fromColumns));
+ }
+
+ public boolean isKey(RelOptTable table, List<Integer> columns) {
+ // In order to match, all column ordinals must be in range 0 .. columnCount
+ return columns.stream().allMatch(columnOrdinal ->
+ (columnOrdinal >= 0)
+ && (columnOrdinal < table.getRowType().getFieldCount()))
+ // ... and the column names match the name of the primary key
+ && keyMap.get(table.getQualifiedName().toString())
+ .contains(columnNames(table, columns));
+ }
+
+ private List<String> columnNames(RelOptTable table, List<Integer> columns) {
+ return columns.stream()
+ .map(columnOrdinal -> table.getRowType().getFieldNames()
+ .get(columnOrdinal))
+ .collect(Collectors.toList());
+ }
+
+ /** Helper during construction. */
+ private static class Initializer {
+ final ImmutableMap.Builder<String, Double> cardinalityMapBuilder =
+ ImmutableMap.builder();
+ final ImmutableMultimap.Builder<String, List<String>> keyMapBuilder =
+ ImmutableMultimap.builder();
+
+ Initializer put(String schema, String table, int count, Object... keys) {
+ String qualifiedName = Arrays.asList(schema, table).toString();
+ cardinalityMapBuilder.put(qualifiedName, (double) count);
+ for (Object key : keys) {
+ final ImmutableList<String> keyList;
+ if (key instanceof String) {
+ keyList = ImmutableList.of((String) key);
+ } else if (key instanceof String[]) {
+ keyList = ImmutableList.copyOf((String[]) key); // composite key
+ } else {
+ throw new AssertionError("unknown key " + key);
+ }
+ keyMapBuilder.put(qualifiedName, keyList);
+ }
+ return this;
+ }
+ }
+}
+
+// End MapSqlStatisticProvider.java
diff --git
a/core/src/main/java/org/apache/calcite/statistic/QuerySqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/statistic/QuerySqlStatisticProvider.java
new file mode 100644
index 0000000..2f4af9d
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/statistic/QuerySqlStatisticProvider.java
@@ -0,0 +1,222 @@
+/*
+ * 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.statistic;
+
+import org.apache.calcite.adapter.jdbc.JdbcRules;
+import org.apache.calcite.adapter.jdbc.JdbcSchema;
+import org.apache.calcite.adapter.jdbc.JdbcTable;
+import org.apache.calcite.materialize.SqlStatisticProvider;
+import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptSchema;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.plan.ViewExpanders;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.rel2sql.RelToSqlConverter;
+import org.apache.calcite.rel.rel2sql.SqlImplementor;
+import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.tools.Frameworks;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.Util;
+
+import com.google.common.cache.CacheBuilder;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+import java.util.Objects;
+import java.util.concurrent.TimeUnit;
+import java.util.function.Consumer;
+import java.util.stream.Collectors;
+import javax.sql.DataSource;
+
+/**
+ * Implementation of {@link SqlStatisticProvider} that generates and executes
+ * SQL queries.
+ */
+public class QuerySqlStatisticProvider implements SqlStatisticProvider {
+ /** Instance that uses SQL to compute statistics,
+ * does not log SQL statements,
+ * and caches up to 1,024 results for up to 30 minutes.
+ * (That period should be sufficient for the
+ * duration of Calcite's tests, and many other purposes.) */
+ public static final SqlStatisticProvider SILENT_CACHING_INSTANCE =
+ new CachingSqlStatisticProvider(
+ new QuerySqlStatisticProvider(sql -> { }),
+ CacheBuilder.newBuilder().expireAfterAccess(30, TimeUnit.MINUTES)
+ .maximumSize(1_024).build());
+
+ /** As {@link #SILENT_CACHING_INSTANCE} but prints SQL statements to
+ * {@link System#out}. */
+ public static final SqlStatisticProvider VERBOSE_CACHING_INSTANCE =
+ new CachingSqlStatisticProvider(
+ new QuerySqlStatisticProvider(sql -> System.out.println(sql + ":")),
+ CacheBuilder.newBuilder().expireAfterAccess(30, TimeUnit.MINUTES)
+ .maximumSize(1_024).build());
+
+ private final Consumer<String> sqlConsumer;
+
+ /** Creates a QuerySqlStatisticProvider.
+ *
+ * @param sqlConsumer Called when each SQL statement is generated
+ */
+ public QuerySqlStatisticProvider(Consumer<String> sqlConsumer) {
+ this.sqlConsumer = Objects.requireNonNull(sqlConsumer);
+ }
+
+ public double tableCardinality(RelOptTable table) {
+ final JdbcTable jdbcTable = table.unwrap(JdbcTable.class);
+ return withBuilder(jdbcTable.jdbcSchema,
+ (cluster, relOptSchema, jdbcSchema, relBuilder) -> {
+ // Generate:
+ // SELECT COUNT(*) FROM `EMP`
+ relBuilder.push(table.toRel(ViewExpanders.simpleContext(cluster)))
+ .aggregate(relBuilder.groupKey(), relBuilder.count());
+
+ final String sql = toSql(relBuilder.build(), jdbcSchema.dialect);
+ final DataSource dataSource = jdbcSchema.getDataSource();
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(sql)) {
+ if (!resultSet.next()) {
+ throw new AssertionError("expected exactly 1 row: " + sql);
+ }
+ final double cardinality = resultSet.getDouble(1);
+ if (resultSet.next()) {
+ throw new AssertionError("expected exactly 1 row: " + sql);
+ }
+ return cardinality;
+ } catch (SQLException e) {
+ throw handle(e, sql);
+ }
+ });
+ }
+
+ public boolean isForeignKey(RelOptTable fromTable, List<Integer> fromColumns,
+ RelOptTable toTable, List<Integer> toColumns) {
+ final JdbcTable jdbcTable = fromTable.unwrap(JdbcTable.class);
+ return withBuilder(jdbcTable.jdbcSchema,
+ (cluster, relOptSchema, jdbcSchema, relBuilder) -> {
+ // EMP(DEPTNO) is a foreign key to DEPT(DEPTNO) if the following
+ // query returns 0:
+ //
+ // SELECT COUNT(*) FROM (
+ // SELECT deptno FROM `EMP` WHERE deptno IS NOT NULL
+ // MINUS
+ // SELECT deptno FROM `DEPT`)
+ final RelOptTable.ToRelContext toRelContext =
+ ViewExpanders.simpleContext(cluster);
+ relBuilder.push(fromTable.toRel(toRelContext))
+ .filter(fromColumns.stream()
+ .map(column ->
+ relBuilder.call(SqlStdOperatorTable.IS_NOT_NULL,
+ relBuilder.field(column)))
+ .collect(Collectors.toList()))
+ .project(relBuilder.fields(fromColumns))
+ .push(toTable.toRel(toRelContext))
+ .project(relBuilder.fields(toColumns))
+ .minus(false, 2)
+ .aggregate(relBuilder.groupKey(), relBuilder.count());
+
+ final String sql = toSql(relBuilder.build(), jdbcSchema.dialect);
+ final DataSource dataSource = jdbcTable.jdbcSchema.getDataSource();
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(sql)) {
+ if (!resultSet.next()) {
+ throw new AssertionError("expected exactly 1 row: " + sql);
+ }
+ final int count = resultSet.getInt(1);
+ if (resultSet.next()) {
+ throw new AssertionError("expected exactly 1 row: " + sql);
+ }
+ return count == 0;
+ } catch (SQLException e) {
+ throw handle(e, sql);
+ }
+ });
+ }
+
+ public boolean isKey(RelOptTable table, List<Integer> columns) {
+ final JdbcTable jdbcTable = table.unwrap(JdbcTable.class);
+ return withBuilder(jdbcTable.jdbcSchema,
+ (cluster, relOptSchema, jdbcSchema, relBuilder) -> {
+ // The collection of columns ['DEPTNO'] is a key for 'EMP' if the
+ // following query returns no rows:
+ //
+ // SELECT 1
+ // FROM `EMP`
+ // GROUP BY `DEPTNO`
+ // HAVING COUNT(*) > 1
+ //
+ final RelOptTable.ToRelContext toRelContext =
+ ViewExpanders.simpleContext(cluster);
+ relBuilder.push(table.toRel(toRelContext))
+ .aggregate(relBuilder.groupKey(relBuilder.fields(columns)),
+ relBuilder.count())
+ .filter(
+ relBuilder.call(SqlStdOperatorTable.GREATER_THAN,
+ Util.last(relBuilder.fields()), relBuilder.literal(1)));
+ final String sql = toSql(relBuilder.build(), jdbcSchema.dialect);
+
+ final DataSource dataSource = jdbcSchema.getDataSource();
+ try (Connection connection = dataSource.getConnection();
+ Statement statement = connection.createStatement();
+ ResultSet resultSet = statement.executeQuery(sql)) {
+ return !resultSet.next();
+ } catch (SQLException e) {
+ throw handle(e, sql);
+ }
+ });
+ }
+
+ private RuntimeException handle(SQLException e, String sql) {
+ return new RuntimeException("Error while executing SQL for statistics: "
+ + sql, e);
+ }
+
+ protected String toSql(RelNode rel, SqlDialect dialect) {
+ final RelToSqlConverter converter = new RelToSqlConverter(dialect);
+ SqlImplementor.Result result = converter.visitChild(0, rel);
+ final SqlNode sqlNode = result.asStatement();
+ final String sql = sqlNode.toSqlString(dialect).getSql();
+ sqlConsumer.accept(sql);
+ return sql;
+ }
+
+ private <R> R withBuilder(JdbcSchema jdbcSchema, BuilderAction<R> action) {
+ return Frameworks.withPlanner(
+ (cluster, relOptSchema, rootSchema) -> {
+ final RelBuilder relBuilder =
+ JdbcRules.JDBC_BUILDER.create(cluster, relOptSchema);
+ return action.apply(cluster, relOptSchema, jdbcSchema, relBuilder);
+ });
+ }
+
+ /** Performs an action with a {@link RelBuilder}.
+ *
+ * @param <R> Result type */
+ private interface BuilderAction<R> {
+ R apply(RelOptCluster cluster, RelOptSchema relOptSchema,
+ JdbcSchema jdbcSchema, RelBuilder relBuilder);
+ }
+}
+
+// End QuerySqlStatisticProvider.java
diff --git
a/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
b/core/src/main/java/org/apache/calcite/statistic/package-info.java
similarity index 68%
copy from
core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
copy to core/src/main/java/org/apache/calcite/statistic/package-info.java
index 8d045e1..e5abe29 100644
---
a/core/src/main/java/org/apache/calcite/materialize/SqlStatisticProvider.java
+++ b/core/src/main/java/org/apache/calcite/statistic/package-info.java
@@ -14,18 +14,15 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
-package org.apache.calcite.materialize;
-
-import java.util.List;
/**
- * Estimates row counts for tables and columns.
+ * Implementations of statistics providers.
*
- * <p>Unlike {@link LatticeStatisticProvider}, works on raw tables and columns
- * and does not need a {@link Lattice}.
+ * @see org.apache.calcite.materialize.SqlStatisticProvider
*/
-public interface SqlStatisticProvider {
- double tableCardinality(List<String> qualifiedTableName);
-}
+@PackageMarker
+package org.apache.calcite.statistic;
+
+import org.apache.calcite.avatica.util.PackageMarker;
-// End SqlStatisticProvider.java
+// End package-info.java
diff --git a/core/src/main/java/org/apache/calcite/tools/Frameworks.java
b/core/src/main/java/org/apache/calcite/tools/Frameworks.java
index 49cd1ac..a273a26 100644
--- a/core/src/main/java/org/apache/calcite/tools/Frameworks.java
+++ b/core/src/main/java/org/apache/calcite/tools/Frameworks.java
@@ -18,7 +18,6 @@ package org.apache.calcite.tools;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteSchema;
-import org.apache.calcite.materialize.MapSqlStatisticProvider;
import org.apache.calcite.materialize.SqlStatisticProvider;
import org.apache.calcite.plan.Context;
import org.apache.calcite.plan.RelOptCluster;
@@ -38,6 +37,7 @@ import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql2rel.SqlRexConvertletTable;
import org.apache.calcite.sql2rel.SqlToRelConverter;
import org.apache.calcite.sql2rel.StandardConvertletTable;
+import org.apache.calcite.statistic.QuerySqlStatisticProvider;
import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableList;
@@ -214,7 +214,7 @@ public class Frameworks {
sqlToRelConverterConfig = SqlToRelConverter.Config.DEFAULT;
typeSystem = RelDataTypeSystem.DEFAULT;
evolveLattice = false;
- statisticProvider = MapSqlStatisticProvider.INSTANCE;
+ statisticProvider = QuerySqlStatisticProvider.SILENT_CACHING_INSTANCE;
}
/** Creates a ConfigBuilder, initializing from an existing config. */
diff --git
a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
index 92e562b..339cfad 100644
---
a/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
+++
b/core/src/test/java/org/apache/calcite/materialize/LatticeSuggesterTest.java
@@ -21,6 +21,7 @@ import org.apache.calcite.rel.RelRoot;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParseException;
+import org.apache.calcite.statistic.MapSqlStatisticProvider;
import org.apache.calcite.test.CalciteAssert;
import org.apache.calcite.test.FoodMartQuerySet;
import org.apache.calcite.test.SlowTests;
@@ -334,10 +335,7 @@ public class LatticeSuggesterTest {
+ "[foodmart, warehouse_class]], "
+ "edges: ["
+ "Step([foodmart, agg_c_14_sales_fact_1997], [foodmart, store],
store_id:store_id), "
- + "Step([foodmart, agg_c_14_sales_fact_1997], [foodmart, time_by_day],"
- + " month_of_year:month_of_year), "
+ "Step([foodmart, customer], [foodmart, region],
customer_region_id:region_id), "
- + "Step([foodmart, customer], [foodmart, store],
state_province:store_state), "
+ "Step([foodmart, employee], [foodmart, employee],
supervisor_id:employee_id), "
+ "Step([foodmart, employee], [foodmart, position],
position_id:position_id), "
+ "Step([foodmart, employee], [foodmart, store], store_id:store_id), "
@@ -352,7 +350,6 @@ public class LatticeSuggesterTest {
+ "Step([foodmart, product], [foodmart, product_class],"
+ " product_class_id:product_class_id), "
+ "Step([foodmart, product], [foodmart, store],
product_class_id:store_id), "
- + "Step([foodmart, product_class], [foodmart, store],
product_class_id:region_id), "
+ "Step([foodmart, salary], [foodmart, department],
department_id:department_id), "
+ "Step([foodmart, salary], [foodmart, employee],
employee_id:employee_id), "
+ "Step([foodmart, salary], [foodmart, employee_closure],
employee_id:employee_id), "
@@ -367,21 +364,24 @@ public class LatticeSuggesterTest {
+ "Step([foodmart, sales_fact_1997], [foodmart, store_ragged],
store_id:store_id), "
+ "Step([foodmart, sales_fact_1997], [foodmart, time_by_day],
product_id:time_id), "
+ "Step([foodmart, sales_fact_1997], [foodmart, time_by_day],
time_id:time_id), "
+ + "Step([foodmart, store], [foodmart, customer],
store_state:state_province), "
+ + "Step([foodmart, store], [foodmart, product_class],
region_id:product_class_id), "
+ "Step([foodmart, store], [foodmart, region], region_id:region_id), "
- + "Step([foodmart, store], [foodmart, warehouse], store_id:stores_id),
"
+ + "Step([foodmart, time_by_day], [foodmart, agg_c_14_sales_fact_1997],
month_of_year:month_of_year), "
+ + "Step([foodmart, warehouse], [foodmart, store], stores_id:store_id),
"
+ "Step([foodmart, warehouse], [foodmart, warehouse_class],"
+ " warehouse_class_id:warehouse_class_id)])";
assertThat(t.s.space.g.toString(), is(expected));
if (evolve) {
- // compared to evolve=false, there are a few more nodes (133 vs 117),
- // the same number of paths, and a lot fewer lattices (27 vs 376)
- assertThat(t.s.space.nodeMap.size(), is(133));
+ // compared to evolve=false, there are a few more nodes (137 vs 119),
+ // the same number of paths, and a lot fewer lattices (27 vs 388)
+ assertThat(t.s.space.nodeMap.size(), is(137));
assertThat(t.s.latticeMap.size(), is(27));
- assertThat(t.s.space.pathMap.size(), is(42));
+ assertThat(t.s.space.pathMap.size(), is(46));
} else {
- assertThat(t.s.space.nodeMap.size(), is(117));
- assertThat(t.s.latticeMap.size(), is(386));
- assertThat(t.s.space.pathMap.size(), is(42));
+ assertThat(t.s.space.nodeMap.size(), is(119));
+ assertThat(t.s.latticeMap.size(), is(388));
+ assertThat(t.s.space.pathMap.size(), is(46));
}
}
@@ -572,6 +572,7 @@ public class LatticeSuggesterTest {
this(
Frameworks.newConfigBuilder()
.defaultSchema(schemaFrom(CalciteAssert.SchemaSpec.SCOTT))
+ .statisticProvider(MapSqlStatisticProvider.INSTANCE)
.build());
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index d391a2e..604ab66 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1440,19 +1440,21 @@ public class RelToSqlConverterTest {
+ "WHERE v2.job LIKE 'PRESIDENT'";
final String expected = "SELECT \"DEPT\".\"DEPTNO\","
+ " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n"
- + "FROM \"JDBC_SCOTT\".\"DEPT\"\n"
- + "LEFT JOIN \"JDBC_SCOTT\".\"EMP\""
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "LEFT JOIN \"SCOTT\".\"EMP\""
+ " ON \"DEPT\".\"DEPTNO\" = \"EMP\".\"DEPTNO\"\n"
+ "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
- final String expected2 = "SELECT DEPT.DEPTNO, EMP.DEPTNO AS DEPTNO0\n"
- + "FROM JDBC_SCOTT.DEPT AS DEPT\n"
- + "LEFT JOIN JDBC_SCOTT.EMP AS EMP ON DEPT.DEPTNO = EMP.DEPTNO\n"
+ // DB2 does not have implicit aliases, so generates explicit "AS DEPT"
+ // and "AS EMP"
+ final String expectedDb2 = "SELECT DEPT.DEPTNO, EMP.DEPTNO AS DEPTNO0\n"
+ + "FROM SCOTT.DEPT AS DEPT\n"
+ + "LEFT JOIN SCOTT.EMP AS EMP ON DEPT.DEPTNO = EMP.DEPTNO\n"
+ "WHERE EMP.JOB LIKE 'PRESIDENT'";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected)
.withDb2()
- .ok(expected2);
+ .ok(expectedDb2);
}
/** Test case for
diff --git a/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
index 1024e80..703cf46 100644
--- a/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
+++ b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
@@ -175,6 +175,7 @@ import org.junit.runners.Suite;
RexProgramFuzzyTest.class,
SqlToRelConverterTest.class,
ProfilerTest.class,
+ SqlStatisticProviderTest.class,
SqlAdvisorJdbcTest.class,
CoreQuidemTest.class,
CalciteRemoteDriverTest.class,
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlStatisticProviderTest.java
b/core/src/test/java/org/apache/calcite/test/SqlStatisticProviderTest.java
new file mode 100644
index 0000000..f34093d
--- /dev/null
+++ b/core/src/test/java/org/apache/calcite/test/SqlStatisticProviderTest.java
@@ -0,0 +1,135 @@
+/*
+ * 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.config.CalciteSystemProperty;
+import org.apache.calcite.materialize.SqlStatisticProvider;
+import org.apache.calcite.plan.RelOptTable;
+import org.apache.calcite.plan.RelTraitDef;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.schema.SchemaPlus;
+import org.apache.calcite.sql.parser.SqlParser;
+import org.apache.calcite.statistic.CachingSqlStatisticProvider;
+import org.apache.calcite.statistic.MapSqlStatisticProvider;
+import org.apache.calcite.statistic.QuerySqlStatisticProvider;
+import org.apache.calcite.tools.Frameworks;
+import org.apache.calcite.tools.Programs;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.util.Util;
+
+import com.google.common.cache.Cache;
+import com.google.common.cache.CacheBuilder;
+
+import org.junit.Test;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.concurrent.TimeUnit;
+import java.util.concurrent.atomic.AtomicInteger;
+import java.util.function.Consumer;
+import java.util.stream.Collectors;
+
+import static org.hamcrest.core.Is.is;
+import static org.junit.Assert.assertThat;
+
+/**
+ * Unit test for {@link org.apache.calcite.materialize.SqlStatisticProvider}
+ * and implementations of it.
+ */
+public class SqlStatisticProviderTest {
+ /** Creates a config based on the "foodmart" schema. */
+ public static Frameworks.ConfigBuilder config() {
+ final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ return Frameworks.newConfigBuilder()
+ .parserConfig(SqlParser.Config.DEFAULT)
+ .defaultSchema(
+ CalciteAssert.addSchema(rootSchema,
+ CalciteAssert.SchemaSpec.JDBC_FOODMART))
+ .traitDefs((List<RelTraitDef>) null)
+ .programs(Programs.heuristicJoinOrder(Programs.RULE_SET, true, 2));
+ }
+
+ @Test public void testMapProvider() {
+ check(MapSqlStatisticProvider.INSTANCE);
+ }
+
+ @Test public void testQueryProvider() {
+ final boolean debug = CalciteSystemProperty.DEBUG.value();
+ final Consumer<String> sqlConsumer =
+ debug ? System.out::println : Util::discard;
+ check(new QuerySqlStatisticProvider(sqlConsumer));
+ }
+
+ @Test public void testQueryProviderWithCache() {
+ Cache<List, Object> cache = CacheBuilder.newBuilder()
+ .expireAfterAccess(5, TimeUnit.MINUTES)
+ .build();
+ final AtomicInteger counter = new AtomicInteger();
+ QuerySqlStatisticProvider provider =
+ new QuerySqlStatisticProvider(sql -> counter.incrementAndGet());
+ final SqlStatisticProvider cachingProvider =
+ new CachingSqlStatisticProvider(provider, cache);
+ check(cachingProvider);
+ final int expectedQueryCount = 6;
+ assertThat(counter.get(), is(expectedQueryCount));
+ check(cachingProvider);
+ assertThat(counter.get(), is(expectedQueryCount)); // no more queries
+ }
+
+ private void check(SqlStatisticProvider provider) {
+ final RelBuilder relBuilder = RelBuilder.create(config().build());
+ final RelNode productScan = relBuilder.scan("product").build();
+ final RelOptTable productTable = productScan.getTable();
+ final RelNode salesScan = relBuilder.scan("sales_fact_1997").build();
+ final RelOptTable salesTable = salesScan.getTable();
+ final RelNode employeeScan = relBuilder.scan("employee").build();
+ final RelOptTable employeeTable = employeeScan.getTable();
+ assertThat(provider.tableCardinality(productTable), is(1_560.0d));
+ assertThat(
+ provider.isKey(productTable, columns(productTable, "product_id")),
+ is(true));
+ assertThat(
+ provider.isKey(salesTable, columns(salesTable, "product_id")),
+ is(false));
+ assertThat(
+ provider.isForeignKey(salesTable, columns(salesTable, "product_id"),
+ productTable, columns(productTable, "product_id")),
+ is(true));
+ // Not a foreign key; product has some ids that are not referenced by any
+ // sale
+ assertThat(
+ provider.isForeignKey(
+ productTable, columns(productTable, "product_id"),
+ salesTable, columns(salesTable, "product_id")),
+ is(false));
+ // There is one supervisor_id, 0, which is not an employee_id
+ assertThat(
+ provider.isForeignKey(
+ employeeTable, columns(employeeTable, "supervisor_id"),
+ employeeTable, columns(employeeTable, "employee_id")),
+ is(false));
+ }
+
+ private List<Integer> columns(RelOptTable table, String... columnNames) {
+ return Arrays.stream(columnNames)
+ .map(columnName ->
+ table.getRowType().getFieldNames().indexOf(columnName))
+ .collect(Collectors.toList());
+ }
+}
+
+// End SqlStatisticProviderTest.java
