[Bug] Wrong result for UNION DISTINCT + ORDER BY + LIMIT/OFFSET when projection is wide (pushed TopN uses wrong sort key)

[gimmickj]

[Bug] Wrong result for UNION DISTINCT + ORDER BY + LIMIT/OFFSET when projection is wide (pushed TopN uses wrong sort key)

Version

doris-4.1.1-rc01 (AVX2) RELEASE
Built on Tue, 19 May 2026 15:00:57 CST

What's Wrong?

When a query is shaped like:

  SELECT <wide column list>
  FROM (SELECT ... UNION SELECT ...) AS u
  ORDER BY u.<some_column>
  LIMIT 1 OFFSET 1;

Doris returns the wrong row(s) if the projection is "wide" (many columns).
With a narrow projection (e.g. only 2 columns), the same query returns the
correct result. Rewriting the query to use ROW_NUMBER() instead of
LIMIT/OFFSET also returns the correct result, which confirms the underlying
data and join/union logic are fine — this looks like a planner/optimizer bug.

EXPLAIN shows the actual root cause: the branch-local pushed VTOP-N node's
sort key is wrong in the wide-projection case.

- Narrow projection: pushed VTOP-N uses "order by: ContactName ASC", which
  matches the outer ORDER BY u.ContactName -> correct result.
- Wide projection: the outer query is still "ORDER BY u.ContactName", but
  the pushed VTOP-N uses "order by: Address ASC" instead -> wrong candidate
  rows are kept locally before the outer sort even runs, so the final
  LIMIT/OFFSET returns the wrong row.

This strongly suggests that when PUSH_DOWN_TOP_N_THROUGH_UNION pushes the
outer ORDER BY expression down into each UNION branch, the column/slot
mapping between the UNION's output and each branch's local output is wrong
in the wide-projection case (possibly an ordinal/position-based mapping that
gets thrown off once column pruning / projection push-down reorders or
trims the branch's output columns), rather than being resolved by slot id.

I tried disabling the following rules individually and in combination, none
of which changed the wrong result or the plan shape:

  SET disable_nereids_rules='PUSH_DOWN_TOP_N_THROUGH_UNION';
  SET disable_nereids_rules='COLUMN_PRUNING';
  SET disable_nereids_rules='PUSH_PROJECT_THROUGH_UNION';
  SET disable_nereids_rules='PUSH_PROJECT_INTO_UNION';
  SET disable_nereids_rules='ELIMINATE_SORT';
  SET disable_nereids_rules='PUSH_DOWN_TOP_N_THROUGH_UNION,COLUMN_PRUNING,PUSH_PROJECT_THROUGH_UNION,PUSH_PROJECT_INTO_UNION';
  SET enable_nereids_planner=false;

Note: this query was originally generated by an EF Core provider
(EFCore.Doris), but it reproduces identically when executed as raw SQL via
Doris FE's MySQL protocol, so it is not an EF Core / client-side issue.

What You Expected?

The query should return the same row(s) as the narrow-projection version
and the ROW_NUMBER()-rewritten version, i.e. the outer ORDER BY u.ContactName
should be honored consistently regardless of how many columns are in the
projection list.

Expected result for the reproduction query below:

CONSH | Berkeley Gardens 12 Brewery | London | Consolidated Holdings | Elizabeth Brown | Sales Representative | UK | (171) 555-9199 | (171) 555-2282 | WX1 6LT

In other words, when Doris pushes a TopN down through a UNION
(PUSH_DOWN_TOP_N_THROUGH_UNION), the sort key used by the branch-local
pushed VTOP-N should always correspond to the column actually referenced
in the outer ORDER BY (ContactName in this case), never a different column
(Address), regardless of projection width.

How to Reproduce?

-- setup
USE northwind;
-- (Customers table is the standard Northwind sample table)

-- 1) WRONG RESULT — wide projection
SELECT `u`.`CustomerID`, `u`.`Address`, `u`.`City`, `u`.`CompanyName`, `u`.`ContactName`,
       `u`.`ContactTitle`, `u`.`Country`, `u`.`Fax`, `u`.`Phone`, `u`.`PostalCode`, `u`.`Region`
FROM (
    SELECT `c`.`CustomerID`, `c`.`Address`, `c`.`City`, `c`.`CompanyName`, `c`.`ContactName`,
           `c`.`ContactTitle`, `c`.`Country`, `c`.`Fax`, `c`.`Phone`, `c`.`PostalCode`, `c`.`Region`
    FROM `Customers` AS `c`
    WHERE `c`.`City` = 'Berlin'
    UNION
    SELECT `c0`.`CustomerID`, `c0`.`Address`, `c0`.`City`, `c0`.`CompanyName`, `c0`.`ContactName`,
           `c0`.`ContactTitle`, `c0`.`Country`, `c0`.`Fax`, `c0`.`Phone`, `c0`.`PostalCode`, `c0`.`Region`
    FROM `Customers` AS `c0`
    WHERE `c0`.`City` = 'London'
) AS `u`
ORDER BY `u`.`ContactName`
LIMIT 1 OFFSET 1;

-- Actual:   ALFKI | ... | Berlin | ... | Maria Anders | ...
-- Expected: CONSH | ... | London | ... | Elizabeth Brown | ...


-- 2) CORRECT RESULT — narrow projection (control case)
SELECT `u`.`CustomerID`, `u`.`ContactName`
FROM (
    SELECT `c`.`CustomerID`, `c`.`ContactName`
    FROM `Customers` AS `c`
    WHERE `c`.`City` = 'Berlin'
    UNION
    SELECT `c0`.`CustomerID`, `c0`.`ContactName`
    FROM `Customers` AS `c0`
    WHERE `c0`.`City` = 'London'
) AS `u`
ORDER BY `u`.`ContactName`
LIMIT 1 OFFSET 1;

-- Result: CONSH | Elizabeth Brown  (correct)


-- 3) CORRECT RESULT — same wide projection, rewritten with ROW_NUMBER()
SELECT `t`.`CustomerID`, `t`.`Address`, `t`.`City`, `t`.`CompanyName`, `t`.`ContactName`,
       `t`.`ContactTitle`, `t`.`Country`, `t`.`Fax`, `t`.`Phone`, `t`.`PostalCode`, `t`.`Region`
FROM (
    SELECT `u`.*, ROW_NUMBER() OVER (ORDER BY `u`.`ContactName`) AS `rn`
    FROM (
        SELECT `c`.`CustomerID`, `c`.`Address`, `c`.`City`, `c`.`CompanyName`, `c`.`ContactName`,
               `c`.`ContactTitle`, `c`.`Country`, `c`.`Fax`, `c`.`Phone`, `c`.`PostalCode`, `c`.`Region`
        FROM `Customers` AS `c`
        WHERE `c`.`City` = 'Berlin'
        UNION
        SELECT `c0`.`CustomerID`, `c0`.`Address`, `c0`.`City`, `c0`.`CompanyName`, `c0`.`ContactName`,
               `c0`.`ContactTitle`, `c0`.`Country`, `c0`.`Fax`, `c0`.`Phone`, `c0`.`PostalCode`, `c0`.`Region`
        FROM `Customers` AS `c0`
        WHERE `c0`.`City` = 'London'
    ) AS `u`
) AS `t`
WHERE `t`.`rn` > 1 AND `t`.`rn` <= 2;

-- Result: CONSH | ... | London | ... | Elizabeth Brown | ...  (correct)

Anything Else?

EXPLAIN diffs (key part):

- Narrow projection: branch-local pushed VTOP-N -> "order by: ContactName ASC"
  (matches outer ORDER BY u.ContactName)

- Wide projection: branch-local pushed VTOP-N -> "order by: Address ASC"
  (does NOT match outer ORDER BY u.ContactName — this is the direct cause
  of the wrong result: the wrong rows are truncated locally before the
  outer ORDER BY / LIMIT / OFFSET can act on them)

I can attach the full EXPLAIN output for both the narrow and wide
projection cases, the exact session variables used, and a smaller minimal
repro (fewer columns / reordered columns) if that would help narrow down
the exact rule/code path — happy to provide on request.

Suspected area: PUSH_DOWN_TOP_N_THROUGH_UNION rule's mapping from the
outer ORDER BY expression to each UNION branch's local output slot,
possibly interacting with column pruning / projection push-down under
UNION when the branch output has many columns.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[zclllyybb]

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, with branch-4.1 backport #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. 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, 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.

[gimmickj]

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, with branch-4.1 backport #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, 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.