Re: [sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

Basically a variant of the final idea you had is what we went with.  Thanks for 
the suggestions!

Jim Borden

Privacy Policy
Marketing 
Preferences
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF giving incorrect results

[Richard Hipp]

On 2/27/19, Jim Borden  wrote:
>  For reference here
> was the query that I was working with:
>
> SELECT fl_result(prediction('EchoModel', dict_of('max_double',
> 1.7976931348623153e+308))) FROM kv_default AS _doc WHERE (_doc.flags & 1 =
> 0);
>

As a work-around, you could code up a UDF max_real() that returned the
value you want.  Or maybe use the extension function ieee754()
(available from https://www.sqlite.org/src/file/ext/misc/ieee754.c)
and code the big number as ieee754(9007199254740991,971).  Or you
could make the big constant a parameter (maybe $maxReal) and then set
the value using sqlite3_bind_double().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

The platforms exhibiting the behavior have so far been MSVC Windows 10 64-bit 
(both 32 and 64 bit processes), and Android x86.  iOS 64-bit was not affected 
and iOS 32-bit was not testable due to outside factors but my guess is that it 
is not affected.  macOS 64-bit was not affected.

I tried to come up with a simpler test case than the one I had but I was unsure 
of how to trigger a path that entered 'codeReal'.  For reference here was the 
query that I was working with:

SELECT fl_result(prediction('EchoModel', dict_of('max_double', 
1.7976931348623153e+308))) FROM kv_default AS _doc WHERE (_doc.flags & 1 = 0);

Between here and when dict_of was called, the value became a false infinity 
(i.e. sqlite3_value_double returned inf).  I couldn't reproduce this by just 
selecting a literal from a query, which made me think it might be taking 
another path.  Since I couldn't figure out how to trigger it that way, I just 
put in a call to strtod alongside the call to sqlite3AtoF inside of codeReal 
and compared the results.


Jim Borden

On 2019/02/28 8:50, "drhsql...@gmail.com on behalf of Richard Hipp" 
 wrote:

On 2/27/19, Jim Borden  wrote:
>
> My theory as to why this varies by platform is because of the use of long
> double during this calculation, which can vary in size.  On the failing
> platforms they are the same size as regular double.  This results in a
> rounding error of the "scale" portion of the calculation (via 
sqlite3Pow10)
> which results in a false infinity.

Just out of curiosity, what platforms are you having trouble with.  I
tried the script below on each of Linux (Ubuntu with gcc 5.4.0), Mac
(LLVM 10.0.0), and Win10 (MSVC) and got the answer you expect in all
three cases, namely 1.79769313486231522322e+308,
8.98846567431157611547e+307, Inf.

SELECT quote(1.7976931348623153E+308);
SELECT quote(1.7976931348623153E+308/2.0);
SELECT quote(1.7976931348623153E+308*2.0);

--
D. Richard Hipp
d...@sqlite.org



Privacy Policy
Marketing 
Preferences
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF giving incorrect results

[Richard Hipp]

On 2/27/19, Jim Borden  wrote:
>
> My theory as to why this varies by platform is because of the use of long
> double during this calculation, which can vary in size.  On the failing
> platforms they are the same size as regular double.  This results in a
> rounding error of the "scale" portion of the calculation (via sqlite3Pow10)
> which results in a false infinity.

Just out of curiosity, what platforms are you having trouble with.  I
tried the script below on each of Linux (Ubuntu with gcc 5.4.0), Mac
(LLVM 10.0.0), and Win10 (MSVC) and got the answer you expect in all
three cases, namely 1.79769313486231522322e+308,
8.98846567431157611547e+307, Inf.

SELECT quote(1.7976931348623153E+308);
SELECT quote(1.7976931348623153E+308/2.0);
SELECT quote(1.7976931348623153E+308*2.0);

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF giving incorrect results

[Richard Hipp]

On 2/27/19, Jim Borden  wrote:
> Hello,
>
> I’ve been tracking down an odd problem where some of our results from an
> application defined SQLite function are coming back as infinity on some
> platforms (notably 32-bit Android, and all Windows).  I’ve narrowed it down
> to the following:
>
> Given the example input "1.7976931348623153e+308" encoded as UTF-8, the
> following lines of code have the following results (z is the const char*
> variable holding the text):
>
> double value = strtod(z, NULL); // value holds 1.7976931348623153E+308
> sqlite3AtoF(z, , sqlite3Strlen30(z), SQLITE_UTF8); // value now hold
> +Inf
>
> My theory as to why this varies by platform is because of the use of long
> double during this calculation, which can vary in size.  On the failing
> platforms they are the same size as regular double.  This results in a
> rounding error of the "scale" portion of the calculation (via sqlite3Pow10)
> which results in a false infinity.
>
> Would this be considered a bug, or is precision not guaranteed to the same
> level as strtod because of other factors?

This is a case of the precision not guaranteed due to other factors.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3AtoF giving incorrect results

[Jim Borden]

Hello,

I’ve been tracking down an odd problem where some of our results from an 
application defined SQLite function are coming back as infinity on some 
platforms (notably 32-bit Android, and all Windows).  I’ve narrowed it down to 
the following:

Given the example input "1.7976931348623153e+308" encoded as UTF-8, the 
following lines of code have the following results (z is the const char* 
variable holding the text):

double value = strtod(z, NULL); // value holds 1.7976931348623153E+308
sqlite3AtoF(z, , sqlite3Strlen30(z), SQLITE_UTF8); // value now hold +Inf

My theory as to why this varies by platform is because of the use of long 
double during this calculation, which can vary in size.  On the failing 
platforms they are the same size as regular double.  This results in a rounding 
error of the "scale" portion of the calculation (via sqlite3Pow10) which 
results in a false infinity.

Would this be considered a bug, or is precision not guaranteed to the same 
level as strtod because of other factors?

Jim Borden
Senior Software Engineer
e: jim.bor...@couchbase.com
t: @borrrden



Privacy Policy
Marketing 
Preferences
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF Handling of high numbers

[Cezary H. Noweta]

Hello,

On 2018-05-17 15:07, Stiefsohn, Roman wrote:

Hello,

i found out that SQLite is having problems with converting high numbers from string to 
double, located in the function "sqlite3AtoF":


Yes -- it is having problems, however the problems are located in a 
compiler which lacks long double support and not in ``sqlite3AtoF''.



Below is a table of input strings, and the returned double values, tested on 
Windows 7 64 bit with SQLite version 3.23.1:

Input String:  Converted Result
1.7976931348623152e+3081.7976931348623153e+308
1.7976931348623154e+3081.7976931348623157e+308
1.7976931348623155e+3081.7976931348623157e+308
1.7976931348623156e+3081.7976931348623157e+308
1.7976931348623157e+308INF
1.7976931348623158e+308INF


Ok - should be:

...152e+308 => ...151e+308
...154e+308 => ...153e+308
...155e+308 => ...155e+308
...156e+308 => ...155e+308
...157e+308 => ...157e+308
...158e+308 => ...157e+308

Use a compiler supporting long doubles -- results will be more adequate.

Conversion must be done using higher precision then a destination type's 
one. There exist two operations (thus a rounding occurs twice), so you 
lose one (ln nof ops / ln 2 == 1) bit at least, what leads to not so 
accurate results if you are using the same precision as a precision of 
the destination type. In a border case:


1.7976931348623158e+308
===

Mantissa (exact value): 1.7976931348623158 ==> 17976931348623158 * 
10^-16 == /2 ==> 8988465674311579 * 2 * 10^-16 ==> (0x1.FEEF63F97D79B * 
2^53) * 10^-16. The last factor goes to an exponent.


Ten's exponent (approx value, however the most accurate, what is never 
occurring): 10^308 * 10^-16 ==> 10^292 ==> 
0x402225AF3D53E7C2BCC068B1E... (243 hex digits) ==> 
0x1.008896BCF54F9|F0AF301A2C79EB7036... * 2^970 == rounding ==> 
0x1.008896BCF54FA * 2^970


Now we have a final number:

(0x1.FEEF63F97D79B * 2^53) * (0x1.008896BCF54FA * 2^970) ==> 
0x1.FEEF63F97D79B * 0x1.008896BCF54FA * 2^1023 ==> 
1.F|946300C... * 2^1023 == rounding ==> 2 * 2^1023 == ps 
==> 2^1024 ==> INF. If there was no more then one additional bit of a 
precision then INF would not appear at all and a finite number: 
1.F8 * 2^1023 would emerge.



Originally the behavior was discovered when executing a select statement with a 
comparison of the DBL_MAX Value (1.7976931348623158e+308 ):

/*
Entry in Table:
Id Value
1  1.1754943508222878e-38
*/
select  id, value from table
inner join
(
select 1 as Id, 1.1754943508222878e-38 as Value
) table2
on table.id = table2.id
where table.Value < (table2.Value - 2.22045e-16) or table.Value > (table2.Value 
+ 2.22045e-16)


1. Could you include working examples in the future?

2. Where is DBL_MAX?

3. You are adding/subtracting numbers with a difference of 22 in ten's 
exponent. Is it intentional? If so, then the WHERE condition can be 
reduced to ``0 < -2.22045e-16 or 0 > +2.22045e-16'' => 0 or 0 => 0 => 
NULL => nothing.



è  This query would return 1 record, although there is a value with the exact 
same value inside the database ( executed with the sqlite c++ api without usage 
of prepared parameters)


4. Due to 3. if an above query had been working, it would not have 
returned any records/rows.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3AtoF Handling of high numbers

[Stiefsohn, Roman]

Hello,

i found out that SQLite is having problems with converting high numbers from 
string to double, located in the function "sqlite3AtoF":

Below is a table of input strings, and the returned double values, tested on 
Windows 7 64 bit with SQLite version 3.23.1:

Input String:  Converted Result
1.7976931348623152e+3081.7976931348623153e+308
1.7976931348623154e+3081.7976931348623157e+308
1.7976931348623155e+3081.7976931348623157e+308
1.7976931348623156e+3081.7976931348623157e+308
1.7976931348623157e+308INF
1.7976931348623158e+308INF

Originally the behavior was discovered when executing a select statement with a 
comparison of the DBL_MAX Value (1.7976931348623158e+308 ):

/*
Entry in Table:
Id Value
1  1.1754943508222878e-38
*/
select  id, value from table
inner join
(
select 1 as Id, 1.1754943508222878e-38 as Value
) table2
on table.id = table2.id
where table.Value < (table2.Value - 2.22045e-16) or table.Value > (table2.Value 
+ 2.22045e-16)


è  This query would return 1 record, although there is a value with the exact 
same value inside the database ( executed with the sqlite c++ api without usage 
of prepared parameters)

Kind regards
Roman Stiefsohn
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF()

[Cezary H. Noweta]

Hello,

On 2017-12-29 02:25, Richard Hipp wrote:

Please test the latest trunk version (or any version after check-in
https://www.sqlite.org/src/timeline?c=fd2e0e7a) and confirm that the
modifications work for you.


It works as expected. Thank you.

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3AtoF()

[Richard Hipp]

On 12/25/17, Cezary H. Noweta  wrote:
>
> Could you consider an exponentiation by squaring (in the main release)
> instead of current n-multiplication of exponents?

Please test the latest trunk version (or any version after check-in
https://www.sqlite.org/src/timeline?c=fd2e0e7a) and confirm that the
modifications work for you.  Thanks for the suggested algorithm
improvement.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3AtoF()

[Cezary H. Noweta]

Hello,

Could you consider an exponentiation by squaring (in the main release)
instead of current n-multiplication of exponents? I've observed that an
average time spent in sqlite3AtoF() is greatly reduced by 12% to over
30%, depending on an exponent's magnitude and a compiler. It is nothing
more then 3 lines of code and a noticeable advantage, for example:

==
--- util.c  2017-10-25 02:25:56.0 +0200
+++ util-esqr.c 2017-12-25 07:02:20.101110300 +0100
@@ -475,11 +475,12 @@
 if( e==0 ){ 
/*OPTIMIZATION-IF-TRUE*/

   result = (double)s;
 }else{
-  LONGDOUBLE_TYPE scale = 1.0;
+  LONGDOUBLE_TYPE scale = 1.0, t;
   /* attempt to handle extremely small/large numbers better */
   if( e>307 ){ 
/*OPTIMIZATION-IF-TRUE*/
 if( e<342 ){ 
/*OPTIMIZATION-IF-TRUE*/

-  while( e%308 ) { scale *= 1.0e+1; e -= 1; }
+  for ( e -= 308, t = 1.0e+1; 1 < e; e >>= 1, t *= t ) { if ( 1 
& e ) scale *= t; }

+  if ( e ) scale *= t;
   if( esign<0 ){
 result = s / scale;
 result /= 1.0e+308;
@@ -499,10 +500,8 @@
   }
 }
   }else{
-/* 1.0e+22 is the largest power of 10 than can be
-** represented exactly. */
-while( e%22 ) { scale *= 1.0e+1; e -= 1; }
-while( e>0 ) { scale *= 1.0e+22; e -= 22; }
+for ( t = 1.0e+1; 1 < e; e >>= 1, t *= t ) { if ( 1 & e ) scale 
*= t; }

+scale *= t;
 if( esign<0 ){
   result = s / scale;
 }else{
==

-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users