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