[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.
I think this example gives you the answer:
select 4/5;
&1 0 1 1 1 % . # table_name % sql_div_single_value # name % tinyint # type % 1 # length [ 0 ]
select 4%5;
&1 0 1 1 1 % . # table_name % mod_single_value # name % tinyint # type % 1 # length [ 4 ]
/ divide % modulo
Romulo
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
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
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
On this comment
The SQL standard says (for division of two exact numerics)
The precision and scale of the result of division are implementation defined.
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 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
On Wed, Sep 24, 2008 at 07:19:23AM -0500, Kirk Abbott wrote:
On this comment
The SQL standard says (for division of two exact numerics)
The precision and scale of the result of division are implementation defined.
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?
The exactness stems from the 'fixed point numerics'.
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?
Yes it could be filed as a feature request.
Finally, these work: select 585.0 / (635.0 + 1.0e-06); select (585.0 + 1.0e-06) / (635);
Niels
Kirk.
Niels Nes wrote:
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
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
participants (4)
-
Kirk Abbott -
Niels Nes -
Romulo Goncalves -
Stefan Manegold