db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Nielsen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2998) Add support for ROW_NUMBER() window function
Date Tue, 11 Sep 2007 07:19:32 GMT

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

Thomas Nielsen commented on DERBY-2998:
---------------------------------------

Bryan,

I agree, lets treat the "AS R ... WHERE" problem in a separate JIRA. There are some similar
constructs that doesn't work either. Looks like it has to do with the WHERE clause working
with the data before projection, and row_number(), for one, is added at projection. I haven't
verified this though.

If you do the nested select, I think you actually need to project the inner select into a
temporary table to enable the outer SELECT ... WHERE to complete without a problem:
ij> select * from (select row_number() as r, a, b from t) as t(r,a,b) where r > 2;
R                   |A          |B          
--------------------------------------------
3                   |3          |7         

This means there is no short-cut in the execution logic to only project the N first/next rows
(as of now).

The exact query you posted, does not actually seem to work though:
ij> select * from (select row_number() as r, * from t) as t(r,*) where r>2;
ERROR 42X01: Syntax error: Encountered "*" at line 1, column 42.

Wildcard column specifier is not allowed in a comma separated list - it probably should be
when used with OLAP functions in this way. Worthy of it's own JIRA possibly?

ij> select * from (select row_number() as r, a, b from t) where r >=2;
ERROR 42X01: Syntax error: Encountered "where" at line 1, column 55.

This looks like a valid query, but I need to check...

> Add support for ROW_NUMBER() window function
> --------------------------------------------
>
>                 Key: DERBY-2998
>                 URL: https://issues.apache.org/jira/browse/DERBY-2998
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Thomas Nielsen
>            Assignee: Thomas Nielsen
>            Priority: Minor
>         Attachments: row_number_prototype.diff, row_number_prototype.stat
>
>
> As part of implementing the overall OLAP Operations features of SQL (DERBY-581), implement
the ROW_NUMBER() window function.
> More information about this feature is available at http://wiki.apache.org/db-derby/OLAPRowNumber

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


Mime
View raw message