Uploaded image for project: 'OpenJPA'
  1. OpenJPA
  2. OPENJPA-1308

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: 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
        bjreed 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
        bjreed 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.
        Hide
        bjreed B.J. Reed added a comment -

        Replacing old patches with new ones...Slightly better

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

        new patches include all changes from code review

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

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

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

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development