db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bruno Medeiros (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3593) ErrorCode 30000 when quering a select with 'having' clause and named tables with aliases for selected fields
Date Thu, 03 Apr 2008 23:07:25 GMT
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.


Mime
View raw message