[MonetDB-users] basic math error?

Niels Nes Niels.Nes at cwi.nl
Wed Sep 24 21:39:18 CEST 2008


On Wed, Sep 24, 2008 at 07:19:23AM -0500, Kirk Abbott wrote:
> On this comment
> > The SQL standard says (for division of two exact numerics)
> >
> > The precision and scale of the result of division are implementation
> > defined.
> >
> >   
> I am not an expert with the sql standard, so  I don't know what is meant 
> by 'two exact numerics'.
> Is the exactness from the 'type', precision or scale? Or does this only 
> apply to numerics that can be promoted/demoted to an integral type, e.g. 
> 635.0000?
The exactness stems from the 'fixed point numerics'.

> 
> There are some nasty surprises for anyone doing division where they 
> don't have control over the precision of the input data (such as in an 
> ETL script, interactive data capture from a user or input from a 
> code-generator).
> 
> Should we file this as a feature request?

Yes it could be filed as a feature request.
> 
> Finally, these work:
> select 585.0 / (635.0 + 1.0e-06);
> select (585.0 + 1.0e-06) / (635);
> 
Niels
> Kirk.
> 
> Niels Nes wrote:
> > On Wed, Sep 24, 2008 at 02:06:19AM +0200, Stefan Manegold wrote:
> >   
> >> Hello Kirk et al.,
> >>
> >> here's a more detailed analysis --- I must confess that I do not know,
> >> whether this behavior is intended and/or whether it is correct according to
> >> the SQL standard --- Niels will hopefully be able to enlighten us soon ...
> >>     
> >
> > The SQL standard says (for division of two exact numerics)
> >
> > The precision and scale of the result of division are implementation
> > defined.
> >
> >   
> >> Apparently, the trailing "0"s are ignored and both literal values are
> >> "silently" treated a integers yielding an integer result:
> >>
> >> sql>select 585.0 / 635.0;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0                    |
> >> +----------------------+
> >> 1 tuple
> >>     
> > Indeed we silently ignore the .0 (we try to put literal values into 
> > the smallest type).
> > And this is probably why the result is unexpected.
> >   
> >> Casting the integer result 0 to a 4-digit numerical:
> >>
> >> sql>select cast(585.0 / 635.0 as numeric(10,4));
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.0000               |
> >> +----------------------+
> >> 1 tuple
> >>     
> > This is okay.
> >   
> >> If we first cast one literal to a numerical, the result has the same type
> >> and precision:
> >>
> >> sql>select cast(585.0 as numeric(10,1)) / 635.0;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.9                  |
> >> +----------------------+
> >> 1 tuple
> >> sql>select 585.0 / cast(635.0 as numeric(10,2));
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.92                 |
> >> +----------------------+
> >> 1 tuple
> >>
> >> Likewise, if we have none-zero training decimals ...:
> >>
> >> sql>select 585.1 / 635.0;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.9                  |
> >> +----------------------+
> >> 1 tuple
> >> sql>select 585.01 / 635.0;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.92                 |
> >> +----------------------+
> >> 1 tuple
> >> sql>select 585.0 / 635.01;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0.92                 |
> >> +----------------------+
> >> 1 tuple
> >>
> >> ... well, with one exception:
> >>
> >> sql>select 585.0 / 635.1;
> >> +----------------------+
> >> | sql_div_single_value |
> >> +======================+
> >> | 0                    |
> >> +----------------------+
> >> 1 tuple
> >>     
> > This seems to be a bug in query caching.
> >
> > Niels
> >   
> >> (Both the "Stable" and the "Current" version of MonetDB/SQL show the same
> >> behavior.)
> >>
> >>
> >> Stefan
> >>
> >>
> >> On Tue, Sep 23, 2008 at 03:27:24PM -0500, Kirk Abbott wrote:
> >>     
> >>> Hello All,
> >>> sql>select 585.0 / 635.0;
> >>> +----------------------+
> >>> | sql_div_single_value |
> >>> +======================+
> >>> | 0                    |
> >>> +----------------------+
> >>>
> >>> and
> >>>
> >>> sql>select cast(585.0 / 635.0 as  numeric(10,4));
> >>> +----------------------+
> >>> | sql_div_single_value |
> >>> +======================+
> >>> | 0.0000               |
> >>> +----------------------+
> >>>
> >>> Is not what I was expecting.
> >>>
> >>> Ideas?
> >>> Kirk.
> >>>
> >>>
> >>> -------------------------------------------------------------------------
> >>> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> >>> Build the coolest Linux based applications with Moblin SDK & win great prizes
> >>> Grand prize is a trip for two to an Open Source event anywhere in the world
> >>> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> >>> _______________________________________________
> >>> MonetDB-users mailing list
> >>> MonetDB-users at lists.sourceforge.net
> >>> https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>>       
> >> -- 
> >> | Dr. Stefan Manegold | mailto:Stefan.Manegold at cwi.nl |
> >> | CWI,  P.O.Box 94079 | http://www.cwi.nl/~manegold/  |
> >> | 1090 GB Amsterdam   | Tel.: +31 (20) 592-4212       |
> >> | The Netherlands     | Fax : +31 (20) 592-4312       |
> >>
> >> -------------------------------------------------------------------------
> >> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> >> Build the coolest Linux based applications with Moblin SDK & win great prizes
> >> Grand prize is a trip for two to an Open Source event anywhere in the world
> >> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> >> _______________________________________________
> >> MonetDB-users mailing list
> >> MonetDB-users at lists.sourceforge.net
> >> https://lists.sourceforge.net/lists/listinfo/monetdb-users
> >>     
> >
> >   
> 
> 
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- 

Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels   e-mail: Niels.Nes at cwi.nl




More information about the users-list mailing list