[jira] [Resolved] (DRILL-7161) Aggregation with group by clause

["Hefei Li (JIRA)" <ji...@apache.org>]

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

Hefei Li resolved DRILL-7161.
-----------------------------
       Resolution: Not A Bug
    Fix Version/s: 1.14.0

By default, Drill does not support different types of JSON lists. For support on JSON data types, you can refer to the **[JSON Data Model|https://drill.apache.org/docs/json-data-model/].

In this case, the ‘B’ column in your given test data is all null.
When Drill reads the column, it will be processed by default according to the VARCHAR type.
So, if you want to work with numeric types as you expect, you can use the [CAST|https://drill.apache.org/docs/data-type-conversion/] type conversion function provided by Drill.

Such as:
{code:java}
select a, sum(CAST(b as INT)) from dfs.`/drill/data/sample.json` group by a
{code}

Then it will work fine.

> Aggregation with group by clause
> --------------------------------
>
>                 Key: DRILL-7161
>                 URL: https://issues.apache.org/jira/browse/DRILL-7161
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Functions - Drill
>    Affects Versions: 1.14.0
>            Reporter: Gayathri
>            Assignee: Hefei Li
>            Priority: Blocker
>              Labels: Drill, issue
>             Fix For: 1.14.0
>
>
> Facing some issues with the following case:
> Json file (*sample.json*) is having the following content:
> {"a":2,"b":null}
> {"a":2,"b":null}
> {"a":3,"b":null}
> {"a":4,"b":null}
> *Query:*
> SELECT a, sum(b) FROM dfs.`C:\\Users\\user\\Desktop\\sample.json` group by a;
> *Error:*
> UNSUPPORTED_OPERATION ERROR: Only COUNT, MIN and MAX aggregate functions supported for VarChar type
> *Observation:*
> If we query without using group by, then it is working fine without any error. If group by is used, then sum of null values is throwing the above error.
>  
> Can anyone please let us know the solution for this or if there are any alternative.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)