[DRILL-3254] Average over window functions returns wrong results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.1.0
• Fix Version/s: 1.1.0
• Component/s: Query Planning & Optimization
• Labels: window_function
• Assignee: Mehant Baid
• Reporter: Abhishek Girish
• Created: 05/Jun/15 01:04
• Updated: 29/Jun/15 23:02
• Resolved: 19/Jun/15 18:25

Description

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

Results from Drill:

> 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)

Results from Postgres:

# 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)

Drill returns right results without window functions:

> 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)