Combine multiple LIMITs

[ranma42]

It is currently possible to end up with multiple consecutive LIMIT in a query.
This is especially likely when combined with other query optimizations (such as those that drop unneeded ORDER BY), as mentioned in #34831

Removing them avoids nesting queries and in some cases makes it possible to use a simpler join instead of an apply.

[ahmednfwela]

I realize this has already been merged, but wouldn't it make sense to use the latest assigned limit, instead of calculating the least limit?

so q.Take(10).Take(90) should end up with Limit 90

[ranma42]

@ahmednfwela the purpose of this PR is not to change the behavior of Take (in fact, if it did, it would have been considered buggy/wrong 😅 ), but just to make it translate to (slightly) simpler SQL queries.

Regarding the semantics of Take, it is defined in https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.take?view=net-9.0
Note that LINQ operators basically transform the incoming enumerable into a new one in a compositional way (only inspecting the sequence of elements).
The x.Take(10) expression restricts the sequence x to the first 10 elements.
The y.Take(90) restricts the sequence to the first 90 elements and has no way (or reason) to emit other elements besides those from y.