This is an automated email from the ASF dual-hosted git repository.
cloud-fan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new dd4b74184e36 [SPARK-57436][SQL] Support start and occurrence
parameters in instr function
dd4b74184e36 is described below
commit dd4b74184e36625aff54d3695a134839a9721bd3
Author: loftiest <[email protected]>
AuthorDate: Tue Jun 30 21:37:06 2026 +0800
[SPARK-57436][SQL] Support start and occurrence parameters in instr function
### What changes were proposed in this pull request?
This PR extends the `instr` function to accept optional `start` and
`occurrence` parameters, supporting forward and backward search with full
collation awareness. The changes include:
- A new expression `StringInstrWithOccurrence` that handles 3‑ and
4‑argument invocations, while the existing two‑argument `instr` continues to
use `StringInstr`.
- A new `StringInstrExpressionBuilder` that routes calls based on the
number of arguments: 2 arguments → `StringInstr`, 3 or 4 arguments →
`StringInstrWithOccurrence`.
- Extended `UTF8String.indexOf(pattern, start, occurrence)` with both
forward (positive `start`) and backward (negative `start`) search, using
efficient single‑pass UTF‑8 byte scanning.
- Extended `CollationAwareUTF8String.lowercaseIndexOf` and ICU `indexOf` to
support multi‑occurrence search for `UTF8_BINARY_LCASE` and ICU collations.
- Updated `FunctionRegistry` to use the new `StringInstrExpressionBuilder`.
- New overloads in `functions.scala` and corresponding Python APIs
(`pyspark.sql.functions.builtin`) with proper default‑parameter handling.
- New error class `INVALID_PARAMETER_VALUE.OCCURRENCE` for invalid (≤ 0)
occurrence values.
- Comprehensive unit tests covering binary, lowercase, and ICU collations,
forward/backward/overlapping searches, multi‑byte characters, and boundary
conditions.
- Registered `StringInstrWithOccurrence` in `CollationTypeCoercion` to
align collation handling for multi-arg `instr`.
- Added `StringInstrWithOccurrence` to the `ResolverGuard` allowlist so
Spark Connect and the hybrid analyzer support it.
The behavior when `substr` is empty remains unchanged (returns `start`
position), preserving backward compatibility with the existing two‑argument
`instr`.
### Why are the changes needed?
Currently, Spark's `instr` only supports two arguments, forcing users to
write complex workarounds when they need to start searching from a specific
position or find a particular occurrence. Oracle, Impala, and Db2 all provide a
four‑argument `INSTR` with these capabilities. This PR aligns Spark SQL with
those databases, simplifies user queries, and reduces migration friction.
### Does this PR introduce _any_ user-facing change?
Yes, but only additive.
- New SQL syntax: `instr(str, substr [, start [, occurrence]])` where
`start` and `occurrence` are optional integers (default 1). Negative `start`
performs backward search.
- New Scala/Python API overloads with corresponding optional parameters.
- An `INVALID_PARAMETER_VALUE.OCCURRENCE` error is thrown unconditionally
when `occurrence <= 0`.
- All existing two‑argument usage remains completely unaffected.
Documentation for the new parameters is included in the expression
description and will be surfaced in SQL function docs.
### How was this patch tested?
- Added extensive unit tests to `StringFunctionsSuite`,
`CollationAwareUTF8StringSuite`, and `UTF8StringSuite` covering:
- Forward and backward search with multiple occurrences
- Overlapping matches (e.g., `'aa'` in `'aaa'`)
- Multi‑byte and supplementary characters
- Collation scenarios: `UTF8_BINARY`, `UTF8_BINARY_LCASE`, `UNICODE_CI`
(including sigma variants, German `ß`, and Turkish `İ` expansion)
- Boundary conditions (`start = 0`, `occurrence <= 0`, `start` out of
range, empty substring)
- Verified Python API behavior matches SQL execution through existing
PySpark doctests.
### Was this patch authored or co-authored using generative AI tooling?
DeepSeek
Closes #56498 from loftiest/instr-4parameters.
Authored-by: loftiest <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
---
.../catalyst/util/CollationAwareUTF8String.java | 135 +++++++
.../spark/sql/catalyst/util/CollationSupport.java | 39 ++
.../org/apache/spark/unsafe/types/UTF8String.java | 108 ++++++
.../spark/unsafe/types/CollationSupportSuite.java | 399 +++++++++++++++++++++
.../apache/spark/unsafe/types/UTF8StringSuite.java | 64 ++++
.../src/main/resources/error/error-conditions.json | 5 +
python/pyspark/sql/connect/functions/builtin.py | 17 +-
python/pyspark/sql/functions/builtin.py | 53 ++-
.../sql/tests/connect/test_connect_function.py | 8 +
.../scala/org/apache/spark/sql/functions.scala | 72 ++++
.../catalyst/analysis/CollationTypeCoercion.scala | 5 +-
.../sql/catalyst/analysis/FunctionRegistry.scala | 2 +-
.../catalyst/analysis/resolver/ResolverGuard.scala | 2 +-
.../catalyst/expressions/stringExpressions.scala | 109 +++++-
.../spark/sql/errors/QueryExecutionErrors.scala | 11 +
.../expressions/StringExpressionsSuite.scala | 84 +++++
.../sql-functions/sql-expression-schema.md | 2 +-
.../collations-string-functions.sql.out | 84 +++++
.../nonansi/string-functions.sql.out | 294 +++++++++++++++
.../analyzer-results/string-functions.sql.out | 294 +++++++++++++++
.../inputs/collations-string-functions.sql | 7 +
.../sql-tests/inputs/string-functions.sql | 54 ++-
.../results/collations-string-functions.sql.out | 158 ++++++++
.../results/nonansi/string-functions.sql.out | 336 +++++++++++++++++
.../sql-tests/results/string-functions.sql.out | 336 +++++++++++++++++
.../apache/spark/sql/StringFunctionsSuite.scala | 41 +++
26 files changed, 2699 insertions(+), 20 deletions(-)
diff --git
a/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationAwareUTF8String.java
b/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationAwareUTF8String.java
index c9fee02125fd..28c5a9f8473d 100644
---
a/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationAwareUTF8String.java
+++
b/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationAwareUTF8String.java
@@ -809,6 +809,77 @@ public class CollationAwareUTF8String {
return lowercaseFind(target, lowerCaseCodePoints(pattern), start);
}
+ /**
+ * Returns the position of the {@code occurrence}-th occurrence of the
pattern string in the
+ * target string, starting from the specified position (1-based index
referring to character
+ * position in UTF8String), with respect to the UTF8_LCASE collation. If the
pattern is not
+ * found, {@code MATCH_NOT_FOUND} is returned.
+ *
+ * @param target the string to be searched in
+ * @param pattern the string to be searched for
+ * @param start the start position for searching (1-based, can be negative
for backward search)
+ * @param occurrence which occurrence to return (must be >= 1)
+ * @return the position of the {@code occurrence}-th occurrence of pattern
in target
+ * (0-based character index), or {@code MATCH_NOT_FOUND} if not found
+ */
+ public static int lowercaseIndexOf(final UTF8String target, final UTF8String
pattern,
+ final int start, final int occurrence) {
+ assert occurrence > 0;
+ if (pattern.numBytes() == 0) return target.indexOfEmpty(start);
+ if (start == 0) return MATCH_NOT_FOUND;
+ if (target.isFullAscii() && pattern.isFullAscii()) {
+ return target.toLowerCase().indexOf(pattern.toLowerCase(), start,
occurrence);
+ }
+ return lowercaseIndexOfSlow(target, pattern, start, occurrence);
+ }
+
+ private static int lowercaseIndexOfSlow(final UTF8String target, final
UTF8String pattern,
+ final int start, final int occurrence) {
+ assert start != 0; // start is char index, 0 is not allowed
+
+ int startIdx; // character index
+ if (start > 0) {
+ startIdx = start - 1; // 1-based to 0-based (left side)
+ if (startIdx >= target.numChars()) return MATCH_NOT_FOUND;
+ }
+ else {
+ startIdx = target.numChars() + start; // 1-based to 0-based (right side)
+ if (startIdx < 0) return MATCH_NOT_FOUND;
+ }
+
+ UTF8String lowercasePattern = lowerCaseCodePoints(pattern);
+ int remaining = occurrence;
+ int searchIdx = -1;
+ if (start > 0) {
+ // Forward search
+ while (remaining > 0) {
+ // Find the starting position of the first occurrence of
+ // lowercasePattern in target from startIdx onward.
+ searchIdx = lowercaseFind(target, lowercasePattern, startIdx);
+ if (searchIdx < 0) return MATCH_NOT_FOUND;
+ remaining--;
+ startIdx = searchIdx + 1;
+ }
+ } else {
+ // Convert startIdx to the exclusive right boundary needed by
lowercaseMatchLengthUntil.
+ startIdx = startIdx + lowercasePattern.numChars();
+ if (startIdx > target.numChars()) startIdx = target.numChars();
+ // Backward search
+ while (remaining > 0) {
+ // Find the matching length (substring in the target) of the first
+ // occurrence of lowercasePattern from startIdx (not included) in the
target.
+ int matchLength = lowercaseMatchLengthUntil(target, lowercasePattern,
startIdx);
+ if (matchLength != MATCH_NOT_FOUND) {
+ remaining--;
+ if (remaining == 0) searchIdx = startIdx - matchLength;
+ }
+ startIdx--;
+ if (startIdx < 0) return MATCH_NOT_FOUND;
+ }
+ }
+ return searchIdx;
+ }
+
public static int indexOf(final UTF8String target, final UTF8String pattern,
final int start, final int collationId) {
if (pattern.numBytes() == 0) return target.indexOfEmpty(start);
@@ -840,6 +911,57 @@ public class CollationAwareUTF8String {
return indexOf;
}
+ /**
+ * Returns the position of the {@code occurrence}-th occurrence of the
pattern string in the
+ * target string, starting from the specified position (1-based index
referring to character
+ * position in UTF8String), with respect to the ICU collation identified by
{@code collationId}.
+ * If the pattern is not found, {@code MATCH_NOT_FOUND} is returned.
+ *
+ * @param target the string to be searched in
+ * @param pattern the string to be searched for
+ * @param start the start position for searching (1-based, can be negative
for backward search)
+ * @param occurrence which occurrence to return (must be >= 1)
+ * @param collationId the ICU collation identifier
+ * @return the position of the {@code occurrence}-th occurrence of pattern
in target
+ * (0-based code point index), or {@code MATCH_NOT_FOUND} if not
found
+ */
+ public static int indexOf(final UTF8String target, final UTF8String pattern,
+ final int start, final int occurrence, final int collationId) {
+ assert occurrence > 0;
+ if (pattern.numBytes() == 0) return target.indexOfEmpty(start);
+ if (target.numBytes() == 0 || start == 0) return MATCH_NOT_FOUND;
+
+ String targetStr = target.toValidString();
+ String patternStr = pattern.toValidString();
+
+ // Adjust the starting position from 1-based to 0-based.
+ int realStart;
+ if (start > 0) {
+ realStart = start - 1;
+ if (targetStr.codePointCount(0, targetStr.length()) <= realStart) return
MATCH_NOT_FOUND;
+ } else {
+ realStart = targetStr.codePointCount(0, targetStr.length()) + start + 1;
+ if (realStart < 0) return MATCH_NOT_FOUND;
+ }
+
+ StringSearch stringSearch =
+ CollationFactory.getStringSearch(targetStr, patternStr,
collationId);
+ // Set Overlapping to true to support finding locations
+ // similar to the second occurrence of 'aa' in 'aaa'.
+ stringSearch.setOverlapping(true);
+ int startIndex = targetStr.offsetByCodePoints(0, realStart);
+ stringSearch.setIndex(startIndex);
+
+ // Search for target index.
+ int searchIndex;
+ if (start > 0) searchIndex = findIndex(stringSearch, occurrence);
+ else searchIndex = findStartIndexReverse(stringSearch, occurrence);
+
+ if (searchIndex == MATCH_NOT_FOUND) return MATCH_NOT_FOUND;
+ // Convert the search index from character count to code point count.
+ return targetStr.codePointCount(0, searchIndex);
+ }
+
private static int findIndex(final StringSearch stringSearch, int count) {
assert(count >= 0);
int index = 0;
@@ -870,6 +992,19 @@ public class CollationAwareUTF8String {
return index + stringSearch.getMatchLength();
}
+ private static int findStartIndexReverse(final StringSearch stringSearch,
int count) {
+ assert(count >= 0);
+ int index = 0;
+ while (count > 0) {
+ index = stringSearch.previous();
+ if (index == StringSearch.DONE) {
+ return MATCH_NOT_FOUND;
+ }
+ count--;
+ }
+ return index;
+ }
+
public static UTF8String subStringIndex(final UTF8String string, final
UTF8String delimiter,
int count, final int collationId) {
if (delimiter.numBytes() == 0 || count == 0 || string.numBytes() == 0) {
diff --git
a/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationSupport.java
b/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationSupport.java
index f950fd864c57..f3f3fe3449a9 100644
---
a/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationSupport.java
+++
b/common/unsafe/src/main/java/org/apache/spark/sql/catalyst/util/CollationSupport.java
@@ -685,6 +685,45 @@ public final class CollationSupport {
}
}
+ public static class StringInstrWithOccurrence {
+ public static int exec(final UTF8String string, UTF8String substring,
+ final int start, final int occurrence, final int collationId) {
+ CollationFactory.Collation collation =
CollationFactory.fetchCollation(collationId);
+ if (collation.supportsSpaceTrimming) {
+ substring = CollationFactory.applyTrimmingPolicy(substring,
collationId);
+ }
+ if (collation.isUtf8BinaryType) {
+ return execBinary(string, substring, start, occurrence);
+ } else if (collation.isUtf8LcaseType) {
+ return execLowercase(string, substring, start, occurrence);
+ } else {
+ return execICU(string, substring, start, occurrence, collationId);
+ }
+ }
+ public static String genCode(final String string, final String substring,
+ final String start, final String occurrence, final int collationId) {
+ String expr = "CollationSupport.StringInstrWithOccurrence.exec";
+ if (collationId == CollationFactory.UTF8_BINARY_COLLATION_ID) {
+ return String.format(expr + "Binary(%s, %s, %s, %s)", string,
substring, start, occurrence);
+ } else {
+ return String.format(expr + "(%s, %s, %s, %s, %d)",
+ string, substring, start, occurrence, collationId);
+ }
+ }
+ public static int execBinary(final UTF8String string, final UTF8String
substring,
+ final int start, final int occurrence) {
+ return string.indexOf(substring, start, occurrence);
+ }
+ public static int execLowercase(final UTF8String string, final UTF8String
substring,
+ final int start, final int occurrence) {
+ return CollationAwareUTF8String.lowercaseIndexOf(string, substring,
start, occurrence);
+ }
+ public static int execICU(final UTF8String string, final UTF8String
substring,
+ final int start, final int occurrence, final int collationId) {
+ return CollationAwareUTF8String.indexOf(string, substring, start,
occurrence, collationId);
+ }
+ }
+
// TODO: Add more collation-aware string expressions.
/**
diff --git
a/common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java
b/common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java
index f8d704193390..49429413904c 100644
--- a/common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java
+++ b/common/unsafe/src/main/java/org/apache/spark/unsafe/types/UTF8String.java
@@ -1262,6 +1262,114 @@ public final class UTF8String implements
Comparable<UTF8String>, Externalizable,
return -1;
}
+ /**
+ * Finds the {@code occurrence}-th occurrence of {@code pattern} in this
string,
+ * starting the search at the specified position.
+ * When {@code start} is positive, the search proceeds forward from the
+ * {@code start}-th character (1-based). When {@code start} is negative, the
+ * search proceeds backward: {@code start} specifies the first character to
+ * compare, counting from the end of the string. For example,
+ * {@code start = -3} points at the 3rd character from the end, and the first
+ * candidate substring is the one that begins at that character.
+ * Overlapping matches are supported (e.g. "aa" in "aaa" returns 0, 1 for
+ * occurrence 1, 2 respectively).
+ *
+ * @param pattern the substring to search for
+ * @param start 1-based start position; if negative, search direction
is reversed
+ * @param occurrence which occurrence to return (must be >= 1)
+ * @return 0-based character index of the match, or -1 if not found
+ */
+ public int indexOf(UTF8String pattern, int start, int occurrence) {
+ assert occurrence > 0;
+ if (pattern.numBytes() == 0) {
+ return indexOfEmpty(start);
+ }
+ if (start == 0) {
+ return -1;
+ }
+
+ int charCount = 0;
+ int charsToSkip, byteIdx;
+ if (start > 0) {
+ byteIdx = 0; // position in byte
+ charsToSkip = start - 1; // skip character count
+ while (byteIdx < numBytes && charCount < charsToSkip) {
+ byteIdx += numBytesForFirstByte(getByte(byteIdx));
+ charCount += 1;
+ }
+ } else {
+ // For negative start, skip |start| characters from the end to position
+ // byteIdx at the starting byte of the first character to compare.
+ charsToSkip = -start;
+ byteIdx = numBytes;
+ while (byteIdx > 0 && charCount < charsToSkip) {
+ byteIdx = prevCharStart(byteIdx);
+ charCount++;
+ }
+ }
+ // If start position is out of range, return -1 immediately
+ if (charCount < charsToSkip) return -1;
+
+ // For forward search, byteIdx is the starting byte offset of the current
character,
+ // and charCount tracks the 0-based character index of that character.
+ // For backward search, byteIdx points to the starting byte of the current
candidate match.
+ if (start > 0) {
+ // Search for the occurrence-th match, starting from the current byteIdx.
+ while (occurrence > 0) {
+ // If byteIdx equals numBytes, it indicates that we have
+ // reached the end of the string, yet we still enter the loop
+ // and return -1 when the 'not found' condition is met.
+ while (byteIdx <= numBytes) {
+ if (pattern.numBytes + byteIdx > numBytes) {
+ return -1;
+ }
+
+ if (ByteArrayMethods.arrayEquals(base, offset + byteIdx,
+ pattern.base, pattern.offset, pattern.numBytes)) {
+ break;
+ }
+ byteIdx += numBytesForFirstByte(getByte(byteIdx));
+ charCount += 1;
+ }
+
+ occurrence--;
+ if (occurrence == 0) return charCount;
+
+ byteIdx += numBytesForFirstByte(getByte(byteIdx));
+ charCount += 1;
+ }
+ } else {
+ // trying to match pattern starting at byteIdx, scanning leftwards
+ while (occurrence > 0) {
+ while (byteIdx >= 0) {
+ // Only attempt to match if there is enough room for the pattern.
+ if (byteIdx + pattern.numBytes <= numBytes &&
+ ByteArrayMethods.arrayEquals(base, offset + byteIdx, pattern.base,
pattern.offset,
+ pattern.numBytes)) {
+ break;
+ }
+ byteIdx = prevCharStart(byteIdx);
+ }
+ if (byteIdx < 0) return -1;
+
+ occurrence--;
+ if (occurrence == 0) return bytePosToChar(byteIdx);
+
+ byteIdx = prevCharStart(byteIdx);
+ }
+ }
+ return -1;
+ }
+
+ private int prevCharStart(int byteIdx) {
+ byteIdx--;
+ // Skip UTF-8 continuation bytes to reach the start of a character
+ while (byteIdx > 0 && (getByte(byteIdx) & 0xC0) == 0x80) {
+ byteIdx--;
+ }
+ return byteIdx;
+ }
+
public int charPosToByte(int charPos) {
if (charPos < 0) {
return -1;
diff --git
a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CollationSupportSuite.java
b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CollationSupportSuite.java
index 6372d7e4663c..cd5b0f0ff962 100644
---
a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CollationSupportSuite.java
+++
b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/CollationSupportSuite.java
@@ -3916,5 +3916,404 @@ public class CollationSupportSuite {
return dict;
}
+ private void assertStringInstrWithOccurrence(String string, String
substring, int start,
+ int occurrence, String collationName, int expected) throws
SparkException {
+ UTF8String str = UTF8String.fromString(string);
+ UTF8String substr = UTF8String.fromString(substring);
+ int collationId = CollationFactory.collationNameToId(collationName);
+ int res = CollationSupport.StringInstrWithOccurrence.exec(
+ str, substr, start, occurrence, collationId) + 1;
+ assertEquals(expected, res);
+ }
+
+ @Test
+ public void testStringInstrWithOccurrence() throws SparkException {
+ // Test start = 1 and occurrence = 1 (equivalent to StringInstr)
+ // Empty strings.
+ assertStringInstrWithOccurrence("", "", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("", "", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("", "", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("", "", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("a", "", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("a", "", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("a", "", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("a", "", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("", "x", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("", "x", 1, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("", "x", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("", "x", 1, 1, UNICODE_CI, 0);
+ // Basic tests.
+ assertStringInstrWithOccurrence("aaads", "aa", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("aaads", "aa", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("aaads", "aa", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("aaads", "aa", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("aaads", "ds", 1, 1, UTF8_BINARY, 4);
+ assertStringInstrWithOccurrence("aaads", "ds", 1, 1, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("aaads", "ds", 1, 1, UNICODE, 4);
+ assertStringInstrWithOccurrence("aaads", "ds", 1, 1, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("aaads", "Aa", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("aaads", "Aa", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("aaads", "Aa", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("aaads", "Aa", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("aaaDs", "de", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("aaaDs", "de", 1, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("aaaDs", "de", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("aaaDs", "de", 1, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("aaaDs", "ds", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("aaaDs", "ds", 1, 1, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("aaaDs", "ds", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("aaaDs", "ds", 1, 1, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("aaadS", "Ds", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("aaadS", "Ds", 1, 1, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("aaadS", "Ds", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("aaadS", "Ds", 1, 1, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("aaaČŠčšcs", "cs", 1, 1, "SR", 8);
+ assertStringInstrWithOccurrence("aaaČŠčšcs", "cs", 1, 1, "SR_CI_AI", 4);
+ // Advanced tests.
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "大千", 1, 1, UTF8_BINARY,
5);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "大千", 1, 1, UTF8_LCASE,
5);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "大千", 1, 1, UNICODE, 5);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "大千", 1, 1, UNICODE_CI,
5);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界X", 1, 1, UTF8_BINARY,
8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界X", 1, 1, UTF8_LCASE,
8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界X", 1, 1, UNICODE, 8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界X", 1, 1, UNICODE_CI,
8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界x", 1, 1, UTF8_BINARY,
0);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界x", 1, 1, UTF8_LCASE,
8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界x", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界x", 1, 1, UNICODE_CI,
8);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界y", 1, 1, UTF8_BINARY,
0);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界y", 1, 1, UTF8_LCASE,
0);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界y", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("test大千世界X大千世界", "界y", 1, 1, UNICODE_CI,
0);
+ // One-to-many case mapping (e.g. Turkish dotted I).
+ assertStringInstrWithOccurrence("i\u0307", "i", 1, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("i\u0307", "\u0307", 1, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("i\u0307", "İ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("İ", "i", 1, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("İoi̇o12", "i\u0307o", 1, 1, UNICODE_CI,
1);
+ assertStringInstrWithOccurrence("i̇oİo12", "İo", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("abİoi̇o", "i\u0307o", 1, 1, UNICODE_CI,
3);
+ assertStringInstrWithOccurrence("abi̇oİo", "İo", 1, 1, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("ai̇oxXİo", "Xx", 1, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("aİoi̇oxx", "XX", 1, 1, UNICODE_CI, 7);
+ assertStringInstrWithOccurrence("i\u0307", "i", 1, 1, UTF8_LCASE, 1); //
!= UNICODE_CI
+ assertStringInstrWithOccurrence("i\u0307", "\u0307", 1, 1, UTF8_LCASE, 2);
// != UNICODE_CI
+ assertStringInstrWithOccurrence("i\u0307", "İ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("İ", "i", 1, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("İoi̇o12", "i\u0307o", 1, 1, UTF8_LCASE,
1);
+ assertStringInstrWithOccurrence("i̇oİo12", "İo", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("abİoi̇o", "i\u0307o", 1, 1, UTF8_LCASE,
3);
+ assertStringInstrWithOccurrence("abi̇oİo", "İo", 1, 1, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("abI\u0307oi̇o", "İo", 1, 1, UTF8_LCASE,
3);
+ assertStringInstrWithOccurrence("ai̇oxXİo", "Xx", 1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("abİoi̇o", "\u0307o", 1, 1, UTF8_LCASE, 6);
+ assertStringInstrWithOccurrence("aİoi̇oxx", "XX", 1, 1, UTF8_LCASE, 7);
+ // Conditional case mapping (e.g. Greek sigmas).
+ assertStringInstrWithOccurrence("σ", "σ", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("σ", "ς", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("σ", "Σ", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("ς", "σ", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("ς", "ς", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("ς", "Σ", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("Σ", "σ", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("Σ", "ς", 1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("Σ", "Σ", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("σ", "σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("σ", "ς", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("σ", "Σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("ς", "σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("ς", "ς", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("ς", "Σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("Σ", "σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("Σ", "ς", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("Σ", "Σ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("σ", "σ", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("σ", "ς", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("σ", "Σ", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("ς", "σ", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("ς", "ς", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("ς", "Σ", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("Σ", "σ", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("Σ", "ς", 1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("Σ", "Σ", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("σ", "σ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("σ", "ς", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("σ", "Σ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("ς", "σ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("ς", "ς", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("ς", "Σ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("Σ", "σ", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("Σ", "ς", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("Σ", "Σ", 1, 1, UNICODE_CI, 1);
+ // Surrogate pairs.
+ assertStringInstrWithOccurrence("a🙃b", "a", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("a🙃b", "a", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("a🙃b", "a", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("a🙃b", "a", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("a🙃b", "🙃", 1, 1, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence("a🙃b", "🙃", 1, 1, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("a🙃b", "🙃", 1, 1, UNICODE, 2);
+ assertStringInstrWithOccurrence("a🙃b", "🙃", 1, 1, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("a🙃b", "b", 1, 1, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("a🙃b", "b", 1, 1, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("a🙃b", "b", 1, 1, UNICODE, 3);
+ assertStringInstrWithOccurrence("a🙃b", "b", 1, 1, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("a🙃🙃b", "🙃", 1, 1, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence("a🙃🙃b", "🙃", 1, 1, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("a🙃🙃b", "🙃", 1, 1, UNICODE, 2);
+ assertStringInstrWithOccurrence("a🙃🙃b", "🙃", 1, 1, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("a🙃🙃b", "b", 1, 1, UTF8_BINARY, 4);
+ assertStringInstrWithOccurrence("a🙃🙃b", "b", 1, 1, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("a🙃🙃b", "b", 1, 1, UNICODE, 4);
+ assertStringInstrWithOccurrence("a🙃🙃b", "b", 1, 1, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("a🙃x🙃b", "b", 1, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("a🙃x🙃b", "b", 1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("a🙃x🙃b", "b", 1, 1, UNICODE, 5);
+ assertStringInstrWithOccurrence("a🙃x🙃b", "b", 1, 1, UNICODE_CI, 5);
+
+ // Test start != 1 or occurrence != 1
+ // Forward, occurrence > 1
+ assertStringInstrWithOccurrence("abcabc", "b", 1, 2, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 1, 2, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 1, 2, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 1, 2, UNICODE, 5);
+ // Forward with start > 1
+ assertStringInstrWithOccurrence("abcabc", "b", 3, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 3, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 3, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", 3, 1, UNICODE, 5);
+ // Forward, occurrence > 1, start > 1
+ assertStringInstrWithOccurrence("abcbabc", "b", 2, 2, UTF8_BINARY, 4);
+ assertStringInstrWithOccurrence("abcbabc", "b", 2, 2, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("abcbabc", "b", 2, 2, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("abcbabc", "b", 2, 2, UNICODE, 4);
+ // Not found due to excessive occurrence
+ assertStringInstrWithOccurrence("abc", "b", 1, 2, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("abc", "b", 1, 2, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("abc", "b", 1, 2, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("abc", "b", 1, 2, UNICODE, 0);
+ // Negative start, occurrence=1
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 1, UNICODE, 5);
+ // Negative start, occurrence=2 (find second from right)
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 2, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 2, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 2, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -1, 2, UNICODE, 2);
+ // Negative start, not at the end
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 1, UNICODE, 5);
+ // Negative start, occurrence=2, start=-2
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 2, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 2, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 2, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("abcabc", "b", -2, 2, UNICODE, 2);
+ // Backward lookup of multibyte characters
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 1, UNICODE, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 2, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 2, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 2, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -1, 2, UNICODE, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 1, UNICODE, 5);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 2, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 2, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 2, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("你好世界你好", "你好", -2, 2, UNICODE, 1);
+ // Reverse lookup under case sensitivity/insensitivity
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -1, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -1, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -1, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -1, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -2, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -2, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "Bc", -2, 1, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -2, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -2, 1, UTF8_LCASE, 5);
+ assertStringInstrWithOccurrence("AbCaBc", "bc", -2, 1, UNICODE_CI, 5);
+ // Forward, occurrence = 2, with UNICODE_CI / UTF8_LCASE
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", 1, 2, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "i\u0307o", 1, 2,
UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", 1, 2, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "i\u0307o", 1, 2,
UTF8_LCASE, 3);
+ // Backward, occurrence = 1 & 2
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -1, 1, UNICODE_CI,
6);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -1, 2, UNICODE_CI,
3);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -1, 2, UTF8_LCASE,
3);
+ // start = -2 (search left from the second-last character)
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -2, 1, UNICODE_CI,
6);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -2, 2, UNICODE_CI,
3);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -2, 1, UTF8_LCASE,
6);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -2, 2, UTF8_LCASE,
3);
+ // Boundary: occurrence exceeds actual count
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", 1, 4, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -1, 4, UTF8_LCASE,
0);
+ // Boundary: start = 0
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", 0, 1, UNICODE_CI, 0);
+ // Boundary: start out of range (forward/backward)
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", 10, 1, UTF8_LCASE,
0);
+ assertStringInstrWithOccurrence("İoi\u0307oİo", "İo", -10, 1, UNICODE_CI,
0);
+ String sigmaStr = "σΣςσΣς"; // 1:σ, 2:Σ, 3:ς, 4:σ, 5:Σ, 6:ς
+ // UTF8_BINARY: all sigma forms are distinct, only exact byte matches
succeed
+ assertStringInstrWithOccurrence("σΣςσΣς", "Σ", 1, 2, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("σΣςσΣς", "ς", 1, 1, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("σΣςσΣς", "ς", 1, 2, UTF8_BINARY, 6);
+ assertStringInstrWithOccurrence("σΣςσΣς", "ς", -2, 1, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("σΣςσΣς", "ς", -1, 1, UTF8_BINARY, 6);
+ // UNICODE_CI: all sigma forms are equivalent
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 1, 2, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence(sigmaStr, "Σ", 1, 3, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence(sigmaStr, "ς", 1, 4, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -1, 1, UNICODE_CI, 6);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -1, 2, UNICODE_CI, 5);
+ assertStringInstrWithOccurrence(sigmaStr, "Σ", -2, 1, UNICODE_CI, 5);
+ // UTF8_LCASE: also case-insensitive, sigma forms match
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 1, 2, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -1, 1, UTF8_LCASE, 6);
+ // UNICODE: σ, ς, Σ are treated as distinct
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 1, 2, UNICODE, 4);
+ assertStringInstrWithOccurrence(sigmaStr, "ς", -1, 1, UNICODE, 6);
+ assertStringInstrWithOccurrence(sigmaStr, "ς", -1, 2, UNICODE, 3);
+ // Boundary: occurrence too large
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 1, 7, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 1, 3, UNICODE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -1, 7, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -1, 7, UTF8_BINARY, 0);
+ // Boundary: start = 0
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 0, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 0, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 0, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 0, 1, UTF8_BINARY, 0);
+ // Boundary: start out of range
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 7, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -7, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 7, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -7, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -7, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", -7, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence(sigmaStr, "σ", 7, 1, UTF8_BINARY, 0);
+ // surrogate pairs, occurrence > 1
+ String emojiStr = "a🙃🙃b🙃c";
+ assertStringInstrWithOccurrence(emojiStr, "🙃", 1, 1, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", 1, 2, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", 1, 3, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", 1, 4, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -1, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -1, 2, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -1, 3, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -1, 4, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", 1, 2, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -2, 1, UNICODE, 5);
+ assertStringInstrWithOccurrence(emojiStr, "🙃", -2, 1, UTF8_BINARY, 5);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 1, UTF8_LCASE, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 1, UNICODE, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 1, UNICODE_CI, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 2, UTF8_BINARY, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 2, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 2, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -1, 2, UNICODE, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "y🙃", -1, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "y🙃", -1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "y🙃", -1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "y🙃", -1, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 1, 4, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 1, 4, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 1, 4, UNICODE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 1, 4, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -1, 4, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -1, 4, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -1, 4, UNICODE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -1, 4, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 3, 2, UTF8_LCASE, 7);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 3, 2, UTF8_BINARY, 7);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 3, 2, UNICODE, 7);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 3, 2, UNICODE_CI, 7);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -3, 2, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -3, 2, UTF8_BINARY, 2);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -3, 2, UNICODE, 2);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -3, 2, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 8, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 8, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 8, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", 8, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -8, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -8, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -8, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "🙃", -8, 1, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 1, UTF8_LCASE, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 1, UNICODE, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 1, UNICODE_CI, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 1, UTF8_BINARY, 6);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 2, UTF8_LCASE, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 2, UNICODE, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 2, UNICODE_CI, 4);
+ assertStringInstrWithOccurrence("x🙃yz🙃z🙃", "z🙃", -2, 2, UTF8_BINARY, 4);
+ // Empty substring with negative start, occurrence >= 1
+ assertStringInstrWithOccurrence("a", "", -1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("a", "", -6, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("a", "", -6, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("a", "", -6, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("a", "", -6, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 2, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 2, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 2, UNICODE, 1);
+ assertStringInstrWithOccurrence("a", "", -1, 2, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("", "", -1, 2, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("", "", -1, 2, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("", "", -1, 2, UNICODE, 1);
+ assertStringInstrWithOccurrence("", "", -1, 2, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("", "x", -1, 1, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("", "x", -1, 1, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("", "x", -1, 1, UNICODE, 0);
+ assertStringInstrWithOccurrence("", "x", -1, 1, UNICODE_CI, 0);
+ // Overlapping matches
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 1, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 3, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 4, UTF8_BINARY, 0);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", 1, 1, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", -1, 2, UTF8_LCASE, 2);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", 1, 3, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", 1, 4, UTF8_LCASE, 0);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 1, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 2, UNICODE_CI, 2);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 3, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 4, UNICODE_CI, 0);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 1, UNICODE, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 2, UNICODE, 2);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 3, UNICODE, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", 1, 4, UNICODE, 0);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 1, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 1, UTF8_BINARY, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 3, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 3, UTF8_BINARY, 1);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", -1, 1, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", -2, 1, UTF8_LCASE, 3);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", -1, 3, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("σσσσ", "σσ", -2, 3, UTF8_LCASE, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 1, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 1, UNICODE_CI, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 3, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 3, UNICODE_CI, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 1, UNICODE, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 1, UNICODE, 3);
+ assertStringInstrWithOccurrence("aaaa", "aa", -1, 3, UNICODE, 1);
+ assertStringInstrWithOccurrence("aaaa", "aa", -2, 3, UNICODE, 1);
+ }
+
}
// checkstyle.on: AvoidEscapedUnicodeCharacters
diff --git
a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/UTF8StringSuite.java
b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/UTF8StringSuite.java
index 0374a1672d22..13d6c30cd256 100644
---
a/common/unsafe/src/test/java/org/apache/spark/unsafe/types/UTF8StringSuite.java
+++
b/common/unsafe/src/test/java/org/apache/spark/unsafe/types/UTF8StringSuite.java
@@ -279,6 +279,70 @@ public class UTF8StringSuite {
assertEquals(-1, fromString("数据砖头").indexOf(fromString("数"), 3));
assertEquals(0, fromString("数据砖头").indexOf(fromString("数"), 0));
assertEquals(3, fromString("数据砖头").indexOf(fromString("头"), 0));
+
+ // Tests for indexOf with start and occurrence parameters
+ // Forward search
+ assertEquals(1, fromString("abcabc").indexOf(fromString("b"), 1, 1));
+ assertEquals(4, fromString("abcabc").indexOf(fromString("b"), 1, 2));
+ assertEquals(4, fromString("abcabc").indexOf(fromString("b"), 3, 1));
+ assertEquals(-1, fromString("abcabc").indexOf(fromString("b"), 10, 1));
+ assertEquals(-1, fromString("abcabc").indexOf(fromString("b"), 0, 1));
+
+ // Backward search (negative start)
+ assertEquals(4, fromString("abcabc").indexOf(fromString("b"), -1, 1));
+ assertEquals(1, fromString("abcabc").indexOf(fromString("b"), -1, 2));
+ assertEquals(4, fromString("abcabc").indexOf(fromString("b"), -2, 1));
+ assertEquals(-1, fromString("abcabc").indexOf(fromString("b"), -10, 1));
+
+ // Overlapping matches ("aa" in "aaaa")
+ assertEquals(0, fromString("aaaa").indexOf(fromString("aa"), 1, 1));
+ assertEquals(1, fromString("aaaa").indexOf(fromString("aa"), 1, 2));
+ assertEquals(2, fromString("aaaa").indexOf(fromString("aa"), 1, 3));
+ assertEquals(-1, fromString("aaaa").indexOf(fromString("aa"), 1, 4));
+ assertEquals(2, fromString("aaaa").indexOf(fromString("aa"), -1, 1));
+ assertEquals(1, fromString("aaaa").indexOf(fromString("aa"), -1, 2));
+ assertEquals(0, fromString("aaaa").indexOf(fromString("aa"), -1, 3));
+ assertEquals(2, fromString("aaaa").indexOf(fromString("aa"), -2, 1));
+ assertEquals(1, fromString("aaaa").indexOf(fromString("aa"), -2, 2));
+ assertEquals(0, fromString("aaaa").indexOf(fromString("aa"), -2, 3));
+
+ // Multi-byte characters
+ assertEquals(0, fromString("你好世界你好").indexOf(fromString("你好"), 1, 1));
+ assertEquals(4, fromString("你好世界你好").indexOf(fromString("你好"), 1, 2));
+ assertEquals(4, fromString("你好世界你好").indexOf(fromString("你好"), -1, 1));
+ assertEquals(0, fromString("你好世界你好").indexOf(fromString("你好"), -1, 2));
+ assertEquals(4, fromString("你好世界你好").indexOf(fromString("你好"), -2, 1));
+ assertEquals(0, fromString("你好世界你好").indexOf(fromString("你好"), -2, 2));
+ assertEquals(0, fromString("你好世界你好").indexOf(fromString("你好"), -3, 1));
+ assertEquals(-1, fromString("你好世界你好").indexOf(fromString("你好"), -3, 2));
+
+ // Empty substring (behavior depends on indexOfEmpty, currently returns 0)
+ assertEquals(0, fromString("hello").indexOf(EMPTY_UTF8, 1, 1));
+ assertEquals(0, fromString("hello").indexOf(EMPTY_UTF8, 5, 1));
+ assertEquals(0, fromString("hello").indexOf(EMPTY_UTF8, -1, 1));
+
+ // Boundary cases
+ assertEquals(0, fromString("x").indexOf(fromString("x"), 1, 1));
+ assertEquals(-1, fromString("x").indexOf(fromString("x"), 1, 2));
+ assertEquals(0, fromString("x").indexOf(fromString("x"), -1, 1));
+ assertEquals(-1, fromString("x").indexOf(fromString("x"), -1, 2));
+ assertEquals(-1, EMPTY_UTF8.indexOf(fromString("a"), 1, 1));
+ assertEquals(-1, EMPTY_UTF8.indexOf(fromString("a"), -1, 1));
+ assertEquals(4, fromString("hello").indexOf(fromString("o"), 5, 1));
+ assertEquals(-1, fromString("hello").indexOf(fromString("o"), 6, 1));
+ assertEquals(0, fromString("hello").indexOf(fromString("h"), -5, 1));
+ assertEquals(-1, fromString("hello").indexOf(fromString("h"), -6, 1));
+
+ // Target larger than string
+ assertEquals(-1, fromString("ab").indexOf(fromString("abc"), 1, 1));
+ assertEquals(-1, fromString("ab").indexOf(fromString("abc"), -1, 1));
+
+ // Backward search with multi-character patterns
+ assertEquals(2, fromString("abcde").indexOf(fromString("cd"), -3, 1));
+ assertEquals(1, fromString("abcde").indexOf(fromString("bc"), -3, 1));
+ assertEquals(0, fromString("abcde").indexOf(fromString("ab"), -3, 1));
+ assertEquals(-1, fromString("abcde").indexOf(fromString("de"), -3, 1));
+ assertEquals(2, fromString("abcde").indexOf(fromString("cde"), -1, 1));
}
@Test
diff --git a/common/utils/src/main/resources/error/error-conditions.json
b/common/utils/src/main/resources/error/error-conditions.json
index b2e8de9c2e36..64d414b10b64 100644
--- a/common/utils/src/main/resources/error/error-conditions.json
+++ b/common/utils/src/main/resources/error/error-conditions.json
@@ -4552,6 +4552,11 @@
"expects a non-NULL value."
]
},
+ "OCCURRENCE" : {
+ "message" : [
+ "expects a positive integer, but got <actual>."
+ ]
+ },
"PATTERN" : {
"message" : [
"<value>."
diff --git a/python/pyspark/sql/connect/functions/builtin.py
b/python/pyspark/sql/connect/functions/builtin.py
index 00183fe283f0..71433899074f 100644
--- a/python/pyspark/sql/connect/functions/builtin.py
+++ b/python/pyspark/sql/connect/functions/builtin.py
@@ -2626,8 +2626,21 @@ def format_string(format: str, *cols: "ColumnOrName") ->
Column:
format_string.__doc__ = pysparkfuncs.format_string.__doc__
-def instr(str: "ColumnOrName", substr: Union[Column, str]) -> Column:
- return _invoke_function("instr", _to_col(str), lit(substr))
+def instr(
+ str: "ColumnOrName",
+ substr: Union[Column, str],
+ start: Optional[Union[Column, int]] = None,
+ occurrence: Optional[Union[Column, int]] = None,
+) -> Column:
+ if start is None and occurrence is None:
+ return _invoke_function_over_columns("instr", str, lit(substr))
+ elif start is not None and occurrence is None:
+ start = lit(start)
+ return _invoke_function_over_columns("instr", str, lit(substr), start)
+ else:
+ start = lit(start) if start is not None else lit(1)
+ occurrence = lit(occurrence)
+ return _invoke_function_over_columns("instr", str, lit(substr), start,
occurrence)
instr.__doc__ = pysparkfuncs.instr.__doc__
diff --git a/python/pyspark/sql/functions/builtin.py
b/python/pyspark/sql/functions/builtin.py
index de6683eabef5..fb47eee5ba69 100644
--- a/python/pyspark/sql/functions/builtin.py
+++ b/python/pyspark/sql/functions/builtin.py
@@ -15235,9 +15235,14 @@ def format_string(format: str, *cols: "ColumnOrName")
-> Column:
@_try_remote_functions
-def instr(str: "ColumnOrName", substr: Union[Column, str]) -> Column:
+def instr(
+ str: "ColumnOrName",
+ substr: Union[Column, str],
+ start: Optional[Union[Column, int]] = None,
+ occurrence: Optional[Union[Column, int]] = None,
+) -> Column:
"""
- Locate the position of the first occurrence of substr column in the given
string.
+ Locate the position of the specified occurrence of substr column in the
given string.
Returns null if either of the arguments are null.
.. versionadded:: 1.5.0
@@ -15245,6 +15250,9 @@ def instr(str: "ColumnOrName", substr: Union[Column,
str]) -> Column:
.. versionchanged:: 3.4.0
Supports Spark Connect.
+ .. versionchanged:: 4.3.0
+ Supports optional `start` and `occurrence` parameters.
+
Notes
-----
The position is not zero based, but 1 based index. Returns 0 if substr
@@ -15259,11 +15267,16 @@ def instr(str: "ColumnOrName", substr: Union[Column,
str]) -> Column:
.. versionchanged:: 4.0.0
`substr` now accepts column.
+ start : int or :class:`~pyspark.sql.Column`, optional
+ Starting position (1-based, can be negative for backward search).
+ If not specified, defaults to 1.
+ occurrence : int or :class:`~pyspark.sql.Column`, optional
+ Which occurrence to locate (must be > 0). Defaults to 1.
Returns
-------
:class:`~pyspark.sql.Column`
- location of the first occurrence of the substring as integer.
+ location of the substring as integer.
See Also
--------
@@ -15297,8 +15310,40 @@ def instr(str: "ColumnOrName", substr: Union[Column,
str]) -> Column:
|abcd| 1|
| xyz| 0|
+----+---------------------------+
+
+ Example 3: Using start and occurrence parameters
+
+ >>> from pyspark.sql import functions as sf
+ >>> df = spark.createDataFrame([("aabcd",), ("xyz",)], ["s",])
+ >>> df.select("*", sf.instr("s", "b", 1, 2)).show()
+ +-----+-----------------+
+ | s|instr(s, b, 1, 2)|
+ +-----+-----------------+
+ |aabcd| 0|
+ | xyz| 0|
+ +-----+-----------------+
+
+ Example 4: Using start parameter
+
+ >>> from pyspark.sql import functions as sf
+ >>> df = spark.createDataFrame([("aabcd",), ("xyz",)], ["s",])
+ >>> df.select("*", sf.instr("s", "a", 2)).show()
+ +-----+-----------------+
+ | s|instr(s, a, 2, 1)|
+ +-----+-----------------+
+ |aabcd| 2|
+ | xyz| 0|
+ +-----+-----------------+
"""
- return _invoke_function_over_columns("instr", str, lit(substr))
+ if start is None and occurrence is None:
+ return _invoke_function_over_columns("instr", str, lit(substr))
+ elif start is not None and occurrence is None:
+ start = lit(start)
+ return _invoke_function_over_columns("instr", str, lit(substr), start)
+ else:
+ start = lit(start) if start is not None else lit(1)
+ occurrence = lit(occurrence)
+ return _invoke_function_over_columns("instr", str, lit(substr), start,
occurrence)
@_try_remote_functions
diff --git a/python/pyspark/sql/tests/connect/test_connect_function.py
b/python/pyspark/sql/tests/connect/test_connect_function.py
index 07ebe0b3b8a0..5c19cb895c7c 100644
--- a/python/pyspark/sql/tests/connect/test_connect_function.py
+++ b/python/pyspark/sql/tests/connect/test_connect_function.py
@@ -2223,6 +2223,14 @@ class SparkConnectFunctionTests(ReusedMixedTestCase,
PandasOnSparkTestUtils):
cdf.select(CF.translate(cdf.b, "abc", "xyz")).toPandas(),
sdf.select(SF.translate(sdf.b, "abc", "xyz")).toPandas(),
)
+ self.assert_eq(
+ cdf.select(CF.instr(cdf.b, "abc", 1)).toPandas(),
+ sdf.select(SF.instr(sdf.b, "abc", 1)).toPandas(),
+ )
+ self.assert_eq(
+ cdf.select(CF.instr(cdf.e, ".", -1, 2)).toPandas(),
+ sdf.select(SF.instr(sdf.e, ".", -1, 2)).toPandas(),
+ )
# TODO(SPARK-41283): To compare toPandas for test cases with dtypes marked
def test_date_ts_functions(self):
diff --git a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
index fec67f3f3f33..0dc667a00f6d 100644
--- a/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
+++ b/sql/api/src/main/scala/org/apache/spark/sql/functions.scala
@@ -5174,6 +5174,78 @@ object functions {
*/
def instr(str: Column, substring: Column): Column = Column.fn("instr", str,
substring)
+ /**
+ * Locate the position of the first occurrence of `substring` in `str`,
starting the search from
+ * position `start`. Returns null if either of the arguments are null.
+ *
+ * @note
+ * The position is not zero based, but 1 based index. Returns 0 if substr
could not be found
+ * in str.
+ * @note
+ * If `start` is positive, the search proceeds forward. If `start` is
negative, the search
+ * proceeds backward from the end of the string. If `start` is 0, returns
0.
+ *
+ * @group string_funcs
+ * @since 4.3.0
+ */
+ def instr(str: Column, substring: Column, start: Int): Column =
+ Column.fn("instr", str, substring, lit(start))
+
+ /**
+ * Locate the position of the first occurrence of `substring` in `str`,
starting the search from
+ * position `start`. Returns null if either of the arguments are null.
+ *
+ * @note
+ * The position is not zero based, but 1 based index. Returns 0 if substr
could not be found
+ * in str.
+ * @note
+ * If `start` is positive, the search proceeds forward. If `start` is
negative, the search
+ * proceeds backward from the end of the string. If `start` is 0, returns
0.
+ *
+ * @group string_funcs
+ * @since 4.3.0
+ */
+ def instr(str: Column, substring: Column, start: Column): Column =
+ Column.fn("instr", str, substring, start)
+
+ /**
+ * Locate the position of the `occurrence`-th occurrence of `substring` in
`str`, starting the
+ * search from position `start`. Returns null if either of the arguments are
null.
+ *
+ * @note
+ * The position is not zero based, but 1 based index. Returns 0 if substr
could not be found
+ * in str.
+ * @note
+ * If `start` is positive, the search proceeds forward. If `start` is
negative, the search
+ * proceeds backward from the end of the string. If `start` is 0, returns
0.
+ * @note
+ * The `occurrence` parameter must be a positive integer.
+ *
+ * @group string_funcs
+ * @since 4.3.0
+ */
+ def instr(str: Column, substring: Column, start: Int, occurrence: Int):
Column =
+ Column.fn("instr", str, substring, lit(start), lit(occurrence))
+
+ /**
+ * Locate the position of the `occurrence`-th occurrence of `substring` in
`str`, starting the
+ * search from position `start`. Returns null if either of the arguments are
null.
+ *
+ * @note
+ * The position is not zero based, but 1 based index. Returns 0 if substr
could not be found
+ * in str.
+ * @note
+ * If `start` is positive, the search proceeds forward. If `start` is
negative, the search
+ * proceeds backward from the end of the string. If `start` is 0, returns
0.
+ * @note
+ * The `occurrence` parameter must be a positive integer.
+ *
+ * @group string_funcs
+ * @since 4.3.0
+ */
+ def instr(str: Column, substring: Column, start: Column, occurrence:
Column): Column =
+ Column.fn("instr", str, substring, start, occurrence)
+
/**
* Computes the character length of a given string or number of bytes of a
binary string. The
* length of character strings include the trailing spaces. The length of
binary strings
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CollationTypeCoercion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CollationTypeCoercion.scala
index 8a83b576d725..f43dfb77a9c0 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CollationTypeCoercion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/CollationTypeCoercion.scala
@@ -90,7 +90,7 @@ object CollationTypeCoercion extends SQLConfHelper {
_: LessThan | _: LessThanOrEqual | _: StartsWith | _: StringInstr | _:
ToNumber |
_: TryToNumber | _: StringToMap | _: Levenshtein | _: StringSplitSQL
| _: SplitPart |
_: Lag | _: Lead | _: RegExpReplace | _: StringRPad | _: StringLPad |
_: Overlay |
- _: Elt | _: SubstringIndex | _: StringLocate | _: If) =>
+ _: Elt | _: SubstringIndex | _: StringLocate | _: If | _:
StringInstrWithOccurrence) =>
val newChildren = collateToSingleType(otherExpr, otherExpr.children)
otherExpr.withNewChildren(newChildren)
@@ -494,7 +494,8 @@ object CollationTypeCoercion extends SQLConfHelper {
case _: BinaryComparison | _: StringPredicate | _: Upper | _: Lower | _:
InitCap |
_: FindInSet | _: StringInstr | _: StringReplace | _: StringLocate |
_: SubstringIndex |
_: StringTrim | _: StringTrimLeft | _: StringTrimRight | _:
StringTranslate |
- _: StringSplitSQL | _: In | _: InSubquery | _: FindInSet => false
+ _: StringSplitSQL | _: In | _: InSubquery | _: FindInSet |
+ _: StringInstrWithOccurrence => false
case _ => true
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
index 737a9da8b2b7..a8654491a269 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/FunctionRegistry.scala
@@ -625,7 +625,7 @@ object FunctionRegistry {
expressionBuilder("to_varchar", ToCharacterBuilder, setAlias = true,
Some("3.5.0")),
expression[GetJsonObject]("get_json_object"),
expression[InitCap]("initcap"),
- expression[StringInstr]("instr"),
+ expressionBuilder("instr", StringInstrExpressionBuilder),
expression[Lower]("lcase", true),
expression[Length]("length"),
expression[Length]("len", setAlias = true, Some("3.4.0")),
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverGuard.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverGuard.scala
index 8db3fe94f3b6..3ba0121dbb64 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverGuard.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/resolver/ResolverGuard.scala
@@ -562,7 +562,7 @@ class ResolverGuard(
_: Length | _: BitLength | _: OctetLength | _: Levenshtein | _:
SoundEx | _: Ascii |
_: Chr | _: Base64 | _: UnBase64 | _: Decode | _: StringDecode | _:
Encode | _: ToBinary |
_: FormatNumber | _: Sentences | _: StringSplitSQL | _: SplitPart |
_: Empty2Null |
- _: Luhncheck =>
+ _: Luhncheck | _: StringInstrWithOccurrence =>
true
// Datetime
case _: CurrentTime | _: CurrentTimestampLike | _:
TimeZoneAwareExpression =>
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
index ec04f9f713fc..66c4a39ce823 100755
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/stringExpressions.scala
@@ -1651,24 +1651,53 @@ case class StringTrimRight(srcStr: Expression, trimStr:
Option[Expression] = Non
trimStr = if (trimStr.isDefined) Some(newChildren.last) else None)
}
-/**
- * A function that returns the position of the first occurrence of substr in
the given string.
- * Returns null if either of the arguments are null and
- * returns 0 if substr could not be found in str.
- *
- * NOTE: that this is not zero based, but 1-based index. The first character
in str has index 1.
- */
// scalastyle:off line.size.limit
@ExpressionDescription(
- usage = "_FUNC_(str, substr) - Returns the (1-based) index of the first
occurrence of `substr` in `str`.",
+ usage = """
+ _FUNC_(str, substr[, start[, occurrence]]) - Returns the (1-based) index
of the specified
+ occurrence of `substr` in `str`, starting the search from position
`start`.
+ If `start` is positive, the search proceeds forward;
+ if `start` is negative, the search proceeds backward.
+ `start` = 0 returns 0.
+ If `start` is not specified, it defaults to 1.
+ `occurrence` must be a positive integer and defaults to 1.
+ """,
examples = """
Examples:
> SELECT _FUNC_('SparkSQL', 'SQL');
6
+ > SELECT _FUNC_('abcabc', 'b', 1);
+ 2
+ > SELECT _FUNC_('abcabc', 'b', 1, 2);
+ 5
+ > SELECT _FUNC_('abcabc', 'b', -1, 1);
+ 5
""",
since = "1.5.0",
group = "string_funcs")
// scalastyle:on line.size.limit
+object StringInstrExpressionBuilder extends ExpressionBuilder {
+ override def build(funcName: String, expressions: Seq[Expression]):
Expression = {
+ val size = expressions.size
+ if (size == 2) {
+ StringInstr(expressions.head, expressions(1))
+ } else if (size == 3) {
+ StringInstrWithOccurrence(expressions.head, expressions(1),
expressions(2), Literal(1))
+ } else if (size == 4) {
+ StringInstrWithOccurrence(expressions.head, expressions(1),
expressions(2), expressions(3))
+ } else {
+ throw QueryCompilationErrors.wrongNumArgsError(funcName, Seq(2, 3, 4),
size)
+ }
+ }
+}
+
+/**
+ * A function that returns the position of the first occurrence of substr in
the given string.
+ * Returns null if either of the arguments are null and
+ * returns 0 if substr could not be found in str.
+ *
+ * NOTE: that this is not zero based, but 1-based index. The first character
in str has index 1.
+ */
case class StringInstr(str: Expression, substr: Expression)
extends BinaryExpression with ImplicitCastInputTypes {
override def nullIntolerant: Boolean = true
@@ -1701,6 +1730,70 @@ case class StringInstr(str: Expression, substr:
Expression)
newLeft: Expression, newRight: Expression): StringInstr = copy(str =
newLeft, substr = newRight)
}
+/**
+ * A function that returns the position of the specified occurrence of
`substr` in the given
+ * string, starting the search from position `start`. If `start` is positive,
the search proceeds
+ * forward; if `start` is negative, the search proceeds backward. `start` = 0
returns 0. If
+ * `start` is not specified, it defaults to 1. If `occurrence` is specified,
it determines which
+ * occurrence of `substr` to return; `occurrence` must be a positive integer
and defaults to 1.
+ *
+ * Returns null if either of the arguments are null and
+ * returns 0 if substr could not be found in str.
+ *
+ * NOTE: that this is not zero based, but 1-based index. The first character
in str has index 1.
+ */
+case class StringInstrWithOccurrence(
+ str: Expression,
+ sub: Expression,
+ start: Expression,
+ occurrence: Expression)
+ extends QuaternaryExpression with ImplicitCastInputTypes {
+ override def nullIntolerant: Boolean = true
+ final lazy val collationId: Int =
first.dataType.asInstanceOf[StringType].collationId
+
+ override def first: Expression = str
+ override def second: Expression = sub
+ override def third: Expression = start
+ override def fourth: Expression = occurrence
+ override def dataType: DataType = IntegerType
+ override def inputTypes: Seq[AbstractDataType] =
+ Seq(
+ StringTypeNonCSAICollation(supportsTrimCollation = true),
+ StringTypeNonCSAICollation(supportsTrimCollation = true),
+ IntegerType,
+ IntegerType
+ )
+
+ override def nullSafeEval(string: Any, sub: Any, start: Any, occurrence:
Any): Any = {
+ val occ = occurrence.asInstanceOf[Int]
+ if (occ <= 0) {
+ throw QueryExecutionErrors.invalidOccurrenceError(prettyName, occ)
+ }
+
CollationSupport.StringInstrWithOccurrence.exec(string.asInstanceOf[UTF8String],
+ sub.asInstanceOf[UTF8String], start.asInstanceOf[Int], occ, collationId)
+ 1
+ }
+
+ override def prettyName: String = "instr"
+
+ override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {
+ nullSafeCodeGen(ctx, ev, (string, substring, start, occurrence) => {
+ val eval = CollationSupport.StringInstrWithOccurrence
+ .genCode(string, substring, start, occurrence, collationId) + " + 1"
+ s"""
+ if ($occurrence <= 0) {
+ throw QueryExecutionErrors.invalidOccurrenceError("$prettyName",
$occurrence);
+ } else {
+ ${ev.value} = $eval;
+ }
+ """
+ })
+ }
+
+ override protected def withNewChildrenInternal(first: Expression, second:
Expression,
+ third: Expression, fourth: Expression): StringInstrWithOccurrence =
+ copy(str = first, sub = second, start = third, occurrence = fourth)
+}
+
/**
* Returns the substring from string str before count occurrences of the
delimiter delim.
* If count is positive, everything the left of the final delimiter (counting
from left) is
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
index 5eb117465175..b892ad55c12c 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/errors/QueryExecutionErrors.scala
@@ -3442,4 +3442,15 @@ private[sql] object QueryExecutionErrors extends
QueryErrorsBase with ExecutionE
errorClass = "INVALID_LINE_SEPARATOR.TOO_LONG",
messageParameters = Map("length" -> length.toString))
}
+
+ def invalidOccurrenceError(functionName: String, occurrence: Int):
SparkRuntimeException = {
+ new SparkRuntimeException(
+ errorClass = "INVALID_PARAMETER_VALUE.OCCURRENCE",
+ messageParameters = Map(
+ "functionName" -> toSQLId(functionName),
+ "parameter" -> toSQLId("occurrence"),
+ "actual" -> occurrence.toString
+ )
+ )
+ }
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/StringExpressionsSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/StringExpressionsSuite.scala
index 19df4e5f39e9..5559972cd795 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/StringExpressionsSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/expressions/StringExpressionsSuite.scala
@@ -1025,6 +1025,90 @@ class StringExpressionsSuite extends SparkFunSuite with
ExpressionEvalHelper {
// Test escaping of arguments
GenerateUnsafeProjection.generate(StringInstr(Literal("\"quote"),
Literal("\"quote")) :: Nil)
+
+ // Test instr with start and occurrence
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("b"), Literal(3), Literal(1)), 5)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("b"), Literal(1), Literal(2)), 5)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("b"), Literal(3), Literal(2)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("b"), Literal(-1), Literal(1)), 5)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("b"), Literal(-1), Literal(2)), 2)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("a"), Literal(-2), Literal(1)), 4)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("a"), Literal(-1), Literal(2)), 1)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-1), Literal(1)), 4)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-2), Literal(1)), 4)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-3), Literal(1)), 4)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-1), Literal(2)), 1)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-2), Literal(2)), 1)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-3), Literal(2)), 1)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-1), Literal(3)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-2), Literal(3)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("ab"), Literal(-3), Literal(3)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abc"), Literal("b"), Literal(0), Literal(1)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abc"), Literal("b"), Literal(0), Literal(2)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abc"), Literal("b"), Literal(1), Literal(3)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abc"), Literal("b"), Literal(-1), Literal(3)), 0)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("abc"), Literal("d"), Literal(1), Literal(1)), 0)
+
+ // NULL
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal.create(null, StringType), Literal("de"), Literal(1),
Literal(1)), null)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("aaads"), Literal.create(null, StringType), Literal(1),
Literal(1)), null)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("aaads"), Literal("aa"), Literal.create(null, IntegerType),
Literal(1)), null)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("aaads"), Literal("aa"), Literal(1), Literal.create(null,
IntegerType)), null)
+
+ // scalastyle:off
+ // non ascii characters are not allowed in the source code, so we disable
the scalastyle.
+ checkEvaluation(StringInstrWithOccurrence(
+ s1, s2, Literal(1), Literal(1)), 3, create_row("花花世界", "世界"))
+ checkEvaluation(StringInstrWithOccurrence(
+ s1, s2, Literal(1), Literal(2)), 0, create_row("花花世界", "世界"))
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("你好世界你好"), Literal("你好"), Literal(1), Literal(2)), 5)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("你好世界你好"), Literal("你好"), Literal(-1), Literal(1)), 5)
+ checkEvaluation(StringInstrWithOccurrence(
+ Literal("你好世界你好"), Literal("你好"), Literal(-1), Literal(2)), 1)
+ // scalastyle:on
+ }
+
+ test("StringInstrExpressionBuilder") {
+ val seq1 = Seq(Literal("abcabc"), Literal("a"), Literal(-1), Literal(2))
+ val seq2 = Seq(Literal("abcabc"), Literal("a"), Literal(-1))
+ val seq3 = Seq(Literal("abcabc"), Literal("a"))
+
+ val instrExp1 = StringInstrExpressionBuilder.build("instr", seq1)
+ val instrExp2 = StringInstrExpressionBuilder.build("instr", seq2)
+ val instrExp3 = StringInstrExpressionBuilder.build("instr", seq3)
+
+ assert(instrExp1 == StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("a"), Literal(-1), Literal(2)))
+ assert(instrExp2 == StringInstrWithOccurrence(
+ Literal("abcabc"), Literal("a"), Literal(-1), Literal(1)))
+ assert(instrExp3 == StringInstr(Literal("abcabc"), Literal("a")))
}
test("LOCATE") {
diff --git a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
index d8abc643afeb..5134975689e3 100644
--- a/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
+++ b/sql/core/src/test/resources/sql-functions/sql-expression-schema.md
@@ -341,7 +341,7 @@
| org.apache.spark.sql.catalyst.expressions.Sqrt | sqrt | SELECT sqrt(4) |
struct<SQRT(4):double> |
| org.apache.spark.sql.catalyst.expressions.Stack | stack | SELECT stack(2, 1,
2, 3) | struct<col0:int,col1:int> |
| org.apache.spark.sql.catalyst.expressions.StartsWithExpressionBuilder |
startswith | SELECT startswith('Spark SQL', 'Spark') | struct<startswith(Spark
SQL, Spark):boolean> |
-| org.apache.spark.sql.catalyst.expressions.StringInstr | instr | SELECT
instr('SparkSQL', 'SQL') | struct<instr(SparkSQL, SQL):int> |
+| org.apache.spark.sql.catalyst.expressions.StringInstrExpressionBuilder |
instr | SELECT instr('SparkSQL', 'SQL') | struct<instr(SparkSQL, SQL):int> |
| org.apache.spark.sql.catalyst.expressions.StringLocate | locate | SELECT
locate('bar', 'foobarbar') | struct<locate(bar, foobarbar, 1):int> |
| org.apache.spark.sql.catalyst.expressions.StringLocate | position | SELECT
position('bar', 'foobarbar') | struct<position(bar, foobarbar, 1):int> |
| org.apache.spark.sql.catalyst.expressions.StringRepeat | repeat | SELECT
repeat('123', 2) | struct<repeat(123, 2):string> |
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/collations-string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/collations-string-functions.sql.out
index 5cda6267432d..257855b4eaa3 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/collations-string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/collations-string-functions.sql.out
@@ -614,6 +614,90 @@ Project [instr(cast(utf8_binary#x as string collate
UTF8_LCASE), collate(AaAA, u
+- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+-- !query
+select instr(utf8_binary, utf8_lcase, 1) from t1
+-- !query analysis
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INDETERMINATE_COLLATION_IN_EXPRESSION",
+ "sqlState" : "42P22",
+ "messageParameters" : {
+ "expr" : "\"instr(utf8_binary, utf8_lcase, 1, 1)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 40,
+ "fragment" : "instr(utf8_binary, utf8_lcase, 1)"
+ } ]
+}
+
+
+-- !query
+select instr(utf8_lcase, 'a', 1) from t1
+-- !query analysis
+Project [instr(utf8_lcase#x, a, 1, 1) AS instr(utf8_lcase, 'a' collate
UTF8_LCASE, 1, 1)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
+-- !query
+select instr(utf8_binary collate utf8_lcase, utf8_lcase collate utf8_lcase,
-1) from t1
+-- !query analysis
+Project [instr(collate(utf8_binary#x, utf8_lcase), collate(utf8_lcase#x,
utf8_lcase), -1, 1) AS instr(collate(utf8_binary, utf8_lcase),
collate(utf8_lcase, utf8_lcase), -1, 1)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
+-- !query
+select instr(s, utf8_binary, 1, 2) from t1
+-- !query analysis
+Project [instr(s#x, utf8_binary#x, 1, 2) AS instr(s, utf8_binary, 1, 2)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
+-- !query
+select instr(utf8_binary, utf8_lcase collate utf8_binary, -1, 2) from t1
+-- !query analysis
+Project [instr(utf8_binary#x, collate(utf8_lcase#x, utf8_binary), -1, 2) AS
instr(utf8_binary, collate(utf8_lcase, utf8_binary), -1, 2)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
+-- !query
+select instr(utf8_binary collate unicode_ai, utf8_lcase collate unicode_ai, 2,
1) from t1
+-- !query analysis
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"collate(utf8_binary, unicode_ai)\"",
+ "inputType" : "\"STRING COLLATE UNICODE_AI\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"STRING\"",
+ "sqlExpr" : "\"instr(collate(utf8_binary, unicode_ai), collate(utf8_lcase,
unicode_ai), 2, 1)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "instr(utf8_binary collate unicode_ai, utf8_lcase collate
unicode_ai, 2, 1)"
+ } ]
+}
+
+
+-- !query
+select instr(utf8_binary, 'a', 0, 1) from t1
+-- !query analysis
+Project [instr(utf8_binary#x, a, 0, 1) AS instr(utf8_binary, a, 0, 1)#x]
++- SubqueryAlias spark_catalog.default.t1
+ +- Relation spark_catalog.default.t1[s#x,utf8_binary#x,utf8_lcase#x] parquet
+
+
-- !query
select find_in_set(utf8_binary, utf8_lcase) from t1
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/string-functions.sql.out
index 942950bcb6f4..15e6f3ada266 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/string-functions.sql.out
@@ -1873,3 +1873,297 @@ select quote(NULL)
-- !query analysis
Project [quote(cast(null as string)) AS quote(NULL)#x]
+- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1)
+-- !query analysis
+Project [instr(abcabc, b, 1, 1) AS instr(abcabc, b, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 3)
+-- !query analysis
+Project [instr(abcabc, b, 3, 1) AS instr(abcabc, b, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', 3)
+-- !query analysis
+Project [instr(abcabc, a, 3, 1) AS instr(abcabc, a, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1)
+-- !query analysis
+Project [instr(abcabc, b, -1, 1) AS instr(abcabc, b, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -2)
+-- !query analysis
+Project [instr(abcabc, b, -2, 1) AS instr(abcabc, b, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -3)
+-- !query analysis
+Project [instr(abcabc, b, -3, 1) AS instr(abcabc, b, -3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', -2)
+-- !query analysis
+Project [instr(abcabc, a, -2, 1) AS instr(abcabc, a, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1, 2)
+-- !query analysis
+Project [instr(abcabc, b, 1, 2) AS instr(abcabc, b, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 3, 1)
+-- !query analysis
+Project [instr(abcabc, b, 3, 1) AS instr(abcabc, b, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1, 3)
+-- !query analysis
+Project [instr(abcabc, b, 1, 3) AS instr(abcabc, b, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1, 1)
+-- !query analysis
+Project [instr(abcabc, b, -1, 1) AS instr(abcabc, b, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1, 2)
+-- !query analysis
+Project [instr(abcabc, b, -1, 2) AS instr(abcabc, b, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -2, 2)
+-- !query analysis
+Project [instr(abcabc, b, -2, 2) AS instr(abcabc, b, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', -1, 2)
+-- !query analysis
+Project [instr(abcabc, a, -1, 2) AS instr(abcabc, a, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 0, 1)
+-- !query analysis
+Project [instr(abcabc, b, 0, 1) AS instr(abcabc, b, 0, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 0, 2)
+-- !query analysis
+Project [instr(abcabc, b, 0, 2) AS instr(abcabc, b, 0, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', 1)
+-- !query analysis
+Project [instr(hello, , 1, 1) AS instr(hello, , 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', 3)
+-- !query analysis
+Project [instr(hello, , 3, 1) AS instr(hello, , 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', -1)
+-- !query analysis
+Project [instr(hello, , -1, 1) AS instr(hello, , -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', -2)
+-- !query analysis
+Project [instr(hello, , -2, 1) AS instr(hello, , -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('', '', 1)
+-- !query analysis
+Project [instr(, , 1, 1) AS instr(, , 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('', '', -1)
+-- !query analysis
+Project [instr(, , -1, 1) AS instr(, , -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', 1, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, 1, 2) AS instr(你好世界你好, 你好, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -1, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -1, 2) AS instr(你好世界你好, 你好, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -2, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -2, 2) AS instr(你好世界你好, 你好, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -1)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -1, 1) AS instr(你好世界你好, 你好, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -2, 1) AS instr(你好世界你好, 你好, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '世界', -1)
+-- !query analysis
+Project [instr(你好世界你好, 世界, -1, 1) AS instr(你好世界你好, 世界, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 2)
+-- !query analysis
+Project [instr(aaaa, aa, 1, 2) AS instr(aaaa, aa, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 3)
+-- !query analysis
+Project [instr(aaaa, aa, 1, 3) AS instr(aaaa, aa, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', -1, 2)
+-- !query analysis
+Project [instr(aaaa, aa, -1, 2) AS instr(aaaa, aa, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', -2, 2)
+-- !query analysis
+Project [instr(aaaa, aa, -2, 2) AS instr(aaaa, aa, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'd', 1, 1)
+-- !query analysis
+Project [instr(abc, d, 1, 1) AS instr(abc, d, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', 1, 3)
+-- !query analysis
+Project [instr(abc, b, 1, 3) AS instr(abc, b, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', 10, 1)
+-- !query analysis
+Project [instr(abc, b, 10, 1) AS instr(abc, b, 10, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', -10, 1)
+-- !query analysis
+Project [instr(abc, b, -10, 1) AS instr(abc, b, -10, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', cast(null as int))
+-- !query analysis
+Project [instr(a, b, cast(null as int), 1) AS instr(a, b, CAST(NULL AS INT),
1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', 1, cast(null as int))
+-- !query analysis
+Project [instr(a, b, 1, cast(null as int)) AS instr(a, b, 1, CAST(NULL AS
INT))#x]
++- OneRowRelation
+
+
+-- !query
+select instr(null, 'b', 1)
+-- !query analysis
+Project [instr(cast(null as string), b, 1, 1) AS instr(NULL, b, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', null, 1)
+-- !query analysis
+Project [instr(a, cast(null as string), 1, 1) AS instr(a, NULL, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', cast(null as int), 2)
+-- !query analysis
+Project [instr(a, b, cast(null as int), 2) AS instr(a, b, CAST(NULL AS INT),
2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr(null, null, cast(null as int), cast(null as int))
+-- !query analysis
+Project [instr(cast(null as string), cast(null as string), cast(null as int),
cast(null as int)) AS instr(NULL, NULL, CAST(NULL AS INT), CAST(NULL AS INT))#x]
++- OneRowRelation
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/string-functions.sql.out
index 942950bcb6f4..15e6f3ada266 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/string-functions.sql.out
@@ -1873,3 +1873,297 @@ select quote(NULL)
-- !query analysis
Project [quote(cast(null as string)) AS quote(NULL)#x]
+- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1)
+-- !query analysis
+Project [instr(abcabc, b, 1, 1) AS instr(abcabc, b, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 3)
+-- !query analysis
+Project [instr(abcabc, b, 3, 1) AS instr(abcabc, b, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', 3)
+-- !query analysis
+Project [instr(abcabc, a, 3, 1) AS instr(abcabc, a, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1)
+-- !query analysis
+Project [instr(abcabc, b, -1, 1) AS instr(abcabc, b, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -2)
+-- !query analysis
+Project [instr(abcabc, b, -2, 1) AS instr(abcabc, b, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -3)
+-- !query analysis
+Project [instr(abcabc, b, -3, 1) AS instr(abcabc, b, -3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', -2)
+-- !query analysis
+Project [instr(abcabc, a, -2, 1) AS instr(abcabc, a, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1, 2)
+-- !query analysis
+Project [instr(abcabc, b, 1, 2) AS instr(abcabc, b, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 3, 1)
+-- !query analysis
+Project [instr(abcabc, b, 3, 1) AS instr(abcabc, b, 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 1, 3)
+-- !query analysis
+Project [instr(abcabc, b, 1, 3) AS instr(abcabc, b, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1, 1)
+-- !query analysis
+Project [instr(abcabc, b, -1, 1) AS instr(abcabc, b, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -1, 2)
+-- !query analysis
+Project [instr(abcabc, b, -1, 2) AS instr(abcabc, b, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', -2, 2)
+-- !query analysis
+Project [instr(abcabc, b, -2, 2) AS instr(abcabc, b, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'a', -1, 2)
+-- !query analysis
+Project [instr(abcabc, a, -1, 2) AS instr(abcabc, a, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 0, 1)
+-- !query analysis
+Project [instr(abcabc, b, 0, 1) AS instr(abcabc, b, 0, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abcabc', 'b', 0, 2)
+-- !query analysis
+Project [instr(abcabc, b, 0, 2) AS instr(abcabc, b, 0, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', 1)
+-- !query analysis
+Project [instr(hello, , 1, 1) AS instr(hello, , 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', 3)
+-- !query analysis
+Project [instr(hello, , 3, 1) AS instr(hello, , 3, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', -1)
+-- !query analysis
+Project [instr(hello, , -1, 1) AS instr(hello, , -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('hello', '', -2)
+-- !query analysis
+Project [instr(hello, , -2, 1) AS instr(hello, , -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('', '', 1)
+-- !query analysis
+Project [instr(, , 1, 1) AS instr(, , 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('', '', -1)
+-- !query analysis
+Project [instr(, , -1, 1) AS instr(, , -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', 1, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, 1, 2) AS instr(你好世界你好, 你好, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -1, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -1, 2) AS instr(你好世界你好, 你好, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -2, 2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -2, 2) AS instr(你好世界你好, 你好, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -1)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -1, 1) AS instr(你好世界你好, 你好, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '你好', -2)
+-- !query analysis
+Project [instr(你好世界你好, 你好, -2, 1) AS instr(你好世界你好, 你好, -2, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('你好世界你好', '世界', -1)
+-- !query analysis
+Project [instr(你好世界你好, 世界, -1, 1) AS instr(你好世界你好, 世界, -1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 2)
+-- !query analysis
+Project [instr(aaaa, aa, 1, 2) AS instr(aaaa, aa, 1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 3)
+-- !query analysis
+Project [instr(aaaa, aa, 1, 3) AS instr(aaaa, aa, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', -1, 2)
+-- !query analysis
+Project [instr(aaaa, aa, -1, 2) AS instr(aaaa, aa, -1, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('aaaa', 'aa', -2, 2)
+-- !query analysis
+Project [instr(aaaa, aa, -2, 2) AS instr(aaaa, aa, -2, 2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'd', 1, 1)
+-- !query analysis
+Project [instr(abc, d, 1, 1) AS instr(abc, d, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', 1, 3)
+-- !query analysis
+Project [instr(abc, b, 1, 3) AS instr(abc, b, 1, 3)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', 10, 1)
+-- !query analysis
+Project [instr(abc, b, 10, 1) AS instr(abc, b, 10, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('abc', 'b', -10, 1)
+-- !query analysis
+Project [instr(abc, b, -10, 1) AS instr(abc, b, -10, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', cast(null as int))
+-- !query analysis
+Project [instr(a, b, cast(null as int), 1) AS instr(a, b, CAST(NULL AS INT),
1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', 1, cast(null as int))
+-- !query analysis
+Project [instr(a, b, 1, cast(null as int)) AS instr(a, b, 1, CAST(NULL AS
INT))#x]
++- OneRowRelation
+
+
+-- !query
+select instr(null, 'b', 1)
+-- !query analysis
+Project [instr(cast(null as string), b, 1, 1) AS instr(NULL, b, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', null, 1)
+-- !query analysis
+Project [instr(a, cast(null as string), 1, 1) AS instr(a, NULL, 1, 1)#x]
++- OneRowRelation
+
+
+-- !query
+select instr('a', 'b', cast(null as int), 2)
+-- !query analysis
+Project [instr(a, b, cast(null as int), 2) AS instr(a, b, CAST(NULL AS INT),
2)#x]
++- OneRowRelation
+
+
+-- !query
+select instr(null, null, cast(null as int), cast(null as int))
+-- !query analysis
+Project [instr(cast(null as string), cast(null as string), cast(null as int),
cast(null as int)) AS instr(NULL, NULL, CAST(NULL AS INT), CAST(NULL AS INT))#x]
++- OneRowRelation
diff --git
a/sql/core/src/test/resources/sql-tests/inputs/collations-string-functions.sql
b/sql/core/src/test/resources/sql-tests/inputs/collations-string-functions.sql
index 0fb73f9978a3..42dac14b2a52 100644
---
a/sql/core/src/test/resources/sql-tests/inputs/collations-string-functions.sql
+++
b/sql/core/src/test/resources/sql-tests/inputs/collations-string-functions.sql
@@ -74,6 +74,13 @@ select instr(utf8_binary collate utf8_lcase, utf8_lcase
collate utf8_lcase) from
select instr(utf8_binary collate unicode_ai, utf8_lcase collate unicode_ai)
from t1;
select instr(utf8_binary, 'a'), instr(utf8_lcase, 'a') from t1;
select instr(utf8_binary, 'AaAA' collate utf8_lcase), instr(utf8_lcase, 'AAa'
collate utf8_binary) from t1;
+select instr(utf8_binary, utf8_lcase, 1) from t1;
+select instr(utf8_lcase, 'a', 1) from t1;
+select instr(utf8_binary collate utf8_lcase, utf8_lcase collate utf8_lcase,
-1) from t1;
+select instr(s, utf8_binary, 1, 2) from t1;
+select instr(utf8_binary, utf8_lcase collate utf8_binary, -1, 2) from t1;
+select instr(utf8_binary collate unicode_ai, utf8_lcase collate unicode_ai, 2,
1) from t1;
+select instr(utf8_binary, 'a', 0, 1) from t1;
-- FindInSet
select find_in_set(utf8_binary, utf8_lcase) from t1;
diff --git a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
index 7559c45ec103..c432093e1ae8 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/string-functions.sql
@@ -318,4 +318,56 @@ select try_validate_utf8(x'80');
-- quote
select quote('Spark');
select quote("Don't");
-select quote(NULL);
\ No newline at end of file
+select quote(NULL);
+
+-- instr
+-- start parameter
+select instr('abcabc', 'b', 1);
+select instr('abcabc', 'b', 3);
+select instr('abcabc', 'a', 3);
+select instr('abcabc', 'b', -1);
+select instr('abcabc', 'b', -2);
+select instr('abcabc', 'b', -3);
+select instr('abcabc', 'a', -2);
+-- start and occurrence
+select instr('abcabc', 'b', 1, 2);
+select instr('abcabc', 'b', 3, 1);
+select instr('abcabc', 'b', 1, 3);
+select instr('abcabc', 'b', -1, 1);
+select instr('abcabc', 'b', -1, 2);
+select instr('abcabc', 'b', -2, 2);
+select instr('abcabc', 'a', -1, 2);
+select instr('abcabc', 'b', 0, 1);
+select instr('abcabc', 'b', 0, 2);
+-- empty substring
+select instr('hello', '', 1);
+select instr('hello', '', 3);
+select instr('hello', '', -1);
+select instr('hello', '', -2);
+select instr('', '', 1);
+select instr('', '', -1);
+-- multi-byte characters
+select instr('你好世界你好', '你好', 1, 2);
+select instr('你好世界你好', '你好', -1, 2);
+select instr('你好世界你好', '你好', -2, 2);
+select instr('你好世界你好', '你好', -1);
+select instr('你好世界你好', '你好', -2);
+select instr('你好世界你好', '世界', -1);
+-- overlapping matches
+select instr('aaaa', 'aa', 1, 2);
+select instr('aaaa', 'aa', 1, 3);
+select instr('aaaa', 'aa', -1, 2);
+select instr('aaaa', 'aa', -2, 2);
+-- not found
+select instr('abc', 'd', 1, 1);
+select instr('abc', 'b', 1, 3);
+-- start out of range
+select instr('abc', 'b', 10, 1);
+select instr('abc', 'b', -10, 1);
+-- null arguments
+select instr('a', 'b', cast(null as int));
+select instr('a', 'b', 1, cast(null as int));
+select instr(null, 'b', 1);
+select instr('a', null, 1);
+select instr('a', 'b', cast(null as int), 2);
+select instr(null, null, cast(null as int), cast(null as int));
\ No newline at end of file
diff --git
a/sql/core/src/test/resources/sql-tests/results/collations-string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/collations-string-functions.sql.out
index 11ace58ee210..37082248e6c6 100644
---
a/sql/core/src/test/resources/sql-tests/results/collations-string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/collations-string-functions.sql.out
@@ -1026,6 +1026,164 @@ struct<instr(utf8_binary, collate(AaAA,
utf8_lcase)):int,instr(utf8_lcase, colla
1 5
+-- !query
+select instr(utf8_binary, utf8_lcase, 1) from t1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+{
+ "errorClass" : "INDETERMINATE_COLLATION_IN_EXPRESSION",
+ "sqlState" : "42P22",
+ "messageParameters" : {
+ "expr" : "\"instr(utf8_binary, utf8_lcase, 1, 1)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 40,
+ "fragment" : "instr(utf8_binary, utf8_lcase, 1)"
+ } ]
+}
+
+
+-- !query
+select instr(utf8_lcase, 'a', 1) from t1
+-- !query schema
+struct<instr(utf8_lcase, 'a' collate UTF8_LCASE, 1, 1):int>
+-- !query output
+0
+0
+0
+0
+0
+0
+0
+0
+1
+1
+1
+1
+1
+1
+21
+
+
+-- !query
+select instr(utf8_binary collate utf8_lcase, utf8_lcase collate utf8_lcase,
-1) from t1
+-- !query schema
+struct<instr(collate(utf8_binary, utf8_lcase), collate(utf8_lcase,
utf8_lcase), -1, 1):int>
+-- !query output
+0
+0
+0
+0
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+8
+
+
+-- !query
+select instr(s, utf8_binary, 1, 2) from t1
+-- !query schema
+struct<instr(s, utf8_binary, 1, 2):int>
+-- !query output
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+
+-- !query
+select instr(utf8_binary, utf8_lcase collate utf8_binary, -1, 2) from t1
+-- !query schema
+struct<instr(utf8_binary, collate(utf8_lcase, utf8_binary), -1, 2):int>
+-- !query output
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+
+-- !query
+select instr(utf8_binary collate unicode_ai, utf8_lcase collate unicode_ai, 2,
1) from t1
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.catalyst.ExtendedAnalysisException
+{
+ "errorClass" : "DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE",
+ "sqlState" : "42K09",
+ "messageParameters" : {
+ "inputSql" : "\"collate(utf8_binary, unicode_ai)\"",
+ "inputType" : "\"STRING COLLATE UNICODE_AI\"",
+ "paramIndex" : "first",
+ "requiredType" : "\"STRING\"",
+ "sqlExpr" : "\"instr(collate(utf8_binary, unicode_ai), collate(utf8_lcase,
unicode_ai), 2, 1)\""
+ },
+ "queryContext" : [ {
+ "objectType" : "",
+ "objectName" : "",
+ "startIndex" : 8,
+ "stopIndex" : 81,
+ "fragment" : "instr(utf8_binary collate unicode_ai, utf8_lcase collate
unicode_ai, 2, 1)"
+ } ]
+}
+
+
+-- !query
+select instr(utf8_binary, 'a', 0, 1) from t1
+-- !query schema
+struct<instr(utf8_binary, a, 0, 1):int>
+-- !query output
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+
+
-- !query
select find_in_set(utf8_binary, utf8_lcase) from t1
-- !query schema
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/string-functions.sql.out
index 2c968a7b2f77..1a8521a37cfc 100644
---
a/sql/core/src/test/resources/sql-tests/results/nonansi/string-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/results/nonansi/string-functions.sql.out
@@ -2365,3 +2365,339 @@ select quote(NULL)
struct<quote(NULL):string>
-- !query output
NULL
+
+
+-- !query
+select instr('abcabc', 'b', 1)
+-- !query schema
+struct<instr(abcabc, b, 1, 1):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'b', 3)
+-- !query schema
+struct<instr(abcabc, b, 3, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'a', 3)
+-- !query schema
+struct<instr(abcabc, a, 3, 1):int>
+-- !query output
+4
+
+
+-- !query
+select instr('abcabc', 'b', -1)
+-- !query schema
+struct<instr(abcabc, b, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -2)
+-- !query schema
+struct<instr(abcabc, b, -2, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -3)
+-- !query schema
+struct<instr(abcabc, b, -3, 1):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'a', -2)
+-- !query schema
+struct<instr(abcabc, a, -2, 1):int>
+-- !query output
+4
+
+
+-- !query
+select instr('abcabc', 'b', 1, 2)
+-- !query schema
+struct<instr(abcabc, b, 1, 2):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', 3, 1)
+-- !query schema
+struct<instr(abcabc, b, 3, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', 1, 3)
+-- !query schema
+struct<instr(abcabc, b, 1, 3):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abcabc', 'b', -1, 1)
+-- !query schema
+struct<instr(abcabc, b, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -1, 2)
+-- !query schema
+struct<instr(abcabc, b, -1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'b', -2, 2)
+-- !query schema
+struct<instr(abcabc, b, -2, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'a', -1, 2)
+-- !query schema
+struct<instr(abcabc, a, -1, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('abcabc', 'b', 0, 1)
+-- !query schema
+struct<instr(abcabc, b, 0, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abcabc', 'b', 0, 2)
+-- !query schema
+struct<instr(abcabc, b, 0, 2):int>
+-- !query output
+0
+
+
+-- !query
+select instr('hello', '', 1)
+-- !query schema
+struct<instr(hello, , 1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', 3)
+-- !query schema
+struct<instr(hello, , 3, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', -1)
+-- !query schema
+struct<instr(hello, , -1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', -2)
+-- !query schema
+struct<instr(hello, , -2, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('', '', 1)
+-- !query schema
+struct<instr(, , 1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('', '', -1)
+-- !query schema
+struct<instr(, , -1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', 1, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, 1, 2):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '你好', -1, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -1, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', -2, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -2, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', -1)
+-- !query schema
+struct<instr(你好世界你好, 你好, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '你好', -2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -2, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '世界', -1)
+-- !query schema
+struct<instr(你好世界你好, 世界, -1, 1):int>
+-- !query output
+3
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 2)
+-- !query schema
+struct<instr(aaaa, aa, 1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 3)
+-- !query schema
+struct<instr(aaaa, aa, 1, 3):int>
+-- !query output
+3
+
+
+-- !query
+select instr('aaaa', 'aa', -1, 2)
+-- !query schema
+struct<instr(aaaa, aa, -1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('aaaa', 'aa', -2, 2)
+-- !query schema
+struct<instr(aaaa, aa, -2, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abc', 'd', 1, 1)
+-- !query schema
+struct<instr(abc, d, 1, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', 1, 3)
+-- !query schema
+struct<instr(abc, b, 1, 3):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', 10, 1)
+-- !query schema
+struct<instr(abc, b, 10, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', -10, 1)
+-- !query schema
+struct<instr(abc, b, -10, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('a', 'b', cast(null as int))
+-- !query schema
+struct<instr(a, b, CAST(NULL AS INT), 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', 'b', 1, cast(null as int))
+-- !query schema
+struct<instr(a, b, 1, CAST(NULL AS INT)):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr(null, 'b', 1)
+-- !query schema
+struct<instr(NULL, b, 1, 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', null, 1)
+-- !query schema
+struct<instr(a, NULL, 1, 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', 'b', cast(null as int), 2)
+-- !query schema
+struct<instr(a, b, CAST(NULL AS INT), 2):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr(null, null, cast(null as int), cast(null as int))
+-- !query schema
+struct<instr(NULL, NULL, CAST(NULL AS INT), CAST(NULL AS INT)):int>
+-- !query output
+NULL
diff --git
a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
index 1d706cba88c9..b0497277c59b 100644
--- a/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/string-functions.sql.out
@@ -2433,3 +2433,339 @@ select quote(NULL)
struct<quote(NULL):string>
-- !query output
NULL
+
+
+-- !query
+select instr('abcabc', 'b', 1)
+-- !query schema
+struct<instr(abcabc, b, 1, 1):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'b', 3)
+-- !query schema
+struct<instr(abcabc, b, 3, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'a', 3)
+-- !query schema
+struct<instr(abcabc, a, 3, 1):int>
+-- !query output
+4
+
+
+-- !query
+select instr('abcabc', 'b', -1)
+-- !query schema
+struct<instr(abcabc, b, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -2)
+-- !query schema
+struct<instr(abcabc, b, -2, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -3)
+-- !query schema
+struct<instr(abcabc, b, -3, 1):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'a', -2)
+-- !query schema
+struct<instr(abcabc, a, -2, 1):int>
+-- !query output
+4
+
+
+-- !query
+select instr('abcabc', 'b', 1, 2)
+-- !query schema
+struct<instr(abcabc, b, 1, 2):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', 3, 1)
+-- !query schema
+struct<instr(abcabc, b, 3, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', 1, 3)
+-- !query schema
+struct<instr(abcabc, b, 1, 3):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abcabc', 'b', -1, 1)
+-- !query schema
+struct<instr(abcabc, b, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('abcabc', 'b', -1, 2)
+-- !query schema
+struct<instr(abcabc, b, -1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'b', -2, 2)
+-- !query schema
+struct<instr(abcabc, b, -2, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abcabc', 'a', -1, 2)
+-- !query schema
+struct<instr(abcabc, a, -1, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('abcabc', 'b', 0, 1)
+-- !query schema
+struct<instr(abcabc, b, 0, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abcabc', 'b', 0, 2)
+-- !query schema
+struct<instr(abcabc, b, 0, 2):int>
+-- !query output
+0
+
+
+-- !query
+select instr('hello', '', 1)
+-- !query schema
+struct<instr(hello, , 1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', 3)
+-- !query schema
+struct<instr(hello, , 3, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', -1)
+-- !query schema
+struct<instr(hello, , -1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('hello', '', -2)
+-- !query schema
+struct<instr(hello, , -2, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('', '', 1)
+-- !query schema
+struct<instr(, , 1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('', '', -1)
+-- !query schema
+struct<instr(, , -1, 1):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', 1, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, 1, 2):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '你好', -1, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -1, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', -2, 2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -2, 2):int>
+-- !query output
+1
+
+
+-- !query
+select instr('你好世界你好', '你好', -1)
+-- !query schema
+struct<instr(你好世界你好, 你好, -1, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '你好', -2)
+-- !query schema
+struct<instr(你好世界你好, 你好, -2, 1):int>
+-- !query output
+5
+
+
+-- !query
+select instr('你好世界你好', '世界', -1)
+-- !query schema
+struct<instr(你好世界你好, 世界, -1, 1):int>
+-- !query output
+3
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 2)
+-- !query schema
+struct<instr(aaaa, aa, 1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('aaaa', 'aa', 1, 3)
+-- !query schema
+struct<instr(aaaa, aa, 1, 3):int>
+-- !query output
+3
+
+
+-- !query
+select instr('aaaa', 'aa', -1, 2)
+-- !query schema
+struct<instr(aaaa, aa, -1, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('aaaa', 'aa', -2, 2)
+-- !query schema
+struct<instr(aaaa, aa, -2, 2):int>
+-- !query output
+2
+
+
+-- !query
+select instr('abc', 'd', 1, 1)
+-- !query schema
+struct<instr(abc, d, 1, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', 1, 3)
+-- !query schema
+struct<instr(abc, b, 1, 3):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', 10, 1)
+-- !query schema
+struct<instr(abc, b, 10, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('abc', 'b', -10, 1)
+-- !query schema
+struct<instr(abc, b, -10, 1):int>
+-- !query output
+0
+
+
+-- !query
+select instr('a', 'b', cast(null as int))
+-- !query schema
+struct<instr(a, b, CAST(NULL AS INT), 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', 'b', 1, cast(null as int))
+-- !query schema
+struct<instr(a, b, 1, CAST(NULL AS INT)):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr(null, 'b', 1)
+-- !query schema
+struct<instr(NULL, b, 1, 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', null, 1)
+-- !query schema
+struct<instr(a, NULL, 1, 1):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr('a', 'b', cast(null as int), 2)
+-- !query schema
+struct<instr(a, b, CAST(NULL AS INT), 2):int>
+-- !query output
+NULL
+
+
+-- !query
+select instr(null, null, cast(null as int), cast(null as int))
+-- !query schema
+struct<instr(NULL, NULL, CAST(NULL AS INT), CAST(NULL AS INT)):int>
+-- !query output
+NULL
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/StringFunctionsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/StringFunctionsSuite.scala
index 772773cb07c8..e5fa22b05ecb 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/StringFunctionsSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/StringFunctionsSuite.scala
@@ -468,6 +468,47 @@ class StringFunctionsSuite extends SharedSparkSession {
checkAnswer(
df.selectExpr("instr(a, b)"),
Row(1))
+
+ checkAnswer(
+ df.selectExpr("instr(a, b, 2)"),
+ Row(2))
+
+ checkAnswer(
+ df.select(instr($"a", $"b", -1)),
+ Row(2))
+
+ checkAnswer(
+ df.selectExpr("instr(a, '', -1)"),
+ Row(1))
+
+ checkAnswer(
+ df.selectExpr("instr(a, b, 1, 2)"),
+ Row(2))
+
+ checkAnswer(
+ df.select(instr($"a", $"b", -1, 2)),
+ Row(1))
+
+ checkAnswer(
+ df.selectExpr("instr(a, '', -1, 2)"),
+ Row(1))
+
+ checkAnswer(
+ df.selectExpr("instr('abcde', 'cd', -3, 1)"),
+ Row(3))
+
+ // Test throw exception when occurrence <= 0
+ checkError(
+ exception = intercept[SparkRuntimeException] {
+ spark.sql("SELECT instr('abc', 'b', 1, 0)").collect()
+ },
+ condition = "INVALID_PARAMETER_VALUE.OCCURRENCE",
+ parameters = Map(
+ "functionName" -> toSQLId("instr"),
+ "parameter" -> toSQLId("occurrence"),
+ "actual" -> "0"
+ )
+ )
}
test("string substring_index function") {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
