You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Abhishek Girish (JIRA)" <ji...@apache.org> on 2015/06/05 03:05:38 UTC
[jira] [Created] (DRILL-3254) Average over window functions returns
wrong results
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)