apache / apache/doris

[Enhancement] Rewrite element_at(split_by_string(s,sep),n) to split_part in filter conjuncts

Open
#66,179 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.