This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 7fb0fc26bd [CALCITE-6666] Support Oracle SYSDATE and SYSTIMESTAMP
functions
7fb0fc26bd is described below
commit 7fb0fc26bdd067a1ce292cfe1642961fa2d1dc41
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Sat Nov 2 22:09:45 2024 +0800
[CALCITE-6666] Support Oracle SYSDATE and SYSTIMESTAMP functions
---
.../main/java/org/apache/calcite/DataContext.java | 5 ++
.../calcite/adapter/enumerable/RexImpTable.java | 8 ++++
.../apache/calcite/jdbc/CalciteConnectionImpl.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 24 ++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 18 ++++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
.../org/apache/calcite/test/SqlValidatorTest.java | 44 ++++++++++++++++++
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 53 ++++++++++++++++++++++
9 files changed, 158 insertions(+)
diff --git a/core/src/main/java/org/apache/calcite/DataContext.java
b/core/src/main/java/org/apache/calcite/DataContext.java
index ed784094bc..eeca324268 100644
--- a/core/src/main/java/org/apache/calcite/DataContext.java
+++ b/core/src/main/java/org/apache/calcite/DataContext.java
@@ -82,6 +82,11 @@ public interface DataContext {
* statement. Required. */
LOCAL_TIMESTAMP("localTimestamp", Long.class),
+ /** The time in the operating system time zone of the database server. In
+ * milliseconds after 1970-01-01 00:00:00, in the time zone of the database
+ * server. Required. */
+ SYS_TIMESTAMP("sysTimestamp", Long.class),
+
/** The Spark engine. Available if Spark is on the class path. */
SPARK_CONTEXT("sparkContext", Object.class),
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 21bca972c4..20680a67b5 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
@@ -299,6 +299,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.STARTS_WITH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STRCMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.STR_TO_MAP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SUBSTRING_INDEX;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SYSDATE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.SYSTIMESTAMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TAND;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TANH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TIME;
@@ -1212,6 +1214,8 @@ public class RexImpTable {
define(CURRENT_DATETIME, systemFunctionImplementor);
define(LOCALTIME, systemFunctionImplementor);
define(LOCALTIMESTAMP, systemFunctionImplementor);
+ define(SYSDATE, systemFunctionImplementor);
+ define(SYSTIMESTAMP, systemFunctionImplementor);
defineAgg(COUNT, CountImplementor.class);
defineAgg(REGR_COUNT, CountImplementor.class);
@@ -3804,6 +3808,10 @@ public class RexImpTable {
return Expressions.call(BuiltInMethod.LOCAL_TIMESTAMP.method, root);
} else if (op == LOCALTIME) {
return Expressions.call(BuiltInMethod.LOCAL_TIME.method, root);
+ } else if (op == SYSDATE) {
+ return Expressions.call(BuiltInMethod.SYSDATE.method, root);
+ } else if (op == SYSTIMESTAMP) {
+ return Expressions.call(BuiltInMethod.SYSTIMESTAMP.method, root);
} else {
throw new AssertionError("unknown function " + op);
}
diff --git
a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
index 073b425657..7b69e2c3ed 100644
--- a/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
+++ b/core/src/main/java/org/apache/calcite/jdbc/CalciteConnectionImpl.java
@@ -427,6 +427,7 @@ abstract class CalciteConnectionImpl
.deriveTimeFrameSet(TimeFrames.CORE);
final long localOffset = timeZone.getOffset(time);
final long currentOffset = localOffset;
+ final long sysOffset = TimeZone.getDefault().getOffset(time);
final String user = "sa";
final String systemUser = System.getProperty("user.name");
final String localeName = connection.config().locale();
@@ -442,6 +443,7 @@ abstract class CalciteConnectionImpl
builder.put(Variable.UTC_TIMESTAMP.camelName, time)
.put(Variable.CURRENT_TIMESTAMP.camelName, time + currentOffset)
.put(Variable.LOCAL_TIMESTAMP.camelName, time + localOffset)
+ .put(Variable.SYS_TIMESTAMP.camelName, time + sysOffset)
.put(Variable.TIME_ZONE.camelName, timeZone)
.put(Variable.TIME_FRAME_SET.camelName, timeFrameSet)
.put(Variable.USER.camelName, user)
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 c9e7eb7c76..980ed5e0fe 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -5387,6 +5387,30 @@ public class SqlFunctions {
return timestampToTime(localTimestamp(root));
}
+ /** SQL {@code SYSTIMESTAMP} function. */
+ @NonDeterministic
+ public static long sysTimestamp(DataContext root) {
+ return DataContext.Variable.SYS_TIMESTAMP.get(root);
+ }
+
+ /** SQL {@code SYSDATE} function.
+ *
+ * <p> When the date is before 1970-01-01 00:00:00, for example: 1969-12-31
23:59:59,
+ * the timestamp will return a negative value, such as -1000. The date(days
since epoch)
+ * returned by timestampToDate(-1000) is 0, so we need to additionally judge
the result
+ * of timestampToTime(-1000). If its value is less than 0, we need to reduce
date by 1
+ * to ensure the accuracy of date. */
+ @NonDeterministic
+ public static int sysDate(DataContext root) {
+ final long timestamp = sysTimestamp(root);
+ int date = timestampToDate(timestamp);
+ final int time = timestampToTime(timestamp);
+ if (time < 0) {
+ --date;
+ }
+ return date;
+ }
+
@NonDeterministic
public static TimeZone timeZone(DataContext root) {
return DataContext.Variable.TIME_ZONE.get(root);
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 8b16906bd8..f74038718f 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -1023,6 +1023,24 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.TIMEDATE)
.withSyntax(SqlSyntax.FUNCTION_ID);
+ /** The "SYSDATE" function. */
+ @LibraryOperator(libraries = {ORACLE})
+ public static final SqlFunction SYSDATE =
+ SqlBasicFunction.create("SYSDATE",
+ ReturnTypes.DATE,
+ OperandTypes.NILADIC,
+ SqlFunctionCategory.TIMEDATE)
+ .withSyntax(SqlSyntax.FUNCTION_ID);
+
+ /** The "SYSTIMESTAMP" function. */
+ @LibraryOperator(libraries = {ORACLE})
+ public static final SqlFunction SYSTIMESTAMP =
+ SqlBasicFunction.create("SYSTIMESTAMP",
+ ReturnTypes.TIMESTAMP_TZ,
+ OperandTypes.NILADIC,
+ SqlFunctionCategory.TIMEDATE)
+ .withSyntax(SqlSyntax.FUNCTION_ID);
+
/** The "DATE_FROM_UNIX_DATE(integer)" function; returns a DATE value
* a given number of seconds after 1970-01-01. */
@LibraryOperator(libraries = {BIG_QUERY, SPARK})
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 2bad29df0f..6236e0e556 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -778,6 +778,8 @@ public enum BuiltInMethod {
String.class),
LOCAL_TIMESTAMP(SqlFunctions.class, "localTimestamp", DataContext.class),
LOCAL_TIME(SqlFunctions.class, "localTime", DataContext.class),
+ SYSDATE(SqlFunctions.class, "sysDate", DataContext.class),
+ SYSTIMESTAMP(SqlFunctions.class, "sysTimestamp", DataContext.class),
TIME_ZONE(SqlFunctions.class, "timeZone", DataContext.class),
USER(SqlFunctions.class, "user", DataContext.class),
SYSTEM_USER(SqlFunctions.class, "systemUser", DataContext.class),
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 8d2fd0f28f..5a841089f6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1658,6 +1658,50 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.withOperatorTable(opTable).ok();
}
+ @Test void testSysDateFunction() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.ORACLE);
+ // test oracle sysdate function validate
+ expr("SYSDATE")
+ .withOperatorTable(opTable)
+ .columnType("DATE NOT NULL");
+ expr("^SYSDATE^")
+ .fails("Column 'SYSDATE' not found in any table");
+ expr("^SYSDATE()^")
+ .withOperatorTable(opTable)
+ .fails("No match found for function signature SYSDATE..");
+ // test oracle sysdate function validate within to_char function
+ expr("TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ expr("TO_CHAR(^SYSDATE^, 'MM-DD-YYYY HH24:MI:SS')")
+ .fails("Column 'SYSDATE' not found in any table");
+ expr("^TO_CHAR(SYSDATE)^")
+ .withOperatorTable(opTable)
+ .fails("Invalid number of arguments to function 'TO_CHAR'. Was
expecting 2 arguments");
+ }
+
+ @Test void testSysTimestampFunction() {
+ final SqlOperatorTable opTable = operatorTableFor(SqlLibrary.ORACLE);
+ // test oracle systimestamp function validate
+ expr("SYSTIMESTAMP")
+ .withOperatorTable(opTable)
+ .columnType("TIMESTAMP_TZ(0) NOT NULL");
+ expr("^SYSTIMESTAMP^")
+ .fails("Column 'SYSTIMESTAMP' not found in any table");
+ expr("^SYSTIMESTAMP()^")
+ .withOperatorTable(opTable)
+ .fails("No match found for function signature SYSTIMESTAMP..");
+ // test oracle systimestamp function validate within to_char function
+ expr("TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF')")
+ .withOperatorTable(opTable)
+ .columnType("VARCHAR NOT NULL");
+ expr("TO_CHAR(^SYSTIMESTAMP^, 'SSSSS.FF')")
+ .fails("Column 'SYSTIMESTAMP' not found in any table");
+ expr("^TO_CHAR(SYSTIMESTAMP)^")
+ .withOperatorTable(opTable)
+ .fails("Invalid number of arguments to function 'TO_CHAR'. Was
expecting 2 arguments");
+ }
+
@Test void testInvalidFunction() {
wholeExpr("foo()")
.fails("No match found for function signature FOO..");
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index d26e4710dd..6a5abd3672 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2933,6 +2933,8 @@ In the following:
| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller than the first one
| b r p | STRPOS(string, substring) | Equivalent to
`POSITION(substring IN string)`
| b m o p r | SUBSTR(string, position [, substringLength ]) | Returns a
portion of *string*, beginning at character *position*, *substringLength*
characters long. SUBSTR calculates lengths using characters as defined by the
input character set
+| o | SYSDATE | Returns the current
date in the operating system time zone of the database server, in a value of
datatype DATE.
+| o | SYSTIMESTAMP | Returns the current
date and time in the operating system time zone of the database server, in a
value of datatype TIMESTAMP WITH TIME ZONE.
| p | TAND(numeric) | Returns the tangent of
*numeric* in degrees as a double. Returns NaN if *numeric* is NaN. Fails if
*numeric is greater than the maximum double value.
| * | TANH(numeric) | Returns the hyperbolic
tangent of *numeric*
| b | TIME(hour, minute, second) | Returns a TIME value
*hour*, *minute*, *second* (all of type INTEGER)
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 6d46affa08..66ba88520e 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -10434,6 +10434,32 @@ public class SqlOperatorTest {
pair.right.close();
}
+ @Tag("slow")
+ @Test void testSysTimestampFuncWithCurrentTime() {
+ testSysTimestampFunc(currentTimeString(CURRENT_TZ));
+ }
+
+ @Test void testSysTimestampFuncWithFixedTime() {
+ testSysTimestampFunc(fixedTimeString(CURRENT_TZ));
+ }
+
+ private void testSysTimestampFunc(Pair<String, Hook.Closeable> pair) {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.SYSTIMESTAMP, VmName.EXPAND)
+ .withLibrary(SqlLibrary.ORACLE);
+
+ f.checkType("SYSTIMESTAMP", "TIMESTAMP_TZ(0) NOT NULL");
+ f.checkFails("^SYSTIMESTAMP()^",
+ "No match found for function signature SYSTIMESTAMP\\(\\)",
+ false);
+ // check systimestamp function within cast function
+ f.checkType("CAST(SYSTIMESTAMP AS VARCHAR(30))", "VARCHAR(30) NOT NULL");
+ f.checkFails("CAST(^SYSTIMESTAMP()^ AS VARCHAR(30))",
+ "No match found for function signature SYSTIMESTAMP\\(\\)",
+ false);
+ pair.right.close();
+ }
+
/**
* Returns a time string, in GMT, that will be valid for at least 2 minutes.
*
@@ -10524,6 +10550,33 @@ public class SqlOperatorTest {
}
}
+ @Tag("slow")
+ @Test void testSysDateFuncWithCurrentTime() {
+ testSysDateFunc(currentTimeString(LOCAL_TZ));
+ }
+
+ @Test void testSysDateFuncWithFixedTime() {
+ testSysDateFunc(fixedTimeString(LOCAL_TZ));
+ }
+
+ private void testSysDateFunc(Pair<String, Hook.Closeable> pair) {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.SYSDATE)
+ .withLibrary(SqlLibrary.ORACLE);
+
+ f.checkType("SYSDATE", "DATE NOT NULL");
+ f.checkType("(SYSDATE - SYSDATE) DAY", "INTERVAL DAY NOT NULL");
+ f.checkFails("^SYSDATE()^",
+ "No match found for function signature SYSDATE\\(\\)",
+ false);
+ // check sysdate function within cast function
+ f.checkType("CAST(SYSDATE AS VARCHAR(30))", "VARCHAR(30) NOT NULL");
+ f.checkFails("CAST(^SYSDATE()^ AS VARCHAR(30))",
+ "No match found for function signature SYSDATE\\(\\)",
+ false);
+ pair.right.close();
+ }
+
@Test void testLastDayFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.LAST_DAY, VmName.EXPAND);
