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

with

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

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