lss602726449 opened a new issue, #1658:
URL: https://github.com/apache/cloudberry/issues/1658
### 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
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
