Skip to content

Aggregations referenced only in HAVING or WHERE are not created #53

@fupelaqu

Description

@fupelaqu

Description

When an aggregation function (e.g., COUNT(*), AVG(field)) appears in a HAVING or WHERE clause but is not present in the SELECT list, the aggregation is never created in the Elasticsearch query. The bucket_selector (for HAVING) references an aggregation name that does not exist, causing Elasticsearch to reject the query.

Reproduction

SELECT profile.city AS city
FROM dql_users
GROUP BY profile.city
HAVING COUNT(*) >= 1

Here COUNT(*) is not in the SELECT list. Before the fix, SingleSearch.aggregates only extracted aggregation fields from SELECT, so no count_all (or value_count) aggregation was created. The bucket_selector referenced a non-existent aggregation path.

Root Cause

SingleSearch.aggregates (in sql/.../query/package.scala) only collected aggregation fields from the SELECT clause:

// Before fix
lazy val aggregates: Seq[Field] = {
  select.fieldsWithComputedAliases
    .filter(f => f.isAggregation || f.isBucketScript)
    .filterNot(_.identifier.hasWindow) ++ windowFields
}

Aggregations appearing only in HAVING or WHERE were invisible to the bridge layer.

Fix

  1. Criteria.extractAggregationFields (sql/.../query/Where.scala): new method on the Criteria sealed trait that recursively extracts aggregation identifiers from criteria expressions and wraps them as Field instances with computed aliases (using Identifier.metricName).

  2. SingleSearch.aggregates (sql/.../query/package.scala): expanded to include aggregations from HAVING and WHERE, deduplicating against those already in SELECT:

lazy val aggregates: Seq[Field] = {
  val selectAggs = /* ... existing SELECT aggregations ... */
  val selectAggNames = selectAggs.flatMap(_.fieldAlias.map(_.alias)).toSet ++
    selectAggs.map(_.identifier.identifierName).toSet
  val havingAggs = having.flatMap(_.criteria).map(_.extractAggregationFields).getOrElse(Seq.empty)
  val whereAggs = where.flatMap(_.criteria).map(_.extractAggregationFields).getOrElse(Seq.empty)
  val extraAggs = (havingAggs ++ whereAggs)
    .filterNot(f =>
      f.fieldAlias.exists(a => selectAggNames.contains(a.alias)) ||
        selectAggNames.contains(f.identifier.identifierName)
    )
    .distinctBy(_.fieldAlias.map(_.alias))
  selectAggs ++ extraAggs
}

Related

Key Files

File Purpose
sql/.../query/package.scala SingleSearch.aggregates — expanded to include HAVING/WHERE
sql/.../query/Where.scala Criteria.extractAggregationFields — new recursive extractor

See Also

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions