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 Mon, 10 Sep 2007 09:13:29 GMT

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

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

I have attached my prototype for the row_number() implementation.

With a simple testtable t you can now do:
---
ij> select row_number(),a,b from t;
row_number()        |A          |B          
--------------------------------------------
1                   |1          |9          
2                   |2          |8          
3                   |3          |7     

AS clause is also functioning for this query.

However,
---
ij> select row_number() as r from t where r >= 2;
ERROR 42X04: Column 'R' is either not in any table in the FROM list or appears within a join
specification and is outside the scope of the join specification or appears in a HAVING clause
and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'R' is
not a column in the target table.
---
This seems to be due to a limitation in derby. I get the same error with 'select a+b as r
...'. This can be rewritten using a nested select statement
---
ij> select * from (select row_number() as r from t) as t(r) where r >= 2;
R                   
--------------------
2                   
3         
---

The patch works along these lines:
I added a new class RowNumberColumnNode that is added to the querytree at compiletime. On
invokation of ResultSetList.generateCore() we generate code to call new method BaseActivation.getSetRowNumber()
that does the actual incrementing during execution.
Diff and .stat files are attached.

> 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