[MonetDB-users] basic math error?

Stefan Manegold Stefan.Manegold at cwi.nl
Wed Sep 24 02:06:19 CEST 2008


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


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

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

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


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




More information about the users-list mailing list