Doris-Breakwater commented on issue #69490: URL: https://github.com/apache/doris/issues/69490#issuecomment-6355663122
Breakwater-GitHub-Analysis-Slot: slot_98b29e2af46f **Initial assessment:** The rule-level concern is valid: `EliminateLimitUnderApply` checks correlation but does not itself check limit, offset, or subquery type. However, the three supplied SQL examples do **not yet establish an end-to-end wrong-result bug on current master**, because analysis has protections before this rewrite. Please keep this open for reproduction/version clarification rather than infer the runtime behavior from the rule alone. **Verified source evidence** I checked upstream master at `cc49069836975cd3a37bcdc9ba3e35a1fca717d3` (2026-10-23), cross-checking the relevant files against the local checkout. This was a read-only source investigation; I did not execute the SQL or run regression tests. [SubExprAnalyzer](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java#L85-L135) predicts the following behavior for the supplied top-level forms; these are source-derived expectations, not measured results: | Case | Handling before the cited rewrite | | --- | --- | | (1) Correlated `EXISTS … LIMIT 1 OFFSET 1` | `visitExistsSubquery` rejects a correlated top-level limit with nonzero offset, with `Unsupported correlated subquery with a LIMIT clause with offset > 0`. | | (2) Correlated `EXISTS … LIMIT 0` | `rootIsLimitZero()` folds the expression to false. The corresponding `NOT EXISTS` folds to true. | | (3) Correlated `IN (… ORDER BY … LIMIT 1)` | `visitInSubquery` invokes `checkRootIsLimit`, which rejects a correlated top-level `LogicalLimit` with `Unsupported correlated subquery with a LIMIT clause`. `NOT IN` uses the same check. | These checks precede `SubqueryToApply`; the elimination batch runs later during subquery unnesting, before decorrelation and Apply-to-Join conversion. See [Analyzer](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Analyzer.java) and [Rewriter](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/executor/Rewriter.java#L450-L485). There is also directly relevant public history: [PR #24630](https://github.com/apache/doris/pull/24630), merged in 2023, added the `EXISTS LIMIT 0` folding and correlated-offset rejection. The current [sub_query_correlated regression suite](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy#L286-L318) still covers both behaviors; its checked-in expected output is empty for correlated `EXISTS LIMIT 0`. This is existing coverage, not a claim that I reran it. **What remains unverified** - A different revision, an analyzer bypass, or a plan shape that hides a limit during analysis and exposes it later could change the conclusion. None is demonstrated by the supplied top-level SQL. A manually constructed Apply-plan counterexample would establish a rule-precondition concern, but needs to be distinguished from a SQL execution reproducer. - [EliminateSortUnderApply](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateSortUnderApply.java#L34-L51) matches a direct sort or project-over-sort under Apply; it does not directly remove a sort beneath a retained limit. Standalone ordering does not affect membership/existence. The correctness concern is losing row selection from a meaningful limit/top-N. - The CaProver duplicate-row claim needs its complete Q1/Q2 and input data. Ordinary `IN` is converted to a [left semi join](https://github.com/apache/doris/blob/cc49069836975cd3a37bcdc9ba3e35a1fca717d3/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/InApplyToJoin.java), so additional inner matches alone do not establish multiplication of an outer row. **Missing evidence and next steps** 1. Provide the exact FE build/commit (`master` is insufficient), relevant session settings including planner/fallback settings, and complete query results or errors for all three examples. For statements accepted by the server, attach `EXPLAIN VERBOSE` output. Include any query-specific FE exception/stack trace if analysis fails unexpectedly. A runtime profile is not needed for this initial correctness check. 2. Provide the full CaProver SQL pair, DDL/inserts, harness revision, and whether results came from actual Doris execution or direct rule/plan evaluation. 3. Re-run at the pinned revision. If an unsafe limit reaches Apply, capture the plan before/after analysis and the elimination batch to identify the bypass. Add a focused planner test and an end-to-end regression for that reachable path, covering negation, NULLs, duplicates, and any relevant aggregate/filter wrappers. 4. For a confirmed reachable path, make the rewrite's semantic preconditions explicit: a positive, zero-offset limit at the existence boundary can be redundant; zero limits, offsets, and membership row selection require separate handling. Keeping an arbitrary correlated limit is not by itself a complete fix: later decorrelation must preserve per-outer-row selection. Retain explicit unsupported-query rejection until that behavior is implemented. Suggested routing: Nereids planner, correctness investigation / needs reproduction. No affected release range or additional fix version is established by this investigation. -- 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]
