SQL: request_timeout query parameter does not behave quite as expected

[Luegg]

According to the docs, the request_timeout parameter acts as a "Timeout before the request fails". Internally, this parameter is passed to the resulting search request as the timeout. This has some shortcomings:

• timeout only limits the length of the fetch phase on each shard, not the runtime of the whole query.
• search scroll requests do not accept a timeout but it looks like they inherit the timeout from the initial search request. This somewhat contradicts with the SQL API that accepts a request_timeout also for scroll requests (which seems reasonable for a request timeout). As a result, request_timeout is currently ignored in scroll requests using search hits (unlike aggregation results).

Since there is not a straightforward fix to make request_timeout behave a) as expected and b) consistent across requests there are two major options:

• deprecate request_timeout: the parameter is somewhat unique among the ES APIs (only EQL also provides it but has similar issues) and request timeouts should typically be handled on the client side (see Cancel search task on connection close #43332). This change would require to first ensure that the xDBC clients work accordingly.
• cancel the search/scroll task after request_timeout passed, ideally on the transport level.

[elasticmachine]

Pinging @elastic/es-ql (Team:QL)

[elasticsearchmachine]

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