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

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.4.1.3
• Component/s: SQL
• Labels: derby_triage10_10
• Urgency: Normal
• Assignee: Unassigned
• Reporter: Thomas Nielsen
• Created: 06/Mar/08 17:53
• Updated: 01/Oct/12 17:09

Description

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.