Skip to content

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

@2010YOUY01

Description

@2010YOUY01

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

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions