[BUG] Request-level filter is ignored for aggregation queries (`COUNT`) but applied for regular `SELECT`

[ZokiA]

Request-level filter is ignored for aggregation queries (COUNT) but applied for regular SELECT

Description

The SQL REST API request-level filter is correctly applied for regular SELECT queries, but it is ignored for aggregation queries such as COUNT(*).

The generated DSL (/_plugins/_sql/_explain) shows that the request filter disappears when the query contains an aggregation.

Environment

Component	Version
OpenSearch	3.6.0
SQL Plugin	3.6.0.0

Expected behavior

The request-level filter should always be merged with the SQL query, regardless of whether the SQL query contains aggregations.

The generated DSL should contain:

• predicates generated from the SQL WHERE clause
• predicates supplied in the request filter

Actual behavior

For regular SELECT queries, the request filter is correctly merged into the generated OpenSearch query.

For aggregation queries (COUNT(*)), the request filter is missing from the generated DSL and therefore has no effect on the result.

---

Reproduction

1. Aggregation query

POST /_plugins/_sql?format=jdbc
{
  "filter": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "(document.type.keyword: \"IE415B\")"
          }
        }
      ]
    }
  },
  "parameters": [
    {
      "type": "keyword",
      "value": "SearchSqlIT"
    }
  ],
  "query": "SELECT COUNT(*) AS c FROM test.app.sample1 WHERE document.name = ?"
}

Result:

{
  "schema": [
    {
      "name": "c",
      "alias": "c",
      "type": "double"
    }
  ],
  "datarows": [
    [
      20
    ]
  ]
}

Expected result:

5

---

2. Explain output

POST /_plugins/_sql/_explain
{
  "filter": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "(document.type.keyword: \"IE415B\")"
          }
        }
      ]
    }
  },
  "parameters": [
    {
      "type": "keyword",
      "value": "SearchSqlIT"
    }
  ],
  "query": "SELECT COUNT(*) AS c FROM test.app.sample1 WHERE document.name = ?"
}

Generated DSL:

{
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "document.name.keyword": {
                    "value": "SearchSqlIT"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggregations": {
    "c": {
      "value_count": {
        "field": "_index"
      }
    }
  }
}

Notice that the request-level filter

{
  "query_string": {
    "query": "(document.type.keyword: \"IE415B\")"
  }
}

is completely missing.

---

3. Regular SELECT

POST /_plugins/_sql/_explain
{
  "filter": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "(document.type.keyword: \"IE415B\")"
          }
        }
      ]
    }
  },
  "parameters": [
    {
      "type": "keyword",
      "value": "SearchSqlIT"
    }
  ],
  "query": "SELECT * FROM test.app.sample1 WHERE document.name = ?"
}

Generated DSL correctly contains both

• SQL predicate
• request-level filter

{
  "from": 0,
  "size": 200,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "document.name.keyword": {
                    "value": "SearchSqlIT",
                    "boost": 1
                  }
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        },
        {
          "bool": {
            "filter": [
              {
                "bool": {
                  "must": [
                    {
                      "query_string": {
                        "query": """(document.type.keyword: "IE415B")""",
                        "fields": [],
                        "type": "best_fields",
                        "default_operator": "or",
                        "max_determinized_states": 10000,
                        "enable_position_increments": true,
                        "fuzziness": "AUTO",
                        "fuzzy_prefix_length": 0,
                        "fuzzy_max_expansions": 50,
                        "phrase_slop": 0,
                        "escape": false,
                        "auto_generate_synonyms_phrase_query": true,
                        "fuzzy_transpositions": true,
                        "boost": 1
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              }
            ],
            "adjust_pure_negative": true,
            "boost": 1
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  }
}

which demonstrates that request filtering works correctly for non-aggregation queries.

---

Verification

The following SQL query returns the expected result:

SELECT COUNT(*)
FROM test.app.sample1
WHERE document.name = ?
  AND document.type = 'IE415B'

Result:

5

Therefore the request-level filter should produce the same result, but it instead returns:

20

---

Conclusion

It appears that the request-level filter is ignored during SQL planning for aggregation queries.

The problem is already visible in the output of /_plugins/_sql/_explain, indicating that the request filter is dropped before the OpenSearch query is executed.

This behavior is inconsistent with regular SELECT queries, where the request filter is correctly merged into the generated DSL.