You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <ji...@apache.org> on 2008/04/10 06:56:05 UTC
[jira] Commented: (DERBY-3593) ErrorCode 30000 when quering a
select with 'having' clause and named tables with aliases for selected
fields
[ https://issues.apache.org/jira/browse/DERBY-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587490#action_12587490 ]
Bryan Pendleton commented on DERBY-3593:
----------------------------------------
Can you provide a complete standalone script that we can
run to see this? I'm having a bit of trouble reproducing the problem.
Here's what I tried, and it worked for me (with the current trunk code):
create table "values" (indicador_id int, valor int);
insert into "values" values (1,1);
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 ;
COL_1 |COL_2 |COL_3
----------------------------------
0 rows selected
> 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.