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

            Activity

            Ascending order - Click to sort in descending order
            Hide
            Permalink
            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.
            Hide
            Permalink
            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
            Permalink
            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
            Permalink
            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.

              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