Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4562

Complation of prepared statement results in Syntax Error

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 10.5.3.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: JDBC, SQL
    • Labels:
      None
    • Environment:
    • Issue & fix info:
      Repro attached, Workaround attached
    • Bug behavior facts:
      Regression

      Description

      In attempting to upgrade our product from Derby 10.3.1.4 to 10.5.3.0, I simply replaced the derby jars - no change to java environment or code. I now experience a Syntax Error when preparing a statement that previously worked correctly.

      This statement:
      SELECT Offset, CMFragmentOID, CMContentOID, FragmentLength, Fragment
      FROM CMFragment
      WHERE CMContentOID = ? AND Offset BETWEEN
      (SELECT Offset FROM CMFragment WHERE CMContentOID = ? AND Offset <= ? AND Offset + FragmentLength > ?)
      AND (SELECT Offset FROM CMFragment WHERE CMContentOID = ? AND Offset < ? AND Offset + FragmentLength >= ?)
      ORDER BY Offset ASC

      Which should evaluate to the following after the parameters are provided:
      SELECT Offset, CMFragmentOID, CMContentOID, FragmentLength, Fragment
      FROM CMFragment
      WHERE CMContentOID = -6915303484809802281 AND Offset BETWEEN
      (SELECT Offset FROM CMFragment WHERE CMContentOID = -6915303484809802281 AND Offset <= 0 AND Offset + FragmentLength > 0)
      AND (SELECT Offset FROM CMFragment WHERE CMContentOID = -6915303484809802281 AND Offset < 131072 AND Offset + FragmentLength >= 131072)
      ORDER BY Offset ASC

      Fails with:

      Caused by: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "Offset" at line 1, column 202.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
      at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
      at com.paisley.foundation.sql.decorator.BaseConnectionDecorator.prepareStatement(BaseConnectionDecorator.java:349)
      at com.paisley.foundation.sql.decorator.batchvalidator.BatchValidatorConnection.prepareStatement(BatchValidatorConnection.java:152)
      at com.paisley.foundation.sql.decorator.BaseConnectionDecorator.prepareStatement(BaseConnectionDecorator.java:349)
      at com.paisley.foundation.database.connection.DatabaseConnection.prepareStatement(DatabaseConnection.java:960)
      at com.paisley.foundation.persistent.sql.PersistentQuery.execute(PersistentQuery.java:236)
      at com.paisley.rnj.content.model.CMFragment.load(CMFragment.java:254)
      ... 25 more
      Caused by: java.sql.SQLException: Syntax error: Encountered "Offset" at line 1, column 202.
      at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
      at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
      ... 44 more
      Caused by: ERROR 42X01: Syntax error: Encountered "Offset" at line 1, column 202.
      at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
      at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
      at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
      at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
      ... 38 more

      The failure position indicated is in this clause: AND Offset + FragmentLength > ?

      This statement does work correctly in 10.3.1.4. It also works as a direct SQL statement, if I don't use a prepared statement, but execute the SQL above.

        Attachments

        1. derby-4562.diff
          3 kB
          Dag H. Wanvik
        2. derby-4562.stat
          0.2 kB
          Dag H. Wanvik
        3. derby-4562b.diff
          3 kB
          Dag H. Wanvik
        4. derby-4562b.stat
          0.2 kB
          Dag H. Wanvik
        5. derby-4562c.diff
          4 kB
          Dag H. Wanvik
        6. derby-4562c.stat
          0.2 kB
          Dag H. Wanvik
        7. derby-4562c-10_5.diff
          4 kB
          Dag H. Wanvik

          Issue Links

            Activity

              People

              • Assignee:
                dagw Dag H. Wanvik
                Reporter:
                cwagner Chuck Wagner
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: