[
https://issues.apache.org/jira/browse/SPARK-59348?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Uroš Bojanić resolved SPARK-59348.
----------------------------------
Fix Version/s: 4.4.0
Resolution: Fixed
Issue resolved by pull request 58637
[https://github.com/apache/spark/pull/58637]
> Rewrite match-all LIKE '%' to IsNotNull in predicates
> -----------------------------------------------------
>
> Key: SPARK-59348
> URL: https://issues.apache.org/jira/browse/SPARK-59348
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.1.0
> Reporter: David Mollitor
> Assignee: David Mollitor
> Priority: Minor
> Labels: pull-request-available
> Fix For: 4.4.0
>
>
> h3. What
> {{col LIKE '%'}} (a pattern of only unescaped {{{}%{}}}) matches every
> non-null value, so a filter{{{}WHERE col LIKE '%'{}}} keeps exactly the
> non-null rows. Today Spark evaluates it as a per-row regex ({{{}.*{}}}) and
> pushes nothing to the data source. This rewrites such patterns, in
> null-rejecting predicate positions, to {{{}IsNotNull(col){}}}.
> {code:java}
> Aggregate [count(1)] Aggregate [count(1)]
> +- Filter (col LIKE '%') ==> +- Filter (isnotnull(col))
> +- Relation t +- Relation t
> {code}
> h3. Why are the changes needed?
> * {{IsNotNull}} is far cheaper than compiling and running a regex per row.
> * {{IsNotNull}} pushes down: Parquet/ORC skip row groups via null-count
> statistics; the raw {{LIKE}} regex does not.
> * If {{col}} is non-nullable, {{IsNotNull(col)}} then folds to {{true}} and
> downstream
> {{{}PruneFilters{}}}/{{{}NullPropagation{}}} drop the filter entirely.
> The pattern shows up in practice from dynamically-generated SQL (an empty
> search box becoming {{{}LIKE '%'{}}}).
> h3. Correctness
> {{col LIKE '%'}} returns {{true}} for a non-null value and {{null}} for
> {{{}null{}}}, whereas
> {{IsNotNull(col)}} returns {{false}} for {{{}null{}}}. They are equivalent
> o{_}nly in null-rejecting{_}
> {_}predicate positions{_}, where {{null}} is treated as {{{}false{}}}. The
> rewrite is therefore
> implemented in {{{}ReplaceNullWithFalseInPredicate{}}}, which already applies
> this kind of null-as-false simplification (see its existing {{Not(In(...))}}
> handling) and which:
> * targets exactly the null-rejecting plan nodes ({{{}Filter{}}}, {{Join}}
> condition, {{{}MergeIntoTable{}}}, {{{}DeleteFromTable{}}},
> {{{}UpdateTable{}}}, {{{}ReplaceData{}}}, {{{}WriteDelta{}}}); and recurses
> only through null-preserving operators ({{{}And{}}}, {{{}Or{}}}, {{{}If{}}},
> {{{}CaseWhen{}}}) and *not* into {{Not}} – so {{NOT (col LIKE '%')}} and
> {{col LIKE '%'}} in a projection are left untouched, where {{null}} vs
> {{false}} would be observable.
> The rewrite fires only for a literal pattern of one or more {{%}} with the
> escape character not being {{%}} (so the {{%}} are wildcards, not escaped
> literals); {{{}LIKE ''{}}}, prefixes, and {{_}}
> patterns are excluded. No collation gate is needed – {{'%'}} matches every
> non-null value under any collation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
