SQL: a more compact way of translating the queries that have `AND` statements

[elasticmachine]

Original comment by @astefan:

At the moment, a query like SELECT name,page_count,release_date AS DATE,my_boolean,SCORE() FROM library WHERE name='NULL' AND release_date>='1800-06-02' AND release_date<='2011-06-02' AND price>10.0000 AND match(name, 'dune') AND match(author, 'dan') AND match(author, 'frank') ORDER BY SCORE() (a series of simple AND statements) gets translated to something like LINK REDACTED, meaning a bunch of nested bool statements which basically have one filter statement per bool, except the very first condition after WHERE.

That long list of nested bools could have been written in a more compact way like LINK REDACTED. For now, I don't know if performance-wise there is any difference, but I'll update this issue with more details when I do find out. Even if there would be comparable from performance point of view, the second approach is much more compact/readable.

[astefan]

Thanks to @jpountz for providing more input regarding this issue from Lucene point of view.
Performance wise, the two approaches should be identical. But from the point of view of caching the queries, a bool with nested bool in it has a slight advantage in that individual bool queries are cached individually, thus if the same bool is "reused" in a different query, it can be used from cache.

The other approach - a single big bool with an array of filter statements - will theoretically be the single query to cache. If the filters in it change just slightly in a new "similar" query, the big bool cached query will not be reused.

I am still in favor of a more visually pleasant Elasticsearch query translation, though. For users migrating from SQL world to Elasticsearch, the translate feature does help, imo, make the migration easier.

[costin]

Fixed by #30267