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.

      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

          Hide
          bryanpendleton Bryan Pendleton added a comment -

          I think that "offset" may now be a reserved word due to the new features added by DERBY-4079.

          Can you try putting the references to the column named "offset" in double quotes, for example::

          order by "OFFSET" asc

          And let us know if that works around the problem?

          Show
          bryanpendleton Bryan Pendleton added a comment - I think that "offset" may now be a reserved word due to the new features added by DERBY-4079 . Can you try putting the references to the column named "offset" in double quotes, for example:: order by "OFFSET" asc And let us know if that works around the problem?
          Hide
          knutanders Knut Anders Hatlen added a comment -

          There were some concerns in DERBY-4079 about the compatibility implications of making OFFSET a reserved keyword, so I think it ended up not being reserved. See for instance the test case OffsetFetchNextTest.testNewKeywordNonReserved() that attempts to verify that we don't require quoting when using OFFSET as an identifier. I think Bryan is right, though, that DERBY-4079 is causing this and that using double quotes is likely to work around the problem.

          Show
          knutanders Knut Anders Hatlen added a comment - There were some concerns in DERBY-4079 about the compatibility implications of making OFFSET a reserved keyword, so I think it ended up not being reserved. See for instance the test case OffsetFetchNextTest.testNewKeywordNonReserved() that attempts to verify that we don't require quoting when using OFFSET as an identifier. I think Bryan is right, though, that DERBY-4079 is causing this and that using double quotes is likely to work around the problem.
          Hide
          cwagner Chuck Wagner added a comment -

          I'll test with the double quotes, but as I mentioned above, if I populate the statement with the parameters and just execute the whole thing (not as a prepared statement) the SQL executes successfully.

          Unfortunately, this SQL is also executed against DB2 and Oracle, so I would also need to verify that the quoted string would work as expected in those cases too.

          Show
          cwagner Chuck Wagner added a comment - I'll test with the double quotes, but as I mentioned above, if I populate the statement with the parameters and just execute the whole thing (not as a prepared statement) the SQL executes successfully. Unfortunately, this SQL is also executed against DB2 and Oracle, so I would also need to verify that the quoted string would work as expected in those cases too.
          Hide
          cwagner Chuck Wagner added a comment -

          Using double quotes around the OFFSET column name is a valid workaround, see the log statements below. Note that not all my use of the word offset was quoted - the select column names are generated automatically, but it still worked.

          2010-02-22 22:41:09.894 GMT Thread[http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), Begin compiling prepared 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 :End prepared statement

          2010-02-22 22:41:09.925 GMT Thread[http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), End compiling prepared 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 :End prepared statement

          2010-02-22 22:41:09.928 GMT Thread[http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), Executing prepared 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 :End prepared statement with 7 parameters begin parameter #1: -6915303484809802281 :end parameter begin parameter #2: -6915303484809802281 :end parameter begin parameter #3: 0 :end parameter begin parameter #4: 0 :end parameter begin parameter #5: -6915303484809802281 :end parameter begin parameter #6: 131072 :end parameter begin parameter #7: 131072 :end parameter

          Show
          cwagner Chuck Wagner added a comment - Using double quotes around the OFFSET column name is a valid workaround, see the log statements below. Note that not all my use of the word offset was quoted - the select column names are generated automatically, but it still worked. 2010-02-22 22:41:09.894 GMT Thread [http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), Begin compiling prepared 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 :End prepared statement 2010-02-22 22:41:09.925 GMT Thread [http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), End compiling prepared 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 :End prepared statement 2010-02-22 22:41:09.928 GMT Thread [http-127.0.0.1-6422-Processor3,5,main] (XID = 158770), (SESSIONID = 471), (DATABASE = C:/workspaces/g400/OfflineDeploy/data), (DRDAID = null), Executing prepared 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 :End prepared statement with 7 parameters begin parameter #1: -6915303484809802281 :end parameter begin parameter #2: -6915303484809802281 :end parameter begin parameter #3: 0 :end parameter begin parameter #4: 0 :end parameter begin parameter #5: -6915303484809802281 :end parameter begin parameter #6: 131072 :end parameter begin parameter #7: 131072 :end parameter
          Hide
          bryanpendleton Bryan Pendleton added a comment -

          Thanks Chuck for helping us track this down.

          If you can possibly boil this down into a standalone Java program which reproduces the
          problem on a clean 10.5 installation, that would be very useful.

          In the meantime, it's great to know you have a workaround.

          Show
          bryanpendleton Bryan Pendleton added a comment - Thanks Chuck for helping us track this down. If you can possibly boil this down into a standalone Java program which reproduces the problem on a clean 10.5 installation, that would be very useful. In the meantime, it's great to know you have a workaround.
          Hide
          knutanders Knut Anders Hatlen added a comment -

          I'm able to reproduce this in ij:

          ij> create table t(offset int);
          0 rows inserted/updated/deleted
          ij> prepare ps as 'select * from t where offset = 0';
          ij> prepare ps as 'select * from t where 1 + offset = 0';
          ij> prepare ps as 'select * from t where offset + 1 = 0';
          ERROR 42X01: Syntax error: Encountered "offset" at line 1, column 23.

          Show
          knutanders Knut Anders Hatlen added a comment - I'm able to reproduce this in ij: ij> create table t(offset int); 0 rows inserted/updated/deleted ij> prepare ps as 'select * from t where offset = 0'; ij> prepare ps as 'select * from t where 1 + offset = 0'; ij> prepare ps as 'select * from t where offset + 1 = 0'; ERROR 42X01: Syntax error: Encountered "offset" at line 1, column 23.
          Hide
          knutanders Knut Anders Hatlen added a comment -

          Setting "Repro attached" and "Workaround attached". Removing the "Deviation from standard" flag, since I don't think giving a syntax error when a reserved keyword is used as an identifier, is a violation of the standard. For example, PostgreSQL gives a syntax error for all the statements in the comment above (the CREATE TABLE statement and all three SELECT statements). I think we should be consistent and either reject all or (preferably) accept all.

          Show
          knutanders Knut Anders Hatlen added a comment - Setting "Repro attached" and "Workaround attached". Removing the "Deviation from standard" flag, since I don't think giving a syntax error when a reserved keyword is used as an identifier, is a violation of the standard. For example, PostgreSQL gives a syntax error for all the statements in the comment above (the CREATE TABLE statement and all three SELECT statements). I think we should be consistent and either reject all or (preferably) accept all.
          Hide
          cwagner Chuck Wagner added a comment -

          Can someone point me to the SQL 2008 standard which describes this use of OFFSET? We have a pretty exhaustive list of reserved words - our code tries to prevent this type of thing because we support so many databases (Oracle / DB2 / Derby / MS Sql Server).

          Show
          cwagner Chuck Wagner added a comment - Can someone point me to the SQL 2008 standard which describes this use of OFFSET? We have a pretty exhaustive list of reserved words - our code tries to prevent this type of thing because we support so many databases (Oracle / DB2 / Derby / MS Sql Server).
          Hide
          dagw Dag H. Wanvik added a comment -

          Vol 2, section 5.2 "<token> and <separator>" has the production <reserved word> which contains "OFFSET".

          Show
          dagw Dag H. Wanvik added a comment - Vol 2, section 5.2 "<token> and <separator>" has the production <reserved word> which contains "OFFSET".
          Hide
          dagw Dag H. Wanvik added a comment -

          Uploading a patch which improves the parser look-ahead to determine if an occurence of OFFSET is an identifier or the start of an offsetClause.
          Added a test case similar to the repro, running regressions.

          Show
          dagw Dag H. Wanvik added a comment - Uploading a patch which improves the parser look-ahead to determine if an occurence of OFFSET is an identifier or the start of an offsetClause. Added a test case similar to the repro, running regressions.
          Hide
          knutanders Knut Anders Hatlen added a comment -

          The patch looks correct to me. One small nit: The two checks for tokKind == EOF seem to be unnecessary because we will return false for EOF even without them. Perhaps also the two else if branches could be collapsed.

          Show
          knutanders Knut Anders Hatlen added a comment - The patch looks correct to me. One small nit: The two checks for tokKind == EOF seem to be unnecessary because we will return false for EOF even without them. Perhaps also the two else if branches could be collapsed.
          Hide
          dagw Dag H. Wanvik added a comment -

          Thanks for looking at this Knut! I agree about the redundant code; some re-factoring led to them I'll clean that up and commit a new version as soon as I have rerun the regressions. I'll back-port this fix o 10.5 as well.

          Show
          dagw Dag H. Wanvik added a comment - Thanks for looking at this Knut! I agree about the redundant code; some re-factoring led to them I'll clean that up and commit a new version as soon as I have rerun the regressions. I'll back-port this fix o 10.5 as well.
          Hide
          dagw Dag H. Wanvik added a comment -

          Attaching derby-4562b which tidies up the code a bit, including Knut's nits.

          Show
          dagw Dag H. Wanvik added a comment - Attaching derby-4562b which tidies up the code a bit, including Knut's nits.
          Hide
          bryanpendleton Bryan Pendleton added a comment -

          Hi Dag, thanks for working on this. The second patch definitely seems cleaner.

          I wonder: is the code intended to handle something like:

          select * from t offset +6 rows

          I see that we have a test case for "offset -1 rows" in OffsetFetchNextTest,
          but I didn't immediately see a test case for an explicit plus sign attached to
          a positive row offset.

          The rest of the patch looks great to me. +1 to commit.

          Show
          bryanpendleton Bryan Pendleton added a comment - Hi Dag, thanks for working on this. The second patch definitely seems cleaner. I wonder: is the code intended to handle something like: select * from t offset +6 rows I see that we have a test case for "offset -1 rows" in OffsetFetchNextTest, but I didn't immediately see a test case for an explicit plus sign attached to a positive row offset. The rest of the patch looks great to me. +1 to commit.
          Hide
          dagw Dag H. Wanvik added a comment -

          Thanks for looking at this, Bryan. I'll add that test case, too for good measure! It should work, yes.

          Show
          dagw Dag H. Wanvik added a comment - Thanks for looking at this, Bryan. I'll add that test case, too for good measure! It should work, yes.
          Hide
          dagw Dag H. Wanvik added a comment -

          Uploading rev "c" of this patch which adds more tests, including the one Bryan suggested.

          Show
          dagw Dag H. Wanvik added a comment - Uploading rev "c" of this patch which adds more tests, including the one Bryan suggested.
          Hide
          bryanpendleton Bryan Pendleton added a comment -

          Thanks Dag! The new test cases look great.

          Show
          bryanpendleton Bryan Pendleton added a comment - Thanks Dag! The new test cases look great.
          Hide
          dagw Dag H. Wanvik added a comment -

          Committed patch derby-4562c as svn 916075.

          Show
          dagw Dag H. Wanvik added a comment - Committed patch derby-4562c as svn 916075.
          Hide
          dagw Dag H. Wanvik added a comment -

          Uploading a slightly tweaked version, derby-4562c-10_5.diff, for the 10.5 branch. I had to remove the parts concerning dynamic parameter ("?"), which wasn't introduced for the offset clause till 10.6.

          Show
          dagw Dag H. Wanvik added a comment - Uploading a slightly tweaked version, derby-4562c-10_5.diff, for the 10.5 branch. I had to remove the parts concerning dynamic parameter ("?"), which wasn't introduced for the offset clause till 10.6.
          Hide
          dagw Dag H. Wanvik added a comment -

          Committed on 10.5 as svn 918241, resolving. Chuck, please close this issue if you are happy with the resolution.

          Show
          dagw Dag H. Wanvik added a comment - Committed on 10.5 as svn 918241, resolving. Chuck, please close this issue if you are happy with the resolution.

            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:

                Development