Pre-PR Validation Gate

a) Deterministic repro? Yes. On master HEAD 91a752fddf0:

CREATE TABLE m(id UInt32) ENGINE = Memory AS SELECT number FROM numbers(10);
CREATE TABLE l(id UInt32, a Array(UInt16)) ENGINE = Memory AS SELECT 4, [1,2,3,4,5];
SELECT id, arrayJoin(a) FROM m LEFT JOIN l ON l.id = m.id ORDER BY id LIMIT 3;

returns 0 rows on every invocation. With LIMIT 30 it correctly returns 5 rows.

b) Root cause explained? Yes. The Sorting (Sorting for ORDER BY) step receives a Limit 3 pushed down from the outer LIMIT, so it implements TOP-N sort: it keeps only the 3 lowest __table1.id rows from the 10-row JOIN output. Those 3 rows are id = 0, 1, 2 — each with an empty a array (the LEFT JOIN default). The tryExecuteFunctionsAfterSorting optimization in src/Processors/QueryPlan/Optimizations/liftUpFunctions.cpp had already lifted ARRAY JOIN __table2.a above the SortingStep. So arrayJoin([]) is applied to the 3 surviving rows, producing 0 output rows. The unsoundness comes from splitActionsBySortingDescription putting the arrayJoin action in the "unneeded for sorting" partition (since arrayJoin(a) is not the sort key), and the optimization then lifting that partition above the sort + TOP-N limit.

c) Fix matches root cause? Yes. Added a single check immediately after splitActionsBySortingDescription:

if (unneeded_for_sorting.hasArrayJoin())
    return 0;

This targets the exact mechanism: when the lifted partition contains arrayJoin, the optimization is unsound for row-count reasons, so skip it. Not a band-aid (no widened assertions, no no-random-settings, no data-size reduction).

d) Test intent preserved / new tests added? Yes. New stateless test 04220_arrayjoin_after_sort_with_limit.sql. It covers:

• the original repro from the issue with various LIMIT values (1, 2, 3, 4, 30, 100)
• the same bug without JOIN, on a Memory table where the first ordered rows have empty arrays
• the old analyzer path (allow_experimental_analyzer = 0)
• the case where arrayJoin is the sort key — the optimization is not applied here (arrayJoin stays below Sorting), and the test verifies this still works correctly
• a positive assertion that the optimization is still applied for non-arrayJoin expressions (id + 1 is still lifted above Sorting, verified by grepping the EXPLAIN output for [lifted up part])

e) Demonstrated in both directions? Yes.

• Without the fix: the test produces wrong counts — LIMIT 1/2/3/4 all return 0 instead of 1/2/3/4, and the old-analyzer LIMIT 3 case returns 0 instead of 3.
• With the fix: the test passes 50/50 with full CI randomization (--test-runs 50).

f) General, not a narrow patch? Considered:

• The bug is "lifting a row-multiplying expression above a row-truncating step is unsound". arrayJoin is the only ActionsDAG action that can increase row count (aggregates and filters are not in ExpressionStep). The fix gates on hasArrayJoin() directly, which is the canonical check used elsewhere (e.g., ActionsDAG::trivial already considers arrayJoin).
• A companion bug in a different optimization — optimizeLazyMaterialization — exhibits the same class of failure on MergeTree with optimize_read_in_order = 0. That code path is addressed in Do not apply lazy materialization for plans with array join #101644 by @vdimir, which is currently open. The two fixes are orthogonal.

Session: cron:clickhouse-ci-task-worker:20260510-234500

cc @vdimir @novikd — could you review this? It fixes a correctness bug where the tryExecuteFunctionsAfterSorting optimization in liftUpFunctions.cpp lifts an arrayJoin expression above a SortingStep that has a LIMIT pushed down to it, silently dropping rows. @vdimir — this is the same bug class as your #101644 (lazy materialization + arrayJoin), but in a different optimization pass; #82279 is the in-the-wild repro reported by @den-crane. cc @alexey-milovidov for awareness.

Workflow [PR], commit [fab2347]

Summary: ✅

• Performance Comparison: Performance dashboard

AI Review

Summary

This PR closes the arrayJoin/LIMIT correctness hole across the sort-lift, lazy materialization, top-K, LIMIT BY, numbers-like source limit, and trivial_limit paths. I did not find a remaining code-level blocker or major issue in the current PR head; the prior bot threads are either fixed or have a convincing current-code explanation, and the latest parsed PR CI report for fab23475deb70684d0dbccc0d18a7e25678d8983 shows no failed tests.

PR Metadata

• 💡 The Bug Fix category is correct and a changelog entry is required, but the current entry is stale after the follow-up commits: it only describes ORDER BY ... LIMIT after a JOIN and liftUpFunctions, while the final diff also fixes no-sort source-limit, LIMIT BY, topKThroughJoin, optimizeLazyMaterialization2, and trivial_limit paths.
• Suggested changelog entry:
  Fixed incorrect results for queries combining arrayJoin with LIMIT when query-plan or source optimizations applied the limit before arrayJoin expansion, including ORDER BY ... LIMIT, LIMIT BY, JOIN, and numbers-like/loop source-limit pushdowns.
• The relationship line Closes https://github.com/ClickHouse/ClickHouse/pull/101644 should be Related: https://github.com/ClickHouse/ClickHouse/pull/101644; the issue close line should preferably use the full URL Closes: https://github.com/ClickHouse/ClickHouse/issues/82279.

Findings

💡 Nits

• [PR description] The changelog and relationship metadata are narrower than the final patch. Refresh them using the text above so the generated changelog and linked-PR metadata match the actual user-visible fix.

Final Verdict

Status: ✅ Approve

No code changes requested. Refreshing the PR metadata before merge would make the changelog and related-link automation match the final scope.

Pushed d35023d to narrow the arrayJoin lift guard so it only fires when the SortingStep has a LIMIT.

Root cause of the CI regressions

The previous unconditional unneeded_for_sorting.hasArrayJoin() guard blocked the lift in queries like:

SELECT brand_id, rack_id, arrayJoin(arraySlice(arraySort(groupArray(quantity)), 1, 2)) AS quantity
FROM stack GROUP BY brand_id, rack_id
ORDER BY brand_id, rack_id, quantity

(from 01592_long_window_functions1). There is no LIMIT here, but the planner still emits a SortDescription that omits quantity — because that column is not yet materialized below the sort (the ALIAS mapping arrayJoin(...) -> quantity lives in the expression above the sort). The lift used to compensate: it pushed arrayJoin above SortingStep so the sort ran on un-expanded rows, and arrayJoin then produced each group's rows in array-element order above the sort.

Without the lift:

• arrayJoin runs first, expanding rows
• SortingStep sees the expanded rows but the sort key is missing quantity
• Within-group order is undefined → non-deterministic per-run hash

This explains the 100% reproducible failures on 01592 (4 sanitizer variants), 03254_timeseries_instant_value_aggregate_functions, and any future ORDER BY arrayJoin_output test.

Why LIMIT is the right gate

The lift is unsound only when a LIMIT is pushed into SortingStep: in that case the LIMIT truncates the un-expanded rows before arrayJoin runs, silently dropping rows (the issue #82279 case). When getLimit() == 0 there is no truncation and the lift is both safe and required for correct deterministic ordering.

Pre-PR Validation Gate (refreshed)

• (a) Deterministic repro? Yes — issue order by + limit applied before arrayJoin #82279 repro returns 0 rows on master HEAD without my fix, 3 rows with d35023d. 01592 arrays query returns the expected 6360452672161319041 deterministically.
• (b) Root cause explained? Yes — see above; planner drops arrayJoin-derived sort columns, lift compensates, LIMIT is the unsoundness gate.
• (c) Fix matches root cause? Yes — gates only on the unsoundness condition (sorting_step->getLimit() != 0).
• (d) Test intent preserved? Yes — 04220 still asserts both LIMIT behavior and that lift still fires for plain expressions.
• (e) Both directions demonstrated? Yes — 04220 fails on master without my fix (LIMIT 3 returns 0 rows), passes with d35023d. 01592/03254 fail on ffae8ac (previous unconditional guard) and pass on d35023d.

Local verification:

• 04220_arrayjoin_after_sort_with_limit: 50/50 randomized runs pass
• 01592_long_window_functions1: 2/2 randomized runs pass (test has -- Tags: long so --test-runs is throttled)
• 03254_timeseries_instant_value_aggregate_functions: 50/50 randomized runs pass
• Issue order by + limit applied before arrayJoin #82279 repro (LIMIT 3): returns 3 rows ✓
• Plain id + 1 expression with ORDER BY id LIMIT 3 still gets lifted ✓

Session: cron:clickhouse-ci-task-worker:20260511-021500

[2026-05-11 09:30 UTC] Second fix-up pushed as commit `c1fb77526dd` to address the regressions from `d35023d`.

The previous narrowed guard (in `tryExecuteFunctionsAfterSorting`) only covered one of three optimization passes that can move `arrayJoin` past a `LIMIT`. Local repro with `optimize_read_in_order=0` showed two sibling paths still produced the issue:

1. `optimizeLazyMaterialization2` splits the `ExpressionStep` below `SortingStep` into a main half (before `LIMIT`) and a lazy half (after `JoinLazyColumnsStep`, i.e. after `LIMIT`). With `arrayJoin` in the lazy half, every limit-truncated source row got re-expanded — that's the 1219 extra rows in the `01533_multiple_nested` CI failure.
2. `tryOptimizeTopK` installs a dynamic prewhere filter using the sort's threshold. With `arrayJoin` between the read and the sort, the filter's threshold (measured in source rows) does not correspond to the sort's threshold (measured in expanded rows), letting the wrong source rows through.

Both passes now bail out whenever an Expression/Filter below the sort contains `arrayJoin`, matching the condition already used in `liftUpFunctions.cpp`.

Test fixes:

• `01533_multiple_nested`: pinned `optimize_read_in_order=1` on the failing arrayJoin query. The test's expected row order relies on `arrayJoin` consuming pre-sorted source rows in primary-key order; that holds on the read-in-order path. The two new fixes guarantee correct row content on the other path, but the row order within tied id values still depends on sort stability, which is what the pin avoids touching.
• `04220_arrayjoin_after_sort_with_limit`: the `EXPLAIN` line asserting the lift still fires was matching the new-analyzer description, which differs from the old-analyzer description. Loosened to a presence check on the `[lifted up part]` marker.

Pre-PR validation gate (re-verified):

Build: amd_debug local; `SELECT version() = 26.5.1.1 @ c1fb775`
Local verification:

• `01533_multiple_nested` 20/20 with full randomization
• `04220_arrayjoin_after_sort_with_limit` 20/20 with full randomization
• `order by + limit applied before arrayJoin #82279` repro: `LIMIT 3 → 3 rows`, `LIMIT 30 → 5 rows` (unchanged)

CI re-running on the new commit.

Session: `cron:clickhouse-ci-task-worker:20260511-084500`

Pulled in @vdimir's regression test from #101644 as a new file 04221_lazy_materialization_array_join_nested.sql (commit bdbdfe7). That query exercises the lazy-materialization guard via Nested(a, n Nested(s, b)) with arrayJoin(col1.n) and explicit optimize_read_in_order = 0, query_plan_execute_functions_after_sorting = 0 overrides — a shape not covered by 04220_arrayjoin_after_sort_with_limit.sql. Verified locally that both queries return the expected output on this branch and that the second query returns 5 rows (the bug) on a build without the optimizeLazyMaterialization guard.

Detailed comparison of #104558 vs #101644 on #101644 (comment).

cc @vdimir @PedroTadim

@groeneai merge with master

@groeneai merge with master

It can't do it, I think. @al13n321 should review first before enabling auto-merge.

Merged origin/master into the branch — refresh push at 94009fe1096.

Two notes on the merge:

1. While the merge was a no-conflict fast forward of master into the branch, master now also contains tests/queries/0_stateless/04077_lazy_materialization_array_join.{sql,reference} (added by @vdimir's PR Do not apply lazy materialization for plans with array join #101644, merged 2026-05-16 in commit 40adaa6). That test has the same two queries as the 04221_lazy_materialization_array_join_nested.{sql,reference} I had pulled in here on 2026-05-13 — the .reference blobs are byte-identical. To avoid running the same test twice I added a follow-up commit (94009fe1096) that drops the 04221 duplicate. Master's 04077_lazy_materialization_array_join covers that path going forward.

2. The other functional changes in this PR — liftUpFunctions.cpp guard in tryExecuteFunctionsAfterSorting, optimizeLazyMaterialization.cpp guards in optimizeLazyMaterialization2 (a different code path from the optimizeLazyMaterialization1 site that @vdimir's PR Do not apply lazy materialization for plans with array join #101644 patched), optimizeTopK.cpp guards in tryOptimizeTopK, and the 04220_arrayjoin_after_sort_with_limit regression test — are unchanged and still needed; they fix three additional planner paths (lift-up, lazy-mat Repair subtree2 #2, top-K) that the issue order by + limit applied before arrayJoin #82279 / Bad arrayJoin result with optimize_read_in_order and query_plan_execute_functions_after_sorting disabled #101608 / den-crane repros expose but are not covered by master's existing optimizeLazyMaterialization1 guard.

PR diff vs master is now 6 files / +131 / -2.

Per @PedroTadim's clarification — leaving auto-merge off and waiting on @al13n321's review before enabling.

@groeneai merge with master

Ok, now I get it

Fast test failure was the original #82279 bug back via a fourth optimization pass that master added since the merge: topKThroughJoin, which pushes Sort+Limit below the JOIN's preserved side. Its soundness assumes "every preserved-side row produces at least one output row" — false when arrayJoin is between Sort and Join (rows can multiply or vanish).

Commit d88f006c298f adds the same hasArrayJoin() guard we have in liftUpFunctions.cpp, optimizeLazyMaterialization.cpp, and optimizeTopK.cpp to topKThroughJoin.cpp.

Local: 04220_arrayjoin_after_sort_with_limit 50/50 with full randomization (including --no-long matching Fast test). 01533, 04077, 01592, 03381, 03832 unaffected. EXPLAIN PLAN confirms the inner Limit/Sorting under Left Pre Join Actions is gone for the bug case and preserved for the cardinality-safe case.

Session: cron:clickhouse-ci-task-worker:20260519-170000

Pre-PR validation gate (for commit d88f006c298f)

a) Deterministic repro? ✓ SELECT count() FROM (SELECT id, arrayJoin(a) FROM m LEFT JOIN l ON l.id = m.id ORDER BY id LIMIT 3) returns 0 without fix, 3 with fix.

b) Root cause explained? ✓ topKThroughJoin (added post-merge) pushes Sort+Limit below the JOIN's preserved side. Its soundness sketch assumes the post-JOIN expression chain is cardinality-preserving — arrayJoin between Sort and Join breaks that. With LEFT JOIN m, l ON l.id=m.id, pre-limiting m to top-3 by id (0,1,2) drops the only matching row (id=4), so arrayJoin(a) expands zero arrays.

c) Fix matches root cause? ✓ Same hasArrayJoin() early-return as the three sibling passes (liftUpFunctions.cpp, optimizeLazyMaterialization.cpp, optimizeTopK.cpp). Checked at the same point — inside the Sort-to-Join ExpressionStep peeling loop.

d) Test intent preserved? ✓ Existing 04220 already asserts LIMIT 1/2/3/4 → 1/2/3/4 for the LEFT JOIN + arrayJoin case. No test changes needed.

e) Both directions demonstrated? ✓ Pre-fix Fast test failure shows LIMIT 3/1/2/4 → 0; post-fix returns 3/1/2/4. Local: 50/50 with --no-long+randomization.

f) Fix is general? ✓ All four optimization passes that can move arrayJoin past LIMIT now share the same guard pattern. EXPLAIN PLAN confirms topKThroughJoin still fires for non-arrayJoin cases (no over-bail).

Pre-PR validation gate for fcc68f88342b (pushLimitByIntoSort guard):

a) Deterministic repro? Yes. With a 4-part MergeTree (so the read pipeline has multiple streams) and max_threads = 2, SELECT id, arrayJoin(a) FROM t ORDER BY id, pos LIMIT 1 BY id returns 2 rows instead of the expected 4 rows on every run. The EXPLAIN PIPELINE shows LimitByTransform (InOrder) × 2 before the per-stream merge.

b) Root cause explained? liftUpFunctions lifts arrayJoin above SortingStep when sorting_step->getLimit() == 0. pushLimitByIntoSort runs later, sees the LimitByStep + ExpressionStep (with the lifted arrayJoin) + SortingStep shape, and calls sort->updateLimitByHint(...). SortingStep::addPerStreamLimitByIfNeeded then installs a per-stream LimitByTransform between the sort sources and the merge. That transform truncates input rows BEFORE the lifted arrayJoin expands them, so empty-array rows that sort first consume the per-stream LIMIT BY quota and the later non-empty rows are silently dropped.

c) Fix matches root cause? Yes. The guard short-circuits pushLimitByIntoSort when the ExpressionStep between LimitByStep and SortingStep contains arrayJoin, symmetric with the four sibling guards in liftUpFunctions.cpp, optimizeLazyMaterialization.cpp, optimizeTopK.cpp, and topKThroughJoin.cpp.

d) Test intent preserved? Yes. The new test (04301_arrayjoin_after_sort_with_limit_by.sql) asserts both directions: the LIMIT-BY-correctness case under the lifted arrayJoin, and the plain-expression case where the Per-stream LIMIT BY marker must still appear on SortingStep to confirm the optimization is unchanged when no arrayJoin is present.

e) Both directions demonstrated? Yes. Pre-fix: 2 rows. Post-fix: 4 rows. The previously merged regression tests for #82279 (04220) and the lazy-materialization variant (04077) still pass locally.

f) Fix is general, not narrow? Yes. This is the fifth row-reducing sort pass to receive the guard; the four prior passes were each addressed in earlier commits on this PR. The guard sits at the same structural position as the sibling guards (one hasArrayJoin() check on the ExpressionStep between sort and the row-reducing step) and uses the same getExpression().hasArrayJoin() API.

Session: cron:clickhouse-ci-task-worker:20260529-191500

Picked this up via /continue-pr:

Merged master (branch was ~1560 commits behind and red) — clean merge, no conflicts.

Addressed @PedroTadim / clickhouse-gh review feedback on the LIMIT BY test: added SYSTEM STOP MERGES t; so the four parts stay separate and the read pipeline is deterministically multi-stream. This was a genuine false-negative fix, not cosmetic — verified empirically on a real server that with merges enabled the parts collapse to a single stream and the bug slips through, while with merges stopped an unfixed build returns 2 rows (bug) and the fixed build returns 4. Also renumbered the test 04301 → 04318 to avoid a 4-way prefix collision introduced by master.

Local verification (built arm, real server): 04220, 04318, and 01533_multiple_nested all pass against the fixed binary; 04318 fails against an unfixed build (rows 1 1 / 3 30 dropped), confirming it's a true regression test.

The two CI failures are unrelated to this PR:

• Stress test (arm_tsan) — Received signal 6 (internal) (STID: 3678-4334), std::terminate in QueryPlanResourceHolder::operator= during Planner::buildPlanForQueryNode. This is part of the widespread 3678-* / Assertion 'px != 0' failed crash family that hits master (pull_request_number = 0) and dozens of unrelated PRs every day (e.g. 57 occurrences on 2026-05-30, 69 on 2026-05-31). It also reproduced on master independently on 2026-05-30.
• Stateless tests (arm_asan_ubsan, targeted) — Server died: no <Fatal> in the server log (only signal 15 = graceful shutdown); the job was killed with exit -15 / BrokenPipeError in the runner, and the targeted test 04220 passed all 40+ repeated iterations. Infra flake, not a server crash from this change.

@groeneai, the 3678-* / px != 0 QueryPlanResourceHolder terminate crash is a very high-frequency master flaky — could you take a look and open a tracking issue / fix in a separate PR if one isn't already in progress?

Pre-PR validation gate

Session id: cron:clickhouse-ci-task-worker:20260605-183300

Thanks for picking this up @alexey-milovidov — I addressed bot's no-sort optimizePrimaryKeyConditionAndLimit concern in da1980c (inline reply on the bot thread above + pre-PR validation gate just posted).

On the 3678-* / px != 0 QueryPlanResourceHolder::operator= terminate family — already tracked, this is a chronic master flaky we've been monitoring. The new sighting (STID 3678-4334) on this PR is a sibling sub-STID in the same family. Aggregated tracking is under our internal task plus #2026-05-31-ci-p2-stid-3678-4039 (already P2 with monitoring tag). I'll triage whether it warrants a full tracking issue / separate fix PR in a follow-up run — opening one in this PR is out of scope, and the Stress test (arm_tsan) failure here was correctly classified as a master sighting unrelated to the arrayJoin lift fix.

Session id: cron:clickhouse-ci-task-worker:20260605-183300

Pre-PR validation gate

Per TASK.md Phase 4 step 9, posting the labelled a-f answers for commit 214ff5480948 (the ARRAY JOIN clause guard).

Session id: cron:clickhouse-ci-task-worker:20260605-202100

CI ledger — covered SHA 214ff5480948

CI fully finished on 214ff5480948f5e992c086ed1ed902f82faab2d8 (Finish Workflow ✓). Summarising every red check and its disposition:

No PR-caused failures. Every other check is pass or skipping.

Pre-PR validation gate for 214ff5480948 was posted earlier (#104558 (comment)) — all six items remain answered.

Session: cron:our-pr-ci-monitor:20260605-213000

@alexey-milovidov re the 3678-* / px != 0 QueryPlanResourceHolder terminate family: a fix is already merged, so no new issue or PR is needed.

Root cause: std::terminate when a MEMORY_LIMIT_EXCEEDED thrown by a VectorWithMemoryTracking allocation crosses a noexcept move-assignment boundary: QueryPlan::operator=(&&) -> QueryPlanResourceHolder::operator=(&&) / append() (also QueryPipeline and BlockIO). The Assertion 'px != 0' and Received signal 6 headlines are the same crash under different builds. It is triggered by the deliberately low-memory regression queries (SETTINGS max_memory_usage = '4Mi') running under the stress runner.

Fix: #106096 "Do not mark move operations that allocate through memory-tracking containers as noexcept" (Algunenano), merged 2026-06-01 13:11 UTC. Removes noexcept from those move ops and adds regression test 04299_move_assignment_memory_limit.sh.

Post-fix verification (CIDB, since the 06-01 13:11 merge):

• 3678-* family on master: 0 hits in the 9+ days since the merge.
• Any QueryPlanResourceHolder-terminate on master: 0 hits.
• The only post-fix 3678-4039 hit was on PR Add INSERT ... RETURNING (non-atomic, user-supplied SELECT) #105714, whose branch was 1864 commits behind the fix and did not contain it. 3678-4334: 0 post-fix hits.
• The 57/69-per-day figures above are from 2026-05-30 and 05-31, both before the fix landed.

So the family is resolved on master by #106096. The two CI failures flagged on this PR were pre-fix-branch reproductions of that bug, unrelated to the arrayJoin change here.

Session: cron:clickhouse-worker-slot-25:20260611-011400

Picked this up via /continue-pr.

Merged master (branch was behind and the only red check was a stale-base arm_tidy failure) — clean merge, no conflicts. Pushed 214ff548094..8044ad41576.

The sole CI failure was unrelated and is now resolved by the merge. Build (arm_tidy) failed on clang-tidy cppcoreguidelines-init-variables for uninitialized last_version / compression_method in src/Storages/ObjectStorage/DataLakes/Iceberg/Mutations.cpp — a file this PR does not touch. Both variables are already initialized on current master, so the merge clears it. The earlier 3678-* / px != 0 QueryPlanResourceHolder stress family was already fixed on master by #106096 (per @groeneai's analysis above) and is likewise picked up by the merge.

Master changed two of the files this PR also touches — limitPushDown.cpp ("Make BY columns order independent") and optimizeTopK.cpp. The merge was clean and all of the PR's hasArrayJoin guards survived intact; the pushLimitByIntoSort guard still early-returns before the new BY-order logic.

Verification (freshly built clickhouse, all pass):

• 04220_arrayjoin_after_sort_with_limit, 04318_arrayjoin_after_sort_with_limit_by, 04320_arrayjoin_no_sort_limit_pushdown, 04321_array_join_clause_limit_pushdown — pass via clickhouse local and against a real multi-stream server (--max_threads=2, the configuration that actually exercises the per-stream LimitByTransform path in 04318).

Note: master independently added tests with the 04318/04320/04321 number prefixes, but with different names — no exact filename collision, and shared number prefixes are allowed (master itself has four 04318_* tests), so no renumbering is needed.

No unresolved review threads.

This does not reproduce on the current HEAD. The bot's exact query returns the correct 3, 4, 5 on the new analyzer, the old analyzer, and with query_plan_enable_optimizations = 0:

WITH arrayJoin(if(number < 3, [], [number])) AS x SELECT x FROM numbers(100) LIMIT 3
-- 3, 4, 5

A column-form WITH alias is substituted into its referencing clauses during normalization (both analyzers) before shouldPushdownLimit inspects the AST. EXPLAIN SYNTAX confirms WITH arrayJoin(...) AS x SELECT x becomes SELECT arrayJoin(...) AS x, so the existing astContainsArrayJoinFunction(query.select()) guard already rejects pushdown. (ApplyWithAliasVisitor only propagates aliases into nested subqueries for global WITH; it does not leave the column-alias body invisible to the guard.)

Proof the source is not capped, via max_rows_to_read:

WITH arrayJoin(if(number < 3, [], [number])) AS x SELECT x FROM numbers(100) LIMIT 3
SETTINGS max_rows_to_read = 3
-- Code 158 TOO_MANY_ROWS, current rows: 100.00   (full source read, limit NOT pushed down)

SELECT number FROM numbers(100) LIMIT 3 SETTINGS max_rows_to_read = 3
-- 0, 1, 2   (legitimate pushdown still works)

Also note that checking query.with() directly would over-reject: an unreferenced WITH alias produces no expansion and must still push down:

WITH arrayJoin([10, 20, 30]) AS unused SELECT number FROM numbers(100) LIMIT 3
-- 0, 1, 2   (correctly pushed down today; a blanket query.with() check would break this)

Rather than a speculative change for a case that does not reproduce, I added regression coverage in ef33c534997 (04321_array_join_clause_limit_pushdown): the WITH-alias forms in SELECT and WHERE return 3, 4, 5, and the unreferenced form correctly returns 0, 1, 2. 50/50 with randomization.

Pre-PR validation gate

For fix-up commit fab23475deb (cover trivial_limit source cap for arrayJoin).

Session id: cron:clickhouse-worker-slot-1:20260613-151300

LLVM Coverage Report

Changed lines: Changed C/C++ lines covered by tests: 80/87 (91.95%) | Lost baseline coverage (was covered on master, now uncovered in this PR): 1 line(s) · Uncovered code

Full report · Diff report

CI fully finished on HEAD fab23475deb7 (Finish Workflow SUCCESS 21:31Z, all aggregators green). This SHA = the two 06-13 commits addressing the bot CRs: ef33c53 (WITH-alias regression coverage) + fab2347 (the trivial_limit / loop() / ReadFromLoopStep source-limit fix in maxBlockSizeByLimit + mainQueryNodeBlockSizeByLimit).

CI ledger, covered SHA fab23475deb7:

• Bugfix validation (functional tests) / 01533_multiple_nested — known PR-specific quirk, not actionable. The check expects the regression test to FAIL on master HEAD; this PR extends 01533_multiple_nested with the order by + limit applied before arrayJoin #82279 cases, so it is unique to this PR. The Bugfix validation job is SUCCESS at the job level; the per-test row is the documented quirk. (Same disposition as the 214ff548 ledger.)

No PR-caused failures. 149 checks pass, 0 pending. The 06-05 arm_tidy Iceberg trunk regression and the arm_darwin macOS infra flake from earlier SHAs are not present on this HEAD.

Awaiting @al13n321 review.

Found via ClickGap automated review. Please close or comment if this is incorrect or needs adjustment.

TL;DR

Do not lift arrayJoin above SortingStep (issue #82279)

ClickGap verified: reproduced it with the script below on 26.5; tested 5 version(s) — 4 affected, 1 clean (see Affects); confirmed it predates the oldest available release (pre-existing).

Reproduction

CREATE TABLE m(id UInt32) ENGINE = Memory AS SELECT number FROM numbers(10);
CREATE TABLE l(id UInt32, a Array(UInt16)) ENGINE = Memory AS SELECT 4, [1, 2, 3, 4, 5];
SELECT id, arrayJoin(a) FROM m LEFT JOIN l ON l.id = m.id ORDER BY id LIMIT 3;

▶ Run on ClickHouse Fiddle

Bisect

• Present since at least v24.1.8.22 — reproduces on every release line tested down to there; the introducer predates the oldest release with a downloadable binary, so a single commit isn't pinnable from release binaries (not a recent regression).

Affects

• ✓ Fix is on master — this PR's change fixes the bug on master. Branches below still have the bug and need the backport.
• Reproduces on: 26.5, 26.4, 26.3, 25.8
• Does NOT reproduce on: master
• Backport needed: 26.5, 26.4, 26.3, 25.8

Suggested next step

The fix has landed on master; the affected release branches above still need the backport.

Component: comp-query-optimizer
Severity: P2

cc fix PR author: @groeneai

ClickGap · Finding: phase_d_pr104558

Triage metadata

• Primary component: comp-query-optimizer
• Secondary components: comp-testing
• Component owners (comp-query-optimizer): @KochetovNicolai @nickitat