[jira] [Resolved] (ASTERIXDB-1396) sum function used after group by throws error

["Yingyi Bu (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/ASTERIXDB-1396?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Yingyi Bu resolved ASTERIXDB-1396.
----------------------------------
    Resolution: Invalid

This is not a bug, but by design. 
AQL's group-by aggregation semantics is different from SQL's.  An aggregation function takes a collection as the input and output a scalar value.  In your query, $t becomes plural after the group-by clause, therefore field-access is not a valid over that.

You can express your intent by:

for $t in dataset TripData
group by $timebin := interval-bin($t.tpep_dropoff_datetime, datetime("2013-01-01T00:00:00.000"), day-time-duration("PT2H")) with $t
let $num := sum(for $i in $t return $i.passenger_count)
return
{ "time_interval": $timebin, "count": $num}


> sum function used after group by throws error
> ---------------------------------------------
>
>                 Key: ASTERIXDB-1396
>                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-1396
>             Project: Apache AsterixDB
>          Issue Type: Bug
>            Reporter: Xi Zhang
>
> Here is my query:
> for $t in dataset TripData
> group by $timebin := interval-bin($t.tpep_dropoff_datetime, datetime("2013-01-01T00:00:00.000"), day-time-duration("PT2H")) with $t
> let $num := sum($t.passenger_count)
> return { "time_interval": $timebin, "count": $num}
> *Error message is:*
> Unsupported type [ TripDataType: closed {
>   id: UUID,
>   vendor_id: INT8,
>   tpep_pickup_datetime: DATETIME,
>   tpep_dropoff_datetime: DATETIME,
>   passenger_count: INT8
>   ...
> }
>  ] for field access expression: function-call: asterix:field-access-by-name, Args:[%0->$$9, AString: {passenger_count}] [AlgebricksException]



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)