Toby1009 opened a new issue, #25926: URL: https://github.com/apache/datafusion/issues/25926
### Is your feature request related to a problem or challenge? Ordering analysis loses properties of uncorrelated scalar subquery expressions, retaining sorts even when the sort key has the same value for every outer row. For example: ```sql CREATE TABLE nums (x INT) AS VALUES (3), (1), (2); EXPLAIN SELECT x FROM nums ORDER BY (SELECT max(x) FROM nums); EXPLAIN SELECT x FROM nums ORDER BY CAST((SELECT max(x) FROM nums) AS BIGINT); ``` Both physical plans contain a `SortExec` under `ScalarSubqueryExec`. Their keys are respectively `scalar_subquery(<pending>)` and `CAST(scalar_subquery(<pending>) AS Int64)`, although each key is constant across the outer rows. The queries return valid results, but the sorts are unnecessary. There are two gaps: 1. `ScalarSubqueryExpr::get_properties` returns `Singleton` with a `Null` range. A safe cast such as Int32 to Int64 then loses `Singleton` because `cast_expr_properties` sees an unknown source type. 2. `EquivalenceProperties::update_properties` calls `get_properties` for non-leaf expressions and literals, and handles columns separately. Other leaves, including `ScalarSubqueryExpr`, retain unknown properties, so the regular ordering-analysis path misses its `Singleton` in the first place. ### Describe the solution you'd like Preserve scalar subquery properties through the regular ordering-analysis path and safe casts, so both queries above can omit the redundant sort. Account for the leaf traversal as well as the missing range type. Add SQL plan regression tests for the direct subquery and safe-cast cases, with results checked separately. ### Describe alternatives you've considered Only adding a typed unbounded range to `ScalarSubqueryExpr::get_properties` fixes direct property calls, but is insufficient for the actual SQL path. Local isolated experiments produced: | Change | Direct subquery SortExec count | Cast subquery SortExec count | | --- | --- | --- | | Current behavior | 1 | 1 | | Recover the subquery range type only | 1 | 1 | | Read its properties during leaf traversal only | 0 | 1 | | Both | 0 | 0 | These experiments establish the two causes; the final implementation can choose an appropriate general approach to leaf property analysis. ### Additional context Follow-up to the scalar subquery observation in [the review of #25668](https://github.com/apache/datafusion/pull/25668#pullrequestreview-5369405678). Separate test/documentation follow-up from the same review: #25925. Relevant code: - [`ScalarSubqueryExpr::get_properties`](https://github.com/apache/datafusion/blob/main/datafusion/physical-expr/src/scalar_subquery.rs#L155). - [`EquivalenceProperties::update_properties`](https://github.com/apache/datafusion/blob/main/datafusion/physical-expr/src/equivalence/properties/mod.rs#L1528). - [`cast_expr_properties`](https://github.com/apache/datafusion/blob/main/datafusion/physical-expr/src/expressions/cast.rs#L328). -- 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]
