This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 1a0b436c50 [CALCITE-7762] DateRangeRules may produce incorrect ranges,
hang, or excessively expand sub-day predicates
1a0b436c50 is described below
commit 1a0b436c50acf0924b6d700711b020e35f9874fc
Author: Darpan Lunagariya <[email protected]>
AuthorDate: Tue Sep 8 13:05:13 2026 +0530
[CALCITE-7762] DateRangeRules may produce incorrect ranges, hang, or
excessively expand sub-day predicates
---
.../apache/calcite/rel/rules/DateRangeRules.java | 94 +++++++++++++--
.../calcite/rel/rules/DateRangeRulesTest.java | 134 +++++++++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.java | 42 +++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 63 ++++++++++
4 files changed, 323 insertions(+), 10 deletions(-)
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 ae0d62e0fc..9a3940dff9 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
@@ -100,12 +100,16 @@ private DateRangeRules() { }
.as(FilterDateRangeRule.FilterDateRangeRuleConfig.class)
.toRule();
+ /** Default maximum number of matching ranges that may be enumerated while
+ * rewriting lower time-unit {@code EXTRACT} calls in one expression. */
+ public static final int DEFAULT_MAX_RANGE_COUNT = 100;
+
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.HOUR, Calendar.HOUR_OF_DAY)
.put(TimeUnitRange.MINUTE, Calendar.MINUTE)
.put(TimeUnitRange.SECOND, Calendar.SECOND)
.put(TimeUnitRange.MILLISECOND, Calendar.MILLISECOND)
@@ -149,7 +153,19 @@ static ImmutableSortedSet<TimeUnitRange>
extractTimeUnits(RexNode e) {
@VisibleForTesting
public static RexNode replaceTimeUnits(RexBuilder rexBuilder, RexNode e,
String timeZone) {
- e = RexUtil.expandSearch(rexBuilder, null, e);
+ return replaceTimeUnits(rexBuilder, e, timeZone, DEFAULT_MAX_RANGE_COUNT);
+ }
+
+ /** Replaces calls to EXTRACT, FLOOR and CEIL in an expression, limiting the
+ * number of ranges enumerated for lower time units. A limit of -1 allows
+ * unlimited ranges. */
+ @VisibleForTesting
+ static RexNode replaceTimeUnits(RexBuilder rexBuilder, RexNode e,
+ String timeZone, int maxRangeCount) {
+ assert maxRangeCount >= -1;
+ // RexUtil expands a SEARCH only if its complexity is strictly less than
+ // the limit. Add one so maxRangeCount remains inclusive.
+ e = RexUtil.expandSearch(rexBuilder, null, e, maxRangeCount + 1);
ImmutableSortedSet<TimeUnitRange> timeUnits = extractTimeUnits(e);
if (!timeUnits.contains(TimeUnitRange.YEAR)) {
// Case when we have FLOOR or CEIL but no extract on YEAR.
@@ -159,11 +175,13 @@ public static RexNode replaceTimeUnits(RexBuilder
rexBuilder, RexNode e,
.addAll(timeUnits).add(TimeUnitRange.YEAR).build();
}
final Map<RexNode, RangeSet<Calendar>> operandRanges = new HashMap<>();
+ final RangeExpansionLimiter rangeExpansionLimiter =
+ new RangeExpansionLimiter(maxRangeCount);
for (TimeUnitRange timeUnit : timeUnits) {
e =
e.accept(
new ExtractShuttle(rexBuilder, timeUnit, operandRanges,
timeUnits,
- timeZone));
+ timeZone, rangeExpansionLimiter));
}
return e;
}
@@ -206,7 +224,8 @@ private static boolean containsRoundingExpression(Filter
filter) {
final String timeZone = filter.getCluster().getPlanner().getContext()
.unwrapOrThrow(CalciteConnectionConfig.class).timeZone();
final RexNode condition =
- replaceTimeUnits(rexBuilder, filter.getCondition(), timeZone);
+ replaceTimeUnits(rexBuilder, filter.getCondition(), timeZone,
+ config.maxRangeCount());
if (condition.equals(filter.getCondition())) {
return;
}
@@ -229,6 +248,16 @@ public interface FilterDateRangeRuleConfig extends
RelRule.Config {
@Override default FilterDateRangeRule toRule() {
return new FilterDateRangeRule(this);
}
+
+ /** Maximum number of matching ranges that may be enumerated while
+ * rewriting lower time-unit {@code EXTRACT} calls. A value of -1 allows
+ * unlimited ranges. */
+ @Value.Default default int maxRangeCount() {
+ return DEFAULT_MAX_RANGE_COUNT;
+ }
+
+ /** Sets {@link #maxRangeCount()}. */
+ FilterDateRangeRuleConfig withMaxRangeCount(int maxRangeCount);
}
}
@@ -274,6 +303,35 @@ private ExtractFinder() {
}
}
+ /** Tracks the number of ranges generated while rewriting an expression. */
+ private static class RangeExpansionLimiter {
+ private final int maxRangeCount;
+ private int rangeCount;
+
+ RangeExpansionLimiter(int maxRangeCount) {
+ this.maxRangeCount = maxRangeCount;
+ }
+
+ int mark() {
+ return rangeCount;
+ }
+
+ boolean tryAddRange() {
+ if (maxRangeCount == -1) {
+ return true;
+ }
+ if (rangeCount >= maxRangeCount) {
+ return false;
+ }
+ ++rangeCount;
+ return true;
+ }
+
+ void reset(int rangeCount) {
+ this.rangeCount = rangeCount;
+ }
+ }
+
/** Walks over an expression, replacing calls to
* {@code EXTRACT}, {@code FLOOR} and {@code CEIL} with date ranges. */
@VisibleForTesting
@@ -284,16 +342,27 @@ static class ExtractShuttle extends RexShuttle {
private final Deque<RexCall> calls = new ArrayDeque<>();
private final ImmutableSortedSet<TimeUnitRange> timeUnitRanges;
private final String timeZone;
+ private final RangeExpansionLimiter rangeExpansionLimiter;
@VisibleForTesting
ExtractShuttle(RexBuilder rexBuilder, TimeUnitRange timeUnit,
Map<RexNode, RangeSet<Calendar>> operandRanges,
ImmutableSortedSet<TimeUnitRange> timeUnitRanges, String timeZone) {
+ this(rexBuilder, timeUnit, operandRanges, timeUnitRanges, timeZone,
+ new RangeExpansionLimiter(DEFAULT_MAX_RANGE_COUNT));
+ }
+
+ private ExtractShuttle(RexBuilder rexBuilder, TimeUnitRange timeUnit,
+ Map<RexNode, RangeSet<Calendar>> operandRanges,
+ ImmutableSortedSet<TimeUnitRange> timeUnitRanges, String timeZone,
+ RangeExpansionLimiter rangeExpansionLimiter) {
this.rexBuilder = requireNonNull(rexBuilder, "rexBuilder");
this.timeUnit = requireNonNull(timeUnit, "timeUnit");
this.operandRanges = requireNonNull(operandRanges, "operandRanges");
this.timeUnitRanges = requireNonNull(timeUnitRanges, "timeUnitRanges");
this.timeZone = timeZone;
+ this.rangeExpansionLimiter =
+ requireNonNull(rangeExpansionLimiter, "rangeExpansionLimiter");
}
@Override public RexNode visitCall(RexCall call) {
@@ -314,7 +383,7 @@ static class ExtractShuttle extends RexShuttle {
RexNode operand = subCall.getOperands().get(1);
if (canRewriteExtract(operand)) {
return compareExtract(call.getKind().reverse(), operand,
- (RexLiteral) op0);
+ (RexLiteral) op0, call);
}
}
if (isFloorCeilCall(op1)) {
@@ -341,7 +410,7 @@ static class ExtractShuttle extends RexShuttle {
RexNode operand = subCall.operands.get(1);
if (canRewriteExtract(operand)) {
return compareExtract(call.getKind(),
- subCall.operands.get(1), (RexLiteral) op1);
+ subCall.operands.get(1), (RexLiteral) op1, call);
}
}
if (isFloorCeilCall(op0)) {
@@ -420,7 +489,7 @@ private boolean canRewriteExtract(RexNode operand) {
clonedOperand =
clonedOperand.accept(
new ExtractShuttle(rexBuilder, timeUnit, operandRanges,
- timeUnitRanges, timeZone));
+ timeUnitRanges, timeZone, rangeExpansionLimiter));
}
if ((clonedOperand != operand) && (update != null)) {
update[0] = true;
@@ -449,12 +518,13 @@ boolean isExtractCall(RexNode e) {
}
RexNode compareExtract(SqlKind comparison, RexNode operand,
- RexLiteral literal) {
+ RexLiteral literal, RexCall originalCall) {
RangeSet<Calendar> rangeSet = operandRanges.get(operand);
if (rangeSet == null) {
rangeSet = ImmutableRangeSet.<Calendar>of().complement();
}
final RangeSet<Calendar> s2 = TreeRangeSet.create();
+ final int rangeCountMark = rangeExpansionLimiter.mark();
// Calendar.MONTH is 0-based
final int v = RexLiteral.intValue(literal)
- (timeUnit == TimeUnitRange.MONTH ? 1 : 0);
@@ -482,6 +552,10 @@ RexNode compareExtract(SqlKind comparison, RexNode operand,
c = (Calendar) r.lowerEndpoint().clone();
int i = 0;
while (next(c, timeUnit, v, r, i++ > 0)) {
+ if (!rangeExpansionLimiter.tryAddRange()) {
+ rangeExpansionLimiter.reset(rangeCountMark);
+ return originalCall;
+ }
s2.add(extractRange(timeUnit, comparison, c));
}
}
@@ -536,10 +610,10 @@ private static boolean isValid(int v, TimeUnitRange
timeUnit) {
case DAY:
return v > 0 && v <= 31;
case HOUR:
- return v >= 0 && v <= 24;
+ return v >= 0 && v < 24;
case MINUTE:
case SECOND:
- return v >= 0 && v <= 60;
+ return v >= 0 && v < 60;
default:
return false;
}
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 159d7643c9..3928c40a84 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
@@ -17,7 +17,9 @@
package org.apache.calcite.rel.rules;
import org.apache.calcite.avatica.util.TimeUnitRange;
+import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.test.RexImplicationCheckerFixtures.Fixture;
import org.apache.calcite.util.DateString;
@@ -35,6 +37,7 @@
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.hasSize;
import static org.hamcrest.Matchers.hasToString;
import static org.hamcrest.core.IsInstanceOf.any;
@@ -355,6 +358,125 @@ class DateRangeRulesTest {
+ " AND(>=($9, 2010-02-01 00:00:00), <($9, 2010-03-01 00:00:00)),
false)"));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractHourFromTimestampColumn() {
+ final Fixture2 f = new Fixture2();
+ checkDateRange(f,
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(f.exMonthTs, f.literal(2)),
+ f.eq(f.exDayTs, f.literal(4)),
+ f.eq(f.exHourTs, f.literal(13))),
+ 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)),"
+ + " AND(>=($9, 2010-02-04 00:00:00), <($9, 2010-02-05 00:00:00)),"
+ + " AND(>=($9, 2010-02-04 13:00:00), <($9, 2010-02-04
14:00:00)))"));
+
+ checkDateRange(f,
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(f.exHourTs, f.literal(24))),
+ is("AND(AND(>=($9, 2010-01-01 00:00:00), <($9, 2011-01-01 00:00:00)),"
+ + " false)"));
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractInvalidMinuteAndSecond() {
+ final Fixture2 f = new Fixture2();
+ for (RexNode extract : ImmutableList.of(f.exMinuteTs, f.exSecondTs)) {
+ checkDateRange(f,
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(extract, f.literal(60))),
+ is("AND(AND(>=($9, 2010-01-01 00:00:00),"
+ + " <($9, 2011-01-01 00:00:00)), false)"));
+ }
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractRangeExpansionLimit() {
+ final Fixture2 f = new Fixture2();
+ final RexNode condition =
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(f.exDayTs, f.literal(31)));
+
+ final RexNode atLimit =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, condition, "UTC", 7);
+ final RexNode dayRanges = ((RexCall) atLimit).operands.get(1);
+ assertThat(((RexCall) dayRanges).operands, hasSize(7));
+
+ final RexNode overLimit =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, condition, "UTC", 6);
+ assertThat(overLimit,
+ hasToString("AND(AND(>=($9, 2010-01-01 00:00:00),"
+ + " <($9, 2011-01-01 00:00:00)), =(EXTRACT(FLAG(DAY), $9), 31))"));
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testSearchExpansionLimit() {
+ final Fixture2 f = new Fixture2();
+ final ImmutableList.Builder<RexNode> yearLiterals =
ImmutableList.builder();
+ for (int year = 2000; year < 2100; ++year) {
+ yearLiterals.add(f.literal(year));
+ }
+ final RexNode searchAtLimit =
+ f.rexBuilder.makeIn(f.exYearTs, yearLiterals.build());
+ assertThat(searchAtLimit.getKind(), is(SqlKind.SEARCH));
+ final RexNode rewrittenAtLimit =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, searchAtLimit, "UTC");
+ assertThat(rewrittenAtLimit.getKind(), is(SqlKind.OR));
+ assertThat(((RexCall) rewrittenAtLimit).operands, hasSize(100));
+
+ yearLiterals.add(f.literal(2100));
+ final RexNode searchOverLimit =
+ f.rexBuilder.makeIn(f.exYearTs, yearLiterals.build());
+ assertThat(searchOverLimit.getKind(), is(SqlKind.SEARCH));
+ final RexNode rewrittenOverLimit =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, searchOverLimit, "UTC");
+ assertThat(rewrittenOverLimit, is(searchOverLimit));
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractRangeExpansionUsesDefaultLimit() {
+ final Fixture2 f = new Fixture2();
+ final RexNode condition =
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(f.exHourTs, f.literal(13)));
+ final RexNode rewritten =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, condition, "UTC");
+ assertThat(rewritten,
+ hasToString("AND(AND(>=($9, 2010-01-01 00:00:00),"
+ + " <($9, 2011-01-01 00:00:00)), =(EXTRACT(FLAG(HOUR), $9),
13))"));
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractSecondRangeExpansionUsesDefaultLimit() {
+ final Fixture2 f = new Fixture2();
+ final RexNode condition =
+ f.and(f.eq(f.exYearTs, f.literal(2010)),
+ f.eq(f.exSecondTs, f.literal(15)));
+ final RexNode rewritten =
+ DateRangeRules.replaceTimeUnits(f.rexBuilder, condition, "UTC");
+ assertThat(rewritten,
+ hasToString("AND(AND(>=($9, 2010-01-01 00:00:00),"
+ + " <($9, 2011-01-01 00:00:00)), =(EXTRACT(FLAG(SECOND), $9),
15))"));
+ }
+
@Test void testUnboundYearExtractRewrite() {
final Fixture2 f = new Fixture2();
// No lower bound on YEAR
@@ -739,6 +861,9 @@ private static class Fixture2 extends Fixture {
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 exHourTs; // EXTRACT HOUR from TIMESTAMP field
+ private final RexNode exMinuteTs; // EXTRACT MINUTE from TIMESTAMP field
+ private final RexNode exSecondTs; // EXTRACT SECOND 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
@@ -765,6 +890,15 @@ private static class Fixture2 extends Fixture {
exDayTs =
rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.DAY), ts));
+ exHourTs =
+ rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
+ ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.HOUR), ts));
+ exMinuteTs =
+ rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
+ ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.MINUTE), ts));
+ exSecondTs =
+ rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
+ ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.SECOND), ts));
exYearD =
rexBuilder.makeCall(SqlStdOperatorTable.EXTRACT,
ImmutableList.of(rexBuilder.makeFlag(TimeUnitRange.YEAR), d));
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 198e869177..7eae22b17e 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -10132,6 +10132,48 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule
rule) {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testFloorDayToRangeAfterNoon() {
+ final String sql = "select floor(hiredate to day) as d\n"
+ + "from sales.emp_b\n"
+ + "where floor(hiredate to day)"
+ + " < timestamp '2010-02-04 13:00:00'";
+ sql(sql).withRule(DateRangeRules.FILTER_INSTANCE)
+ .withContext(c -> Contexts.of(CalciteConnectionConfig.DEFAULT, c))
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testCeilDayToRangeAfterNoon() {
+ final String sql = "select ceil(hiredate to day) as d\n"
+ + "from sales.emp_b\n"
+ + "where ceil(hiredate to day)"
+ + " < timestamp '2010-02-04 13:00:00'";
+ sql(sql).withRule(DateRangeRules.FILTER_INSTANCE)
+ .withContext(c -> Contexts.of(CalciteConnectionConfig.DEFAULT, c))
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7762";>[CALCITE-7762]
+ * DateRangeRules may produce incorrect ranges, hang, or excessively expand
+ * sub-day predicates</a>. */
+ @Test void testExtractHourRangeExpansionIsBounded() {
+ final String sql = "select extract(hour from hiredate) as h\n"
+ + "from sales.emp_b\n"
+ + "where extract(year from hiredate) = 2010"
+ + " and extract(hour from hiredate) = 13";
+ sql(sql).withRule(DateRangeRules.FILTER_INSTANCE)
+ .withContext(c -> Contexts.of(CalciteConnectionConfig.DEFAULT, c))
+ .check();
+ }
+
@Test void testFilterRemoveIsNotDistinctFromRule() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
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 85d71efdf8..88c80246ad 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2080,6 +2080,27 @@ case when cast(ename as double) < 5 then 0.0
<![CDATA[
LogicalProject(T=[CASE(<(CAST(CASE(>($1, 'abc'), $1,
null:VARCHAR(20))):DOUBLE, 5.0E0), 0.0E0:DOUBLE, CASE(IS NOT
NULL(CAST(CASE(>($1, 'abc'), $1, null:VARCHAR(20))):DOUBLE),
CAST(CAST(CASE(>($1, 'abc'), $1, null:VARCHAR(20))):DOUBLE):DOUBLE NOT NULL,
1.0E0:DOUBLE))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testCeilDayToRangeAfterNoon">
+ <Resource name="sql">
+ <![CDATA[select ceil(hiredate to day) as d
+from sales.emp_b
+where ceil(hiredate to day) < timestamp '2010-02-04 13:00:00']]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(D=[CEIL($4, FLAG(DAY))])
+ LogicalFilter(condition=[<(CEIL($4, FLAG(DAY)), 2010-02-04 13:00:00)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(D=[CEIL($4, FLAG(DAY))])
+ LogicalFilter(condition=[<=($4, 2010-02-04 00:00:00)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</Resource>
</TestCase>
@@ -5940,6 +5961,27 @@ WHERE bool_col IS FALSE]]>
LogicalProject(BOOL_COL=[$0])
LogicalFilter(condition=[IS FALSE($0)])
LogicalValues(tuples=[[{ true }, { false }, { null }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testExtractHourRangeExpansionIsBounded">
+ <Resource name="sql">
+ <![CDATA[select extract(hour from hiredate) as h
+from sales.emp_b
+where extract(year from hiredate) = 2010 and extract(hour from hiredate) =
13]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(H=[EXTRACT(FLAG(HOUR), $4)])
+ LogicalFilter(condition=[AND(=(EXTRACT(FLAG(YEAR), $4), 2010),
=(EXTRACT(FLAG(HOUR), $4), 13))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(H=[EXTRACT(FLAG(HOUR), $4)])
+ LogicalFilter(condition=[AND(SEARCH($4, Sarg[[2010-01-01
00:00:00..2011-01-01 00:00:00)]), =(EXTRACT(FLAG(HOUR), $4), 13))])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</Resource>
</TestCase>
@@ -6524,6 +6566,27 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalFilter(condition=[=($cor0.DEPTNO, $9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+(*(2, $7),
30)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFloorDayToRangeAfterNoon">
+ <Resource name="sql">
+ <![CDATA[select floor(hiredate to day) as d
+from sales.emp_b
+where floor(hiredate to day) < timestamp '2010-02-04 13:00:00']]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(D=[FLOOR($4, FLAG(DAY))])
+ LogicalFilter(condition=[<(FLOOR($4, FLAG(DAY)), 2010-02-04 13:00:00)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(D=[FLOOR($4, FLAG(DAY))])
+ LogicalFilter(condition=[<($4, 2010-02-05 00:00:00)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</Resource>
</TestCase>
