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] Commented: (DERBY-3593) ErrorCode 30000 when quering a select with 'having' clause and named tables with aliases for selected fields
Date Thu, 10 Apr 2008 16:14:07 GMT

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

Bruno Medeiros commented on DERBY-3593:
---------------------------------------

Hi, Brian!

I tried with your simple script and got the same error on my version (10.3.2.2):
---------------------------------------------------------------------------------------------
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
---------------------------------------------------------------------------------------------

You told that the problem don't occour in the head version (is it the 10.7?), so it's probably
been fixed in some branch.
Could you port this fix to the 10.3 branch?
I think it's dificult to me to use a version much ahead then the official released version.

Thanks,

Bruno Medeiros

> 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