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