ODriscollBoys opened a new pull request, #68868:
URL: https://github.com/apache/doris/pull/68868
### What problem does this PR solve?
Issue Number: close #68716
Related PR: None
Problem Summary:
`substring_index` gives different results depending on whether Nereids folds
the call in FE or BE evaluates it, when the delimiter can overlap itself:
```sql
SELECT substring_index('aaa', 'aa', -1); -- FE fold: 'a', BE: ''
SELECT substring_index('baaab', 'aa', -1); -- FE fold: 'ab', BE: 'b'
SELECT substring_index('ababa', 'aba', -1); -- FE fold: 'ba', BE: ''
SELECT substring_index('aaaaa', 'aa', -2); -- FE fold: 'aaa', BE: 'aa'
```
BE (`FunctionSubstringIndex`, and MySQL as well) matches the delimiter
without overlapping in the direction of the count: from the left for a positive
count, and from the right (`rfind`) for a negative count, so each occurrence
has to end before the previous one starts. The FE implementation split the
string from the left with `String.split` for both directions and then took the
last parts, which picks different occurrences as soon as two occurrences of the
delimiter can overlap. For `('aaa', 'aa', -1)` the split gives `['', 'a']` and
FE returned `a`, while the rightmost `aa` really starts at index 1 and BE
returns the empty string.
The FE folding now walks the occurrences with `indexOf` from the left for a
positive count and with `lastIndexOf` from the right for a negative count, and
returns the whole string when there are fewer occurrences than requested, which
is exactly what BE does. The positive-count result is unchanged. Iterating up
to the count instead of taking `Math.abs` of it also means `Integer.MIN_VALUE`
now folds to the whole string (not enough occurrences) instead of `''`.
### Release note
Fix constant folding of `substring_index` with a negative count when the
delimiter overlaps itself, so the folded result matches the BE result.
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [x] Regression test
- [x] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
Added `StringArithmeticTest` cases for overlapping and ordinary
delimiters in both directions (they fail on the old folding and pass with this
change), and `testFoldConst` cases in `fold_constant_string_arithmatic` that
compare the folded result with the BE result for overlapping delimiters. I also
cross-checked the new folding against a line-by-line port of the BE algorithm
on about 3.9 million random string/delimiter/count combinations with no
mismatch.
- Behavior changed:
- [ ] No.
- [x] Yes. <!-- Explain the behavior change -->
Constant folding of `substring_index` with a negative count and a
self-overlapping delimiter now returns the same result as BE.
- Does this need documentation?
- [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR should
merge into -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
