OpenJPA
  1. OpenJPA
  2. OPENJPA-867

Unexpected Behaviour of DBDictionary.indexOf() method

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.2.0, 2.0.0-M2
    • Fix Version/s: 2.2.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      openjpa-jdbc 1.2.0 and trunk

      Description

      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.

        Activity

        Alan Raison created issue -
        Milosz Tylenda made changes -
        Field Original Value New Value
        Assignee Milosz Tylenda [ milosz ]
        Hide
        Milosz Tylenda added a comment -

        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.

        Show
        Milosz Tylenda added a comment - 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.
        Milosz Tylenda made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Milosz Tylenda made changes -
        Fix Version/s 2.2.0 [ 12315910 ]
        Hide
        Milosz Tylenda added a comment -

        Resolving for the databases with 3-argument LOCATE functions. Others (e.g. PostgreSQL) have to still live with the SUBSTRING trick.

        Show
        Milosz Tylenda added a comment - Resolving for the databases with 3-argument LOCATE functions. Others (e.g. PostgreSQL) have to still live with the SUBSTRING trick.
        Milosz Tylenda made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Milosz Tylenda made changes -
        Component/s sql [ 12311310 ]
        Hide
        Albert Lee added a comment -

        Close issue in preparation for 2.2.0 release.

        Show
        Albert Lee added a comment - Close issue in preparation for 2.2.0 release.
        Albert Lee made changes -
        Status Resolved [ 5 ] Closed [ 6 ]

          People

          • Assignee:
            Milosz Tylenda
            Reporter:
            Alan Raison
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development