Skip to content

[BUG] Aggregation with filter loses filtering when the aggregate is not pushed down #122

Closed
@chloe-zh

Description

@chloe-zh

Describe the bug
The aggregator properties are missing after analyzing because the analyzer wraps the aggregator to the delegated aggregator of NamedAggregator without inheriting the properties to the wrapper (Named Aggregator).

To Reproduce

Sample data:

PUT accounts/_bulk
{"index":{"_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"[email protected]","city":"Brogan","state":"IL"}
{"index":{"_id":"6"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"[email protected]","city":"Dante","state":"TN"}
{"index":{"_id":"13"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Madison Street","employer":"Quility","email":"[email protected]","city":"Nogal","state":"VA"}
{"index":{"_id":"18"}}
{"account_number":18,"balance":4180,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M","address":"467 Hutchinson Court","email":"[email protected]","city":"Orick","state":"MD"}

Example of bug:

select count(*) filter(where age > 30) from (select * from accounts) as a

Result:
{
  "schema": [
    {
      "name": """count(*) filter(where age > 30)""",
      "type": "integer"
    }
  ],
  "datarows": [
    [
      4
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

Explain:
{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": """[count(*) filter(where age > 30)]"""
    },
    "children": [
      {
        "name": "AggregationOperator",
        "description": {
          "aggregators": """[count(*) filter(where age > 30)]""",
          "groupBy": "[]"
        },
        "children": [
          {
            "name": "ProjectOperator",
            "description": {
              "fields": "[account_number, firstname, address, balance, gender, city, employer, state, age, email, lastname]"
            },
            "children": [
              {
                "name": "OpenSearchIndexScan",
                "description": {
                  "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":200,"timeout":"1m","_source":{"includes":["account_number","firstname","address","gender","balance","city","employer","state","age","email","lastname"],"excludes":[]}}, searchDone=false)"""
                },
                "children": []
              }
            ]
          }
        ]
      }
    ]
  }
}

Example working as expected:

select count(*) filter(where age > 30) from accounts

Result:
{
  "schema": [
    {
      "name": """count(*) filter(where age > 30)""",
      "type": "integer"
    }
  ],
  "datarows": [
    [
      3
    ]
  ],
  "total": 1,
  "size": 1,
  "status": 200
}

Explain:
{
  "root": {
    "name": "ProjectOperator",
    "description": {
      "fields": """[count(*) filter(where age > 30)]"""
    },
    "children": [
      {
        "name": "OpenSearchIndexScan",
        "description": {
          "request": """OpenSearchQueryRequest(indexName=accounts, sourceBuilder={"from":0,"size":0,"timeout":"1m","aggregations":{"count(*) filter(where age > 30)":{"filter":{"range":{"age":{"from":30,"to":null,"include_lower":false,"include_upper":true,"boost":1.0}}},"aggregations":{"count(*) filter(where age > 30)":{"value_count":{"field":"_index"}}}}}}, searchDone=false)"""
        },
        "children": []
      }
    ]
  }
}

Plugins
SQL

Host/Environment (please complete the following information):

  • OS: MacOS
  • Version: Catalina 10.15.7

Metadata

Metadata

Assignees

Labels

SQLbugSomething isn't working

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions