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 ed4d9b3b99 [CALCITE-6664] Replace GREATEST, LEAST functions in Spark
library with the implementation of PostgreSQL Library
ed4d9b3b99 is described below
commit ed4d9b3b99c2fc911db80ab0ec4893d4d6118d4f
Author: joeyutong <[email protected]>
AuthorDate: Fri Dec 13 15:31:08 2024 +0800
[CALCITE-6664] Replace GREATEST, LEAST functions in Spark library with the
implementation of PostgreSQL Library
---
babel/src/test/resources/sql/spark.iq | 30 ++++++++++++++++++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 6 ++---
.../calcite/sql/fun/SqlLibraryOperators.java | 14 +++++-----
.../org/apache/calcite/test/SqlOperatorTest.java | 10 +++++---
4 files changed, 47 insertions(+), 13 deletions(-)
diff --git a/babel/src/test/resources/sql/spark.iq
b/babel/src/test/resources/sql/spark.iq
index ec4f471e5d..5f0927961f 100644
--- a/babel/src/test/resources/sql/spark.iq
+++ b/babel/src/test/resources/sql/spark.iq
@@ -667,4 +667,34 @@ SELECT KEY, "set" IS NULL AS N FROM COMPLEX;
!ok
+SELECT greatest(1, null, 3) AS x;
++---+
+| X |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+SELECT least(1, 2, null, 3) AS x;
++---+
+| X |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+
+SELECT greatest(null, null) AS x;
++---+
+| X |
++---+
+| |
++---+
+(1 row)
+
+!ok
+
# End spark.iq
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 0994306450..5457f205b1 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -475,10 +475,10 @@ public enum SqlKind {
/** {@code NVL2} function (Oracle, Spark). */
NVL2,
- /** {@code GREATEST} function (Oracle, Spark). */
+ /** {@code GREATEST} function (Oracle). */
GREATEST,
- /** {@code GREATEST} function (PostgreSQL). */
+ /** {@code GREATEST} function (PostgreSQL, Spark). */
GREATEST_PG,
/** The two-argument {@code CONCAT} function (Oracle). */
@@ -503,7 +503,7 @@ public enum SqlKind {
/** {@code LEAST} function (Oracle). */
LEAST,
- /** {@code LEAST} function (PostgreSQL). */
+ /** {@code LEAST} function (PostgreSQL, Spark). */
LEAST_PG,
/** {@code LOG} function. (Mysql, Spark). */
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 322224c9ed..b0173db832 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
@@ -480,28 +480,30 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
public static final SqlFunction GREATEST =
SqlBasicFunction.create(SqlKind.GREATEST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.SAME_VARIADIC);
- /** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ /** The "GREATEST(value, value)" function. Identical to the standard
<code>GREATEST</code>
+ * function except it skips null values and only returns null if all
parameters are nulls. */
+ @LibraryOperator(libraries = {POSTGRESQL, SPARK})
public static final SqlFunction GREATEST_PG =
SqlBasicFunction.create("GREATEST", SqlKind.GREATEST_PG,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.SAME_VARIADIC);
/** The "LEAST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE})
public static final SqlFunction LEAST =
SqlBasicFunction.create(SqlKind.LEAST,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
OperandTypes.SAME_VARIADIC);
- /** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {POSTGRESQL})
+ /** The "LEAST(value, value)" function. Identical to the standard
<code>LEAST</code>
+ * function except it skips null values and only returns null if all
parameters are nulls. */
+ @LibraryOperator(libraries = {POSTGRESQL, SPARK})
public static final SqlFunction LEAST_PG =
SqlBasicFunction.create("LEAST", SqlKind.LEAST_PG,
ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
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 0bb72cff54..9e10c67b67 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -11728,7 +11728,7 @@ public class SqlOperatorTest {
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.SPARK);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11750,7 +11750,8 @@ public class SqlOperatorTest {
f.checkScalar("greatest(CAST(NULL AS INTEGER), CAST(NULL AS INTEGER))",
isNullValue(),
"INTEGER");
};
- final List<SqlLibrary> libraries = list(SqlLibrary.POSTGRESQL,
SqlLibrary.REDSHIFT);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
f0.forEachLibrary(libraries, consumer);
}
@@ -11774,7 +11775,7 @@ public class SqlOperatorTest {
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.SPARK);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE);
f0.forEachLibrary(libraries, consumer);
}
@@ -11796,7 +11797,8 @@ public class SqlOperatorTest {
f.checkScalar("least(CAST(NULL AS INTEGER), CAST(NULL AS INTEGER))",
isNullValue(),
"INTEGER");
};
- final List<SqlLibrary> libraries = list(SqlLibrary.POSTGRESQL,
SqlLibrary.REDSHIFT);
+ final List<SqlLibrary> libraries =
+ list(SqlLibrary.POSTGRESQL, SqlLibrary.REDSHIFT, SqlLibrary.SPARK);
f0.forEachLibrary(libraries, consumer);
}
