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

Abierto
  • ¿Algo no está bien?
#5,649 1 comentario 0 reacciones 1 asignado Ver en GitHub

Los mantenedores suelen responder en 1 día

@RyanL1997 ya está trabajando en esto.

Desde el 28/7/2026.

Evaluación

Este issue todavía no se ha evaluado.

Descripción

bug

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.

Lenguaje dominante
Java
Estrellas
175
Forks
236
Merge medio
2 d 15 h
PR fusionados (30 d)
41

Preparar el entorno

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de opensearch-project/sql

Todos los issues de opensearch-project/sql

Issues similares

Más issues de Java

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.