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] Commented: (DERBY-2998) Add support for ROW_NUMBER() window function
Date Wed, 06 Feb 2008 15:51:08 GMT

    [ 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

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

View raw message