Repository: calcite Updated Branches: refs/heads/master 1c7f690d3 -> fa19580ec
[CALCITE-2090] Convert FLOOR and CEIL into time ranges (Nishant Bangarwa) Add test for multiple operands. Allow RexLiteral.getValueAs(Calendar) on TIMESTAMP_WITH_LOCAL_TIME_ZONE literals. Close apache/calcite#603 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/fa19580e Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/fa19580e Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/fa19580e Branch: refs/heads/master Commit: fa19580ecfe404b75fcb6c9c174cfe9da73d2db8 Parents: 1c7f690 Author: Nishant <[email protected]> Authored: Thu Jan 11 02:24:26 2018 +0530 Committer: Julian Hyde <[email protected]> Committed: Thu Jan 11 19:53:24 2018 -0800 ---------------------------------------------------------------------- .../calcite/rel/rules/DateRangeRules.java | 269 ++++++++++-- .../java/org/apache/calcite/rex/RexLiteral.java | 3 + .../calcite/rel/rules/DateRangeRulesTest.java | 407 ++++++++++++++++++- .../org/apache/calcite/test/DruidAdapterIT.java | 3 +- 4 files changed, 636 insertions(+), 46 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/fa19580e/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 index d670305..c8d17bf 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/DateRangeRules.java @@ -64,7 +64,9 @@ import java.util.Set; /** * Collection of planner rules that convert - * {@code EXTRACT(timeUnit FROM dateTime) = constant} to + * {@code EXTRACT(timeUnit FROM dateTime) = constant}, + * {@code FLOOR(dateTime to timeUnit} = constant} and + * {@code CEIL(dateTime to timeUnit} = constant} to * {@code dateTime BETWEEN lower AND upper}. * * <p>The rules allow conversion of queries on time dimension tables, such as @@ -87,13 +89,14 @@ public abstract class DateRangeRules { private static final Predicate<Filter> FILTER_PREDICATE = new PredicateImpl<Filter>() { @Override public boolean test(Filter filter) { - final ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get(); - assert finder.timeUnits.isEmpty() : "previous user did not clean up"; - try { + try (ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get()) { + assert finder.timeUnits.isEmpty() && finder.opKinds.isEmpty() + : "previous user did not clean up"; filter.getCondition().accept(finder); - return !finder.timeUnits.isEmpty(); - } finally { - finder.timeUnits.clear(); + // bail out if there is no EXTRACT of YEAR, or call to FLOOR or CEIL + return finder.timeUnits.contains(TimeUnitRange.YEAR) + || finder.opKinds.contains(SqlKind.FLOOR) + || finder.opKinds.contains(SqlKind.CEIL); } } }; @@ -132,23 +135,24 @@ public abstract class DateRangeRules { * 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. */ static ImmutableSortedSet<TimeUnitRange> extractTimeUnits(RexNode e) { - final ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get(); - try { - assert finder.timeUnits.isEmpty() : "previous user did not clean up"; + try (ExtractFinder finder = ExtractFinder.THREAD_INSTANCES.get()) { + assert finder.timeUnits.isEmpty() && finder.opKinds.isEmpty() + : "previous user did not clean up"; e.accept(finder); return ImmutableSortedSet.copyOf(finder.timeUnits); - } finally { - finder.timeUnits.clear(); } } - /** Replaces calls to EXTRACT in an expression. */ + /** Replaces calls to EXTRACT, FLOOR and CEIL in an expression. */ @VisibleForTesting public static RexNode replaceTimeUnits(RexBuilder rexBuilder, RexNode e) { - final ImmutableSortedSet<TimeUnitRange> timeUnits = extractTimeUnits(e); + ImmutableSortedSet<TimeUnitRange> timeUnits = extractTimeUnits(e); if (!timeUnits.contains(TimeUnitRange.YEAR)) { - // bail out if there is no year extract. - return e; + // Case when we have FLOOR or CEIL but no extract on YEAR. + // Add YEAR as TimeUnit so that FLOOR gets replaced in first iteration + // with timeUnit YEAR. + timeUnits = ImmutableSortedSet.<TimeUnitRange>naturalOrder() + .addAll(timeUnits).add(TimeUnitRange.YEAR).build(); } final Map<String, RangeSet<Calendar>> operandRanges = new HashMap<>(); for (TimeUnitRange timeUnit : timeUnits) { @@ -158,7 +162,8 @@ public abstract class DateRangeRules { return e; } - /** Rule that converts EXTRACT in a Filter condition into a date range. */ + /** Rule that converts EXTRACT, FLOOR and CEIL in a {@link Filter} into a date + * range. */ @SuppressWarnings("WeakerAccess") public static class FilterDateRangeRule extends RelOptRule { public FilterDateRangeRule(RelBuilderFactory relBuilderFactory) { @@ -182,11 +187,13 @@ public abstract class DateRangeRules { } } - /** Visitor that searches for calls to the {@code EXTRACT} function, building - * a list of distinct time units. */ - private static class ExtractFinder extends RexVisitorImpl { + /** Visitor that searches for calls to {@code EXTRACT}, {@code FLOOR} or + * {@code CEIL}, building a list of distinct time units. */ + private static class ExtractFinder extends RexVisitorImpl + implements AutoCloseable { private final Set<TimeUnitRange> timeUnits = EnumSet.noneOf(TimeUnitRange.class); + private final Set<SqlKind> opKinds = EnumSet.noneOf(SqlKind.class); private static final ThreadLocal<ExtractFinder> THREAD_INSTANCES = new ThreadLocal<ExtractFinder>() { @@ -204,12 +211,26 @@ public abstract class DateRangeRules { case EXTRACT: final RexLiteral operand = (RexLiteral) call.getOperands().get(0); timeUnits.add((TimeUnitRange) operand.getValue()); + break; + case FLOOR: + case CEIL: + // Check that the call to FLOOR/CEIL is on date-time + if (call.getOperands().size() == 2) { + opKinds.add(call.getKind()); + } + break; } return super.visitCall(call); } + + public void close() { + timeUnits.clear(); + opKinds.clear(); + } } - /** Walks over an expression, replacing {@code EXTRACT} with date ranges. */ + /** Walks over an expression, replacing calls to + * {@code EXTRACT}, {@code FLOOR} and {@code CEIL} with date ranges. */ @VisibleForTesting static class ExtractShuttle extends RexShuttle { private final RexBuilder rexBuilder; @@ -241,16 +262,45 @@ public abstract class DateRangeRules { final RexNode op1 = call.operands.get(1); switch (op0.getKind()) { case LITERAL: - if (isExtractCall(op1) && canRewriteExtract()) { - return compareExtract(call.getKind().reverse(), - ((RexCall) op1).getOperands().get(1), (RexLiteral) op0); + assert op0 instanceof RexLiteral; + if (isExtractCall(op1)) { + assert op1 instanceof RexCall; + final RexCall subCall = (RexCall) op1; + RexNode operand = subCall.getOperands().get(1); + if (canRewriteExtract(operand)) { + return compareExtract(call.getKind().reverse(), operand, + (RexLiteral) op0); + } + } + if (isFloorCeilCall(op1)) { + assert op1 instanceof RexCall; + final RexCall subCall = (RexCall) op1; + final RexLiteral flag = (RexLiteral) subCall.operands.get(1); + final TimeUnitRange timeUnit = (TimeUnitRange) flag.getValue(); + return compareFloorCeil(call.getKind().reverse(), + subCall.getOperands().get(0), (RexLiteral) op0, + timeUnit, op1.getKind() == SqlKind.FLOOR); } } switch (op1.getKind()) { case LITERAL: - if (isExtractCall(op0) && canRewriteExtract()) { - return compareExtract(call.getKind(), - ((RexCall) op0).getOperands().get(1), (RexLiteral) op1); + assert op1 instanceof RexLiteral; + if (isExtractCall(op0)) { + assert op0 instanceof RexCall; + final RexCall subCall = (RexCall) op0; + RexNode operand = subCall.operands.get(1); + if (canRewriteExtract(operand)) { + return compareExtract(call.getKind(), + subCall.operands.get(1), (RexLiteral) op1); + } + } + if (isFloorCeilCall(op0)) { + final RexCall subCall = (RexCall) op0; + final RexLiteral flag = (RexLiteral) subCall.operands.get(1); + final TimeUnitRange timeUnit = (TimeUnitRange) flag.getValue(); + return compareFloorCeil(call.getKind(), + subCall.getOperands().get(0), (RexLiteral) op1, + timeUnit, op0.getKind() == SqlKind.FLOOR); } } // fall through @@ -264,7 +314,7 @@ public abstract class DateRangeRules { } } - private boolean canRewriteExtract() { + private boolean canRewriteExtract(RexNode operand) { // We rely on timeUnits being sorted (so YEAR comes before MONTH // before HOUR) and unique. If we have seen a predicate on YEAR, // operandRanges will not be empty. This checks whether we can rewrite @@ -274,7 +324,21 @@ public abstract class DateRangeRules { // OR extract(YEAR from time) = someValue // // we cannot rewrite extract on MONTH. - return timeUnit == TimeUnitRange.YEAR || !operandRanges.isEmpty(); + if (timeUnit == TimeUnitRange.YEAR) { + return true; + } + final RangeSet<Calendar> calendarRangeSet = + operandRanges.get(operand.toString()); + if (calendarRangeSet == null || calendarRangeSet.isEmpty()) { + return false; + } + for (Range<Calendar> range : calendarRangeSet.asRanges()) { + // Cannot reWrite if range does not have an upper or lower bound + if (!range.hasUpperBound() || !range.hasLowerBound()) { + return false; + } + } + return true; } @Override protected List<RexNode> visitList(List<? extends RexNode> exprs, @@ -339,6 +403,12 @@ public abstract class DateRangeRules { // Calendar.MONTH is 0-based final int v = ((BigDecimal) literal.getValue()).intValue() - (timeUnit == TimeUnitRange.MONTH ? 1 : 0); + + if (!isValid(v, timeUnit)) { + // Comparison with an invalid value for timeUnit, always false. + return rexBuilder.makeLiteral(false); + } + for (Range<Calendar> r : rangeSet.asRanges()) { final Calendar c; switch (timeUnit) { @@ -346,18 +416,18 @@ public abstract class DateRangeRules { c = Util.calendar(); c.clear(); c.set(v, Calendar.JANUARY, 1); - s2.add(range(timeUnit, comparison, c)); + s2.add(extractRange(timeUnit, comparison, c)); break; case MONTH: case DAY: case HOUR: case MINUTE: case SECOND: - if (r.hasLowerBound()) { + if (r.hasLowerBound() && r.hasUpperBound()) { c = (Calendar) r.lowerEndpoint().clone(); int i = 0; while (next(c, timeUnit, v, r, i++ > 0)) { - s2.add(range(timeUnit, comparison, c)); + s2.add(extractRange(timeUnit, comparison, c)); } } } @@ -372,6 +442,7 @@ public abstract class DateRangeRules { return RexUtil.composeDisjunction(rexBuilder, nodes); } + // Assumes v is a valid value for given timeunit private boolean next(Calendar c, TimeUnitRange timeUnit, int v, Range<Calendar> r, boolean strict) { final Calendar original = (Calendar) c.clone(); @@ -395,6 +466,24 @@ public abstract class DateRangeRules { } } + private static boolean isValid(int v, TimeUnitRange timeUnit) { + switch (timeUnit) { + case YEAR: + return v > 0; + case MONTH: + return v >= Calendar.JANUARY && v <= Calendar.DECEMBER; + case DAY: + return v > 0 && v <= 31; + case HOUR: + return v >= 0 && v <= 24; + case MINUTE: + case SECOND: + return v >= 0 && v <= 60; + default: + return false; + } + } + private RexNode toRex(RexNode operand, Range<Calendar> r) { final List<RexNode> nodes = new ArrayList<>(); if (r.hasLowerBound()) { @@ -431,7 +520,7 @@ public abstract class DateRangeRules { } } - private Range<Calendar> range(TimeUnitRange timeUnit, SqlKind comparison, + private Range<Calendar> extractRange(TimeUnitRange timeUnit, SqlKind comparison, Calendar c) { switch (comparison) { case EQUALS: @@ -461,6 +550,120 @@ public abstract class DateRangeRules { } return c; } + + private RexNode compareFloorCeil(SqlKind comparison, RexNode operand, + RexLiteral timeLiteral, TimeUnitRange timeUnit, boolean floor) { + RangeSet<Calendar> rangeSet = operandRanges.get(operand.toString()); + if (rangeSet == null) { + rangeSet = ImmutableRangeSet.<Calendar>of().complement(); + } + final RangeSet<Calendar> s2 = TreeRangeSet.create(); + final Calendar c = timeLiteral.getValueAs(Calendar.class); + final Range<Calendar> range = floor + ? floorRange(timeUnit, comparison, c) + : ceilRange(timeUnit, comparison, c); + s2.add(range); + // Intersect old range set with new. + s2.removeAll(rangeSet.complement()); + operandRanges.put(operand.toString(), ImmutableRangeSet.copyOf(s2)); + if (range.isEmpty()) { + return rexBuilder.makeLiteral(false); + } + return toRex(operand, range); + } + + private Range<Calendar> floorRange(TimeUnitRange timeUnit, SqlKind comparison, + Calendar c) { + Calendar floor = floor(c, timeUnit); + boolean boundary = floor.equals(c); + switch (comparison) { + case EQUALS: + return Range.closedOpen(floor, boundary ? increment(floor, timeUnit) : floor); + case LESS_THAN: + return boundary ? Range.lessThan(floor) : Range.lessThan(increment(floor, timeUnit)); + case LESS_THAN_OR_EQUAL: + return Range.lessThan(increment(floor, timeUnit)); + case GREATER_THAN: + return Range.atLeast(increment(floor, timeUnit)); + case GREATER_THAN_OR_EQUAL: + return boundary ? Range.atLeast(floor) : Range.atLeast(increment(floor, timeUnit)); + default: + throw Util.unexpected(comparison); + } + } + + private Range<Calendar> ceilRange(TimeUnitRange timeUnit, SqlKind comparison, + Calendar c) { + final Calendar ceil = ceil(c, timeUnit); + boolean boundary = ceil.equals(c); + switch (comparison) { + case EQUALS: + return Range.openClosed(boundary ? decrement(ceil, timeUnit) : ceil, ceil); + case LESS_THAN: + return Range.atMost(decrement(ceil, timeUnit)); + case LESS_THAN_OR_EQUAL: + return boundary ? Range.atMost(ceil) : Range.atMost(decrement(ceil, timeUnit)); + case GREATER_THAN: + return boundary ? Range.greaterThan(ceil) : Range.greaterThan(decrement(ceil, timeUnit)); + case GREATER_THAN_OR_EQUAL: + return Range.greaterThan(decrement(ceil, timeUnit)); + default: + throw Util.unexpected(comparison); + } + } + + boolean isFloorCeilCall(RexNode e) { + switch (e.getKind()) { + case FLOOR: + case CEIL: + final RexCall call = (RexCall) e; + return call.getOperands().size() == 2; + default: + return false; + } + } + + private Calendar increment(Calendar c, TimeUnitRange timeUnit) { + c = (Calendar) c.clone(); + c.add(TIME_UNIT_CODES.get(timeUnit), 1); + return c; + } + + private Calendar decrement(Calendar c, TimeUnitRange timeUnit) { + c = (Calendar) c.clone(); + c.add(TIME_UNIT_CODES.get(timeUnit), -1); + return c; + } + + private Calendar ceil(Calendar c, TimeUnitRange timeUnit) { + Calendar floor = floor(c, timeUnit); + return floor.equals(c) ? floor : increment(floor, timeUnit); + } + + /** + * Commputes floor of given calendar object to provided timeunit + * @return returns a copy of calendar, floored to the given timeunit + */ + private Calendar floor(Calendar c, TimeUnitRange timeUnit) { + c = (Calendar) c.clone(); + switch (timeUnit) { + case YEAR: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.MONTH), Calendar.JANUARY); + // Ignore checkstyle failure for Fall through from previous branch of the switch statement. + // CHECKSTYLE: IGNORE 10 + case MONTH: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.DAY), 1); + case DAY: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.HOUR), 0); + case HOUR: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.MINUTE), 0); + case MINUTE: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.SECOND), 0); + case SECOND: + c.set(TIME_UNIT_CODES.get(TimeUnitRange.MILLISECOND), 0); + } + return c; + } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/fa19580e/core/src/main/java/org/apache/calcite/rex/RexLiteral.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexLiteral.java b/core/src/main/java/org/apache/calcite/rex/RexLiteral.java index a5ed918..b5900bb 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexLiteral.java +++ b/core/src/main/java/org/apache/calcite/rex/RexLiteral.java @@ -868,6 +868,9 @@ public class RexLiteral extends RexNode { if (clazz == Long.class) { // Milliseconds since 1970-01-01 00:00:00 return clazz.cast(((TimestampString) value).getMillisSinceEpoch()); + } else if (clazz == Calendar.class) { + // Note: Nanos are ignored + return clazz.cast(((TimestampString) value).toCalendar()); } break; case INTERVAL_YEAR: http://git-wip-us.apache.org/repos/asf/calcite/blob/fa19580e/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 index f247fbb..f0439ab 100644 --- a/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java +++ b/core/src/test/java/org/apache/calcite/rel/rules/DateRangeRulesTest.java @@ -20,19 +20,17 @@ import org.apache.calcite.avatica.util.TimeUnitRange; import org.apache.calcite.rex.RexNode; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.test.RexImplicationCheckerTest.Fixture; +import org.apache.calcite.util.TimestampString; +import org.apache.calcite.util.Util; import com.google.common.collect.ImmutableList; import com.google.common.collect.ImmutableSet; -import com.google.common.collect.ImmutableSortedSet; -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.Map; import java.util.Set; import static org.hamcrest.core.Is.is; @@ -301,6 +299,364 @@ public class DateRangeRulesTest { + " AND(>=($8, 2001-01-01), <($8, 2001-02-01)))))")); } + @Test public void testExtractRewriteForInvalidMonthComparison() { + // "EXTRACT(MONTH FROM ts) = 14" will never be TRUE + final Fixture2 f = new Fixture2(); + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(14))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " false)")); + + // "EXTRACT(MONTH FROM ts) = 0" will never be TRUE + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(0))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " false)")); + + // "EXTRACT(MONTH FROM ts) = 13" will never be TRUE + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(13))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " false)")); + + // "EXTRACT(MONTH FROM ts) = 12" might be TRUE + // Careful with boundaries, because Calendar.DECEMBER = 11 + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(12))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-12-01 00:00:00), <($9, 2011-01-01 00:00:00)))")); + + // "EXTRACT(MONTH FROM ts) = 1" can happen + // Careful with boundaries, because Calendar.JANUARY = 0 + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(1))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-01-01 00:00:00), <($9, 2010-02-01 00:00:00)))")); + } + + @Test public void testExtractRewriteForInvalidDayComparison() { + final Fixture2 f = new Fixture2(); + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(11)), + f.eq(f.exDayTs, f.literal(32))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-11-01 00:00:00), <($9, 2010-12-01 00:00:00)), false)")); + // Feb 31 is an invalid date + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(2)), + f.eq(f.exDayTs, f.literal(31))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-02-01 00:00:00), <($9, 2010-03-01 00:00:00)), false)")); + } + + @Test public void testUnboundYearExtractRewrite() { + final Fixture2 f = new Fixture2(); + // No lower bound on YEAR + checkDateRange(f, + f.and(f.le(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(11)), + f.eq(f.exDayTs, f.literal(2))), + is("AND(<($9, 2011-01-01 00:00:00), =(EXTRACT(FLAG(MONTH), $9), 11)," + + " =(EXTRACT(FLAG(DAY), $9), 2))")); + + // No upper bound on YEAR + checkDateRange(f, + f.and(f.ge(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(11)), + f.eq(f.exDayTs, f.literal(2))), + // Since the year does not have a upper bound, MONTH and DAY cannot be replaced + is("AND(>=($9, 2010-01-01 00:00:00), =(EXTRACT(FLAG(MONTH), $9), 11)," + + " =(EXTRACT(FLAG(DAY), $9), 2))")); + + // No lower/upper bound on YEAR for individual rexNodes. + checkDateRange(f, + f.and(f.le(f.exYearTs, f.literal(2010)), + f.ge(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(5))), + is("AND(<($9, 2011-01-01 00:00:00), AND(>=($9, 2010-01-01 00:00:00)," + + " <($9, 2011-01-01 00:00:00)), AND(>=($9, 2010-05-01 00:00:00)," + + " <($9, 2010-06-01 00:00:00)))")); + } + + // Test reWrite with multiple operands + @Test public void testExtractRewriteMultipleOperands() { + final Fixture2 f = new Fixture2(); + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(10)), + f.eq(f.exMonthD, f.literal(5))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-10-01 00:00:00), <($9, 2010-11-01 00:00:00))," + + " =(EXTRACT(FLAG(MONTH), $8), 5))")); + + checkDateRange(f, + f.and(f.eq(f.exYearTs, f.literal(2010)), + f.eq(f.exMonthTs, f.literal(10)), + f.eq(f.exYearD, f.literal(2011)), + f.eq(f.exMonthD, f.literal(5))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-10-01 00:00:00), <($9, 2010-11-01 00:00:00))," + + " AND(>=($8, 2011-01-01), <($8, 2012-01-01)), AND(>=($8, 2011-05-01)," + + " <($8, 2011-06-01)))")); + } + + @Test public void testFloorEqRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + // Always False + checkDateRange(f, f.eq(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("false")); + checkDateRange(f, f.eq(f.timestampLiteral(TimestampString.fromCalendarFields(c)), f.floorYear), + is("false")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.floorMonth, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-02-01 00:00:00), <($9, 2010-03-01 00:00:00))")); + + c.set(2010, Calendar.DECEMBER, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.floorMonth, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-12-01 00:00:00), <($9, 2011-01-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 0, 0, 0); + checkDateRange(f, f.eq(f.floorDay, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-02-04 00:00:00), <($9, 2010-02-05 00:00:00))")); + + c.set(2010, Calendar.DECEMBER, 31, 0, 0, 0); + checkDateRange(f, f.eq(f.floorDay, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-12-31 00:00:00), <($9, 2011-01-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 4, 0, 0); + checkDateRange(f, f.eq(f.floorHour, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-02-04 04:00:00), <($9, 2010-02-04 05:00:00))")); + + c.set(2010, Calendar.DECEMBER, 31, 23, 0, 0); + checkDateRange(f, f.eq(f.floorHour, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-12-31 23:00:00), <($9, 2011-01-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 2, 32, 0); + checkDateRange(f, + f.eq(f.floorMinute, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-02-04 02:32:00), <($9, 2010-02-04 02:33:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 2, 59, 0); + checkDateRange(f, + f.eq(f.floorMinute, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>=($9, 2010-02-04 02:59:00), <($9, 2010-02-04 03:00:00))")); + } + + @Test public void testFloorLtRewrite() { + final Calendar c = Util.calendar(); + + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.lt(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<($9, 2011-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.lt(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<($9, 2010-01-01 00:00:00)")); + } + + @Test public void testFloorLeRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.le(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<($9, 2011-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.le(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<($9, 2011-01-01 00:00:00)")); + } + + @Test public void testFloorGtRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.gt(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">=($9, 2011-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.gt(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">=($9, 2011-01-01 00:00:00)")); + } + + @Test public void testFloorGeRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.ge(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">=($9, 2011-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.ge(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">=($9, 2010-01-01 00:00:00)")); + } + + @Test public void testFloorExtractBothRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + Fixture2 f = new Fixture2(); + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, + f.and(f.eq(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + f.eq(f.exMonthTs, f.literal(5))), + is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00))," + + " AND(>=($9, 2010-05-01 00:00:00), <($9, 2010-06-01 00:00:00)))")); + + // No lower range for floor + checkDateRange(f, + f.and(f.le(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + f.eq(f.exMonthTs, f.literal(5))), + is("AND(<($9, 2011-01-01 00:00:00), =(EXTRACT(FLAG(MONTH), $9), 5))")); + + // No lower range for floor + checkDateRange(f, + f.and(f.gt(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + f.eq(f.exMonthTs, f.literal(5))), + is("AND(>=($9, 2011-01-01 00:00:00), =(EXTRACT(FLAG(MONTH), $9), 5))")); + + // No upper range for individual floor rexNodes, but combined results in bounded interval + checkDateRange(f, + f.and(f.le(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + f.eq(f.exMonthTs, f.literal(5)), + f.ge(f.floorYear, f.timestampLiteral(TimestampString.fromCalendarFields(c)))), + is("AND(<($9, 2011-01-01 00:00:00), AND(>=($9, 2010-05-01 00:00:00)," + + " <($9, 2010-06-01 00:00:00)), >=($9, 2010-01-01 00:00:00))")); + + } + + @Test public void testCeilEqRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + // Always False + checkDateRange(f, f.eq(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("false")); + checkDateRange(f, f.eq(f.timestampLiteral(TimestampString.fromCalendarFields(c)), f.ceilYear), + is("false")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2009-01-01 00:00:00), <=($9, 2010-01-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.ceilMonth, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-01-01 00:00:00), <=($9, 2010-02-01 00:00:00))")); + + c.set(2010, Calendar.DECEMBER, 1, 0, 0, 0); + checkDateRange(f, f.eq(f.ceilMonth, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-11-01 00:00:00), <=($9, 2010-12-01 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 0, 0, 0); + checkDateRange(f, f.eq(f.ceilDay, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-02-03 00:00:00), <=($9, 2010-02-04 00:00:00))")); + + c.set(2010, Calendar.DECEMBER, 31, 0, 0, 0); + checkDateRange(f, f.eq(f.ceilDay, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-12-30 00:00:00), <=($9, 2010-12-31 00:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 4, 0, 0); + checkDateRange(f, f.eq(f.ceilHour, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-02-04 03:00:00), <=($9, 2010-02-04 04:00:00))")); + + c.set(2010, Calendar.DECEMBER, 31, 23, 0, 0); + checkDateRange(f, f.eq(f.ceilHour, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-12-31 22:00:00), <=($9, 2010-12-31 23:00:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 2, 32, 0); + checkDateRange(f, + f.eq(f.ceilMinute, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-02-04 02:31:00), <=($9, 2010-02-04 02:32:00))")); + + c.set(2010, Calendar.FEBRUARY, 4, 2, 59, 0); + checkDateRange(f, + f.eq(f.ceilMinute, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("AND(>($9, 2010-02-04 02:58:00), <=($9, 2010-02-04 02:59:00))")); + } + + @Test public void testCeilLtRewrite() { + final Calendar c = Util.calendar(); + + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.lt(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<=($9, 2010-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.lt(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<=($9, 2009-01-01 00:00:00)")); + } + + @Test public void testCeilLeRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.le(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<=($9, 2010-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.le(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is("<=($9, 2010-01-01 00:00:00)")); + } + + @Test public void testCeilGtRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.gt(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">($9, 2010-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.gt(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">($9, 2010-01-01 00:00:00)")); + } + + @Test public void testCeilGeRewrite() { + final Calendar c = Util.calendar(); + c.clear(); + c.set(2010, Calendar.FEBRUARY, 10, 11, 12, 05); + final Fixture2 f = new Fixture2(); + checkDateRange(f, f.ge(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">($9, 2010-01-01 00:00:00)")); + + c.clear(); + c.set(2010, Calendar.JANUARY, 1, 0, 0, 0); + checkDateRange(f, f.ge(f.ceilYear, f.timestampLiteral(TimestampString.fromCalendarFields(c))), + is(">($9, 2009-01-01 00:00:00)")); + } + private static Set<TimeUnitRange> set(TimeUnitRange... es) { return ImmutableSet.copyOf(es); } @@ -311,14 +667,7 @@ public class DateRangeRulesTest { private void checkDateRange(Fixture f, RexNode e, Matcher<String> matcher, Matcher<String> simplifyMatcher) { - final Map<String, RangeSet<Calendar>> operandRanges = new HashMap<>(); - final ImmutableSortedSet<TimeUnitRange> timeUnits = - DateRangeRules.extractTimeUnits(e); - for (TimeUnitRange timeUnit : timeUnits) { - e = e.accept( - new DateRangeRules.ExtractShuttle(f.rexBuilder, timeUnit, - operandRanges, timeUnits)); - } + e = DateRangeRules.replaceTimeUnits(f.rexBuilder, e); assertThat(e.toString(), matcher); final RexNode e2 = f.simplify.simplify(e); assertThat(e2.toString(), simplifyMatcher); @@ -333,6 +682,18 @@ public class DateRangeRulesTest { private final RexNode exMonthD; // EXTRACT MONTH from DATE field private final RexNode exDayD; // EXTRACT DAY from DATE field + private final RexNode floorYear; + private final RexNode floorMonth; + private final RexNode floorDay; + private final RexNode floorHour; + private final RexNode floorMinute; + + private final RexNode ceilYear; + private final RexNode ceilMonth; + private final RexNode ceilDay; + private final RexNode ceilHour; + private final RexNode ceilMinute; + Fixture2() { exYearTs = rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), ts)); @@ -350,6 +711,28 @@ public class DateRangeRulesTest { exDayD = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.EXTRACT, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), d)); + + floorYear = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.FLOOR, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.YEAR))); + floorMonth = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.FLOOR, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.MONTH))); + floorDay = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.FLOOR, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.DAY))); + floorHour = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.FLOOR, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.HOUR))); + floorMinute = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.FLOOR, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.MINUTE))); + + ceilYear = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.CEIL, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.YEAR))); + ceilMonth = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.CEIL, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.MONTH))); + ceilDay = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.CEIL, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.DAY))); + ceilHour = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.CEIL, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.HOUR))); + ceilMinute = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.CEIL, + ImmutableList.<RexNode>of(ts, rexBuilder.makeFlag(TimeUnitRange.MINUTE))); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/fa19580e/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 e5931b1..3e7f7bd 100644 --- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java +++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java @@ -1434,13 +1434,14 @@ public class DruidAdapterIT { + "from \"wiki\"\n" + "where ceil(\"time\" to DAY) >= '1997-01-01 00:00:00 UTC'\n" + "and ceil(\"time\" to DAY) < '1997-09-01 00:00:00 UTC'\n" + + "and \"time\" + INTERVAL '1' DAY > '1997-01-01'\n" + "group by \"countryName\", ceil(CAST(\"time\" AS TIMESTAMP) TO DAY)\n" + "order by c limit 5"; String plan = "BindableProject(countryName=[$0], EXPR$1=[$1], C=[CAST($2):INTEGER NOT NULL])\n" + " BindableSort(sort0=[$2], dir0=[ASC], fetch=[5])\n" + " BindableAggregate(group=[{0, 1}], agg#0=[COUNT()])\n" + " BindableProject(countryName=[$1], EXPR$1=[CEIL(CAST($0):TIMESTAMP(0) NOT NULL, FLAG(DAY))])\n" - + " BindableFilter(condition=[AND(>=(CEIL($0, FLAG(DAY)), 1997-01-01 00:00:00), <(CEIL($0, FLAG(DAY)), 1997-09-01 00:00:00))])\n" + + " BindableFilter(condition=[AND(>($0, 1996-12-31 00:00:00), <=($0, 1997-08-31 00:00:00), >(+($0, 86400000), CAST('1997-01-01'):TIMESTAMP_WITH_LOCAL_TIME_ZONE(0) NOT NULL))])\n" + " DruidQuery(table=[[wiki, wiki]], intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]], projects=[[$0, $5]])"; // NOTE: Druid query only has countryName as the dimension // being queried after project is pushed to druid query.
