Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1259562450.499241267032148050.JavaMail.jira@brutus.apache.org>
Date: Wed, 24 Feb 2010 17:22:28 +0000 (UTC)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4562) Complation of prepared statement
 results in Syntax Error
In-Reply-To: <1436350829.444351266875311148.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4562?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12837891#action_12837891 ] 

Dag H. Wanvik commented on DERBY-4562:
--------------------------------------

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


> 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.<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.