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