db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (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 17:08:10 GMT

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

Bryan Pendleton commented on DERBY-3593:
----------------------------------------

> Could you port this fix to the 10.3 branch? 

Gladly! We just need to identify which fix it was.

I'll try to find some time to do this, but if you can help 
identify the fix, that would be great.

For example, the first step would be if you could fetch the
current tip of the 10.3 branch and build it yourself, and
verify that the problem still occurs, since it's possible that
the fix has already been merged to the 10.3 branch after
the 10.3.2.2 release was made.

> 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