Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 11 Nov 2011 13:36:52 +0000 (UTC)
From: "Rick Hillegas (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <1848018304.21080.1321018612482.JavaMail.tomcat@hel.zones.apache.org>
In-Reply-To: 
 <1975346544.41924.1320085532606.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-5488) Add remaining JDBC 4.1 bits which
 did not appear in the Java 7 javadoc.
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Rick Hillegas commented on DERBY-5488:
--------------------------------------

Hi Bryan,

Sorry to not be clear. The short answer to your question is that what I am describing is behavior internal to Derby. The user can use either the JDBC escape syntax or the SQL Standard syntax. Internally, Derby keeps track of which syntax the user chose. Hopefully, the following will be more helpful:

Normally, it's pretty easy for Derby to internally transform the JDBC limit/offset syntax into the corresponding SQL Standard syntax. For instance, the following statement which uses JDBC escape syntax...

 select * from T order by A { limit 20 offset 10 }

...is treated by Derby as equivalent to the following SQL Standard syntax:

 select * from T order by A offset 10 rows fetch next 20 rows only

However, the following statement...

 select * from T order by A { limit 0 offset 10 }

...is not equivalent to...

 select * from T order by A offset 10 rows fetch next 0 rows only

...because "fetch next 0 rows" raises an exception. In this case, Derby just ignores the last clause, treating the original statement like...

 select * from T order by A offset 10 rows

That's all well and good. The tricky part comes when ? parameters pop up.

 select * from T order by A { limit ? offset 10 }

...is treated like:

 select * from T order by A offset 10 rows fetch next ? rows only

At run-time, Derby has to know that setting ? equal to 0 is OK if the original statement was the one with JDBC escape syntax, but not OK if the original statement was the one with SQL Standard syntax.

To make it possible for Derby to make this distinction, I had to pass a boolean all the way from the parser to the run-time logic. The boolean indicates whether the original statement used JDBC escape syntax or SQL Standard syntax. That's what forced me to touch so many files along the way.

I hope I have not made this more confusing. If this is still puzzling, please let me know.

Thanks,
-Rick


> Add remaining JDBC 4.1 bits which did not appear in the Java 7 javadoc.
> -----------------------------------------------------------------------
>
>                 Key: DERBY-5488
>                 URL: https://issues.apache.org/jira/browse/DERBY-5488
>             Project: Derby
>          Issue Type: Improvement
>          Components: JDBC, SQL
>    Affects Versions: 10.9.0.0
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: JDBC_4.1_Supplement.html, derby-5488-01-aa-objectMappingAndConversion.diff, derby-5488-02-aa-fixBigInteger.diff, derby-5488-03-ac-moveDecimalSetterGetterAndTest.diff, derby-5488-04-aa-fixBigIntegerDecimal.diff, derby-5488-05-ad-limitOffset.diff, z.java
>
>
> In addition to the JDBC 4.1 bits which were visible in the Java 7 javadoc, a couple other items appear in the JDBC 4.1 Maintenance Review spec. This spec has been published on the JCP website at http://download.oracle.com/otndocs/jcp/jdbc-4_1-mrel-eval-spec/index.html. I will attach a functional spec for the remaining bits.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira