This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit c8d42f350c2aa4efc24a585f96eba268310aa1ab
Author: Nitish Kumar <knitish5...@gmail.com>
AuthorDate: Mon Jun 17 16:42:23 2024 -0700
[CALCITE-6441] Add BOOLAGG_AND, BOOLAGG_OR aggregate functions (enabled in
Snowflake library)
---
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../calcite/sql/fun/SqlLibraryOperators.java | 12 +++++
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 58 ++++++++++++++++++++++
4 files changed, 76 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 3485cf2b21..a76eb0aa01 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
@@ -148,6 +148,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.BITAND_AGG;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BITOR_AGG;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BIT_GET;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BIT_LENGTH;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOLAND_AGG;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOLOR_AGG;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_AND;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_OR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CEIL_BIG_QUERY;
@@ -1058,6 +1060,8 @@ public class RexImpTable {
aggMap.put(EVERY, minMax);
aggMap.put(BOOL_AND, minMax);
aggMap.put(BOOL_OR, minMax);
+ aggMap.put(BOOLAND_AGG, minMax);
+ aggMap.put(BOOLOR_AGG, minMax);
aggMap.put(LOGICAL_AND, minMax);
aggMap.put(LOGICAL_OR, minMax);
final Supplier<BitOpImplementor> bitop =
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 c795483c57..3888bfcd40 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
@@ -635,6 +635,18 @@ public abstract class SqlLibraryOperators {
public static final SqlAggFunction BOOL_OR =
new SqlMinMaxAggFunction("BOOL_OR", SqlKind.MAX, OperandTypes.BOOLEAN);
+ /** The "BOOLAND_AGG(condition)" aggregate function, Snowflake's
+ * equivalent to {@link SqlStdOperatorTable#EVERY}. */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlAggFunction BOOLAND_AGG =
+ new SqlMinMaxAggFunction("BOOLAND_AGG", SqlKind.MIN,
OperandTypes.BOOLEAN);
+
+ /** The "BOOLOR_AGG(condition)" aggregate function, Snowflake's
+ * equivalent to {@link SqlStdOperatorTable#SOME}. */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlAggFunction BOOLOR_AGG =
+ new SqlMinMaxAggFunction("BOOLOR_AGG", SqlKind.MAX,
OperandTypes.BOOLEAN);
+
/** The "LOGICAL_AND(condition)" aggregate function, BigQuery's
* equivalent to {@link SqlStdOperatorTable#EVERY}. */
@LibraryOperator(libraries = {BIG_QUERY})
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 04356a29aa..c74832c9ac 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2935,6 +2935,8 @@ Dialect-specific aggregate functions.
| b p | ARRAY_CONCAT_AGG( [ ALL | DISTINCT ] value [ ORDER BY orderItem
[, orderItem ]* ] ) | Concatenates arrays into arrays
| p s | BOOL_AND(condition) | Synonym for `EVERY`
| p s | BOOL_OR(condition) | Synonym for `SOME`
+| f | BOOLAND_AGG(condition) | Synonym for `EVERY`
+| f | BOOLOR_AGG(condition) | Synonym for `SOME`
| b | COUNTIF(condition) | Returns the number of
rows for which *condition* is TRUE; equivalent to `COUNT(*) FILTER (WHERE
condition)`
| m | GROUP_CONCAT( [ ALL | DISTINCT ] value [, value ]* [ ORDER BY
orderItem [, orderItem ]* ] [ SEPARATOR separator ] ) | MySQL-specific variant
of `LISTAGG`
| b | LOGICAL_AND(condition) | Synonym for `EVERY`
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 f5c978d53a..a6d3a80469 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -14496,9 +14496,12 @@ public class SqlOperatorTest {
final String[] values = {"true", "true", "null"};
f.checkAggFails("^bool_and(x)^", values,
"No match found for function signature BOOL_AND\\(<BOOLEAN>\\)",
false);
+ f.checkAggFails("^booland_agg(x)^", values,
+ "No match found for function signature BOOLAND_AGG\\(<BOOLEAN>\\)",
false);
checkBoolAndFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
checkBoolAndFunc(f.withLibrary(SqlLibrary.SPARK));
+ checkBoolAndAggFunc(f.withLibrary(SqlLibrary.SNOWFLAKE));
}
private static void checkBoolAndFunc(SqlOperatorFixture f) {
@@ -14527,6 +14530,32 @@ public class SqlOperatorTest {
f.checkAgg("bool_and(x)", values4, isNullValue());
}
+ private static void checkBoolAndAggFunc(SqlOperatorFixture f) {
+ f.setFor(SqlLibraryOperators.BOOLAND_AGG, VM_EXPAND);
+
+ f.checkFails("booland_agg(^*^)", "Unknown identifier '\\*'", false);
+ f.checkType("booland_agg(true)", "BOOLEAN");
+ f.checkFails("^booland_agg(1)^",
+ "Cannot apply 'BOOLAND_AGG' to arguments of type
'BOOLAND_AGG\\(<INTEGER>\\)'\\. "
+ + "Supported form\\(s\\): 'BOOLAND_AGG\\(<BOOLEAN>\\)'",
+ false);
+ f.checkFails("^booland_agg()^",
+ "Invalid number of arguments to function 'BOOLAND_AGG'. Was expecting
1 arguments",
+ false);
+ f.checkFails("^booland_agg(true, true)^",
+ "Invalid number of arguments to function 'BOOLAND_AGG'. Was expecting
1 arguments",
+ false);
+
+ final String[] values1 = {"true", "true", "null"};
+ f.checkAgg("booland_agg(x)", values1, isSingle(true));
+ String[] values2 = {"true", "false", "null"};
+ f.checkAgg("booland_agg(x)", values2, isSingle(false));
+ String[] values3 = {"true", "false", "false"};
+ f.checkAgg("booland_agg(x)", values3, isSingle(false));
+ String[] values4 = {"null"};
+ f.checkAgg("booland_agg(x)", values4, isNullValue());
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6094";>
* Linq4j.ConstantExpression.write crashes on special FP values</a>. */
@@ -14544,9 +14573,12 @@ public class SqlOperatorTest {
final String[] values = {"true", "true", "null"};
f.checkAggFails("^bool_or(x)^", values,
"No match found for function signature BOOL_OR\\(<BOOLEAN>\\)", false);
+ f.checkAggFails("^boolor_agg(x)^", values,
+ "No match found for function signature BOOLOR_AGG\\(<BOOLEAN>\\)",
false);
checkBoolOrFunc(f.withLibrary(SqlLibrary.POSTGRESQL));
checkBoolOrFunc(f.withLibrary(SqlLibrary.SPARK));
+ checkBoolOrAggFunc(f.withLibrary(SqlLibrary.SNOWFLAKE));
}
private static void checkBoolOrFunc(SqlOperatorFixture f) {
@@ -14575,6 +14607,32 @@ public class SqlOperatorTest {
f.checkAgg("bool_or(x)", values4, isNullValue());
}
+ private static void checkBoolOrAggFunc(SqlOperatorFixture f) {
+ f.setFor(SqlLibraryOperators.BOOLOR_AGG, VM_EXPAND);
+
+ f.checkFails("boolor_agg(^*^)", "Unknown identifier '\\*'", false);
+ f.checkType("boolor_agg(true)", "BOOLEAN");
+ f.checkFails("^boolor_agg(1)^",
+ "Cannot apply 'BOOLOR_AGG' to arguments of type
'BOOLOR_AGG\\(<INTEGER>\\)'\\. "
+ + "Supported form\\(s\\): 'BOOLOR_AGG\\(<BOOLEAN>\\)'",
+ false);
+ f.checkFails("^boolor_agg()^",
+ "Invalid number of arguments to function 'BOOLOR_AGG'. Was expecting 1
arguments",
+ false);
+ f.checkFails("^boolor_agg(true, true)^",
+ "Invalid number of arguments to function 'BOOLOR_AGG'. Was expecting 1
arguments",
+ false);
+
+ final String[] values1 = {"true", "true", "null"};
+ f.checkAgg("boolor_agg(x)", values1, isSingle(true));
+ String[] values2 = {"true", "false", "null"};
+ f.checkAgg("boolor_agg(x)", values2, isSingle(true));
+ String[] values3 = {"false", "false", "false"};
+ f.checkAgg("boolor_agg(x)", values3, isSingle(false));
+ String[] values4 = {"null"};
+ f.checkAgg("boolor_agg(x)", values4, isNullValue());
+ }
+
@Test void testLogicalAndFunc() {
final SqlOperatorFixture f = fixture();
// not in standard dialect
