Search before asking
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?
Code of Conduct
Search before asking
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 tosplit_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_atreturns NULL whilesplit_partcan 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?
Code of Conduct