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 dad90736b0 [CALCITE-5634] Enable GREATEST, LEAST functions in
PostgreSQL library
dad90736b0 is described below
commit dad90736b0dbf30eae0d8f215112e22f64c90235
Author: Norman Jordan <[email protected]>
AuthorDate: Fri Jun 21 15:35:33 2024 -0700
[CALCITE-5634] Enable GREATEST, LEAST functions in PostgreSQL library
* Add new GREATEST and LEAST functions for PostgreSQL
* The new functions only return null if all arguments are null
---
babel/src/test/resources/sql/postgresql.iq | 15 ++++++
.../org/apache/calcite/sql/SqlBasicFunction.java | 18 +++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 6 +++
.../calcite/sql/fun/SqlLibraryOperators.java | 18 ++++++-
.../calcite/sql2rel/StandardConvertletTable.java | 56 ++++++++++++++++++++++
site/_docs/reference.md | 4 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 50 +++++++++++++++++--
7 files changed, 159 insertions(+), 8 deletions(-)
diff --git a/babel/src/test/resources/sql/postgresql.iq
b/babel/src/test/resources/sql/postgresql.iq
index 30baac65b7..492c6a4d51 100644
--- a/babel/src/test/resources/sql/postgresql.iq
+++ b/babel/src/test/resources/sql/postgresql.iq
@@ -506,4 +506,19 @@ X
true
!ok
+SELECT greatest(1, 2, 3) AS x;
+X
+3
+!ok
+
+SELECT greatest(1, null, 3) AS x;
+X
+3
+!ok
+
+SELECT least(1, 2, 3) AS x;
+X
+1
+!ok
+
# End postgresql.iq
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlBasicFunction.java
b/core/src/main/java/org/apache/calcite/sql/SqlBasicFunction.java
index 44ca45744d..05c1f80c0b 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlBasicFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlBasicFunction.java
@@ -89,6 +89,24 @@ public class SqlBasicFunction extends SqlFunction {
this.dynamic = dynamic;
}
+ /**
+ * Creates a {@code SqlBasicFunction}.
+ *
+ * @param name function name
+ * @param kind function kind
+ * @param returnTypeInference Strategy to use for return type inference
+ * @param operandTypeChecker Strategy to use for parameter type checking
+ * @return a {@code SqlBasicFunction}
+ */
+ public static SqlBasicFunction create(String name, SqlKind kind,
+ SqlReturnTypeInference returnTypeInference,
+ SqlOperandTypeChecker operandTypeChecker) {
+ return new SqlBasicFunction(name, kind,
+ SqlSyntax.FUNCTION, true, returnTypeInference, null,
+ OperandHandlers.DEFAULT, operandTypeChecker, 0,
+ SqlFunctionCategory.SYSTEM, call -> SqlMonotonicity.NOT_MONOTONIC,
false);
+ }
+
/** Creates a {@code SqlBasicFunction} whose name is the same as its kind
* and whose category {@link SqlFunctionCategory#SYSTEM}. */
public static SqlBasicFunction create(SqlKind kind,
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 29089a6ebe..9f64e13c5a 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -430,6 +430,9 @@ public enum SqlKind {
/** {@code GREATEST} function (Oracle, Spark). */
GREATEST,
+ /** {@code GREATEST} function (PostgreSQL). */
+ GREATEST_PG,
+
/** The two-argument {@code CONCAT} function (Oracle). */
CONCAT2,
@@ -449,6 +452,9 @@ public enum SqlKind {
/** {@code LEAST} function (Oracle). */
LEAST,
+ /** {@code LEAST} function (PostgreSQL). */
+ LEAST_PG,
+
/** {@code LOG} function. (Mysql, Spark). */
LOG,
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 671282383f..642b74690a 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
@@ -447,19 +447,33 @@ public abstract class SqlLibraryOperators {
SqlFunctionCategory.STRING);
/** The "GREATEST(value, value)" function. */
- @LibraryOperator(libraries = {BIG_QUERY, ORACLE, REDSHIFT, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
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})
+ 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, REDSHIFT, SPARK})
+ @LibraryOperator(libraries = {BIG_QUERY, ORACLE, SPARK})
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})
+ public static final SqlFunction LEAST_PG =
+ SqlBasicFunction.create("LEAST", SqlKind.LEAST_PG,
+ ReturnTypes.LEAST_RESTRICTIVE.andThen(SqlTypeTransforms.TO_NULLABLE),
+ OperandTypes.SAME_VARIADIC);
+
/** The "CEIL(value)" function. Identical to the standard <code>CEIL</code>
function
* except the return type should be a double if the operand is an integer. */
@LibraryOperator(libraries = {BIG_QUERY})
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index e04f2e7f3c..68f412dd61 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -193,7 +193,9 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
new TrimConvertlet(SqlTrimFunction.Flag.TRAILING));
registerOp(SqlLibraryOperators.GREATEST, new GreatestConvertlet());
+ registerOp(SqlLibraryOperators.GREATEST_PG, new GreatestPgConvertlet());
registerOp(SqlLibraryOperators.LEAST, new GreatestConvertlet());
+ registerOp(SqlLibraryOperators.LEAST_PG, new GreatestPgConvertlet());
registerOp(SqlLibraryOperators.SUBSTR_BIG_QUERY,
new SubstrConvertlet(SqlLibrary.BIG_QUERY));
registerOp(SqlLibraryOperators.SUBSTR_MYSQL,
@@ -1900,6 +1902,60 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
}
+ /** Convertlet that converts {@code GREATEST} and {@code LEAST}. */
+ private static class GreatestPgConvertlet implements SqlRexConvertlet {
+ @Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
+ // Translate
+ // GREATEST(a, b, c, d)
+ // to
+ // CASE
+ // WHEN a IS NOT NULL AND (b IS NULL OR a > b) AND (c IS NULL OR a >
c) AND
+ // (d IS NULL OR a > d)
+ // THEN a
+ // WHEN b IS NOT NULL AND (c IS NULL OR b > c) AND (d IS NULL OR b > d)
+ // THEN b
+ // WHEN C IS NOT NULL AND (d IS NULL OR c > d)
+ // THEN c
+ // WHEN d IS NOT NULL
+ // THEN d
+ // ELSE NULL
+ // END
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ final RelDataType type =
+ cx.getValidator().getValidatedNodeType(call);
+ final SqlBinaryOperator op;
+ switch (call.getKind()) {
+ case GREATEST_PG:
+ op = SqlStdOperatorTable.GREATER_THAN;
+ break;
+ case LEAST_PG:
+ op = SqlStdOperatorTable.LESS_THAN;
+ break;
+ default:
+ throw new AssertionError();
+ }
+ final List<RexNode> exprs =
+ convertOperands(cx, call, SqlOperandTypeChecker.Consistency.NONE);
+ final List<RexNode> list = new ArrayList<>();
+ for (int i = 0; i < exprs.size(); i++) {
+ RexNode expr = exprs.get(i);
+ final List<RexNode> andList = new ArrayList<>();
+ andList.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL,
expr));
+ for (int j = i + 1; j < exprs.size(); j++) {
+ final RexNode expr2 = exprs.get(j);
+ final List<RexNode> orList = new ArrayList<>();
+ orList.add(rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, expr2));
+ orList.add(rexBuilder.makeCall(op, expr, expr2));
+ andList.add(RexUtil.composeDisjunction(rexBuilder, orList));
+ }
+ list.add(RexUtil.composeConjunction(rexBuilder, andList));
+ list.add(expr);
+ }
+ list.add(rexBuilder.makeNullLiteral(type));
+ return rexBuilder.makeCall(type, SqlStdOperatorTable.CASE, list);
+ }
+ }
+
/** Convertlet that handles {@code FLOOR} and {@code CEIL} functions. */
private class FloorCeilConvertlet implements SqlRexConvertlet {
@Override public RexNode convertCall(SqlRexContext cx, SqlCall call) {
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 2a3090ad26..dc80e67ed9 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2782,7 +2782,7 @@ In the following:
| b | FORMAT_TIME(string, time) | Formats *time*
according to the specified format *string*
| b | FORMAT_TIMESTAMP(string timestamp) | Formats *timestamp*
according to the specified format *string*
| s | GETBIT(value, position) | Equivalent to
`BIT_GET(value, position)`
-| b o r s | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
+| b o p r s | GREATEST(expr [, expr ]*) | Returns the greatest of
the expressions
| b h s | IF(condition, value1, value2) | Returns *value1* if
*condition* is TRUE, *value2* otherwise
| b s | IFNULL(value1, value2) | Equivalent to
`NVL(value1, value2)`
| p | string1 ILIKE string2 [ ESCAPE string3 ] | Whether *string1*
matches pattern *string2*, ignoring case (similar to `LIKE`)
@@ -2801,7 +2801,7 @@ In the following:
| m | JSON_REPLACE(jsonValue, path, val [, path, val ]*) | Returns a JSON
document replace a data of *jsonValue*, *path*, *val*
| m | JSON_SET(jsonValue, path, val [, path, val ]*) | Returns a JSON document
set a data of *jsonValue*, *path*, *val*
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of
bytes used to store the binary representation of *jsonValue*
-| b o r s | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
+| b o p r s | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
| b m p r s | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
| f s | LEN(string) | Equivalent to
`CHAR_LENGTH(string)`
| b f s | LENGTH(string) | Equivalent to
`CHAR_LENGTH(string)`
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 0cad83d55a..1c0b2cae2b 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -10789,8 +10789,29 @@ public class SqlOperatorTest {
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT,
- SqlLibrary.SPARK);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5634";>
+ * [CALCITE-5634] Enable GREATEST, LEAST functions in PostgreSQL
library</a>. */
+ @Test void testGreatestPgFunc() {
+ final SqlOperatorFixture f0 =
+ fixture().setFor(SqlLibraryOperators.GREATEST_PG, VmName.EXPAND);
+ f0.checkFails("^greatest('on', 'earth')^",
+ "No match found for function signature GREATEST\\(<CHARACTER>,
<CHARACTER>\\)",
+ false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("greatest('on', 'earth')", "on ", "CHAR(5) NOT NULL");
+ f.checkString("greatest('show', 'on', 'earth')", "show ",
+ "CHAR(5) NOT NULL");
+ f.checkScalar("greatest(12, CAST(NULL AS INTEGER), 3)", "12",
+ "INTEGER");
+ f.checkScalar("greatest(false, true)", true, "BOOLEAN NOT NULL");
+ f.checkScalar("greatest(CAST(NULL AS INTEGER), CAST(NULL AS INTEGER))",
isNullValue(),
+ "INTEGER");
+ };
+ final List<SqlLibrary> libraries = list(SqlLibrary.POSTGRESQL,
SqlLibrary.REDSHIFT);
f0.forEachLibrary(libraries, consumer);
}
@@ -10814,8 +10835,29 @@ public class SqlOperatorTest {
"VARCHAR(5) NOT NULL");
};
final List<SqlLibrary> libraries =
- list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.REDSHIFT,
- SqlLibrary.SPARK);
+ list(SqlLibrary.BIG_QUERY, SqlLibrary.ORACLE, SqlLibrary.SPARK);
+ f0.forEachLibrary(libraries, consumer);
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5634";>
+ * [CALCITE-5634] Enable GREATEST, LEAST functions in PostgreSQL
library</a>. */
+ @Test void testLeastPgFunc() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.LEAST_PG, VmName.EXPAND);
+ f0.checkFails("^least('on', 'earth')^",
+ "No match found for function signature LEAST\\(<CHARACTER>,
<CHARACTER>\\)",
+ false);
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkString("least('on', 'earth')", "earth", "CHAR(5) NOT NULL");
+ f.checkString("least('show', 'on', 'earth')", "earth",
+ "CHAR(5) NOT NULL");
+ f.checkScalar("least(12, CAST(NULL AS INTEGER), 3)", "3",
+ "INTEGER");
+ f.checkScalar("least(false, true)", false, "BOOLEAN NOT NULL");
+ f.checkScalar("least(CAST(NULL AS INTEGER), CAST(NULL AS INTEGER))",
isNullValue(),
+ "INTEGER");
+ };
+ final List<SqlLibrary> libraries = list(SqlLibrary.POSTGRESQL,
SqlLibrary.REDSHIFT);
f0.forEachLibrary(libraries, consumer);
}
