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

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

Hi Anthony, thanks! in fact, this might not be necessary any more, since after a thorough analysis of our code base and "design", Sjoerd concluded that our code was already safe as it used to be, and consequently reverted my "over-conservative" (and slightly short-sighted) change; cf., http://dev.monetdb.org/hg/MonetDB/rev/642e282e1a32 Thus, as of above changeset, the Feb2013 branch should again behave as the SP3 did (regarding decimal types, that is). Given this, as wel as Sjoerd's recent (post-SP4) heap-cache fix, we plan to release an SP5 bugfix release soon, i.e., (well?) before the end of this month (October). Once again sorry for the inconveniences, and thank you very much for checking and reporting the problems! Best, Stefan ps: We consider announcing our future internal release candidates also on the monetdb-announcements mailing lists, such that enthusiastic users like you can have a pre-view and together we can detect and fix such problems before the actual release. ----- Original Message -----

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 datasets that 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.nl > wrote:

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

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