From derby-dev-return-66587-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Tue Mar 03 03:45:26 2009 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 49211 invoked from network); 3 Mar 2009 03:45:26 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 3 Mar 2009 03:45:26 -0000 Received: (qmail 35445 invoked by uid 500); 3 Mar 2009 03:45:20 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 35425 invoked by uid 500); 3 Mar 2009 03:45:20 -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 35158 invoked by uid 99); 3 Mar 2009 03:45:20 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 02 Mar 2009 19:45:20 -0800 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, 03 Mar 2009 03:45:17 +0000 Received: from brutus (localhost [127.0.0.1]) by brutus.apache.org (Postfix) with ESMTP id 27853234C4AE for ; Mon, 2 Mar 2009 19:44:56 -0800 (PST) Message-ID: <1659120385.1236051896160.JavaMail.jira@brutus> Date: Mon, 2 Mar 2009 19:44:56 -0800 (PST) From: "Dag H. Wanvik (JIRA)" To: derby-dev@db.apache.org Subject: [jira] Updated: (DERBY-4079) Add support for SQL:2008 and to limit result set cardinality In-Reply-To: <336682448.1236050096298.JavaMail.jira@brutus> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org [ https://issues.apache.org/jira/browse/DERBY-4079?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-4079: --------------------------------- Description: SQL 2008 has added new syntax to support a direct way to limit the returned set of rows in a result set. This allows an application to retrieve only some rows of an otherwise larger result set, similar to the popular LIMIT clauses use in some databases. Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER() function in a nested subquery to achieve the effect of the , cf. DERBY-2998, a method which is rather more indirect and still not efficient (DERBY-3505), and primarily intended for OLAP functionality, perhaps. There has been no direct way to achieve the effect of the via SQL. Syntax (cf. SQL 2008, section 7.13): ::= OFFSET {ROW | ROWS} ::= FETCH {FIRST | NEXT} [] {ROW | ROWS} ONLY where is an integer. The two clauses syntactically follow the ORDER BY clause in the grammar. Note that both ORDER BY and the new clauses above are allowed also in subqueries in the new version of the SQL standard (section 7.13). I only propose to include this at the top level in DERBY for now. (ORDER BY is presently also not allowed in subqueries in Derby since SQL didn't allow for this until SQL 2008 either). was: SQL 2008 has added new syntax to support a direct way to limit the returned set of rows in a result set. This allows an application to retrieve only some rows of an otherwise larger result set, similar to the popular LIMIT clauses use in some databases. Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER() function in a nested subquery to achieve the effect of the , cf. DERBY-2998, a method which is rather more indirect and still not efficient (DERBY-3505), and primarily intended for OLAP functionality, perhaps. There has been no direct way to achieve the effect of the via SQL. Syntax (cf. SQL 2008, section 7.13): ::= OFFSET {ROW | ROWS} ::= FETCH {FIRST | NEXT} [] {ROW | ROWS} ONLY where is an integer. It syntactically follows the ORDER BY clause. Note that both ORDER BY and the new clauses above are allowed also in subqueries in the new version of the SQL standard (section 7.13). I only propose to include this at the top level in DERBY for now. (ORDER BY is presently also not allowed in subqueries in Derby since SQL didn't allow for this until SQL 2008 either). > Add support for SQL:2008 and to limit result set cardinality > -------------------------------------------------------------------------------------------------------- > > Key: DERBY-4079 > URL: https://issues.apache.org/jira/browse/DERBY-4079 > Project: Derby > Issue Type: New Feature > Components: SQL > Reporter: Dag H. Wanvik > Assignee: Dag H. Wanvik > Attachments: ref.zip > > > SQL 2008 has added new syntax to support a direct way to limit the > returned set of rows in a result set. This allows an application to > retrieve only some rows of an otherwise larger result set, similar to > the popular LIMIT clauses use in some databases. > Up till now, in Derby (and SQL) we have had to use the ROW_NUMBER() > function in a nested subquery to achieve the effect of the first clause>, cf. DERBY-2998, a method which is rather more indirect > and still not efficient (DERBY-3505), and primarily intended for OLAP > functionality, perhaps. > There has been no direct way to achieve the effect of the offset clause> via SQL. > Syntax (cf. SQL 2008, section 7.13): > ::= OFFSET {ROW | ROWS} > ::= FETCH {FIRST | NEXT} [] {ROW | ROWS} ONLY > where is an integer. The two clauses syntactically follow the ORDER BY > clause in the grammar. > Note that both ORDER BY and the new clauses above are allowed also in > subqueries in the new version of the SQL standard (section 7.13). I > only propose to include this at the top level in DERBY for now. (ORDER > BY is presently also not allowed in subqueries in Derby since SQL > didn't allow for this until SQL 2008 either). -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.