Re: [sqlite] SQLite on PocketBook
On Nov 5, 2009, at 5:15 PM, Beau Wilkinson wrote: > I really think this warrants further discussion. Perhaps the correct > answer (that ARMs implement a non-standard FP type which is > incompatible with Sqlite) is already out there, but I think the > issues I raised with that answer should at least be addressed. I don't know if this is the problem on PocketBook, but... We have successfully used the SQLite compile option SQLITE_MIXED_ENDIAN_64BIT_FLOAT when building SQLite for ARM to get interoperability of databases between ARM Linux and other platforms such as x86 Linux, MacOSX, and Windows. Some compilers and runtimes for ARM use a format wherein the two 32-bit halves of a double are swapped relative to other platforms (the two 32-bit words are in big- endian order, whereas the bytes in the words are in little endian order, hence the rationale for the MIXED nomenclature in the option name). e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
On 5 Nov 2009, at 10:45pm, D. Richard Hipp wrote: > I'm sorry that you are having difficulty and that SQLite is not > working to your satisfaction. But the fact remains that it works > great on every platform that I have access to. If you are able to > find a bug in SQLite we will be happy to look into it for you. But we > have no capability of helping you with your ARM problems. Just to supplement what DRH wrote, iPhones have an ARM processor, and SQLite is prevalent throughout the iPhone system software -- not just Apps written by the public but in the standard system operations like the list of recent calls, and how SMS messages work. I'm not saying that the iPhone is completely bug free, but if there were significant bugs in the iPhone version of SQLite, five million users would probably have discovered it. So if there's a problem with how SQLite is working on your platform, this is not something that affects all ARM devices. But you'll need someone who understands the low level of SQLite better than I do to figure out what's going wrong. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
On Nov 5, 2009, at 5:15 PM, Beau Wilkinson wrote: > > Assuming (and perhaps this is the rub...) that Sqlite is built > around C++ "float" and "double," then I fail to see how any FP > system that is even plausibly useful could give the results cited by > Mr Drozd. If I put (for example) the value 100.0 into a "double," > and then transport or store/retrieve the binary representation > somehow, and then take those bits and once more treat them as a > "double," then I ought to get 100 (or at least something very, very > close). These are the sorts of things that Sqlite should, to my mind > at least, be doing with real number data, and it ought not to matter > what the underlying representation is. > This is exactly what SQLite does. It uses the C (not C++) double type for floating point operations. It assumes that double is represented by 64 contiguous bits in a format that can be understood by different architectures and it blindly copies those bits to disk. Later, it reads the same bits back from disk and copies them into another double. If you write a double into an SQLite database on a machine that has IEEE FP, then transfer that database to another machine with a different floating point format, then read the double out of the database, the bits won't be right and you'll get a weird answer. This comes up a lot on ARM because of the use of mixed-endian representation - neither big-endian or little-endian. I thought we had worked around that problem, but perhaps you have found a way to defeat our work-around. We've also seen problems with GCC's floating point optimizer going overboard and simply generating bad code when the --fast-math opton is specified. We recommend that you not use --fast-math. I'm sorry that you are having difficulty and that SQLite is not working to your satisfaction. But the fact remains that it works great on every platform that I have access to. If you are able to find a bug in SQLite we will be happy to look into it for you. But we have no capability of helping you with your ARM problems. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
I really think this warrants further discussion. Perhaps the correct answer (that ARMs implement a non-standard FP type which is incompatible with Sqlite) is already out there, but I think the issues I raised with that answer should at least be addressed. Assuming (and perhaps this is the rub...) that Sqlite is built around C++ "float" and "double," then I fail to see how any FP system that is even plausibly useful could give the results cited by Mr Drozd. If I put (for example) the value 100.0 into a "double," and then transport or store/retrieve the binary representation somehow, and then take those bits and once more treat them as a "double," then I ought to get 100 (or at least something very, very close). These are the sorts of things that Sqlite should, to my mind at least, be doing with real number data, and it ought not to matter what the underlying representation is. And yet it has been put forth in this forum that such is not the case. Rather, the underlying representation must comply with the IEEE FP standard, or even basic operations will not work. And this is so certain, well-known, and reasonable that discussion amongst the plebians is not warranted. How is this possible architecturally? The only explanation I can fathom is that Sqlite depends on the underlying representation following the IEEE standard at the bit level. For example, when doing sorts, maybe Sqlite is assuming the mantissae and exponents are in the bit ranges specified by IEEE, and that they are represented in the specified format (e.g. excess vs. complement notation) as well. If this is indeed the case, I think this is a very misguided architecture. Depending on the bit-level representation is bad. It's a brittle design. Of course, it's easy for you all to intimidate anyone who has a problem with this architecture... the complainer is "not in compliance with the IEEE standard" and is thus clearly worthy of your speedy dismissal. Bah. Ultimately, I think this is an easy excuse for a bad design. Types like "float" and "double" are intended by their designers to abstract over many FP implementations. They are not just convenient macros from IEEE FP, nor should they be. I could go on to take issue with the IEEE standard itself. The allocation of bits to exponent-versus-mantissa is rigid, for example. IEEE makes no allowance (that I know of) for allowing a tradeoff between precision and dynamic range, which is a major oversight for such a widely-used standard. Until very recently IEEE FP included no support for 16-bit (half precision) data. IEEE was also designed by committee so it includes all sorts of nice-to-have pet features (two zeros, distinct error and condition codes, etc.) which may or may not be worthwhile on any given real-world system. (I tend to lean toward the "may not" direction). But whether IEEE is bad or good or indifferent makes no difference- the standard should not, in my opinion, be built into Sqlite. Basic software engineering sense must still trump even the best standard. Forgive me if I have missed something here, but this seems like what I would call "Standardizationism" run amok. From: Beau Wilkinson Sent: Wednesday, November 04, 2009 9:39 AM To: General Discussion of SQLite Database Cc: Alexander Drozd Subject: RE: [sqlite] SQLite on PocketBook >I'm guessing that your hardward does not implement IEEE 754 floating >point correctly. We've seen that sort of thing before, especially >with GCC. There are some options to GCC (which escape my memory right >now) that can force it to use strict IEEE 754 floating point rather >than its preferred, speedier but non-standard alternative. What he's getting back is so far from correct, though, that I would tend to blame a run-of-the-mill bug rather than some point-of-detail. Non-IEEE floating point often sacrifices things like the distinctions between "Not-a-Number" and "Infinity," or the difference between positive and negative zero, and so on. Perhaps in some cases the rounding of the last bit is wrong. But no FP system should give results that are flat out wrong, especially when doing arithmetic. (I can see where series of operations involving exponents &c. might get way out-of-line but I don't think Sqlite is doing any of these things.) What he is getting back looks like Double.MaxVal... is there a divide-by-zero somewhere? That is the sort of thing that different FP specs will legimately handle differently. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp [...@hwaci.com] Sent: Wednesday, November 04, 2009 9:26 AM To: General Discussion of SQLite Database Cc: Alexander Drozd Subject: Re: [sqlite] SQLite on PocketB
Re: [sqlite] SQLite on PocketBook
> Correct, ARM's emulate hardware floating point using software.
That doesn't really say anything about compliance with standards/
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On
Behalf Of O'Neill, Owen [oone...@averyberkel.com]
Sent: Wednesday, November 04, 2009 9:36 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook
Correct, ARM's emulate hardware floating point using software.
And it's really slow too since it forces a context switch as the
processor spots it's been given a FP operation to do and has to jump
into the fp emulation.
If you are lucky enough to have control over it & depending on the level
of accuracy you need, I'd recommend storing everything as an integer
with a separate scalar.
Owen.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, November 04, 2009 3:26 PM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook
On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
> My name is Alexander. I am working on an open-source spaced-
> repetition software project (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very sorry to bother you, but I tried to submit my problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
> The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.
I'm guessing that your hardward does not implement IEEE 754 floating
point correctly. We've seen that sort of thing before, especially
with GCC. There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this e-mail is privileged and confidential
information intended only for the use of the individual or entity named. If
you are not the intended recipient, or the employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
any disclosure, dissemination, distribution, or copying of this communication
is strictly prohibited. If you have received this e-mail in error, please
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
>I'm guessing that your hardward does not implement IEEE 754 floating
>point correctly. We've seen that sort of thing before, especially
>with GCC. There are some options to GCC (which escape my memory right
>now) that can force it to use strict IEEE 754 floating point rather
>than its preferred, speedier but non-standard alternative.
What he's getting back is so far from correct, though, that I would tend to
blame a run-of-the-mill bug rather than some point-of-detail. Non-IEEE floating
point often sacrifices things like the distinctions between "Not-a-Number" and
"Infinity," or the difference between positive and negative zero, and so on.
Perhaps in some cases the rounding of the last bit is wrong. But no FP system
should give results that are flat out wrong, especially when doing arithmetic.
(I can see where series of operations involving exponents &c. might get way
out-of-line but I don't think Sqlite is doing any of these things.)
What he is getting back looks like Double.MaxVal... is there a divide-by-zero
somewhere? That is the sort of thing that different FP specs will legimately
handle differently.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On
Behalf Of D. Richard Hipp [...@hwaci.com]
Sent: Wednesday, November 04, 2009 9:26 AM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook
On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
> My name is Alexander. I am working on an open-source spaced-
> repetition software project (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very sorry to bother you, but I tried to submit my problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
> The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.
I'm guessing that your hardward does not implement IEEE 754 floating
point correctly. We've seen that sort of thing before, especially
with GCC. There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The information contained in this e-mail is privileged and confidential
information intended only for the use of the individual or entity named. If
you are not the intended recipient, or the employee or agent responsible for
delivering this message to the intended recipient, you are hereby notified that
any disclosure, dissemination, distribution, or copying of this communication
is strictly prohibited. If you have received this e-mail in error, please
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
Correct, ARM's emulate hardware floating point using software.
And it's really slow too since it forces a context switch as the
processor spots it's been given a FP operation to do and has to jump
into the fp emulation.
If you are lucky enough to have control over it & depending on the level
of accuracy you need, I'd recommend storing everything as an integer
with a separate scalar.
Owen.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Wednesday, November 04, 2009 3:26 PM
To: General Discussion of SQLite Database
Cc: Alexander Drozd
Subject: Re: [sqlite] SQLite on PocketBook
On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
> My name is Alexander. I am working on an open-source spaced-
> repetition software project (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very sorry to bother you, but I tried to submit my problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
> The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.
I'm guessing that your hardward does not implement IEEE 754 floating
point correctly. We've seen that sort of thing before, especially
with GCC. There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on PocketBook
On Nov 4, 2009, at 4:53 AM, Alexander Drozd wrote:
>
> My name is Alexander. I am working on an open-source spaced-
> repetition software project (http://code.google.com/p/pbanki/). My
> software relies on SQLite library. I came across some bug-like
> problems with running SQLite on a low-memory e-ink reader device. I
> am very sorry to bother you, but I tried to submit my problem to
> the bugtracker at the SQLite site, and for some reason anonymous
> login failed.
>
> The problem appears at the point of reading real values from an
> SQLite database. I created a simple database
>
> CREATE TABLE cards (
>text TEXT NOT NULL,
>value REAL NOT NULL
> );
>
> I also tried to use NUMERIC and FLOAT instead of REAL. Then I
> inserted a few values:
>
> INSERT INTO cards VALUES('second',100.1);
> INSERT INTO cards VALUES('first', 100.0);
>
> Then I execute "select * from cards order by due" query with sample
> code from http://www.sqlite.org/quickstart.html It works perfectly
> when compiled on desktop computer, but fails on target device. The
> device is PocketBook301+ (http://pocketbook.com.ua/). Unfortunately
> their site does not have an English version. This device is based on
> Samsung S3C2440 AL-40 CPU. It runs under open-source firmware called
> pocketbookfree, that is based on Linux 2.6.18.2 armv4tl.
>
> The above query run on pocketbook returns corrupted values for
> floats if they have a non-zero fractional part:
>
> text = first
> val = 100.0
>
> text = second
> val = 1.90359837350824e+185
>
> Sorting by columns containing float numbers also fails when
> specified with ORDER BY. I am not sure whether this is an issue with
> SQLite or with cross-compiler for PocketBook, but I would greatly
> appreciate any suggestions on how to treat this problem.
I'm guessing that your hardward does not implement IEEE 754 floating
point correctly. We've seen that sort of thing before, especially
with GCC. There are some options to GCC (which escape my memory right
now) that can force it to use strict IEEE 754 floating point rather
than its preferred, speedier but non-standard alternative.
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
