What happens when using LIMIT/OFFSET in Spark without an explicit ORDER BY clause?

Does Spark Implicitly Sort for LIMIT/OFFSET?

No. Spark does not apply a global sort when LIMIT or OFFSET are used without an explicit ORDER BY clause. Instead, Spark treats these operations as a request for an arbitrary subset of the data.

Likely Execution Behavior

When no sort order is specified, Spark typically employs a CollectLimit operator. It gathers rows from available partitions in an arbitrary order and stops as soon as the limit is reached. OFFSET simply skips the first N rows of this non-deterministic stream.

Because there is no implicit sort, the following behaviors occur:

• Nondeterminism: Results are not guaranteed to be consistent across different runs, even on the same dataset, as partition processing order can vary.
• No Global Shuffle: Unlike a sorted limit, a plain LIMIT does not trigger a global shuffle to organize the data.
• Low Resource Overhead: Since no sorting occurs, there is no risk of memory pressure or OOM errors caused by sort-key skew.

Performance and Spark UI Observations

If you are monitoring the Spark UI, you can distinguish between a sorted limit and an unsorted limit by looking at the stages and physical plan:

Metric	Without ORDER BY	With ORDER BY
Physical Plan	CollectLimit	TakeOrderedAndProject / Sort
Shuffle Volume	Minimal to zero	Significant (Exchange hash/range partitioning)
Stage Duration	Short; stops upon limit	Longer; must sort entire dataset first

Verification Steps

To confirm if your specific version of Spark is performing an implicit sort, use the following methods:

1. Check the Plan: Run EXPLAIN on your query. If you do not see an Exchange (shuffle) or a Sort operator before the CollectLimit, no implicit sort is happening.
2. Test Determinism: Execute the same LIMIT/OFFSET query twice on a static dataset. If the returned rows differ, the operation is nondeterministic and unsorted.
3. UI Audit: Check the SQL tab in the Spark UI to verify the absence of a Sort operator in the physical plan.

Assumption: This behavior applies to Spark 3.4+ (where OFFSET is natively supported). In earlier versions, OFFSET is not a valid SQL keyword and will result in a parsing error.

Diagnostic Detail Needed: To provide a more tailored performance analysis, please specify if your data is stored in a format with inherent ordering (like certain sorted Delta tables) or if you are using a custom shuffle partition configuration.