[Bug] orca incorrectly eliminated the sorting #1658
Description
Apache Cloudberry version
main branch
What happened
postgres=# explain WITH samples AS (
SELECT random() r FROM generate_series(1, 1000) ORDER BY 1
), indexed_samples AS (
SELECT (row_number() OVER())-1.0 i, r FROM samples
)
select * from indexed_samples;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Sequence (cost=0.00..431.16 rows=1000 width=16) (operatorMem: 100kB)
-> Shared Scan (share slice:id 0:0) (cost=0.00..0.11 rows=1000 width=1) (operatorMem: 231kB)
-> Function Scan on generate_series (cost=0.00..0.00 rows=1000 width=1) (operatorMem: 231kB)
-> WindowAgg (cost=0.00..431.02 rows=1000 width=16) (operatorMem: 231kB)
-> Shared Scan (share slice:id 0:0) (cost=0.00..431.02 rows=1000 width=8) (operatorMem: 231kB)
Optimizer: GPORCA
(6 rows)
postgres=# explain WITH samples AS (
SELECT random() r FROM generate_series(1, 1000) ORDER BY 1
), indexed_samples AS (
SELECT (row_number() OVER())-1.0 i, r FROM samples
)
select * from indexed_samples;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
WindowAgg (cost=62.33..82.33 rows=1000 width=40) (operatorMem: 512kB)
-> Sort (cost=62.33..64.83 rows=1000 width=8) (operatorMem: 512kB)
Sort Key: (random())
-> Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=8) (operatorMem: 512kB)
Optimizer: Postgres query optimizer
(5 rows)
query result different. For PG, column i is order by random() result, FOR ORCA not.
What you think should happen instead
No response
How to reproduce
run the query
Operating System
rockylinux
Anything else
No response
Are you willing to submit PR?
- Yes, I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct.