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.
