REGRESSION: Window ordered by (total DESC, k) fails SanityCheckPlan when k is a function of a filtered constant column

Describe the bug

A window function ordered by (total DESC, k) fails to plan with a SanityCheckPlan error when:

k is a function of a column that a WHERE equality filter makes constant (for example lower(g) under WHERE g = 'a');
a SUM(...) OVER (PARTITION BY k) window feeds the ordered window; and
target_partitions = 1.

The optimizer seems to treat k as a constant and keeps the child order [k, total] as enough, so it adds no sort. The sanity check then does not see k as a constant and rejects the plan.

To Reproduce

SET datafusion.execution.target_partitions = 1;
CREATE TABLE t (g VARCHAR, v BIGINT);
INSERT INTO t VALUES ('a', 1), ('b', 2);
SELECT k, DENSE_RANK() OVER (ORDER BY total DESC, k) AS r
FROM (
  SELECT k, SUM(s) OVER (PARTITION BY k) AS total
  FROM (SELECT lower(g) AS k, SUM(v) AS s FROM t WHERE g = 'a' GROUP BY lower(g))
);

Run with datafusion-cli -f repro.sql. The output (plan abridged):

SanityCheckPlan
caused by
Error during planning: Plan: ["BoundedWindowAggExec: wdw=[dense_rank() ORDER BY [total DESC NULLS FIRST, k ASC NULLS LAST] ...], mode=[Sorted]",
  "  ProjectionExec: expr=[k@0 as k, sum(s) PARTITION BY [k] ... as total]",
  "    WindowAggExec: wdw=[sum(s) PARTITION BY [k] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ...]",
  "      ProjectionExec: expr=[lower(t.g)@0 as k, sum(t.v)@1 as s]",
  "        AggregateExec: mode=Single, gby=[lower(g@0) as lower(t.g)], aggr=[sum(t.v)]",
  "          FilterExec: g@0 = a",
  "            DataSourceExec: partitions=1, partition_sizes=[1]"]
does not satisfy order requirements: [total@1 DESC, k@0 ASC NULLS LAST].
Child-0 order: [[k@0 ASC NULLS LAST, total@1 DESC], [k@0 ASC NULLS LAST, total@1 ASC NULLS LAST], [k@0 ASC NULLS LAST, total@1 ASC], [k@0 ASC NULLS LAST, total@1 DESC NULLS LAST]]

Expected behavior

The query plans and returns one row, k = 'a', r = 1.

Additional context

What I checked:

Change	Result
`datafusion-cli` 54.0.0	works
`main` at `390d210` and at `350ca76`	fails
`GROUP BY g` (plain column) in place of `lower(g)` or `NULLIF(g, '')`	works
No `WHERE g = 'a'`	works
`target_partitions` = 4 or 16	works
`ROW_NUMBER()` in place of `DENSE_RANK()`	fails
`ORDER BY total DESC, k` at the end of the query, in place of the window	works
No `SUM(...) OVER (PARTITION BY k)` window (rank the aggregate directly)	works

A workaround is to order the window by one struct key, which the optimizer does not reduce to the child order: DENSE_RANK() OVER (ORDER BY named_struct('total', -total, 'key', k) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Are you currently working on this?

I had a draft ready but if you're able to work on it in the next 1-2 days I'm happy to review your fix.

@adriangb

@adriangb How can i connect to you , i want to ask something regarding this and
Sir are you a maintainer?

The only communication option is to post publicly on this issue. I expect all communication other be human-driven. I recommend posting a plan for this change here as a first step.

@adriangb

I just wanted to connect just because I am new to this repo @adriangb sir

I don't know what you mean by "connect". I recommend you are more specific in your communication. I've already stated clearly that the only acceptable form of communication here is for you to post a proposed implementation plan for your fix. I would like your next message to be focused on tackling the task at hand otherwise I will not be able to dedicate any more time to this conversation.

@adriangb

@adriangb Sir I am currently up into this issue please give me some time, the next chat to this will be the proper implementation from my side. Thanks for your time sir

@adriangb

Hi @adriangb,

Thanks for your patience. I've submitted a PR for #26772, which addresses a window ordering issue.

My approach was to reproduce the failure with a regression test, identify where the optimizer incorrectly removes a required SortExec, and add validation of the replacement window's input ordering before accepting sort removal.

The regression test and physical optimizer tests are passing.
I'd appreciate any feedback on the approach.

PR: #26858

I reproduced this and confirmed it is a regression, so I have marked this issue as such.

Running the SQL from the description with datafusion-cli -f repro.sql against released binaries and current main:

Version	Result
53.0.0	works
53.1.0	works
54.0.0	works
54.1.0	works
55.0.0	fails with `SanityCheckPlan`
55.1.0	fails with `SanityCheckPlan`
`main` (`181a87a`)	fails with `SanityCheckPlan`

I bisected between 54.1.0 and 55.0.0 (building datafusion-cli at each step and running the repro). The first bad commit is d185ed5, "Fix ordering for UNION ALL over heterogeneous constants" (#23528). Its parent, 620262b, plans the query fine. That PR changed EquivalenceGroup::normalize_sort_exprs / normalize_sort_requirements so that only AcrossPartitions::Uniform constants are dropped from a sort requirement, while Heterogeneous constants are kept. That matches the symptom here: after the change the optimizer and the sanity checker no longer agree on whether k (which is lower(g) under WHERE g = 'a') counts as a constant that can be removed from the ordering, so one side decides no sort is needed and the other rejects the plan.

For the record, the EnsureRequirements rule named in the error (#21976, fb1c0f3) is not the cause: the query works both at that commit and at its parent.

@vadimpiven

cc @vadimpiven since you worked on #23528

@alamb

@alamb a second SanityCheckPlan regression is on branch-56, and #27191 fixes it along with this issue. Can it count as part of this issue on the 56 list, rather than needing its own? The cause is different: it comes from #26747 and involves no constant.

A window ordered by (k, c) fails when its input is a LIMIT query ordered by (k, a, c), the filter says a = CAST(c AS DATE), and the query doesn't select a. Changing any one of those conditions avoids the error, so the shape is narrow.

CREATE TABLE t (k VARCHAR, a DATE, c TIMESTAMP) AS VALUES
  ('x', DATE '2026-01-02', TIMESTAMP '2026-01-02 05:00:00'),
  ('x', DATE '2026-01-01', TIMESTAMP '2026-01-01 09:00:00'),
  ('x', DATE '2026-01-02', TIMESTAMP '2026-01-02 01:00:00'),
  ('y', DATE '2026-01-03', TIMESTAMP '2026-01-03 01:00:00');

SELECT k, c, ROW_NUMBER() OVER (ORDER BY k, c) AS r
FROM (SELECT k, a, c FROM t WHERE a = CAST(c AS DATE) ORDER BY k, a, c LIMIT 10)
ORDER BY r;

Build	Result
Just before #26747	4 rows, with `r` from 1 to 4 in `(k, c)` order
#26747 (found with `git bisect`), `branch-56`, `main`	`SanityCheckPlan` error
#27191	Same 4 rows

Cause, and the query changes that avoid the error

Since #26747, a sort with a fetch keeps its own keys when they satisfy its parent (pushdown_sorts_helper). Its comment gives the reason: replacing the keys can change which rows the LIMIT keeps. So the TopK now stays on (k, a, c), which proves (k, c) through a = CAST(c AS DATE), and the window's sort is removed below the projection. The projection then drops a, and above it only (k) can be proven:

BoundedWindowAggExec: ORDER BY [k, c], mode=[Sorted]
  ProjectionExec: k, c
    SortExec: TopK(fetch=10), expr=[k, a, c]

#27191 keeps the sort above a projection that can't prove the ordering its input has.

Each of these changes alone makes the query work on main:

Change	Result
Also select `a`	Works
Remove `WHERE a = CAST(c AS DATE)`	Works
Remove `LIMIT 10`	Works
Replace the window with an outer `ORDER BY k, c`	Works

The results are the same at the default target_partitions and at 1.

A wider check found a third new failure on branch-56, from #25922: a window on c over a TopK on (k, a, c), with k a per-partition constant and a = CAST(c AS DATE). #27191 now fixes that too, so all three are in one PR. Across 7,440 generated query runs it has no regression against 55.0.0, 55.1.0, 55.2.0 or the commit before #23528.

Navigation Menu

Repository navigation

REGRESSION: Window ordered by (total DESC, k) fails SanityCheckPlan when k is a function of a filtered constant column #26762

Describe the bug

To Reproduce

Expected behavior

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

REGRESSION: Window ordered by (total DESC, k) fails SanityCheckPlan when k is a function of a filtered constant column #26762

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

lakshaydevoo-arch commented on Oct 26, 2026

adriangb commented on Oct 26, 2026

lakshaydevoo-arch commented on Oct 26, 2026

adriangb commented on Oct 26, 2026

lakshaydevoo-arch commented on Oct 26, 2026

adriangb commented on Oct 26, 2026

lakshaydevoo-arch commented on Oct 26, 2026

lakshaydevoo-arch commented on Oct 28, 2026

alamb commented on Nov 3, 2026

adriangb commented on Nov 4, 2026

jayzhan211 commented on Nov 7, 2026

jayzhan211 commented on Nov 7, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions