[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574756#action_12574756 ] thomanie edited comment on DERBY-2998 at 3/3/08 1:54 PM: --- I was looking into why the distinct issue happened tonight and decided to fix it. Attaching 'd2998-followup-distinct.diff' that solves this for both queries and subqueries, as well as adding a couple of DISTINCT queries to OLAPTest. The updated OLAPTest pass. The proposed change in ProjectRestrictNode.java isn't perfect. I had to use a boolean variable to trigger VCN regeneration to avoid getting a nasty if-instanceof construct. It might just be too late in the day... When the distinct issue is fixed the ArrayIndexOutOfBoundsException Army reported is avoided since RealDistinctScanStatistics is not used. I'll remove the patch I uploaded earlier today. was (Author: thomanie): I was looking into why the distinct issue tonight and decided to fix it. Attaching 'd2998-followup-distinct.diff' that solves this for both queries and subqueries, as well as adding a couple of DISTINCT queries to OLAPTest. The updated OLAPTest pass. The proposed change in ProjectRestrictNode.java isn't perfect. I had to use a boolean variable to trigger VCN regeneration to avoid getting a nasty if-instanceof construct. It might just be too late in the day... When the distinct issue is fixed the ArrayOutOfBoundsException Army reported is avoided since RealDistinctScanResultSet is not used. I'll remove the patch I uploaded earlier today. > 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-distinct.diff, > d2998-followup-distinct.stat, d2998-followup-issue1.diff, > d2998-followup-issue1.stat, d2998-followup-issue4.diff, > d2998-followup-issue4.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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574006#action_12574006 ] army edited comment on DERBY-2998 at 2/29/08 3:35 PM: - Committed d2998-19.diff and d2998-test9.diff both with svn # 632494: URL: http://svn.apache.org/viewvc?rev=632494&view=rev In addition to the follow-up work mentioned in my previous comment, I also noticed one more thing that requires investigation. The following query fails with an ArrayIndexOutOfBounds exception while trying to print the query plan (i.e. after the query is executed, and only if "logQueryPlan" is set to true): 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); If I remove the "DISTINCT" or else replace one of the row_number() columns in the SELECT DISTINCT query with a simple column reference, the query plan is generated correctly. Pretty minor in the grand scheme of things, but I thought I'd mention it. Thanks for all of your work on this, Thomas! was (Author: army): Committed d2998-19.diff and d9998-test9.diff both with svn # 632494: URL: http://svn.apache.org/viewvc?rev=632494&view=rev In addition to the follow-up work mentioned in my previous comment, I also noticed one more thing that requires investigation. The following query fails with an ArrayIndexOutOfBounds exception while trying to print the query plan (i.e. after the query is executed, and only if "logQueryPlan" is set to true): 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); If I remove the "DISTINCT" or else replace one of the row_number() columns in the SELECT DISTINCT query with a simple column reference, the query plan is generated correctly. Pretty minor in the grand scheme of things, but I thought I'd mention it. Thanks for all of your work on this, Thomas! > 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-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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12573708#action_12573708 ] thomanie edited comment on DERBY-2998 at 2/29/08 8:19 AM: Both suites.All and derbyAll passed with patch 19 was (Author: thomanie): suites.All passed with patch 19, derbyAll running > 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-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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572531#action_12572531 ] thomanie edited comment on DERBY-2998 at 2/26/08 8:08 AM: The culprit turned out to be ResultColumnList.allExpressionsAreColumns(). After adding an explict check for any window functions here, we avoid massing up the correlation flag and every thing seems to be working as expected. All 20-ish queries in the test7 patch returns the expected rows. I should probably add that I want to clean up a couple of issues with patch 17 before I check 'patch available', but I wanted to post the working code so you can play with it, and maybe give some additional feedback. was (Author: thomanie): The culprit turned out to be ResultColumnList.allExpressionsAreColumns(). After adding an explict check for any window functions here, we avoid massing up the correlation flag and every thing seems to be working as expected. > 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-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-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 > > > 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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12572040#action_12572040 ] thomanie edited comment on DERBY-2998 at 2/25/08 2:03 AM: d2998-15 changes: - adds abstract class WindowFunctionColumnNode, which RowNumberColumnNode subclasses. This makes any instanceof-checks in other parts of code generic. Common code moved from RNCN to WFCN. - simplified pulling WindowNodes into the querytree in SelectNode.genProjectRestrictNode. The 15 patch still has the resultset numbering problem in the RCLof the PRN that was originally a FromSubquery. was (Author: thomanie): d2998-15 changes: - adds abstract class WindowFunctionColumnNode, which RowNumberColumnNode subclasses. This makes any instanceof-checks in other parts of code generic. Common code moved from RNCN to WFCN. - simplified pulling WindowNodes into the querytree in SelectNode.genProjectRestrictNode. The 15 patch still has the resultset numbering in the RCLof the PRN that was originally a FromSubquery. > 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-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-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 > > > 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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536670 ] thomanie edited comment on DERBY-2998 at 10/22/07 6:49 AM: - Here's how it seems to work for indexes for the following simple query on table t where column a is the primary key. select * from t where a <= 2; During parsing we add a CursorNode and an IndexToBaseRowNode to the QueryTree since we have an index on 'a'. Based on the IndexToBaseRowNode we create the accompanying IndexRowToBaseRowResultSet and feed any restrictions to its constructor. In my case the restriction is generated code for '<='. On opening the IndexRowToBaseRowResultSet we also call its "source" .openCore() method BulkTableScanResultSet.openCore(). BulkTableScanResultSet does just that, it reads a base table or an index in bulk (chunks) from Store. The execution then does a do-while loop that calls .getNextRowCore(). For each pass it invokes the restriction code it got in its constructor by restriction.invoke(). In my case this ends up invoking SQLInteger.lessOrEqual(). The do-while loop exits once the restriction is met. The key seems to be the three calls to openCore(), getNextRowCore() and closeCore(), and overrides of these in the ResultSet subclasses. was (Author: thomanie): Here's how it seems to work for indexes for the following simple query on table t where column a is the primary key. select * from t where a <= 2; During parsing we add a CursorNode and an IndexToBaseRowNode to the QueryTree since we have an index on 'a'. Based on the IndexToBaseRowNode we create the accompanying IndexRowToBaseRowResultSet and feed any restrictions to its constructor. In my case the restriction is generated code for '<='. On opening the IndexRowToBaseRowResultSet we also call its "source" .openCore() method BulkTableScanResultSet.openCore(). BulkTableScanResultSet does just that, it reads a base table or an index in bulk (chunks) from Store. The execution then does a do-while loop that calls .getNextRowCore(). For each pass it invokes the restriction code it got its the constructor with restriction.invoke(). In my case this ends up invoking SQLInteger.lessOrEqual(). The do-while loop exits once the restriction is met. The key seems to be the three calls to openCore(), getNextRowCore() and closeCore(), and overrides of these in the ResultSet subclasses. > 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: row_number_prototype-2c.diff, > row_number_prototype-2c.stat > > > 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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526372 ] thomanie edited comment on DERBY-2998 at 9/11/07 12:38 AM: - Bryan, I agree, lets treat the "AS R ... WHERE" problem in a separate JIRA. There are some similar constructs that doesn't work either. Looks like it has to do with the WHERE clause working with the data before projection, and row_number(), for one, is added at projection. I haven't verified this though. If you do the nested select, I think you actually need to project the inner select into a temporary table to enable the outer SELECT ... WHERE to complete without a problem: ij> select * from (select row_number() as r, a, b from t) as t(r,a,b) where r > 2; R |A |B 3 |3 |7 This means there is no short-cut in the execution logic to only project the N first/next rows (as of now). The exact query you posted, does not actually seem to work though: ij> select * from (select row_number() as r, * from t) as t(r,*) where r>2; ERROR 42X01: Syntax error: Encountered "*" at line 1, column 42. Wildcard column specifier is not allowed in a comma separated list with column specifiers or expressions. However using 't.*' (kinda) works: ij> select * from (select row_number() as r, t.* from t) as t(r,*) where r>2; ERROR 42X01: Syntax error: Encountered "where" at line 1, column 55. ij> select * from (select row_number() as r, a, b from t) where r >=2; ERROR 42X01: Syntax error: Encountered "where" at line 1, column 55. The two last ones are valid queries AFAIK, and accepting WHERE here is possibly worthy of its own JIRA? was (Author: thomanie): Bryan, I agree, lets treat the "AS R ... WHERE" problem in a separate JIRA. There are some similar constructs that doesn't work either. Looks like it has to do with the WHERE clause working with the data before projection, and row_number(), for one, is added at projection. I haven't verified this though. If you do the nested select, I think you actually need to project the inner select into a temporary table to enable the outer SELECT ... WHERE to complete without a problem: ij> select * from (select row_number() as r, a, b from t) as t(r,a,b) where r > 2; R |A |B 3 |3 |7 This means there is no short-cut in the execution logic to only project the N first/next rows (as of now). The exact query you posted, does not actually seem to work though: ij> select * from (select row_number() as r, * from t) as t(r,*) where r>2; ERROR 42X01: Syntax error: Encountered "*" at line 1, column 42. Wildcard column specifier is not allowed in a comma separated list - it probably should be when used with OLAP functions in this way. Worthy of it's own JIRA possibly? ij> select * from (select row_number() as r, a, b from t) where r >=2; ERROR 42X01: Syntax error: Encountered "where" at line 1, column 55. This looks like a valid query, but I need to check... > 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: row_number_prototype.diff, row_number_prototype.stat > > > 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.
[jira] Issue Comment Edited: (DERBY-2998) Add support for ROW_NUMBER() window function
[ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526103 ] thomanie edited comment on DERBY-2998 at 9/10/07 6:35 AM: I have attached my prototype for the row_number() implementation. It's not intended for commit. With a simple testtable t you can now do: --- ij> select row_number(),a,b from t; row_number()|A |B 1 |1 |9 2 |2 |8 3 |3 |7 AS clause is also functioning for this query. However, --- ij> select row_number() as r from t where r >= 2; ERROR 42X04: Column 'R' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'R' is not a column in the target table. --- This seems to be due to a limitation in derby. I get the same error with 'select a+b as r ...'. This can be rewritten using a nested select statement --- ij> select * from (select row_number() as r from t) as t(r) where r >= 2; R 2 3 --- The patch works along these lines: I added a new class RowNumberColumnNode that is added to the querytree at compiletime. On invokation of ResultSetList.generateCore() we generate code to call new method BaseActivation.getSetRowNumber() that does the actual incrementing during execution. Diff and .stat files are attached. There is one issue I shortcut - the setup of the rnCache array in BaseActivation. To get it working I just created a 10 element array to hold the row_number() values from invocation to invocation. This should be either number of actual row_number() columns, or total number of columns in the resultset we are building. Your comments are greatly appreciated :) was (Author: thomanie): I have attached my prototype for the row_number() implementation. With a simple testtable t you can now do: --- ij> select row_number(),a,b from t; row_number()|A |B 1 |1 |9 2 |2 |8 3 |3 |7 AS clause is also functioning for this query. However, --- ij> select row_number() as r from t where r >= 2; ERROR 42X04: Column 'R' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'R' is not a column in the target table. --- This seems to be due to a limitation in derby. I get the same error with 'select a+b as r ...'. This can be rewritten using a nested select statement --- ij> select * from (select row_number() as r from t) as t(r) where r >= 2; R 2 3 --- The patch works along these lines: I added a new class RowNumberColumnNode that is added to the querytree at compiletime. On invokation of ResultSetList.generateCore() we generate code to call new method BaseActivation.getSetRowNumber() that does the actual incrementing during execution. Diff and .stat files are attached. > 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: row_number_prototype.diff, row_number_prototype.stat > > > 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.
