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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@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@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@cwi.nl