Doris-Breakwater commented on issue #67676: URL: https://github.com/apache/doris/issues/67676#issuecomment-5587907359
Breakwater-GitHub-Analysis-Slot: slot_cc4de64f10ee ## Initial triage **Assessment:** high-confidence Nereids optimizer gap causing a performance regression; no result-correctness problem is demonstrated. The report is actionable: it has a minimal schema, exact SQL, an affected FE version, two useful control cases, observed scan predicates/tablet counts, a semantics-preserving workaround, and impact data. The issue currently has no labels, assignee, milestone, linked PR, or discussion. Candidate labels are `area/nereids`, `area/optimizer`, and `kind/performance`; `4.1.x-tbd` is also appropriate if maintainers want to track branch disposition. ### Verified source-level mechanism The three reported behaviors line up with distinct unnesting paths in `4.1.3-rc02`: 1. [`PROJECT_SUBQUERY_TO_APPLY`](https://github.com/apache/doris/blob/7126cf65d96ebc43fce0906f51e92c1a2ccf24a6/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java#L144-L184) constructs `ReplaceSubquery(..., true)`. For a projection expression such as `CASE WHEN EXISTS(...)`, [`visitExistsSubquery`](https://github.com/apache/doris/blob/7126cf65d96ebc43fce0906f51e92c1a2ccf24a6/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java#L628-L655) therefore creates a `MarkJoinSlotReference` and replaces the `EXISTS` result with `nvl(mark_slot, false)`. 2. [`ExistsApplyToJoin`](https://github.com/apache/doris/blob/7126cf65d96ebc43fce0906f51e92c1a2ccf24a6/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/ExistsApplyToJoin.java#L82-L107) converts the correlated apply to a `LEFT_SEMI_JOIN`, carries that optional mark slot, and puts the correlation equality into the join conditions. Because the mark slot is present, `LogicalJoin.isMarkJoin()` is true. 3. [`InferPredicates.visitLogicalJoin`](https://github.com/apache/doris/blob/7126cf65d96ebc43fce0906f51e92c1a2ccf24a6/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InferPredicates.java#L92-L137) returns immediately for every mark join. Consequently it never combines the left predicate `p.id = 'P1'` with the correlation equality `p.id = c.parent_id` to derive a right-child filter `c.parent_id = 'P1'`. Without a right-child `LogicalFilter`, the inner scan has no constant on its distribution key and static tablet pruning remains `4/4`. This also explains both controls: - A bare `WHERE EXISTS` does not need to output a mark value, so it becomes an ordinary left-semi join. The same `InferPredicates` method handles ordinary `LEFT_SEMI_JOIN` by inferring into both children, producing the inner constant and enabling `1/4` tablet pruning. - The projection-list scalar aggregate becomes a left-outer join, not a mark join. The left-outer branch explicitly infers predicates into the right child, so it is not affected by the mark-join early return. The same unconditional mark-join guard is still present on the upstream `branch-4.1` head and current `master` inspected on 2026-09-08, so there is no visible existing fix in those branches. I have not independently executed the SQL; this conclusion is based on the exact release source plus the mutually consistent plan evidence in the report. ### Fix direction and safety boundary The likely fix is to support **one-way, semantics-safe inference into the build/right side** for this correlated `EXISTS` mark-join shape, or to perform that inference while the node is still a `LogicalApply` and its `EXISTS` provenance is explicit. Removing the `isMarkJoin()` guard wholesale would be risky. A mark join preserves unmatched probe rows to produce a false/null mark, so inferring a right-side restriction back into the left side can remove rows incorrectly. `IN`/`NOT IN` mark joins also have three-valued/null-aware behavior. The implementation should therefore preserve mark semantics and narrowly allow deterministic outer/probe predicates to cross equality correlation predicates into the inner/build side; it should not enable unrestricted bidirectional inference for all mark joins. ### Missing evidence No additional information is required to accept and reproduce the optimizer gap. The following would make verification and performance scoping stronger: - Full `EXPLAIN VERBOSE` or `EXPLAIN SHAPE PLAN` output for Case A, Case B, and the workaround, rather than only the scan excerpts. - The exact FE build/commit identifier and relevant optimizer overrides (especially any disabled Nereids rules), to rule out packaging or session differences. - For the reported 38–60x production impact only: sanitized `EXPLAIN ANALYZE`/query profile, inner-table row counts, bucket/partition layout, and statistics freshness. These are not prerequisites for the minimal planner bug. ### Recommended maintainer next steps 1. Reproduce the supplied SQL on `4.1.3-rc02`, current `branch-4.1`, and `master`; confirm the logical plan contains a marked left-semi join and that no inferred filter appears on `CHILD_T`. 2. Add a plan regression asserting the right scan receives `parent_id = 'P1'` and prunes to one tablet, plus a result regression for the `CASE WHEN EXISTS` output. 3. Add safety coverage for `NOT EXISTS`, nullable correlation keys, `IN`/`NOT IN`, non-equality correlations, and multiple projection-list mark expressions before relaxing any mark-join inference guard. 4. If fixed on master, evaluate a 4.1 backport because the affected release and current 4.1 branch share the same guard. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
