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 d7a0f99eb1 [CALCITE-6311] Support PostgreSQL DATE_PART
d7a0f99eb1 is described below
commit d7a0f99eb1afcced693648ad544dfd9738035f58
Author: Norman Jordan <[email protected]>
AuthorDate: Tue May 21 15:33:20 2024 -0700
[CALCITE-6311] Support PostgreSQL DATE_PART
* PostgreSQL expects the first argument to be a string
* The first argument can be any expression that results in a valid time
unit string
* RedShift and PostgreSQL share the same implementation
---
babel/src/main/codegen/config.fmpp | 1 +
babel/src/main/codegen/includes/parserImpls.ftl | 30 +++++++++++++-
babel/src/test/resources/sql/postgresql.iq | 34 +++++++++++++++
babel/src/test/resources/sql/redshift.iq | 25 +++++++++--
.../calcite/adapter/enumerable/RexImpTable.java | 15 ++++++-
.../org/apache/calcite/runtime/SqlFunctions.java | 12 ++++++
.../apache/calcite/sql/SqlIntervalQualifier.java | 48 ++++++++++++++++++++++
.../apache/calcite/sql/fun/SqlExtractFunction.java | 35 ++++++++++++++--
.../calcite/sql/fun/SqlLibraryOperators.java | 4 +-
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +-
.../org/apache/calcite/util/BuiltInMethod.java | 2 +-
11 files changed, 192 insertions(+), 16 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp
b/babel/src/main/codegen/config.fmpp
index 4e1f9c36d4..bf5895c7c7 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -555,6 +555,7 @@ data: {
# Return type of method implementation should be "SqlNode".
# Example: "DateaddFunctionCall()".
builtinFunctionCallMethods: [
+ "DatePartFunctionCall()",
"DateaddFunctionCall()"
]
diff --git a/babel/src/main/codegen/includes/parserImpls.ftl
b/babel/src/main/codegen/includes/parserImpls.ftl
index b481a29f0f..7565303fa0 100644
--- a/babel/src/main/codegen/includes/parserImpls.ftl
+++ b/babel/src/main/codegen/includes/parserImpls.ftl
@@ -29,6 +29,33 @@ JoinType LeftAntiJoin() :
<LEFT> <ANTI> <JOIN> { return JoinType.LEFT_ANTI_JOIN; }
}
+SqlNode DatePartFunctionCall() :
+{
+ final Span s;
+ final SqlOperator op;
+ final SqlNode unit;
+ final List<SqlNode> args;
+ SqlNode e;
+}
+{
+ <DATE_PART> { op = SqlLibraryOperators.DATE_PART; }
+ { s = span(); }
+ <LPAREN>
+ ( unit = TimeUnitOrName() {
+ args = startList(unit);
+ }
+ | unit = Expression(ExprContext.ACCEPT_NON_QUERY) {
+ args = startList(unit);
+ }
+ )
+ <COMMA> e = Expression(ExprContext.ACCEPT_SUB_QUERY) {
+ args.add(e);
+ }
+ <RPAREN> {
+ return op.createCall(s.end(this), args);
+ }
+}
+
SqlNode DateaddFunctionCall() :
{
final Span s;
@@ -38,8 +65,7 @@ SqlNode DateaddFunctionCall() :
SqlNode e;
}
{
- ( <DATE_PART> { op = SqlLibraryOperators.DATE_PART; }
- | <DATEADD> { op = SqlLibraryOperators.DATEADD; }
+ ( <DATEADD> { op = SqlLibraryOperators.DATEADD; }
| <DATEDIFF> { op = SqlLibraryOperators.DATEDIFF; }
| <DATEPART> { op = SqlLibraryOperators.DATEPART; }
)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index a126c0d5a3..30baac65b7 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -423,6 +423,40 @@ EXPR$0
2022-06-03 18:46:32
!ok
+select date_part('microsecond', timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+48678000
+!ok
+
+select date_part(MICROSECOND, timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+48678000
+!ok
+
+select date_part('minute', timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+15
+!ok
+
+select date_part(MINUTE, timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+15
+!ok
+
+select date_part('dow', timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+6
+!ok
+
+select date_part(DOW, timestamp '2022-06-03 12:15:48.678');
+EXPR$0
+6
+!ok
+
+select date_part('foo', timestamp '2022-06-03 12:15:48.678');
+java.sql.SQLException: Error while executing SQL "select date_part('foo',
timestamp '2022-06-03 12:15:48.678')": From line 1, column 18 to line 1, column
22: 'foo' is not a valid time frame
+!error
+
# -----------------------------------------------------------------------------
# Posix regex
diff --git a/babel/src/test/resources/sql/redshift.iq
b/babel/src/test/resources/sql/redshift.iq
index e0ef58abd1..ca0ed2fb9f 100755
--- a/babel/src/test/resources/sql/redshift.iq
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -1219,23 +1219,40 @@ select date_part(w, timestamp '2008-06-17 09:44:54');
!}
!if (dateAdd) {
-select date_part(w, timestamp '2008-06-17 09:44:54');
+-- returns 8
+select date_part(minute, timestamp '2009-01-01 02:08:01');
+!ok
+!}
+
+!if (dateAdd) {
+select date_part(minute, time '02:08:01');
+!ok
+!}
+
+!if (dateAdd) {
+select date_part(minute, date '2009-01-01');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns 25
+select date_part('w', timestamp '2008-06-17 09:44:54');
!ok
!}
!if (dateAdd) {
-- returns 8
-select date_part(minute, timestamp '2009-01-01 02:08:01');
+select date_part('minute', timestamp '2009-01-01 02:08:01');
!ok
!}
!if (dateAdd) {
-select date_part(minute, time '02:08:01');
+select date_part('minute', time '02:08:01');
!ok
!}
!if (dateAdd) {
-select date_part(minute, date '2009-01-01');
+select date_part('minute', date '2009-01-01');
!ok
!}
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 6d7e9070a8..3485cf2b21 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
@@ -52,6 +52,7 @@ import org.apache.calcite.schema.ImplementableFunction;
import org.apache.calcite.schema.impl.AggregateFunctionImpl;
import org.apache.calcite.sql.SqlAggFunction;
import org.apache.calcite.sql.SqlBinaryOperator;
+import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlJsonConstructorNullClause;
import org.apache.calcite.sql.SqlJsonEmptyOrError;
import org.apache.calcite.sql.SqlJsonValueEmptyOrErrorBehavior;
@@ -3106,8 +3107,18 @@ public class RexImpTable {
@Override Expression implementSafe(final RexToLixTranslator translator,
final RexCall call, final List<Expression> argValueList) {
- final TimeUnitRange timeUnitRange =
- (TimeUnitRange) translator.getLiteralValue(argValueList.get(0));
+ // May need to convert the first argument from a String to a
TimeUnitRange
+ final Object timeUnitRangeObj =
translator.getLiteralValue(argValueList.get(0));
+ final TimeUnitRange timeUnitRange;
+ if (timeUnitRangeObj instanceof String) {
+ timeUnitRange =
+ TimeUnitRange.of(
+ SqlIntervalQualifier.stringToDatePartTimeUnit((String)
timeUnitRangeObj),
+ null);
+ } else {
+ timeUnitRange = (TimeUnitRange) timeUnitRangeObj;
+ }
+
final TimeUnit unit = requireNonNull(timeUnitRange,
"timeUnitRange").startUnit;
Expression operand = argValueList.get(1);
boolean isIntervalType =
SqlTypeUtil.isInterval(call.operands.get(1).getType());
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 90da5db2ee..b594ce6405 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -37,6 +37,7 @@ import org.apache.calcite.linq4j.tree.Primitive;
import org.apache.calcite.rel.type.TimeFrame;
import org.apache.calcite.rel.type.TimeFrameSet;
import org.apache.calcite.runtime.FlatLists.ComparableList;
+import org.apache.calcite.sql.SqlIntervalQualifier;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
import org.apache.calcite.util.NumberUtil;
@@ -3988,6 +3989,17 @@ public class SqlFunctions {
.toString();
}
+ public static long unixDateExtract(TimeUnitRange range, long date) {
+ return DateTimeUtils.unixDateExtract(range, date);
+ }
+
+ public static long unixDateExtract(String rangeString, long date) {
+ final TimeUnitRange timeUnitRange =
+
TimeUnitRange.of(SqlIntervalQualifier.stringToDatePartTimeUnit(rangeString),
+ null);
+ return DateTimeUtils.unixDateExtract(timeUnitRange, date);
+ }
+
/** State for {@code FORMAT_DATE}, {@code FORMAT_TIMESTAMP},
* {@code FORMAT_DATETIME}, {@code FORMAT_TIME}, {@code TO_CHAR} functions.
*/
@Deterministic
diff --git
a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
index b3a29c715d..638f636601 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlIntervalQualifier.java
@@ -35,6 +35,7 @@ import com.google.common.collect.ImmutableSet;
import org.checkerframework.checker.nullness.qual.Nullable;
import java.math.BigDecimal;
+import java.util.Locale;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -445,6 +446,53 @@ public class SqlIntervalQualifier extends SqlNode {
return sign;
}
+ public static TimeUnit stringToDatePartTimeUnit(String stringValue) {
+ final String timeUnitString = stringValue.toUpperCase(Locale.ROOT);
+
+ switch (timeUnitString) {
+ case "MICROSECOND":
+ return TimeUnit.MICROSECOND;
+ case "MILLISECOND":
+ return TimeUnit.MILLISECOND;
+ case "SECOND":
+ return TimeUnit.SECOND;
+ case "MINUTE":
+ return TimeUnit.MINUTE;
+ case "HOUR":
+ return TimeUnit.HOUR;
+ case "DAY":
+ return TimeUnit.DAY;
+ case "DAYOFWEEK":
+ case "DOW":
+ return TimeUnit.DOW;
+ case "DAYOFYEAR":
+ case "DOY":
+ return TimeUnit.DOY;
+ case "ISODOW":
+ return TimeUnit.ISODOW;
+ case "ISODOY":
+ return TimeUnit.ISOYEAR;
+ case "WEEK":
+ return TimeUnit.WEEK;
+ case "MONTH":
+ return TimeUnit.MONTH;
+ case "QUARTER":
+ return TimeUnit.QUARTER;
+ case "YEAR":
+ return TimeUnit.YEAR;
+ case "EPOCH":
+ return TimeUnit.EPOCH;
+ case "DECADE":
+ return TimeUnit.DECADE;
+ case "CENTURY":
+ return TimeUnit.CENTURY;
+ case "MILLENNIUM":
+ return TimeUnit.MILLENNIUM;
+ default:
+ throw new IllegalArgumentException("Date/Time units \"" + stringValue +
"\" not recognized");
+ }
+ }
+
private static String stripLeadingSign(String value) {
String unsignedValue = value;
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
index 1c8a20b0ad..8d2c4eedf4 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlExtractFunction.java
@@ -19,6 +19,7 @@ package org.apache.calcite.sql.fun;
import org.apache.calcite.avatica.util.TimeUnitRange;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCharStringLiteral;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlIntervalQualifier;
@@ -27,6 +28,7 @@ import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlMonotonicity;
import org.apache.calcite.sql.validate.SqlValidator;
@@ -35,6 +37,8 @@ import org.apache.calcite.util.Util;
import com.google.common.collect.ImmutableSet;
+import java.util.Objects;
+
import static
org.apache.calcite.sql.validate.SqlNonNullableAccessors.getOperandLiteralValueOrThrow;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -49,9 +53,12 @@ public class SqlExtractFunction extends SqlFunction {
// SQL2003, Part 2, Section 4.4.3 - extract returns a exact numeric
// TODO: Return type should be decimal for seconds
- public SqlExtractFunction(String name) {
+ public SqlExtractFunction(String name, boolean allowString) {
super(name, SqlKind.EXTRACT, ReturnTypes.BIGINT_NULLABLE, null,
- OperandTypes.INTERVALINTERVAL_INTERVALDATETIME,
+ allowString
+ ? OperandTypes.INTERVALINTERVAL_INTERVALDATETIME
+ .or(OperandTypes.family(SqlTypeFamily.STRING,
SqlTypeFamily.DATETIME))
+ : OperandTypes.INTERVALINTERVAL_INTERVALDATETIME,
SqlFunctionCategory.SYSTEM);
}
@@ -141,7 +148,17 @@ public class SqlExtractFunction extends SqlFunction {
// startUnit = EPOCH and timeFrameName = 'MINUTE15'.
//
// If the latter, check that timeFrameName is valid.
- SqlIntervalQualifier qualifier = call.operand(0);
+ SqlIntervalQualifier qualifier;
+ if (call.operand(0) instanceof SqlCharStringLiteral) {
+ final SqlCharStringLiteral stringLiteral = call.operand(0);
+ qualifier =
+ new SqlIntervalQualifier(
+ Objects.requireNonNull(
+ stringLiteral.toValue()),
+ call.operand(0).getParserPosition());
+ } else {
+ qualifier = call.operand(0);
+ }
validator.validateTimeFrame(qualifier);
TimeUnitRange range = qualifier.timeUnitRange;
@@ -203,7 +220,17 @@ public class SqlExtractFunction extends SqlFunction {
}
@Override public SqlMonotonicity getMonotonicity(SqlOperatorBinding call) {
- TimeUnitRange value = getOperandLiteralValueOrThrow(call, 0,
TimeUnitRange.class);
+ final TimeUnitRange value;
+ if
(SqlTypeName.CHAR_TYPES.contains(call.getOperandType(0).getSqlTypeName())) {
+ value =
+ TimeUnitRange.of(
+ SqlIntervalQualifier.stringToDatePartTimeUnit(
+ Objects.requireNonNull(call.getOperandLiteralValue(0,
String.class))),
+ null);
+ } else {
+ value = getOperandLiteralValueOrThrow(call, 0, TimeUnitRange.class);
+ }
+
switch (value) {
case YEAR:
return call.getOperandMonotonicity(1).unstrict();
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 9960c460a6..c795483c57 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
@@ -188,7 +188,7 @@ public abstract class SqlLibraryOperators {
* (Databricks, Postgres, Redshift, Snowflake). */
@LibraryOperator(libraries = {POSTGRESQL})
public static final SqlFunction DATE_PART =
- new SqlExtractFunction("DATE_PART") {
+ new SqlExtractFunction("DATE_PART", true) {
@Override public void unparse(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
getSyntax().unparse(writer, this, call, leftPrec, rightPrec);
@@ -207,7 +207,7 @@ public abstract class SqlLibraryOperators {
* (Microsoft SQL Server). */
@LibraryOperator(libraries = {MSSQL})
public static final SqlFunction DATEPART =
- new SqlExtractFunction("DATEPART") {
+ new SqlExtractFunction("DATEPART", false) {
@Override public void unparse(SqlWriter writer, SqlCall call,
int leftPrec, int rightPrec) {
getSyntax().unparse(writer, this, call, leftPrec, rightPrec);
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 93f510518b..9d1b1a27ca 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
@@ -2001,7 +2001,7 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
* <code>EXTRACT(HOUR FROM INTERVAL '364 23:59:59')</code> returns <code>
* 23</code>
*/
- public static final SqlFunction EXTRACT = new SqlExtractFunction("EXTRACT");
+ public static final SqlFunction EXTRACT = new SqlExtractFunction("EXTRACT",
false);
/**
* The SQL <code>YEAR</code> operator. Returns the Year
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 40fbc81ef4..07bc45ab5c 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -670,7 +670,7 @@ public enum BuiltInMethod {
"intervalYearMonthToString", int.class, TimeUnitRange.class),
INTERVAL_DAY_TIME_TO_STRING(DateTimeUtils.class, "intervalDayTimeToString",
long.class, TimeUnitRange.class, int.class),
- UNIX_DATE_EXTRACT(DateTimeUtils.class, "unixDateExtract",
+ UNIX_DATE_EXTRACT(SqlFunctions.class, "unixDateExtract",
TimeUnitRange.class, long.class),
UNIX_DATE_FLOOR(DateTimeUtils.class, "unixDateFloor",
TimeUnitRange.class, int.class),
