[ES|QL] DATE_DIFF Integer Overflow returns nothing

[BenB196]

Elasticsearch Version

8.17.1

Installed Plugins

No response

Java Version

bundled

OS Version

Container

Problem Description

When you run the DATE_DIFF function that produces a value larger than what can fit into an Integer (DATE_DIFF's response value), nothing is returned.

I'm opening this as a bug for a few reasons, but it's possible that it might also be a feature request:

1. ES|QL doesn't seem to have any documentation on the standard response one should expect when a value overflow happens; (should it be an error/exception?, should it return null?)
   • For me it took a bit to recognize in a query that I was writing that I was seeing weird results because DATE_DIFF was overflowing
2. DATE_DIFF being locked to a response of integer rather than long (or possibly definable/dynamic) seems somewhat limiting, especially with the smaller time units and longer date ranges.

Steps to Reproduce

1. Run an ES|QL query where 2 dates have a diff larger than int in any time unit.

Example:

ROW start_date = "2025-02-14T17:05:36.630Z"::datetime, end_date = "2025-03-22T15:17:56.631Z"::datetime
| EVAL diff = DATE_DIFF("millisecond", start_date, end_date)

2. Observe that the response does not include anything for diff

{
  "rawResponse": {
    "is_running": false,
    "took": 3,
    "all_columns": [
      {
        "name": "start_date",
        "type": "date"
      },
      {
        "name": "end_date",
        "type": "date"
      },
      {
        "name": "diff",
        "type": "integer"
      }
    ],
    "columns": [
      {
        "name": "start_date",
        "type": "date"
      },
      {
        "name": "end_date",
        "type": "date"
      }
    ],
    "values": [
      [
        "2025-02-14T17:05:36.630Z",
        "2025-03-22T15:17:56.631Z"
      ]
    ]
  }
}

3. Change the unit to something that doesn't overflow int

Example:

ROW start_date = "2025-02-14T17:05:36.630Z"::datetime, end_date = "2025-03-22T15:17:56.631Z"::datetime
| EVAL diff = DATE_DIFF("second", start_date, end_date)

4. Observe that a value is included for diff.

{
  "rawResponse": {
    "is_running": false,
    "took": 2,
    "all_columns": [
      {
        "name": "start_date",
        "type": "date"
      },
      {
        "name": "end_date",
        "type": "date"
      },
      {
        "name": "diff",
        "type": "integer"
      }
    ],
    "columns": [
      {
        "name": "start_date",
        "type": "date"
      },
      {
        "name": "end_date",
        "type": "date"
      },
      {
        "name": "diff",
        "type": "integer"
      }
    ],
    "values": [
      [
        "2025-02-14T17:05:36.630Z",
        "2025-03-22T15:17:56.631Z",
        3103940
      ]
    ]
  }
}

Logs (if relevant)

No response

[elasticsearchmachine]

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