[
https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12573916#action_12573916
]
Thomas Nielsen commented on DERBY-2998:
---------------------------------------
Thanks for your continued help Army. I can indeed add some smaller additional
smaller patches to fix these issues if patch 19 goes in the trunk.
Ad 1) Agree. This should follow the existing pattern.
Ad 2) ...obviously not needed anymore.
Ad 3)
In SQL2003 DISTINCT handling is defined in 10.9 <aggregate function>. Its
section "General Rules", clause 4), b), i) refers on to subclause 8.2
<comparison predicate> for how to eliminate duplicates. 8.2 does not specify
how to handle window functions explicitly, only general number/value handling.
Since 10.9s "General Rules" 4) a) specifies that expressions should be
evaluated for every row before DISTINCT, I agree that the current patch is not
according to the spec in this respect. The DB2 behaviour you see is conformant.
We should return 5, not 1, rows as you say.
I haven't given this a very hard look, but it seems this will introduce some
additional branching in SelectNode.genProjectRestrict(). As of now, DISTINCT is
evaluated before ordering and grouping. To be conformant for window functions,
it looks like we have to delay the DISTINCT evaluation, or even pull the
DISTINCT out of the current PRN, and evaluate it in an additional PRN on top of
the current one. Probably is sufficient to mark as !distinctScanPossible and
delay insertion of the DISTINCT_NODE if there's a window function in the RCL.
Ad 4)
My imagination... *ugh* :) I'll add the one you listed at least, and see if I
can find some extra ones too.
Ad 5)
No, window functions are not allowed in the ORDER BY clause as of now. Agree -
it's a good idea to add this to the docs.
> 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-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.
