You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Thomas Nielsen (JIRA)" <ji...@apache.org> on 2007/09/10 15:35:30 UTC
[jira] Issue Comment Edited: (DERBY-2998) Add support for
ROW_NUMBER() window function
[ 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.