Describe the bug
EnsureRequirements does not reach its fixpoint in one application. Applying it to a plan it has just produced can still change that plan, so a chain that enforces requirements once after its own rewrites may be left with a plan the rule itself would improve.
Observed on a real 34-node plan, in a chain that enforces requirements several times. Two independent pieces of evidence:
The chain trace. Rendering the plan after every rule shows an enforcement pass changing the plan, then five rules in a row changing nothing, then the next enforcement pass changing it again:
4 OutputRequirements CHANGED
5 EnsureRequirements CHANGED
6 aggregate_statistics no
7 join_selection no
8 LimitedDistinctAggregation no
9 FilterPushdown no
10 WindowTopN no
11 EnsureRequirements CHANGED <- nothing touched the plan since pass 5
A direct check. Re-running the rule on the exact plan object it had returned, with nothing in between, produces a different plan. That is the definition, not an inference from the trace above. The difference is a RepartitionExec moving from above a SortExec to below it, with the sort switching to preserve_partitioning=true:
kept: RepartitionExec: Hash([ticker], 12) re-run: ProjectionExec
ProjectionExec FilterExec
FilterExec BoundedWindowAggExec
BoundedWindowAggExec SortExec preserve_partitioning=true
SortExec preserve_partitioning=false RepartitionExec: Hash([ticker], 12)
<scan> <scan>
The re-run is the better plan: the sort runs per partition instead of after a merge.
To Reproduce
I have not reduced this to the built-in chain over built-in sources. The shape it shows up on is a scan whose output ordering conflicts with a window function's required sort, planned through a chain with several enforcement passes. Happy to help narrow it down.
Note that calling the rule directly on a plan with no OutputRequirementExec above it is not a reproduction: without a parent requirement the sorting phase legitimately drops a merge on the second call, which looks like non-idempotence but is not. Any reproduction needs the output requirement in place.
Expected behavior
Either the rule converges in one application, or it iterates internally until it does. As it stands, whether a plan gets the better arrangement depends on how many times the surrounding chain happens to call the rule, which is not something a chain author can reason about.
It also makes a chain hard to size. A downstream chain naturally puts one enforcement pass behind each rewrite that can invalidate requirements, which is what the built-in list does with its single pass and its deliberate ordering. If the rule needs more than one application to settle, one pass behind each rewrite is not enough, and there is no way for the chain author to know how many would be.
Additional context
Found while measuring #25355 / #25356. a related problem where the rule's phases cancel each other out is #25360.
Describe the bug
EnsureRequirementsdoes not reach its fixpoint in one application. Applying it to a plan it has just produced can still change that plan, so a chain that enforces requirements once after its own rewrites may be left with a plan the rule itself would improve.Observed on a real 34-node plan, in a chain that enforces requirements several times. Two independent pieces of evidence:
The chain trace. Rendering the plan after every rule shows an enforcement pass changing the plan, then five rules in a row changing nothing, then the next enforcement pass changing it again:
A direct check. Re-running the rule on the exact plan object it had returned, with nothing in between, produces a different plan. That is the definition, not an inference from the trace above. The difference is a
RepartitionExecmoving from above aSortExecto below it, with the sort switching topreserve_partitioning=true:The re-run is the better plan: the sort runs per partition instead of after a merge.
To Reproduce
I have not reduced this to the built-in chain over built-in sources. The shape it shows up on is a scan whose output ordering conflicts with a window function's required sort, planned through a chain with several enforcement passes. Happy to help narrow it down.
Note that calling the rule directly on a plan with no
OutputRequirementExecabove it is not a reproduction: without a parent requirement the sorting phase legitimately drops a merge on the second call, which looks like non-idempotence but is not. Any reproduction needs the output requirement in place.Expected behavior
Either the rule converges in one application, or it iterates internally until it does. As it stands, whether a plan gets the better arrangement depends on how many times the surrounding chain happens to call the rule, which is not something a chain author can reason about.
It also makes a chain hard to size. A downstream chain naturally puts one enforcement pass behind each rewrite that can invalidate requirements, which is what the built-in list does with its single pass and its deliberate ordering. If the rule needs more than one application to settle, one pass behind each rewrite is not enough, and there is no way for the chain author to know how many would be.
Additional context
Found while measuring #25355 / #25356. a related problem where the rule's phases cancel each other out is #25360.