[
https://issues.apache.org/jira/browse/CALCITE-7926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Atharv Rastogi updated CALCITE-7926:
------------------------------------
Description:
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.
Reproducer:
SELECT SUBSTRING_INDEX('a.b.c', '.', -2);
Expected: 'b.c'
Actual: '.c'
SELECT SUBSTRING_INDEX('[abc.apache.org|http://www.apache.org/]', '.', -2);
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.
Root cause:
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.
Proposed fix:
Use String.lastIndexOf(delimiter, start) for the backwards search.
Add regression tests covering negative counts requiring multiple delimiter
matches, multi-character delimiters, counts exceeding the available
occurrences, and constant reduction producing the expected folded literals.
Related issue: CALCITE-5807 — Add SUBSTRING_INDEX function (enabled in Spark
library).
was:
{{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.
> 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
> Priority: Major
>
> 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.
> Reproducer:
> SELECT SUBSTRING_INDEX('a.b.c', '.', -2);
> Expected: 'b.c'
> Actual: '.c'
> SELECT SUBSTRING_INDEX('[abc.apache.org|http://www.apache.org/]', '.', -2);
> 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.
> Root cause:
> 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.
> Proposed fix:
> Use String.lastIndexOf(delimiter, start) for the backwards search.
> Add regression tests covering negative counts requiring multiple delimiter
> matches, multi-character delimiters, counts exceeding the available
> occurrences, and constant reduction producing the expected folded literals.
> Related issue: CALCITE-5807 — Add SUBSTRING_INDEX function (enabled in Spark
> library).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
