James Xu created SPARK-60841:
--------------------------------
Summary: LIKE on UTF8_LCASE strings can silently return wrong
results after optimizer rewrites
Key: SPARK-60841
URL: https://issues.apache.org/jira/browse/SPARK-60841
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: James Xu
h2. Problem
When a UTF8_LCASE string is compared with LIKE using a pattern of a simple shape
({{'ab%'}}, {{'%bc'}}, {{'a%c'}}, {{'%b%'}}, {{'abc'}}), the LikeSimplification
optimizer rule rewrites it into a cheaper predicate (StartsWith / EndsWith /
Contains / EqualTo). These predicates evaluate under UTF8_LCASE via ICU
lowercase
mapping, while LIKE itself matches via Java regex case folding. The two mappings
disagree for some code points, so the rewritten predicate accepts or rejects
rows
that the original LIKE would not.
Example:
{code:sql}
SELECT like(collate('ı', 'UTF8_LCASE'), '%i%')
{code}
This returns true: Java regex case folding treats dotless i (U+0131) as matching
'i'. The rewrite, {{Contains(collate('ı', 'UTF8_LCASE'), 'i')}}, evaluates via
ICU
lowercase mapping, under which U+0131 does not match 'i' -- so against a column
the optimized query returns false where the unoptimized query returns true.
The {{'prefix%suffix'}} split has a related hazard (SPARK-59063): the anchors
pin
literal pattern bytes while UTF8_LCASE LIKE folds code points whose UTF-8 length
differs from the pattern's (e.g. the Kelvin sign U+212A, three bytes folding to
'k'), so a single multibyte code point can satisfy both anchors and clear the
length guard.
h2. Root Cause
LikeSimplification rewrites simple-shape LIKE patterns without checking the
input
collation. Under UTF8_LCASE, the replacement predicates use ICU lowercase-based
comparison, while LIKE matches through Java regex case folding. The two disagree
for code points such as U+0131 (dotless i) and U+212A (Kelvin sign). The
code-point Length guard added in SPARK-59063 only addresses the length aspect of
the {{'prefix%suffix'}} split; it cannot reconcile the anchor/Contains matching
semantics, so any rewrite of a UTF8_LCASE LIKE is unsafe.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
