Repository: calcite Updated Branches: refs/heads/master d26330f2c -> 2918b8fe5
[CALCITE-2122] In DateRangeRules, make either TIMESTAMP or DATE literal, according to target type (Nishant Bangarwa) Close apache/calcite#599 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/2918b8fe Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/2918b8fe Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/2918b8fe Branch: refs/heads/master Commit: 2918b8fe55c58e0bafd38accfface242f1847c54 Parents: d26330f Author: Nishant <[email protected]> Authored: Mon Jan 8 00:22:51 2018 +0530 Committer: Julian Hyde <[email protected]> Committed: Mon Jan 8 13:34:48 2018 -0800 ---------------------------------------------------------------------- .../calcite/rel/rules/DateRangeRules.java | 37 ++- .../calcite/rel/rules/DateRangeRulesTest.java | 318 ++++++++++--------- .../calcite/sql/test/SqlOperatorBaseTest.java | 8 + .../calcite/test/RexImplicationCheckerTest.java | 48 +-- core/src/test/resources/sql/misc.iq | 16 +- .../org/apache/calcite/test/DruidAdapterIT.java | 29 +- .../calcite/test/DruidDateRangeRulesTest.java | 6 +- 7 files changed, 260 insertions(+), 202 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/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 9288ce4..d670305 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 @@ -36,6 +36,7 @@ import org.apache.calcite.tools.RelBuilder; import org.apache.calcite.tools.RelBuilderFactory; import org.apache.calcite.util.Bug; import org.apache.calcite.util.DateString; +import org.apache.calcite.util.TimestampString; import org.apache.calcite.util.Util; import com.google.common.annotations.VisibleForTesting; @@ -241,15 +242,15 @@ public abstract class DateRangeRules { switch (op0.getKind()) { case LITERAL: if (isExtractCall(op1) && canRewriteExtract()) { - return foo(call.getKind().reverse(), + return compareExtract(call.getKind().reverse(), ((RexCall) op1).getOperands().get(1), (RexLiteral) op0); } } switch (op1.getKind()) { case LITERAL: if (isExtractCall(op0) && canRewriteExtract()) { - return foo(call.getKind(), ((RexCall) op0).getOperands().get(1), - (RexLiteral) op1); + return compareExtract(call.getKind(), + ((RexCall) op0).getOperands().get(1), (RexLiteral) op1); } } // fall through @@ -328,7 +329,8 @@ public abstract class DateRangeRules { } } - RexNode foo(SqlKind comparison, RexNode operand, RexLiteral literal) { + RexNode compareExtract(SqlKind comparison, RexNode operand, + RexLiteral literal) { RangeSet<Calendar> rangeSet = operandRanges.get(operand.toString()); if (rangeSet == null) { rangeSet = ImmutableRangeSet.<Calendar>of().complement(); @@ -344,7 +346,7 @@ public abstract class DateRangeRules { c = Util.calendar(); c.clear(); c.set(v, Calendar.JANUARY, 1); - s2.add(baz(timeUnit, comparison, c)); + s2.add(range(timeUnit, comparison, c)); break; case MONTH: case DAY: @@ -355,7 +357,7 @@ public abstract class DateRangeRules { c = (Calendar) r.lowerEndpoint().clone(); int i = 0; while (next(c, timeUnit, v, r, i++ > 0)) { - s2.add(baz(timeUnit, comparison, c)); + s2.add(range(timeUnit, comparison, c)); } } } @@ -401,8 +403,7 @@ public abstract class DateRangeRules { : SqlStdOperatorTable.GREATER_THAN; nodes.add( rexBuilder.makeCall(op, operand, - rexBuilder.makeDateLiteral( - DateString.fromCalendarFields(r.lowerEndpoint())))); + dateTimeLiteral(rexBuilder, r.lowerEndpoint(), operand))); } if (r.hasUpperBound()) { final SqlBinaryOperator op = r.upperBoundType() == BoundType.CLOSED @@ -410,13 +411,27 @@ public abstract class DateRangeRules { : SqlStdOperatorTable.LESS_THAN; nodes.add( rexBuilder.makeCall(op, operand, - rexBuilder.makeDateLiteral( - DateString.fromCalendarFields(r.upperEndpoint())))); + dateTimeLiteral(rexBuilder, r.upperEndpoint(), operand))); } return RexUtil.composeConjunction(rexBuilder, nodes, false); } - private Range<Calendar> baz(TimeUnitRange timeUnit, SqlKind comparison, + private RexLiteral dateTimeLiteral(RexBuilder rexBuilder, Calendar calendar, + RexNode operand) { + switch (operand.getType().getSqlTypeName()) { + case TIMESTAMP: + case TIMESTAMP_WITH_LOCAL_TIME_ZONE: + final TimestampString ts = TimestampString.fromCalendarFields(calendar); + return rexBuilder.makeTimestampLiteral(ts, operand.getType().getPrecision()); + case DATE: + final DateString d = DateString.fromCalendarFields(calendar); + return rexBuilder.makeDateLiteral(d); + default: + throw Util.unexpected(operand.getType().getSqlTypeName()); + } + } + + private Range<Calendar> range(TimeUnitRange timeUnit, SqlKind comparison, Calendar c) { switch (comparison) { case EQUALS: http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/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 aec4d20..f247fbb 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 @@ -44,52 +44,52 @@ public class DateRangeRulesTest { @Test public void testExtractYearFromDateColumn() { final Fixture2 f = new Fixture2(); - final RexNode e = f.eq(f.literal(2014), f.exYear); + final RexNode e = f.eq(f.literal(2014), f.exYearD); 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(>=($9, 2014-01-01), <($9, 2015-01-01))")); - checkDateRange(f, f.eq(f.exYear, f.literal(2014)), - is("AND(>=($9, 2014-01-01), <($9, 2015-01-01))")); - checkDateRange(f, f.ge(f.exYear, f.literal(2014)), - is(">=($9, 2014-01-01)")); - checkDateRange(f, f.gt(f.exYear, f.literal(2014)), - is(">=($9, 2015-01-01)")); - checkDateRange(f, f.lt(f.exYear, f.literal(2014)), - is("<($9, 2014-01-01)")); - checkDateRange(f, f.le(f.exYear, f.literal(2014)), - is("<($9, 2015-01-01)")); - checkDateRange(f, f.ne(f.exYear, f.literal(2014)), - is("<>(EXTRACT(FLAG(YEAR), $9), 2014)")); + checkDateRange(f, e, is("AND(>=($8, 2014-01-01), <($8, 2015-01-01))")); + checkDateRange(f, f.eq(f.exYearD, f.literal(2014)), + is("AND(>=($8, 2014-01-01), <($8, 2015-01-01))")); + checkDateRange(f, f.ge(f.exYearD, f.literal(2014)), + is(">=($8, 2014-01-01)")); + checkDateRange(f, f.gt(f.exYearD, f.literal(2014)), + is(">=($8, 2015-01-01)")); + checkDateRange(f, f.lt(f.exYearD, f.literal(2014)), + is("<($8, 2014-01-01)")); + checkDateRange(f, f.le(f.exYearD, f.literal(2014)), + is("<($8, 2015-01-01)")); + checkDateRange(f, f.ne(f.exYearD, f.literal(2014)), + is("<>(EXTRACT(FLAG(YEAR), $8), 2014)")); } @Test public void testExtractYearFromTimestampColumn() { final Fixture2 f = new Fixture2(); - checkDateRange(f, f.eq(f.exYear, f.literal(2014)), - is("AND(>=($9, 2014-01-01), <($9, 2015-01-01))")); - checkDateRange(f, f.ge(f.exYear, f.literal(2014)), - is(">=($9, 2014-01-01)")); - checkDateRange(f, f.gt(f.exYear, f.literal(2014)), - is(">=($9, 2015-01-01)")); - checkDateRange(f, f.lt(f.exYear, f.literal(2014)), - is("<($9, 2014-01-01)")); - checkDateRange(f, f.le(f.exYear, f.literal(2014)), - is("<($9, 2015-01-01)")); - checkDateRange(f, f.ne(f.exYear, f.literal(2014)), + checkDateRange(f, f.eq(f.exYearTs, f.literal(2014)), + is("AND(>=($9, 2014-01-01 00:00:00), <($9, 2015-01-01 00:00:00))")); + checkDateRange(f, f.ge(f.exYearTs, f.literal(2014)), + is(">=($9, 2014-01-01 00:00:00)")); + checkDateRange(f, f.gt(f.exYearTs, f.literal(2014)), + is(">=($9, 2015-01-01 00:00:00)")); + checkDateRange(f, f.lt(f.exYearTs, f.literal(2014)), + is("<($9, 2014-01-01 00:00:00)")); + checkDateRange(f, f.le(f.exYearTs, f.literal(2014)), + is("<($9, 2015-01-01 00:00:00)")); + 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(>=($9, 2014-01-01), <($9, 2015-01-01))," - + " AND(>=($9, 2014-06-01), <($9, 2014-07-01)))"), - is("AND(>=($9, 2014-01-01), <($9, 2015-01-01)," - + " >=($9, 2014-06-01), <($9, 2014-07-01))")); + f.and(f.eq(f.exYearD, f.literal(2014)), f.eq(f.exMonthD, 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 case for @@ -98,36 +98,36 @@ public class DateRangeRulesTest { @Test public void testExtractYearAndMonthFromDateColumn2() { final Fixture2 f = new Fixture2(); final String s1 = "AND(" - + "AND(>=($9, 2000-01-01), <($9, 2001-01-01))," + + "AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + " OR(" - + "AND(>=($9, 2000-02-01), <($9, 2000-03-01)), " - + "AND(>=($9, 2000-03-01), <($9, 2000-04-01)), " - + "AND(>=($9, 2000-05-01), <($9, 2000-06-01))))"; - final String s2 = "AND(>=($9, 2000-01-01), <($9, 2001-01-01)," + + "AND(>=($8, 2000-02-01), <($8, 2000-03-01)), " + + "AND(>=($8, 2000-03-01), <($8, 2000-04-01)), " + + "AND(>=($8, 2000-05-01), <($8, 2000-06-01))))"; + final String s2 = "AND(>=($8, 2000-01-01), <($8, 2001-01-01)," + " OR(" - + "AND(>=($9, 2000-02-01), <($9, 2000-03-01)), " - + "AND(>=($9, 2000-03-01), <($9, 2000-04-01)), " - + "AND(>=($9, 2000-05-01), <($9, 2000-06-01))))"; + + "AND(>=($8, 2000-02-01), <($8, 2000-03-01)), " + + "AND(>=($8, 2000-03-01), <($8, 2000-04-01)), " + + "AND(>=($8, 2000-05-01), <($8, 2000-06-01))))"; final RexNode e = - f.and(f.eq(f.exYear, f.literal(2000)), - f.or(f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(5)))); + f.and(f.eq(f.exYearD, f.literal(2000)), + f.or(f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(5)))); checkDateRange(f, e, is(s1), is(s2)); } @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(>=($9, 2010-01-01), <($9, 2011-01-01))," - + " OR(AND(>=($9, 2010-01-31), <($9, 2010-02-01))," - + " AND(>=($9, 2010-03-31), <($9, 2010-04-01))," - + " AND(>=($9, 2010-05-31), <($9, 2010-06-01))," - + " AND(>=($9, 2010-07-31), <($9, 2010-08-01))," - + " AND(>=($9, 2010-08-31), <($9, 2010-09-01))," - + " AND(>=($9, 2010-10-31), <($9, 2010-11-01))," - + " AND(>=($9, 2010-12-31), <($9, 2011-01-01))))")); + f.and(f.eq(f.exYearD, f.literal(2010)), f.eq(f.exDayD, 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))))")); } @@ -137,45 +137,46 @@ public class DateRangeRulesTest { // namely 29th February 2012 and 2016. // // Currently there are redundant conditions, e.g. - // "AND(>=($9, 2011-01-01), <($9, 2020-01-01))". We should remove them by + // "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(>=($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))))")); + f.and(f.gt(f.exYearD, f.literal(2010)), + f.lt(f.exYearD, f.literal(2020)), + f.eq(f.exMonthD, f.literal(2)), f.eq(f.exDayD, 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.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(>=($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))))")); + f.and(f.gt(f.exYearD, f.literal(2010)), + f.lt(f.exYearD, f.literal(2020)), + f.eq(f.exMonthD, f.literal(2)), f.eq(f.exDayD, 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 case #1 for @@ -189,18 +190,18 @@ public class DateRangeRulesTest { final Fixture2 f = new Fixture2(); checkDateRange(f, f.or( - f.and(f.eq(f.exYear, f.literal(2000)), - f.or(f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(5)))), - f.and(f.eq(f.exYear, f.literal(2001)), - f.eq(f.exMonth, f.literal(1)))), - is("OR(AND(AND(>=($9, 2000-01-01), <($9, 2001-01-01))," - + " OR(AND(>=($9, 2000-02-01), <($9, 2000-03-01))," - + " AND(>=($9, 2000-03-01), <($9, 2000-04-01))," - + " AND(>=($9, 2000-05-01), <($9, 2000-06-01))))," - + " AND(AND(>=($9, 2001-01-01), <($9, 2002-01-01))," - + " AND(>=($9, 2001-01-01), <($9, 2001-02-01))))")); + f.and(f.eq(f.exYearD, f.literal(2000)), + f.or(f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(5)))), + f.and(f.eq(f.exYearD, f.literal(2001)), + f.eq(f.exMonthD, f.literal(1)))), + is("OR(AND(AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + + " OR(AND(>=($8, 2000-02-01), <($8, 2000-03-01))," + + " AND(>=($8, 2000-03-01), <($8, 2000-04-01))," + + " AND(>=($8, 2000-05-01), <($8, 2000-06-01))))," + + " AND(AND(>=($8, 2001-01-01), <($8, 2002-01-01))," + + " AND(>=($8, 2001-01-01), <($8, 2001-02-01))))")); } /** Test case #2 for @@ -215,23 +216,23 @@ public class DateRangeRulesTest { final Fixture2 f = new Fixture2(); checkDateRange(f, f.and( - f.or(f.eq(f.exYear, f.literal(2000)), - f.eq(f.exYear, f.literal(2001))), + f.or(f.eq(f.exYearD, f.literal(2000)), + f.eq(f.exYearD, f.literal(2001))), f.or( - f.and(f.eq(f.exYear, f.literal(2000)), - f.or(f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(5)))), - f.and(f.eq(f.exYear, f.literal(2001)), - f.eq(f.exMonth, f.literal(1))))), - is("AND(OR(AND(>=($9, 2000-01-01), <($9, 2001-01-01))," - + " AND(>=($9, 2001-01-01), <($9, 2002-01-01)))," - + " OR(AND(AND(>=($9, 2000-01-01), <($9, 2001-01-01))," - + " OR(AND(>=($9, 2000-02-01), <($9, 2000-03-01))," - + " AND(>=($9, 2000-03-01), <($9, 2000-04-01))," - + " AND(>=($9, 2000-05-01), <($9, 2000-06-01))))," - + " AND(AND(>=($9, 2001-01-01), <($9, 2002-01-01))," - + " AND(>=($9, 2001-01-01), <($9, 2001-02-01)))))")); + f.and(f.eq(f.exYearD, f.literal(2000)), + f.or(f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(5)))), + f.and(f.eq(f.exYearD, f.literal(2001)), + f.eq(f.exMonthD, f.literal(1))))), + is("AND(OR(AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + + " AND(>=($8, 2001-01-01), <($8, 2002-01-01)))," + + " OR(AND(AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + + " OR(AND(>=($8, 2000-02-01), <($8, 2000-03-01))," + + " AND(>=($8, 2000-03-01), <($8, 2000-04-01))," + + " AND(>=($8, 2000-05-01), <($8, 2000-06-01))))," + + " AND(AND(>=($8, 2001-01-01), <($8, 2002-01-01))," + + " AND(>=($8, 2001-01-01), <($8, 2001-02-01)))))")); } /** Test case #3 for @@ -246,21 +247,21 @@ public class DateRangeRulesTest { final Fixture2 f = new Fixture2(); checkDateRange(f, f.and( - f.ne(f.exYear, f.literal(2000)), + f.ne(f.exYearD, f.literal(2000)), f.or( - f.and(f.eq(f.exYear, f.literal(2000)), - f.or(f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(5)))), - f.and(f.eq(f.exYear, f.literal(2001)), - f.eq(f.exMonth, f.literal(1))))), - is("AND(<>(EXTRACT(FLAG(YEAR), $9), 2000)," - + " OR(AND(AND(>=($9, 2000-01-01), <($9, 2001-01-01))," - + " OR(AND(>=($9, 2000-02-01), <($9, 2000-03-01))," - + " AND(>=($9, 2000-03-01), <($9, 2000-04-01))," - + " AND(>=($9, 2000-05-01), <($9, 2000-06-01))))," - + " AND(AND(>=($9, 2001-01-01), <($9, 2002-01-01))," - + " AND(>=($9, 2001-01-01), <($9, 2001-02-01)))))")); + f.and(f.eq(f.exYearD, f.literal(2000)), + f.or(f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(5)))), + f.and(f.eq(f.exYearD, f.literal(2001)), + f.eq(f.exMonthD, f.literal(1))))), + is("AND(<>(EXTRACT(FLAG(YEAR), $8), 2000)," + + " OR(AND(AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + + " OR(AND(>=($8, 2000-02-01), <($8, 2000-03-01))," + + " AND(>=($8, 2000-03-01), <($8, 2000-04-01))," + + " AND(>=($8, 2000-05-01), <($8, 2000-06-01))))," + + " AND(AND(>=($8, 2001-01-01), <($8, 2002-01-01))," + + " AND(>=($8, 2001-01-01), <($8, 2001-02-01)))))")); } /** Test case #4 for @@ -275,29 +276,29 @@ public class DateRangeRulesTest { final Fixture2 f = new Fixture2(); checkDateRange(f, f.and( - f.or(f.eq(f.exMonth, f.literal(1)), - f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(4)), - f.eq(f.exMonth, f.literal(5))), + f.or(f.eq(f.exMonthD, f.literal(1)), + f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(4)), + f.eq(f.exMonthD, f.literal(5))), f.or( - f.and(f.eq(f.exYear, f.literal(2000)), - f.or(f.eq(f.exMonth, f.literal(2)), - f.eq(f.exMonth, f.literal(3)), - f.eq(f.exMonth, f.literal(5)))), - f.and(f.eq(f.exYear, f.literal(2001)), - f.eq(f.exMonth, f.literal(1))))), - is("AND(OR(=(EXTRACT(FLAG(MONTH), $9), 1)," - + " =(EXTRACT(FLAG(MONTH), $9), 2)," - + " =(EXTRACT(FLAG(MONTH), $9), 3)," - + " =(EXTRACT(FLAG(MONTH), $9), 4)," - + " =(EXTRACT(FLAG(MONTH), $9), 5))," - + " OR(AND(AND(>=($9, 2000-01-01), <($9, 2001-01-01))," - + " OR(AND(>=($9, 2000-02-01), <($9, 2000-03-01))," - + " AND(>=($9, 2000-03-01), <($9, 2000-04-01))," - + " AND(>=($9, 2000-05-01), <($9, 2000-06-01))))," - + " AND(AND(>=($9, 2001-01-01), <($9, 2002-01-01))," - + " AND(>=($9, 2001-01-01), <($9, 2001-02-01)))))")); + f.and(f.eq(f.exYearD, f.literal(2000)), + f.or(f.eq(f.exMonthD, f.literal(2)), + f.eq(f.exMonthD, f.literal(3)), + f.eq(f.exMonthD, f.literal(5)))), + f.and(f.eq(f.exYearD, f.literal(2001)), + f.eq(f.exMonthD, f.literal(1))))), + is("AND(OR(=(EXTRACT(FLAG(MONTH), $8), 1)," + + " =(EXTRACT(FLAG(MONTH), $8), 2)," + + " =(EXTRACT(FLAG(MONTH), $8), 3)," + + " =(EXTRACT(FLAG(MONTH), $8), 4)," + + " =(EXTRACT(FLAG(MONTH), $8), 5))," + + " OR(AND(AND(>=($8, 2000-01-01), <($8, 2001-01-01))," + + " OR(AND(>=($8, 2000-02-01), <($8, 2000-03-01))," + + " AND(>=($8, 2000-03-01), <($8, 2000-04-01))," + + " AND(>=($8, 2000-05-01), <($8, 2000-06-01))))," + + " AND(AND(>=($8, 2001-01-01), <($8, 2002-01-01))," + + " AND(>=($8, 2001-01-01), <($8, 2001-02-01)))))")); } private static Set<TimeUnitRange> set(TimeUnitRange... es) { @@ -325,19 +326,30 @@ public class DateRangeRulesTest { /** 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; // EXTRACT YEAR from TIMESTAMP field + private final RexNode exMonthTs; // EXTRACT MONTH from TIMESTAMP field + private final RexNode exDayTs; // EXTRACT DAY from TIMESTAMP field + private final RexNode exYearD; // EXTRACT YEAR from DATE field + private final RexNode exMonthD; // EXTRACT MONTH from DATE field + private final RexNode exDayD; // EXTRACT DAY from DATE field Fixture2() { - exYear = rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT, + exYearTs = rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), ts)); - exMonth = rexBuilder.makeCall(intRelDataType, + exMonthTs = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.EXTRACT, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), ts)); - exDay = rexBuilder.makeCall(intRelDataType, + exDayTs = rexBuilder.makeCall(intRelDataType, SqlStdOperatorTable.EXTRACT, ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), ts)); + exYearD = rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT, + ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), d)); + exMonthD = rexBuilder.makeCall(intRelDataType, + SqlStdOperatorTable.EXTRACT, + ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MONTH), d)); + exDayD = rexBuilder.makeCall(intRelDataType, + SqlStdOperatorTable.EXTRACT, + ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), d)); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index e1c9e9c..19c722d 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -1320,6 +1320,14 @@ public abstract class SqlOperatorBaseTest { "cast('1945-02-24 12:42:25.34' as TIMESTAMP)", "1945-02-24 12:42:25", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "cast('1945-12-31' as TIMESTAMP)", + "1945-12-31 00:00:00", + "TIMESTAMP(0) NOT NULL"); + tester.checkScalar( + "cast('2004-02-29' as TIMESTAMP)", + "2004-02-29 00:00:00", + "TIMESTAMP(0) NOT NULL"); if (Bug.FRG282_FIXED) { tester.checkScalar( http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/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 b42241c..0a2d74b 100644 --- a/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java +++ b/core/src/test/java/org/apache/calcite/test/RexImplicationCheckerTest.java @@ -184,15 +184,15 @@ public class RexImplicationCheckerTest { @Test public void testSimpleDate() { final Fixture f = new Fixture(); final DateString d = DateString.fromCalendarFields(Util.calendar()); - final RexNode node1 = f.ge(f.dt, f.rexBuilder.makeDateLiteral(d)); - final RexNode node2 = f.eq(f.dt, f.rexBuilder.makeDateLiteral(d)); + final RexNode node1 = f.ge(f.d, f.dateLiteral(d)); + final RexNode node2 = f.eq(f.d, f.dateLiteral(d)); f.checkImplies(node2, node1); f.checkNotImplies(node1, node2); final DateString dBeforeEpoch1 = DateString.fromDaysSinceEpoch(-12345); final DateString dBeforeEpoch2 = DateString.fromDaysSinceEpoch(-123); - final RexNode nodeBe1 = f.lt(f.dt, f.rexBuilder.makeDateLiteral(dBeforeEpoch1)); - final RexNode nodeBe2 = f.lt(f.dt, f.rexBuilder.makeDateLiteral(dBeforeEpoch2)); + final RexNode nodeBe1 = f.lt(f.d, f.dateLiteral(dBeforeEpoch1)); + final RexNode nodeBe2 = f.lt(f.d, f.dateLiteral(dBeforeEpoch2)); f.checkImplies(nodeBe1, nodeBe2); f.checkNotImplies(nodeBe2, nodeBe1); } @@ -389,22 +389,22 @@ public class RexImplicationCheckerTest { public final RelDataType floatDataType; public final RelDataType charDataType; public final RelDataType dateDataType; - public final RelDataType timeStampDataType; + 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 RexNode bl; // a field of Java type "Boolean" + public final RexNode i; // a field of Java type "Integer" + public final RexNode dec; // a field of Java type "Double" + public final RexNode lg; // a field of Java type "Long" + public final RexNode sh; // a field of Java type "Short" + public final RexNode by; // a field of Java type "Byte" + public final RexNode fl; // a field of Java type "Float" (not a SQL FLOAT) + public final RexNode d; // a field of Java type "Date" + public final RexNode ch; // a field of Java type "Character" + public final RexNode ts; // a field of Java type "Timestamp" + public final RexNode t; // a field of Java type "Time" + public final RexNode str; // a field of Java type "String" public final RexImplicationChecker checker; public final RelDataType rowType; @@ -423,7 +423,7 @@ public class RexImplicationCheckerTest { floatDataType = typeFactory.createJavaType(Float.class); charDataType = typeFactory.createJavaType(Character.class); dateDataType = typeFactory.createJavaType(Date.class); - timeStampDataType = typeFactory.createJavaType(Timestamp.class); + timestampDataType = typeFactory.createJavaType(Timestamp.class); timeDataType = typeFactory.createJavaType(Time.class); stringDataType = typeFactory.createJavaType(String.class); @@ -435,8 +435,8 @@ public class RexImplicationCheckerTest { by = ref(5, byteDataType); fl = ref(6, floatDataType); ch = ref(7, charDataType); - dt = ref(8, dateDataType); - ts = ref(9, timeStampDataType); + d = ref(8, dateDataType); + ts = ref(9, timestampDataType); t = ref(10, timeDataType); str = ref(11, stringDataType); @@ -450,7 +450,7 @@ public class RexImplicationCheckerTest { .add("float", floatDataType) .add("char", charDataType) .add("date", dateDataType) - .add("timestamp", timeStampDataType) + .add("timestamp", timestampDataType) .add("time", timeDataType) .add("string", stringDataType) .build(); @@ -544,9 +544,13 @@ public class RexImplicationCheckerTest { new NlsString(z, null, SqlCollation.COERCIBLE)); } + public RexNode dateLiteral(DateString d) { + return rexBuilder.makeDateLiteral(d); + } + public RexNode timestampLiteral(TimestampString ts) { return rexBuilder.makeTimestampLiteral(ts, - timeStampDataType.getPrecision()); + timestampDataType.getPrecision()); } public RexNode timeLiteral(TimeString t) { http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/core/src/test/resources/sql/misc.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index 9a79bb7..e32ffbf 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -1508,7 +1508,7 @@ select (case when (true) then 1 end) from (values(1)); EXPR$0 INTEGER(10) !type -# Cast an character literal to a timestamp; note: the plan does not contain CAST +# Cast a character literal to a timestamp; note: the plan does not contain CAST values cast('1969-07-21 12:34:56' as timestamp); +---------------------+ | EXPR$0 | @@ -1522,6 +1522,20 @@ EnumerableCalc(expr#0=[{inputs}], expr#1=[1969-07-21 12:34:56], EXPR$0=[$t1]) EnumerableValues(tuples=[[{ 0 }]]) !plan +# Cast a character literal without time to a timestamp; note: the plan does not contain CAST +values cast('1969-07-21' as timestamp); ++---------------------+ +| EXPR$0 | ++---------------------+ +| 1969-07-21 00:00:00 | ++---------------------+ +(1 row) + +!ok +EnumerableCalc(expr#0=[{inputs}], expr#1=[1969-07-21 00:00:00], EXPR$0=[$t1]) + EnumerableValues(tuples=[[{ 0 }]]) +!plan + # Cast a character literal to a date; note: the plan does not contain CAST values cast('1969-07-21' as date); +------------+ http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/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 6108813..e5931b1 100644 --- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java +++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java @@ -2986,8 +2986,7 @@ public class DruidAdapterIT { .returnsUnordered("EXPR$0=3799"); } - @Test - public void testCountWithNonNull() { + @Test public void testCountWithNonNull() { final String sql = "select count(\"timestamp\") from \"foodmart\"\n"; final String druidQuery = "{'queryType':'timeseries','dataSource':'foodmart'"; sql(sql) @@ -3383,8 +3382,7 @@ public class DruidAdapterIT { .returnsUnordered("C=86829"); } - @Test - public void testFilterwithFloorOnTime() { + @Test public void testFilterWithFloorOnTime() { // Test filter on floor on time column is pushed to druid final String sql = "Select cast(floor(\"timestamp\" to MONTH) as timestamp) as t from \"foodmart\" where " @@ -3408,8 +3406,7 @@ public class DruidAdapterIT { .returnsOrdered("T=1997-01-01 00:00:00", "T=1997-01-01 00:00:00"); } - @Test - public void testSelectFloorOnTimeWithFilterOnFloorOnTime() { + @Test public void testSelectFloorOnTimeWithFilterOnFloorOnTime() { final String sql = "Select cast(floor(\"timestamp\" to MONTH) as timestamp) as t from " + "\"foodmart\" where floor(\"timestamp\" to MONTH) >= '1997-05-01 00:00:00 UTC' order by t" + " limit 1"; @@ -3424,8 +3421,7 @@ public class DruidAdapterIT { .returnsOrdered("T=1997-05-01 00:00:00"); } - @Test - public void testTmeWithFilterOnFloorOnTimeAndCastToTimestamp() { + @Test public void testTmeWithFilterOnFloorOnTimeAndCastToTimestamp() { final String sql = "Select cast(floor(\"timestamp\" to MONTH) as timestamp) as t from " + "\"foodmart\" where floor(\"timestamp\" to MONTH) >= cast('1997-05-01 00:00:00' as TIMESTAMP) order by t" + " limit 1"; @@ -3440,8 +3436,7 @@ public class DruidAdapterIT { .returnsOrdered("T=1997-05-01 00:00:00"); } - @Test - public void testTmeWithFilterOnFloorOnTimeWithTimezone() { + @Test public void testTmeWithFilterOnFloorOnTimeWithTimezone() { final String sql = "Select cast(floor(\"timestamp\" to MONTH) as timestamp) as t from " + "\"foodmart\" where floor(\"timestamp\" to MONTH) >= cast('1997-05-01 00:00:00'" + " as TIMESTAMP) order by t limit 1"; @@ -3464,8 +3459,7 @@ public class DruidAdapterIT { .returnsOrdered("T=1997-05-01 05:30:00"); } - @Test - public void testTmeWithFilterOnFloorOnTimeWithTimezoneConversion() { + @Test public void testTmeWithFilterOnFloorOnTimeWithTimezoneConversion() { final String sql = "Select cast(floor(\"timestamp\" to MONTH) as timestamp) as t from " + "\"foodmart\" where floor(\"timestamp\" to MONTH) >= '1997-04-30 18:30:00 UTC' order by t" + " limit 1"; @@ -3488,6 +3482,17 @@ public class DruidAdapterIT { .returnsOrdered("T=1997-05-01 05:30:00"); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2122";>[CALCITE-2122] + * DateRangeRules issues</a>. */ + @Test public void testCombinationOfValidAndNotValidAndInterval() { + final String sql = "SELECT COUNT(*) FROM \"foodmart\" " + + "WHERE \"timestamp\" < CAST('1998-01-02' as TIMESTAMP) AND " + + "EXTRACT(MONTH FROM \"timestamp\") = 01 AND EXTRACT(YEAR FROM \"timestamp\") = 1996 "; + sql(sql, FOODMART) + .runs() + .queryContains(druidChecker("{\"queryType\":\"timeseries\"")); + } } // End DruidAdapterIT.java http://git-wip-us.apache.org/repos/asf/calcite/blob/2918b8fe/druid/src/test/java/org/apache/calcite/test/DruidDateRangeRulesTest.java ---------------------------------------------------------------------- diff --git a/druid/src/test/java/org/apache/calcite/test/DruidDateRangeRulesTest.java b/druid/src/test/java/org/apache/calcite/test/DruidDateRangeRulesTest.java index 0b5d034..ceb2d5e 100644 --- a/druid/src/test/java/org/apache/calcite/test/DruidDateRangeRulesTest.java +++ b/druid/src/test/java/org/apache/calcite/test/DruidDateRangeRulesTest.java @@ -128,11 +128,11 @@ public class DruidDateRangeRulesTest { c.set(2011, Calendar.JANUARY, 1); final TimestampString to = TimestampString.fromCalendarFields(c); - // dt >= 2010-01-01 AND dt < 2011-01-01 + // d >= 2010-01-01 AND d < 2011-01-01 checkDateRangeNoSimplify(f, f.and( - f.ge(f.dt, f.cast(f.timeStampDataType, f.timestampLiteral(from))), - f.lt(f.dt, f.cast(f.timeStampDataType, f.timestampLiteral(to)))), + f.ge(f.d, f.cast(f.timestampDataType, f.timestampLiteral(from))), + f.lt(f.d, f.cast(f.timestampDataType, f.timestampLiteral(to)))), is("[2010-01-01T00:00:00.000Z/2011-01-01T00:00:00.000Z]")); }
