[Enhancement] Rewrite element_at(split_by_string(s,sep),n) to split_part in filter conjuncts
- Dominant language
- Java
- Stars
- 15.9k
- Forks
- 3.9k
- Avg merge
- 2d 23h
- Merged PRs (30d)
- 520
Description
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Description
`element_at(split_by_string(s, sep), n)` materializes the entire split array (a PODArray allocation plus a memcpy per part, per row) and then selects a single entry. When such an expression is the root of a top-level filter conjunct, it can be rewritten to `split_part(s, sep, n)`, which short-circuits as soon as it locates the n-th delimiter — eliminating the intermediate array and the trailing-parts work.
The rewrite must be semantics-preserving. The two forms differ only when the string yields no n-th part: `element_at` returns NULL while `split_part` can return `''`. This can be neutralized by restricting the rewrite to the root of a top-level filter conjunct (where NULL and false both drop the row) and guarding it (non-empty string-literal comparison RHS, non-empty literal separator, non-negative literal index), and by not recursing into NOT / OR / CASE where NULL and `''` are no longer interchangeable.
### Use case
Queries filtering on a specific token of a delimited string column, e.g. `WHERE split_by_string(col, ',')[1] = 'x'`, run a per-row array build today; the rewrite makes them scan-friendlier.
### Related issues
_No response_
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
Contributor guide
Research direction
The issue names no files or tests. Start by locating Doris's filter-conjunct expression-rewrite or optimizer entry point and the handling for split_by_string, element_at, and split_part; done means the guarded top-level-conjunct rewrite preserves the stated NULL behavior and excludes NOT, OR, and CASE contexts.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 55/100