Return-Path: 
 <derby-dev-return-72572-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 90060 invoked from network); 28 Aug 2009 03:41:33 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3)
  by minotaur.apache.org with SMTP; 28 Aug 2009 03:41:33 -0000
Received: (qmail 16533 invoked by uid 500); 28 Aug 2009 03:41:33 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 16419 invoked by uid 500); 28 Aug 2009 03:41:33 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 16411 invoked by uid 99); 28 Aug 2009 03:41:33 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 28 Aug 2009 03:41:33 +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; Fri, 28 Aug 2009 03:41:20 +0000
Received: from brutus (localhost [127.0.0.1])
	by brutus.apache.org (Postfix) with ESMTP id 53F53234C044
	for <derby-dev@db.apache.org>; Thu, 27 Aug 2009 20:40:59 -0700 (PDT)
Message-ID: <547715109.1251430859339.JavaMail.jira@brutus>
Date: Thu, 27 Aug 2009 20:40:59 -0700 (PDT)
From: "Dag H. Wanvik (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3634) Cannot use row_number() in ORDER BY
 clause
In-Reply-To: <1747973222.1208544261642.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394
X-Virus-Checked: Checked by ClamAV on apache.org


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

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

Thanks for looking at this, Bryan!

Yes, that was indeed my intention for the "window function node" classes.

Regarding the ordering of patches, what you propose makes sense, I
guess I hesitated to do that at first since I wasn't sure how long it
would take to replace the old code, and it's tedious to maintain
sequences of dependent patches. But the removal patch would be quite stable, 
so I'll get on to that.


> Cannot use row_number() in ORDER BY clause
> ------------------------------------------
>
>                 Key: DERBY-3634
>                 URL: https://issues.apache.org/jira/browse/DERBY-3634
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Rick Hillegas
>            Assignee: Dag H. Wanvik
>         Attachments: derby-3634-a.diff, derby-3634-a.stat, derby-3634-a.txt, derby-3634-b.diff, derby-3634-b.stat
>
>
> The following query works correctly:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by abs(a)
> I expected the following query to also work, but it raised an exception:
> select abs(a), row_number() over ()
> from t
> where a > 100 and a < 111
> order by row_number() over ()
> This is the error I saw: "ERROR 42X01: Syntax error: Encountered "over" at line 5, column 23".
> Here are the reasons why I think that this syntax is supposed to be supported:
> According to my reading of the 2003 SQL spec, the ORDER BY clause should be able to sort on any expression in the SELECT list. That includes OLAP expressions. I believe this is so because, according to part 2, section 10.10 (<sort specification>), a <sort key> can be any <value expression> and if you follow the grammar for <value expression>, it can resolve to be a <value expression primary> (see section 6.3), which can in turn resolve to be a <window function>. This reasoning is supported by tracing the hotlinks on the following page which lays out the SQL 2003 BNF: http://savage.net.au/SQL/sql-2003-2.bnf.html This interpretation is further supported by the example of an ORDER BY clause referencing an OLAP expression which is provided on page 23 of the introduction to OLAP written by Fred Zemke, Krishna Kulkarni, Andy Witkowski, and Bob Lyle: www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/OLAP-99-154r2.pdf

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.