[
https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12566164#action_12566164
]
Thomas Nielsen commented on DERBY-2998:
---------------------------------------
Thanks for pitching in Army.
Ad 1)
Good question. This probably needs a little more explaining :)
The MaterializedResultSet does not materialize the complete lower result before
pulling rows from it to the upper result. A row is generated, stored (i.e
materialized), and then passed up the chain. Then the next row is materialized,
and so on. This means you have only materialized those rows you have actually
visited, so the optimization we seek will still happen. The query, and with
that the row materialization, is stopped once the condition is met. The more
involved your subquery is, the better effect materialization (or caching) will
give.
With that said I don't really see the need for materializing subqueries for
ROW_NUMBER(). I have turned off materialization in the latest patches. But for
other functions it may be valid, so materialization or not should probably
become dependant on what function is evaluated.
Ad 2)
I did have a look at the GroupedAggregateResultSet at a very early stage, but I
should probably revisit that now.
Ad 3)
Yes, that is basically how the trees will look like.
It might be worth looking into generating all window functions belonging to a
given RCL in the same WindowNode and/or WindowResultSet, but I won't dive into
that now. Doing that is still only valid if the functions operate on the exact
same window definition - however (un)likely that is, and adding handling of
multiple window definitions to WindowNode and/or WindowResultSet will probably
introduce some unnecessary complexity. The extra cost of passing the row
through an additional ResultSet in the chain is pretty small, so I'm not yet
convinced it's worth doing.
> 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-10.diff, d2998-10.stat, d2998-11.diff,
> 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-9-derby.log, d2998-9.diff, d2998-9.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.
