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 Fri, 29 Feb 2008 19:32:51 GMT

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

View raw message