You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by se...@apache.org on 2019/03/25 20:08:43 UTC
[calcite] branch master updated: [CALCITE-2908] Implement SQL
LAST_DAY function (Chunwei Lei)
This is an automated email from the ASF dual-hosted git repository.
sereda 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 25ffeb4 [CALCITE-2908] Implement SQL LAST_DAY function (Chunwei Lei)
25ffeb4 is described below
commit 25ffeb4ac9c996855f8a946f6180dc01844f80dc
Author: Chunwei Lei <ch...@alibaba-inc.com>
AuthorDate: Mon Mar 11 14:10:49 2019 +0800
[CALCITE-2908] Implement SQL LAST_DAY function (Chunwei Lei)
---
.../calcite/adapter/enumerable/RexImpTable.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 27 ++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 9 +++
.../org/apache/calcite/sql/type/ReturnTypes.java | 8 +++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 77 ++++++++++++++++++++++
core/src/test/resources/sql/misc.iq | 21 ++++++
site/_docs/reference.md | 1 +
8 files changed, 147 insertions(+)
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 9554f80..e5679b2 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
@@ -175,6 +175,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_TYPE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_VALUE_ANY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_VALUE_EXPRESSION;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.LAG;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.LAST_DAY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.LAST_VALUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.LEAD;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.LESS_THAN;
@@ -375,6 +376,8 @@ public class RexImpTable {
BuiltInMethod.UNIX_TIMESTAMP_CEIL.method,
BuiltInMethod.UNIX_DATE_CEIL.method), false);
+ defineMethod(LAST_DAY, "lastDay", NullPolicy.STRICT);
+
map.put(IS_NULL, new IsXxxImplementor(null, false));
map.put(IS_NOT_NULL, new IsXxxImplementor(null, true));
map.put(IS_TRUE, new IsXxxImplementor(true, false));
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 5e9d6df..50079cb 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1930,6 +1930,33 @@ public class SqlFunctions {
return v - remainder;
}
+ /**
+ * SQL {@code LAST_DAY} function.
+ *
+ * @param date days since epoch
+ * @return days of the last day of the month since epoch
+ */
+ public static int lastDay(int date) {
+ int y0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date);
+ int m0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date);
+ int last = lastDay(y0, m0);
+ return DateTimeUtils.ymdToUnixDate(y0, m0, last);
+ }
+
+ /**
+ * SQL {@code LAST_DAY} function.
+ *
+ * @param timestamp milliseconds from epoch
+ * @return milliseconds of the last day of the month since epoch
+ */
+ public static int lastDay(long timestamp) {
+ int date = (int) (timestamp / DateTimeUtils.MILLIS_PER_DAY);
+ int y0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.YEAR, date);
+ int m0 = (int) DateTimeUtils.unixDateExtract(TimeUnitRange.MONTH, date);
+ int last = lastDay(y0, m0);
+ return DateTimeUtils.ymdToUnixDate(y0, m0, last);
+ }
+
/** SQL {@code CURRENT_TIMESTAMP} function. */
@NonDeterministic
public static long currentTimestamp(DataContext root) {
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index f514ccd..50e6eb1 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1966,6 +1966,15 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
public static final SqlDatePartFunction SECOND =
new SqlDatePartFunction("SECOND", TimeUnit.SECOND);
+ public static final SqlFunction LAST_DAY =
+ new SqlFunction(
+ "LAST_DAY",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.DATE_NULLABLE,
+ null,
+ OperandTypes.DATETIME,
+ SqlFunctionCategory.TIMEDATE);
+
/**
* The ELEMENT operator, used to convert a multiset with only one item to a
* "regular" type. Example ... log(ELEMENT(MULTISET[1])) ...
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index fddcdb5..a700a11 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -220,6 +220,14 @@ public abstract class ReturnTypes {
*/
public static final SqlReturnTypeInference DATE =
explicit(SqlTypeName.DATE);
+
+ /**
+ * Type-inference strategy whereby the result type of a call is nullable
+ * Date.
+ */
+ public static final SqlReturnTypeInference DATE_NULLABLE =
+ cascade(DATE, SqlTypeTransforms.TO_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is Time(0).
*/
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 9a67e16..962c3bf 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -392,6 +392,7 @@ public enum BuiltInMethod {
TimeUnitRange.class, long.class),
UNIX_TIMESTAMP_CEIL(DateTimeUtils.class, "unixTimestampCeil",
TimeUnitRange.class, long.class),
+ LAST_DAY(SqlFunctions.class, "lastDay", int.class),
CURRENT_TIMESTAMP(SqlFunctions.class, "currentTimestamp", DataContext.class),
CURRENT_TIME(SqlFunctions.class, "currentTime", DataContext.class),
CURRENT_DATE(SqlFunctions.class, "currentDate", DataContext.class),
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 fcf7fe9..cff7f36 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
@@ -5590,6 +5590,83 @@ public abstract class SqlOperatorBaseTest {
}
}
+ @Test public void testLastDayFunc() {
+ tester.setFor(SqlStdOperatorTable.LAST_DAY);
+ tester.checkScalar("last_day(DATE '2019-02-10')",
+ "2019-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-06-10')",
+ "2019-06-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-07-10')",
+ "2019-07-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-09-10')",
+ "2019-09-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-12-10')",
+ "2019-12-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '9999-12-10')",
+ "9999-12-31", "DATE NOT NULL");
+
+ // Edge tests
+ tester.checkScalar("last_day(DATE '1900-01-01')",
+ "1900-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '1935-02-01')",
+ "1935-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '1965-09-01')",
+ "1965-09-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '1970-01-01')",
+ "1970-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-02-28')",
+ "2019-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-12-31')",
+ "2019-12-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-01-01')",
+ "2019-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2019-06-30')",
+ "2019-06-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2020-02-20')",
+ "2020-02-29", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '2020-02-29')",
+ "2020-02-29", "DATE NOT NULL");
+ tester.checkScalar("last_day(DATE '9999-12-31')",
+ "9999-12-31", "DATE NOT NULL");
+
+ tester.checkNull("last_day(cast(null as date))");
+
+ tester.checkScalar("last_day(TIMESTAMP '2019-02-10 02:10:12')",
+ "2019-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-06-10 06:10:16')",
+ "2019-06-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-07-10 07:10:17')",
+ "2019-07-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-09-10 09:10:19')",
+ "2019-09-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-12-10 12:10:22')",
+ "2019-12-31", "DATE NOT NULL");
+
+ // Edge tests
+ tester.checkScalar("last_day(TIMESTAMP '1900-01-01 01:01:02')",
+ "1900-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '1935-02-01 02:01:03')",
+ "1935-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '1970-01-01 01:01:02')",
+ "1970-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-02-28 02:28:30')",
+ "2019-02-28", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-12-31 12:31:43')",
+ "2019-12-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-01-01 01:01:02')",
+ "2019-01-31", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2019-06-30 06:30:36')",
+ "2019-06-30", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2020-02-20 02:20:33')",
+ "2020-02-29", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '2020-02-29 02:29:31')",
+ "2020-02-29", "DATE NOT NULL");
+ tester.checkScalar("last_day(TIMESTAMP '9999-12-31 12:31:43')",
+ "9999-12-31", "DATE NOT NULL");
+
+ tester.checkNull("last_day(cast(null as timestamp))");
+ }
+
@Test public void testSubstringFunction() {
tester.setFor(SqlStdOperatorTable.SUBSTRING);
tester.checkString(
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index c692f42..e0732af 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2219,4 +2219,25 @@ EXPR$0
true
!ok
+# [CALCITE-2908] Implement SQL LAST_DAY function
+with data(c_date, c_timestamp) as (select * from (values
+ (DATE'1965-01-10', TIMESTAMP '1965-01-10 20:20:20'),
+ (DATE'2019-01-01', TIMESTAMP '2019-01-01 18:19:20'),
+ (DATE'2019-02-20', TIMESTAMP '2019-02-20 00:00:00'),
+ (DATE'2019-02-28', TIMESTAMP '2019-02-28 03:04:05'),
+ (DATE'2019-03-02', TIMESTAMP '2019-03-02 15:10:05'),
+ (DATE'2019-06-28', TIMESTAMP '2019-06-28 17:32:01'),
+ (DATE'2019-12-12', TIMESTAMP '2019-12-12 12:12:01')))
+select last_day(c_date), last_day(c_timestamp) from data;
+
+EXPR$0, EXPR$1
+1965-01-31, 1965-01-31
+2019-01-31, 2019-01-31
+2019-02-28, 2019-02-28
+2019-02-28, 2019-02-28
+2019-03-31, 2019-03-31
+2019-06-30, 2019-06-30
+2019-12-31, 2019-12-31
+!ok
+
# End misc.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e7e9282..591a54b 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1236,6 +1236,7 @@ Not implemented:
| SECOND(date) | Equivalent to `EXTRACT(SECOND FROM date)`. Returns an integer between 0 and 59.
| TIMESTAMPADD(timeUnit, integer, datetime) | Returns *datetime* with an interval of (signed) *integer* *timeUnit*s added. Equivalent to `datetime + INTERVAL 'integer' timeUnit`
| TIMESTAMPDIFF(timeUnit, datetime, datetime2) | Returns the (signed) number of *timeUnit* intervals between *datetime* and *datetime2*. Equivalent to `(datetime2 - datetime) timeUnit`
+| LAST_DAY(date) | Returns the date of the last day of the month in a value of datatype DATE; For example, it returns DATE'2020-02-29' for both DATE'2020-02-10' and TIMESTAMP'2020-02-10 10:10:10'
Calls to niladic functions such as `CURRENT_DATE` do not accept parentheses in
standard SQL. Calls with parentheses, such as `CURRENT_DATE()` are accepted in certain