[MonetDB-users] basic math error?

Niels Nes Niels.Nes at cwi.nl
Wed Sep 24 09:21:15 CEST 2008


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

-- 

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