gimmickj opened a new issue, #65787: URL: https://github.com/apache/doris/issues/65787
### 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-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. ``` **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. ``` ### 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 above: 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? _No response_ ### Anything Else? _No response_ ### 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]
