[
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.
