ESQL: Filtering on _index is ignored

[nik9000]

Description

I was trying to debug something with our logging cluster and ran:

POST _query 
{
  "query": "FROM logging-*:logs-* METADATA _index\n| WHERE _index NOT LIKE \"*east*\"\n| STATS c=COUNT(*) BY _index\n| SORT c DESC\n| LIMIT 10",
  "locale": "en",
  "include_ccs_metadata": true,
  "filter": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "@timestamp": {
              "format": "strict_date_optional_time",
              "gte": "2025-06-16T21:37:04.079Z",
              "lte": "2025-06-16T21:52:04.079Z"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  },
  "profile": true
}

The top hit was for logging-us-east-1:.ds-logs-proxy.log-default-2025.06.14-001371. Which contains east. If I shift the WHERE to after the STATS the filtering works.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[nik9000]

I saw this in 8.18

[nik9000]

I'm looking to reproduce this locally. Seems important.

[nik9000]

I've tracked this one down. To hit this bug you must:

• Use METADATA _index
• Use LIKE or NOT LIKE on it
• In a position where we'd push a query to Lucene if it were a "regular" field

The mechanism of action is that we go through the "push to lucene" steps, building a ql WildcardQuery which builds a WildcardQueryBuilder which asks the field type to build a wildcardQuery. Here's where things go wrong. Most field types implement the glob-style matching we expect for LIKE. But _index, implemented by IndexFieldType, does not. It sends the wildcard string to SearchIndexNameMatcher which very different semantics.

It splits on : - the remote cluster separator. If there isn't a : then the pattern only matches local indices. That's where the example in the description fails. All the indices I was querying are remote.

If there is a : it glob matches the stuff before the : against the cluster alias.

The post : is not glob matched against the index name. It's expanded into all matching indices using the rules we use for index patterns. Then we check if the index being queried is in the list we expand into. Meaning it'll resolve data streams and aliases. And, I believe, date math.

I'm sure this is very useful for wildcard queries over _search. But we need a purely textual match on the automaton generated by our Like infrastructure. On the name of the index. LIKE can't suddenly do more than text matching if it's before a STATS. And it can't ignore all indices on remote clusters if the pattern doesn't have : in it. * matches :.

This is another one of those "it's a shame we used the query DSL's query builder for push down". Honestly I'm thinking we should build something else for push down. More methods on MappedFieldType maybe. I dunno. I'll see if I can hack up a solution that works now and we can reason about something better later.