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 17:30:50 GMT

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

A B commented on DERBY-2998:

Thank you for all of the follow-up patches, Thomas.  (And thank for the commits, Dyre!)

Regarding d2998-followup-2.diff: with this change the method "pointsToWindowFunction()" in
ColumnReference.java is no longer called.  Would it make sense to remove the method altogether?

Regarding d2998-followup-queryplanAOOB.diff: With this patch applied the query plan now shows:

 select count(*) from
  (select i, row_number() over () from t1
    union all
   select distinct row_number() over(), row_number() over() from t1) x(a, b); 

        Distinct Scan ResultSet for T1 at read committed isolation level using instantaneous
share row locking:
        Number of opens = 1
        Hash table size = 1
        Distinct column is column number -1
        Rows seen = 1

>From reading the plan it wasn't immediately clear to me what "Distinct column is column
number -1" meant.  Esp. what does a column number of "-1" mean?  I then ran a bunch of simple
SELECT DISTINCT ... from T1 queries and determined that the distinct column number which is
printed as part of the query plan is meant to be the 0-based position of the column as it
appears in the conglomerate being scanned.  Since ROW_NUMBER() is not a column in the conglomerate,
I guess "-1" seems like an okay value.

Having said that, though, I think it's interesting that if we replace ROW_NUMBER() with any
other expression--such as "i+j" or a literal--a Distinct scan on the conglomerate doesn't
happen: instead, we do a normal scan of the conglomerate and then a distinct sort on the results.
 I was expecting ROW_NUMBER() to behave the same, but apparently it doesn't.  Is this because
ROW_NUMBER() is (incorrectly) applied *after* the DISTINCT, instead of before, per earlier
discussion?  And if so, do you happen to know if this change to RealDistinctScanStatistics
will still be needed after the DISTINCT issue is resolved?

> 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-issue1.diff, d2998-followup-issue1.stat, d2998-followup-issue4.diff,
d2998-followup-issue4.stat, d2998-followup-queryplanAOOB.diff, d2998-followup-queryplanAOOB.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