mustafasrepo opened a new issue, #6159:
URL: https://github.com/apache/arrow-datafusion/issues/6159
### Is your feature request related to a problem or challenge?
Datafusion can detect aliased columns (exactly same columns). This enables
us to do additional optimizations during planning. However, same thing cannot
be done in terms of ordering.
Consider query below
```sql
(SELECT c9,
ROW_NUMBER() OVER(ORDER BY c9 ASC) as rn1
FROM aggregate_test_100
ORDER BY c9 ASC)
```
Output of this query would satisfy `c9 ASC`. However, it will also satisfy
`rn1 ASC`. However, currently we cannot detect this
query below
```sql
SELECT c9, rn1 FROM (SELECT c9,
ROW_NUMBER() OVER(ORDER BY c9 ASC) as rn1
FROM aggregate_test_100
ORDER BY c9 ASC)
ORDER BY rn1
```
produces the following plan
```
"SortExec: expr=[rn1@1 ASC NULLS LAST]",
" ProjectionExec: expr=[c9@0 as c9, ROW_NUMBER() ORDER BY
[aggregate_test_100.c9 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW@1 as rn1]",
" BoundedWindowAggExec: wdw=[ROW_NUMBER(): Ok(Field { name:
\"ROW_NUMBER()\", data_type: UInt64, nullable: false, dict_id: 0,
dict_is_ordered: false, metadata: {} }), frame: WindowFrame { units: Range,
start_bound: Preceding(UInt32(NULL)), end_bound: CurrentRow }], mode=[Sorted]",
" SortExec: expr=[c9@0 ASC NULLS LAST]",
" CsvExec: files={1 group: [[SOURCE_PATH]]}, has_header=true,
limit=None, projection=[c9]",
```
We could have produced the following if were to detect that `c9 ASC` and
`rn1 ASC` defines same ordering.
```
"ProjectionExec: expr=[c9@0 as c9, ROW_NUMBER() ORDER BY
[aggregate_test_100.c9 ASC NULLS LAST] RANGE BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW@1 as rn1]",
" BoundedWindowAggExec: wdw=[ROW_NUMBER(): Ok(Field { name:
\"ROW_NUMBER()\", data_type: UInt64, nullable: false, dict_id: 0,
dict_is_ordered: false, metadata: {} }), frame: WindowFrame { units: Range,
start_bound: Preceding(UInt32(NULL)), end_bound: CurrentRow }], mode=[Sorted]",
" SortExec: expr=[c9@0 ASC NULLS LAST]",
" CsvExec: files={1 group: [[SOURCE_PATH]]}, has_header=true,
limit=None, projection=[c9]",
```
### Describe the solution you'd like
I would like to have this support.
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
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]
