goldmedal commented on issue #11903:
URL: https://github.com/apache/datafusion/issues/11903#issuecomment-2278175889
I did some tests. If I didn't provide any group-by key, the SQL will fail.
```
> select sn, amount, sum(amount) from sales_global_with_pk;
Error during planning: Projection references non-aggregate values:
Expression sales_global_with_pk.sn could not be resolved from available
columns: sum(sales_global_with_pk.amount)
```
However, if I provide at least 1 group-by key, DataFusion will add the
others to the group-by key implicitly.
```
> select sn, amount, sum(amount) from sales_global_with_pk group by sn;
+----+--------+----------------------------------+
| sn | amount | sum(sales_global_with_pk.amount) |
+----+--------+----------------------------------+
| 1 | 50.0 | 50.0 |
| 4 | 100.0 | 100.0 |
| 0 | 30.0 | 30.0 |
| 3 | 200.0 | 200.0 |
| 2 | 75.0 | 75.0 |
+----+--------+----------------------------------+
5 row(s) fetched.
Elapsed 0.005 seconds.
> explain select sn, amount, sum(amount) from sales_global_with_pk group by
sn;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| plan_type | plan
|
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| logical_plan | Aggregate: groupBy=[[sales_global_with_pk.sn,
sales_global_with_pk.amount]], aggr=[[sum(CAST(sales_global_with_pk.amount AS
Float64))]] |
| | TableScan: sales_global_with_pk projection=[sn, amount]
|
| physical_plan | AggregateExec: mode=FinalPartitioned, gby=[sn@0 as sn,
amount@1 as amount], aggr=[sum(sales_global_with_pk.amount)]
|
| | CoalesceBatchesExec: target_batch_size=8192
|
| | RepartitionExec: partitioning=Hash([sn@0, amount@1],
8), input_partitions=8 |
| | RepartitionExec: partitioning=RoundRobinBatch(8),
input_partitions=1
|
| | AggregateExec: mode=Partial, gby=[sn@0 as sn,
amount@1 as amount], aggr=[sum(sales_global_with_pk.amount)]
|
| | MemoryExec: partitions=1, partition_sizes=[1]
|
| |
|
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.002 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: [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]
