[ES|QL] Remove the implicit `LIMIT` command added to each subquery (`UnionAll` branch)

[fang-xing-esql]

This is a follow up to #135744, removing the implicit LIMIT command added to each subquery potentially lifts the restrictions on:

1. Subqueries followed by LOOKUP JOIN under CCS context, as LOOKUP JOIN may not work with remote index patterns + LIMIT before it.
2. Subqueries followed by INLINE STATS, as INLINE STATS may not work when there is a LIMIT command before it.

[elasticsearchmachine]

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