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