Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 20836 invoked from network); 23 Feb 2010 08:59:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 23 Feb 2010 08:59:56 -0000 Received: (qmail 86870 invoked by uid 500); 23 Feb 2010 08:59:56 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 86809 invoked by uid 500); 23 Feb 2010 08:59:56 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 86766 invoked by uid 99); 23 Feb 2010 08:59:56 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 Feb 2010 08:59:56 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.140] (HELO brutus.apache.org) (140.211.11.140) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 23 Feb 2010 08:59:47 +0000 Received: from brutus.apache.org (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id D231C234C052 for ; Tue, 23 Feb 2010 00:59:27 -0800 (PST) Message-ID: <1653330709.457611266915567859.JavaMail.jira@brutus.apache.org> Date: Tue, 23 Feb 2010 08:59:27 +0000 (UTC) From: "Knut Anders Hatlen (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Updated: (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 X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ 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.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.