Lifepillar, on Friday, May 24, 2019 02:48 PM, wrote...
>On 24 May 2019, at 19:53, Warren Young wrote:
>>
>>https://chiselapp.com/user/lifepillar/repository/sqlite3decimal/index
>
>sqlite3decimal’s author here: be warned that the extension is still in
> development. In particular, it’s far
Rowan Worth, on Monday, May 27, 2019 11:07 PM, wrote...
>On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera
>wrote:
>
>> Ok, I think it happens even before the casting. This should be,
>> 3.2598, and yet, it's 3.26.
>>
>> sqlite> SELECT 0.005 + 3.2548;
>> 3.26
On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera
wrote:
> Ok, I think it happens even before the casting. This should be,
> 3.2598, and yet, it's 3.26.
>
> sqlite> SELECT 0.005 + 3.2548;
> 3.26
>
Note that no arithmetic is required to see these symptoms:
sqlite>
James K. Lowden, on Sunday, May 26, 2019 04:51 PM, wrote...
>On Fri, 24 May 2019 13:10:49 +
>Jose Isaias Cabrera wrote:
>
>> >Consider these two queries:
>> >
>> > SELECT round(3.255,2);
>> > SELECT round(3.2548,2);
>> >
>> >Do you expect them to give different
On Fri, 24 May 2019 13:10:49 +
Jose Isaias Cabrera wrote:
> >Consider these two queries:
> >
> > SELECT round(3.255,2);
> > SELECT round(3.2548,2);
> >
> >Do you expect them to give different answers?
>
> 3.26
> 3.25
sqlite> SELECT cast(100 * (0.005 + 3.2548) as
> INSERT INTO t1(a,b) VALUES(2,3.254893418589635);
But this is a different scenario. The value is already stored in the database
as 3.255.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
On 24 May 2019, at 13:35, Jose Isaias Cabrera wrote:
> 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
sqlite> select roundhe(3.255, 2);
3.26
sqlite> select printf('%!.18f', 3.255);
3.254893
sqlite> select printf('%!.18f', roundhe(3.255, 2));
3.259787
sqlite> select printf('%!.18f', pow(10.0, 2));
100.0
sqlite> select printf('%!.18f', pow(10.0, 2) * 3.255);
325.5
The good:
On 24 May 2019, at 19:53, Warren Young wrote:
>
> On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera wrote:
>>
>> 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
Yes, I fear users are attempting simplistic banking apps with floating
point rounds. :(
May explain where millions of my pennies went in my last android stock sale!
On Fri, May 24, 2019 at 1:55 PM Warren Young wrote:
> On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera
> wrote:
> >
> > Dr.
On May 24, 2019, at 7:10 AM, Jose Isaias Cabrera wrote:
>
> 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
Sorry, I was too fast with sending.
With the three values mentioned before:
a) 3.255
b) 3.254999
c) 3.254893418589635
Both SQLite and MySQL (however, I used MariaDB) return these values on a simple
SELECT b:
a) 3.255
b) 3.254999
c) 3.255
And ROUND(b,2) returns:
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.254893418589635);
> SELECT
On 5/24/19, Jose Isaias Cabrera wrote:
>
> FWIW, I went to sqlfiddle [1] and these are the answers for this SQL
> command:
>
> SELECT round(3.255,2), round(3.2548,2);
I also went to sqlfiddle and did a slightly more realistic scenario:
CREATE TABLE t1(a INT, b DOUBLE PRECISION);
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.254999;2) --> 3.25
FWIW, I went to sqlfiddle [1] and these are the answers for this SQL command:
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 *
> 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...
> 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
On 5/24/19, Thomas Kurz 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.2548 are
the exact same number, and hence
Sorry, but even Excel (which usually isn't very good at decimal math) gives
correct results:
ROUND(3.255;2) --> 3.26
ROUND(3.254999;2) --> 3.25
Yours is clearly incorrect.
- Original Message -
From: Richard Hipp
To: SQLite mailing list
Sent: Friday, May 24, 2019, 14:44:52
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.2548,2);
>
>Do you expect them to give different
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
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
On 5/24/19, Jose Isaias Cabrera 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.2548,2);
Do you expect them to give different answers?
If so, do you
Simon Slavin, on Friday, May 24, 2019 08:34 AM, wrote...
>On 24 May 2019, at 1:30pm, Jose Isaias Cabrera 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
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
On 24 May 2019, at 1:30pm, Jose Isaias Cabrera 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
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
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 wrote:
>
> In Delphi I have 2 functions that works (I
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 *
On 5/24/19, Hajo Bruns 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
31 matches
Mail list logo
