This is an automated email from the ASF dual-hosted git repository.
LuciferYang 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 c8a3f3d6f99b [SPARK-60028][SQL] Do not split LIKE 'prefix%suffix' over
a nondeterministic input
c8a3f3d6f99b is described below
commit c8a3f3d6f99bc43b955abdcd27d6256f24860d8c
Author: YangJie <[email protected]>
AuthorDate: Mon Oct 12 10:58:51 2026 +0800
[SPARK-60028][SQL] Do not split LIKE 'prefix%suffix' over a
nondeterministic input
### What changes were proposed in this pull request?
`LikeSimplification` rewrites `input LIKE 'prefix%suffix'` into a length
check, a `StartsWith` and an `EndsWith`, each over its own copy of `input`.
This PR makes that rewrite only when the input is deterministic. A
nondeterministic input keeps the `LIKE`. The other single-pattern rewrites read
the input once and are unchanged.
### Why are the changes needed?
A nondeterministic input is evaluated once per copy, so the three checks
can each see a different value and pass together for a row where no single
value matches the pattern:
```scala
val pick = udf(() => if (scala.util.Random.nextBoolean()) "ay" else
"bz").asNondeterministic()
spark.udf.register("pick", pick)
spark.sql(
"SELECT count_if(r) FROM (SELECT pick() LIKE 'a%z' AS r FROM range(0,
1000, 1, 1))").show()
// a few hundred on master (239 in one run); 0 with this change
```
The optimized plan on master computes `r` as `((octet_length(pick()) >= 2)
AND (StartsWith(pick(), a) AND EndsWith(pick(), z)))`. `pick() ILIKE 'A%Z'`
becomes `lower(pick()) LIKE 'a%z'` and fails the same way. The `LIKE ALL` and
`LIKE ANY` rewrites already skip an input that is not cheap.
### Does this PR introduce _any_ user-facing change?
Yes. `LIKE` or `ILIKE` with a `'prefix%suffix'` pattern over a
nondeterministic input now evaluates the input once per row, so the result is
that of matching that one value. Released versions have the same rewrite, with
`length` instead of `octet_length` in the length check.
### How was this patch tested?
`LikeSimplificationSuite`: `uuid() LIKE 'abc%def'` is left unchanged,
`uuid() LIKE 'abc%'` is still rewritten to `StartsWith`, and `substring(a, 1,
5) LIKE 'abc%def'`, deterministic but not cheap, is still split into the length
check, `StartsWith` and `EndsWith`. The first assertion fails without the
change.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Opus 5
Closes #59300 from LuciferYang/SPARK-60028.
Lead-authored-by: YangJie <[email protected]>
Co-authored-by: reviewer <reviewer@local>
Signed-off-by: yangjie01 <[email protected]>
---
.../spark/sql/catalyst/optimizer/expressions.scala | 4 +++-
.../catalyst/optimizer/LikeSimplificationSuite.scala | 19 +++++++++++++++++++
2 files changed, 22 insertions(+), 1 deletion(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala
index 23eb8009e247..671ce87d4fa2 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/expressions.scala
@@ -903,7 +903,9 @@ object LikeSimplification extends Rule[LogicalPlan] with
PredicateHelper {
Some(EndsWith(input, Literal.create(postfix, input.dataType)))
// 'a%a' pattern is basically same with 'a%' && '%a'.
// However, the additional length condition is required to prevent 'a'
match 'a%a'.
- case startsAndEndsWith(prefix, postfix) =>
+ // The replacement reads the input three times, so it is made only for
a deterministic
+ // input; a nondeterministic one could give each read a different
value.
+ case startsAndEndsWith(prefix, postfix) if input.deterministic =>
// The length guard only rejects inputs too short to hold both the
prefix and the
// suffix. When the collation matches raw bytes
(supportsBinaryEquality),
// StartsWith/EndsWith pin the literal bytes of the prefix and
suffix, so a
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LikeSimplificationSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LikeSimplificationSuite.scala
index 8b142f0c53d7..911531ff19d6 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LikeSimplificationSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/LikeSimplificationSuite.scala
@@ -77,6 +77,25 @@ class LikeSimplificationSuite extends PlanTest {
comparePlans(optimized, correctAnswer)
}
+ test("SPARK-60028: do not simplify 'prefix%suffix' over a nondeterministic
input") {
+ // The replacement reads the input three times, and each read could see a
different value.
+ val input = Uuid(Some(0L))
+ val originalQuery = testRelation.where(input like "abc%def").analyze
+ comparePlans(Optimize.execute(originalQuery), originalQuery)
+
+ // A replacement that reads the input once is still made.
+ comparePlans(
+ Optimize.execute(testRelation.where(input like "abc%").analyze),
+ testRelation.where(StartsWith(input, "abc")).analyze)
+
+ // The three-read replacement is still made for a deterministic input,
even one not cheap.
+ val substr = $"a".substring(1, 5)
+ comparePlans(
+ Optimize.execute(testRelation.where(substr like "abc%def").analyze),
+ testRelation.where(OctetLength(substr) >= 6 &&
+ (StartsWith(substr, "abc") && EndsWith(substr, "def"))).analyze)
+ }
+
test("simplify Like into Contains") {
val originalQuery =
testRelation
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
