SQL: clients REST payload format control simplification

[bpintea]

Currently (since #48261), the server will respond with CBOR to the JDBC,ODBC,CLI clients when the request object field binary_format is set to true, but also if absent. It does so taking precedence over the Accept HTTP header and the format query string/URL parameter.
(As a side note, currently both CLI and JDBC always set Accept: application/json, even along with "binary_format": true, which seems contradictory.)

If binary_format is present and false however, the choice is deferred to the other content format methods.

Since (1) the format type is decided based on the client's type and (2) there is already a clear default for the above mentioned clients, it might make sense to simply drop the binary_format parameter altogether and only base the decision on the client type, which is always provided by these clients. For the troubleshooting purposes when JSON would be desired, the clients could make use of the already existing controls (HTTP header or parameter).

This would simplify the interface and potentially keep the backwards-compatibility effort around the SQL API lower (besides being more HTTP standard friendly).

This assumes however that having an explicit format control embedded into the request object and prevent HTTP-based controls from interfering does not bring a clear benefit -- which might not be all true?

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[elasticsearchmachine]

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

[elasticsearchmachine]

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