gimmickj commented on issue #65787: URL: https://github.com/apache/doris/issues/65787#issuecomment-5014689739
> Breakwater-GitHub-Analysis-Slot: slot_2a17e44d2a36 This content is generated by AI for reference only. > > Initial analysis: > > This looks like a valid planner correctness bug for `doris-4.1.1-rc01`, and the code path matches the reported symptom. > > For SQL `UNION` / `UNION DISTINCT`, Doris rewrites the plan through `BuildAggForUnion` into a distinct aggregate over `UNION ALL`, so the relevant pushdown rule is `PushDownTopNDistinctThroughUnion`, not only `PushDownTopNThroughUnion`. > > In `4.1.1-rc01`, `PushDownTopNDistinctThroughUnion` builds the branch replacement map with: > > replaceMap.put(output, child.getOutput().get(i)); > > However, set operations keep the authoritative mapping from set-operation output columns to each branch in `regularChildrenOutputs` / `getRegularChildOutput(childIdx)`. If projection pushdown or pruning causes the child plan output order/cardinality to differ from the regular set-operation output mapping, using `child.getOutput().get(i)` can rewrite the outer `ORDER BY` key to the wrong branch slot. That is consistent with the reported wide-projection plan where outer `ORDER BY u.ContactName` becomes a branch-local TopN sorted by `Address`, causing the wrong rows to be kept before the final global sort and offset. > > This exact issue class appears to be fixed already in public Doris code by [#64908](https://github.com/apache/doris/pull/64908), with branch-4.1 backport [#65071](https://github.com/apache/doris/pull/65071). That change updates `PushDownTopNDistinctThroughUnion` and `PushDownLimitDistinctThroughUnion` to build the replacement map from: > > union.getRegularChildOutput(childIdx).get(i) > > instead of `child.getOutput().get(i)`. The reported `4.1.1-rc01` build predates that fix. > > Suggested next steps: > > 1. Please retest this repro on current `branch-4.1` or any 4.1 build containing [branch-4.1: [fix](set operation) Use regular child output for set operation rules #64908 #65071](https://github.com/apache/doris/pull/65071). The fixed plan should not show a pushed branch TopN using `Address` for an outer `ORDER BY ContactName`. > > 2. Add a regression case for `UNION DISTINCT` with a wide projection, `ORDER BY` on a non-leading projected column, and `LIMIT/OFFSET`; the test should assert both the result row and the absence of the wrong pushed sort key. > > 3. For affected builds, the targeted rule to disable is likely `PUSH_DOWN_TOP_N_DISTINCT_THROUGH_UNION`; disabling only `PUSH_DOWN_TOP_N_THROUGH_UNION` would not cover this `UNION DISTINCT` shape. The reporter's `ROW_NUMBER()` rewrite is also a safe workaround. > > > If the issue still reproduces on a build containing [#65071](https://github.com/apache/doris/pull/65071), the most useful missing information would be the full `EXPLAIN VERBOSE` output for the wide and narrow queries, `SHOW VARIABLES LIKE 'disable_nereids_rules'` after the attempted workaround, and a minimal DDL/data script small enough to add directly as a regression test. Thanks, this is very helpful. This matches what we observed on `doris-4.1.1-rc01`: in the wide-projection `UNION DISTINCT + ORDER BY + LIMIT/OFFSET` case, the branch-local TopN is pushed down with `Address` as the sort key instead of `ContactName`, which leads to a wrong result. We will retest this on a 4.1 build containing `#65071`. If it still reproduces there, we will provide: - full `EXPLAIN VERBOSE` output for the narrow and wide projections - `SHOW VARIABLES LIKE 'disable_nereids_rules'` - a minimal DDL/data/SQL repro script for regression testing Could you also confirm which released version first includes `#65071`? -- 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]
