[
https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Nielsen updated DERBY-2998:
----------------------------------
Attachment: d2998-8.diff
d2998-8.stat
I have reworked my patch.
Attaching 'd2998-8.diff' and 'd2998-8.stat'.
During parsing (sqlgrammar.jj) we introduce the new RowNumberColumnNode and a
WindowNode holding the specification of the window in the AST. The WindowNode
does not do much as of today, but is there for future window functions and/or
window specification to use.
Once the best plan has been found during optimization, the last step in the
current trunk code is to swap SelectNodes with ProjectRestrictNodes, and top
the PRNs off with GroupByNode, DistinctNode, and a OrderByNode if required.
At the generate PRN stage, and with the current 'd2998-8' patch, we pull the
WindowNode up from under the window function node, and place it on top of the
existing (sub)tree, ending up with the following tree for code generation:
PRN
|
WindowNode
|
OrderByNode
|
DistictNode
|
GroupByNode
|
<PRN on top of anything below us, restricted on any where, and projected>
This ensures the WindowNodeResultSet is generated to pull rows and evaluate the
window function over the correct lower restricted, projected, grouped and
ordered ResultSets.
The attached patch 'd2998-8.diff' work for selects and nested selects, and
Knuts join posted further up.
The attached patch does not, however, work with group by, ditinct and/or order
by.
The reason is that the ResultColumn object for the window function column is
added at execution time by WindowResultSet. The PRN topping off the SelectNode
from list (typically a PRN on top of a FromBaseTable for a simple select)
currently has a 'null' (no object) for the window function ResultColumn. This
in turn cause the sorters to object and thorw exceptions, as they find a null
ResultColumn.
Any suggestions as to how to solve this are very welcome!
At the moment I see three solutions:
- introduce an non-null but "empty" or "phantom" ResultColumn object in the
bottom PRN, and replace it with the correct value once we get to the
WindowResultSet?
- adjust the bottom PRN, and the orderby, groupby and having ResultColumns to
only have the ResultColumns of the underlying ResultSet? Today they contain a
reference to, and thereby sort on, all ResultColumns, including any window
function ResultColumns.
- adjust the sorters to allow null values?
Changing the sorters sounds dangerous . I don't feel truly comfortable with
introducing phantom values either, even for a very short while. So maybe the
second option is the best?
> 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: d2998-4.diff, d2998-4.stat, d2998-5.diff, d2998-5.stat,
> d2998-6.diff, d2998-6.stat, d2998-7.diff, d2998-7.stat, d2998-8.diff,
> d2998-8.stat, d2998-doc-1.diff, d2998-doc-1.stat, d2998-test.diff,
> d2998-test.stat, d2998-test2.diff, d2998-test2.stat, d2998-test3.diff,
> d2998-test3.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.
