[jira] [Updated] (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-3505:
-----------------------------------

    Urgency: Normal
     Labels: derby_triage10_10  (was: )
    
> Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3505
>                 URL: https://issues.apache.org/jira/browse/DERBY-3505
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.4.1.3
>            Reporter: Thomas Nielsen
>              Labels: derby_triage10_10
>
> Using ROW_NUMBER() to limit the number of rows returned is typically done with the following query:
>     SELECT * FROM (
>       SELECT row_number() over () as r, t.* FROM T
>     ) AS tmp WHERE r <= 3;
> The query plan shows that the restriction is perfomed in the outermost ProjectRestrictResultSet, and that it actually sees all rows in the table.
>   ******* Project-Restrict ResultSet (1):
>   Number of opens = 1
>   Rows seen = 1280
>   Rows filtered = 1277
>   restriction = true
> In this case all 1280 rows are read from disk, and passed up the ResultSet chain. 1277 rows are filtered out so that, in the end, we only return 3 rows. 
> Ideally the execution should stop after pulling only 3 rows through the chain.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira