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