This is an automated email from the ASF dual-hosted git repository.
zabetak pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new c945b7f49b [CALCITE-5314] Prune empty parts of a query by exploiting
stats/metadata
c945b7f49b is described below
commit c945b7f49b99538748c871557f6ac80957be2b6e
Author: Hanumath Maduri <[email protected]>
AuthorDate: Sun Oct 9 08:56:00 2022 -0700
[CALCITE-5314] Prune empty parts of a query by exploiting stats/metadata
Close apache/calcite#2935
---
.../java/org/apache/calcite/plan/RelOptRules.java | 1 +
.../apache/calcite/rel/rules/PruneEmptyRules.java | 45 +++++++++++++++
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
.../org/apache/calcite/test/RelOptRulesTest.java | 26 +++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 44 +++++++++++++++
.../calcite/test/catalog/MockCatalogReader.java | 65 +++++++++++++++++++---
.../test/catalog/MockCatalogReaderSimple.java | 8 +++
7 files changed, 183 insertions(+), 7 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
index 760cd0649e..a875ce85d5 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptRules.java
@@ -103,6 +103,7 @@ public class RelOptRules {
PruneEmptyRules.JOIN_LEFT_INSTANCE,
PruneEmptyRules.JOIN_RIGHT_INSTANCE,
PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE,
+ PruneEmptyRules.EMPTY_TABLE_INSTANCE,
CoreRules.UNION_MERGE,
CoreRules.INTERSECT_MERGE,
CoreRules.MINUS_MERGE,
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
index a5977f5ae6..34780e1cf3 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
@@ -33,6 +33,7 @@ import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Minus;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.logical.LogicalValues;
@@ -165,6 +166,19 @@ public abstract class PruneEmptyRules {
return false;
}
+ /**
+ * Rule that converts a {@link org.apache.calcite.rel.core.TableScan}
+ * to empty if the table has no rows in it.
+ *
+ * The rule exploits the {@link
org.apache.calcite.rel.metadata.RelMdMaxRowCount} to derive if
+ * the table is empty or not.
+ */
+ public static final RelOptRule EMPTY_TABLE_INSTANCE =
+ ImmutableZeroMaxRowsRuleConfig.of()
+ .withOperandSupplier(b0 -> b0.operand(TableScan.class).noInputs())
+ .withDescription("PruneZeroRowsTable")
+ .toRule();
+
/**
* Rule that converts a {@link org.apache.calcite.rel.core.Project}
* to empty if its child is empty.
@@ -540,4 +554,35 @@ public abstract class PruneEmptyRules {
};
}
}
+
+ /** Configuration for rule that transforms an empty relational expression
into an empty values.
+ *
+ * It relies on {@link org.apache.calcite.rel.metadata.RelMdMaxRowCount} to
derive if the relation
+ * is empty or not. If the stats are not available then the rule is a noop.
*/
+ @Value.Immutable
+ public interface ZeroMaxRowsRuleConfig extends PruneEmptyRule.Config {
+
+ @Override default PruneEmptyRule toRule() {
+ return new PruneEmptyRule(this) {
+ @Override public boolean matches(RelOptRuleCall call) {
+ RelNode node = call.rel(0);
+ Double maxRowCount = call.getMetadataQuery().getMaxRowCount(node);
+ return maxRowCount != null && maxRowCount == 0.0;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ RelNode node = call.rel(0);
+ RelNode emptyValues = call.builder().push(node).empty().build();
+ RelTraitSet traits = node.getTraitSet();
+ // propagate all traits (except convention) from the original
tableScan
+ // into the empty values
+ if (emptyValues.getConvention() != null) {
+ traits = traits.replace(emptyValues.getConvention());
+ }
+ emptyValues = emptyValues.copy(traits, Collections.emptyList());
+ call.transformTo(emptyValues);
+ }
+ };
+ }
+ }
}
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 54032f3db1..9f47e6b28c 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -87,6 +87,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"TABLE(CATALOG.SALES.EMP_B)",
"TABLE(CATALOG.SALES.EMP_20)",
"TABLE(CATALOG.SALES.EMPNULLABLES_20)",
+ "TABLE(CATALOG.SALES.EMPTY_PRODUCTS)",
"TABLE(CATALOG.SALES.EMP_ADDRESS)",
"TABLE(CATALOG.SALES.DEPT)",
"TABLE(CATALOG.SALES.DEPT_NESTED)",
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index a8f04a1582..868f4f7885 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -135,6 +135,7 @@ import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import java.util.Arrays;
+import java.util.Collection;
import java.util.Collections;
import java.util.EnumSet;
import java.util.List;
@@ -3426,6 +3427,31 @@ class RelOptRulesTest extends RelOptTestBase {
.check();
}
+ @Test void testEmptyTable() {
+ // table is transformed to empty values and extra project will be removed.
+ final String sql = "select * from EMPTY_PRODUCTS\n";
+ sql(sql)
+ .withRule(
+ PruneEmptyRules.EMPTY_TABLE_INSTANCE,
+ PruneEmptyRules.PROJECT_INSTANCE)
+ .check();
+ }
+
+ @Test void testEmptyTableTransformsComplexQueryToSingleTableScan() {
+ final String sql = "select products.PRODUCTID, products.NAME from products
left join\n"
+ + "(select * from products as e\n"
+ + " inner join EMPTY_PRODUCTS as d on e.PRODUCTID = d.PRODUCTID\n"
+ + " where e.SUPPLIERID > 10) dt\n"
+ + " on products.PRODUCTID = dt.PRODUCTID";
+ Collection<RelOptRule> rules = Arrays.asList(
+ PruneEmptyRules.EMPTY_TABLE_INSTANCE,
+ PruneEmptyRules.JOIN_RIGHT_INSTANCE,
+ PruneEmptyRules.FILTER_INSTANCE,
+ PruneEmptyRules.PROJECT_INSTANCE,
+ CoreRules.PROJECT_MERGE);
+
sql(sql).withProgram(HepProgram.builder().addRuleCollection(rules).build()).check();
+ }
+
@Test void testLeftEmptyInnerJoin() {
// Plan should be empty
final String sql = "select * from (\n"
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 61957b201c..6a93a89044 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -3058,6 +3058,50 @@ LogicalSort(sort0=[$7], dir0=[ASC], fetch=[0])
<Resource name="planAfter">
<![CDATA[
LogicalValues(tuples=[[]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testEmptyTable">
+ <Resource name="sql">
+ <![CDATA[select * from EMPTY_PRODUCTS
+]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPTY_PRODUCTS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalValues(tuples=[[]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testEmptyTableTransformsComplexQueryToSingleTableScan">
+ <Resource name="sql">
+ <![CDATA[select products.PRODUCTID, products.NAME from products left join
+(select * from products as e
+ inner join EMPTY_PRODUCTS as d on e.PRODUCTID = d.PRODUCTID
+ where e.SUPPLIERID > 10) dt
+ on products.PRODUCTID = dt.PRODUCTID]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(PRODUCTID=[$0], NAME=[$1])
+ LogicalJoin(condition=[=($0, $3)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS]])
+ LogicalProject(PRODUCTID=[$0], NAME=[$1], SUPPLIERID=[$2],
PRODUCTID0=[$3], NAME0=[$4], SUPPLIERID0=[$5])
+ LogicalFilter(condition=[>($2, 10)])
+ LogicalJoin(condition=[=($0, $3)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMPTY_PRODUCTS]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(PRODUCTID=[$0], NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, PRODUCTS]])
]]>
</Resource>
</TestCase>
diff --git
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReader.java
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReader.java
index ca1cbf90c9..52bb25ea4b 100644
---
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReader.java
+++
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReader.java
@@ -40,6 +40,9 @@ import org.apache.calcite.rel.RelReferentialConstraint;
import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.logical.LogicalTableScan;
+import org.apache.calcite.rel.metadata.BuiltInMetadata;
+import org.apache.calcite.rel.metadata.MetadataDef;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.DynamicRecordTypeImpl;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -291,7 +294,9 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
* Mock implementation of
* {@link org.apache.calcite.prepare.Prepare.PreparingTable}.
*/
- public static class MockTable extends Prepare.AbstractPreparingTable {
+ public static class MockTable extends Prepare.AbstractPreparingTable
+ implements BuiltInMetadata.MaxRowCount.Handler {
+
protected final MockCatalogReader catalogReader;
protected final boolean stream;
protected final double rowCount;
@@ -303,6 +308,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
protected RelDataType rowType;
protected List<RelCollation> collationList;
protected final List<String> names;
+ protected final Double maxRowCount;
protected final Set<String> monotonicColumnSet = new HashSet<>();
protected StructKind kind = StructKind.FULLY_QUALIFIED;
protected final ColumnResolver resolver;
@@ -321,7 +327,16 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
InitializerExpressionFactory initializerFactory) {
this(catalogReader, ImmutableList.of(catalogName, schemaName, name),
stream, temporal, rowCount, resolver, initializerFactory,
- ImmutableList.of());
+ ImmutableList.of(), Double.POSITIVE_INFINITY);
+ }
+
+ public MockTable(MockCatalogReader catalogReader, String catalogName,
+ String schemaName, String name, boolean stream, boolean temporal,
+ double rowCount, ColumnResolver resolver,
+ InitializerExpressionFactory initializerFactory, Double maxRowCount) {
+ this(catalogReader, ImmutableList.of(catalogName, schemaName, name),
+ stream, temporal, rowCount, resolver, initializerFactory,
+ ImmutableList.of(), maxRowCount);
}
public void registerRolledUpColumn(String columnName) {
@@ -331,7 +346,8 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
private MockTable(MockCatalogReader catalogReader, List<String> names,
boolean stream, boolean temporal, double rowCount,
ColumnResolver resolver,
- InitializerExpressionFactory initializerFactory, List<Object> wraps) {
+ InitializerExpressionFactory initializerFactory, List<Object> wraps,
+ Double maxRowCount) {
this.catalogReader = catalogReader;
this.stream = stream;
this.temporal = temporal;
@@ -340,6 +356,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
this.resolver = resolver;
this.initializerFactory = initializerFactory;
this.wraps = ImmutableList.copyOf(wraps);
+ this.maxRowCount = maxRowCount;
}
/**
@@ -360,6 +377,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
this.names = names;
this.kind = kind;
this.resolver = resolver;
+ this.maxRowCount = Double.POSITIVE_INFINITY;
this.initializerFactory = initializerFactory;
for (String name : monotonicColumnSet) {
addMonotonic(name);
@@ -374,6 +392,14 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
wraps.add(wrap);
}
+ @Override public @Nullable Double getMaxRowCount(RelNode r,
RelMetadataQuery mq) {
+ return maxRowCount;
+ }
+
+ @Override public MetadataDef<BuiltInMetadata.MaxRowCount> getDef() {
+ return BuiltInMetadata.MaxRowCount.Handler.super.getDef();
+ }
+
/** Implementation of AbstractModifiableTable. */
private class ModifiableTable extends AbstractModifiableTable
implements ExtensibleTable, Wrapper {
@@ -443,7 +469,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
@Override protected RelOptTable extend(final Table extendedTable) {
return new MockTable(catalogReader, names, stream, temporal, rowCount,
- resolver, initializerFactory, wraps) {
+ resolver, initializerFactory, wraps, maxRowCount) {
@Override public RelDataType getRowType() {
return extendedTable.getRowType(catalogReader.typeFactory);
}
@@ -455,10 +481,15 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
return create(catalogReader, schema, name, stream, rowCount, null);
}
+ public static MockTable create(MockCatalogReader catalogReader,
+ MockSchema schema, String name, boolean stream, double rowCount,
double maxRowCount) {
+ return create(catalogReader, schema, name, stream, rowCount, null,
maxRowCount);
+ }
+
public static MockTable create(MockCatalogReader catalogReader,
List<String> names, boolean stream, double rowCount) {
return new MockTable(catalogReader, names, stream, false, rowCount, null,
- NullInitializerExpressionFactory.INSTANCE, ImmutableList.of());
+ NullInitializerExpressionFactory.INSTANCE, ImmutableList.of(),
Double.POSITIVE_INFINITY);
}
public static MockTable create(MockCatalogReader catalogReader,
@@ -468,6 +499,26 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
NullInitializerExpressionFactory.INSTANCE, false);
}
+ public static MockTable create(MockCatalogReader catalogReader,
+ MockSchema schema, String name, boolean stream, double rowCount,
+ ColumnResolver resolver, double maxRowCount) {
+ return create(catalogReader, schema, name, stream, rowCount, resolver,
+ NullInitializerExpressionFactory.INSTANCE, false, maxRowCount);
+ }
+
+ public static MockTable create(MockCatalogReader catalogReader,
+ MockSchema schema, String name, boolean stream, double rowCount,
+ ColumnResolver resolver,
+ InitializerExpressionFactory initializerExpressionFactory,
+ boolean temporal, Double maxRowCount) {
+ MockTable table =
+ new MockTable(catalogReader, schema.getCatalogName(), schema.name,
+ name, stream, temporal, rowCount, resolver,
+ initializerExpressionFactory, maxRowCount);
+ schema.addTable(name);
+ return table;
+ }
+
public static MockTable create(MockCatalogReader catalogReader,
MockSchema schema, String name, boolean stream, double rowCount,
ColumnResolver resolver,
@@ -631,7 +682,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
InitializerExpressionFactory initializerExpressionFactory) {
super(catalogReader, ImmutableList.of(catalogName, schemaName, name),
stream, false, rowCount, resolver, initializerExpressionFactory,
- ImmutableList.of());
+ ImmutableList.of(), Double.POSITIVE_INFINITY);
this.modifiableViewTable = modifiableViewTable;
}
@@ -744,7 +795,7 @@ public abstract class MockCatalogReader extends
CalciteCatalogReader {
InitializerExpressionFactory initializerExpressionFactory) {
super(catalogReader, ImmutableList.of(catalogName, schemaName, name),
stream, false, rowCount, resolver, initializerExpressionFactory,
- ImmutableList.of());
+ ImmutableList.of(), Double.POSITIVE_INFINITY);
this.viewTable = viewTable;
}
diff --git
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
index 5b69c1e279..4418b7a4b8 100644
---
a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
+++
b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
@@ -261,6 +261,14 @@ public class MockCatalogReaderSimple extends
MockCatalogReader {
productsTable.addColumn("SUPPLIERID", fixture.intType);
registerTable(productsTable);
+ // Register "EMPTY_PRODUCTS" table.
+ MockTable emptyProductsTable = MockTable.create(this, salesSchema,
"EMPTY_PRODUCTS",
+ false, 0D, 0D);
+ emptyProductsTable.addColumn("PRODUCTID", fixture.intType);
+ emptyProductsTable.addColumn("NAME", fixture.varchar20Type);
+ emptyProductsTable.addColumn("SUPPLIERID", fixture.intType);
+ registerTable(emptyProductsTable);
+
// Register "PRODUCTS_TEMPORAL" table.
MockTable productsTemporalTable =
MockTable.create(this, salesSchema, "PRODUCTS_TEMPORAL", false, 200D,
