This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 8ddd84f213 [CALCITE-6002] CONTAINS_SUBSTR does not unparse correctly
8ddd84f213 is described below
commit 8ddd84f2135d7e2c9bf4b2b03ab5b9193e89e434
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Aug 4 22:01:54 2026 -0700
[CALCITE-6002] CONTAINS_SUBSTR does not unparse correctly
Signed-off-by: Mihai Budiu <[email protected]>
---
.../org/apache/calcite/runtime/SqlFunctions.java | 2 +-
.../calcite/sql/fun/SqlContainsSubstrFunction.java | 60 ++++++++++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 6 +--
.../calcite/test/SqlOperatorUnparseTest.java | 6 ---
.../apache/calcite/sql/parser/SqlParserTest.java | 10 ++++
.../java/org/apache/calcite/sql/test/SqlTests.java | 2 +-
6 files changed, 74 insertions(+), 12 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 15d526393b..cf80e7fc0e 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1122,7 +1122,7 @@ public static List<ByteString> split(ByteString s,
ByteString delimiter) {
return nullFlag ? null : false;
}
- /** SQL <code>CONTAINS_SUBSTR(jsonString, substr,
json_scope=>jsonScope)</code>
+ /** SQL <code>CONTAINS_SUBSTR(jsonString, substr, json_scope =>
jsonScope)</code>
* operator. */
public static boolean containsSubstr(String jsonString, String substr,
String jsonScope) {
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlContainsSubstrFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlContainsSubstrFunction.java
new file mode 100644
index 0000000000..b3e2e50ac4
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlContainsSubstrFunction.java
@@ -0,0 +1,60 @@
+/*
+ * 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.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.SqlWriter;
+import org.apache.calcite.sql.type.OperandTypes;
+import org.apache.calcite.sql.type.ReturnTypes;
+
+/**
+ * Definition of the "CONTAINS_SUBSTR(expression, string [, json_scope =>
+ * json_scope_value ])" function; returns whether string exists as a
+ * substring in expression.
+ */
+public class SqlContainsSubstrFunction extends SqlFunction {
+ public SqlContainsSubstrFunction() {
+ super("CONTAINS_SUBSTR", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.BOOLEAN_NULLABLE, null,
+ OperandTypes.ANY_STRING_OPTIONAL_STRING,
+ SqlFunctionCategory.STRING);
+ }
+
+ /**
+ * The parser only accepts the optional third operand with the named
+ * syntax "json_scope => json_scope_value", both in Calcite and in
+ * BigQuery, so {@code unparse} must emit "JSON_SCOPE =>" before it.
+ */
+ @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
+ int rightPrec) {
+ final SqlWriter.Frame frame = writer.startFunCall(getName());
+ writer.sep(",");
+ call.operand(0).unparse(writer, 0, 0);
+ writer.sep(",");
+ call.operand(1).unparse(writer, 0, 0);
+ if (call.operandCount() == 3) {
+ writer.sep(",");
+ writer.keyword("JSON_SCOPE");
+ writer.keyword("=>");
+ call.operand(2).unparse(writer, 0, 0);
+ }
+ writer.endFunCall(frame);
+ }
+}
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 539d6b327f..089f20eec9 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
@@ -1069,14 +1069,12 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
ReturnTypes.INTEGER_NULLABLE, OperandTypes.DATE,
SqlFunctionCategory.TIMEDATE);
- /** "CONTAINS_SUBSTR(expression, string[, json_scope =>
json_scope_value ])"
+ /** "CONTAINS_SUBSTR(expression, string[, json_scope => json_scope_value
])"
* function; returns whether string exists as substring in expression, with
optional
* json_scope argument. */
@LibraryOperator(libraries = {BIG_QUERY})
public static final SqlFunction CONTAINS_SUBSTR =
- SqlBasicFunction.create("CONTAINS_SUBSTR",
- ReturnTypes.BOOLEAN_NULLABLE,
OperandTypes.ANY_STRING_OPTIONAL_STRING,
- SqlFunctionCategory.STRING);
+ new SqlContainsSubstrFunction();
/** The "MONTHNAME(datetime)" function; returns the name of the month,
* in the current locale, of a TIMESTAMP or DATE argument. */
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlOperatorUnparseTest.java
b/core/src/test/java/org/apache/calcite/test/SqlOperatorUnparseTest.java
index b382a6412a..b4e4e66769 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlOperatorUnparseTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlOperatorUnparseTest.java
@@ -114,10 +114,4 @@ String rewrite(StringAndPos sap) throws SqlParseException {
void testSafeOffsetOperator() {
super.testSafeOffsetOperator();
}
-
- @Override @Disabled("https://issues.apache.org/jira/browse/CALCITE-6002 "
- + "CONTAINS_SUBSTR does not unparse correctly")
- void testContainsSubstrFunc() {
- super.testContainsSubstrFunc();
- }
}
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 394aecf26f..beae2eb7bf 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -7657,6 +7657,16 @@ private static Consumer<List<? extends Throwable>>
checkWarnings(
.ok("CAST(`X` AS VARBINARY)");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6002";>[CALCITE-6002]
+ * CONTAINS_SUBSTR does not unparse correctly</a>. */
+ @Test void testContainsSubstr() {
+ expr("CONTAINS_SUBSTR(x, 'a')")
+ .ok("CONTAINS_SUBSTR(`X`, 'a')");
+ expr("CONTAINS_SUBSTR(x, 'a', json_scope=>'JSON_KEYS')")
+ .ok("CONTAINS_SUBSTR(`X`, 'a', JSON_SCOPE => 'JSON_KEYS')");
+ }
+
@Test void testTimestampAdd() {
final String sql = "select * from t\n"
+ "where timestampadd(month, 5, hiredate) < curdate";
diff --git a/testkit/src/main/java/org/apache/calcite/sql/test/SqlTests.java
b/testkit/src/main/java/org/apache/calcite/sql/test/SqlTests.java
index 7831767acd..2cfaf8fc87 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/SqlTests.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/SqlTests.java
@@ -395,7 +395,7 @@ public static void checkEx(@Nullable Throwable ex,
if (sap.pos == null) {
throw new AssertionError("Actual error had a position, but expected "
+ "error did not. Add error position carets to sql:\n"
- + sqlWithCarets);
+ + sqlWithCarets, actualException);
}
}
