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:
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:
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.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.
Separate test/documentation follow-up from the same review: #25925.
Relevant code:
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:
Both physical plans contain a
SortExecunderScalarSubqueryExec. Their keys are respectivelyscalar_subquery(<pending>)andCAST(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:
ScalarSubqueryExpr::get_propertiesreturnsSingletonwith aNullrange. A safe cast such as Int32 to Int64 then losesSingletonbecausecast_expr_propertiessees an unknown source type.EquivalenceProperties::update_propertiescallsget_propertiesfor non-leaf expressions and literals, and handles columns separately. Other leaves, includingScalarSubqueryExpr, retain unknown properties, so the regular ordering-analysis path misses itsSingletonin 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_propertiesfixes direct property calls, but is insufficient for the actual SQL path.Local isolated experiments produced:
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.
Separate test/documentation follow-up from the same review: #25925.
Relevant code:
ScalarSubqueryExpr::get_properties.EquivalenceProperties::update_properties.cast_expr_properties.