round function inconsistent

classic Classic list List threaded Threaded
36 messages Options
12
Reply | Threaded
Open this post in threaded view
|

round function inconsistent

Hajo Bruns
Hi,
the round function seems to round inconsistently:

ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
gives
5,49      3,56      3,25

Last result should be 3.26
Used Version
3.24.0
(sqlite interop.dll)


Plan Software GmbH
Martin-Luther Str. 20 * D-66111 Saarbrücken * Germany
phone +49 (0) 681 / 37927-248
fax +49 (0) 681 / 37927-29
mailto:[hidden email]
Geschäftsführer: Axel Biewer, Mesut Cengiz, Kevin Dewi, Michael Wille
Amtsgericht Saarbrücken, HRB 9989




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Richard Hipp-3
On 5/24/19, Hajo Bruns <[hidden email]> wrote:
> Hi,
> the round function seems to round inconsistently:
>
> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
> gives
> 5,49      3,56      3,25
>
> Last result should be 3.26

3.255 cannot be exactly represented as an IEEE754 double-precision
binary floating point number.  So the system has to use an
approximation.  The closest approximation is
3.25499999999999989341858963598497211933135986328125 and that value
rounds to 3.25.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Radovan Antloga-2
In Delphi I have 2 functions that works (I override default sqlite round):

const
   ExtEps = 1.0842021725E-19;
   DblEps = 2.2204460493E-16;
   KnownErrorLimit = 1.234375;
   SafetyFactor = 2;
   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2):
Extended;
var
   E: Extended;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
var
   E: Double;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan



On 24.05.2019 13:13, Richard Hipp wrote:

> On 5/24/19, Hajo Bruns <[hidden email]> wrote:
>> Hi,
>> the round function seems to round inconsistently:
>>
>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
>> gives
>> 5,49      3,56      3,25
>>
>> Last result should be 3.26
> 3.255 cannot be exactly represented as an IEEE754 double-precision
> binary floating point number.  So the system has to use an
> approximation.  The closest approximation is
> 3.25499999999999989341858963598497211933135986328125 and that value
> rounds to 3.25.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Radovan Antloga
In reply to this post by Richard Hipp-3
In Delphi I have 2 functions that works (I override default sqlite round):

const
   ExtEps = 1.0842021725E-19;
   DblEps = 2.2204460493E-16;
   KnownErrorLimit = 1.234375;
   SafetyFactor = 2;
   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;

function RoundExt(const AValue: Extended; const ADigit: Integer = -2):
Extended;
var
   E: Extended;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
end;

function RoundDbl(const AValue: Double; const ADigit: Integer = -2):
Double;
var
   E: Double;
begin
   E := IntPower(10, -ADigit);
   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
end;

You could implement it in sqlite.

Regards Radovan


On 24.05.2019 13:13, Richard Hipp wrote:

> On 5/24/19, Hajo Bruns <[hidden email]> wrote:
>> Hi,
>> the round function seems to round inconsistently:
>>
>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
>> gives
>> 5,49      3,56      3,25
>>
>> Last result should be 3.26
> 3.255 cannot be exactly represented as an IEEE754 double-precision
> binary floating point number.  So the system has to use an
> approximation.  The closest approximation is
> 3.25499999999999989341858963598497211933135986328125 and that value
> rounds to 3.25.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Alessandro Merolli
Great! Now use SQLite API and add a new user defined function for your used case.
I suppose that SQlite should always follow a well-defined pattern: in this case as Mr. Hipp said, it is IEEE754.
Cheers!

> On 24 May 2019, at 08:27, radovan5 <[hidden email]> wrote:
>
> In Delphi I have 2 functions that works (I override default sqlite round):
>
> const
>   ExtEps = 1.0842021725E-19;
>   DblEps = 2.2204460493E-16;
>   KnownErrorLimit = 1.234375;
>   SafetyFactor = 2;
>   MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
>   MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;
>
> function RoundExt(const AValue: Extended; const ADigit: Integer = -2): Extended;
> var
>   E: Extended;
> begin
>   E := IntPower(10, -ADigit);
>   Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
> end;
>
> function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
> var
>   E: Double;
> begin
>   E := IntPower(10, -ADigit);
>   Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
> end;
>
> You could implement it in sqlite.
>
> Regards Radovan
>
>
> On 24.05.2019 13:13, Richard Hipp wrote:
>> On 5/24/19, Hajo Bruns <[hidden email]> wrote:
>>> Hi,
>>> the round function seems to round inconsistently:
>>>
>>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
>>> gives
>>> 5,49      3,56      3,25
>>>
>>> Last result should be 3.26
>> 3.255 cannot be exactly represented as an IEEE754 double-precision
>> binary floating point number.  So the system has to use an
>> approximation.  The closest approximation is
>> 3.25499999999999989341858963598497211933135986328125 and that value
>> rounds to 3.25.
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4
In reply to this post by Richard Hipp-3

Dr. Richard Hipp, on Friday, May 24, 2019 07:13 AM, wrote...

>> Last result should be 3.26
>
>3.255 cannot be exactly represented as an IEEE754 double-precision
>binary floating point number.  So the system has to use an
>approximation.  The closest approximation is
>3.25499999999999989341858963598497211933135986328125 and that value
>rounds to 3.25.

I actually use lots of rounding in SQLite such as this one (please ignore the extra code--not enough time to clean it up),

  char[] cmd;
  cmd ~= "\nBEGIN;";
  cmd ~= "\nUPDATE LSOpenJobs SET ProjFund = " ~
    "\n(" ~
    "\n  SELECT " ~
    "\n  ( " ~
    "\n    CASE " ~
    "\n    WHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
    "\n      round(sum(t2.ProjFund) * cast(LSOpenJobs.XtraD as integer)/100.0, 2) " ~
    "\n    ELSE " ~
    "\n      LSOpenJobs.ProjFund " ~
    "\n    END " ~
    "\n  ) " ~
    "\n  FROM LSOpenJobs t2 " ~
    "\n    WHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
    "\n    AND t2.PSubClass != 'Portal-Fee' " ~
    "\n    AND t2.PSubClass != 'Rush-Job' " ~
    "\n) " ~
    "\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
  cmd ~= "\nUPDATE LSOpenJobs SET Xtra8 = " ~
    "\n(" ~
    "\n  SELECT " ~
    "\n  ( " ~
    "\n    CASE " ~
    "\n    WHEN instr(LSOpenJobs.XtraD,'%') > 0 THEN " ~
    "\n      round(sum(t2.Xtra8) * cast(LSOpenJobs.XtraD as integer)/100.0, 2) " ~
    "\n    ELSE " ~
    "\n      LSOpenJobs.Xtra8 " ~
    "\n    END " ~
    "\n  ) " ~
    "\n  FROM LSOpenJobs t2 " ~
    "\n    WHERE LSOpenJobs.ProjID = t2.ProjID AND LSOpenJobs.lang = t2.lang " ~
    "\n    AND t2.PSubClass != 'Portal-Fee' " ~
    "\n    AND t2.PSubClass != 'Rush-Job' " ~
    "\n) " ~
    "\nWHERE ProjID = " ~ pid ~ " AND PSubClass = 'Rush-Job';";
  cmd ~= "\nEND;";

and I was told by some of the users that our tool calculations was missing cents here and there in the quotes.  I said, "Blasphemy! SQLite does not makes mistakes!" and now, that I see this, I have to apologize and ask them for their forgiveness. :-)  I was blaming Excel and whatever else they used before inserting data into SQLite.  But now, I see that I was wrong.

Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Simon Slavin-3
On 24 May 2019, at 1:30pm, Jose Isaias Cabrera <[hidden email]> wrote:

> Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  Thanks.

As Dr. Hipp wrote, round was giving the right answer.  All you need to do is pass the number 3.255 as the parameter.  If you're passing the wrong number, don't blame SQLite for getting the wrong result.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4
In reply to this post by Alessandro Merolli

Alessandro Merolli, on Friday, May 24, 2019 07:39 AM, wrote...

> Great! Now use SQLite API and add a new user defined function for
> your used case.
> I suppose that SQlite should always follow a well-defined pattern: in
> this case as Mr. Hipp said, it is IEEE754.

If IEEE754 can't figure out how to round 3.555 to 2 decimals, I don't know if I'd trust it. ;-) thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3

Simon Slavin, on Friday, May 24, 2019 08:34 AM, wrote...
>On 24 May 2019, at 1:30pm, Jose Isaias Cabrera <[hidden email]> wrote:
>
>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.
>
>As Dr. Hipp wrote, round was giving the right answer.  All you need to do
> is pass the number 3.255 as the parameter.  If you're passing the wrong
> number, don't blame SQLite for getting the wrong result.

Ok, maybe I am wrong, but to round 3.255 to two decimal points, should be 3.26, correct?  If SQLite result is 3.25, then it's wrong.  Am I wrong on this?  Sorry, I know a little math, but I don't get as deep as you guys do.  Please correct my lack of knowledge.  I am serious.  I don't understand the reasoning behind it.  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Richard Hipp-3
In reply to this post by Jose Isaias Cabrera-4
On 5/24/19, Jose Isaias Cabrera <[hidden email]> wrote:
>
> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.
>

Consider these two queries:

   SELECT round(3.255,2);
   SELECT round(3.2549999999999998,2);

Do you expect them to give different answers?

If so, do you realize that 3.255 and 3.49999999999998 are in fact the
exact same floating point number?  That number in (unambiguous) hex
notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
the round() function to return different answers for two cases where
it is given bit-for-bit identical inputs?  How does it know which
answer to give?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Radovan Antloga
In reply to this post by Alessandro Merolli
Well I do. But when I use sqlite in PHP I have default round.

Radovan

On 24.05.2019 13:39, Alessandro Merolli wrote:

> Great! Now use SQLite API and add a new user defined function for your used case.
> I suppose that SQlite should always follow a well-defined pattern: in this case as Mr. Hipp said, it is IEEE754.
> Cheers!
>
>> On 24 May 2019, at 08:27, radovan5 <[hidden email]> wrote:
>>
>> In Delphi I have 2 functions that works (I override default sqlite round):
>>
>> const
>>    ExtEps = 1.0842021725E-19;
>>    DblEps = 2.2204460493E-16;
>>    KnownErrorLimit = 1.234375;
>>    SafetyFactor = 2;
>>    MaxRelErrDbl = DblEps * KnownErrorLimit * SafetyFactor;
>>    MaxRelErrExt = ExtEps * KnownErrorLimit * SafetyFactor;
>>
>> function RoundExt(const AValue: Extended; const ADigit: Integer = -2): Extended;
>> var
>>    E: Extended;
>> begin
>>    E := IntPower(10, -ADigit);
>>    Result := Round(AValue * (1 + MaxRelErrExt) * E) / E;
>> end;
>>
>> function RoundDbl(const AValue: Double; const ADigit: Integer = -2): Double;
>> var
>>    E: Double;
>> begin
>>    E := IntPower(10, -ADigit);
>>    Result := Round(AValue * (1 + MaxRelErrDbl) * E) / E;
>> end;
>>
>> You could implement it in sqlite.
>>
>> Regards Radovan
>>
>>
>> On 24.05.2019 13:13, Richard Hipp wrote:
>>> On 5/24/19, Hajo Bruns <[hidden email]> wrote:
>>>> Hi,
>>>> the round function seems to round inconsistently:
>>>>
>>>> ivesselect round(5.485,2), round (3.555,2),round (3.255,2)
>>>> gives
>>>> 5,49      3,56      3,25
>>>>
>>>> Last result should be 3.26
>>> 3.255 cannot be exactly represented as an IEEE754 double-precision
>>> binary floating point number.  So the system has to use an
>>> approximation.  The closest approximation is
>>> 3.25499999999999989341858963598497211933135986328125 and that value
>>> rounds to 3.25.
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Radovan Antloga
In reply to this post by Simon Slavin-3
Developers have problems using default round function so it is somethink
we need.
So I have:

  ROUTINES FOR ROUNDING IEEE-754 FLOATS TO SPECIFIED NUMBER OF DECIMAL
FRACTIONS

   These routines round input values to fit as closely as possible to an
   output number with desired number of decimal fraction digits.

   Because, in general, numbers with decimal fractions cannot be exactly
   represented in IEEE-754 floating binary point variables, error limits
   are used to determine if the input numbers are intended to represent an
   exact decimal fraction rather than a nearby value.   Thus an error limit
   will be taken into account when deciding that a number input such as
   1.295, which internally is represented 1.29499 99999 …, really should
   be considered exactly 1.295 and that 0.29999 99999 ... really should
   be interpreted as 0.3 when applying the rounding rules.

I just provided 2 functions. But I have many options like (copy from code):

     (Abbr: 'HalfEven'; Dscr: 'Round to nearest or to even whole number '
                              '(a.k.a Bankers) '),
     (Abbr: 'HalfPos' ; Dscr: 'Round to nearest or toward positive'),
     (Abbr: 'HalfNeg' ; Dscr: 'Round to nearest or toward negative'),
     (Abbr: 'HalfDown'; Dscr: 'Round to nearest or toward zero'),
     (Abbr: 'HalfUp'  ; Dscr: 'Round to nearest or away from zero'),
     (Abbr: 'RndNeg'  ; Dscr: 'Round toward negative. (a.k.a. Floor) '),
     (Abbr: 'RndPos'  ; Dscr: 'Round toward positive. (a.k.a. Ceil ) '),
     (Abbr: 'RndDown' ; Dscr: 'Round toward zero. (a.k.a. Trunc) '),
     (Abbr: 'RndUp'   ; Dscr: 'Round away from zero.') );


Radovan

On 24.05.2019 14:34, Simon Slavin wrote:
> On 24 May 2019, at 1:30pm, Jose Isaias Cabrera <[hidden email]> wrote:
>
>> Dr. Hipp, how many more scenarios, where round gives the wrong answer, exist?  Thanks.
> As Dr. Hipp wrote, round was giving the right answer.  All you need to do is pass the number 3.255 as the parameter.  If you're passing the wrong number, don't blame SQLite for getting the wrong result.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4
In reply to this post by Richard Hipp-3

Dr. Richard Hipp, on Friday, May 24, 2019 08:44 AM, wrote...

>> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
>> exist?  Thanks.
>>
>
>Consider these two queries:
>
>   SELECT round(3.255,2);
>   SELECT round(3.2549999999999998,2);
>
>Do you expect them to give different answers?

3.26
3.25

>If so, do you realize that 3.255 and 3.49999999999998 are in fact the
>exact same floating point number?  That number in (unambiguous) hex
>notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
>the round() function to return different answers for two cases where
>it is given bit-for-bit identical inputs?  How does it know which
>answer to give?

Hmmmm.  Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Thomas Kurz
In reply to this post by Richard Hipp-3
Sorry, but even Excel (which usually isn't very good at decimal math) gives correct results:

ROUND(3.255;2) --> 3.26
ROUND(3.25499999999999;2) --> 3.25

Yours is clearly incorrect.


----- Original Message -----
From: Richard Hipp <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Friday, May 24, 2019, 14:44:52
Subject: [sqlite] round function inconsistent

On 5/24/19, Jose Isaias Cabrera <[hidden email]> wrote:

> Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> exist?  Thanks.


Consider these two queries:

   SELECT round(3.255,2);
   SELECT round(3.2549999999999998,2);

Do you expect them to give different answers?

If so, do you realize that 3.255 and 3.49999999999998 are in fact the
exact same floating point number?  That number in (unambiguous) hex
notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
the round() function to return different answers for two cases where
it is given bit-for-bit identical inputs?  How does it know which
answer to give?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Richard Hipp-3
On 5/24/19, Thomas Kurz <[hidden email]> wrote:
> ... decimal math ....

Therein lies your misunderstanding.  SQLite does not do decimal math.
It does binary math, and in particular IEEE754 double-precision binary
math.  And in that numeric system, 3.255 and 3.2549999999999998 are
the exact same number, and hence always round the same.

I do not know what the underlying representation for floating point
numbers is in Excel, but as your experiment shows, it is probably not
IEEE754 double-precision binary.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Chris Locke-3
In reply to this post by Thomas Kurz
>  Yours is clearly incorrect

lol.  "Your software gives a different result to the one I expect,
therefore its wrong."

You are aware that your first example (3.255) probably isn't being stored
internally as a single.
Just because computers work outside your understanding doesn't make them
'incorrect'.

On Fri, May 24, 2019 at 2:15 PM Thomas Kurz <[hidden email]> wrote:

> Sorry, but even Excel (which usually isn't very good at decimal math)
> gives correct results:
>
> ROUND(3.255;2) --> 3.26
> ROUND(3.25499999999999;2) --> 3.25
>
> Yours is clearly incorrect.
>
>
> ----- Original Message -----
> From: Richard Hipp <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Sent: Friday, May 24, 2019, 14:44:52
> Subject: [sqlite] round function inconsistent
>
> On 5/24/19, Jose Isaias Cabrera <[hidden email]> wrote:
>
> > Dr. Hipp, how many more scenarios, where round gives the wrong answer,
> > exist?  Thanks.
>
>
> Consider these two queries:
>
>    SELECT round(3.255,2);
>    SELECT round(3.2549999999999998,2);
>
> Do you expect them to give different answers?
>
> If so, do you realize that 3.255 and 3.49999999999998 are in fact the
> exact same floating point number?  That number in (unambiguous) hex
> notation is 0x1.a0a3d70a3d70ap+1. So how is it that you would expect
> the round() function to return different answers for two cases where
> it is given bit-for-bit identical inputs?  How does it know which
> answer to give?
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Ling, Andy
In reply to this post by Richard Hipp-3
> I do not know what the underlying representation for floating point
> numbers is in Excel, but as your experiment shows, it is probably not
> IEEE754 double-precision binary.
>

Well according to this is does...

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

But that seems to be lying. If I enter the two number into Excel, they behave differently.
So something in Excel "knows"

There are some exceptions listed in that document, but they don't seem to explain
the difference for these two numbers.



**********************************************************************
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4
In reply to this post by Thomas Kurz

Thomas Kurz, on Friday, May 24, 2019 09:13 AM, wrote...
>Sorry, but even Excel (which usually isn't very good at decimal math) gives correct results:
>
>ROUND(3.255;2) --> 3.26
>ROUND(3.25499999999999;2) --> 3.25

FWIW, I went to sqlfiddle [1] and these are the answers for this SQL command:

SELECT round(3.255,2), round(3.2549999999999998,2);

for MySQL 5.6, answer...
round(3.255,2) | round(3.2549999999999998,2)
3.26 | 3.25

PostgreSQL 9.6, answer...
round | round
3.26 | 3.25

SQLite (SQL.js), answer...
round(3.255,2) | round(3.2549999999999998,2)
3.25 | 3.25

MS SQL Server 2017, answer...
 |
3.26 | 3.25

I could not get Oracle to work. But, it looks like MySQL, PostgreSQL and MSSQL provide correct output. By the way, the SQLite version on this site is old (3.15.1). Thanks.

josé

[1] http://sqlfiddle.com/#!9/a0753b/3
[http://sqlfiddle.com/images/fiddle_transparent.png]<http://sqlfiddle.com/#!9/a0753b/3>
SQL Fiddle | A tool for easy online testing and sharing of database problems and their solutions.<http://sqlfiddle.com/#!9/a0753b/3>
Application for testing and sharing SQL queries.
sqlfiddle.com

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Richard Hipp-3
On 5/24/19, Jose Isaias Cabrera <[hidden email]> wrote:

>
> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:
>
> SELECT round(3.255,2), round(3.2549999999999998,2);

I also went to sqlfiddle and did a slightly more realistic scenario:

  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
  INSERT INTO t1(a,b) VALUES(1,3.255);
  INSERT INTO t1(a,b) VALUES(2,3.254999999999999893418589635);
  SELECT a, b FROM t1;

In other words, I made the value actually pass through the database.

For MySQL I got:

    1 3.26
    2 3.26

For SQL Server I got:

    1 3.25
    2 3.25

The query does not work on PostgreSQL, because PG wisely prohibits
using the two-argument around() function on binary floating-point
values, perhaps to prevent discussions such as this one.  In order to
get this to work on PG I had to modify the query as follows:

   SELECT a, round(CAST(b AS NUMERIC),2) from t1

And the result is then:

    1 3.26
    2 3.26

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: round function inconsistent

Jose Isaias Cabrera-4

Dr. Richard Hipp, on Friday, May 24, 2019 01:06 PM, wrote...
>
>I also went to sqlfiddle and did a slightly more realistic scenario:
>
>  CREATE TABLE t1(a INT, b DOUBLE PRECISION);
>  INSERT INTO t1(a,b) VALUES(1,3.255);
>  INSERT INTO t1(a,b) VALUES(2,3.254999999999999893418589635);
>  SELECT a, b FROM t1;
>
>In other words, I made the value actually pass through the database.
Good call...

>For MySQL I got:
>
>    1 3.26
>    2 3.26
>
>For SQL Server I got:
>
>    1 3.25
>    2 3.25
>
>The query does not work on PostgreSQL, because PG wisely prohibits
>using the two-argument around() function on binary floating-point
>values, perhaps to prevent discussions such as this one.  In order to
>get this to work on PG I had to modify the query as follows:
>
>   SELECT a, round(CAST(b AS NUMERIC),2) from t1
>
>And the result is then:
>
>    1 3.26
>    2 3.26

Touché!

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12