Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2013471629.1203364114901.JavaMail.jira@brutus>
Date: Mon, 18 Feb 2008 11:48:34 -0800 (PST)
From: "Thomas Nielsen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2998) Add support for ROW_NUMBER() window
 function
In-Reply-To: <14732006.1186647103190.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12569995#action_12569995 ] 

Thomas Nielsen commented on DERBY-2998:
---------------------------------------

The problem with the latest patch is due to the new code that changes the ResultColumnList column references into virtual column references.

With this issue fixed I get incorrect results whenever there is a subquery involved. This again is because of how preprocessing is done. The outer SelectNodes FromList contains a FromSubquery with the inner SelectNode. The FromSubquery is rewritten during preprocessing to a ProjectRestictNode. This PRN gets the original ResultColumns (i.e before we optimize and modify the access paths and pull the WindowNodes into the tree and ResultColumList) In the original RCL the virtual column ids are not what the lower levels PRNs end up with after optimization:

So, before preprocessing:
SelectNode
   |
FromSubquery
   | 
SelectNode
   |
...

During preprocessing this is rewritten to:
SelectNode
   |
PRN <-- This PRN has original RCL
   |
SelectNode
   |
...

After optimization we have something like:
PRN
  |
PRN <-- This PRN has original RCL, which now has wrong VirtualColumnID references
  |
PRN <-- This has correct RCL after optimization and pulling WindowNodes up
  |
WindowNode
  |
PRN
  |
....

Just need to figure out how to solve this. Might be possible to collapse the PRN as it seems to be a no-op, or pull the RCL up from the lower PRN after/during optimization if there are window columns involved?

> 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-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
>
>
> 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.