Atharv Rastogi created CALCITE-7926:
---------------------------------------
Summary: SUBSTRING_INDEX returns incorrect results for negative
counts
Key: CALCITE-7926
URL: https://issues.apache.org/jira/browse/CALCITE-7926
Project: Calcite
Issue Type: Bug
Reporter: Atharv Rastogi
Assignee: Atharv Rastogi
{{SUBSTRING_INDEX}} returns incorrect results when a negative count requires
finding more than one delimiter from the right.
Reproduced against the Java implementation in Calcite *1.42.0* and upstream
{{main}} at commit {{{}f8963be2d5e0224bbbfe141cda14211b501b2f85{}}}.
{{SELECT SUBSTRING_INDEX('a.b.c', '.', -2); }}{{-- Expected: 'b.c'
-- Actual: '.c' }}
{{}}
{code:java}
SELECT SUBSTRING_INDEX('www.apache.org', '.', -2);{code}
{{-- Expected: 'apache.org'
-- Actual: '.org'}}
For a negative count, the function should return everything to the right of the
specified delimiter occurrence, counting from the right.
The problem appears to be in {{{}SqlFunctions.rfind(){}}}. Although intended to
search backwards, it calls {{String.indexOf(delimiter, start)}} and returns
{{start}} whenever a match exists at or after that position. Consequently, it
can return a position that does not contain a delimiter.
This affects runtime evaluation through {{SqlFunctions}} and constant
expression reduction, where an incorrect result can become a folded literal.
The proposed fix is to use {{String.lastIndexOf(delimiter, start)}} for the
backwards search, with regression coverage for negative counts, multi-character
delimiters, and counts exceeding the available occurrences. Constant-reduction
tests should verify that folding produces the same expected results.
Related issue: CALCITE-5807 — Add SUBSTRING_INDEX function.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
