[MonetDB-users] basic math error?
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.
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:
-- | 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 |
On Wed, Sep 24, 2008 at 02:06:19AM +0200, Stefan Manegold wrote:
The SQL standard says (for division of two exact numerics) The precision and scale of the result of division are implementation defined.
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.
This is okay.
This seems to be a bug in query caching. Niels
-- 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
On this comment
I am not an expert with the sql standard, so I don't know what is meant by 'two exact numerics'. Is the exactness from the 'type', precision or scale? Or does this only apply to numerics that can be promoted/demoted to an integral type, e.g. 635.0000? There are some nasty surprises for anyone doing division where they don't have control over the precision of the input data (such as in an ETL script, interactive data capture from a user or input from a code-generator). Should we file this as a feature request? Finally, these work: select 585.0 / (635.0 + 1.0e-06); select (585.0 + 1.0e-06) / (635); Kirk. Niels Nes wrote:
On Wed, Sep 24, 2008 at 07:19:23AM -0500, Kirk Abbott wrote:
Yes it could be filed as a feature request.
Niels
-- 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
participants (4)
-
Kirk Abbott -
Niels Nes -
Romulo Goncalves -
Stefan Manegold