You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Victoria Markman (JIRA)" <ji...@apache.org> on 2015/06/30 01:03:04 UTC
[jira] [Closed] (DRILL-3254) Average over window functions returns
wrong results
[ https://issues.apache.org/jira/browse/DRILL-3254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Victoria Markman closed DRILL-3254.
-----------------------------------
> 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: Mehant Baid
> Labels: window_function
> Fix For: 1.1.0
>
> Attachments: DRILL-3254.patch
>
>
> 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)