You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "2010YOUY01 (via GitHub)" <gi...@apache.org> on 2025/09/25 08:31:42 UTC
[I] RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm) [datafusion]
2010YOUY01 opened a new issue, #17771:
URL: https://github.com/apache/datafusion/issues/17771
### Describe the bug
datafusion-cli is compiled from the latest main commit 5bbdb7eb1
This query is able to run in DuckDB and PostgreSQL, but fails in DataFusion
```
> WITH part AS (
SELECT *
FROM (VALUES (1, 'A'), (2, 'B')) AS t(partkey, name)
),
RevenueCTE AS (
SELECT partkey, total_revenue
FROM (VALUES (1, 10.0), (2, 5.0)) AS t(partkey, total_revenue)
),
SupplierCTE AS (
SELECT partkey, total_supply_cost
FROM (VALUES (1, 2.0), (2, 3.0)) AS t(partkey, total_supply_cost)
),
RankedParts AS (
SELECT
p.partkey,
p.name,
COALESCE(r.total_revenue, 0) AS total_revenue,
COALESCE(s.total_supply_cost, 0) AS total_supply_cost,
RANK() OVER (
ORDER BY COALESCE(r.total_revenue, 0) DESC
) AS revenue_rank
FROM part AS p
LEFT JOIN RevenueCTE AS r ON p.partkey = r.partkey
LEFT JOIN SupplierCTE AS s ON p.partkey = s.partkey
)
SELECT
partkey,
name,
total_revenue,
total_supply_cost,
CASE
WHEN total_supply_cost > 0 THEN total_revenue / total_supply_cost
ELSE NULL
END AS ratio
FROM RankedParts
WHERE revenue_rank <= 10
ORDER BY total_revenue DESC;
SanityCheckPlan
caused by
Error during planning: Plan: ["SortPreservingMergeExec: [total_revenue@2 DESC]", " ProjectionExec: expr=[partkey@0 as partkey, name@1 as name, total_revenue@2 as total_revenue, total_supply_cost@3 as total_supply_cost, CASE WHEN total_supply_cost@3 > 0 THEN total_revenue@2 / total_supply_cost@3 END as ratio]", " ProjectionExec: expr=[partkey@0 as partkey, name@1 as name, CASE WHEN total_revenue@2 IS NOT NULL THEN total_revenue@2 ELSE 0 END as total_revenue, CASE WHEN total_supply_cost@3 IS NOT NULL THEN total_supply_cost@3 ELSE 0 END as total_supply_cost]", " CoalesceBatchesExec: target_batch_size=8192", " FilterExec: rank() ORDER BY [coalesce(r.total_revenue, Int64(0)) DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@4 <= 10, projection=[partkey@0, name@1, total_revenue@2, total_supply_cost@3]", " RepartitionExec: partitioning=RoundRobinBatch(14), input_partitions=1", " BoundedWindowAggExec: wdw=[rank() ORDER BY [coalesce(r.t
otal_revenue, Int64(0)) DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { name: \"rank() ORDER BY [coalesce(r.total_revenue, Int64(0)) DESC NULLS FIRST] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\", data_type: UInt64, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]", " SortPreservingMergeExec: [CASE WHEN total_revenue@2 IS NOT NULL THEN total_revenue@2 ELSE 0 END DESC]", " SortExec: expr=[CASE WHEN total_revenue@2 IS NOT NULL THEN total_revenue@2 ELSE 0 END DESC], preserve_partitioning=[true]", " ProjectionExec: expr=[partkey@1 as partkey, name@2 as name, total_revenue@3 as total_revenue, total_supply_cost@0 as total_supply_cost]", " CoalesceBatchesExec: target_batch_size=8192", " HashJoinExec: mode=CollectLeft, join_type=Right, on=[(partkey@0, partkey@0)], projection=[tota
l_supply_cost@1, partkey@2, name@3, total_revenue@4]", " ProjectionExec: expr=[column1@0 as partkey, column2@1 as total_supply_cost]", " DataSourceExec: partitions=1, partition_sizes=[1]", " RepartitionExec: partitioning=RoundRobinBatch(14), input_partitions=1", " ProjectionExec: expr=[partkey@1 as partkey, name@2 as name, total_revenue@0 as total_revenue]", " CoalesceBatchesExec: target_batch_size=8192", " HashJoinExec: mode=CollectLeft, join_type=Right, on=[(partkey@0, partkey@0)], projection=[total_revenue@1, partkey@2, name@3]", " ProjectionExec: expr=[column1@0 as partkey, column2@1 as total_revenue]", " DataSourceExec: partitions=1, partition_sizes=[1]", " ProjectionExec: expr=[column1@0 as partkey, column2@1 as name]", "
DataSourceExec: partitions=1, partition_sizes=[1]"] does not satisfy order requirements: [total_revenue@2 DESC]. Child-0 order: []
```
### To Reproduce
_No response_
### Expected behavior
_No response_
### Additional context
Found by SQLStorm #17698
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm) [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #17771:
URL: https://github.com/apache/datafusion/issues/17771#issuecomment-3332840970
Asked Codex to reduce the bug reproducer, but looks like this one can be further simplified 🤔 Will try to do it later
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm) [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #17771:
URL: https://github.com/apache/datafusion/issues/17771#issuecomment-3395759773
Thank you for the triage! @dqkqd
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm) [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 closed issue #17771: RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm)
URL: https://github.com/apache/datafusion/issues/17771
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] RANK() in CTE with LEFT JOIN + COALESCE fails when filtered and ordered in outer query (SQLStorm) [datafusion]
Posted by "dqkqd (via GitHub)" <gi...@apache.org>.
dqkqd commented on issue #17771:
URL: https://github.com/apache/datafusion/issues/17771#issuecomment-3392929602
I bisected and found that this is fixed by #17419
```
> WITH part AS (
SELECT *
FROM (VALUES (1, 'A'), (2, 'B')) AS t(partkey, name)
),
RevenueCTE AS (
SELECT partkey, total_revenue
FROM (VALUES (1, 10.0), (2, 5.0)) AS t(partkey, total_revenue)
),
SupplierCTE AS (
SELECT partkey, total_supply_cost
FROM (VALUES (1, 2.0), (2, 3.0)) AS t(partkey, total_supply_cost)
),
RankedParts AS (
SELECT
p.partkey,
p.name,
COALESCE(r.total_revenue, 0) AS total_revenue,
COALESCE(s.total_supply_cost, 0) AS total_supply_cost,
RANK() OVER (
ORDER BY COALESCE(r.total_revenue, 0) DESC
) AS revenue_rank
FROM part AS p
LEFT JOIN RevenueCTE AS r ON p.partkey = r.partkey
LEFT JOIN SupplierCTE AS s ON p.partkey = s.partkey
)
SELECT
partkey,
name,
total_revenue,
total_supply_cost,
CASE
WHEN total_supply_cost > 0 THEN total_revenue / total_supply_cost
ELSE NULL
END AS ratio
FROM RankedParts
WHERE revenue_rank <= 10
ORDER BY total_revenue DESC;
+---------+------+---------------+-------------------+--------------------+
| partkey | name | total_revenue | total_supply_cost | ratio |
+---------+------+---------------+-------------------+--------------------+
| 1 | A | 10.0 | 2.0 | 5.0 |
| 2 | B | 5.0 | 3.0 | 1.6666666666666667 |
+---------+------+---------------+-------------------+--------------------+
2 row(s) fetched.
Elapsed 0.039 seconds.
>
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org