| Bug #98042 | Substring_index returns wrong result using count=-1 and delimiter underscore | ||
|---|---|---|---|
| Submitted: | 20 Dec 2019 18:22 | Modified: | 3 Jan 2020 20:45 |
| Reporter: | Robert Roland | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.018 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | SUBSTRING_INDEX | ||
[3 Jan 2020 7:08]
MySQL Verification Team
Hello Robert Roland, Thank you for the report and test case. Imho this is not a bug because - quoting from manual "SUBSTRING_INDEX(str,delim,count) - Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned" - https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring-index -- If count is negative, everything to the right of the final delimiter (counting from the right) is returned - here there is nothing in the right of the final delimiter and thus result is empty mysql> select substring_index("_ExpectedResult_","_",-1); +--------------------------------------------+ | substring_index("_ExpectedResult_","_",-1) | +--------------------------------------------+ | | +--------------------------------------------+ 1 row in set (0.00 sec) -- If count is positive, everything to the left of the final delimiter (counting from the left) is returned - here there is nothing in the left of the final delimiter and thus result is empty mysql> select substring_index("_ExpectedResult_","_",1); +-------------------------------------------+ | substring_index("_ExpectedResult_","_",1) | +-------------------------------------------+ | | +-------------------------------------------+ 1 row in set (0.00 sec) -- Below result should consult mysql> select substring_index("_ExpectedResult_THIS-SHOULD-RETURN-NEG","_",-1); +------------------------------------------------------------------+ | substring_index("_ExpectedResult_THIS-SHOULD-RETURN-NEG","_",-1) | +------------------------------------------------------------------+ | THIS-SHOULD-RETURN-NEG | +------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select substring_index("THIS-SHOULD-RETURN-POS_ExpectedResult_THIS-SHOULD-RETURN-NEG","_",1); +---------------------------------------------------------------------------------------+ | substring_index("THIS-SHOULD-RETURN-POS_ExpectedResult_THIS-SHOULD-RETURN-NEG","_",1) | +---------------------------------------------------------------------------------------+ | THIS-SHOULD-RETURN-POS | +---------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Moreover, this is not about _(wildcard char) but even with any chars you would get the same results: mysql> mysql> select substring_index("$ExpectedResult$","$",-1); +--------------------------------------------+ | substring_index("$ExpectedResult$","$",-1) | +--------------------------------------------+ | | +--------------------------------------------+ 1 row in set (0.00 sec) mysql> select substring_index("$ExpectedResult$","$",1); +-------------------------------------------+ | substring_index("$ExpectedResult$","$",1) | +-------------------------------------------+ | | +-------------------------------------------+ 1 row in set (0.00 sec) Or am I missing anything in the report? Please let me know. Thank you! regards, Umesh
[3 Jan 2020 20:45]
Robert Roland
I agree with you. It is not a bug. Please close this issue.
[4 Jan 2020 7:54]
MySQL Verification Team
Thank you for confirming, closing now. regards, Umesh
Description: select substring_index("_ExpectedResult_","_",-1); returns nothing, yet select substring_index("XExpectedResult_","X",-1); returns "ExpectedResult" How to repeat: select substring_index("_ExpectedResult_","_",-1); Suggested fix: select substring_index("_ExpectedResult_","_",-1); should return "ExpectedResult"