[
https://issues.apache.org/jira/browse/DERBY-3593?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12588317#action_12588317
]
Knut Anders Hatlen commented on DERBY-3593:
-------------------------------------------
The bug was fixed by this commit:
------------------------------------------------------------------------
r516454 | abrown | 2007-03-09 17:37:20 +0100 (Fri, 09 Mar 2007) | 8 lines
DERBY-681: Remove the "wrap group by's in a subselect" rewrite in the parser.
This patch preserves the having clause through bind and optimize phases and
then, during the final rewrite for aggregates in the GroupByNode, it transforms
the having clause to a valid restriction. See text file attached to the Jira
for more information.
Contributed by Manish Khettry ([EMAIL PROTECTED])
------------------------------------------------------------------------
> 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.
