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.0.0
Reporter: Thomas Nielsen
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.
-
You can reply to this email to add a comment to the issue online.
