You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "mustafasrepo (via GitHub)" <gi...@apache.org> on 2024/02/13 10:54:00 UTC
[I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
mustafasrepo opened a new issue, #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213
### Is your feature request related to a problem or challenge?
Query below
```sql
SELECT a, ARRAY_AGG(c ORDER BY c)[1] as result
FROM multiple_ordered_table
GROUP BY a;
```
and
```sql
SELECT a, NTH_VALUE(c, 1 ORDER BY c) as result
FROM multiple_ordered_table
GROUP BY a;
```
produces same results. However, first query generates following plan
```
"ProjectionExec: expr=[a@0 as a, (ARRAY_AGG(multiple_ordered_table.c) ORDER BY [multiple_ordered_table.c ASC NULLS LAST]@1).[1] as result]",
" AggregateExec: mode=Single, gby=[a@0 as a], aggr=[ARRAY_AGG(multiple_ordered_table.c)], ordering_mode=Sorted",
" CsvExec: file_groups={1 group: [[CSV_PATH]]}, projection=[a, c], output_orderings=[[a@0 ASC NULLS LAST], [c@1 ASC NULLS LAST]], has_header=true",
```
whereas second query generates following plan
```
"ProjectionExec: expr=[a@0 as a, NTH_VALUE(multiple_ordered_table.c,Int64(1)) ORDER BY [multiple_ordered_table.c ASC NULLS LAST]@1 as result]",
" AggregateExec: mode=Single, gby=[a@0 as a], aggr=[NTH_VALUE(multiple_ordered_table.c,Int64(1))], ordering_mode=Sorted",
" CsvExec: file_groups={1 group: [[CSV_PATH]]}, projection=[a, c], output_orderings=[[a@0 ASC NULLS LAST], [c@1 ASC NULLS LAST]], has_header=true",
```
### Describe the solution you'd like
we can rewrite first query as second one, which executes faster with less memory. Because it no longer needs to keep all results in the array_agg.
### 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: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
Posted by "Lordworms (via GitHub)" <gi...@apache.org>.
Lordworms commented on issue #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213#issuecomment-1956889363
Yes, I truely want to add some general approach instead of writing some "if else", I would try to work this out
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
Posted by "Lordworms (via GitHub)" <gi...@apache.org>.
Lordworms commented on issue #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213#issuecomment-1951571163
Hello @mustafasrepo could you give me the actual table creation SQL for this issue?
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
Posted by "Lordworms (via GitHub)" <gi...@apache.org>.
Lordworms commented on issue #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213#issuecomment-1955235686
take
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
Posted by "mustafasrepo (via GitHub)" <gi...@apache.org>.
mustafasrepo commented on issue #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213#issuecomment-1951953841
> Hello @mustafasrepo could you give me the actual table creation SQL for this issue?
Sure,
```sql
CREATE EXTERNAL TABLE multiple_ordered_table (
a0 INTEGER,
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER
)
STORED AS CSV
WITH HEADER ROW
WITH ORDER (a ASC, b ASC)
WITH ORDER (c ASC)
LOCATION '../core/tests/data/window_2.csv';
```
you can create the table in the queries with above snippet
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Convert indexed ARRAY_AGG to NTH_VALUE [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #9213:
URL: https://github.com/apache/arrow-datafusion/issues/9213#issuecomment-1956034509
As @Lordworms points out, maybe we can try to implement this feature in some way that is general and not special cased in the optimzer -- aka https://github.com/apache/arrow-datafusion/issues/9289
This would look like
1. Making `ARRAY_AGG` an `AggregateUDF` (which probably would mean making a `datafusion-aggregates` crate
2. Adding an API to do the rewrite
This would certainly take more work and thus more time than just implementing a special case for the BuiltInAggregateFunction, so I don't think it is necessary
However, if we think this is a reasonbale approach I can file some tickets with the basic ideas sketeched out (I didn't want to sketch out too many things at once and we already have a bunch of work related to pulling out scalar UDFs)
--
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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org