David Mollitor created SPARK-59185:
--------------------------------------
Summary: Derive a StartsWith prefix filter from leading-literal
LIKE patterns
Key: SPARK-59185
URL: https://issues.apache.org/jira/browse/SPARK-59185
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 1.4.0
Reporter: David Mollitor
h3. What
{{LikeSimplification}} rewrites simple {{LIKE}} patterns into cheaper
predicates:
{{'A%'}} -> {{{}StartsWith{}}}, {{'%B'}} -> {{{}EndsWith{}}}, {{'A%B'}} ->
length guard +
{{StartsWith}} + {{{}EndsWith{}}}, {{'%B%'}} -> {{{}Contains{}}}, and an exact
string -> {{{}EqualTo{}}}.
Multi-wildcard patterns that have a leading literal but do not match any of
those shapes –
e.g. {{{}'A%B%'{}}}, {{{}'AB%CD%EF'{}}}, {{'A_B%'}} – fall through unchanged
and remain a full regex
{{{}Like{}}}. Nothing is derived from the leading literal, so the data source
receives no
predicate and the per-row regex runs on every row.
This improvement makes {{LikeSimplification}} additionally derive the leading
literal {{A}}
as the necessary condition {{{}StartsWith(col, A){}}}, keeping the original
{{LIKE}} as the exact
residual:
{code:java}
col LIKE 'A%B%' ==> StartsWith(col, A) && (col LIKE 'A%B%')
{code}
{{StartsWith}} is placed first so the cheap check short-circuits the regex, and
it is a
predicate the existing pushdown path already understands.
h3. Why
* {{StartsWith}} on a UTF8_BINARY column translates to
{{sources.StringStartsWith}} and lets Parquet prune row groups via min/max
statistics (readers cannot prune on the raw {{{}Like{}}}).
* On rows that fail the prefix, the cheap {{StartsWith}} short-circuits the
more expensive
regex match.
* Results are unchanged: {{StartsWith(A)}} is implied by {{{}LIKE 'A%...'{}}},
and the exact
{{LIKE}} is retained as the residual, so the conjunction accepts exactly the
same rows.
h3. Restricted to binary-equality collations
The derivation only fires when the input collation has binary equality
({{{}StringType.supportsBinaryEquality{}}}, i.e. UTF8_BINARY). Two reasons:
* *Correctness.* {{Like}} matches via a Java regex with collation-aware case
flags, whereas {{StartsWith}} matches via {{{}CollationSupport{}}}. Under a
non-binary collation (e.g. UTF8_LCASE) these two case-folding engines can
disagree on edge cases, so {{StartsWith(A)}} would not be a guaranteed
necessary condition and could wrongly drop a matching row. Under binary equality
both are exact byte matching, so the implication holds.
* *Benefit.* {{StringStartsWith}} is pushed down (and prunes in Parquet) only
for UTF8_BINARY; for non-binary collations it is wrapped as
{{{}CollatedStringStartsWith{}}}, which readers ignore.
h3. Example
{code:java}
-- before: full regex, no pushdown
Filter (col LIKE 'ab%cd%')
-- after: prefix pushes down and prunes; LIKE re-checks exactly
Filter (StartsWith(col, 'ab') AND (col LIKE 'ab%cd%'))
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
