[MonetDB-users] basic math error?

Kirk Abbott kabbott at daxpy.com
Wed Sep 24 14:19:23 CEST 2008


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





More information about the users-list mailing list