Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <15872206.1164964222357.JavaMail.jira@brutus>
Date: Fri, 1 Dec 2006 01:10:22 -0800 (PST)
From: "Andrew McIntyre (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-681) Eliminate the parser's rewriting of
 the abstract syntax tree for queries with GROUP BY and/or HAVING clauses
In-Reply-To: <1503805749.1131051224607.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-681?page=comments#action_12454838 ] 
            
Andrew McIntyre commented on DERBY-681:
---------------------------------------

Hi Manish,

I took a look at the current code for group by a while ago in relation to DERBY-1624 and eventually gave up as correctly fixing the issue went beyond my knowledge of the parsing/compilation phase of query processing and my available time. I've come across blog posts like this one:

http://blog.tremend.ro/2006/10/03/about-the-maturity-of-apache-derby/

in which the main difficulty was the inability to reference column aliases from the select list in group by clauses, so I think this is a worthwhile project to work on.

I came up with a band-aid of a fix which addressed DERBY-1624 by copying the relevant base column info and correlation name for the aliased columns in the rewritten subquery into the ColumnReferences for the result column list of the inner subquery, for which the correlation name could then be compared with the names of the result columns in the outer select with the group by clause to resolve the correct base column. IIRC, this approach worked for some cases, but there were failures in lang/groupby.sql that I didn't have time to track down.

I think a proper fix, as mentioned in the description of this issue, involves eliminating the rewriting of the query that happens in sqlgrammar.jj. If for some reason it might help you, though, I could try and find the patch for DERBY-1624 that I had worked on earlier.

> Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-681
>                 URL: http://issues.apache.org/jira/browse/DERBY-681
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>         Assigned To: Manish Khettry
>
> If a query contains a GROUP BY or HAVING clause, the parser rewrites the abstract syntax tree, putting aggregates into a subselect and treating the HAVING clause as the WHERE clause of a fabricated outer select from the subquery. This allows the compiler to re-use some machinery since the HAVING clause operates on the grouped result the way that the WHERE clause operates on the from list. Unfortunately, this rewriting creates an explosion of special cases in the compiler after parsing is done. The rewriting is not systematically handled later on in the compiler. This gives rise to defects like bug 280. We need to eliminate this special rewriting and handle the HAVING clause in a straightforward way. This is not a small bugfix but is a medium sized project.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira