Derby
  1. Derby
  2. DERBY-4562

Complation of prepared statement results in Syntax Error

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker 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-4562c-10_5.diff
        4 kB
        Dag H. Wanvik
      2. derby-4562c.stat
        0.2 kB
        Dag H. Wanvik
      3. derby-4562c.diff
        4 kB
        Dag H. Wanvik
      4. derby-4562b.diff
        3 kB
        Dag H. Wanvik
      5. derby-4562b.stat
        0.2 kB
        Dag H. Wanvik
      6. derby-4562.stat
        0.2 kB
        Dag H. Wanvik
      7. derby-4562.diff
        3 kB
        Dag H. Wanvik

        Issue Links

          Activity

          Hide
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          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
          Dag H. Wanvik added a comment -

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

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

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

          Show
          Dag H. Wanvik added a comment - Attaching derby-4562b which tidies up the code a bit, including Knut's nits.
          Hide
          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
          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
          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
          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
          Dag H. Wanvik added a comment -

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

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

          Thanks Dag! The new test cases look great.

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

          Committed patch derby-4562c as svn 916075.

          Show
          Dag H. Wanvik added a comment - Committed patch derby-4562c as svn 916075.
          Hide
          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
          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
          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
          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:
              Dag H. Wanvik
              Reporter:
              Chuck Wagner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development