[
https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12836980#action_12836980
]
Chuck Wagner commented on DERBY-4562:
-------------------------------------
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
> Complation of prepared statement results in Syntax Error
> --------------------------------------------------------
>
> Key: DERBY-4562
> URL: https://issues.apache.org/jira/browse/DERBY-4562
> Project: Derby
> Issue Type: Bug
> Components: JDBC, SQL
> Affects Versions: 10.5.3.0
> Environment: Windows 7, output from SysInfo:
> ------------------ Java Information ------------------
> Java Version: 1.6.0_12
> Java Vendor: Sun Microsystems Inc.
> Java home: C:\workspaces\g400\OfflineDeploy\java\jre
> Java classpath:
> ..\..\Tomcat;..\..\java\lib\tools.jar;..\..\java\jre\lib\rt.jar;..\..\Tomcat\lib\offlineclasspath.jar;..\..\Lib\WinFoldersJava.jar;..\..\Tomcat\lib\derby.jar;..\..\Tomcat\lib\derbynet.jar;..\..\Tomcat\lib\derbytools.jar
> OS name: Windows Vista
> OS architecture: x86
> OS version: 6.1
> Java user name: Chuck.Wagner
> Java user home: C:\Users\chuck.wagner
> Java user dir: C:\workspaces\g400\OfflineDeploy\Tomcat\lib
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.6
> --------- Derby Information --------
> JRE - JDBC: Java SE 6 - JDBC 4.0
> [C:\workspaces\g400\OfflineDeploy\Tomcat\lib\derby.jar] 10.5.3.0 - (802917)
> [C:\workspaces\g400\OfflineDeploy\Tomcat\lib\derbytools.jar] 10.5.3.0 -
> (802917)
> [C:\workspaces\g400\OfflineDeploy\Tomcat\lib\derbynet.jar] 10.5.3.0 - (802917)
> ------------------------------------------------------
> ----------------- Locale Information -----------------
> Reporter: Chuck Wagner
> Priority: Blocker
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
