This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 91f5bb5589ca5b7c08b09969ad21c5e9bd18d293 Author: Julian Hyde <jh...@apache.org> AuthorDate: Thu Oct 17 01:22:09 2019 -0700 [CALCITE-3412] FLOOR(timestamp TO WEEK) gives wrong result Upgrade to avatica-1.16.0, getting fix for [CALCITE-3199] "DateTimeUtils.unixDateCeil should not return the same value as unixDateFloor". Implement "FLOOR(date TO timeUnit)" for QUARTER, WEEK, and fix bugs in YEAR, MONTH. --- .../calcite/adapter/enumerable/RexImpTable.java | 11 +++- .../calcite/sql/test/SqlOperatorBaseTest.java | 12 ++++ .../java/org/apache/calcite/test/JdbcTest.java | 36 +++++++---- core/src/test/resources/sql/operator.iq | 69 ++++++++++++++++++++++ 4 files changed, 115 insertions(+), 13 deletions(-) diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index 107b9e9..c82358f 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -2201,6 +2201,7 @@ public class RexImpTable { case 2: final Type type; final Method floorMethod; + final boolean preFloor; Expression operand = translatedOperands.get(0); switch (call.getType().getSqlTypeName()) { case TIMESTAMP_WITH_LOCAL_TIME_ZONE: @@ -2212,19 +2213,25 @@ public class RexImpTable { case TIMESTAMP: type = long.class; floorMethod = timestampMethod; + preFloor = true; break; default: type = int.class; floorMethod = dateMethod; + preFloor = false; } final ConstantExpression tur = (ConstantExpression) translatedOperands.get(1); final TimeUnitRange timeUnitRange = (TimeUnitRange) tur.value; switch (timeUnitRange) { case YEAR: + case QUARTER: case MONTH: - return Expressions.call(floorMethod, tur, - call(operand, type, TimeUnit.DAY)); + case WEEK: + case DAY: + final Expression operand1 = + preFloor ? call(operand, type, TimeUnit.DAY) : operand; + return Expressions.call(floorMethod, tur, operand1); case NANOSECOND: default: return call(operand, type, timeUnitRange.startUnit); 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 98b3975..e16a7af 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 @@ -7908,9 +7908,14 @@ public abstract class SqlOperatorBaseTest { "2015-02-19 12:34:00", "TIMESTAMP(0) NOT NULL"); tester.checkScalar("floor(timestamp '2015-02-19 12:34:56' to year)", "2015-01-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("floor(date '2015-02-19' to year)", + "2015-01-01", "DATE NOT NULL"); tester.checkScalar("floor(timestamp '2015-02-19 12:34:56' to month)", "2015-02-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("floor(date '2015-02-19' to month)", + "2015-02-01", "DATE NOT NULL"); tester.checkNull("floor(cast(null as timestamp) to month)"); + tester.checkNull("floor(cast(null as date) to month)"); } @Test public void testCeilFuncDateTime() { @@ -7944,13 +7949,20 @@ public abstract class SqlOperatorBaseTest { "2015-02-19 12:35:00", "TIMESTAMP(0) NOT NULL"); tester.checkScalar("ceil(timestamp '2015-02-19 12:34:56' to year)", "2016-01-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("ceil(date '2015-02-19' to year)", + "2016-01-01", "DATE NOT NULL"); tester.checkScalar("ceil(timestamp '2015-02-19 12:34:56' to month)", "2015-03-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("ceil(date '2015-02-19' to month)", + "2015-03-01", "DATE NOT NULL"); tester.checkNull("ceil(cast(null as timestamp) to month)"); + tester.checkNull("ceil(cast(null as date) to month)"); // ceiling alias tester.checkScalar("ceiling(timestamp '2015-02-19 12:34:56' to month)", "2015-03-01 00:00:00", "TIMESTAMP(0) NOT NULL"); + tester.checkScalar("ceiling(date '2015-02-19' to month)", + "2015-03-01", "DATE NOT NULL"); tester.checkNull("ceiling(cast(null as timestamp) to month)"); } diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index ed30c6d..af92927 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -6056,7 +6056,7 @@ public class JdbcTest { assertTrue(!rs.next()); } - /** Test for MONTHNAME and DAYNAME functions in two locales. */ + /** Test for MONTHNAME, DAYNAME and DAYOFWEEK functions in two locales. */ @Test public void testMonthName() { final String sql = "SELECT * FROM (VALUES(\n" + " monthname(TIMESTAMP '1969-01-01 00:00:00'),\n" @@ -6066,8 +6066,14 @@ public class JdbcTest { + " dayname(TIMESTAMP '1969-01-01 00:00:00'),\n" + " dayname(DATE '1969-01-01'),\n" + " dayname(DATE '2019-02-10'),\n" - + " dayname(TIMESTAMP '2019-02-10 02:10:12')\n" - + ")) AS t(t0, t1, t2, t3, t4, t5, t6, t7)"; + + " dayname(TIMESTAMP '2019-02-10 02:10:12'),\n" + + " dayofweek(DATE '2019-02-09'),\n" // sat=7 + + " dayofweek(DATE '2019-02-10'),\n" // sun=1 + + " extract(DOW FROM DATE '2019-02-09'),\n" // sat=7 + + " extract(DOW FROM DATE '2019-02-10'),\n" // sun=1 + + " extract(ISODOW FROM DATE '2019-02-09'),\n" // sat=6 + + " extract(ISODOW FROM DATE '2019-02-10')\n" // sun=7 + + ")) AS t(t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13)"; Stream.of(TestLocale.values()).forEach(t -> { try { CalciteAssert.that() @@ -6085,6 +6091,12 @@ public class JdbcTest { assertThat(rs.getString(6), is(t.wednesday)); assertThat(rs.getString(7), is(t.sunday)); assertThat(rs.getString(8), is(t.sunday)); + assertThat(rs.getInt(9), is(7)); + assertThat(rs.getInt(10), is(1)); + assertThat(rs.getInt(11), is(7)); + assertThat(rs.getInt(12), is(1)); + assertThat(rs.getInt(13), is(6)); + assertThat(rs.getInt(14), is(7)); assertThat(rs.next(), is(false)); } } catch (SQLException e) { @@ -7880,15 +7892,15 @@ public class JdbcTest { * and expected results of those functions. */ enum TestLocale { ROOT(Locale.ROOT.toString(), shorten("Wednesday"), shorten("Sunday"), - shorten("January"), shorten("February")), - EN("en", "Wednesday", "Sunday", "January", "February"), - FR("fr", "mercredi", "dimanche", "janvier", "f\u00e9vrier"), - FR_FR("fr_FR", "mercredi", "dimanche", "janvier", "f\u00e9vrier"), - FR_CA("fr_CA", "mercredi", "dimanche", "janvier", "f\u00e9vrier"), + shorten("January"), shorten("February"), 0), + EN("en", "Wednesday", "Sunday", "January", "February", 0), + FR("fr", "mercredi", "dimanche", "janvier", "f\u00e9vrier", 6), + FR_FR("fr_FR", "mercredi", "dimanche", "janvier", "f\u00e9vrier", 6), + FR_CA("fr_CA", "mercredi", "dimanche", "janvier", "f\u00e9vrier", 6), ZH_CN("zh_CN", "\u661f\u671f\u4e09", "\u661f\u671f\u65e5", "\u4e00\u6708", - "\u4e8c\u6708"), + "\u4e8c\u6708", 6), ZH("zh", "\u661f\u671f\u4e09", "\u661f\u671f\u65e5", "\u4e00\u6708", - "\u4e8c\u6708"); + "\u4e8c\u6708", 6); private static String shorten(String name) { // In root locale, for Java versions 9 and higher, day and month names @@ -7902,14 +7914,16 @@ public class JdbcTest { public final String sunday; public final String january; public final String february; + public final int sundayDayOfWeek; TestLocale(String localeName, String wednesday, String sunday, - String january, String february) { + String january, String february, int sundayDayOfWeek) { this.localeName = localeName; this.wednesday = wednesday; this.sunday = sunday; this.january = january; this.february = february; + this.sundayDayOfWeek = sundayDayOfWeek; } } } diff --git a/core/src/test/resources/sql/operator.iq b/core/src/test/resources/sql/operator.iq index 689ab73..2f40480 100644 --- a/core/src/test/resources/sql/operator.iq +++ b/core/src/test/resources/sql/operator.iq @@ -165,4 +165,73 @@ values multiset ['a', 'c', 'a', 'a'] multiset intersect distinct multiset ['a', !ok +# FLOOR and CEIL of DATE +select v, + case when b then 'ceil' else 'floor' end as op, + case when b then ceil(v to year) else floor(v to year) end as y, + case when b then ceil(v to quarter) else floor(v to quarter) end as q, + case when b then ceil(v to month) else floor(v to month) end as m, + case when b then ceil(v to week) else floor(v to week) end as w, + case when b then ceil(v to day) else floor(v to day) end as d +from (values (date '2019-07-05')) as t(v), + (values false, true) as u(b) +order by 1,2; ++------------+-------+------------+------------+------------+------------+------------+ +| V | OP | Y | Q | M | W | D | ++------------+-------+------------+------------+------------+------------+------------+ +| 2019-07-05 | ceil | 2020-01-01 | 2019-10-01 | 2019-08-01 | 2019-07-07 | 2019-07-05 | +| 2019-07-05 | floor | 2019-01-01 | 2019-07-01 | 2019-07-01 | 2019-06-30 | 2019-07-05 | ++------------+-------+------------+------------+------------+------------+------------+ +(2 rows) + +!ok + +# FLOOR and CEIL of TIMESTAMP +select v, + case when b then 'ceil' else 'floor' end as op, + case when b then ceil(v to year) else floor(v to year) end as y, + case when b then ceil(v to quarter) else floor(v to quarter) end as q, + case when b then ceil(v to month) else floor(v to month) end as m, + case when b then ceil(v to week) else floor(v to week) end as w, + case when b then ceil(v to day) else floor(v to day) end as d, + case when b then ceil(v to hour) else floor(v to hour) end as h, + case when b then ceil(v to minute) else floor(v to minute) end as mi, + case when b then ceil(v to second) else floor(v to second) end as s +from (values (timestamp '2019-07-05 12:34:56')) as t(v), + (values false, true) as u(b) +order by 1,2; ++---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ +| V | OP | Y | Q | M | W | D | H | MI | S | ++---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ +| 2019-07-05 12:34:56 | ceil | 2020-01-01 00:00:00 | 2019-10-01 00:00:00 | 2019-08-01 00:00:00 | 2019-07-07 00:00:00 | 2019-07-06 00:00:00 | 2019-07-05 13:00:00 | 2019-07-05 12:35:00 | 2019-07-05 12:34:56 | +| 2019-07-05 12:34:56 | floor | 2019-01-01 00:00:00 | 2019-07-01 00:00:00 | 2019-07-01 00:00:00 | 2019-06-30 00:00:00 | 2019-07-05 00:00:00 | 2019-07-05 12:00:00 | 2019-07-05 12:34:00 | 2019-07-05 12:34:56 | ++---------------------+-------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+ +(2 rows) + +!ok + +# FLOOR and CEIL of TIME +select v, + case when b then 'ceil' else 'floor' end as op, + case when b then ceil(v to year) else floor(v to year) end as y, + case when b then ceil(v to quarter) else floor(v to quarter) end as q, + case when b then ceil(v to month) else floor(v to month) end as m, + case when b then ceil(v to week) else floor(v to week) end as w, + case when b then ceil(v to day) else floor(v to day) end as d, + case when b then ceil(v to hour) else floor(v to hour) end as h, + case when b then ceil(v to minute) else floor(v to minute) end as mi, + case when b then ceil(v to second) else floor(v to second) end as s +from (values (time '12:34:56.7')) as t(v), + (values false, true) as u(b) +order by 1,2; ++----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ +| V | OP | Y | Q | M | W | D | H | MI | S | ++----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ +| 12:34:56 | ceil | 12:34:57 | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 13:00:00 | 12:35:00 | 12:34:57 | +| 12:34:56 | floor | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 12:34:56 | 12:00:00 | 12:34:00 | 12:34:56 | ++----------+-------+----------+----------+----------+----------+----------+----------+----------+----------+ +(2 rows) + +!ok + # End operator.iq