Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <4757442.1173736991236.JavaMail.jira@brutus>
Date: Mon, 12 Mar 2007 15:03:11 -0700 (PDT)
From: "A B (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


    [ https://issues.apache.org/jira/browse/DERBY-681?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12480197 ] 

A B commented on DERBY-681:
---------------------------

Hi Manish,

I think I've found another case where use of "size()" instead of "visibleSize()" is leading to incorrect behavior after the patch for this issue.  Take the following example:

ij> create table t1 (d double, vc varchar(20), x int);
0 rows inserted/updated/deleted

ij> select distinct vc, x from t1 as myt1
      where d <= (select max(myt1.d) from t1 as myt2
          where myt1.vc = myt2.vc and myt1.d <= myt2.d
          having count(distinct d) <= 3);
ERROR 42X39: Subquery is only allowed to return a single column.

Before the commit for this issue the above query ran without error.  But now, with the latest trunk, the query fails because Derby thinks the subquery is returning more than one column, which is not true. The relevant code is in SubqueryNode.java:

        /* The parser does not enforce the fact that a subquery can only return
         * a single column, so we must check here.
         */
        if (resultColumns.size() != 1)
        {
            throw StandardException.newException(SQLState.LANG_NON_SINGLE_COLUMN_SUBQUERY);
        }

I think the call to "size()" here should be replaced with a call to "visibleSize()".  If you agree that this is the correct fix then I can go ahead and commit this change to trunk as a "followup" patch for this issue.

> Eliminate the parser's rewriting of the abstract syntax tree for queries with GROUP BY and/or HAVING clauses
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-681
>                 URL: https://issues.apache.org/jira/browse/DERBY-681
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>         Assigned To: Manish Khettry
>         Attachments: 681.patch.txt, 681.patch2.txt, 681.patch3.txt, notes.txt
>
>
> 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.
-
You can reply to this email to add a comment to the issue online.