You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by da...@apache.org on 2020/04/15 09:40:37 UTC
[calcite] branch master updated: [CALCITE-3881]
SqlFunctions#addMonths yields incorrect results in some corner case
(Zhenghua Gao)
This is an automated email from the ASF dual-hosted git repository.
danny0405 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 43261e4 [CALCITE-3881] SqlFunctions#addMonths yields incorrect results in some corner case (Zhenghua Gao)
43261e4 is described below
commit 43261e4094a37ce23eb181a6a8f653dabc4db599
Author: Zhenghua Gao <do...@gmail.com>
AuthorDate: Mon Mar 30 16:26:38 2020 +0800
[CALCITE-3881] SqlFunctions#addMonths yields incorrect results in some corner case (Zhenghua Gao)
SqlFunctions#addMonths use DateTimeUtils#ymdToUnixDate to calculate the
JDN(julian day number). But in some corner cases it yields incorrent
results. The root cause is: the algorithm of DateTimeUtils#ymdToUnixDate
requires reasonable month(1 to 12)[1], but SqlFunctions#addMonths may
pass in a month out of the reasonable range. This PR will fix it.
close apache/calcite#1890
---
.../java/org/apache/calcite/runtime/SqlFunctions.java | 12 +++++++++---
.../apache/calcite/sql/test/SqlOperatorBaseTest.java | 17 +++++++++++++++++
.../java/org/apache/calcite/test/SqlFunctionsTest.java | 2 ++
3 files changed, 28 insertions(+), 3 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 43934f3..4b89963 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2702,9 +2702,15 @@ public class SqlFunctions {
int y0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date);
int m0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date);
int d0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.DAY, date);
- int y = m / 12;
- y0 += y;
- m0 += m - y * 12;
+ m0 += m;
+ int deltaYear = (int) DateTimeUtils.floorDiv(m0, 12);
+ y0 += deltaYear;
+ m0 = (int) DateTimeUtils.floorMod(m0, 12);
+ if (m0 == 0) {
+ y0 -= 1;
+ m0 += 12;
+ }
+
int last = lastDay(y0, m0);
if (d0 > last) {
d0 = last;
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 638682f..b631878 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
@@ -1990,6 +1990,9 @@ public abstract class SqlOperatorBaseTest {
tester.checkScalar("{fn TIMESTAMPDIFF(HOUR,"
+ " TIMESTAMP '2014-03-29 12:34:56',"
+ " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL");
+ tester.checkScalar("{fn TIMESTAMPDIFF(MONTH,"
+ + " TIMESTAMP '2019-09-01 00:00:00',"
+ + " TIMESTAMP '2020-03-01 00:00:00')}", "6", "INTEGER NOT NULL");
if (Bug.CALCITE_2539_FIXED) {
tester.checkFails("{fn WEEK(DATE '2014-12-10')}",
@@ -8283,6 +8286,14 @@ public abstract class SqlOperatorBaseTest {
+ "timestamp '2014-02-24 12:42:25', "
+ "timestamp '2016-02-24 12:42:25')",
"24", "INTEGER NOT NULL");
+ tester.checkScalar("timestampdiff(MONTH, "
+ + "timestamp '2019-09-01 00:00:00', "
+ + "timestamp '2020-03-01 00:00:00')",
+ "6", "INTEGER NOT NULL");
+ tester.checkScalar("timestampdiff(MONTH, "
+ + "timestamp '2019-09-01 00:00:00', "
+ + "timestamp '2016-08-01 00:00:00')",
+ "-37", "INTEGER NOT NULL");
tester.checkScalar("timestampdiff(QUARTER, "
+ "timestamp '2014-02-24 12:42:25', "
+ "timestamp '2016-02-24 12:42:25')",
@@ -8305,6 +8316,12 @@ public abstract class SqlOperatorBaseTest {
"timestampdiff(MONTH, date '2016-03-15', date '2016-06-14')",
"2",
"INTEGER NOT NULL");
+ tester.checkScalar("timestampdiff(MONTH, date '2019-09-01', date '2020-03-01')",
+ "6",
+ "INTEGER NOT NULL");
+ tester.checkScalar("timestampdiff(MONTH, date '2019-09-01', date '2016-08-01')",
+ "-37",
+ "INTEGER NOT NULL");
tester.checkScalar(
"timestampdiff(DAY, date '2016-06-15', date '2016-06-14')",
"-1",
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 75779b1..bb494d4 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -298,6 +298,8 @@ class SqlFunctionsTest {
checkAddMonths(2016, 3, 31, 2016, 2, 29, -1);
checkAddMonths(2016, 3, 31, 2116, 3, 31, 1200);
checkAddMonths(2016, 2, 28, 2116, 2, 28, 1200);
+ checkAddMonths(2019, 9, 1, 2020, 3, 1, 6);
+ checkAddMonths(2019, 9, 1, 2016, 8, 1, -37);
}
private void checkAddMonths(int y0, int m0, int d0, int y1, int m1, int d1,