Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3593

ErrorCode 30000 when quering a select with 'having' clause and named tables with aliases for selected fields

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.2.2.1, 10.3.3.0
    • Fix Version/s: 10.3.3.1, 10.4.1.3
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      WinVista 32bits, Running a java 1.4 application Aplication
    • Urgency:
      Blocker

      Description

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              brunojcm Bruno Medeiros
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: