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 Sat, 12 Apr 2008 17:17:04 GMT

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

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

I tried the script from the comment above with the current 10.3 branch and I get no errors,
so it seems that this problem has already been fixed in the 10.3 branch.

Here's my sysinfo from the code I was running:

--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[/home2/bpendleton/src/derby/10_3/10.3/jars/sane/derby.jar] 10.3.2.2 - (647473)

It seems that you should be able to resolve your problem by building and running
with the current 10.3 branch.

> 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