calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [2/4] calcite git commit: [CALCITE-1334] Convert predicates on EXTRACT function calls into date ranges
Date Tue, 06 Sep 2016 23:44:28 GMT
[CALCITE-1334] Convert predicates on EXTRACT function calls into date ranges

Test the code on the 'timestamp' column of a Druid table.

This change requires RangeSet, and therefore Guava 14 or higher.


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/87435a9b
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/87435a9b
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/87435a9b

Branch: refs/heads/master
Commit: 87435a9b86e0d44a6c9123c028e5c5180fe3da88
Parents: 8eca1c9
Author: Julian Hyde <jhyde@apache.org>
Authored: Sat Jul 30 12:56:19 2016 -0700
Committer: Julian Hyde <jhyde@apache.org>
Committed: Tue Sep 6 11:39:10 2016 -0700

----------------------------------------------------------------------
 .../org/apache/calcite/plan/RelOptUtil.java     |   2 +
 .../calcite/rel/rules/DateRangeRules.java       | 362 +++++++++++++++++++
 .../java/org/apache/calcite/sql/SqlKind.java    |   7 +-
 .../calcite/sql/fun/SqlExtractFunction.java     |   6 +-
 .../calcite/sql/fun/SqlStdOperatorTable.java    |   2 +-
 .../calcite/rel/rules/DateRangeRulesTest.java   | 218 +++++++++++
 .../apache/calcite/sql/test/SqlAdvisorTest.java |   1 +
 .../org/apache/calcite/test/CalciteSuite.java   |   2 +
 .../apache/calcite/test/MockCatalogReader.java  |  18 +-
 .../apache/calcite/test/RelOptRulesTest.java    |  26 ++
 .../org/apache/calcite/test/RelOptTestBase.java |   4 +
 .../calcite/test/RexImplicationCheckerTest.java | 100 ++---
 .../org/apache/calcite/test/RelOptRulesTest.xml |  42 +++
 .../adapter/druid/DruidConnectionImpl.java      |  59 ++-
 .../calcite/adapter/druid/DruidQuery.java       |  21 +-
 .../adapter/druid/DruidTableFactory.java        |   2 +-
 .../org/apache/calcite/test/DruidAdapterIT.java |  24 +-
 .../test/resources/druid-foodmart-model.json    |   2 +-
 pom.xml                                         |   2 +-
 19 files changed, 812 insertions(+), 88 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index 7cc5212..e1ef79d 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -41,6 +41,7 @@ import org.apache.calcite.rel.logical.LogicalFilter;
 import org.apache.calcite.rel.logical.LogicalJoin;
 import org.apache.calcite.rel.logical.LogicalProject;
 import org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule;
+import org.apache.calcite.rel.rules.DateRangeRules;
 import org.apache.calcite.rel.rules.FilterMergeRule;
 import org.apache.calcite.rel.rules.MultiJoin;
 import org.apache.calcite.rel.rules.ProjectToWindowRule;
@@ -1685,6 +1686,7 @@ public abstract class RelOptUtil {
     planner.addRule(PruneEmptyRules.SORT_FETCH_ZERO_INSTANCE);
     planner.addRule(ProjectToWindowRule.PROJECT);
     planner.addRule(FilterMergeRule.INSTANCE);
+    planner.addRule(DateRangeRules.FILTER_INSTANCE);
   }
 
   /**

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java b/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java
new file mode 100644
index 0000000..1cb30ae
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java
@@ -0,0 +1,362 @@
+/*
+ * 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.rel.rules;
+
+import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.plan.RelOptRule;
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.rel.core.Filter;
+import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rex.RexBuilder;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexLiteral;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.rex.RexVisitorImpl;
+import org.apache.calcite.sql.SqlBinaryOperator;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.tools.RelBuilder;
+import org.apache.calcite.tools.RelBuilderFactory;
+import org.apache.calcite.util.Bug;
+
+import com.google.common.annotations.VisibleForTesting;
+import com.google.common.base.Predicate;
+import com.google.common.collect.BoundType;
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.ImmutableRangeSet;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Range;
+import com.google.common.collect.RangeSet;
+import com.google.common.collect.TreeRangeSet;
+
+import java.math.BigDecimal;
+import java.util.ArrayList;
+import java.util.Calendar;
+import java.util.EnumSet;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * Collection of planner rules that convert
+ * {@code EXTRACT(timeUnit FROM dateTime) = constant} to
+ * {@code dateTime BETWEEN lower AND upper}.
+ *
+ * <p>The rules allow conversion of queries on time dimension tables, such as
+ *
+ * <blockquote>SELECT ... FROM sales JOIN time_by_day USING (time_id)
+ * WHERE time_by_day.the_year = 1997
+ * AND time_by_day.the_month IN (4, 5, 6)</blockquote>
+ *
+ * <p>into
+ *
+ * <blockquote>SELECT ... FROM sales JOIN time_by_day USING (time_id)
+ * WHERE the_date BETWEEN DATE '2016-04-01' AND DATE '2016-06-30'</blockquote>
+ *
+ * <p>and is especially useful for Druid, which has a single timestamp column.
+ */
+public abstract class DateRangeRules {
+
+  private DateRangeRules() {}
+
+  private static final Predicate<Filter> FILTER_PREDICATE =
+      new Predicate<Filter>() {
+        @Override public boolean apply(Filter filter) {
+          final ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get();
+          assert finder.timeUnits.isEmpty() : "previous user did not clean up";
+          try {
+            filter.getCondition().accept(finder);
+            return !finder.timeUnits.isEmpty();
+          } finally {
+            finder.timeUnits.clear();
+          }
+        }
+      };
+
+  public static final RelOptRule FILTER_INSTANCE =
+      new FilterDateRangeRule(RelFactories.LOGICAL_BUILDER);
+
+  private static final Map<TimeUnitRange, Integer> TIME_UNIT_CODES =
+      ImmutableMap.<TimeUnitRange, Integer>builder()
+          .put(TimeUnitRange.YEAR, Calendar.YEAR)
+          .put(TimeUnitRange.MONTH, Calendar.MONTH)
+          .put(TimeUnitRange.DAY, Calendar.DAY_OF_MONTH)
+          .put(TimeUnitRange.HOUR, Calendar.HOUR)
+          .put(TimeUnitRange.MINUTE, Calendar.MINUTE)
+          .put(TimeUnitRange.SECOND, Calendar.SECOND)
+          .put(TimeUnitRange.MILLISECOND, Calendar.MILLISECOND)
+          .build();
+
+  private static final Map<TimeUnitRange, TimeUnitRange> TIME_UNIT_PARENTS =
+      ImmutableMap.<TimeUnitRange, TimeUnitRange>builder()
+          .put(TimeUnitRange.MONTH, TimeUnitRange.YEAR)
+          .put(TimeUnitRange.DAY, TimeUnitRange.MONTH)
+          .put(TimeUnitRange.HOUR, TimeUnitRange.DAY)
+          .put(TimeUnitRange.MINUTE, TimeUnitRange.HOUR)
+          .put(TimeUnitRange.SECOND, TimeUnitRange.MINUTE)
+          .put(TimeUnitRange.MILLISECOND, TimeUnitRange.SECOND)
+          .put(TimeUnitRange.MICROSECOND, TimeUnitRange.SECOND)
+          .build();
+
+  /** Returns whether an expression contains one or more calls to the
+   * {@code EXTRACT} function. */
+  @VisibleForTesting
+  static Set<TimeUnitRange> extractTimeUnits(RexNode e) {
+    final ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get();
+    try {
+      assert finder.timeUnits.isEmpty() : "previous user did not clean up";
+      e.accept(finder);
+      return ImmutableSet.copyOf(finder.timeUnits);
+    } finally {
+      finder.timeUnits.clear();
+    }
+  }
+
+  /** Rule that converts EXTRACT in a Filter condition into a date range. */
+  @SuppressWarnings("WeakerAccess")
+  public static class FilterDateRangeRule extends RelOptRule {
+    public FilterDateRangeRule(RelBuilderFactory relBuilderFactory) {
+      super(operand(Filter.class, null, FILTER_PREDICATE, any()),
+          relBuilderFactory, "FilterDateRangeRule");
+    }
+
+    @Override public void onMatch(RelOptRuleCall call) {
+      final Filter filter = call.rel(0);
+      final RexBuilder rexBuilder = filter.getCluster().getRexBuilder();
+      RexNode condition = filter.getCondition();
+      final Map<String, RangeSet<Calendar>> operandRanges = new HashMap<>();
+      for (TimeUnitRange timeUnit : extractTimeUnits(condition)) {
+        condition = condition.accept(
+            new ExtractShuttle(rexBuilder, timeUnit, operandRanges));
+      }
+      if (RexUtil.eq(condition, filter.getCondition())) {
+        return;
+      }
+      final RelBuilder relBuilder =
+          relBuilderFactory.create(filter.getCluster(), null);
+      relBuilder.push(filter.getInput())
+          .filter(RexUtil.simplify(rexBuilder, condition, true));
+      call.transformTo(relBuilder.build());
+    }
+  }
+
+  /** Visitor that searches for calls to the {@code EXTRACT} function, building
+   * a list of distinct time units. */
+  private static class ExtractFinder extends RexVisitorImpl {
+    private final Set<TimeUnitRange> timeUnits =
+        EnumSet.noneOf(TimeUnitRange.class);
+
+    private static final ThreadLocal<ExtractFinder> THREAD_INSTANCES =
+        new ThreadLocal<ExtractFinder>() {
+          @Override protected ExtractFinder initialValue() {
+            return new ExtractFinder();
+          }
+        };
+
+    private ExtractFinder() {
+      super(true);
+    }
+
+    @Override public Object visitCall(RexCall call) {
+      switch (call.getKind()) {
+      case EXTRACT:
+        final RexLiteral operand = (RexLiteral) call.getOperands().get(0);
+        timeUnits.add((TimeUnitRange) operand.getValue());
+      }
+      return super.visitCall(call);
+    }
+  }
+
+  /** Walks over an expression, replacing {@code EXTRACT} with date ranges. */
+  @VisibleForTesting
+  static class ExtractShuttle extends RexShuttle {
+    private final RexBuilder rexBuilder;
+    private final TimeUnitRange timeUnit;
+    private final Map<String, RangeSet<Calendar>> operandRanges;
+
+    @VisibleForTesting
+    ExtractShuttle(RexBuilder rexBuilder, TimeUnitRange timeUnit,
+        Map<String, RangeSet<Calendar>> operandRanges) {
+      this.rexBuilder = rexBuilder;
+      this.timeUnit = timeUnit;
+      Bug.upgrade("Change type to Map<RexNode, RangeSet<Calendar>> when"
+          + " [CALCITE-1367] is fixed");
+      this.operandRanges = operandRanges;
+    }
+
+    @Override public RexNode visitCall(RexCall call) {
+      switch (call.getKind()) {
+      case EQUALS:
+      case GREATER_THAN_OR_EQUAL:
+      case LESS_THAN_OR_EQUAL:
+      case GREATER_THAN:
+      case LESS_THAN:
+        final RexNode op0 = call.operands.get(0);
+        final RexNode op1 = call.operands.get(1);
+        switch (op0.getKind()) {
+        case LITERAL:
+          if (isExtractCall(op1)) {
+            return foo(call.getKind().reverse(),
+                ((RexCall) op1).getOperands().get(1), (RexLiteral) op0);
+          }
+        }
+        switch (op1.getKind()) {
+        case LITERAL:
+          if (isExtractCall(op0)) {
+            return foo(call.getKind(), ((RexCall) op0).getOperands().get(1),
+                (RexLiteral) op1);
+          }
+        }
+      }
+      return super.visitCall(call);
+    }
+
+    boolean isExtractCall(RexNode e) {
+      switch (e.getKind()) {
+      case EXTRACT:
+        final RexCall call = (RexCall) e;
+        final RexLiteral flag = (RexLiteral) call.operands.get(0);
+        final TimeUnitRange timeUnit = (TimeUnitRange) flag.getValue();
+        return timeUnit == this.timeUnit;
+      default:
+        return false;
+      }
+    }
+
+    RexNode foo(SqlKind comparison, RexNode operand, RexLiteral literal) {
+      RangeSet<Calendar> rangeSet = operandRanges.get(operand.toString());
+      if (rangeSet == null) {
+        rangeSet = ImmutableRangeSet.<Calendar>of().complement();
+      }
+      final RangeSet<Calendar> s2 = TreeRangeSet.create();
+      // Calendar.MONTH is 0-based
+      final int v = ((BigDecimal) literal.getValue()).intValue()
+          - (timeUnit == TimeUnitRange.MONTH ? 1 : 0);
+      for (Range<Calendar> r : rangeSet.asRanges()) {
+        final Calendar c;
+        switch (timeUnit) {
+        case YEAR:
+          c = Calendar.getInstance();
+          c.clear();
+          c.set(v, Calendar.JANUARY, 1);
+          s2.add(baz(timeUnit, comparison, c));
+          break;
+        case MONTH:
+        case DAY:
+        case HOUR:
+        case MINUTE:
+        case SECOND:
+          if (r.hasLowerBound()) {
+            c = (Calendar) r.lowerEndpoint().clone();
+            int i = 0;
+            while (next(c, timeUnit, v, r, i++ > 0)) {
+              s2.add(baz(timeUnit, comparison, c));
+            }
+          }
+        }
+      }
+      // Intersect old range set with new.
+      s2.removeAll(rangeSet.complement());
+      operandRanges.put(operand.toString(), ImmutableRangeSet.copyOf(s2));
+      final List<RexNode> nodes = new ArrayList<>();
+      for (Range<Calendar> r : s2.asRanges()) {
+        nodes.add(toRex(operand, r));
+      }
+      return RexUtil.composeDisjunction(rexBuilder, nodes, false);
+    }
+
+    private boolean next(Calendar c, TimeUnitRange timeUnit, int v,
+        Range<Calendar> r, boolean strict) {
+      final Calendar original = (Calendar) c.clone();
+      final int code = TIME_UNIT_CODES.get(timeUnit);
+      for (;;) {
+        c.set(code, v);
+        int v2 = c.get(code);
+        if (v2 < v) {
+          // E.g. when we set DAY=30 on 2014-02-01, we get 2014-02-30 because
+          // February has 28 days.
+          continue;
+        }
+        if (strict && original.compareTo(c) == 0) {
+          c.add(TIME_UNIT_CODES.get(TIME_UNIT_PARENTS.get(timeUnit)), 1);
+          continue;
+        }
+        if (!r.contains(c)) {
+          return false;
+        }
+        return true;
+      }
+    }
+
+    private RexNode toRex(RexNode operand, Range<Calendar> r) {
+      final List<RexNode> nodes = new ArrayList<>();
+      if (r.hasLowerBound()) {
+        final SqlBinaryOperator op = r.lowerBoundType() == BoundType.CLOSED
+            ? SqlStdOperatorTable.GREATER_THAN_OR_EQUAL
+            : SqlStdOperatorTable.GREATER_THAN;
+        nodes.add(
+            rexBuilder.makeCall(op, operand,
+                rexBuilder.makeDateLiteral(r.lowerEndpoint())));
+      }
+      if (r.hasUpperBound()) {
+        final SqlBinaryOperator op = r.upperBoundType() == BoundType.CLOSED
+            ? SqlStdOperatorTable.LESS_THAN_OR_EQUAL
+            : SqlStdOperatorTable.LESS_THAN;
+        nodes.add(
+            rexBuilder.makeCall(op, operand,
+                rexBuilder.makeDateLiteral(r.upperEndpoint())));
+      }
+      return RexUtil.composeConjunction(rexBuilder, nodes, false);
+    }
+
+    private Range<Calendar> baz(TimeUnitRange timeUnit, SqlKind comparison,
+        Calendar c) {
+      switch (comparison) {
+      case EQUALS:
+        return Range.closedOpen(round(c, timeUnit, true),
+            round(c, timeUnit, false));
+      case LESS_THAN:
+        return Range.lessThan(round(c, timeUnit, true));
+      case LESS_THAN_OR_EQUAL:
+        return Range.lessThan(round(c, timeUnit, false));
+      case GREATER_THAN:
+        return Range.atLeast(round(c, timeUnit, false));
+      case GREATER_THAN_OR_EQUAL:
+        return Range.atLeast(round(c, timeUnit, true));
+      default:
+        throw new AssertionError(comparison);
+      }
+    }
+
+    /** Returns a copy of a calendar, optionally rounded up to the next time
+     * unit. */
+    private Calendar round(Calendar c, TimeUnitRange timeUnit, boolean down) {
+      c = (Calendar) c.clone();
+      if (!down) {
+        final Integer code = TIME_UNIT_CODES.get(timeUnit);
+        final int v = c.get(code);
+        c.set(code, v + 1);
+      }
+      return c;
+    }
+  }
+}
+
+// End DateRangeRules.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 358f398..5c180b0 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -583,6 +583,11 @@ public enum SqlKind {
   RTRIM,
 
   /**
+   * The "EXTRACT" function.
+   */
+  EXTRACT,
+
+  /**
    * Call to a function using JDBC function syntax.
    */
   JDBC_FN,
@@ -878,7 +883,7 @@ public enum SqlKind {
               EnumSet.of(AS, ARGUMENT_ASSIGNMENT, DEFAULT,
                   DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
                   SELECT, JOIN, OTHER_FUNCTION, CAST, TRIM, FLOOR, CEIL,
-                  TIMESTAMP_ADD, TIMESTAMP_DIFF,
+                  TIMESTAMP_ADD, TIMESTAMP_DIFF, EXTRACT,
                   LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY,
                   NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE,
                   VALUES, WITH, WITH_ITEM),

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
index fd3bad6..eb31799 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
@@ -40,11 +40,7 @@ public class SqlExtractFunction extends SqlFunction {
   // SQL2003, Part 2, Section 4.4.3 - extract returns a exact numeric
   // TODO: Return type should be decimal for seconds
   public SqlExtractFunction() {
-    super(
-        "EXTRACT",
-        SqlKind.OTHER_FUNCTION,
-        ReturnTypes.BIGINT_NULLABLE,
-        null,
+    super("EXTRACT", SqlKind.EXTRACT, ReturnTypes.BIGINT_NULLABLE, null,
         OperandTypes.INTERVALINTERVAL_INTERVALDATETIME,
         SqlFunctionCategory.SYSTEM);
   }

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 00c69c7..e94748f 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1096,7 +1096,7 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
   /** Internal operator that extracts time periods (year, month, date) from a
    * date in internal format (number of days since epoch). */
   public static final SqlSpecialOperator EXTRACT_DATE =
-      new SqlSpecialOperator("EXTRACT_DATE", SqlKind.OTHER);
+      new SqlSpecialOperator("EXTRACT_DATE", SqlKind.EXTRACT);
 
   //-------------------------------------------------------------
   //                   FUNCTIONS

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java b/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java
new file mode 100644
index 0000000..6ef898e
--- /dev/null
+++ b/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java
@@ -0,0 +1,218 @@
+/*
+ * 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.rel.rules;
+
+import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.test.RexImplicationCheckerTest.Fixture;
+
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Ordering;
+import com.google.common.collect.RangeSet;
+
+import org.hamcrest.CoreMatchers;
+import org.hamcrest.Matcher;
+
+import org.junit.Test;
+
+import java.util.Calendar;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import static org.hamcrest.core.Is.is;
+import static org.junit.Assert.assertThat;
+
+/** Unit tests for {@link DateRangeRules} algorithms. */
+public class DateRangeRulesTest {
+
+  @Test public void testExtractYearFromDateColumn() {
+    final Fixture2 f = new Fixture2();
+
+    final RexNode e = f.eq(f.literal(2014), f.exYear);
+    assertThat(DateRangeRules.extractTimeUnits(e),
+        is(set(TimeUnitRange.YEAR)));
+    assertThat(DateRangeRules.extractTimeUnits(f.dec), is(set()));
+    assertThat(DateRangeRules.extractTimeUnits(f.literal(1)), is(set()));
+
+    // extract YEAR from a DATE column
+    checkDateRange(f, e, is("AND(>=($8, 2014-01-01), <($8, 2015-01-01))"));
+    checkDateRange(f, f.eq(f.exYear, f.literal(2014)),
+        is("AND(>=($8, 2014-01-01), <($8, 2015-01-01))"));
+    checkDateRange(f, f.ge(f.exYear, f.literal(2014)),
+        is(">=($8, 2014-01-01)"));
+    checkDateRange(f, f.gt(f.exYear, f.literal(2014)),
+        is(">=($8, 2015-01-01)"));
+    checkDateRange(f, f.lt(f.exYear, f.literal(2014)),
+        is("<($8, 2014-01-01)"));
+    checkDateRange(f, f.le(f.exYear, f.literal(2014)),
+        is("<($8, 2015-01-01)"));
+    checkDateRange(f, f.ne(f.exYear, f.literal(2014)),
+        is("<>(EXTRACT_DATE(FLAG(YEAR), $8), 2014)"));
+  }
+
+  @Test public void testExtractYearFromTimestampColumn() {
+    final Fixture2 f = new Fixture2();
+    checkDateRange(f, f.eq(f.exYearTs, f.literal(2014)),
+        is("AND(>=($9, 2014-01-01), <($9, 2015-01-01))"));
+    checkDateRange(f, f.ge(f.exYearTs, f.literal(2014)),
+        is(">=($9, 2014-01-01)"));
+    checkDateRange(f, f.gt(f.exYearTs, f.literal(2014)),
+        is(">=($9, 2015-01-01)"));
+    checkDateRange(f, f.lt(f.exYearTs, f.literal(2014)),
+        is("<($9, 2014-01-01)"));
+    checkDateRange(f, f.le(f.exYearTs, f.literal(2014)),
+        is("<($9, 2015-01-01)"));
+    checkDateRange(f, f.ne(f.exYearTs, f.literal(2014)),
+        is("<>(EXTRACT(FLAG(YEAR), $9), 2014)"));
+  }
+
+  @Test public void testExtractYearAndMonthFromDateColumn() {
+    final Fixture2 f = new Fixture2();
+    checkDateRange(f,
+        f.and(f.eq(f.exYear, f.literal(2014)), f.eq(f.exMonth, f.literal(6))),
+        is("AND(AND(>=($8, 2014-01-01), <($8, 2015-01-01)),"
+            + " AND(>=($8, 2014-06-01), <($8, 2014-07-01)))"),
+        is("AND(>=($8, 2014-01-01), <($8, 2015-01-01),"
+            + " >=($8, 2014-06-01), <($8, 2014-07-01))"));
+  }
+
+  @Test public void testExtractYearAndDayFromDateColumn() {
+    final Fixture2 f = new Fixture2();
+    checkDateRange(f,
+        f.and(f.eq(f.exYear, f.literal(2010)), f.eq(f.exDay, f.literal(31))),
+        is("AND(AND(>=($8, 2010-01-01), <($8, 2011-01-01)),"
+            + " OR(AND(>=($8, 2010-01-31), <($8, 2010-02-01)),"
+            + " AND(>=($8, 2010-03-31), <($8, 2010-04-01)),"
+            + " AND(>=($8, 2010-05-31), <($8, 2010-06-01)),"
+            + " AND(>=($8, 2010-07-31), <($8, 2010-08-01)),"
+            + " AND(>=($8, 2010-08-31), <($8, 2010-09-01)),"
+            + " AND(>=($8, 2010-10-31), <($8, 2010-11-01)),"
+            + " AND(>=($8, 2010-12-31), <($8, 2011-01-01))))"));
+
+  }
+
+  @Test public void testExtractYearMonthDayFromDateColumn() {
+    final Fixture2 f = new Fixture2();
+    // The following condition finds the 2 leap days between 2010 and 2020,
+    // namely 29th February 2012 and 2016.
+    //
+    // Currently there are redundant conditions, e.g.
+    // "AND(>=($8, 2011-01-01), <($8, 2020-01-01))". We should remove them by
+    // folding intervals.
+    checkDateRange(f,
+        f.and(f.gt(f.exYear, f.literal(2010)), f.lt(f.exYear, f.literal(2020)),
+            f.eq(f.exMonth, f.literal(2)), f.eq(f.exDay, f.literal(29))),
+        is("AND(>=($8, 2011-01-01),"
+            + " AND(>=($8, 2011-01-01), <($8, 2020-01-01)),"
+            + " OR(AND(>=($8, 2011-02-01), <($8, 2011-03-01)),"
+            + " AND(>=($8, 2012-02-01), <($8, 2012-03-01)),"
+            + " AND(>=($8, 2013-02-01), <($8, 2013-03-01)),"
+            + " AND(>=($8, 2014-02-01), <($8, 2014-03-01)),"
+            + " AND(>=($8, 2015-02-01), <($8, 2015-03-01)),"
+            + " AND(>=($8, 2016-02-01), <($8, 2016-03-01)),"
+            + " AND(>=($8, 2017-02-01), <($8, 2017-03-01)),"
+            + " AND(>=($8, 2018-02-01), <($8, 2018-03-01)),"
+            + " AND(>=($8, 2019-02-01), <($8, 2019-03-01))),"
+            + " OR(AND(>=($8, 2012-02-29), <($8, 2012-03-01)),"
+            + " AND(>=($8, 2016-02-29), <($8, 2016-03-01))))"));
+  }
+
+  @Test public void testExtractYearMonthDayFromTimestampColumn() {
+    final Fixture2 f = new Fixture2();
+    checkDateRange(f,
+        f.and(f.gt(f.exYearTs, f.literal(2010)),
+            f.lt(f.exYearTs, f.literal(2020)),
+            f.eq(f.exMonthTs, f.literal(2)), f.eq(f.exDayTs, f.literal(29))),
+        is("AND(>=($9, 2011-01-01),"
+            + " AND(>=($9, 2011-01-01), <($9, 2020-01-01)),"
+            + " OR(AND(>=($9, 2011-02-01), <($9, 2011-03-01)),"
+            + " AND(>=($9, 2012-02-01), <($9, 2012-03-01)),"
+            + " AND(>=($9, 2013-02-01), <($9, 2013-03-01)),"
+            + " AND(>=($9, 2014-02-01), <($9, 2014-03-01)),"
+            + " AND(>=($9, 2015-02-01), <($9, 2015-03-01)),"
+            + " AND(>=($9, 2016-02-01), <($9, 2016-03-01)),"
+            + " AND(>=($9, 2017-02-01), <($9, 2017-03-01)),"
+            + " AND(>=($9, 2018-02-01), <($9, 2018-03-01)),"
+            + " AND(>=($9, 2019-02-01), <($9, 2019-03-01))),"
+            + " OR(AND(>=($9, 2012-02-29), <($9, 2012-03-01)),"
+            + " AND(>=($9, 2016-02-29), <($9, 2016-03-01))))"));
+  }
+
+  private static Set<TimeUnitRange> set(TimeUnitRange... es) {
+    return ImmutableSet.copyOf(es);
+  }
+
+  private void checkDateRange(Fixture f, RexNode e, Matcher<String> matcher) {
+    checkDateRange(f, e, matcher, CoreMatchers.any(String.class));
+  }
+
+  private void checkDateRange(Fixture f, RexNode e, Matcher<String> matcher,
+      Matcher<String> simplifyMatcher) {
+    final Map<String, RangeSet<Calendar>> operandRanges = new HashMap<>();
+    // We rely on the collection being sorted (so YEAR comes before MONTH
+    // before HOUR) and unique. A predicate on MONTH is not useful if there is
+    // no predicate on YEAR. Then when we apply the predicate on DAY it doesn't
+    // generate hundreds of ranges we'll later throw away.
+    final List<TimeUnitRange> timeUnits =
+        Ordering.natural().sortedCopy(DateRangeRules.extractTimeUnits(e));
+    for (TimeUnitRange timeUnit : timeUnits) {
+      e = e.accept(
+          new DateRangeRules.ExtractShuttle(f.rexBuilder, timeUnit,
+              operandRanges));
+    }
+    assertThat(e.toString(), matcher);
+    final RexNode e2 = RexUtil.simplify(f.rexBuilder, e);
+    assertThat(e2.toString(), simplifyMatcher);
+  }
+
+  /** Common expressions across tests. */
+  private static class Fixture2 extends Fixture {
+    private final RexNode exYear;
+    private final RexNode exMonth;
+    private final RexNode exDay;
+    private final RexNode exYearTs;
+    private final RexNode exMonthTs;
+    private final RexNode exDayTs;
+
+    Fixture2() {
+      exYear = rexBuilder.makeCall(intRelDataType,
+          SqlStdOperatorTable.EXTRACT_DATE,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), dt));
+      exMonth = rexBuilder.makeCall(intRelDataType,
+          SqlStdOperatorTable.EXTRACT_DATE,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), dt));
+      exDay = rexBuilder.makeCall(intRelDataType,
+          SqlStdOperatorTable.EXTRACT_DATE,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), dt));
+      exYearTs = rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), ts));
+      exMonthTs = rexBuilder.makeCall(intRelDataType,
+          SqlStdOperatorTable.EXTRACT,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), ts));
+      exDayTs = rexBuilder.makeCall(intRelDataType,
+          SqlStdOperatorTable.EXTRACT,
+          ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), ts));
+    }
+  }
+}
+
+// End DateRangeRulesTest.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
----------------------------------------------------------------------
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 80a330c..2a3c02f 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
@@ -66,6 +66,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
   protected static final List<String> SALES_TABLES =
       Arrays.asList(
           "TABLE(CATALOG.SALES.EMP)",
+          "TABLE(CATALOG.SALES.EMP_B)",
           "TABLE(CATALOG.SALES.EMP_20)",
           "TABLE(CATALOG.SALES.EMP_ADDRESS)",
           "TABLE(CATALOG.SALES.DEPT)",

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
----------------------------------------------------------------------
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 988f5cc..7211e05 100644
--- a/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
+++ b/core/src/test/java/org/apache/calcite/test/CalciteSuite.java
@@ -30,6 +30,7 @@ import org.apache.calcite.plan.volcano.VolcanoPlannerTraitTest;
 import org.apache.calcite.prepare.LookupOperatorOverloadsTest;
 import org.apache.calcite.rel.RelCollationTest;
 import org.apache.calcite.rel.rel2sql.RelToSqlConverterTest;
+import org.apache.calcite.rel.rules.DateRangeRulesTest;
 import org.apache.calcite.rex.RexBuilderTest;
 import org.apache.calcite.rex.RexExecutorTest;
 import org.apache.calcite.runtime.BinarySearchTest;
@@ -114,6 +115,7 @@ import org.junit.runners.Suite;
     SqlValidatorTest.class,
     SqlAdvisorTest.class,
     RelMetadataTest.class,
+    DateRangeRulesTest.class,
     RelOptRulesTest.class,
     ScannableTableTest.class,
     RexExecutorTest.class,

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
index e56dd96..039a655 100644
--- a/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
+++ b/core/src/test/java/org/apache/calcite/test/MockCatalogReader.java
@@ -153,6 +153,8 @@ public class MockCatalogReader implements Prepare.CatalogReader {
         typeFactory.createSqlType(SqlTypeName.VARCHAR, 20);
     final RelDataType timestampType =
         typeFactory.createSqlType(SqlTypeName.TIMESTAMP);
+    final RelDataType dateType =
+        typeFactory.createSqlType(SqlTypeName.DATE);
     final RelDataType booleanType =
         typeFactory.createSqlType(SqlTypeName.BOOLEAN);
     final RelDataType rectilinearCoordType =
@@ -196,6 +198,21 @@ public class MockCatalogReader implements Prepare.CatalogReader {
     empTable.addColumn("SLACKER", booleanType);
     registerTable(empTable);
 
+    // Register "EMP_B" table. As "EMP", birth with a "BIRTHDATE" column.
+    final MockTable empBTable =
+        MockTable.create(this, salesSchema, "EMP_B", false, 14);
+    empBTable.addColumn("EMPNO", intType);
+    empBTable.addColumn("ENAME", varchar20Type);
+    empBTable.addColumn("JOB", varchar10Type);
+    empBTable.addColumn("MGR", intTypeNull);
+    empBTable.addColumn("HIREDATE", timestampType);
+    empBTable.addColumn("SAL", intType);
+    empBTable.addColumn("COMM", intType);
+    empBTable.addColumn("DEPTNO", intType);
+    empBTable.addColumn("SLACKER", booleanType);
+    empBTable.addColumn("BIRTHDATE", dateType);
+    registerTable(empBTable);
+
     // Register "DEPT" table.
     MockTable deptTable = MockTable.create(this, salesSchema, "DEPT", false, 4);
     deptTable.addColumn("DEPTNO", intType);
@@ -345,7 +362,6 @@ public class MockCatalogReader implements Prepare.CatalogReader {
                   builder.add(Pair.<String, Schema>of(empTable.names.get(1), null));
                   builder.add(Pair.<String, Schema>of(empTable.names.get(2), null));
                   return Schemas.path(builder.build());
-//                  return empTable.names;
                 }
 
                 @Override public ImmutableIntList getColumnMapping() {

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
----------------------------------------------------------------------
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 6ad6747..f672a03 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -46,6 +46,7 @@ import org.apache.calcite.rel.rules.AggregateUnionAggregateRule;
 import org.apache.calcite.rel.rules.AggregateUnionTransposeRule;
 import org.apache.calcite.rel.rules.CalcMergeRule;
 import org.apache.calcite.rel.rules.CoerceInputsRule;
+import org.apache.calcite.rel.rules.DateRangeRules;
 import org.apache.calcite.rel.rules.FilterAggregateTransposeRule;
 import org.apache.calcite.rel.rules.FilterJoinRule;
 import org.apache.calcite.rel.rules.FilterMergeRule;
@@ -2382,6 +2383,31 @@ public class RelOptRulesTest extends RelOptTestBase {
     checkSubQuery(sql).check();
   }
 
+  /** Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-434">[CALCITE-434]
+   * Converting predicates on date dimension columns into date ranges</a>,
+   * specifically a rule that converts {@code EXTRACT(YEAR FROM ...) = constant}
+   * to a range. */
+  @Test public void testExtractYearToRange() throws Exception {
+    final String sql = "select *\n"
+        + "from sales.emp_b as e\n"
+        + "where extract(year from birthdate) = 2014";
+    HepProgram program = new HepProgramBuilder()
+        .addRuleInstance(DateRangeRules.FILTER_INSTANCE)
+        .build();
+    sql(sql).with(program).check();
+  }
+
+  @Test public void testExtractYearMonthToRange() throws Exception {
+    final String sql = "select *\n"
+        + "from sales.emp_b as e\n"
+        + "where extract(year from birthdate) = 2014"
+        + "and extract(month from birthdate) = 4";
+    HepProgram program = new HepProgramBuilder()
+        .addRuleInstance(DateRangeRules.FILTER_INSTANCE)
+        .build();
+    sql(sql).with(program).check();
+  }
 }
 
 // End RelOptRulesTest.java

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
index c12aa30..637ddef 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java
@@ -190,6 +190,10 @@ abstract class RelOptTestBase extends SqlToRelTestBase {
       return new Sql(sql, hepPlanner, expand);
     }
 
+    public Sql with(HepProgram program) {
+      return new Sql(sql, new HepPlanner(program), expand);
+    }
+
     public Sql expand(boolean expand) {
       return new Sql(sql, hepPlanner, expand);
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java b/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java
index fd06d5b..a54167d 100644
--- a/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java
@@ -269,37 +269,39 @@ public class RexImplicationCheckerTest {
    * <p>We put the data in here, rather than as fields in the test case, so that
    * the data can be garbage-collected as soon as the test has executed.
    */
-  private static class Fixture {
-    private final RexBuilder rexBuilder;
-    private final RexNode bl;
-    private final RexNode i;
-    private final RexNode dec;
-    private final RexNode lg;
-    private final RexNode sh;
-    private final RexNode by;
-    private final RexNode fl;
-    private final RexNode dt;
-    private final RexNode ch;
-    private final RexNode ts;
-    private final RexNode t;
-    private final RexNode str;
-
-    private final RelDataType boolRelDataType;
-    private final RelDataType intRelDataType;
-    private final RelDataType decRelDataType;
-    private final RelDataType longRelDataType;
-    private final RelDataType shortDataType;
-    private final RelDataType byteDataType;
-    private final RelDataType floatDataType;
-    private final RelDataType charDataType;
-    private final RelDataType dateDataType;
-    private final RelDataType timeStampDataType;
-    private final RelDataType timeDataType;
-    private final RelDataType stringDataType;
-    private final RelDataTypeFactory typeFactory;
-    private final RexImplicationChecker checker;
-    private final RelDataType rowType;
-    private final RexExecutorImpl executor;
+  @SuppressWarnings("WeakerAccess")
+  public static class Fixture {
+    public final RelDataTypeFactory typeFactory;
+    public final RexBuilder rexBuilder;
+    public final RelDataType boolRelDataType;
+    public final RelDataType intRelDataType;
+    public final RelDataType decRelDataType;
+    public final RelDataType longRelDataType;
+    public final RelDataType shortDataType;
+    public final RelDataType byteDataType;
+    public final RelDataType floatDataType;
+    public final RelDataType charDataType;
+    public final RelDataType dateDataType;
+    public final RelDataType timeStampDataType;
+    public final RelDataType timeDataType;
+    public final RelDataType stringDataType;
+
+    public final RexNode bl;
+    public final RexNode i;
+    public final RexNode dec;
+    public final RexNode lg;
+    public final RexNode sh;
+    public final RexNode by;
+    public final RexNode fl;
+    public final RexNode dt;
+    public final RexNode ch;
+    public final RexNode ts;
+    public final RexNode t;
+    public final RexNode str;
+
+    public final RexImplicationChecker checker;
+    public final RelDataType rowType;
+    public final RexExecutorImpl executor;
 
     public Fixture() {
       typeFactory = new JavaTypeFactoryImpl(RelDataTypeSystem.DEFAULT);
@@ -363,76 +365,76 @@ public class RexImplicationCheckerTest {
       checker = new RexImplicationChecker(rexBuilder, executor, rowType);
     }
 
-    RexInputRef ref(int i, RelDataType type) {
+    public RexInputRef ref(int i, RelDataType type) {
       return new RexInputRef(i,
           typeFactory.createTypeWithNullability(type, true));
     }
 
-    RexLiteral literal(int i) {
+    public RexLiteral literal(int i) {
       return rexBuilder.makeExactLiteral(new BigDecimal(i));
     }
 
-    RexNode gt(RexNode node1, RexNode node2) {
+    public RexNode gt(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.GREATER_THAN, node1, node2);
     }
 
-    RexNode ge(RexNode node1, RexNode node2) {
+    public RexNode ge(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(
           SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, node1, node2);
     }
 
-    RexNode eq(RexNode node1, RexNode node2) {
+    public RexNode eq(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, node1, node2);
     }
 
-    RexNode ne(RexNode node1, RexNode node2) {
+    public RexNode ne(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.NOT_EQUALS, node1, node2);
     }
 
-    RexNode lt(RexNode node1, RexNode node2) {
+    public RexNode lt(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, node1, node2);
     }
 
-    RexNode le(RexNode node1, RexNode node2) {
+    public RexNode le(RexNode node1, RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN_OR_EQUAL, node1,
           node2);
     }
 
-    RexNode notNull(RexNode node1) {
+    public RexNode notNull(RexNode node1) {
       return rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, node1);
     }
 
-    RexNode isNull(RexNode node2) {
+    public RexNode isNull(RexNode node2) {
       return rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, node2);
     }
 
-    RexNode and(RexNode node1, RexNode node2) {
-      return rexBuilder.makeCall(SqlStdOperatorTable.AND, node1, node2);
+    public RexNode and(RexNode... nodes) {
+      return rexBuilder.makeCall(SqlStdOperatorTable.AND, nodes);
     }
 
-    RexNode longLiteral(long value) {
+    public RexNode longLiteral(long value) {
       return rexBuilder.makeLiteral(value, longRelDataType, true);
     }
 
-    RexNode shortLiteral(short value) {
+    public RexNode shortLiteral(short value) {
       return rexBuilder.makeLiteral(value, shortDataType, true);
     }
 
-    RexLiteral floatLiteral(double value) {
+    public RexLiteral floatLiteral(double value) {
       return rexBuilder.makeApproxLiteral(new BigDecimal(value));
     }
 
-    RexLiteral charLiteral(String z) {
+    public RexLiteral charLiteral(String z) {
       return rexBuilder.makeCharLiteral(
           new NlsString(z, null, SqlCollation.COERCIBLE));
     }
 
-    RexNode timestampLiteral(Calendar calendar) {
+    public RexNode timestampLiteral(Calendar calendar) {
       return rexBuilder.makeTimestampLiteral(
           calendar, timeStampDataType.getPrecision());
     }
 
-    RexNode timeLiteral(Calendar calendar) {
+    public RexNode timeLiteral(Calendar calendar) {
       return rexBuilder.makeTimestampLiteral(
           calendar, timeDataType.getPrecision());
     }

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
----------------------------------------------------------------------
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 79d7784..ba974f6 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -1125,6 +1125,48 @@ LogicalProject(EMPNO=[$0])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testExtractYearMonthToRange">
+        <Resource name="sql">
+            <![CDATA[select *
+from sales.emp_b as e
+where extract(year from birthdate) = 2014and extract(month from birthdate) = 4]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], BIRTHDATE=[$9])
+  LogicalFilter(condition=[AND(=(EXTRACT_DATE(FLAG(YEAR), Reinterpret($9)), 2014), =(EXTRACT_DATE(FLAG(MONTH), Reinterpret($9)), 4))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], BIRTHDATE=[$9])
+  LogicalFilter(condition=[AND(>=(Reinterpret($9), 2014-01-01), <(Reinterpret($9), 2015-01-01), >=(Reinterpret($9), 2014-04-01), <(Reinterpret($9), 2014-05-01))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testExtractYearToRange">
+        <Resource name="sql">
+            <![CDATA[select *
+from sales.emp_b as e
+where extract(year from birthdate) = 2014]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], BIRTHDATE=[$9])
+  LogicalFilter(condition=[=(EXTRACT_DATE(FLAG(YEAR), Reinterpret($9)), 2014)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], BIRTHDATE=[$9])
+  LogicalFilter(condition=[AND(>=(Reinterpret($9), 2014-01-01), <(Reinterpret($9), 2015-01-01))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testNestedAggregates">
         <Resource name="sql">
             <![CDATA[SELECT

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidConnectionImpl.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidConnectionImpl.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidConnectionImpl.java
index fb61b6b..77bb817 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidConnectionImpl.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidConnectionImpl.java
@@ -17,12 +17,12 @@
 package org.apache.calcite.adapter.druid;
 
 import org.apache.calcite.avatica.AvaticaUtils;
+import org.apache.calcite.avatica.ColumnMetaData;
 import org.apache.calcite.interpreter.Row;
 import org.apache.calcite.interpreter.Sink;
 import org.apache.calcite.linq4j.AbstractEnumerable;
 import org.apache.calcite.linq4j.Enumerable;
 import org.apache.calcite.linq4j.Enumerator;
-import org.apache.calcite.linq4j.tree.Primitive;
 import org.apache.calcite.prepare.CalcitePrepareImpl;
 import org.apache.calcite.sql.type.SqlTypeName;
 import org.apache.calcite.util.Holder;
@@ -42,11 +42,15 @@ import com.google.common.collect.ImmutableSet;
 import java.io.ByteArrayInputStream;
 import java.io.IOException;
 import java.io.InputStream;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
 import java.util.Collections;
+import java.util.Date;
 import java.util.List;
 import java.util.Map;
 import java.util.NoSuchElementException;
 import java.util.Set;
+import java.util.TimeZone;
 import java.util.concurrent.ArrayBlockingQueue;
 import java.util.concurrent.BlockingQueue;
 import java.util.concurrent.ExecutorService;
@@ -61,6 +65,14 @@ class DruidConnectionImpl implements DruidConnection {
   private final String url;
   private final String coordinatorUrl;
 
+  private static final SimpleDateFormat UTC_TIMESTAMP_FORMAT;
+
+  static {
+    final TimeZone utc = TimeZone.getTimeZone("UTC");
+    UTC_TIMESTAMP_FORMAT = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
+    UTC_TIMESTAMP_FORMAT.setTimeZone(utc);
+  }
+
   private static final Set<String> SUPPORTED_TYPES =
       ImmutableSet.of("LONG", "DOUBLE", "STRING", "hyperUnique");
 
@@ -80,7 +92,7 @@ class DruidConnectionImpl implements DruidConnection {
    * @throws IOException on error
    */
   public void request(QueryType queryType, String data, Sink sink,
-      List<String> fieldNames, List<Primitive> fieldTypes, Page page)
+      List<String> fieldNames, List<ColumnMetaData.Rep> fieldTypes, Page page)
       throws IOException {
     final String url = this.url + "/druid/v2/?pretty";
     final Map<String, String> requestHeaders =
@@ -97,7 +109,7 @@ class DruidConnectionImpl implements DruidConnection {
   /** Parses the output of a {@code topN} query, sending the results to a
    * {@link Sink}. */
   private void parse(QueryType queryType, InputStream in, Sink sink,
-      List<String> fieldNames, List<Primitive> fieldTypes, Page page) {
+      List<String> fieldNames, List<ColumnMetaData.Rep> fieldTypes, Page page) {
     final JsonFactory factory = new JsonFactory();
     final Row.RowBuilder rowBuilder = Row.newBuilder(fieldNames.size());
 
@@ -195,15 +207,17 @@ class DruidConnectionImpl implements DruidConnection {
     }
   }
 
-  private void parseFields(List<String> fieldNames, List<Primitive> fieldTypes,
-      Row.RowBuilder rowBuilder, JsonParser parser) throws IOException {
+  private void parseFields(List<String> fieldNames,
+      List<ColumnMetaData.Rep> fieldTypes, Row.RowBuilder rowBuilder,
+      JsonParser parser) throws IOException {
     while (parser.nextToken() == JsonToken.FIELD_NAME) {
       parseField(fieldNames, fieldTypes, rowBuilder, parser);
     }
   }
 
-  private void parseField(List<String> fieldNames, List<Primitive> fieldTypes,
-      Row.RowBuilder rowBuilder, JsonParser parser) throws IOException {
+  private void parseField(List<String> fieldNames,
+      List<ColumnMetaData.Rep> fieldTypes, Row.RowBuilder rowBuilder,
+      JsonParser parser) throws IOException {
     final String fieldName = parser.getCurrentName();
 
     // Move to next token, which is name's value
@@ -212,25 +226,25 @@ class DruidConnectionImpl implements DruidConnection {
     if (i < 0) {
       return;
     }
+    ColumnMetaData.Rep type = fieldTypes.get(i);
     switch (token) {
     case VALUE_NUMBER_INT:
+      if (type == null) {
+        type = ColumnMetaData.Rep.INTEGER;
+      }
+      // fall through
     case VALUE_NUMBER_FLOAT:
-      Primitive type = fieldTypes.get(i);
       if (type == null) {
-        if (token == JsonToken.VALUE_NUMBER_INT) {
-          type = Primitive.INT;
-        } else {
-          type = Primitive.FLOAT;
-        }
+        type = ColumnMetaData.Rep.FLOAT;
       }
       switch (type) {
       case BYTE:
-        rowBuilder.set(i, parser.getIntValue());
+        rowBuilder.set(i, parser.getByteValue());
         break;
       case SHORT:
         rowBuilder.set(i, parser.getShortValue());
         break;
-      case INT:
+      case INTEGER:
         rowBuilder.set(i, parser.getIntValue());
         break;
       case LONG:
@@ -252,8 +266,19 @@ class DruidConnectionImpl implements DruidConnection {
       break;
     case VALUE_NULL:
       break;
+    case VALUE_STRING:
     default:
-      rowBuilder.set(i, parser.getText());
+      if (type == ColumnMetaData.Rep.JAVA_SQL_TIMESTAMP) {
+        try {
+          final Date parse = UTC_TIMESTAMP_FORMAT.parse(parser.getText());
+          rowBuilder.set(i, parse.getTime());
+        } catch (ParseException e) {
+          // ignore bad value
+        }
+      } else {
+        rowBuilder.set(i, parser.getText());
+      }
+      break;
     }
   }
 
@@ -329,7 +354,7 @@ class DruidConnectionImpl implements DruidConnection {
           public void run() {
             try {
               final Page page = new Page();
-              final List<Primitive> fieldTypes =
+              final List<ColumnMetaData.Rep> fieldTypes =
                   Collections.nCopies(fieldNames.size(), null);
               request(queryType, request, this, fieldNames, fieldTypes, page);
               enumerator.done.set(true);

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
index 2b862ef..e6fc682 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidQuery.java
@@ -17,6 +17,7 @@
 package org.apache.calcite.adapter.druid;
 
 import org.apache.calcite.DataContext;
+import org.apache.calcite.avatica.ColumnMetaData;
 import org.apache.calcite.config.CalciteConnectionProperty;
 import org.apache.calcite.interpreter.BindableRel;
 import org.apache.calcite.interpreter.Bindables;
@@ -24,7 +25,6 @@ import org.apache.calcite.interpreter.Interpreter;
 import org.apache.calcite.interpreter.Node;
 import org.apache.calcite.interpreter.Sink;
 import org.apache.calcite.linq4j.Enumerable;
-import org.apache.calcite.linq4j.tree.Primitive;
 import org.apache.calcite.plan.RelOptCluster;
 import org.apache.calcite.plan.RelOptCost;
 import org.apache.calcite.plan.RelOptPlanner;
@@ -681,7 +681,7 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
     }
 
     public void run() throws InterruptedException {
-      final List<Primitive> fieldTypes = new ArrayList<>();
+      final List<ColumnMetaData.Rep> fieldTypes = new ArrayList<>();
       for (RelDataTypeField field : query.getRowType().getFieldList()) {
         fieldTypes.add(getPrimitive(field));
       }
@@ -703,21 +703,24 @@ public class DruidQuery extends AbstractRelNode implements BindableRel {
       }
     }
 
-    private Primitive getPrimitive(RelDataTypeField field) {
+    private ColumnMetaData.Rep getPrimitive(RelDataTypeField field) {
+      if (field.getName().equals(query.druidTable.timestampFieldName)) {
+        return ColumnMetaData.Rep.JAVA_SQL_TIMESTAMP;
+      }
       switch (field.getType().getSqlTypeName()) {
       case BIGINT:
-        return Primitive.LONG;
+        return ColumnMetaData.Rep.LONG;
       case INTEGER:
-        return Primitive.INT;
+        return ColumnMetaData.Rep.INTEGER;
       case SMALLINT:
-        return Primitive.SHORT;
+        return ColumnMetaData.Rep.SHORT;
       case TINYINT:
-        return Primitive.BYTE;
+        return ColumnMetaData.Rep.BYTE;
       case REAL:
-        return Primitive.FLOAT;
+        return ColumnMetaData.Rep.FLOAT;
       case DOUBLE:
       case FLOAT:
-        return Primitive.DOUBLE;
+        return ColumnMetaData.Rep.DOUBLE;
       default:
         return null;
       }

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
----------------------------------------------------------------------
diff --git a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
index 2453fcb..3127ddd 100644
--- a/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
+++ b/druid/src/main/java/org/apache/calcite/adapter/druid/DruidTableFactory.java
@@ -88,7 +88,7 @@ public class DruidTableFactory implements TableFactory {
       }
     }
     if (timestampColumnName != null) {
-      fieldBuilder.put(timestampColumnName, SqlTypeName.VARCHAR);
+      fieldBuilder.put(timestampColumnName, SqlTypeName.TIMESTAMP);
     }
     final String dataSourceName = Util.first(dataSource, name);
     DruidConnectionImpl c;

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
----------------------------------------------------------------------
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
index 2b2d1ae..adbdca6 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
@@ -524,8 +524,8 @@ public class DruidAdapterIT {
         + "order by c desc limit 2";
     final String explain = "PLAN="
         + "EnumerableInterpreter\n"
-        + "  BindableSort(sort0=[$0], dir0=[DESC], fetch=[2])\n"
-        + "    BindableProject(C=[$2], state_province=[$1], city=[$0])\n"
+        + "  BindableProject(C=[$2], state_province=[$1], city=[$0])\n"
+        + "    BindableSort(sort0=[$2], dir0=[DESC], fetch=[2])\n"
         + "      DruidQuery(table=[[foodmart, foodmart]], groups=[{28, 29}], aggs=[[COUNT()]])";
     sql(sql)
         .returnsOrdered("C=7394; state_province=WA; city=Spokane",
@@ -664,6 +664,26 @@ public class DruidAdapterIT {
             "state_province=WA; city=Yakima; product_name=High Top Dried Mushrooms");
   }
 
+  /** Tests that conditions applied to time units extracted via the EXTRACT
+   * function become ranges on the timestamp column {@code the_date}.
+   *
+   * <p>Test case for
+   * <a href="https://issues.apache.org/jira/browse/CALCITE-1334">[CALCITE-1334]
+   * Convert predicates on EXTRACT function calls into date ranges</a>. */
+  @Test public void testFilterTimestamp() {
+    String sql = "select count(*) as c\n"
+        + "from \"foodmart\"\n"
+        + "where extract(year from \"timestamp\") = 1997\n"
+        + "and extract(month from \"timestamp\") in (4, 6)\n";
+    final String explain = "EnumerableInterpreter\n"
+        + "  BindableAggregate(group=[{}], C=[COUNT()])\n"
+        + "    BindableFilter(condition=[AND(>=(/INT(Reinterpret($91), 86400000), 1997-01-01), <(/INT(Reinterpret($91), 86400000), 1998-01-01), >=(/INT(Reinterpret($91), 86400000), 1997-04-01), <(/INT(Reinterpret($91), 86400000), 1997-05-01))])\n"
+        + "      DruidQuery(table=[[foodmart, foodmart]])";
+    sql(sql)
+        .explainContains(explain)
+        .returnsUnordered("C=6588");
+  }
+
   /** Tests a query that exposed several bugs in the interpreter. */
   @Test public void testWhereGroupBy() {
     String sql = "select \"wikiticker\".\"countryName\" as \"c0\",\n"

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/druid/src/test/resources/druid-foodmart-model.json
----------------------------------------------------------------------
diff --git a/druid/src/test/resources/druid-foodmart-model.json b/druid/src/test/resources/druid-foodmart-model.json
index 8e0e14e..f81e2c4 100644
--- a/druid/src/test/resources/druid-foodmart-model.json
+++ b/druid/src/test/resources/druid-foodmart-model.json
@@ -32,7 +32,7 @@
           "factory": "org.apache.calcite.adapter.druid.DruidTableFactory",
           "operand": {
             "interval": "1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z",
-            "timestampColumn": "the_date",
+            "timestampColumn": "timestamp",
             "dimensions": [
               "product_id",
               "brand_name",

http://git-wip-us.apache.org/repos/asf/calcite/blob/87435a9b/pom.xml
----------------------------------------------------------------------
diff --git a/pom.xml b/pom.xml
index f1f06ce..ca3fd1e 100644
--- a/pom.xml
+++ b/pom.xml
@@ -70,7 +70,7 @@ limitations under the License.
     <git-commit-id-plugin.version>2.1.9</git-commit-id-plugin.version>
 
     <!-- We support (and test against) Guava versions between
-         12.0.1 (HDFS, HBase) and 19.0 (most recent).
+         14.0.1 (Hive) and 19.0 (most recent).
          Override with your preferred version and it should work. -->
     <guava.version>18.0</guava.version>
     <h2.version>1.4.185</h2.version>


Mime
View raw message