Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3254

Average over window functions returns wrong results

    Details

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              mehant Mehant Baid
              Reporter:
              agirish Abhishek Girish
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: