OpenJPA
  1. OpenJPA
  2. OPENJPA-487

Generated SUBSTRING SQL is ugly and inefficient

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 0.9.0, 0.9.6, 0.9.7, 1.0.0, 1.0.1, 1.0.2, 1.1.0
    • Fix Version/s: 2.2.0
    • Component/s: sql
    • Labels:
      None

      Description

      When performing a JPQL query with a SUBSTRING clause, OpenJPA converts from one-based index + length parameters to zero-based index + end index for internal handling, and then re-converts back to one-based + length when generating SQL. This conversion is entirely performed in query values, so the generated SQL has a lot of math in it. This extra math is never necessary.

        Issue Links

          Activity

          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.
          Hide
          Milosz Tylenda added a comment -

          I noticed that parameters in GROUP BY are handled in a half-baked fashion -
          parameter markers are correctly inserted into SQL but are not filled with
          values. A patch is attached but seems unnecessary since most databases will
          reject parameters in GROUP BY anyway. I found only MySQL accepting such a
          query (TestJDBCGrouping.testSubstringInGroupBy), probably because MySQL
          only emulates prepared statements by default:

          SELECT SUBSTRING(t0.stringField, ?, ?), COUNT(t0.id) FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField, ?, ?)

          Other databases require parameters be inlined into SQL:

          SELECT SUBSTRING(t0.stringField, 1, 1), COUNT(t0.id) FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField, 1, 1)

          That's why DBDictionary.substring still uses inlining where possible.

          Show
          Milosz Tylenda added a comment - I noticed that parameters in GROUP BY are handled in a half-baked fashion - parameter markers are correctly inserted into SQL but are not filled with values. A patch is attached but seems unnecessary since most databases will reject parameters in GROUP BY anyway. I found only MySQL accepting such a query (TestJDBCGrouping.testSubstringInGroupBy), probably because MySQL only emulates prepared statements by default: SELECT SUBSTRING(t0.stringField, ?, ?), COUNT(t0.id) FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField, ?, ?) Other databases require parameters be inlined into SQL: SELECT SUBSTRING(t0.stringField, 1, 1), COUNT(t0.id) FROM AllFieldTypes t0 GROUP BY SUBSTRING(t0.stringField, 1, 1) That's why DBDictionary.substring still uses inlining where possible.
          Hide
          Milosz Tylenda added a comment -

          Switched expressions from JDO style (0-based index, end index provided to substring)
          to JPA style (1-based index, length provided to substring). Same change also applied
          to LOCATE function.

          JPQL:
          UPDATE CompUser e SET e.name = CONCAT('Ablahum', SUBSTRING(e.name, LOCATE('e', e.name), 4)) WHERE e.name='Seetha'

          Old SQL:
          UPDATE CompUser SET name = (?||SUBSTRING(name, ((((POSITION(? IN name) - 1) + ?) - ?) + 1), (? + (((POSITION(? IN name) - 1) + ?) - ?)) - ((((POSITION(? IN name) - 1) + ?) - ?)))) WHERE userid IN (SELECT DISTINCT t0.userid FROM CompUser t0 WHERE (t0.name = ?)) [params=(String) Ablahum, (String) e, (int) 1, (int) 1, (int) 4, (String) e, (int) 1, (int) 1, (String) e, (int) 1, (int) 1, (String) Seetha]

          New SQL:
          UPDATE CompUser SET name = (?||SUBSTRING(name, (POSITION(? IN name)), ?)) WHERE userid IN (SELECT DISTINCT t0.userid FROM CompUser t0 WHERE (t0.name = ?)) [params=(String) Ablahum, (String) e, (int) 4, (String) Seetha]

          I also changed the following which I assumed were oversights:

          • DB2Dictionary.indexOf: reverted condition for constant checking.
          • JPQLExceptionBuilder: implicit type of locateFromIndex changed to Integer from String.

          There are still some problems with PostgreSQL - espressions like 1+1 as
          SUBSTRING parameter fail. A possible solution would be to use CASTs heavily,
          like we do with DB2.

          Show
          Milosz Tylenda added a comment - Switched expressions from JDO style (0-based index, end index provided to substring) to JPA style (1-based index, length provided to substring). Same change also applied to LOCATE function. JPQL: UPDATE CompUser e SET e.name = CONCAT('Ablahum', SUBSTRING(e.name, LOCATE('e', e.name), 4)) WHERE e.name='Seetha' Old SQL: UPDATE CompUser SET name = (?||SUBSTRING(name, ((((POSITION(? IN name) - 1) + ?) - ?) + 1), (? + (((POSITION(? IN name) - 1) + ?) - ?)) - ((((POSITION(? IN name) - 1) + ?) - ?)))) WHERE userid IN (SELECT DISTINCT t0.userid FROM CompUser t0 WHERE (t0.name = ?)) [params=(String) Ablahum, (String) e, (int) 1, (int) 1, (int) 4, (String) e, (int) 1, (int) 1, (String) e, (int) 1, (int) 1, (String) Seetha] New SQL: UPDATE CompUser SET name = (?||SUBSTRING(name, (POSITION(? IN name)), ?)) WHERE userid IN (SELECT DISTINCT t0.userid FROM CompUser t0 WHERE (t0.name = ?)) [params=(String) Ablahum, (String) e, (int) 4, (String) Seetha] I also changed the following which I assumed were oversights: DB2Dictionary.indexOf: reverted condition for constant checking. JPQLExceptionBuilder: implicit type of locateFromIndex changed to Integer from String. There are still some problems with PostgreSQL - espressions like 1+1 as SUBSTRING parameter fail. A possible solution would be to use CASTs heavily, like we do with DB2.

            People

            • Assignee:
              Milosz Tylenda
              Reporter:
              Patrick Linskey
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development