This is a little bit off-topic, but there should be no risk of overflow in
a well-designed computation of the mean.
The simple and obvious algorithm where you add up all the numbers and
divide by the count is simply the wrong way to compute the mean if you want
numerical stability. The method sometimes attributed to Welford is
considerably better and avoids the risk of overflow.
This situation is event worse if you accumulate variance (aka standard
deviation squared) at the same time that you accumulate the mean.
Here are specific examples framed as computations against an array of
values.
*WRONG:*
long count = 0;
double sum = 0;
for (int i = 0; i < x.length; i++) {
count++;
sum += x[i];
}
mean = sum /count;
*VERY WRONG:*
long count = 0;
double sum = 0;
double sum2 = 0;
for (int i = 0; i < x.length; i++) {
count++;
sum += x[i];
sum2 += x[i] * x[i];
}
mean = sum / count;
variance = (sum2 - sum * sum) / count;
*RIGHT:*
long count = 0;
double mean = 0;
double variance = 0;
for (int i = 0; i < x.length; i++) {
count++;
double before = x[i] - mean;
mean += before / count;
double after = x[i] - mean;
variance += (before * after - variance) / count;
}
On Tue, Jul 26, 2016 at 4:39 AM, Khurram Faraaz <[email protected]>
wrote:
> Another example where we don't detect/report overflow
>
> Results from Postgres
>
> postgres=# SELECT col0, AVG(col0) OVER ( ORDER BY col0 + col1 ) avg_col0
> FROM fewrwspqq_101 GROUP BY col0,col1;
>
> ERROR: bigint out of range
>
> postgres=#
>
> Results from Drill 1.8.0
>
> 0: jdbc:drill:schema=dfs.tmp> SELECT col0, AVG(col0) OVER ( ORDER BY col0 +
> col1 ) avg_col0 FROM `allTypsUniq.parquet` GROUP BY col0,col1;
> +-------------+-----------------------+
> | col0 | avg_col0 |
> +-------------+-----------------------+
> | 23 | 23.0 |
> | -1 | 11.0 |
> | -65535 | -21837.666666666668 |
> | 3 | -16377.5 |
> | 4 | -13101.2 |
> | 5 | -10916.833333333334 |
> | 6 | -9356.42857142857 |
> | 7 | -8186.0 |
> | 8 | -7275.555555555556 |
> | 13 | -6546.7 |
> | 19 | -5949.818181818182 |
> | 9 | -5453.25 |
> | 1 | -5033.692307692308 |
> | 65535 | 6.928571428571429 |
> | 2 | 6.6 |
> | 10 | 6.8125 |
> | 10000000 | 588241.7058823529 |
> | 1073741823 | 6.020788511111111E7 |
> | 2147483647 | 1.7006450415789473E8 |
> | 109 | 1.615612844E8 |
> | 29 | 1.538678912857143E8 |
> | 0 | 1.4687389622727272E8 |
> +-------------+-----------------------+
> 22 rows selected (0.341 seconds)
>
>
>
> On Tue, Jul 26, 2016 at 2:07 AM, Khurram Faraaz <[email protected]>
> wrote:
>
> > Hi All,
> >
> > As of today Drill does not handle overflow detection and does not report
> > that was an overflow to users, instead we just return results that are
> > incorrect. This issue has been discussed (but not in detail) in the past.
> >
> > It would be great if Drill also handled overflow detection in data of
> type
> > (int, bigint etc) like other existing DBMSs do. Users will not want to
> see
> > incorrect/wrong results, instead an error that informs users that there
> was
> > an overflow will make more sense.
> >
> > Here is an example of one such query that returns incorrect results as
> > compared to Postgres. Difference in results (related to overflow
> detection
> > problem), col1 is of type BIGINT
> >
> > {noformat}
> > 0: jdbc:drill:schema=dfs.tmp> SELECT col1, AVG(SUM(col1)) OVER (
> PARTITION
> > BY col7 ORDER BY col0 ) FROM `allTypsUniq.parquet` GROUP BY
> col0,col1,col7;
> > +----------------------+--------------------------+
> > | col1 | EXPR$1 |
> > +----------------------+--------------------------+
> > | 5000 | 5000.0 |
> > | 9223372036854775807 | -4.6116860184273853E18 |
> > | 65534 | -3.0744573456182349E18 |
> > | -1 | -2.30584300921367629E18 |
> > | 1 | -1.84467440737094093E18 |
> > | 17 | -1.53722867280911744E18 |
> > | 1000 | -1.31762457669352909E18 |
> > | 200 | -1.15292150460683802E18 |
> > | 4611686018427387903 | -5.1240955760303514E17 |
> > | 1001 | -4.6116860184273152E17 |
> > | 30 | -4.1924418349339232E17 |
> > | -65535 | -65535.0 |
> > | 10000000 | 4967232.5 |
> > | 0 | 3311488.3333333335 |
> > | 13 | 2483619.5 |
> > | 23 | 1986900.2 |
> > | 9999999 | 3322416.6666666665 |
> > | 197 | 2847813.8571428573 |
> > | 9223372036854775806 | -1.1529215046043552E18 |
> > | 92233720385475807 | -1.01457092404992947E18 |
> > | 25 | -9.1311383164493645E17 |
> > | 3000 | -8.3010348331357837E17 |
> > +----------------------+--------------------------+
> > 22 rows selected (0.46 seconds)
> > {noformat}
> >
> > Results from Postgres
> >
> > {noformat}
> > postgres=# SELECT col1, AVG(SUM(col1)) OVER ( PARTITION BY col7 ORDER BY
> > col0 ) FROM fewrwspqq_101 GROUP BY col0,col1,col7;
> > col1 | avg
> > ---------------------+-----------------------
> > 5000 | 5000.0000000000000000
> > 9223372036854775807 | 4611686018427390404
> > 65534 | 3074457345618282114
> > -1 | 2305843009213711585
> > 1 | 1844674407370969268
> > 17 | 1537228672809141060
> > 1000 | 1317624576693549623
> > 200 | 1152921504606855945
> > 4611686018427387903 | 1537228672809137273
> > 1001 | 1383505805528223646
> > 30 | 1257732550480203317
> > -65535 | -65535.000000000000
> > 10000000 | 4967232.500000000000
> > 0 | 3311488.333333333333
> > 13 | 2483619.500000000000
> > 23 | 1986900.200000000000
> > 9999999 | 3322416.666666666667
> > 197 | 2847813.857142857143
> > 9223372036854775806 | 1152921504609338813
> > 92233720385475807 | 1035067306362242923
> > 25 | 931560575726018634
> > 3000 | 846873250660017212
> > (22 rows)
> > {noformat}
> >
> > Thanks,
> > Khurram
> >
>
