Re: [sqlite] Sqlite3 Data Base write failure cases.

[Prajeesh Prakash]

Hi Simon Slavin,
Thank you for the reply. Yes I am using single thread and single connection. 
Can you tell me what happen if i try with multiple thread and single connection 
to write the data (Multiple thread trying to write into DB will it follow the 
serialization because of the FULLMUTEX and do the write operation one by one).

Thank You

> On December 17, 2018 at 3:16 PM Simon Slavin  wrote:
> 
> 
> On 17 Dec 2018, at 9:01am, Prajeesh Prakash  
> wrote:
> 
> > What are the possible failure cases of DB write in case of single 
> > connection. Because in my application i needs to handle those cases. Can 
> > anyone help me for the same.
> 
> You state that you're using a single connection.  I will assume your software 
> accesses the database only via a single thread, and therefore there can be no 
> contention for using that connection.  I will also assume no other program is 
> accessing the database at the same time as the software you're concerned 
> about.  If those things are not true, post again explaining the exceptions.
> 
> For failure cases which can be detected by your software ...
> 
> Record the value returned from your _step() or _exec() call.  Check to see 
> whether this value is SQLITE_OK ( which is 0 ).  If it is, the command 
> succeeded.  If it isn't, it failed, and your program should report the 
> failure and react to it.
> 
> It's that simple: check to see that the result is 0, and everything else is a 
> failure.  You can unpick this slightly, and detect database rule violations 
> (e.g. SQLITE_CONSTRAINT == 19 , indicating that you've broken database rules 
> you included in the schema) but since your software is meant to be preventing 
> all that, it really doesn't matter what the value is, just report or log it 
> and quit.
> 
> For a list of result codes see
> 
> 
> 
> If the command failed, it never the right thing to do to just have your 
> software try again.
> 
> For failure cases which cannot be detected by your software, read
> 
> 
> 
> Simon.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Application Question

[Roger Schlueter]

I am starting work on a prototype application so this might be an 
excellent opportunity to use SQLite for my application file format.  
Part of this would be the saving and restoring of GUI elements such as 
window positions and sizes, control states, themes, etc.  I can conceive 
of a few different approaches to this but instead of reinventing the 
wheel, I decided to ask this list if you know of examples of SQLite 
usage for this specific purpose.  Of course, it would be nice if the db 
and associated application code were available for inspection.


Thanks in advance.

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

Re: [sqlite] Question about floating point

[Dennis Clarke]

Apologies ... I should have included a link to Jean-Michel Muller's work
on "Elementary Functions" and on preserving monotonicity and always
getting correctly rounded results when implementing the elementary
functions in floating-point arithmetic.

https://link.springer.com/book/10.1007/978-1-4899-7983-4


Also an interesting read in IEEE Transactions on Computers, Vol 66, 
Issue 12 : Exponential Sums and Correctly-Rounded Functions.


https://ieeexplore.ieee.org/document/7891945




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

[sqlite] Linker error

[JuanJo Villegas]

When I link the  amalgamation with my C Source it giveme the follow error:

 

sqlite3.o: In function `sqlite3MemoryBarrier':

/home/Juanjo/Bd/sqlite3.c:26067: undefined reference to
`___sync_synchronize'

 

Thank you

Bye

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

Re: [sqlite] Question about floating point

[Dennis Clarke]

On 12/18/18 6:01 AM, R Smith wrote:> On 2018/12/17 11:53 PM, Dennis 
Clarke wrote:

>>
>> This thread is getting out of hand. Firstly there is no such binary
>> representation ( in this universe ) for a trivial decimal number such as
>> one tenth ( 0.10 ) and really folks should refer to the text book
>> recently published ( 2nd Edition actually ) where all this is covered
>> : //
>
>
> My good man, did the discussion really irritate you ...
[WARNING : written with a smile ]

Well, I guess the real issue is that I see fairly baseline stuff going
in circles over and over and over and over and very little clarity. I
merely posted that textbook because it really was written by the experts
and I have done a fair amount of emails in life back and forth to a few
of the authors on various topics who always cleared the air. They really
are the experts and the only name missing from that list is the great
and dreaded William Kahan himself.  I say that with a smile as Professor
Emeritus of Mathematics Kahan is well known to write very bluntly about
people who have not a clue about trivial things. Trivial to him. The
rest of us merely try to catch up and get a solid understanding of the
basics which, as I was saying, have been covered over and over and over
and over in circles over and over and it gets ... annoying to walk into
a store and hear Beethoven's Ninth Symphony played over dirty cheap
speakers.  Certainly when I have heard live performances a few times in
my life. Very irritating is the word.

I see this sort of thing happen from time to time and I have to take the
approach of 'care' or 'do not care'.  In the case of the store with bad
speakers the option is 'do not care' and simply accept the noise. In the
case were good people can be led down a wrong path and then fall into a
pitfall or trap I feel 'care' happens.  I am not a sociopath nor some
old greybeard UNIX geek that merely enjoys retirement too much to 'care'
anymore.

So let's play a little game based on an early lesson from William Kahan
which will demonstrate how poorly floating point works when used with
nothing but blind trust in bit games.  Also we will assume that we are
going to play by the rules of his IEEE 754 and I may make a passing
reference to these three documents :

1 ) IEEE 754-2008 - IEEE Standard for Floating-Point Arithmetic
https://standards.ieee.org/standard/754-2008.html

2 ) Formal Verification of Floating-Point Hardware Design
https://www.springer.com/us/book/9783319955124

3 ) Handbook of Floating-Point Arithmetic
https://www.springer.com/us/book/9783319765259

4 ) The Art of Computer Programming
https://cs.stanford.edu/~knuth/taocp.html

5 ) Oral history interview with Donald E. Knuth
Charles Babbage Institute, 2001
https://conservancy.umn.edu/handle/11299/107413

6 ) How Futile are Mindless Assessments of Roundoff
in Floating-Point Computation ?
Prof William Kahan
https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf

Also perhaps ISO/IEC/IEEE 60559:2011 and working group ISO/IEC 
JTC1/SC22/WG11 publications.


Let's begin with a quick and incomplete definition of "floating point"
data representation thus :

Given a radix B with precision p we express a 'floting point'
number in the format

[  (+/-)m_0 . m_1 m_2 m_3 ... m_(p-1)  ]  *  B^e

where we call e the exponent which is always an integer and the
expression m_0 . m_1  m_2 m_3 ... m_(p-1) will be called the not
so friendy word "significand" which is expressed in radix B.

A complete and formal definition will be found in chapter 3 of reference
(3) above. This is not a new idea and in fact is quite old. One may find
a fairly nice history of "floating point" in computing machines such as
the Babbage difference engine ( see Charles Babbage et. al. ) and other
machines that performed numerical computation in (4) and (5) above.
Suffice it to say that radix 60 mathematics was common in the Babylonian
history and the Yale Babylonian Collection provides a tablet with an
approximation of the positive square root of two with four sexagesimal
digits 1, 24, 51, 10.  Numerical data representation is not new at all
however I personally fell into the problem in while working on long term
trajectory computations with early Apollo systems. We had not yet been
able to establish a formal method to detect and handle numerical error
conditions and the much loved William Kahan provides us this rather
trivial example to illustrate:

Given   u_0 = 2  and   u_1 = -4   we then compute

u_[n] = 111 - 1130/(u[n-1]) + 3000/(u[n-1]*u[n-2])

where n > 1 clearly.


Trivial :


#include 
#include 
#include 

#define LOOPCNT 30

int main (int argc, char *argv[])
{
long double u[LOOPCNT];
int j;

u[0] = (long double)2.0L;
u[1] = (long double)-4.0L;

printf("u[00] = %+20.18Lf\n", u[0]);

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Keith Medcalf]

On Tuesday, 18 December, 2018 14:50, Nathan Green  wrote:

>Except the problem isn't just in Chrome. Apparently, any system that
>allows SQL injection is vulnerable. Since SQLite can be used as a file
>format to transport application data 
>(https://www.sqlite.org/appfileformat.html),
>other applications might be also be vulnerable. It's not hard to
>conceive of exploiting an application with a "restore from backup" 
>feature.

But this is not an SQLite3 problem.  This is a crap design of the application 
problem as the precondition "any system that allows SQL injection is 
vulnerable" is an absolute requirement.  If you prohibit that precondition, the 
issue cannot exist (though I suppose it would be possible for a malicious 
application to deliberately send malicious SQL, but again, this is an 
application problem, not an SQLite3 problem).

>How "remote" the RCE is depends on the application architecture. I'm
>thankful that SQLite works really well for my use cases, and also that I have
>sandboxed all of my code to run in unprivileged accounts.

Allāhu Akbar!  (Facing north cuz, well, some other people failed spherical 
geometry in grade school)

Hanging curtains (and closing them) on the bedroom windows to prevent the 
neighbours over the way from peeping in through the one-way glass and taking 
pictures of your naughty bits is the prudent thing to do.  You can giggle and 
"move along, nothing to see here" when the peeper over the way puts pictures of 
your neighbour in the local newspaper because he believed the glass vendors 
claim of the one-way-ness of the glazing and ignore all the kerfufle that 
ensues (with a nice glass of single malt and a bag of popcorn).

I have been told that it is "not fair" to implement proper security and protect 
oneself in advance and that one should follow the "Best Practice" and view the 
world though the fog of short-sightedness so induced in such a manner as to 
create the most "Oh Shit" moments possible and to avoid giggling when something 
that cannot possibly affect me affects my lesser prepared neighbours ...
 
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.





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

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Richard Hipp]

On 12/18/18, Dominique Devienne  wrote:
> https://blade.tencent.com/magellan/index_en.html
>
> Sounds to me it more related to a "remote callable" program like Chrome,
> than SQLite proper, but I'd like an official stance on SQLite itself please.
>

There was a bug in FTS3 (not in the SQLite core) that allowed an SQL
Injection to escalate into a Remote Code Execution (RCE).  By making
malicious changes to the shadow tables that FTS3 uses and then running
FTS3 queries that used those tables, an integer overflow could cause a
buffer overrun, which if carefully managed might lead to an RCE.

This is only a problem for application that enable FTS3 (using the
SQLITE_ENABLE_FTS3 or SQLITE_ENABLE_FTS4 compile-time options) and
which allow potential attackers to run arbitrary SQL.  Contrary to
published reports, there are probably *not* millions of application
that meet those circumstances.  In fact, I only know of one:  Chrome.
Chrome has FTS3 enabled, and allows arbitrary SQL through the WebSQL
interface.  On the other hand, Chrome only allows this within a
sandbox, so as far as I know, the problem did not lead to an exploit
even in Chrome.  Perhaps there were other techniques used for escaping
the sandbox after executing the RCE against FTS3, but if not then the
attack against Chrome was also benign, as far as I know.

I believe that Safari also enables WebSQL, but unless I am mistaken,
Safari has disabled FTS3 and so it is probably not vulnerable.

The Fossil Version Control System can be configured to allow anonymous
users to enter SQL to generate bug-report summaries.  However, that
SQL is very restricted.  Only SELECT statements are allowed, and they
are only allowed to access specific tables.  So Fossil is not
vulnerable.

I am not aware of any other applications that deliberately run SQL
from anonymous sources

In any application that enables FTS3 and also has an SQL Injection
bugs, the magellan problem allows those SQL Injection bugs to escalate
to an RCE bug.   And so, the FTS3 bug makes a preexisting SQL
Injection bug worse, but does not introduce any new bugs.

The bug in FTS3 was fixed in version 3.26.0.  So if you are using
SQLite 3.26.0, then you are not vulnerable to this RCE even if you do
enable FTS3 and do allow SQL Injections.

Our policy with respect to bugs is to try to not only fix the bug, but
also fix the process.  Hence, when this issue was discovered in FTS3,
we tried to figure out what we could have done differently from the
beginning to have prevented this bug in the first place, and what we
could do differently moving forward to prevent future similar bugs.
As part of that analysis, we came up with the new
SQLITE_DBCONFIG_DEFENSIVE option.  Applications that do deliberately
allow anonymously-sourced SQL can enable SQLITE_DBCONFIG_DEFENSIVE to
prevent the SQL from deliberately corrupting the database file.  By
preventing corrupt database files, the attack surface is reduced.  Had
this option been available before Magellan, and had Chrome used it
(the latest Chrome releases *do* use it, I am told) then the Magellan
bug would have never happened.  But SQLITE_DBCONFIG_DEFENSIVE is not
the "fix" for the bug.  It is defense-in-depth against similar future
bugs.

We are hard at work on additional defense-in-depth measures now.  I do
not know of any other exploits against FTS3 or SQLite or any other
common SQLite extensions.  But we are working to make sure no new ones
are discovered.

-- 
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] Claimed vulnerability in SQLite: Info or Intox?

[Nathan Green]

On Tue, Dec 18, 2018 at 4:00 PM Peter da Silva  wrote:

> On Tue, Dec 18, 2018 at 3:49 PM Nathan Green  wrote:
>
> > Except the problem isn't just in Chrome. Apparently, any system that
> allows
> > SQL injection is vulnerable.
> >
>
> That's kind of a tautology isn't it? Isn't there some kind of Godwin's Law
> variant for XKCD 327?
>
> I notice that the 12 points on https://www.sqlite.org/appfileformat.html
> don't include "secure".
>
> I mean, sure, we used to distribute software on Usenet as shell scripts
> (look up "shar archive") but it's not 1984 any more.
>
>
SQL injection in the generic sense isn't exactly RCE because SQL is
declarative. Arbitrarily messing up things in a database is not the same as
running any executable code that the database process might have permission
to execute.

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

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

On Tue, Dec 18, 2018 at 3:49 PM Nathan Green  wrote:

> Except the problem isn't just in Chrome. Apparently, any system that allows
> SQL injection is vulnerable.
>

That's kind of a tautology isn't it? Isn't there some kind of Godwin's Law
variant for XKCD 327?

I notice that the 12 points on https://www.sqlite.org/appfileformat.html
don't include "secure".

I mean, sure, we used to distribute software on Usenet as shell scripts
(look up "shar archive") but it's not 1984 any more.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Nathan Green]

On Tue, Dec 18, 2018 at 3:14 PM Simon Slavin  wrote:

> On 18 Dec 2018, at 9:00pm, Peter da Silva  wrote:
>
> > I have to say I'm pretty boggled that Chrome allows hostile users to
> feed code directly into an SQL interpreter that wasn't written from the
> ground up to be secure.
>
> Chrome has problems far more serious than that.  And one can do all sorts
> of nasty things in Chrome extensions.  It's difficult for the developers of
> Chrome to both prevent exploits by webmaster and extension writers, and
> also allow those people to do wonderful, entirely legitimate, things.  At
> the level of making an API call it's not possible for the called function
> to work out whether it's being used legitimately or not without a lot of
> extra processing which would make it so slow nobody would use it.
>
> The tencent.com report is not entirely straightforward about precisely
> where the problem lies, and what an exploit could do.  It would be just as
> useful a report if it mentioned the problem in Chrome and avoided all
> mention of SQLite.  And implying that SQLite ever had a remote code
> execution problem is incorrect.
>
> Simon.
>
>
Except the problem isn't just in Chrome. Apparently, any system that allows
SQL injection is vulnerable. Since SQLite can be used as a file format to
transport application data (https://www.sqlite.org/appfileformat.html),
other applications might be also be vulnerable. It's not hard to conceive
of exploiting an application with a "restore from backup" feature. How
"remote" the RCE is depends on the application architecture. I'm thankful
that SQLite works really well for my use cases, and also that I have
sandboxed all of my code to run in unprivileged accounts.

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

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

Javascript was designed from the start to safely execute malicious code.
That doesn't mean it is safe, it just means it might be. There have been
all kinds of javascript-based exploits, after all.

But an interpreter that was not originally designed to be safe in the face
of malicious code? I can't understand the confusion in the mind that would
lead one to expect miracles of it. This is not a criticism of sqlite, by
any means. Safe languages are rare.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Keith Medcalf]

Why shocked?  

Chrome allows direct execution of untrusted and likely malicious code that it 
gets over the network.  It is called JavaScript.  That a new method for 
execution of untrusted remote malicious code has been created is completely 
unsurprising since the whole point of Chrome is to permit local execution of 
remotely obtained and possibly malicious code.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Peter da Silva
>Sent: Tuesday, 18 December, 2018 14:00
>To: SQLite mailing list
>Subject: Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?
>
>I have to say I'm pretty boggled that Chrome allows hostile users to
>feed
>code directly into an SQL interpreter that wasn't written from the
>ground
>up to be secure. Secure interpreters are *hard* even when you're
>designing
>them from scratch (see also, the whole history of web-based
>vulnerabilities). That seems to be dancing with the screwup fairy to
>me.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Simon Slavin]

On 18 Dec 2018, at 9:00pm, Peter da Silva  wrote:

> I have to say I'm pretty boggled that Chrome allows hostile users to feed 
> code directly into an SQL interpreter that wasn't written from the ground up 
> to be secure.

Chrome has problems far more serious than that.  And one can do all sorts of 
nasty things in Chrome extensions.  It's difficult for the developers of Chrome 
to both prevent exploits by webmaster and extension writers, and also allow 
those people to do wonderful, entirely legitimate, things.  At the level of 
making an API call it's not possible for the called function to work out 
whether it's being used legitimately or not without a lot of extra processing 
which would make it so slow nobody would use it.

The tencent.com report is not entirely straightforward about precisely where 
the problem lies, and what an exploit could do.  It would be just as useful a 
report if it mentioned the problem in Chrome and avoided all mention of SQLite. 
 And implying that SQLite ever had a remote code execution problem is incorrect.

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

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Peter da Silva]

I have to say I'm pretty boggled that Chrome allows hostile users to feed
code directly into an SQL interpreter that wasn't written from the ground
up to be secure. Secure interpreters are *hard* even when you're designing
them from scratch (see also, the whole history of web-based
vulnerabilities). That seems to be dancing with the screwup fairy to me.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Clemens Ladisch]

Dominique Devienne wrote:
> I'd like an official stance on SQLite itself please.




Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "make test" fails on Mageia Linux v7 x86-64

[Shlomi Fish]

Hi,

With the attached bash script on Mageia Linux v7 x86-64 I consistently get the
test failures here:

https://www.shlomifish.org/Files/files/text/sqlite-mga7-rpm-build.txt.xz

this is with sqlite 3.26.0.

This affects our rpm %check phase.

Can you help?

-- 
-
Shlomi Fish   http://www.shlomifish.org/
Interview with Ben Collins-Sussman - http://shlom.in/sussman

I don’t believe in fairies. Oops! A fairy died.
I don’t believe in fairies. Oops! Another fairy died.
— http://www.shlomifish.org/humour.html

Please reply to list if it's a mailing list post - http://shlom.in/reply .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[ajm]

Although the problem is well known by the readers, may be someone would like 
remember the basics (somethin written by me some years ago -in spanish-).
http://www.zator.com/Cpp/E2_2_4a.htm
If you want "play" whiths the numbers in IEE754 this page bay be the fun 
(unfortuately, the original is not longer available)
http://www.zator.com/Cpp/E2_2_4a1.htm

A.J. Millan

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

Re: [sqlite] Question about floating point

[Scott Robison]

On Mon, Dec 17, 2018 at 2:50 PM Thomas Kurz  wrote:

> Ok, as there seem to be some experts about floating-point numbers here,
> there is one aspect that I never understood:
>
> floats are stored as a fractional part, which is binary encoded, and an
> integer-type exponent. The first leads to the famous rounding errors as
> there is no exact representation of most fractions.
>
> Can someone explain to me why it has been defined this way? Having 1 bit
> sign, 11 bit exponent, and 52 bit mantissa, I would have stored the (in the
> meantime well known) number 211496.26 as 21149626E-2, i.e. I would have
> stored a 52 bit integer number and appropriate exponent. This way there
> should be no rounding errors and one would always have a guaranteed
> precision of ~15 significant digits.
>

To get the maximum precision possible from a binary floating point number,
the designers of the format took advantage of the fact that all numbers
other than zero would have a 1 bit set somewhere in their representation.
To that end, "normal" floating point numbers actually have a 53 bit
mantissa. "But that equals 65 bits! You can't cram 65 bits into a 64 bit
word." But you can if the most significant set bit of the mantissa is
implied just to the left of the explicitly given 52 bits of the mantissa.
The most significant digit of a decimal number can be any value from 1
through 9, so you can't use this same trick to extend the precision of a
decimal floating point number.

In addition to normal numbers, there are subnormal numbers, where the left
most digit is implicitly a 0 bit. The value zero happens to be a subnormal
number with all bits set to zero.

Even without the implicit bit, many / most schemes for encoding decimal
digits in binary lose some portion of the range that is possible with
binary representations, and the IEEE designers wanted the best of both
worlds, range and precision, so they gave up exact decimal representation
in favor of binary.

Your approach of coding is what the decimal type does in the .net platform,
among other examples, but the available range is smaller than IEEE binary
floating point numbers of the same size.

As far as it goes, you can still have rounding errors that propagate with a
decimal scheme such as you suggest. Simply add 1/3 + 1/3 + 1/3 in a decimal
representation.

333E-15 + 333E-15 + 333E-15 =
999E-15. But it should be 1000E-15 (or 1E0). It
doesn't matter how many bits of precision you add, you can never do this
type of math exactly with decimal floating point numbers. Any time the
decimal expansion extends beyond the bit length of the available precision,
rounding choices are going to have to be made at some point, and some
calculation will be inexact.

Note: I am spouting from memory, so my apologies if I've gotten any
terminology wrong (such as subnormal vs denormal, so similar other ideas).

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

Re: [sqlite] Question about floating point

[Richard Damon]

On 12/18/18 6:21 AM, R Smith wrote:
>
> I'm not even going to touch on silly/stupid programming and
> calculations that round along the intermediate steps, those have been
> mentioned already, they are evil and it isn't the fault of the storage
> medium.

Actually, periodically rounding IS a valid method IF you know the
'precision' of the native numbers. For example, if you know that all the
numbers in a list supposed to be exact values to two decimals, as you
add them up the rounding error grows, and if the list is long enough,
can cause an error i those two decimal digits, but by periodically
rounding to that two decimals resets the rounding error.

The error is in doing this sort of rounding when the input numbers are
NOT known to be 'exact' to those two digits, but you round to that
precision. THAT rounding will increase the error in the calculation.

-- 
Richard Damon

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

Re: [sqlite] Question about floating point

[R Smith]

On 2018/12/18 1:21 AM, James K. Lowden wrote:



First, the problem is not storage it's calculation.

Second, the thread was started because a floating point calculation
in SQLite, exactly as it is run today, led to the following value:

211496.252

which is typical of such problems.

What problem?  Rounded to the number of significant digits -- 2 decimal
places in the input -- the number is correct.



Exactly, and I would go further to suggest that the problem is much more 
to do with the human brain and visual concepts than the numbers.


Sure enough, as a mathematician the numbers
211496.252 and
211496.26

look exactly the same to me (when I consider scales in the sub 15 digit 
range), as it does to an IEEE float, but you can see how, to the average 
onlooker's brain, those look vastly different - see them as pictures 
rather than numbers, which obviously look completely different - and 
this is what scares people and why this keeps being a problem.


I'm not even going to touch on silly/stupid programming and calculations 
that round along the intermediate steps, those have been mentioned 
already, they are evil and it isn't the fault of the storage medium.


The fact that the SQLite programmers and the Python programmers (or 
perhaps Python-SQLite-wrapper programmers) did not choose the same 
presentation is just one more case of "not the storage method's fault 
but indeed the interpretation's fault".



PS, a good video to cure you of "visual number deficit" sickness would 
be one of those explaining why 0.999... (recurring) is exactly equal to 
1. (And that's not even touching IEEE)

This be as good as any: https://www.youtube.com/watch?v=G_gUE74YVos


Cheers!
Ryan


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

Re: [sqlite] Question about floating point

[R Smith]

On 2018/12/17 11:53 PM, Dennis Clarke wrote:


This thread is getting out of hand. Firstly there is no such binary
representation ( in this universe ) for a trivial decimal number such as
one tenth ( 0.10 ) and really folks should refer to the text book
recently published ( 2nd Edition actually ) where all this is covered 
: //



My good man, did the discussion really irritate you that much? I'm truly 
sorry to hear that, but I'd like to offer as consolation that it's 
probably enlightening to most others.


If we follow the proposed rationale above, we could replace this entire 
forum with a single web page sporting some links to the very great SQL, 
DB, and SQLite books out there, then nobody need discuss anything.


Of course, we know that believing any one book blindly also doesn't lead 
to complete knowledge, so perhaps discussion is not as evil (especially 
the kind containing varied views) and we should keep at it?
I hope in that light you will join us in enjoying the rest of this 
thread, wherever it may lead.



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

[sqlite] Claimed vulnerability in SQLite: Info or Intox?

[Dominique Devienne]

https://blade.tencent.com/magellan/index_en.html

Sounds to me it more related to a "remote callable" program like Chrome,
than SQLite proper, but I'd like an official stance on SQLite itself please.

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users