Re: Overflow detection in Drill
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 <kfar...@maprtech.com> 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.6668 | > | 3 | -16377.5 | > | 4 | -13101.2 | > | 5 | -10916.8334 | > | 6 | -9356.42857142857 | > | 7 | -8186.0 | > | 8 | -7275.5556| > | 13 | -6546.7 | > | 19 | -5949.818181818182| > | 9 | -5453.25 | > | 1 | -5033.692307692308| > | 65535 | 6.928571428571429 | > | 2 | 6.6 | > | 10 | 6.8125| > | 1000| 588241.7058823529 | > | 1073741823 | 6.0207885E7 | > | 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 <kfar...@maprtech.com> > 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
Re: Overflow detection in Drill
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.6668 | | 3 | -16377.5 | | 4 | -13101.2 | | 5 | -10916.8334 | | 6 | -9356.42857142857 | | 7 | -8186.0 | | 8 | -7275.5556| | 13 | -6546.7 | | 19 | -5949.818181818182| | 9 | -5453.25 | | 1 | -5033.692307692308| | 65535 | 6.928571428571429 | | 2 | 6.6 | | 10 | 6.8125| | 1000| 588241.7058823529 | | 1073741823 | 6.0207885E7 | | 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 <kfar...@maprtech.com> 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 | > | 1000 | 4967232.5| > | 0| 3311488.35 | > | 13 | 2483619.5| > | 23 | 1986900.2| > | 999 | 3322416.65 | > | 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. > 9223372036854775807 | 4611686018427390404 >65534 | 3074457345618282114 > -1 | 2305843009213711585 >1 | 1844674407370969268 > 17 | 1537228672809141060 > 1000 | 1317624576693549623 > 200 | 1152921504606855945 > 4611686018427387903 | 1537228672809137273 > 1001 | 1383505805528223646 > 30 | 1257732550480203317 > -65535 | -65535. > 1000 | 4967232.5000 >0 | 3311488.333
Overflow detection in Drill
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 | | 1000 | 4967232.5| | 0| 3311488.35 | | 13 | 2483619.5| | 23 | 1986900.2| | 999 | 3322416.65 | | 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. 9223372036854775807 | 4611686018427390404 65534 | 3074457345618282114 -1 | 2305843009213711585 1 | 1844674407370969268 17 | 1537228672809141060 1000 | 1317624576693549623 200 | 1152921504606855945 4611686018427387903 | 1537228672809137273 1001 | 1383505805528223646 30 | 1257732550480203317 -65535 | -65535. 1000 | 4967232.5000 0 | 3311488. 13 | 2483619.5000 23 | 1986900.2000 999 | 3322416.6667 197 | 2847813.857142857143 9223372036854775806 | 1152921504609338813 92233720385475807 | 1035067306362242923 25 |931560575726018634 3000 |846873250660017212 (22 rows) {noformat} Thanks, Khurram