L. C. Hsieh created SPARK-60632:
-----------------------------------
Summary: LIKE/RLIKE expensive heuristic checks the wrong operand
and loses filter pushdown
Key: SPARK-60632
URL: https://issues.apache.org/jira/browse/SPARK-60632
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: L. C. Hsieh
SPARK-47672 added an `expensive` heuristic to `StringRegexExpression`
(LIKE/RLIKE) so that `PushPredicateThroughNonJoin` avoids double evaluation of
expensive aliases when `spark.sql.optimizer.avoidDoubleFilterEval` is true (the
default since 4.2.0). The heuristic has several bugs:
1. It matches on `left`, the subject string, instead of `right`, the pattern.
For the usual `col LIKE/RLIKE 'literal'` it falls into `case _ => true`, so
every LIKE/RLIKE over a column is classed expensive, including `'^1'`, which
the code's own comment says should be cheap.
2. The backtracking check `Pattern.compile("\\+\\*\\{").matcher(str).matches()`
only matches the literal 3-character string `+*{`, so it never fires.
3. The `^` / `\b` anchor checks are regex-specific but are also applied to LIKE
patterns.
4. The detection `Pattern` is a per-instance `val`, so it is compiled for every
Like/RLike instance.
User-visible impact: a filter on an alias of a LIKE/RLIKE expression is no
longer pushed below the Project, which loses partition pruning and data filter
pushdown. For example, over a Parquet table partitioned by `part`:
{code:sql}
SELECT * FROM (SELECT name, part RLIKE '^1' AS p FROM t) WHERE p
{code}
gets `PartitionFilters: []` with the default config, but prunes partitions with
`spark.sql.optimizer.avoidDoubleFilterEval=false`. The same happens for `part
LIKE '1_' OR part = '1'` and for data filters such as `name RLIKE '^1'`. (`LIKE
'x%'` is not affected because `LikeSimplification` rewrites it to `StartsWith`
first.)
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
