[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[
https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-4562:
-
Attachment: derby-4562c-10_5.diff
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.
> 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
>Assignee: Dag H. Wanvik
>Priority: Blocker
> Fix For: 10.6.0.0
>
> Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff,
> derby-4562b.stat, derby-4562c-10_5.diff, derby-4562c.diff, derby-4562c.stat
>
>
> 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.(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.sq
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Issue & fix info: [Repro attached, Workaround attached] (was: [Workaround attached, Repro attached, Patch Available]) Fix Version/s: 10.6.0.0 > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Fix For: 10.6.0.0 > > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat, derby-4562c.diff, derby-4562c.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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) >
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: derby-4562c.stat derby-4562c.diff Uploading rev "c" of this patch which adds more tests, including the one Bryan suggested. > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat, derby-4562c.diff, derby-4562c.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: derby-4562b.diff > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.DatabaseConnect
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: (was: derby-4562b.diff) > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.Data
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: derby-4562b.diff > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.DatabaseConnect
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: (was: derby-4562b.diff) > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.Data
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: derby-4562b.stat derby-4562b.diff Attaching derby-4562b which tidies up the code a bit, including Knut's nits. > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat, derby-4562b.diff, > derby-4562b.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.prepareState
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Issue & fix info: [Patch Available, Repro attached, Workaround attached] (was: [Workaround attached, Repro attached]) > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.pa
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4562: - Attachment: derby-4562.stat derby-4562.diff 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. > 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 >Assignee: Dag H. Wanvik >Priority: Blocker > Attachments: derby-4562.diff, derby-4562.stat > > > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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
[jira] Updated: (DERBY-4562) Complation of prepared statement results in Syntax Error
[ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Knut Anders Hatlen updated DERBY-4562: -- Issue & fix info: [Repro attached, Workaround attached] Bug behavior facts: [Regression] (was: [Regression, Deviation from standard]) 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. > 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.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown > Source) > at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(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.
