OpenJPA
  1. OpenJPA
  2. OPENJPA-1308

shouldn't always use CAST with UPPER and LOWER for DB2

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 1.0.3, 1.2.1, 2.0.0-M2, 2.0.0-M3
    • Fix Version/s: 1.0.4, 1.2.2, 1.3.0, 2.0.0-beta
    • Component/s: sql
    • Labels:
      None
    • Environment:
      DB2
    • Patch Info:
      Patch Available

      Description

      Using DB2, when a user uses the UPPER or LOWER function, the field is automatically CAST to a varchar. It would be nice if OpenJPA were a little smarter and only used CAST when necessary. Using the CAST causes any indexes not to be used so performance won't be what it should.

      I saw some other DB2 functions that also are always CAST, so we may need to improve these also.

      1. OPENJPA-1308-1.0.x.patch
        16 kB
        B.J. Reed
      2. OPENJPA-1308-1.2.x.patch
        12 kB
        B.J. Reed
      3. OPENJPA-1308-trunk.patch
        13 kB
        B.J. Reed

        Activity

        Hide
        Michael Dick added a comment -

        Thanks for the patches, BJ. The fix is in the latest SNAPSHOT builds.

        Show
        Michael Dick added a comment - Thanks for the patches, BJ. The fix is in the latest SNAPSHOT builds.
        Hide
        B.J. Reed added a comment -

        new patches include all changes from code review

        Show
        B.J. Reed added a comment - new patches include all changes from code review
        Hide
        B.J. Reed added a comment -

        Replacing old patches with new ones...Slightly better

        Show
        B.J. Reed added a comment - Replacing old patches with new ones...Slightly better
        Hide
        B.J. Reed added a comment -

        Attached patches to be considered for inclusion to OpenJPA. No real differences in the patches except that the 1.0.x patch includes a new test case that wasn't already present in 1.0.x (the other 2 just modify the existing test case).

        For the most part, the change is that when UPPER or LOWER are used, DB2Dictionary looks to see what the database column is defined as before doing the cast. In this way, if the column is already a VARCHAR, then indexes would still be used since no cast would be generated.

        Show
        B.J. Reed added a comment - Attached patches to be considered for inclusion to OpenJPA. No real differences in the patches except that the 1.0.x patch includes a new test case that wasn't already present in 1.0.x (the other 2 just modify the existing test case). For the most part, the change is that when UPPER or LOWER are used, DB2Dictionary looks to see what the database column is defined as before doing the cast. In this way, if the column is already a VARCHAR, then indexes would still be used since no cast would be generated.

          People

          • Assignee:
            Michael Dick
            Reporter:
            B.J. Reed
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development