Example/recipe for truncating fp numbers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Example/recipe for truncating fp numbers

jm cuaz
Hello,

Below is a recipe on a "best effort" basis, to truncate fp numbers on
the right side of the decimal separator with SQLite.

It is not intended to correct any fp numbers processing, but only to
discard, without any rounding, unwanted fractional digits.

The following is directed to discard all digits of a number after the
third fractional position (slight and easy adjustments must be done for
different positional truncations) :

     round(  CAST(/expression/ AS INTEGER)

               + /((expression/  * 1000 % 1000 ) / 1000 ), 3)

(round() is used against eventually imprecise representation resulting
from the top level addition operation)

Caveat : force an eventual INTEGER representation resulting from
/expression/ to its equivalent REAL representation (ie : x -> x.0)

(not a pb for me)

Improvements/comments welcome (and wil be happy when an equivalent
function finds its way into SQLite)

Thanks.

-jm






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

Simon Slavin-3


On 10 Jul 2017, at 6:17pm, Jean-Marie CUAZ <[hidden email]> wrote:

> Improvements/comments welcome

I would suggest you try an equivalent function, starting by turning the number into a string and looking for the decimal point in it.  This may or may not work better, but a second way of doing things is always interesting.

>  (and wil be happy when an equivalent function finds its way into SQLite)

As I’m sure you know by now, the developers of SQLite consider that SQLite is for store and recall, and that processing should be done by your own code (or "awk" or whatever you use).

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

Re: Example/recipe for truncating fp numbers

Keith Medcalf
In reply to this post by jm cuaz

Why not just use:

  cast(value * 1000 as integer) / 1000.0


> Hello,
>
> Below is a recipe on a "best effort" basis, to truncate fp numbers on
> the right side of the decimal separator with SQLite.
>
> It is not intended to correct any fp numbers processing, but only to
> discard, without any rounding, unwanted fractional digits.
>
> The following is directed to discard all digits of a number after the
> third fractional position (slight and easy adjustments must be done for
> different positional truncations) :
>
>      round(  CAST(/expression/ AS INTEGER)
>
>                + /((expression/  * 1000 % 1000 ) / 1000 ), 3)
>
> (round() is used against eventually imprecise representation resulting
> from the top level addition operation)
>
> Caveat : force an eventual INTEGER representation resulting from
> /expression/ to its equivalent REAL representation (ie : x -> x.0)
>
> (not a pb for me)
>
> Improvements/comments welcome (and wil be happy when an equivalent
> function finds its way into SQLite)
>
> Thanks.
>
> -jm
>
>
>
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

Richard Hipp-3
In reply to this post by jm cuaz
On 7/10/17, Jean-Marie CUAZ <[hidden email]> wrote:
> Hello,
>
> Below is a recipe on a "best effort" basis, to truncate fp numbers on
> the right side of the decimal separator with SQLite.

I don't understand how this is different from "round(N,3)"?

What are you trying to do to the fp number N that "round(N,3)" does not do?

What am I missing?

>
> It is not intended to correct any fp numbers processing, but only to
> discard, without any rounding, unwanted fractional digits.
>
> The following is directed to discard all digits of a number after the
> third fractional position (slight and easy adjustments must be done for
> different positional truncations) :
>
>      round(  CAST(/expression/ AS INTEGER)
>
>                + /((expression/  * 1000 % 1000 ) / 1000 ), 3)
>
> (round() is used against eventually imprecise representation resulting
> from the top level addition operation)
>
> Caveat : force an eventual INTEGER representation resulting from
> /expression/ to its equivalent REAL representation (ie : x -> x.0)
>
> (not a pb for me)
>
> Improvements/comments welcome (and wil be happy when an equivalent
> function finds its way into SQLite)
>
> Thanks.
>
> -jm
>
>
>
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

Keith Medcalf
In reply to this post by Keith Medcalf

Though I would use:

  trunc(value * pow(10, places)) / pow(10, places)

so that all the operations are performed using full floating point, but then I have the whole math library loaded into SQLite3 ...

I just added an override for the math library trunc function that takes two arguments so you can do:

  trunc(value, 3)

which does the truncation (towards 0) and maintains precision.

On the other hand, I have never seen any need to truncate a floating point number, though there are many times when one needs to apply proper rounding (half-to-even) for which I have written a "roundhe" function ...

Truncation or rounding of course only *ever* applied to "display to user and discarded" results and never ever applied to intermediates or stored so having the ability to do this inside the SQLite engine rather than at the application level is of dubious value ... other than if you need to keep a log or something that does not participate in further calculations.

Here is the roundhe function to do statistical / stochastic / bankers' / half-even rounding:

/*
** Define a Statistical (Gaussian/Bankers) rounding function
**
** Round < 0.5 towards zero
**       = 0.5 towards even
**       > 0.5 away from zero
**
** Implements recommended IEEE round-half-even
*/

SQLITE_PRIVATE void _heroundingFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    int p = 0;
    double x, scale, xval, ipart, fpart, sgn;

    if ((argc == 0) || (argc > 2))
        return;
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
        return;
    x = sqlite3_value_double(argv[0]);
    if (argc == 2)
    {
        if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
            return;
        p = sqlite3_value_int(argv[1]);
        p = p > 15 ? 15 : (p < 0 ? 0 : p);
    }
    scale = pow(10.0, p);
    sgn = 1.0;
    if (x < 0)
        sgn = -1.0;
    xval = sgn * x * scale;
    if (log10(xval) > 16.0)
    {
        sqlite3_result_double(context, x);
        return;
    }
    fpart = modf(xval, &ipart);
    if ((fpart > 0.5) || ((fpart == 0.5) && (fmod(ipart, 2.0) == 1.0)))
        ipart += 1.0;
    xval = sgn * ipart / scale;
    sqlite3_result_double(context, xval);
}

and I just added one that truncates without rounding:

/*
** Define a floating point truncation function that allows truncation to some number of decimal places
** Retains 1 ulp precision
*/

SQLITE_PRIVATE void _fptruncateFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    int p = 0;
    double x, scale, xval, ipart, fpart, sgn;

    if ((argc == 0) || (argc > 2))
        return;
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL)
        return;
    x = sqlite3_value_double(argv[0]);
    if (argc == 2)
    {
        if (sqlite3_value_type(argv[1]) == SQLITE_NULL)
            return;
        p = sqlite3_value_int(argv[1]);
    }
    scale = pow(10.0, p);
    sgn = 1.0;
    if (x < 0)
        sgn = -1.0;
    xval = sgn * x * scale;
    fpart = modf(xval, &ipart);
    xval = sgn * ipart / scale;
    sqlite3_result_double(context, xval);
}

 

> Why not just use:
>
>   cast(value * 1000 as integer) / 1000.0
>
>
> > Hello,
> >
> > Below is a recipe on a "best effort" basis, to truncate fp numbers on
> > the right side of the decimal separator with SQLite.
> >
> > It is not intended to correct any fp numbers processing, but only to
> > discard, without any rounding, unwanted fractional digits.
> >
> > The following is directed to discard all digits of a number after the
> > third fractional position (slight and easy adjustments must be done for
> > different positional truncations) :
> >
> >      round(  CAST(/expression/ AS INTEGER)
> >
> >                + /((expression/  * 1000 % 1000 ) / 1000 ), 3)
> >
> > (round() is used against eventually imprecise representation resulting
> > from the top level addition operation)
> >
> > Caveat : force an eventual INTEGER representation resulting from
> > /expression/ to its equivalent REAL representation (ie : x -> x.0)
> >
> > (not a pb for me)
> >
> > Improvements/comments welcome (and wil be happy when an equivalent
> > function finds its way into SQLite)
> >
> > Thanks.
> >
> > -jm
> >
> >
> >
> >
> >
> >
> > ---
> > L'absence de virus dans ce courrier électronique a été vérifiée par le
> > logiciel antivirus Avast.
> > https://www.avast.com/antivirus
> > _______________________________________________
> > 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
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

R Smith
In reply to this post by Richard Hipp-3

On 2017/07/11 3:51 AM, Richard Hipp wrote:

> On 7/10/17, Jean-Marie CUAZ <[hidden email]> wrote:
>> Hello,
>>
>> Below is a recipe on a "best effort" basis, to truncate fp numbers on
>> the right side of the decimal separator with SQLite.
> I don't understand how this is different from "round(N,3)"?
>
> What are you trying to do to the fp number N that "round(N,3)" does not do?
>
> What am I missing?

Well, Truncation is different from Rounding. Floor(N,3) would probably
be a closer match if that existed.
To demonstrate the difference in output, and also compare all the
methods offered, including one of mine, see the next results.

Simon's string method works well, but retains trailing zeroes, not sure
if this is acceptable to the OP, but I included a simple cast version to
fix that.
I believe Keith's and my methods will win (by a very small margin) on
speed tests, but they are certainly less complex.

I included two tests with both 3rd and 6th decimal truncation for all
methods using source values designed to be tricky:
(I hope the formatting holds)


   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
version 2.0.2.4.
   --
================================================================================================

WITH SRC(Val) AS (
     VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
)
SELECT Val,
        round(Val,3) AS Rounded,
        printf('%0.3f', Val) AS PrntF,
        round(CAST(Val AS INTEGER)+ (((Val * 1000) % 1000)/1000),3) AS
Tr_JeanM,
        substr(Val||'0000',1,length(printf('%0.3f',Val))) AS Tr_Simon,
CAST(substr(Val||'0000',1,length(printf('%0.3f',Val))) AS REAL) AS
Tr_SimonFix,
        CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
        round(Val-0.000499999,3) AS Tr_Ryan
   FROM SRC
;
   --                 |           |           | |           | Tr_Simon-
|           |
   --             Val |   Rounded |     PrntF |  Tr_JeanM | Tr_Simon
|       Fix |  Tr_Keith |   Tr_Ryan
   -- --------------- | --------- | --------- | --------- | --------- |
--------- | --------- | ---------
   --               0 |       0.0 |     0.000 |       0.0 |     00000
|       0.0 |       0.0 |       0.0
   --       1.0000001 |       1.0 |     1.000 |       1.0 |     1.000
|       1.0 |       1.0 |       1.0
   --      1.12345678 |     1.123 |     1.123 |     1.123 |     1.123
|     1.123 |     1.123 |     1.123
   --      1.99999999 |       2.0 |     2.000 |     1.999 |     1.999
|     1.999 |     1.999 |     1.999
   --           1.888 |     1.888 |     1.888 |     1.888 |     1.888
|     1.888 |     1.888 |     1.888
   --      9.87654321 |     9.877 |     9.877 |     9.876 |     9.876
|     9.876 |     9.876 |     9.876
   --     1.555555555 |     1.556 |     1.556 |     1.555 |     1.555
|     1.555 |     1.555 |     1.555
   --       1.4999999 |       1.5 |     1.500 |     1.499 |     1.499
|     1.499 |     1.499 |     1.499
   --      1.49494999 |     1.495 |     1.495 |     1.494 |     1.494
|     1.494 |     1.494 |     1.494



WITH SRC(Val) AS (
     VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
)
SELECT Val,
        round(Val,6) AS Rounded,
        printf('%0.6f', Val) AS PrntF,
        round(CAST(Val AS INTEGER)+ (((Val * 1000000) %
1000000)/1000000),6) AS Tr_JeanM,
        substr(Val||'0000000',1,length(printf('%0.6f',Val))) AS Tr_Simon,
CAST(substr(Val||'0000000',1,length(printf('%0.6f',Val))) AS REAL) AS
Tr_SimonFix,
        CAST(Val*1000000 AS INT) / 1000000.0 AS Tr_Keith,
        round(Val-0.000000499999,6) AS Tr_Ryan
   FROM SRC
;


   --             Val |      Rounded |        PrntF | Tr_JeanM |    
Tr_Simon |  Tr_SimonFix |     Tr_Keith | Tr_Ryan
   -- --------------- | ------------ | ------------ | ------------ |
------------ | ------------ | ------------ | ------------
   --               0 |          0.0 |     0.000000 |          0.0 |    
00000000 |          0.0 |          0.0 |          0.0
   --       1.0000001 |          1.0 |     1.000000 |          1.0 |    
1.000000 |          1.0 |          1.0 |          1.0
   --      1.12345678 |     1.123457 |     1.123457 | 1.123456 |    
1.123456 |     1.123456 |     1.123456 | 1.123456
   --      1.99999999 |          2.0 |     2.000000 | 1.999999 |    
1.999999 |     1.999999 |     1.999999 | 1.999999
   --           1.888 |        1.888 |     1.888000 | 1.888 |    
1.888000 |        1.888 |        1.888 |        1.888
   --      9.87654321 |     9.876543 |     9.876543 | 9.876543 |    
9.876543 |     9.876543 |     9.876543 | 9.876543
   --     1.555555555 |     1.555556 |     1.555556 | 1.555555 |    
1.555555 |     1.555555 |     1.555555 | 1.555555
   --       1.4999999 |          1.5 |     1.500000 | 1.499999 |    
1.499999 |     1.499999 |     1.499999 | 1.499999
   --      1.49494999 |      1.49495 |     1.494950 | 1.494949 |    
1.494949 |     1.494949 |     1.494949 | 1.494949



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

Re: Example/recipe for truncating fp numbers

R Smith
In case any astute mathematicians were paying attention to this thread -
the previous solution I offered would obviously fail on Negative values
- which may or may not be a requirement.

Either way, I've amended it to work for all values, but I think Keith's
method might be better than this amended method, if not in speed, then
certainly in complexity terms:

   --
================================================================================================
WITH SRC(Val) AS (
     VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
   UNION ALL
     SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
        round(CAST(Val AS INTEGER)+ (((Val * 1000) % 1000)/1000),3) AS
Tr_JeanM,
        CAST(Val*1000 AS INT) / 1000.0 AS Tr_Keith,
        round(Val-(ifnull(Val/abs(Val),0)*0.000499999),3) AS Tr_Ryan
   FROM SRC
;

   --              Val |   Tr_JeanM |   Tr_Keith | Tr_Ryan
   -- ---------------- | ---------- | ---------- | ----------
   --                0 |        0.0 |        0.0 | 0.0
   --        1.0000001 |        1.0 |        1.0 | 1.0
   --       1.12345678 |      1.123 |      1.123 | 1.123
   --       1.99999999 |      1.999 |      1.999 | 1.999
   --            1.888 |      1.888 |      1.888 | 1.888
   --       9.87654321 |      9.876 |      9.876 | 9.876
   --      1.555555555 |      1.555 |      1.555 | 1.555
   --        1.4999999 |      1.499 |      1.499 | 1.499
   --       1.49494999 |      1.494 |      1.494 | 1.494
   --                0 |        0.0 |        0.0 | 0.0
   --       -1.0000001 |       -1.0 |       -1.0 | -1.0
   --      -1.12345678 |     -1.123 |     -1.123 | -1.123
   --      -1.99999999 |     -1.999 |     -1.999 | -1.999
   --           -1.888 |     -1.888 |     -1.888 | -1.888
   --      -9.87654321 |     -9.876 |     -9.876 | -9.876
   --     -1.555555555 |     -1.555 |     -1.555 | -1.555
   --       -1.4999999 |     -1.499 |     -1.499 | -1.499
   --      -1.49494999 |     -1.494 |     -1.494 | -1.494



WITH SRC(Val) AS (
     VALUES
(0),(1.0000001),(1.12345678),(1.99999999),(1.888),(9.87654321),(1.555555555),(1.4999999),(1.49494999)
   UNION ALL
     SELECT -Val FROM SRC LIMIT 18
)
SELECT Val,
        round(CAST(Val AS INTEGER)+ (((Val * 1000000) %
1000000)/1000000),6) AS Tr_JeanM,
        CAST(Val*1000000 AS INT) / 1000000.0 AS Tr_Keith,
        round(Val-(ifnull(Val/abs(Val),0)*0.000000499999),6) AS Tr_Ryan
   FROM SRC
;

   --              Val |      Tr_JeanM |      Tr_Keith |       Tr_Ryan
   -- ---------------- | ------------- | ------------- | -------------
   --                0 |           0.0 |           0.0 |           0.0
   --        1.0000001 |           1.0 |           1.0 |           1.0
   --       1.12345678 |      1.123456 |      1.123456 |      1.123456
   --       1.99999999 |      1.999999 |      1.999999 |      1.999999
   --            1.888 |         1.888 |         1.888 |         1.888
   --       9.87654321 |      9.876543 |      9.876543 |      9.876543
   --      1.555555555 |      1.555555 |      1.555555 |      1.555555
   --        1.4999999 |      1.499999 |      1.499999 |      1.499999
   --       1.49494999 |      1.494949 |      1.494949 |      1.494949
   --                0 |           0.0 |           0.0 |           0.0
   --       -1.0000001 |          -1.0 |          -1.0 |          -1.0
   --      -1.12345678 |     -1.123456 |     -1.123456 | -1.123456
   --      -1.99999999 |     -1.999999 |     -1.999999 | -1.999999
   --           -1.888 |        -1.888 |        -1.888 |        -1.888
   --      -9.87654321 |     -9.876543 |     -9.876543 | -9.876543
   --     -1.555555555 |     -1.555555 |     -1.555555 | -1.555555
   --       -1.4999999 |     -1.499999 |     -1.499999 | -1.499999
   --      -1.49494999 |     -1.494949 |     -1.494949 | -1.494949



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

Re: Example/recipe for truncating fp numbers

Richard Hipp-3
Truncation is a string operation, not a mathematical operation.  So I
suggest using string functions:

WITH SRC(Val) AS (
    VALUES (0),(1.0000001),(1.12345678),(1.99999999),(1.888),
    (9.87654321),(1.555555555),(1.4999999),(1.49494999),
    (12345.67890123), (1234.56)
  UNION ALL
    SELECT -Val FROM SRC LIMIT 18
)
SELECT substr(val,1,instr(val,'.')+3) FROM src;



--
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
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

R Smith

On 2017/07/11 2:31 PM, Richard Hipp wrote:

> Truncation is a string operation, not a mathematical operation.  So I
> suggest using string functions:
>
> WITH SRC(Val) AS (
>      VALUES (0),(1.0000001),(1.12345678),(1.99999999),(1.888),
>      (9.87654321),(1.555555555),(1.4999999),(1.49494999),
>      (12345.67890123), (1234.56)
>    UNION ALL
>      SELECT -Val FROM SRC LIMIT 18
> )
> SELECT substr(val,1,instr(val,'.')+3) FROM src;

Yep, that's a good method, exactly what Simon suggested, and as with his
suggestion, if we simply add a CAST to REAL we end up with a numeric
value that can be used in further computations. I opted for a length()
on a printf statement (when implementing Simon's suggestion) to
determine the truncation point because I'm not exactly sure if the
decimal separator will always be a "." inside SQLite regardless of any
localization setting - but if it is, this method is certainly shorter.

I still think Keith's method will win for speed, if string functions are
slower than math functions, but I could be wrong. I suppose a speedtest
is in order, but I have meetings, perhaps someone else fancies some testing.


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

Re: Example/recipe for truncating fp numbers

David Raymond
Not to be the new guy here, but would someone be so good as to explain why no one else is panicking that the modulo operator is horrifically broken? In http://www.sqlite.org/lang_expr.html it just says:
"The operator % outputs the value of its left operand modulo its right operand."

There's nothing in there that says anything about requirements for the operands to be integers or about quietly rounding the results on you etc.

sqlite> select 1.4444 % .001;
1.4444 % .001
NULL
Shouldn't that be .0004?

sqlite> select 1.4444 % 2;
1.4444 % 2
1.0
Why is this not 1.4444? This is worse than the NULL by blatantly giving a wrong answer and letting you think it succeeded.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

Keith Medcalf
On Tuesday, 11 July, 2017 07:24, David Raymond <[hidden email]> said:


> Not to be the new guy here, but would someone be so good as to explain why
> no one else is panicking that the modulo operator is horrifically broken?
> In http://www.sqlite.org/lang_expr.html it just says:
> "The operator % outputs the value of its left operand modulo its right
> operand."
>
> There's nothing in there that says anything about requirements for the
> operands to be integers or about quietly rounding the results on you etc.
>
> sqlite> select 1.4444 % .001;
> 1.4444 % .001
> NULL
> Shouldn't that be .0004?
>
> sqlite> select 1.4444 % 2;
> 1.4444 % 2
> 1.0
> Why is this not 1.4444? This is worse than the NULL by blatantly giving a
> wrong answer and letting you think it succeeded.

You are correct.  The modulus operator only works on integers, not on floating point.  The operands are converted to integer and the return value is the result of that integer modulus operation.  This should probably be noted in the documentation for the % operator.

SQLite does not natively implement the fmod(x, y) function from the platform math library.  You would have to add that yourself either as a loadable or builtin extension.




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

Re: Example/recipe for truncating fp numbers

Keith Medcalf
In reply to this post by jm cuaz

The issue here is that you are "truncating" using Base10 (Decimal) but the data is stored in Base2 Floating Point.  Therefore you will always get approximate results and never an exact one.  On modern computers floating point arithmetic is defined in such a way as to represent approximations to fixed point numbers with a few ULP of the scaled binary representation.  For magnitude 1 the epsilon (accuracy) of standard double-precision floating point is 2.220446049250313e-16 (which is why we say that double precision floating point is accurate to 15 to 17 decimal digits, depending on who you ask).

When you perform floating point arithmetic operations, a sequence of pathologic calculations will end up with a representation accuracy equal to the ULP of the largest number in the calculation sequence.  There are various numerical calculation methods that avoid doing calculations in pathological order so that the representation is maintained to within a few ULP of the scale of the result.  (that is, since the result of any operation between two floating point numbers x and y with result z has a precision of max(epsilon(x), epsilon(y), epsilon(z)), one can ensure maximum precision by re-ordering operations to ensure that epsilon(x) and epsilon(y) are as close to the same as possible, for each operation in the chain, such as by doing running averages rather than simple sum/division averages, etc.)

However, when you take any floating point number and "truncate" it, it no longer has the precision defined by the machine representation epsilon.  Using that truncated result in any future calculation will (likely depending on what you do with it) lead to massive precision loss.  "Rounding" intermediates, by whatever method, also usually leads to massive precision loss.

That is why one should always store and work with floating point data at its native precision and only truncate/round a result that will never participate in any further calculation.

While cast(value * 1000 as integer) / 1000.0 is very fast, it will of course only work for numbers where the scaled number fits in a 64-bit signed integer.

trunc(value, 3) which does the truncation (towards 0) and maintains precision

means that the operation is performed by the math library or floating point unit in its usual extended precision and the result is then converted back to double precision using the IEEE-754 standard conversion, so the result is represented as an approximation to within a few ULP of the truncated value.  However, the value of the input and the value of the output may be entirely different as they are still approximations (to different values also).

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Jean-Marie CUAZ
> Sent: Tuesday, 11 July, 2017 05:35
> To: [hidden email]
> Subject: Re: [sqlite] Example/recipe for truncating fp numbers
>
> To M. Slavin, Medcalf ans Hipp:
>
> Thanks for your attention.
>
> Yes, "cast(value * 1000 as integer) / 1000.0" is much better and I will
> switch to your formula when possible, thanks very much.
>
> Thank you for your offer of C functions : a trunc() function is
> certainely what is needed, but I don't understand the sentence
>
> "trunc(value, 3) which does the truncation (towards 0) and maintains
> precision"
>
> What is needed is something like substr(value,1,Y) (as M. Slavin guessed
> it), but on numbers rather than strings, without any alteration of the
> subset returned compared to the orginal in the same range : just discard
> everything above Y = nb of digits on the right side of the decimal
> separator . So trunc(123.1246, 3) = trunc(123.1242, 3) = 123.124  (and
> trunc(123.1, 3) = 123.1 greatly prefered to = 123.100)
>
> Precision 1 : the need of this truncation of fp numbers is only at
> storage time, (the intermediate calculus in "/expression"/ in my example
> is done (in SQL) at full system-available precision of course.
>
> Precision 2 : the number of digits kept on the right side of the decimal
> separator results from mandatory external constraint, and is not a
> developper's choice. With truncation, the loss of precision can, if
> needed and with additional efforts, be (imperfectly) restored; I don't
> know how to to do the same when rounding.
>
> To M. Hipp :
>
> round(123.45678, 3) returns 123.457 and what is needed is a "never
> round" method : 123.456
>
> Reason : the processing done at SQL level involves lot of
> multiplications / divisions on real numbers and unfortunately "integer
> arythmetics" is not an option (readability pb + error prone) . The
> purpose of the truncation done here (wich I believe is a method allso
> used in similar softwares) is an effort to reduce the loss of
> associative property of addition over multiplication. If you send a
> private mail to me I'll give more details.
>
> Thanks
>
> -jm
>
>
>
>
>
>
> ---
> L'absence de virus dans ce courrier électronique a été vérifiée par le
> logiciel antivirus Avast.
> https://www.avast.com/antivirus
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Example/recipe for truncating fp numbers

jm cuaz
In reply to this post by jm cuaz
Thank you M. Medcalf for your nice explanation.

In my first post, I gave half of the used solution : when storing a
"truncated value", eventual remaining digits are allso, separately,
stored as a whole integer.

Both parts are reassembled later when needed (i.e. when doing set
agregation). The intent is to (imperfectly) recover lost precision
resulting from truncation at elementary level : naîve and ugly, but the
best I'm able to do.

 From your explanation, can I conclude that when doing summation on
large sets of fp numbers, a prior sort on this set gives chances to a
better accuracy of the result ?  In SQL, if the usage of a separate
table to be filled in sorted order is not wanted, what would be the
differences in efficiency/memory usage between a sub-select in the FROM
clause compared to the same ordered set processed via a WITH clause ?

Thanks again.

-jm






---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

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