wjjc1017 opened a new issue, #69490: URL: https://github.com/apache/doris/issues/69490
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version Doris Nereids `master` ### What's Wrong? `fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/EliminateLimitUnderApply.java:34-44`: ```java return logicalApply(any(), logicalLimit()).then(apply -> { if (!apply.isCorrelated()) { return null; } List<Plan> children = new ImmutableList.Builder<Plan>() .add(apply.left()) .add(apply.right().child()) .build(); return apply.withChildren(children); }).toRule(RuleType.ELIMINATE_LIMIT_UNDER_APPLY); ``` For any correlated subquery the `LogicalLimit` is removed without checking `limit == 0`, `offset > 0`, or the subquery type. `EliminateSortUnderApply` similarly removes the `LogicalSort`, so `ORDER BY ... LIMIT 1` loses both its ordering and its cardinality bound. ### What You Expected? ### Expected vs Actual | Case | Expected | Actual | |:---|:---|:---| | (1) `LIMIT 1 OFFSET 1` | `(1)` (only `k=1` has 2 matching rows) | `(1), (2)` | | (2) `LIMIT 0` | `[]` | `(1), (2)` | | (3) `IN (... ORDER BY ... LIMIT 1)` | `(2)` (top-1 for `k=1` is `200`, not `100`; for `k=2` is `300`) | `(1), (2)` (`100` now matches the full group) | For case (3), `CaProver`'s equivalent pair additionally exposes duplicate rows when a non-top row matches: `Q1 = [(10,)]` vs `Q2 = [(10,), (10,), (10,)]`. ### How to Reproduce? ```sql CREATE TABLE a (k INT, x INT) DUPLICATE KEY(k) DISTRIBUTED BY HASH(k) BUCKETS 1; CREATE TABLE b (k INT, v INT) DUPLICATE KEY(k) DISTRIBUTED BY HASH(k) BUCKETS 1; INSERT INTO a VALUES (1, 100), (2, 300); INSERT INTO b VALUES (1, 100), (1, 200), (2, 300); -- (1) EXISTS with OFFSET: requires at least 2 matching rows SELECT a.k FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.k = a.k LIMIT 1 OFFSET 1); -- (2) EXISTS with LIMIT 0: always false SELECT a.k FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.k = a.k LIMIT 0); -- (3) IN with ORDER BY ... LIMIT 1: only the top-1 row must be compared SELECT a.k FROM a WHERE a.x IN (SELECT b.v FROM b WHERE b.k = a.k ORDER BY b.v DESC LIMIT 1); ### Anything Else? ### Root cause The rule assumes "a limit in a correlated subquery never affects the result", which only holds for `EXISTS` without `OFFSET` when the limit is at the top (`LIMIT k`, `k >= 1`). It breaks for `LIMIT 0`, for `OFFSET > 0` (which changes the minimum matching cardinality), and for `IN`/`NOT IN` (where the limit selects a specific row set). ### Suggested fix - Keep the limit when `limit == 0`. - Keep the limit when `offset > 0`. - Keep `LIMIT`/`ORDER BY` for `IN`/`NOT IN` scalar subqueries. - Only eliminate `LIMIT k` (`k >= 1`, `OFFSET 0`) for correlated `EXISTS`, and only when the limit is not below an aggregate or a filter that observes the row count. ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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]
