Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1609487947.1209599575649.JavaMail.jira@brutus>
Date: Wed, 30 Apr 2008 16:52:55 -0700 (PDT)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-3593) ErrorCode 30000 when quering a select
 with 'having' clause and named tables with aliases for selected fields
In-Reply-To: <517973056.1207264045036.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-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-3593:
----------------------------------


you should run sysinfo and give this info if you think you might be having build/path problems.  Similar to what Bryan did above when he tested the issue.  If you are building your own code
line there is no reason you should not see the fix.  You should make sure that you have posted
exactly the problem script as people have only been testing what they can cut/paste from the
bug description.

You could try out the "test" release that andrew just produced - see
http://article.gmane.org/gmane.comp.apache.db.derby.devel/59167

Jars used for nightly branch testing can be found off of:
http://dbtg.thresher.com/derby/bits/10.3/

Note none of these are official apache releases, just private builds that have been made public.

> ErrorCode 30000 when quering a select with 'having' clause and named tables with aliases for selected fields
> ------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3593
>                 URL: https://issues.apache.org/jira/browse/DERBY-3593
>             Project: Derby
>          Issue Type: Bug
>    Affects Versions: 10.2.2.1, 10.3.2.2
>         Environment: WinVista 32bits, Running a java 1.4 application Aplication 
>            Reporter: Bruno Medeiros
>            Priority: Critical
>
> When I run a query like this:
> -------------------------------------------------------------------------
> select 
>     v.indicador_id as col_1, 
>     'someString' as col_2, 
>     sum(v.valor) as col_3
> from 
>     VALUES v
> where v.valor is null 
>     and v.indicador_id = 13
> group by v.indicador_id
> having sum(v.valor) > 3
> --------------------------------------------------------------------------
> I got a error:
> Error: Column 'V.COL_1' 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 'V.COL_1' is not a column in the target table.
> SQLState:  42X04
> ErrorCode: 30000
> ----------------------------------------------------------------------------
> if i gave no name to the table 'VALUES' or remove the aliases 'col_1' and 'col_3' of the corresponding selected fields, the query runs ok. The alias for the constant column, 'col_2', don't affect the query.
> The query also runs ok if i remove the 'having' clause.
> Queries that work:
> ----------------------------------------------------------------------------
> select 
>     v.indicador_id , 
>     'jujuba' as col_2, 
>     sum(v.valor) 
> from 
>     VALUES v
> where v.valor is null 
>     and v.indicador_id = 13
> group by v.indicador_id
> having sum(v.valor) > 3
> ----------------------------------------------------------------------------
> select 
>     indicador_id as col_1, 
>     'jujuba' as col_2, 
>     sum(valor) as col_3
> from 
>     VALUES
> where valor is null 
>     and indicador_id = 13
> group by indicador_id
> having sum(valor) > 3
> ----------------------------------------------------------------------------
> select 
>     v.indicador_id as col_1, 
>     'jujuba' as col_2, 
>     sum(v.valor) as col_3
> from 
>     VALUES v
> where v.valor is null 
>     and v.indicador_id = 13
> group by v.indicador_id
> ----------------------------------------------------------------------------
> I think there's a problem when derby is trying to match the selected fields with the grouped ones, because 'V.COL_1', as it appears in the error message, doesn't exist in any place of my query. The correct would be 'V.indicador' or 'COL_1'.
> Thanks in advance,
> Bruno Medeiros

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.