julianhyde commented on code in PR #3147:
URL: https://github.com/apache/calcite/pull/3147#discussion_r1253447643
##########
core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java:
##########
@@ -765,6 +765,11 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
ReturnTypes.INTEGER_NULLABLE, OperandTypes.DATE,
SqlFunctionCategory.TIMEDATE);
+ /** The "CONTAINS_SUBSTR(expression, search_value_literal[, json_scope =>
json_scope_value])"
Review Comment:
as i said elsewhere 'performs' is a zero-information word.
need to escape '=>' for HTML.
##########
core/src/main/java/org/apache/calcite/sql/SqlKind.java:
##########
@@ -839,6 +839,8 @@ public enum SqlKind {
* TABLE(udx(CURSOR(SELECT ...), x, y, z))</code>. */
CURSOR,
+ CONTAINS_SUBSTR,
Review Comment:
need javadoc comment
##########
core/src/main/java/org/apache/calcite/sql/fun/SqlContainsSubstrFunction.java:
##########
@@ -0,0 +1,47 @@
+/*
+ * 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.calcite.sql.fun;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+
+/** The "CONTAINS_SUBSTR(expression, search_value_literal[, json_scope =>
json_scope_value])"
+ * function; performs a search to see if a value exists as a substring in an
expression. */
+public class SqlContainsSubstrFunction extends SqlFunction {
+ public SqlContainsSubstrFunction() {
+ super("CONTAINS_SUBSTR", SqlKind.CONTAINS_SUBSTR,
ReturnTypes.BOOLEAN_NULLABLE,
+ null, OperandTypes.ANY_STRING_OPTIONAL_STRING,
+ SqlFunctionCategory.STRING);
+ }
+
+ @Override public RelDataType deriveType(SqlValidator validator,
SqlValidatorScope scope,
Review Comment:
indentation for `SqlCall call` line looks off.
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -562,6 +564,91 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
}
}
+ public static @Nullable Boolean containsSubstr(@Nullable Object @Nullable []
s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ if (s0 == null) {
+ return false;
+ }
+ for (Object obj : s0) {
+ if (obj == null) {
+ nullFlag = true;
+ } else if (obj instanceof Object[]) {
+ return containsSubstr((Object[]) obj, s1);
+ } else if (obj instanceof ArrayList) {
+ return containsSubstr((List) obj, s1);
+ } else if (normalize(obj.toString()).contains(normalize(s1))) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(List s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ for (Object item : s0) {
+ if (item == null) {
+ nullFlag = true;
+ }
+ if (item != null && containsSubstr(item, s1)) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(String s0, String s1, String
s2) {
+ // The third argument specifies the json_scope, either keys, values, or
both
+ LinkedHashMap<String, String> map = (LinkedHashMap)
JsonFunctions.dejsonize(s0);
+ Set<String> keys = map.keySet();
+ Collection<String> values = map.values();
+ if (s2.equals("JSON_KEYS")) {
Review Comment:
should `s2` be an `enum` rather than a `String`?
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -562,6 +564,91 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
}
}
+ public static @Nullable Boolean containsSubstr(@Nullable Object @Nullable []
s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ if (s0 == null) {
+ return false;
+ }
+ for (Object obj : s0) {
+ if (obj == null) {
+ nullFlag = true;
+ } else if (obj instanceof Object[]) {
+ return containsSubstr((Object[]) obj, s1);
+ } else if (obj instanceof ArrayList) {
+ return containsSubstr((List) obj, s1);
+ } else if (normalize(obj.toString()).contains(normalize(s1))) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(List s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ for (Object item : s0) {
+ if (item == null) {
+ nullFlag = true;
+ }
+ if (item != null && containsSubstr(item, s1)) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(String s0, String s1, String
s2) {
+ // The third argument specifies the json_scope, either keys, values, or
both
+ LinkedHashMap<String, String> map = (LinkedHashMap)
JsonFunctions.dejsonize(s0);
+ Set<String> keys = map.keySet();
+ Collection<String> values = map.values();
+ if (s2.equals("JSON_KEYS")) {
+ return keys.contains(s1);
+ } else if (s2.equals("JSON_VALUES")) {
+ return values.contains(s1);
+ } else if (s2.equals("JSON_KEYS_AND_VALUES")) {
+ return keys.contains(s1) || values.contains(s1);
+ } else {
+ throw new IllegalArgumentException("json_scope argument must be one of:
\"JSON_KEYS\", "
+ + "\"JSON_VALUES\", \"JSON_KEYS_AND_VALUES\".");
+ }
+ }
+
+ /** SQL {@code CONTAINS_SUBSTR(string, string)} function. */
Review Comment:
Use more descriptive names for arguments. Make the arguments in the javadoc
comment (`string`) and Java parameters (`s0`, `s1`) consistent.
##########
core/src/main/java/org/apache/calcite/sql/fun/SqlContainsSubstrFunction.java:
##########
@@ -0,0 +1,47 @@
+/*
+ * 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.calcite.sql.fun;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+
+/** The "CONTAINS_SUBSTR(expression, search_value_literal[, json_scope =>
json_scope_value])"
+ * function; performs a search to see if a value exists as a substring in an
expression. */
+public class SqlContainsSubstrFunction extends SqlFunction {
Review Comment:
Do we need a class for this? Would `SqlBasicFunction.create` suffice? If
not, what's missing?
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -562,6 +564,91 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
}
}
+ public static @Nullable Boolean containsSubstr(@Nullable Object @Nullable []
s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ if (s0 == null) {
+ return false;
+ }
+ for (Object obj : s0) {
+ if (obj == null) {
+ nullFlag = true;
+ } else if (obj instanceof Object[]) {
+ return containsSubstr((Object[]) obj, s1);
+ } else if (obj instanceof ArrayList) {
+ return containsSubstr((List) obj, s1);
+ } else if (normalize(obj.toString()).contains(normalize(s1))) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(List s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ for (Object item : s0) {
+ if (item == null) {
+ nullFlag = true;
+ }
+ if (item != null && containsSubstr(item, s1)) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(String s0, String s1, String
s2) {
+ // The third argument specifies the json_scope, either keys, values, or
both
Review Comment:
Rather than adding comments, just rename `s2` to `jsonScope`
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -562,6 +564,91 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
}
}
+ public static @Nullable Boolean containsSubstr(@Nullable Object @Nullable []
s0, String s1) {
Review Comment:
Each of these methods needs a simple javadoc comment. Without such comments,
it's quite difficult to map from java types to the SQL types.
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -562,6 +564,91 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
}
}
+ public static @Nullable Boolean containsSubstr(@Nullable Object @Nullable []
s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ if (s0 == null) {
+ return false;
+ }
+ for (Object obj : s0) {
+ if (obj == null) {
+ nullFlag = true;
+ } else if (obj instanceof Object[]) {
+ return containsSubstr((Object[]) obj, s1);
+ } else if (obj instanceof ArrayList) {
+ return containsSubstr((List) obj, s1);
+ } else if (normalize(obj.toString()).contains(normalize(s1))) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(List s0, String s1) {
+ // If s0 has null arguments, it should return TRUE if substring is found,
otherwise NULL
+ boolean nullFlag = false;
+ for (Object item : s0) {
+ if (item == null) {
+ nullFlag = true;
+ }
+ if (item != null && containsSubstr(item, s1)) {
+ return true;
+ }
+ }
+ return nullFlag ? null : false;
+ }
+
+ public static @Nullable Boolean containsSubstr(String s0, String s1, String
s2) {
+ // The third argument specifies the json_scope, either keys, values, or
both
+ LinkedHashMap<String, String> map = (LinkedHashMap)
JsonFunctions.dejsonize(s0);
+ Set<String> keys = map.keySet();
+ Collection<String> values = map.values();
+ if (s2.equals("JSON_KEYS")) {
+ return keys.contains(s1);
+ } else if (s2.equals("JSON_VALUES")) {
+ return values.contains(s1);
+ } else if (s2.equals("JSON_KEYS_AND_VALUES")) {
+ return keys.contains(s1) || values.contains(s1);
+ } else {
+ throw new IllegalArgumentException("json_scope argument must be one of:
\"JSON_KEYS\", "
+ + "\"JSON_VALUES\", \"JSON_KEYS_AND_VALUES\".");
+ }
+ }
+
+ /** SQL {@code CONTAINS_SUBSTR(string, string)} function. */
+ public static Boolean containsSubstr(@Nullable Object s0, String s1) {
Review Comment:
Do all these methods need to return `Boolean` rather than `boolean`?
Remember that Calcite's code generator will short-circuit the call based on
the function's strong-ness. For normal functions, if any arguments are null, it
will not call the function, and the result is SQL NULL (or UNKNOWN). In such
cases, you should not handle nulls. `Integer` arguments can be `int`, `Boolean`
return can be `boolean`, etc.
##########
site/_docs/reference.md:
##########
@@ -2685,6 +2687,7 @@ BigQuery's type system uses confusingly different names
for types and functions:
| m p | CONCAT_WS(separator, str1 [, string ]*) | Concatenates one or
more strings, returns null only when separator is null, otherwise treats null
arguments as empty strings
| q | CONCAT_WS(separator, str1, str2 [, string ]*) | Concatenates two or
more strings, requires at least 3 arguments (up to 254), treats null arguments
as empty strings
| m | COMPRESS(string) | Compresses a string
using zlib compression and returns the result as a binary string
+| b | CONTAINS_SUBSTR(expression, string [ , json_scope=>json_scope_value]) |
Performs a normalized, case-insensitive search to see if a value exists as a
substring in an expression. Can also search JSON data within an
optionally-specified scope. Returns TRUE if the string is found and FALSE if it
is not.
Review Comment:
need to escape `=>`?
spaces around `[` and `]`
trailing period
replace 'a value' with '*string*'
'Performs' is a zero-information word. 'Returns whether...'.
Remove the 'Returns TRUE ...' sentence. It is covered by previous
description. Also it implies (wrongly?) that the function can never return
UNKNOWN.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
