db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2998) Add support for ROW_NUMBER() window function
Date Mon, 03 Mar 2008 23:56:50 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574793#action_12574793

A B commented on DERBY-2998:

I applied "d2998-followup-distinct.diff" and verified that both the DISTINCT issue and the
ArrayIndexOutOfBounds issue are resolved.

That said, in re-reading the comments in ProjectRestrictNode I noticed this:

     * We have a window function column in the RCL of our child
     * PRN, and need to regenerate the VCNs.

Out of curiosity I made a quick change so that, instead of checking for instances of WindowNode
and DIstinctNode, the code just checked the condition described in the comment, i.e. "do we
have a window function column in the RCL of our child PRN?".  So I replaced:

        boolean regenVCN = false;
        ProjectRestrictNode prn = (ProjectRestrictNode)childResult;
        if (prn.childResult instanceof WindowNode){
            regenVCN = true;
        if (prn.childResult instanceof DistinctNode){
            DistinctNode dn = (DistinctNode)prn.childResult;
            if (dn.childResult instanceof WindowNode){
                regenVCN = true;
        if (regenVCN){


        ProjectRestrictNode prn = (ProjectRestrictNode)childResult;
        if (prn.childResult.getResultColumns()

>From what I can tell this change still makes all of the queries pass (including OLAPTest).
 Would this be a viable alternative, or would such an approach be too broad?  I admit I didn't
do any tracing with this particular change, I just ran the tests and noted that they appear
to run to correctly.  The code seems cleaner and matches the comment, so I thought I'd throw
it out there...

On a completely unrelated note, I accidentally discovered that queries of the form:

   select * from (select row_number() over() as r, ... from t) x where r < ...

can actually return different results depending on the presence of indexes.  This is because
the rows returned from the subquery have no guaranteed ordering (Derby doesn't allow ORDER
BY in subqueries), and thus any predicate which restricts based on row_number() will restrict
the rows based on an undefined order.  Since the order of the rows from the subquery may depend
on the presence of indexes, the set of rows which survives a row_order()-based restriction
may depend on the indexes, as well.  In the end I do _not_ think this is a bug--but it does
strike me as a probable point of confusion for users.  It seems that anyone who wants "the
first x rows only" has to either accept the fact that "first" does not imply "ordered" (and
thus results can vary depending on what conglomerate the optimizer chooses), or else s/he
has to use optimizer ovverides to force the optimizer to use an index which is ordered on
the desired columns.  Is that an accurate assessment? I'm not saying anything needs to be
done to address this, I'm just curious as to whether or not I've understood this correctly.

But all of that aside, thanks for resolving the DISTINCT issue, Thomas! :)

> 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-12.diff, d2998-12.stat,
d2998-13.diff, d2998-13.stat, d2998-14.diff, d2998-14.stat, d2998-15.diff, d2998-15.stat,
d2998-16.diff, d2998-16.stat, d2998-17.diff, d2998-17.stat, d2998-18.diff, d2998-18.stat,
d2998-19.diff, d2998-19.stat, 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-doc-2.diff, d2998-doc-2.stat, d2998-followup-2.diff,
d2998-followup-2.stat, d2998-followup-distinct.diff, d2998-followup-distinct.stat, d2998-followup-issue1.diff,
d2998-followup-issue1.stat, d2998-followup-issue4.diff, d2998-followup-issue4.stat, d2998-followup-testsuite.diff,
d2998-followup-testsuite.stat, d2998-test.diff, d2998-test.stat, d2998-test2.diff, d2998-test2.stat,
d2998-test3.diff, d2998-test3.stat, d2998-test4.diff, d2998-test4.stat, d2998-test6.diff,
d2998-test7.diff, d2998-test8.diff, d2998-test9.diff
> 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