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.