[
https://issues.apache.org/jira/browse/CALCITE-7926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Atharv Rastogi updated CALCITE-7926:
------------------------------------
Priority: Minor (was: Major)
> 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: Minor
>
> 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)
