This is an automated email from the ASF dual-hosted git repository.
tanner 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 0a0fd871fa [CALCITE-6205] Add BITAND_AGG, BITOR_AGG functions (enabled
in Snowflake library)
0a0fd871fa is described below
commit 0a0fd871fa9b734f5730f05a1725e7bb560d3799
Author: Tanner Clary <[email protected]>
AuthorDate: Tue Jan 16 10:49:03 2024 -0800
[CALCITE-6205] Add BITAND_AGG, BITOR_AGG functions (enabled in Snowflake
library)
---
.../calcite/adapter/enumerable/RexImpTable.java | 6 +-
.../calcite/sql/dialect/SnowflakeSqlDialect.java | 10 ++
.../calcite/sql/fun/SqlBitOpAggFunction.java | 19 ++-
.../calcite/sql/fun/SqlLibraryOperators.java | 12 ++
.../calcite/sql2rel/StandardConvertletTable.java | 2 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 +++
site/_docs/reference.md | 2 +
.../org/apache/calcite/test/SqlOperatorTest.java | 152 ++++++++++++---------
8 files changed, 162 insertions(+), 63 deletions(-)
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 c1288c730d..71c65fb599 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
@@ -143,6 +143,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_TO_STRING;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_UNION;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ASINH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ATANH;
+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.BOOL_AND;
@@ -1041,6 +1043,8 @@ public class RexImpTable {
aggMap.put(LOGICAL_OR, minMax);
final Supplier<BitOpImplementor> bitop =
constructorSupplier(BitOpImplementor.class);
+ aggMap.put(BITAND_AGG, bitop);
+ aggMap.put(BITOR_AGG, bitop);
aggMap.put(BIT_AND, bitop);
aggMap.put(BIT_OR, bitop);
aggMap.put(BIT_XOR, bitop);
@@ -1793,7 +1797,7 @@ public class RexImpTable {
if (SqlTypeUtil.isBinary(info.returnRelType())) {
start = Expressions.field(null, ByteString.class, "EMPTY");
} else {
- Object initValue = info.aggregation() == BIT_AND ? -1L : 0;
+ Object initValue = info.aggregation().kind == SqlKind.BIT_AND ? -1L :
0;
start = Expressions.constant(initValue, info.returnType());
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
index f7d8741491..9b38821202 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SnowflakeSqlDialect.java
@@ -43,6 +43,16 @@ public class SnowflakeSqlDialect extends SqlDialect {
@Override public void unparseCall(final SqlWriter writer, final SqlCall
call, final int leftPrec,
final int rightPrec) {
switch (call.getKind()) {
+ case BIT_AND:
+ SqlCall bitAndCall = SqlLibraryOperators.BITAND_AGG
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, bitAndCall, leftPrec, rightPrec);
+ break;
+ case BIT_OR:
+ SqlCall bitOrCall = SqlLibraryOperators.BITOR_AGG
+ .createCall(SqlParserPos.ZERO, call.getOperandList());
+ super.unparseCall(writer, bitOrCall, leftPrec, rightPrec);
+ break;
case CHAR_LENGTH:
SqlCall lengthCall = SqlLibraryOperators.LENGTH
.createCall(SqlParserPos.ZERO, call.getOperandList());
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlBitOpAggFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlBitOpAggFunction.java
index 17eacae87f..808724d02d 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlBitOpAggFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlBitOpAggFunction.java
@@ -39,7 +39,7 @@ public class SqlBitOpAggFunction extends SqlAggFunction {
//~ Constructors -----------------------------------------------------------
- /** Creates a SqlBitOpAggFunction. */
+ /** Creates a SqlBitOpAggFunction from a SqlKind. */
public SqlBitOpAggFunction(SqlKind kind) {
super(kind.name(),
null,
@@ -56,6 +56,23 @@ public class SqlBitOpAggFunction extends SqlAggFunction {
|| kind == SqlKind.BIT_XOR);
}
+ /** Creates a SqlBitOpAggFunction from a name and SqlKind. */
+ public SqlBitOpAggFunction(String name, SqlKind kind) {
+ super(name,
+ null,
+ kind,
+ ReturnTypes.ARG0_NULLABLE_IF_EMPTY,
+ null,
+ OperandTypes.INTEGER.or(OperandTypes.BINARY),
+ SqlFunctionCategory.NUMERIC,
+ false,
+ false,
+ Optionality.FORBIDDEN);
+ Preconditions.checkArgument(kind == SqlKind.BIT_AND
+ || kind == SqlKind.BIT_OR
+ || kind == SqlKind.BIT_XOR);
+ }
+
@Override public <T extends Object> @Nullable T unwrap(Class<T> clazz) {
if (clazz.isInstance(SqlSplittableAggFunction.SelfSplitter.INSTANCE)) {
return clazz.cast(SqlSplittableAggFunction.SelfSplitter.INSTANCE);
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 d4c8b9d1ea..265084c4f6 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
@@ -2203,6 +2203,18 @@ public abstract class SqlLibraryOperators {
InferTypes.FIRST_KNOWN,
OperandTypes.COMPARABLE_UNORDERED_COMPARABLE_UNORDERED);
+ /** The "BITAND_AGG(expression)" function. Equivalent to
+ * the standard "BIT_AND(expression)". */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlAggFunction BITAND_AGG =
+ new SqlBitOpAggFunction("BITAND_AGG", SqlKind.BIT_AND);
+
+ /** The "BITOR_AGG(expression)" function. Equivalent to
+ * the standard "BIT_OR(expression)". */
+ @LibraryOperator(libraries = {SNOWFLAKE})
+ public static final SqlAggFunction BITOR_AGG =
+ new SqlBitOpAggFunction("BITOR_AGG", SqlKind.BIT_OR);
+
/** The "BIT_LENGTH(string or binary)" function. */
@LibraryOperator(libraries = {SPARK})
public static final SqlFunction BIT_LENGTH =
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 d9bc1d27d5..ccd946c437 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -137,6 +137,8 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
addAlias(SqlLibraryOperators.REGEXP_SUBSTR,
SqlLibraryOperators.REGEXP_EXTRACT);
addAlias(SqlLibraryOperators.ENDSWITH, SqlLibraryOperators.ENDS_WITH);
addAlias(SqlLibraryOperators.STARTSWITH, SqlLibraryOperators.STARTS_WITH);
+ addAlias(SqlLibraryOperators.BITAND_AGG, SqlStdOperatorTable.BIT_AND);
+ addAlias(SqlLibraryOperators.BITOR_AGG, SqlStdOperatorTable.BIT_OR);
// Register convertlets for specific objects.
registerOp(SqlStdOperatorTable.CAST, this::convertCast);
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 7a5bc0c499..258aca0efa 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -6662,6 +6662,28 @@ class RelToSqlConverterTest {
sql(sql).withMysql().ok(expectedMysql);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6205";>[CALCITE-6205]
+ * Add BITAND_AGG, BITOR_AGG functions (enabled in Snowflake library)</a>. */
+ @Test void testBitAndAgg() {
+ final String query = "select bit_and(\"product_id\")\n"
+ + "from \"product\"";
+ final String expectedSnowflake = "SELECT BITAND_AGG(\"product_id\")\n"
+ + "FROM \"foodmart\".\"product\"";
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6205";>[CALCITE-6205]
+ * Add BITAND_AGG, BITOR_AGG functions (enabled in Snowflake library)</a>. */
+ @Test void testBitOrAgg() {
+ final String query = "select bit_or(\"product_id\")\n"
+ + "from \"product\"";
+ final String expectedSnowflake = "SELECT BITOR_AGG(\"product_id\")\n"
+ + "FROM \"foodmart\".\"product\"";
+
sql(query).withLibrary(SqlLibrary.SNOWFLAKE).withSnowflake().ok(expectedSnowflake);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6156";>[CALCITE-6156]
* Add ENDSWITH, STARTSWITH functions (enabled in Postgres, Snowflake
libraries)</a>. */
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 3f97448543..1ba228d312 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2685,6 +2685,8 @@ In the following:
| s | SORT_ARRAY(array [, ascendingOrder]) | Sorts the *array* in
ascending or descending order according to the natural ordering of the array
elements. The default order is ascending if *ascendingOrder* is not specified.
Null elements will be placed at the beginning of the returned array in
ascending order or at the end of the returned array in descending order
| * | ASINH(numeric) | Returns the inverse
hyperbolic sine of *numeric*
| * | ATANH(numeric) | Returns the inverse
hyperbolic tangent of *numeric*
+| f | BITAND_AGG(value) | Equivalent to
`BIT_AND(value)`
+| f | BITOR_AGG(value) | Equivalent to
`BIT_OR(value)`
| s | BIT_LENGTH(binary) | Returns the bit length
of *binary*
| s | BIT_LENGTH(string) | Returns the bit length
of *string*
| s | BIT_GET(value, position) | Returns the bit (0 or
1) value at the specified *position* of numeric *value*. The positions are
numbered from right to left, starting at zero. The *position* argument cannot
be negative
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 dabb16e752..24bf994591 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -13479,76 +13479,106 @@ public class SqlOperatorTest {
f.checkAgg("logical_or(x)", values4, isNullValue());
}
+ @Test void testBitAndAggFunc() {
+ final SqlOperatorFixture f = fixture();
+ f.setFor(SqlLibraryOperators.BITAND_AGG, VmName.EXPAND);
+ checkBitAnd(f, FunctionAlias.of(SqlLibraryOperators.BITAND_AGG));
+ }
+
@Test void testBitAndFunc() {
final SqlOperatorFixture f = fixture();
- f.setFor(SqlStdOperatorTable.BIT_AND, VM_FENNEL, VM_JAVA);
- f.checkFails("bit_and(^*^)", "Unknown identifier '\\*'", false);
- f.checkType("bit_and(1)", "INTEGER");
- f.checkType("bit_and(CAST(2 AS TINYINT))", "TINYINT");
- f.checkType("bit_and(CAST(2 AS SMALLINT))", "SMALLINT");
- f.checkType("bit_and(distinct CAST(2 AS BIGINT))", "BIGINT");
- f.checkType("bit_and(CAST(x'02' AS BINARY(1)))", "BINARY(1)");
- f.checkFails("^bit_and(1.2)^",
- "Cannot apply 'BIT_AND' to arguments of type 'BIT_AND\\(<DECIMAL\\(2,
1\\)>\\)'\\. Supported form\\(s\\): 'BIT_AND\\(<INTEGER>\\)'\n"
- + "'BIT_AND\\(<BINARY>\\)'",
- false);
- f.checkFails("^bit_and()^",
- "Invalid number of arguments to function 'BIT_AND'. Was expecting 1
arguments",
- false);
- f.checkFails("^bit_and(1, 2)^",
- "Invalid number of arguments to function 'BIT_AND'. Was expecting 1
arguments",
- false);
- final String[] values = {"3", "2", "2"};
- f.checkAgg("bit_and(x)", values, isSingle("2"));
- final String[] binaryValues = {
- "CAST(x'03' AS BINARY)",
- "cast(x'02' as BINARY)",
- "cast(x'02' AS BINARY)",
- "cast(null AS BINARY)"};
- f.checkAgg("bit_and(x)", binaryValues, isSingle("02"));
- f.checkAgg("bit_and(x)", new String[]{"CAST(x'02' AS BINARY)"},
isSingle("02"));
+ f.setFor(SqlStdOperatorTable.BIT_AND, VmName.EXPAND);
+ checkBitAnd(f, FunctionAlias.of(SqlStdOperatorTable.BIT_AND));
+ }
+
+ /** Tests the {@code BIT_AND} and {@code BITAND_AGG} operators. */
+ void checkBitAnd(SqlOperatorFixture f0, FunctionAlias functionAlias) {
+ final SqlFunction function = functionAlias.function;
+ final String fn = function.getName();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkFails(fn + "(^*^)", "Unknown identifier '\\*'", false);
+ f.checkType(fn + "(1)", "INTEGER");
+ f.checkType(fn + "(CAST(2 AS TINYINT))", "TINYINT");
+ f.checkType(fn + "(CAST(2 AS SMALLINT))", "SMALLINT");
+ f.checkType(fn + "(distinct CAST(2 AS BIGINT))", "BIGINT");
+ f.checkType(fn + "(CAST(x'02' AS BINARY(1)))", "BINARY(1)");
+ f.checkFails("^" + fn + "(1.2)^",
+ "Cannot apply '" + fn + "' to arguments of type '"
+ + fn + "\\(<DECIMAL\\(2, 1\\)>\\)'\\. Supported form\\(s\\): '"
+ + fn + "\\(<INTEGER>\\)'\n"
+ + "'" + fn + "\\(<BINARY>\\)'",
+ false);
+ f.checkFails("^" + fn + "()^",
+ "Invalid number of arguments to function '" + fn + "'. Was expecting
1 arguments",
+ false);
+ f.checkFails("^" + fn + "(1, 2)^",
+ "Invalid number of arguments to function '" + fn + "'. Was expecting
1 arguments",
+ false);
+ final String[] values = {"3", "2", "2"};
+ f.checkAgg(fn + "(x)", values, isSingle("2"));
+ final String[] binaryValues = {
+ "CAST(x'03' AS BINARY)",
+ "cast(x'02' as BINARY)",
+ "cast(x'02' AS BINARY)",
+ "cast(null AS BINARY)"};
+ f.checkAgg(fn + "(x)", binaryValues, isSingle("02"));
+ f.checkAgg(fn + "(x)", new String[]{"CAST(x'02' AS BINARY)"},
isSingle("02"));
+ f.checkAggFails(fn + "(x)",
+ new String[]{"CAST(x'0201' AS VARBINARY)", "CAST(x'02' AS
VARBINARY)"},
+ "Error while executing SQL .*"
+ + " Different length for bitwise operands: the first: 2, the
second: 1",
+ true);
+ };
+ f0.forEachLibrary(list(functionAlias.libraries), consumer);
}
- @Test void testBitAndFuncRuntimeFails() {
+ @Test void testBitOrAggFunc() {
final SqlOperatorFixture f = fixture();
- f.checkAggFails("bit_and(x)",
- new String[]{"CAST(x'0201' AS VARBINARY)", "CAST(x'02' AS VARBINARY)"},
- "Error while executing SQL .*"
- + " Different length for bitwise operands: the first: 2, the
second: 1",
- true);
+ f.setFor(SqlLibraryOperators.BITOR_AGG, VmName.EXPAND);
+ checkBitOr(f, FunctionAlias.of(SqlLibraryOperators.BITOR_AGG));
}
@Test void testBitOrFunc() {
final SqlOperatorFixture f = fixture();
- f.setFor(SqlStdOperatorTable.BIT_OR, VM_FENNEL, VM_JAVA);
- f.checkFails("bit_or(^*^)", "Unknown identifier '\\*'", false);
- f.checkType("bit_or(1)", "INTEGER");
- f.checkType("bit_or(CAST(2 AS TINYINT))", "TINYINT");
- f.checkType("bit_or(CAST(2 AS SMALLINT))", "SMALLINT");
- f.checkType("bit_or(distinct CAST(2 AS BIGINT))", "BIGINT");
- f.checkType("bit_or(CAST(x'02' AS BINARY(1)))", "BINARY(1)");
- f.checkFails("^bit_or(1.2)^",
- "Cannot apply 'BIT_OR' to arguments of type "
- + "'BIT_OR\\(<DECIMAL\\(2, 1\\)>\\)'\\. Supported form\\(s\\): "
- + "'BIT_OR\\(<INTEGER>\\)'\n"
- + "'BIT_OR\\(<BINARY>\\)'",
- false);
- f.checkFails("^bit_or()^",
- "Invalid number of arguments to function 'BIT_OR'. Was expecting 1
arguments",
- false);
- f.checkFails("^bit_or(1, 2)^",
- "Invalid number of arguments to function 'BIT_OR'. Was expecting 1
arguments",
- false);
- final String[] values = {"1", "2", "2"};
- f.checkAgg("bit_or(x)", values, isSingle(3));
- final String[] binaryValues = {
- "CAST(x'01' AS BINARY)",
- "cast(x'02' as BINARY)",
- "cast(x'02' AS BINARY)",
- "cast(null AS BINARY)"};
- f.checkAgg("bit_or(x)", binaryValues, isSingle("03"));
- f.checkAgg("bit_or(x)", new String[]{"CAST(x'02' AS BINARY)"},
- isSingle("02"));
+ f.setFor(SqlStdOperatorTable.BIT_OR, VmName.EXPAND);
+ checkBitOr(f, FunctionAlias.of(SqlStdOperatorTable.BIT_OR));
+ }
+
+ /** Tests the {@code BIT_OR} and {@code BITOR_AGG} operators. */
+ void checkBitOr(SqlOperatorFixture f0, FunctionAlias functionAlias) {
+ final SqlFunction function = functionAlias.function;
+ final String fn = function.getName();
+ final Consumer<SqlOperatorFixture> consumer = f -> {
+ f.checkFails(fn + "(^*^)", "Unknown identifier '\\*'", false);
+ f.checkType(fn + "(1)", "INTEGER");
+ f.checkType(fn + "(CAST(2 AS TINYINT))", "TINYINT");
+ f.checkType(fn + "(CAST(2 AS SMALLINT))", "SMALLINT");
+ f.checkType(fn + "(distinct CAST(2 AS BIGINT))", "BIGINT");
+ f.checkType(fn + "(CAST(x'02' AS BINARY(1)))", "BINARY(1)");
+ f.checkFails("^" + fn + "(1.2)^",
+ "Cannot apply '" + fn + "' to arguments of type "
+ + "'" + fn + "\\(<DECIMAL\\(2, 1\\)>\\)'\\. Supported
form\\(s\\): "
+ + "'" + fn + "\\(<INTEGER>\\)'\n"
+ + "'" + fn + "\\(<BINARY>\\)'",
+ false);
+ f.checkFails("^" + fn + "()^",
+ "Invalid number of arguments to function '" + fn + "'. Was expecting
1 arguments",
+ false);
+ f.checkFails("^" + fn + "(1, 2)^",
+ "Invalid number of arguments to function '" + fn + "'. Was expecting
1 arguments",
+ false);
+ final String[] values = {"1", "2", "2"};
+ f.checkAgg("bit_or(x)", values, isSingle(3));
+ final String[] binaryValues = {
+ "CAST(x'01' AS BINARY)",
+ "cast(x'02' as BINARY)",
+ "cast(x'02' AS BINARY)",
+ "cast(null AS BINARY)"};
+ f.checkAgg(fn + "(x)", binaryValues, isSingle("03"));
+ f.checkAgg(fn + "(x)", new String[]{"CAST(x'02' AS BINARY)"},
+ isSingle("02"));
+ };
+ f0.forEachLibrary(list(functionAlias.libraries), consumer);
}
@Test void testBitXorFunc() {
