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] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
Date Mon, 10 Sep 2007 13:35:30 GMT

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

thomanie edited comment on DERBY-2998 at 9/10/07 6:35 AM:
----------------------------------------------------------------

I have attached my prototype for the row_number() implementation. It's not intended for commit.

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.

There is one issue I shortcut - the setup of the rnCache array in BaseActivation. To get it
working I just created a 10 element array to hold the row_number() values from invocation
to invocation. This should be either number of actual row_number() columns, or total number
of columns in the resultset we are building.

Your comments are greatly appreciated :)

      was (Author: thomanie):
    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