surawut-jirasaktavee commented on issue #68716:
URL: https://github.com/apache/doris/issues/68716#issuecomment-6129245888
I reproduced this on 4.1.4 (`doris-4.1.4-rc04-ad35a140c7f`) and I'd like to
fix it. Before I write the code, I need a maintainer to confirm which result is
intended, because MySQL does not give one answer here.
The two sides disagree only when the count is negative and the delimiter can
overlap itself. Positive counts and non-overlapping delimiters such as `,`,
`ab`, `||`, and multi-byte delimiters give the same result on FE and BE.
| `substring_index(s, 'aa', n)` | FE folding | BE | MySQL 8.4, latin1 |
MySQL 8.4, utf8mb4 |
|---|---|---|---|---|
| `s='aaa'`, n=-1 | `a` | `''` | `''` | `a` |
| `s='aaaaa'`, n=-1 | `a` | `''` | `''` | `a` |
| `s='aaaaa'`, n=-2 | `aaa` | `aa` | `aa` | `aaa` |
- FE (`StringArithmetic.substringIndex`) splits from the left with
`String.split`, so it finds delimiters left to right.
- BE (`FunctionSubstringIndex`) uses `rfind` for a negative count, so it
finds delimiters right to left.
- MySQL 8.4 does both. Its single-byte charset path scans from the right,
which matches Doris BE. Its multi-byte charset path, used for utf8mb4, scans
from the left, which matches Doris FE.
There are two ways to fix this:
1. **FE matches BE.** Change only the FE folding to scan from the right.
This follows the documentation ("returns content after the |field|-th delimiter
from the right"), and results for column data do not change. Only queries whose
arguments are all constants change.
2. **BE matches FE.** Change the BE function to scan from the left. This
matches MySQL with utf8mb4, but it changes the results of existing queries over
stored data.
I plan to do option 1, with an FE unit test and `testFoldConst` regression
cases that cover both overlapping and non-overlapping delimiters. Does that
match the intended behavior, or should Doris follow MySQL utf8mb4 instead?
--
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]
