[jira] [Created] (DRILL-3254) Average over window functions returns wrong results

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

Abhishek Girish created DRILL-3254:
--------------------------------------

             Summary: Average over window functions returns wrong results
                 Key: DRILL-3254
                 URL: https://issues.apache.org/jira/browse/DRILL-3254
             Project: Apache Drill
          Issue Type: Bug
          Components: Query Planning & Optimization
    Affects Versions: 1.1.0
            Reporter: Abhishek Girish
            Assignee: Deneche A. Hakim


Average function on numeric column returns an (inaccurate) integer value, instead of an (accurate) decimal (or floating point) value.

*Results from Drill:*
{code:sql}
> select s_city, s_store_sk, avg(s_number_employees) over (PARTITION BY s_city ORDER BY s_store_sk) from store limit 10;
+-----------+-------------+---------+
|  s_city   | s_store_sk  | EXPR$2  |
+-----------+-------------+---------+
| Fairview  | 5           | 288     |
| Fairview  | 8           | 283     |
| Fairview  | 12          | 286     |
| Midway    | 1           | 245     |
| Midway    | 2           | 240     |
| Midway    | 3           | 239     |
| Midway    | 4           | 233     |
| Midway    | 6           | 232     |
| Midway    | 7           | 243     |
| Midway    | 9           | 247     |
+-----------+-------------+---------+
10 rows selected (0.197 seconds)
{code}

*Results from Postgres:*
{code:sql}
# select s_city, s_store_sk, avg(s_number_employees) over (PARTITION BY s_city ORDER BY s_store_sk) from store limit 10;
  s_city  | s_store_sk |         avg          
----------+------------+----------------------
 Fairview |          5 | 288.0000000000000000
 Fairview |          8 | 283.0000000000000000
 Fairview |         12 | 286.6666666666666667
 Midway   |          1 | 245.0000000000000000
 Midway   |          2 | 240.5000000000000000
 Midway   |          3 | 239.0000000000000000
 Midway   |          4 | 233.7500000000000000
 Midway   |          6 | 232.8000000000000000
 Midway   |          7 | 243.5000000000000000
 Midway   |          9 | 247.4285714285714286
(10 rows)
{code}

Drill returns right results without window functions:
{code:sql}
> select s_city, s_store_sk, avg(s_number_employees) from store group by s_city, s_store_sk order by 1,2 limit 10;
+-----------+-------------+---------+
|  s_city   | s_store_sk  | EXPR$2  |
+-----------+-------------+---------+
| Fairview  | 5           | 288.0   |
| Fairview  | 8           | 278.0   |
| Fairview  | 12          | 294.0   |
| Midway    | 1           | 245.0   |
| Midway    | 2           | 236.0   |
| Midway    | 3           | 236.0   |
| Midway    | 4           | 218.0   |
| Midway    | 6           | 229.0   |
| Midway    | 7           | 297.0   |
| Midway    | 9           | 271.0   |
+-----------+-------------+---------+
10 rows selected (0.306 seconds)
{code}



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