This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new ad5701e8914 new SCALAR_IN_ARRAY function analogous to DRUID_IN (#16306)
ad5701e8914 is described below
commit ad5701e89147f7468a0da7263d3149f487cd53de
Author: Sree Charan Manamala <[email protected]>
AuthorDate: Fri Apr 19 09:45:15 2024 +0530
new SCALAR_IN_ARRAY function analogous to DRUID_IN (#16306)
* scalar_in function
* api doc
* refactor
---
docs/querying/math-expr.md | 1 +
docs/querying/sql-array-functions.md | 1 +
docs/querying/sql-functions.md | 8 +++
.../java/org/apache/druid/math/expr/Function.java | 38 ++++++++++++
.../org/apache/druid/math/expr/FunctionTest.java | 12 ++++
.../builtin/ScalarInArrayOperatorConversion.java | 50 ++++++++++++++++
.../sql/calcite/planner/DruidOperatorTable.java | 2 +
.../druid/sql/calcite/CalciteArraysQueryTest.java | 67 ++++++++++++++++++++++
website/.spelling | 1 +
9 files changed, 180 insertions(+)
diff --git a/docs/querying/math-expr.md b/docs/querying/math-expr.md
index ee47fc7c2db..d5255544a03 100644
--- a/docs/querying/math-expr.md
+++ b/docs/querying/math-expr.md
@@ -184,6 +184,7 @@ See javadoc of java.lang.Math for detailed explanation for
each function.
| array_ordinal(arr,long) | returns the array element at the 1 based index
supplied, or null for an out of range index |
| array_contains(arr,expr) | returns 1 if the array contains the element
specified by expr, or contains all elements specified by expr if expr is an
array, else 0 |
| array_overlap(arr1,arr2) | returns 1 if arr1 and arr2 have any elements in
common, else 0 |
+| scalar_in_array(expr, arr) | returns 1 if the scalar is present in the
array, else 0 |
| array_offset_of(arr,expr) | returns the 0 based index of the first
occurrence of expr in the array, or `null` or `-1` if
`druid.generic.useDefaultValueForNull=true` (deprecated legacy mode) if no
matching elements exist in the array. |
| array_ordinal_of(arr,expr) | returns the 1 based index of the first
occurrence of expr in the array, or `null` or `-1` if
`druid.generic.useDefaultValueForNull=true` (deprecated legacy mode) if no
matching elements exist in the array. |
| array_prepend(expr,arr) | adds expr to arr at the beginning, the resulting
array type determined by the type of the array |
diff --git a/docs/querying/sql-array-functions.md
b/docs/querying/sql-array-functions.md
index 203b0e0980e..ab84c664dee 100644
--- a/docs/querying/sql-array-functions.md
+++ b/docs/querying/sql-array-functions.md
@@ -54,6 +54,7 @@ The following table describes array functions. To learn more
about array aggrega
|`ARRAY_ORDINAL(arr, long)`|Returns the array element at the 1-based index
supplied, or null for an out of range index.|
|`ARRAY_CONTAINS(arr, expr)`|If `expr` is a scalar type, returns 1 if `arr`
contains `expr`. If `expr` is an array, returns 1 if `arr` contains all
elements of `expr`. Otherwise returns 0.|
|`ARRAY_OVERLAP(arr1, arr2)`|Returns 1 if `arr1` and `arr2` have any elements
in common, else 0.|
+| `SCALAR_IN_ARRAY(expr, arr)`|Returns 1 if the scalar `expr` is present in
`arr`. else 0.|
|`ARRAY_OFFSET_OF(arr, expr)`|Returns the 0-based index of the first
occurrence of `expr` in the array. If no matching elements exist in the array,
returns `null` or `-1` if `druid.generic.useDefaultValueForNull=true`
(deprecated legacy mode).|
|`ARRAY_ORDINAL_OF(arr, expr)`|Returns the 1-based index of the first
occurrence of `expr` in the array. If no matching elements exist in the array,
returns `null` or `-1` if `druid.generic.useDefaultValueForNull=true`
(deprecated legacy mode).|
|`ARRAY_PREPEND(expr, arr)`|Adds `expr` to the beginning of `arr`, the
resulting array type determined by the type of `arr`.|
diff --git a/docs/querying/sql-functions.md b/docs/querying/sql-functions.md
index 9a43afab1d7..093e7ce60fd 100644
--- a/docs/querying/sql-functions.md
+++ b/docs/querying/sql-functions.md
@@ -206,6 +206,14 @@ Returns the 1-based index of the first occurrence of
`expr` in the array. If no
Returns 1 if `arr1` and `arr2` have any elements in common, else 0.|
+## SCALAR_IN_ARRAY
+
+`SCALAR_IN_ARRAY(expr, arr)`
+
+**Function type:** [Array](./sql-array-functions.md)
+
+Returns 1 if the scalar `expr` is present in `arr`, else 0.|
+
## ARRAY_PREPEND
`ARRAY_PREPEND(expr, arr)`
diff --git a/processing/src/main/java/org/apache/druid/math/expr/Function.java
b/processing/src/main/java/org/apache/druid/math/expr/Function.java
index 4c073114286..aa54409e132 100644
--- a/processing/src/main/java/org/apache/druid/math/expr/Function.java
+++ b/processing/src/main/java/org/apache/druid/math/expr/Function.java
@@ -3724,6 +3724,44 @@ public interface Function extends NamedFunction
}
}
+ class ArrayScalarInFunction extends ArrayScalarFunction
+ {
+ @Override
+ public String name()
+ {
+ return "scalar_in_array";
+ }
+
+ @Nullable
+ @Override
+ public ExpressionType getOutputType(Expr.InputBindingInspector inspector,
List<Expr> args)
+ {
+ return ExpressionType.LONG;
+ }
+
+ @Override
+ Expr getScalarArgument(List<Expr> args)
+ {
+ return args.get(0);
+ }
+
+ @Override
+ Expr getArrayArgument(List<Expr> args)
+ {
+ return args.get(1);
+ }
+
+ @Override
+ ExprEval doApply(ExprEval arrayExpr, ExprEval scalarExpr)
+ {
+ final Object[] array =
arrayExpr.castTo(scalarExpr.asArrayType()).asArray();
+ if (array == null) {
+ return ExprEval.ofLong(null);
+ }
+ return
ExprEval.ofLongBoolean(Arrays.asList(array).contains(scalarExpr.value()));
+ }
+ }
+
class ArrayAppendFunction extends ArrayAddElementFunction
{
@Override
diff --git
a/processing/src/test/java/org/apache/druid/math/expr/FunctionTest.java
b/processing/src/test/java/org/apache/druid/math/expr/FunctionTest.java
index 30d549dc351..da81a556b0b 100644
--- a/processing/src/test/java/org/apache/druid/math/expr/FunctionTest.java
+++ b/processing/src/test/java/org/apache/druid/math/expr/FunctionTest.java
@@ -369,6 +369,18 @@ public class FunctionTest extends
InitializedNullHandlingTest
assertExpr("array_ordinal_of(a, 'baz')", 3L);
}
+ @Test
+ public void testScalarInArray()
+ {
+ assertExpr("scalar_in_array(2, [1, 2, 3])", 1L);
+ assertExpr("scalar_in_array(4, [1, 2, 3])", 0L);
+ assertExpr("scalar_in_array(b, [3, 4])", 0L);
+ assertExpr("scalar_in_array(1, null)", null);
+ assertExpr("scalar_in_array(null, null)", null);
+ assertExpr("scalar_in_array(null, [1, null, 2])", 1L);
+ assertExpr("scalar_in_array(null, [1, 2])", 0L);
+ }
+
@Test
public void testArrayContains()
{
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/ScalarInArrayOperatorConversion.java
b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/ScalarInArrayOperatorConversion.java
new file mode 100644
index 00000000000..f6e3dcecf9d
--- /dev/null
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/ScalarInArrayOperatorConversion.java
@@ -0,0 +1,50 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.druid.sql.calcite.expression.builtin;
+
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.druid.sql.calcite.expression.DirectOperatorConversion;
+import org.apache.druid.sql.calcite.expression.OperatorConversions;
+
+public class ScalarInArrayOperatorConversion extends DirectOperatorConversion
+{
+ private static final SqlFunction SQL_FUNCTION = OperatorConversions
+ .operatorBuilder("SCALAR_IN_ARRAY")
+ .operandTypeChecker(
+ OperandTypes.sequence(
+ "'SCALAR_IN_ARRAY(expr, array)'",
+ OperandTypes.or(
+ OperandTypes.family(SqlTypeFamily.CHARACTER),
+ OperandTypes.family(SqlTypeFamily.NUMERIC)
+ ),
+ OperandTypes.family(SqlTypeFamily.ARRAY)
+ )
+ )
+ .returnTypeInference(ReturnTypes.BOOLEAN_NULLABLE)
+ .build();
+
+ public ScalarInArrayOperatorConversion()
+ {
+ super(SQL_FUNCTION, "scalar_in_array");
+ }
+}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
index 25c2b031f9a..723d5f45a39 100644
---
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
+++
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidOperatorTable.java
@@ -110,6 +110,7 @@ import
org.apache.druid.sql.calcite.expression.builtin.RepeatOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.ReverseOperatorConversion;
import org.apache.druid.sql.calcite.expression.builtin.RightOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.SafeDivideOperatorConversion;
+import
org.apache.druid.sql.calcite.expression.builtin.ScalarInArrayOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.SearchOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.StringFormatOperatorConversion;
import
org.apache.druid.sql.calcite.expression.builtin.StringToArrayOperatorConversion;
@@ -262,6 +263,7 @@ public class DruidOperatorTable implements SqlOperatorTable
.add(new ArrayToStringOperatorConversion())
.add(new StringToArrayOperatorConversion())
.add(new ArrayToMultiValueStringOperatorConversion())
+ .add(new ScalarInArrayOperatorConversion())
.build();
private static final List<SqlOperatorConversion>
MULTIVALUE_STRING_OPERATOR_CONVERSIONS =
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteArraysQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteArraysQueryTest.java
index 7c388f697d3..949e6b9cd00 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteArraysQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteArraysQueryTest.java
@@ -1338,6 +1338,73 @@ public class CalciteArraysQueryTest extends
BaseCalciteQueryTest
);
}
+ @Test
+ public void testScalarInArrayFilter()
+ {
+ msqIncompatible();
+ testQuery(
+ "SELECT dim2 FROM druid.numfoo WHERE SCALAR_IN_ARRAY(dim2,
ARRAY['a', 'd']) LIMIT 5",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE3)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .filters(
+ new
ExpressionDimFilter("scalar_in_array(\"dim2\",array('a','d'))",
ExprMacroTable.nil())
+ )
+ .columns("dim2")
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .limit(5)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ ),
+ ImmutableList.of(
+ new Object[]{"a"},
+ new Object[]{"a"}
+ )
+ );
+ }
+
+ @Test
+ public void testArrayScalarInFilter_MVD()
+ {
+ msqIncompatible();
+ testBuilder()
+ .sql(
+ "SELECT dim3, (CASE WHEN scalar_in_array(dim3, Array['a',
'b', 'd']) THEN 'abd' ELSE 'not abd' END) " +
+ "FROM druid.numfoo"
+ )
+ .expectedQueries(
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE3)
+
.intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ new ExpressionVirtualColumn(
+ "v0",
+
"case_searched(scalar_in_array(\"dim3\",array('a','b','d')),'abd','not abd')",
+ ColumnType.STRING,
+ ExprMacroTable.nil()
+ )
+ )
+ .columns("dim3", "v0")
+
.resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+ )
+ )
+ .expectedResults(ResultMatchMode.RELAX_NULLS,
+ ImmutableList.of(
+ new Object[]{"[\"a\",\"b\"]", "[\"abd\",\"abd\"]"},
+ new Object[]{"[\"b\",\"c\"]", "[\"abd\",\"not
abd\"]"},
+ new Object[]{"d", "abd"},
+ new Object[]{"", "not abd"},
+ new Object[]{null, "not abd"},
+ new Object[]{null, "not abd"}
+ )
+ )
+ .run();
+
+ }
@Test
public void testArraySlice()
diff --git a/website/.spelling b/website/.spelling
index 3ceea924b57..468c6f9f1b7 100644
--- a/website/.spelling
+++ b/website/.spelling
@@ -1508,6 +1508,7 @@ array_overlap
array_prepend
array_slice
array_to_string
+scalar_in_array
asin
atan
atan2
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
