[
https://issues.apache.org/jira/browse/OPENJPA-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13088223#comment-13088223
]
Milosz Tylenda commented on OPENJPA-867:
----------------------------------------
It turns out that the following databases do have a 3-argument version of
LOCATE-like functions making the trick with SUBSTRING obsolete:
- Oracle (INSTR),
- MySQL (LOCATE),
- H2 (LOCATE),
- SQLServer (CHARINDEX).
Sybase supports LOCATE only in some of database flavours. Since it is not clear
for me which flavours support it (seems IQ does and ASE does not) and I don't
have access to a Sybase instance, I am going to leave the current behaviour on
Sybase as is. If anyone knows which Sybase databases support the 3-argument
LOCATE and how to tell them, please speak up.
> Unexpected Behaviour of DBDictionary.indexOf() method
> -----------------------------------------------------
>
> Key: OPENJPA-867
> URL: https://issues.apache.org/jira/browse/OPENJPA-867
> Project: OpenJPA
> Issue Type: Bug
> Affects Versions: 1.2.0, 2.0.0-M2
> Environment: openjpa-jdbc 1.2.0 and trunk
> Reporter: Alan Raison
> Assignee: Milosz Tylenda
> Priority: Minor
>
> There is potentially unexpected behaviour of the indexOf method of the
> DBDictionary class (org.apache.openjpa.jdbc.sql.DBDictionary) when a start
> index is specified but the search string is not found.
> When a start index is specified (say "N"), the search target string has the
> first N characters removed, the search performed (by INSTR in the default
> case), this is reduced by 1 to make it 0-indexed and then crucially the start
> index in added to the result.
> In Oracle, if the search term is not found, INSTR returns 0. If a start
> index is supplied, this is then added and 1 is taken away, so the "result of
> indexOf" will be "start index - 1" if the search string is not found. It may
> not be obvious whether, once a query is run, a number represents a successful
> match or not.
> I would expect the case where the string is not found to return 0 or -1,
> depending on the index base. I think it is misleading for this to return a
> positive integer if the string is not found.
> Since you cannot tell whether the string will be matched at the time the
> query is constructed, it may be difficult to find a fix for this.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
