Here we describe the implementation of the SQL standards ROW_NUMBER() window function in Derby.
The motivation behind implementing DERBY-2998 (subtask of DERBY-581) is to
improve derby performance
close the MySQL gap
The planned changes will improve Derby performance for queries where the user wants to see the N next rows starting from row M.
Typical uses are applications or application frameworks where one is browsing large volumes of data in a paginated fashion. Webshops and JPA (Java Persistence) are good real-life examples of such use.
We will also close part of the MySQL gap where MySQL implements this functionality in an effective manner using 'LIMIT' and 'OFFSET' as SQL keywords. MySQLs LIMIT and OFFSET are not part of the SQL standard as of SQL-2003.
We aim to add the same functionality in a similarly effective fashion, but while strictly adhering to the SQL standard specifying the ranking function ROW_NUMBER() to do this.
This change will extend interface IF4 Derby SQL by allowing the ROW_NUMBER() window function syntax (part of feature T-611) of IF102 SQL, section 6.10. All other changes are internal to Derby and not visible to the user, nor visible in any other interface.
We will not implement full window support as specified in the SQL-2003 standard with this change.
Changes by interface
IF4 - Derby SQL:
Add keyword ROW_NUMBER()
Add keyword OVER
Changes to code
The SQL parser (sqlgrammar.jj) will be extended to allow the ROW_NUMBER() syntax according to IF102. A RowNumberColumnNode will be inserted in the generated query tree as appropriate, and the necessary bytecode generated and executed. A counter will be held in the Activation, and the result of the ROW_NUMBER() function will be added to the ResultSet.
Tests will be written in the Derby testing framework to ensure correctness of the implemented feature.
There are no compatibility issues with previous versions, as this is an extension of new functionality to an existing interface.
There are no upgrade issues relating to this change.
Updates are needed to the following documentation, describing the new syntax.
Derby User Guide.
Derby SQL documentation.
Wiki pages comparing Derby to SQL-99/SQL-2003.
Wiki pages describing the ROW_NUMBER() functionality.
This change does not need an entry in the Release Notes.