Details
-
Bug
-
Status: Closed
-
Blocker
-
Resolution: Fixed
-
10.5.3.0
-
None
-
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 -----------------
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 -----------------
-
Repro attached, Workaround attached
-
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
Attachments
Issue Links
- is related to
-
DERBY-4079 Add support for SQL:2008 <result offset clause> and <fetch first clause> to limit result set cardinality
-
- Closed
-