[jira] [Created] (DRILL-2438) Query on views with Avg on integer column returns wrong result

["Abhishek Girish (JIRA)" <ji...@apache.org>]

Abhishek Girish created DRILL-2438:
--------------------------------------

             Summary: Query on views with Avg on integer column returns wrong result
                 Key: DRILL-2438
                 URL: https://issues.apache.org/jira/browse/DRILL-2438
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 0.8.0
            Reporter: Abhishek Girish
            Assignee: Jinfeng Ni


Git.Commit.ID: b3bdc27 (Mar 10)

Average on an integer column returns an (inaccurate) integer value, instead of an (accurate) decimal value. 

*The following query returns wrong results:*
{code:sql}
> SELECT i_item_id, avg(i_manufact_id) agg1
. . . . . . . . . . . . . . . . . > FROM item
. . . . . . . . . . . . . . . . . > GROUP  BY i_item_id 
. . . . . . . . . . . . . . . . . > ORDER  BY i_item_id
. . . . . . . . . . . . . . . . . > LIMIT 5; 
+------------+------------+
| i_item_id  |    agg1    |
+------------+------------+
| AAAAAAAAAAABAAAA | 152        |
| AAAAAAAAAAACAAAA | 187        |
| AAAAAAAAAAAEAAAA | 251        |
| AAAAAAAAAABAAAAA | 199        |
| AAAAAAAAAABBAAAA | 636        |
+------------+------------+
5 rows selected (0.324 seconds)
{code}

*Postgres results:*
{code:sql}
# SELECT i_item_id, avg(i_manufact_id) agg1
tpcds1_new-# FROM item
tpcds1_new-# GROUP  BY i_item_id 
tpcds1_new-# ORDER  BY i_item_id
tpcds1_new-# LIMIT 5; 
    i_item_id     |         agg1         
------------------+----------------------
 AAAAAAAAAAABAAAA | 152.3333333333333333
 AAAAAAAAAAACAAAA | 373.0000000000000000
 AAAAAAAAAAAEAAAA | 251.0000000000000000
 AAAAAAAAAABAAAAA | 198.6666666666666667
 AAAAAAAAAABBAAAA | 636.0000000000000000
(5 rows)
{code}



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