Optimizer 'common_sub_expression_eliminate' fails in a window function (SQLStorm)

Describe the bug

datafusion-cli is compiled from the latest main commit 5bbdb7e

The query is able to run in DuckDB but fails in DataFusion

D WITH suppliers AS (
    SELECT *
    FROM (VALUES (1, 10.0), (1, 20.0)) AS t(nation, acctbal)
  )
  SELECT
    ROW_NUMBER() OVER (PARTITION BY nation ORDER BY acctbal DESC) AS rn
  FROM suppliers AS s
  WHERE acctbal > (
    SELECT AVG(acctbal) FROM suppliers
  );
┌───────┐
│  rn   │
│ int64 │
├───────┤
│   1   │
└───────┘

> WITH suppliers AS (
  SELECT *
  FROM (VALUES (1, 10.0), (1, 20.0)) AS t(nation, acctbal)
)
SELECT
  ROW_NUMBER() OVER (PARTITION BY nation ORDER BY acctbal DESC) AS rn
FROM suppliers AS s
WHERE acctbal > (
  SELECT AVG(acctbal) FROM suppliers
);

Optimizer rule 'common_sub_expression_eliminate' failed
caused by
Error during planning: Window has mismatch between number of expressions (1) and number of fields in schema (0)

To Reproduce

No response

Expected behavior

No response

Additional context

Found by SQLStorm #17698

take

The window aggregation had its input schema containing 2 columns [nation:Int64;N, acctbal:Float64;N] initially:

    Projection: row_number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rn [rn:UInt64]
      WindowAggr: windowExpr=[[row_number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]] [nation:Int64;N, acctbal:Float64;N, row_number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:UInt64]
        Filter: s.acctbal > (<subquery>) [nation:Int64;N, acctbal:Float64;N]
          Subquery: [avg(suppliers.acctbal):Float64;N]
            Projection: avg(suppliers.acctbal) [avg(suppliers.acctbal):Float64;N]
              Aggregate: groupBy=[[]], aggr=[[avg(suppliers.acctbal)]] [avg(suppliers.acctbal):Float64;N]
                SubqueryAlias: suppliers [nation:Int64;N, acctbal:Float64;N]
                  Projection: t.nation, t.acctbal [nation:Int64;N, acctbal:Float64;N]
                    SubqueryAlias: t [nation:Int64;N, acctbal:Float64;N]
                      Projection: column1 AS nation, column2 AS acctbal [nation:Int64;N, acctbal:Float64;N]
                        Values: (Int64(1), Float64(10)), (Int64(1), Float64(20)) [column1:Int64;N, column2:Float64;N]
          SubqueryAlias: s [nation:Int64;N, acctbal:Float64;N]
            SubqueryAlias: suppliers [nation:Int64;N, acctbal:Float64;N]
              Projection: t.nation, t.acctbal [nation:Int64;N, acctbal:Float64;N]
                SubqueryAlias: t [nation:Int64;N, acctbal:Float64;N]
                  Projection: column1 AS nation, column2 AS acctbal [nation:Int64;N, acctbal:Float64;N]
                    Values: (Int64(1), Float64(10)), (Int64(1), Float64(20)) [column1:Int64;N, column2:Float64;N]

After scalar_subquery_to_join rule, the subquery is rewritten as join,
its output schema (which is window aggregation input schema) now containing 3 columns: [nation:Int64;N, acctbal:Float64;N, avg(suppliers.acctbal):Float64;N].

    Projection: row_Thus causing `common_sub_expression_eliminate` panic as number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS rn [rn:UInt64]
      WindowAggr: windowExpr=[[row_number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]] [nation:Int64;N, acctbal:Float64;N, row_number() PARTITION BY [s.nation] ORDER BY [s.acctbal DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:UInt64]
        Filter: s.acctbal > __scalar_sq_1.avg(suppliers.acctbal) [nation:Int64;N, acctbal:Float64;N, avg(suppliers.acctbal):Float64;N]
          Left Join:  Filter: Boolean(true) [nation:Int64;N, acctbal:Float64;N, avg(suppliers.acctbal):Float64;N]
            SubqueryAlias: s [nation:Int64;N, acctbal:Float64;N]
              SubqueryAlias: suppliers [nation:Int64;N, acctbal:Float64;N]
                Projection: t.nation, t.acctbal [nation:Int64;N, acctbal:Float64;N]
                  SubqueryAlias: t [nation:Int64;N, acctbal:Float64;N]
                    Projection: column1 AS nation, column2 AS acctbal [nation:Int64;N, acctbal:Float64;N]
                      Values: (Int64(1), Float64(10)), (Int64(1), Float64(20)) [column1:Int64;N, column2:Float64;N]
            SubqueryAlias: __scalar_sq_1 [avg(suppliers.acctbal):Float64;N]
              Projection: avg(suppliers.acctbal) [avg(suppliers.acctbal):Float64;N]
                Aggregate: groupBy=[[]], aggr=[[avg(suppliers.acctbal)]] [avg(suppliers.acctbal):Float64;N]
                  SubqueryAlias: suppliers [nation:Int64;N, acctbal:Float64;N]
                    Projection: t.nation, t.acctbal [nation:Int64;N, acctbal:Float64;N]
                      SubqueryAlias: t [nation:Int64;N, acctbal:Float64;N]
                        Projection: column1 AS nation, column2 AS acctbal [nation:Int64;N, acctbal:Float64;N]
                          Values: (Int64(1), Float64(10)), (Int64(1), Float64(20)) [column1:Int64;N, column2:Float64;N]

This cause common_sub_expression_eliminate fails when creating the old window aggregation plan with the new input.

datafusion/datafusion/optimizer/src/common_subexpr_eliminate.rs

Lines 223 to 227 in 297e537

    
           Window::try_new_with_schema( 
        
               new_window_expr, 
        
               Arc::new(plan), 
        
               schema, 
        
           )

Navigation Menu

Repository navigation

Optimizer 'common_sub_expression_eliminate' fails in a window function (SQLStorm) #17770

Describe the bug

To Reproduce

Expected behavior

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	Window::try_new_with_schema(
	new_window_expr,
	Arc::new(plan),
	schema,
	)

Optimizer 'common_sub_expression_eliminate' fails in a window function (SQLStorm) #17770

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

dqkqd commented on Sep 30, 2025

dqkqd commented on Oct 1, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions