[BUG] PPL where command does not work as expected.

[penghuo]

What is the bug?
PPL query include where command throw unexpected exception

{
  "error": {
    "reason": "Error occurred in OpenSearch engine: all shards failed",
    "details": "Shard[0]: [test/LI_vgiDbRJ6XzIAwO1uWtw] QueryShardException[failed to create query: Failed to compile inline script [{\"langType\":\"calcite\",\"script\":\"rO0ABXQEoXsKICAib3AiOiB7CiAgICAibmFtZSI6ICJBTkQiLAogICAgImtpbmQiOiAiQU5EIiwKICAgICJzeW50YXgiOiAiQklOQVJZIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQk9PTEVBTiIsCiAgICAgICAgIm51bGxhYmxlIjogdHJ1ZQogICAgICB9CiAgICB9LAogICAgewogICAgICAib3AiOiB7CiAgICAgICAgIm5hbWUiOiAicXVlcnlfc3RyaW5nIiwKICAgICAgICAia2luZCI6ICJPVEhFUl9GVU5DVElPTiIsCiAgICAgICAgInN5bnRheCI6ICJGVU5DVElPTiIKICAgICAgfSwKICAgICAgIm9wZXJhbmRzIjogWwogICAgICAgIHsKICAgICAgICAgICJvcCI6IHsKICAgICAgICAgICAgIm5hbWUiOiAiTUFQIiwKICAgICAgICAgICAgImtpbmQiOiAiTUFQX1ZBTFVFX0NPTlNUUlVDVE9SIiwKICAgICAgICAgICAgInN5bnRheCI6ICJTUEVDSUFMIgogICAgICAgICAgfSwKICAgICAgICAgICJvcGVyYW5kcyI6IFsKICAgICAgICAgICAgewogICAgICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAgICAgICAgICJ0eXBlIjogewogICAgICAgICAgICAgICAgInR5cGUiOiAiVkFSQ0hBUiIsCiAgICAgICAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgICAgICAgInByZWNpc2lvbiI6IC0xCiAgICAgICAgICAgICAgfQogICAgICAgICAgICB9LAogICAgICAgICAgICB7CiAgICAgICAgICAgICAgImR5bmFtaWNQYXJhbSI6IDIsCiAgICAgICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICAgICAidHlwZSI6ICJWQVJDSEFSIiwKICAgICAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgICAgICAgICAicHJlY2lzaW9uIjogLTEKICAgICAgICAgICAgICB9CiAgICAgICAgICAgIH0KICAgICAgICAgIF0KICAgICAgICB9CiAgICAgIF0sCiAgICAgICJjbGFzcyI6ICJvcmcub3BlbnNlYXJjaC5zcWwuZXhwcmVzc2lvbi5mdW5jdGlvbi5Vc2VyRGVmaW5lZEZ1bmN0aW9uQnVpbGRlciQxIiwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQk9PTEVBTiIsCiAgICAgICAgIm51bGxhYmxlIjogZmFsc2UKICAgICAgfSwKICAgICAgImRldGVybWluaXN0aWMiOiBmYWxzZSwKICAgICAgImR5bmFtaWMiOiBmYWxzZQogICAgfQogIF0KfQ==\"}] using lang [opensearch_compounded_script]]; nested: GeneralScriptException[Failed to compile inline script [{\"langType\":\"calcite\",\"script\":\"rO0ABXQEoXsKICAib3AiOiB7CiAgICAibmFtZSI6ICJBTkQiLAogICAgImtpbmQiOiAiQU5EIiwKICAgICJzeW50YXgiOiAiQklOQVJZIgogIH0sCiAgIm9wZXJhbmRzIjogWwogICAgewogICAgICAiZHluYW1pY1BhcmFtIjogMCwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQk9PTEVBTiIsCiAgICAgICAgIm51bGxhYmxlIjogdHJ1ZQogICAgICB9CiAgICB9LAogICAgewogICAgICAib3AiOiB7CiAgICAgICAgIm5hbWUiOiAicXVlcnlfc3RyaW5nIiwKICAgICAgICAia2luZCI6ICJPVEhFUl9GVU5DVElPTiIsCiAgICAgICAgInN5bnRheCI6ICJGVU5DVElPTiIKICAgICAgfSwKICAgICAgIm9wZXJhbmRzIjogWwogICAgICAgIHsKICAgICAgICAgICJvcCI6IHsKICAgICAgICAgICAgIm5hbWUiOiAiTUFQIiwKICAgICAgICAgICAgImtpbmQiOiAiTUFQX1ZBTFVFX0NPTlNUUlVDVE9SIiwKICAgICAgICAgICAgInN5bnRheCI6ICJTUEVDSUFMIgogICAgICAgICAgfSwKICAgICAgICAgICJvcGVyYW5kcyI6IFsKICAgICAgICAgICAgewogICAgICAgICAgICAgICJkeW5hbWljUGFyYW0iOiAxLAogICAgICAgICAgICAgICJ0eXBlIjogewogICAgICAgICAgICAgICAgInR5cGUiOiAiVkFSQ0hBUiIsCiAgICAgICAgICAgICAgICAibnVsbGFibGUiOiB0cnVlLAogICAgICAgICAgICAgICAgInByZWNpc2lvbiI6IC0xCiAgICAgICAgICAgICAgfQogICAgICAgICAgICB9LAogICAgICAgICAgICB7CiAgICAgICAgICAgICAgImR5bmFtaWNQYXJhbSI6IDIsCiAgICAgICAgICAgICAgInR5cGUiOiB7CiAgICAgICAgICAgICAgICAidHlwZSI6ICJWQVJDSEFSIiwKICAgICAgICAgICAgICAgICJudWxsYWJsZSI6IHRydWUsCiAgICAgICAgICAgICAgICAicHJlY2lzaW9uIjogLTEKICAgICAgICAgICAgICB9CiAgICAgICAgICAgIH0KICAgICAgICAgIF0KICAgICAgICB9CiAgICAgIF0sCiAgICAgICJjbGFzcyI6ICJvcmcub3BlbnNlYXJjaC5zcWwuZXhwcmVzc2lvbi5mdW5jdGlvbi5Vc2VyRGVmaW5lZEZ1bmN0aW9uQnVpbGRlciQxIiwKICAgICAgInR5cGUiOiB7CiAgICAgICAgInR5cGUiOiAiQk9PTEVBTiIsCiAgICAgICAgIm51bGxhYmxlIjogZmFsc2UKICAgICAgfSwKICAgICAgImRldGVybWluaXN0aWMiOiBmYWxzZSwKICAgICAgImR5bmFtaWMiOiBmYWxzZQogICAgfQogIF0KfQ==\"}] using lang [opensearch_compounded_script]]; nested: UnsupportedOperationException[Relevance search query functions are only supported when they are pushed down];\n\nFor more details, please send request for Json format to see the raw response from OpenSearch engine.",
    "type": "SearchPhaseExecutionException"
  },
  "status": 400
}

How can one reproduce the bug?

###
PUT {{baseUrl}}/test
Content-Type: application/x-ndjson

{
  "mappings": {
    "properties": {
      "@timestamp": {
        "type": "date"
      },
      "url": {
        "type": "text"
      },
      "is_internal": {
        "type": "boolean"
      }
    }
  }
}

###
POST {{baseUrl}}/test/_bulk
Content-Type: application/x-ndjson

{"index": {}}
{"@timestamp":"2025-08-19T04:51:24Z", "url": "http"}
{"index": {}}
{"@timestamp":"2025-08-19T04:51:24Z", "url": "http", "is_internal": true}
{"index": {}}
{"@timestamp":"2025-08-19T04:51:24Z", "url": "http", "is_internal": false}

###
POST {{baseUrl}}/_plugins/_ppl/
Content-Type: application/x-ndjson

{
    "query": "source=test url=http | where is_internal=true"
}

What is the expected behavior?
return correctly

What is your host/environment?

• main, commit id 425682f

[songkant-aws]

Let me take a look

[songkant-aws]

The type of "is_internal" field in the index mapping is BOOLEAN. Since it's already a BOOLEAN type, Calcite simplifies the expression condition from EQUAL(is_internal, true) to FIELD(is_internal). It's no longer a regular expression.

The goal of this query is to push down this expression AND(query_string(), FIELD(is_internal)) to scan. Our pushdown PredicateAnalyzer won't treat a FIELD(is_internal) as regular expression. So the analyzed expression list will place NULL at that place, causing PredicateAnalyzer throws NPE while generating DSL query blob. This triggers a fallback mechanism to wrap this condition expression as script but our script can't execute the search relevance function. Instead, it should be a pure DSL query that combines the two operands using a logical AND.

I think we may need to support this case in pushdown PredicateAnalyzer.

[qianheng-aws]

@songkant-aws Both below queries can work. But combining these 2 filter conditions together induces the above exception.

source=test url=http

source=test  | where is_internal=true

The query "source=test url=http | where is_internal=true" will produce a script for SCRIPT->AND(query_string(MAP('query', 'url:http':VARCHAR)), $0) while query_string function is not supported in our script.

[songkant-aws]

@qianheng-aws Yes, that's what I have observed in PredicateAnalyzer. The current andOr only evaluates non-field expressions. So it fallbacks to script, which throws exception at execution runtime