wjjc1017 commented on issue #69490:
URL: https://github.com/apache/doris/issues/69490#issuecomment-6357433215
Reproduced locally on **doris-4.1.3-rc02-7126cf65d96** (all-in-one arm64
image; `@@version_comment`
and `SHOW FRONTENDS` both report this build). You are right that the three
top-level forms are
rejected/folded by `SubExprAnalyzer`. The reachable path is to wrap the
correlated LIMIT in a
**derived table**, because `rootIsLimitZero()` / `rootIsLimitWithOffset()` /
`checkRootIsLimit()`
all test `logicalPlan instanceof LogicalLimit`.
### DDL / data
```sql
CREATE TABLE a (k INT, x INT) DUPLICATE KEY(k)
DISTRIBUTED BY HASH(k) BUCKETS 1 PROPERTIES ("replication_num"="1");
CREATE TABLE b (k INT, v INT) DUPLICATE KEY(k)
DISTRIBUTED BY HASH(k) BUCKETS 1 PROPERTIES ("replication_num"="1");
INSERT INTO a VALUES (1,100),(2,300);
INSERT INTO b VALUES (1,100),(1,200),(2,300);
```
### Repro and results (ELIMINATE_LIMIT_UNDER_APPLY enabled)
```sql
-- (A) expected (1), actual (1),(2)
SELECT a.k FROM a WHERE EXISTS (
SELECT * FROM (SELECT 1 FROM b WHERE b.k = a.k LIMIT 1 OFFSET 1) t) ORDER
BY a.k;
-- (B) expected empty, actual (1),(2)
SELECT a.k FROM a WHERE EXISTS (
SELECT * FROM (SELECT 1 FROM b WHERE b.k = a.k LIMIT 0) t) ORDER BY a.k;
-- (C) expected (2), actual (1),(2)
SELECT a.k FROM a WHERE a.x IN (
SELECT v FROM (SELECT b.v FROM b WHERE b.k = a.k ORDER BY b.v DESC LIMIT
1) t) ORDER BY a.k;
```
Semantic baselines (no buggy rewrite involved):
```sql
SELECT a.k FROM a WHERE (SELECT COUNT(*) FROM b WHERE b.k = a.k) > 1; --
(1)
SELECT a.k FROM a WHERE a.x = (SELECT MAX(b.v) FROM b WHERE b.k = a.k); --
(2)
```
### Plan evidence
`EXPLAIN REWRITTEN PLAN` for (A): `LEFT_SEMI_JOIN(a,b) ON k = k`, **no
LogicalLimit**.
For (C): `LEFT_SEMI_JOIN(a,b) ON x = v AND k = k`, **no LogicalLimit/Sort**.
With `ELIMINATE_LIMIT_UNDER_APPLY` disabled in the same session, the
`LogicalLimit` is retained
(that path also exposes a separate correlation-loss fallback, so disabling
the rule is not a
workaround; the baselines above are the semantic reference).
### Root cause
`PullUpProjectUnderApply` runs before `EliminateUselessPlanUnderApply` in
the "Subquery unnesting"
topic. It pulls the derived-table project above the Apply, exposing the
`LogicalLimit` as the Apply's
RHS root; `EliminateLimitUnderApply` then removes it.
### Suggested fix
Make the analyzer limit checks recursive (inspect the plan under
`Project`/`SubQueryAlias` wrappers),
or have `EliminateLimitUnderApply` check `limit == 0`, `offset > 0`, and the
subquery type. Keeping an
arbitrary correlated limit is not sufficient by itself: later decorrelation
must preserve per-outer-row
selection.
--
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]
