[FEATURE] Support date field comparsion operation pushdown

[penghuo]

Is your feature request related to a problem?

• when plugins.calcite.enabled is enabled, date field comparsion is not pushdown to DSL

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

{
    "query": "source=testindex | where timestamp > '2025-05-01 05:40:00'"
}

{
  "calcite": {
    "logical": "LogicalProject(timestamp=[$0])\n  LogicalFilter(condition=[>(TIMESTAMP($0), TIMESTAMP('2025-05-01 05:40:00':VARCHAR))])\n    CalciteLogicalIndexScan(table=[[OpenSearch, testindex]])\n",
    "physical": "EnumerableCalc(expr#0=[{inputs}], expr#1=[TIMESTAMP($t0)], expr#2=['2025-05-01 05:40:00':EXPR_TIMESTAMP VARCHAR], expr#3=[>($t1, $t2)], timestamp=[$t0], $condition=[$t3])\n  CalciteEnumerableIndexScan(table=[[OpenSearch, testindex]], PushDownContext=[[PROJECT->[timestamp]], OpenSearchRequestBuilder(sourceBuilder={\"from\":0,\"timeout\":\"1m\",\"_source\":{\"includes\":[\"timestamp\"],\"excludes\":[]}}, requestedTotalSize=10000, pageSize=null, startFrom=0)])\n"
  }
}

What solution would you like?
date field comparsion should pushdown to DSL

What alternatives have you considered?
n/a

Do you have any additional context?

• prepare data

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

{
  "mappings": {
    "properties": {
      "timestamp" : {
        "type" : "date",
        "format" : "yyyy-MM-dd HH:mm:ss" 
      }
    }
  }
}

###
POST {{baseUrl}}/testindex/_doc
Content-Type: application/x-ndjson

{
  "timestamp" : "2025-05-01 05:49:00"
}

[LantaoJin]

It's known issue which is related to script pushdown #3379, right? @yuancu @qianheng-aws . the timestamp comparing a string will Implicit adding a converting udf and it belongs to udf pushdown (script).

[LantaoJin]

@penghuo how about close this one after being linked to its duplicated (parent) issue?

[penghuo]

@penghuo how about close this one after being linked to its duplicated (parent) issue?

I do not think it required script pushdown. Can pushdown leverage DSL range query? for instance

{"from":0,"size":10000,"timeout":"1m","query":{"range":{"timestamp":{"from":"2025-05-01 05:40:00","to":null,"include_lower":false,"include_upper":true,"boost":1.0}}},"_source":{"includes":["v","timestamp"],"excludes":[]},"sort":[{"_doc":{"order":"asc"}}]}

Timestamp comparison is one of the most common use cases on the Discover page. The time picker automatically appends timestamp filters to each query.