SQL: Investigate handling of shard failures in SQL requests

[imotov]

SQL users are expecting to receive all results. We need to make sure that we handle the shard failures in SQL request properly. Setting allow_partial_search_results to false and adding a test might be all that is required.

[elasticmachine]

Pinging @elastic/es-search-aggs

[imotov]

After discussion with @costin it turned out that we are already setting allow_partial_search_results to false globally.