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-trunk.patch
        13 kB
        B.J. Reed
      2. OPENJPA-1308-1.2.x.patch
        12 kB
        B.J. Reed
      3. OPENJPA-1308-1.0.x.patch
        16 kB
        B.J. Reed

        Activity

        Donald Woods made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Donald Woods made changes -
        Fix Version/s 1.3.0 [ 12313326 ]
        Fix Version/s 2.0.0-M4 [ 12314149 ]
        Fix Version/s 2.0.0 [ 12314019 ]
        Affects Version/s 2.0.0-M3 [ 12314148 ]
        Darren Woods committed 830260 (18 files)
        Reviews: none

        OPENJPA-1308 shouldn't always use CAST with UPPER and LOWER for DB2. merged in from trunk/1.2.2.

        openjpa 1.3.x
        Michael Dick made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        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.
        Michael Dick made changes -
        Issue Type Improvement [ 4 ] Bug [ 1 ]
        Michael Dick made changes -
        Fix Version/s 1.0.4 [ 12313301 ]
        Fix Version/s 1.2.2 [ 12313681 ]
        Michael Dick made changes -
        Assignee Michael Dick [ mikedd ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-1.2.x.patch [ 12422562 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-trunk.patch [ 12422561 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-1.0.x.patch [ 12422563 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-1.0.x.patch [ 12422700 ]
        Attachment OPENJPA-1308-1.2.x.patch [ 12422701 ]
        Attachment OPENJPA-1308-trunk.patch [ 12422702 ]
        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
        B.J. Reed made changes -
        Attachment OPENJPA-1308-1.2.x.patch [ 12422237 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-trunk.patch [ 12422238 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-1.0.x.patch [ 12422236 ]
        B.J. Reed made changes -
        Attachment OPENJPA-1308-trunk.patch [ 12422561 ]
        Attachment OPENJPA-1308-1.2.x.patch [ 12422562 ]
        Attachment OPENJPA-1308-1.0.x.patch [ 12422563 ]
        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
        Donald Woods made changes -
        Patch Info [Patch Available]
        B.J. Reed made changes -
        Field Original Value New Value
        Attachment OPENJPA-1308-1.0.x.patch [ 12422236 ]
        Attachment OPENJPA-1308-1.2.x.patch [ 12422237 ]
        Attachment OPENJPA-1308-trunk.patch [ 12422238 ]
        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.
        B.J. Reed created issue -

          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