You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2020/03/09 19:06:11 UTC
[calcite] 02/03: [CALCITE-3412] FLOOR(timestamp TO WEEK) gives
wrong result
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