hi Stefan, i unfortunately cannot provide this data set -- but once the other SP4 bugs are fixed, i'll be on the lookout for it in one of the public use datasetshttps://github.com/ajdamico/usgsd/search?q=MonetDB.R&ref=cmdformthat i use frequently. here's the structure--
schema table column type location count typewidth columnsize heapsize indices sorted sys x11 bene_sex_ident_cd varchar 02\02\20253 2588595 1 2588595 524288 0 FALSE
On Thu, Oct 3, 2013 at 3:30 AM, Stefan Manegold Stefan.Manegold@cwi.nlwrote:
Hi Anthony,
to explain: it's not (only) about the extreme values, but also about the (potential) number of decimal digits both left and right of the decimal point. Recall that multiplying two decimal numbers can potentially result in a number that requires the sum of decimal digits of both input both left and right of the decimal point, e.g., 9.99 * 99.9 = 998.001 (i.e., 1+2=3 & 2+1=3, thus, while the operands require only 3 digits, the result requires 6).
Having said that, could you possibly provide us with all the details (schema, data) to be able to reproduce the effect ourselves? Than, we could check whether our calculation of potentially required digits is correct or not.
Thanks! Stefan
On Mon, Sep 30, 2013 at 12:14:05PM -0400, Anthony Damico wrote:
thanks Stefan..i don't really understand how this all works, but would adding this functionality -- http://bugs.monetdb.org/show_bug.cgi?id=3378-- circumvent the problem? i don't see why the numbers i'm creating are close to the extreme, the data table only has about 2 million records :/
On Mon, Sep 30, 2013 at 11:55 AM, Stefan Manegold <
Stefan.Manegold@cwi.nl>wrote:
Hi Anthony,
thanks for reporting and sorry for the inconvenience.
The change in behaviour is most probably triggered by this checkin: http://dev.monetdb.org/hg/MonetDB/rev/f8f1b9c98a40
The background it that we used to allow up to 19 digits for (signed) decimals stored as 64-bit (signed) integers in MonetDB.
However, with the smallest/largest signed 64-bit value being -9223372036854775808 (-(2^63)) and 9223372036854775807 ((2^63)-1), respectively, i.e., the
smallest/largest
fitting values with "all 9" are 18 digits long: -999999999999999999 and 999999999999999999
allowing 19 digits, meant that we (silently and without check) hoped that only about
92.23% of
the value range (9223372036854775807/9999999999999999999) were used, and the remaining ~7.77% were not used.
Above checkin opts for the more conservative solution to only allow
the 18
digits that safely fit.
If in your case SP3 did yield the correct results, it means that the actual values in your case do fit in the "safe" 92.23% of the 19-digit range.
We will check, whether we can safely (re-)allow 19 digits by checking
the
actual value range rather than (only) the number of digits --- at the "expense" that only 92.23% of the full 19-digit range can actually be used.
Best, Stefan
----- Original Message -----
the workaround for this appears to be
dbGetQuery( db , "select sum( CAST( ( CAST( bene_sex_ident_cd = 1
AS
INTEGER ) ) AS DOUBLE ) ) / CAST( count( * ) AS DOUBLE ) as
pct_male
from
x11" )
but needing to do this is new as of SP4.. is there a reason why, and
could
this be fixed? thank you :)
On Mon, Sep 30, 2013 at 8:42 AM, Anthony Damico < ajdamico@gmail.com>
wrote:
this issue is new for SP-4 ..i have been using this syntax for a long
time
and never encountered this problem until the upgrade :(
dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) ) ) / (
count( *
) ) as pct_male from x11" )
pct_male 1 0
dbGetQuery( db , "select ( sum( ( bene_sex_ident_cd = 1 ) )*1.000
) / (
count( * )*1.000 ) as pct_male from x11" )
Error in .local(conn, statement, ...) : Unable to execute statement 'select ( sum( ( bene_sex_ident_cd = 1 )
)*1.000
) / ( count( * )*1.000 ) as pct_male from x11'. Server says 'too many digits (19 > 18)' [#22003].
what diagnostics can i run for you to troubleshoot this? thanks!! :)
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list