Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jay A. Kreibich]

On Mon, Jun 06, 2011 at 11:27:26PM +0200, Sidney Cadot scratched on the wall:
> Hi Jay,
> 
> > One should never assume a database uses IEEE 754, so one should never
> > assume it uses similar semantics.
> 
> One should not assume it unless it is documented, of course. Postgres,
> for example, half-heartedly embraces IEEE-754 'on platforms that use
> it' (see section 8.1.3 of its manual). It documents the fact that +/-
> infinity and NaN are useable on such systems.

  Well, half-heatedly is about what I would expect, and defines nearly
  every application out there.  IEEE 754 isn't a bad model, it just
  isn't a complete model for most environments.  Adaptations need to
  be made.

> > Even those databases that do use
> > IEEE 754 for a select few of their types have other considerations.
> > In the bigger picture, IEEE 754 makes up, at most, a small part of
> > the SQL numeric environment.
> 
> For SQL: yes. For SQLite though, it is the only option.

  No, it isn't.  SQLite's "numeric environment" consist of more than
  just floating point numbers, and most DBs even have other real-number
  representations.  For that matter, the SQL "numeric environment" goes
  beyond even numbers, since SQL very much has the concept of things like 
  "NULL + 2", and SQL standard needs to be a defined way of dealing
  with such things.

  As it is, SQLite is also somewhat casual about translation between
  floats and integers.  For example, some floating point whole numbers
  are translated into integers before they're stored.  The two types of
  numbers are highly integrated.  You can call that wrong if you want,
  but at the end of the day the math works out the way most people
  expect.  I would rather have an environment that is constant
  across the board then one that's highly specialized, but only for
  specific numeric types, and who knows what happens when they mix.

  Beyond that, SQLite is dependent on the underlying architecture, just
  as Postgres is.  Given the number of low-power embedded processors
  that SQLite runs on, I fully expect there are thousands, if not
  millions, of copies of SQLite running on hardware that is not fully
  IEEE 754 compliant.  In storage, perhaps, but not in calculations.
  Even if we all agreed that making SQLite a perfect 754 environment
  is a Good Idea, it couldn't be done.

> > Using 754 as a reference for the rest of the environment strikes me as
> > poorly thought out and putting the tail before the dog.
> 
> In terms of generic SQL you may be right (although I'd be willing to
> argue for it). However, I think that for a specific DB product, it is
> a good thing to document without ambiguity what the properties and
> guarantees of the numeric types and operations are; and IEEE-754 is
> the only game in town when it comes to properly specified floating
> point numbers.

  OK, how does IEEE 754 define "NULL + 2.0"?  Not "let's pretend NULLs
  are NaN", but a NULL in the fullest SQL sense of that word.  Also
  remember that SQL is based off the Relational Model (which is where
  the concept of NULL comes from), which is a formally defined set of
  mathematics, complete with formal theorems and proofs, so you have to
  work consistently within that formal mathematical model.  That's
  going to be an interesting challenge, given that IEEE 754 doesn't even
  work within the model of basic arithmetic.

  IEEE 754, by itself, isn't enough to define a compute environment
  for something like SQL.  SQL has to extend and adapt it, just like any
  good numeric aware application does.  It is about 99% of what you
  need, but the edges and the details count.  SQL must step beyond just
  IEEE 754 alone, and once you've done that you might as well round off
  the odd and confusing edges.

> I feel this is especially true for the light-weight database system
> that is SQLite. I get the impression that you are advocating to keep
> floating point operations deliberately vague and underspecified
> (please correct me if I am wrong).

  Not at all.  I'm simply trying to say that IEEE 754 is a poor goal
  for this type of environment.  It is a great base, but you need more,
  and you have to go beyond it.

  My calculator returns "error" when I put in 1.0/0.0.  If it didn't,
  I'd throw it out.  I would expect SQL to return an error as well
  (as per the standard).  I would never expect it to return infinitely.
  SQLite's choice of returning NULL may not be fully standardized, but
  it makes a heck of a lot more sense to a database engineer than
  +Inf ever would.  IEEE 754 might say it is correct to return +Inf,
  but to any math student in the world that answer is flat out wrong,
  and most database users will as well, so it makes sense to scrap the
  IEEE 754 view of the world and give people what they expect.  This is
  even more true as IEEE 754 isn't in the center of this world.

  Now, it *would* be nice if there was more available documentation that
  went into how IEEE 754 is adapted 

Re: [sqlite] .import error: cannot open large file

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/06/2011 04:47 PM, Rense Corten wrote:
> Just to report back on this issue: recompiling as per Nuno's
> instructions indeed solved the problem. Still, if anyone can explain
> to me why the original executable would work without problems on a
> different machine, I would be grateful.

You'd need to use strace to find out what system calls are behaving
differently/failing.

The lack of large file support for the shell is a known problem:

  http://www.sqlite.org/src/tktview?name=92af7da36b

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk3tocgACgkQmOOfHg372QREkgCg1M++4/zFH6EwcJQqEVakd2tt
A8AAn1G9wOEVOqIN8mC2ZTwz1cPHoQS1
=Y0ma
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .import error: cannot open large file

[Rense Corten]

Just to report back on this issue: recompiling as per Nuno's
instructions indeed solved the problem. Still, if anyone can explain
to me why the original executable would work without problems on a
different machine, I would be grateful.

>
> On Fri, Jun 3, 2011 at 10:28 AM, Rense Corten  wrote:
>> Thanks for your answer, Nuno.
>> However, the system I am using is already 64-bit (I should have
>> mentioned that) , and the same binary can do the job on another Ubuntu
>> 64-bit  system. I'll try your suggestion nevertheless, but can there
>> be other causes?
>> Rense
>>
>>> It's what the thread says. The SQLite shell on Ubuntu (on 11.04) isn't
>>> compiled with large file support on 32-bit systems, so while the
>>> SQLite library does work with 64-bit database, the shell doesn't.
>>> The easy solution is to either use a 64-bit Ubuntu system or compile
>>> the shell yourself with large file support.
>>>
>>> To compile it, download the sqlite amalgamation files and run:
>>>
>>> gcc -o sqli -O3 -DNDEBUG=1 -D_FILE_OFFSET_BITS=64 sqlite3.c shell.c
>>> -ldl -pthread
>>>
>>> The resulting binary (sqli) will be compiled with large file support
>>> (I verified it was using strace).
>>>
>>>
>>> Regards,
>>> ~Nuno Lucas
>>>
>>> P.S.- While this could be considered an Ubuntu bug, the truth is that
>>> the linux shell binary on the sqlite site also isn't compiled with
>>> large file support, so I would consider this an SQLite bug.
>>>
>>>
>>> --
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Mon, Jun 6, 2011 at 6:28 PM, Simon Slavin  wrote:
> I've rethought my earlier position.  This re-think is the result of the SQL 
> standard being incompatible with the IEEE standard.  If you want to do IEEE 
> arithmetic, do it in your own software, and use SQL just for retrieving the 
> numbers you're going to handle.  You can store those numbers as numbers, text 
> or BLOBs, whatever works for your purposes.

You might as well rip out the numeric aggregation functions then...
Why have them if they're not safe to use?

> If SQLite is to be changed at all, it should be changed to conform to the SQL 
> standard, including treating division by zero as an error.  I regard this 
> change as one worth putting on the bug list, but not one which needs 
> immediate attention.

It seems other RDBMSes do not so much conform to the standard though
as to they implement semantics that could be standardized.  The
relevant standard is behind the times then, no?  Why implement that
instead of interoperable semantics?  To me this really requires more
research, specifically to establish what is the most common subset of
numeric/real semantics in major RDBMSes (there's precendent for this,
as the SQLite3 page on NULL handling demonstrates).

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Simon Slavin]

On 7 Jun 2011, at 12:01am, Nico Williams wrote:

> On Mon, Jun 6, 2011 at 4:58 PM, Jean-Christophe Deschamps
>  wrote:
>> Look at a FP-intensive product like Spatialite (SQLite-based).  You'd
>> probably agree it performs much more complex tasks than average, mean
>> squares and such.
>> I'd be very surprised if it used NaN representations!
> 
> Sure, if you're just computing average() then you'll not get any NaNs.

You will if one of the numbers you're taking an average of is a NaN.

I've rethought my earlier position.  This re-think is the result of the SQL 
standard being incompatible with the IEEE standard.  If you want to do IEEE 
arithmetic, do it in your own software, and use SQL just for retrieving the 
numbers you're going to handle.  You can store those numbers as numbers, text 
or BLOBs, whatever works for your purposes.

If SQLite is to be changed at all, it should be changed to conform to the SQL 
standard, including treating division by zero as an error.  I regard this 
change as one worth putting on the bug list, but not one which needs immediate 
attention.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Mon, Jun 6, 2011 at 4:58 PM, Jean-Christophe Deschamps
 wrote:
> Look at a FP-intensive product like Spatialite (SQLite-based).  You'd
> probably agree it performs much more complex tasks than average, mean
> squares and such.
> I'd be very surprised if it used NaN representations!

Sure, if you're just computing average() then you'll not get any NaNs.
 But you might be dividing averages, or whatever.  It's not just the
aggregate functions, but what you do with their results.

> What I'm saying is that if you (or anyone else) insist on realistic
> scientific FP support in SQLite, then you need _way_ more than just
> NaNs (signaling or not).  FP operations are not even associative in
> general and there are too many cases where a given FP number can be
> decided to be distinct from itself!

No one asks for high precision in DTrace.  Depending on the data set,
precision may not be important, yet the ability to handle infinities
and NaNs might be.  (That said, I think it's clear that there's some
demand for SQLite3 to be extensible such that extended precision FP
libraries could be transparently integrated.  Whether that demand will
be met is another story.  And whether SQLite3's existing, limited FP
functionality is sufficient or not for any particular app is yet
another story.)

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

>> > You have a DOUBLE column where you need to store NaN? ?Go ahead and
>> > store 'NaN' in offending rows.
>>
>> You mean, as a string?
>
>  No, by binding the raw value using the C interfaces as any
>  respectable program would do.

But then I'd lose the ability to actually use those values in
computations. Also, I'm not particularly fond of SQLite's
heterogeneous columns, so I'd like to avoid them. Especially when a
much better solution is staring us in the face.

>  Yes, well, it's been pretty clearly determined that SQLite doesn't
>  deal with floating point numbers for your definition of "as it should."

Great, I am glad that my point got across.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jean-Christophe Deschamps]

Nico,

>There is one thing that makes numerical support in RDBMSes important:
>aggregate functions.
>
>Aggregate functions are critical because they allow one to do much
>analysis at the data source, instead of having to transport it
>elsewhere for analysis.

I agree with you, totally.  Read me again: I never said FP support in 
SQLite or any DBMS is useless, just that complete support and control 
of FP is not in the possibility of SQLite (and probably not achieved in 
any other DBMS).

>Storing them is not the issue.  Getting them as results of arithmetic
>operations in SQL statements is the issue.

Agreed again.  But which kind of computation, statistical analysis or 
other common operation does produce NaNs routinely?  If you get NaNs in 
computing a variance or some other fancy (but basic) chained 
computation, then your data is at fault.  No, not your data: your model.

Before embarking in any computation you absolutely need a clear idea of 
the limits it may trepass either as a final result or in route: 
precision, accuracy, ...

Remember a recent thread here about native SQLite Round() and its 
varying results depending on the system used?  Just such a "naïve' 
Round(), without all the bells and whistles of the many rounding 
options that 754 & al support /*for all operations*/.

Look at a FP-intensive product like Spatialite (SQLite-based).  You'd 
probably agree it performs much more complex tasks than average, mean 
squares and such.
I'd be very surprised if it used NaN representations!

What I'm saying is that if you (or anyone else) insist on realistic 
scientific FP support in SQLite, then you need _way_ more than just 
NaNs (signaling or not).  FP operations are not even associative in 
general and there are too many cases where a given FP number can be 
decided to be distinct from itself!

I encourage anyone interested in learning where the dark corners of FP 
lie to read this document: 
http://hal.archives-ouvertes.fr/docs/00/28/14/29/PDF/floating-point-article.pdf

Since such a "simple" function like round() is non-portable how can one 
expect not "full" but only "reasonable" compliance with things like 
NaNs in a product like SQLite, over the full range of hardware, OS, 
compilers (+ releases + options), wrappers, application language, ... 
it will be used on?  That's plain impossible.



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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jay A. Kreibich]

On Mon, Jun 06, 2011 at 11:33:35PM +0200, Sidney Cadot scratched on the wall:
> On Mon, Jun 6, 2011 at 9:55 PM, Jean-Christophe Deschamps
>  wrote:
> 
> > You have a DOUBLE column where you need to store NaN? ?Go ahead and
> > store 'NaN' in offending rows.
> 
> You mean, as a string?

  No, by binding the raw value using the C interfaces as any
  respectable program would do.
  
> Also, it doesn't work as it should:
>
> sqlite> SELECT 1.0 + 'NaN';
> 1.0

  Yes, well, it's been pretty clearly determined that SQLite doesn't
  deal with floating point numbers for your definition of "as it should."


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

> Given that there are
> many, many SQLite3 applications, it is really not possible to say,
> with a straight face anyways, that no applications would break.

That is true. I would certainly not advocate changing the default behavior.

However the 'once we make a mistake, we can't fix it' idea cannot be
maintained eternially, and I presume there will have to come a day
where sqlite will have a compatibility-breaking upgrade (sililar to
Python2 -> Python3) ... Sqlite has accumulated a handful of warts over
the years, and that would be a good time to fix them.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

On Mon, Jun 6, 2011 at 9:55 PM, Jean-Christophe Deschamps
 wrote:

> You have a DOUBLE column where you need to store NaN?  Go ahead and
> store 'NaN' in offending rows.

You mean, as a string? That's rather a dirty hack. Also, it doesn't
work as it should:


sqlite> SELECT 1.0 + 'NaN';
1.0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Mon, Jun 6, 2011 at 4:27 PM, Sidney Cadot  wrote:
>> If you want bare metal IEEE 754 for your scientific computing
>> application, then you might want to rethink doing your math operations
>> in a data storage system.
>
> You are making it sound as if proper support for IEEE-754 types would
> open up some can of worms for regular users, but I really don't see
> why you think that is the case. They would see an occasional "NaN"
> instead of NULL if they did something naughty; I personally think that
> is a lot more informative.

There's not the slightest doubt in my mind that such a change would
break applications that have baked in the expectation that infinities
and NaNs are NULLs.  I don't know that there are such applications,
only that if there are any such, they will break if SQLite3 begins to
handl such values differently all of a sudden.  Given that there are
many, many SQLite3 applications, it is really not possible to say,
with a straight face anyways, that no applications would break.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

Hi Jay,

> One should never assume a database uses IEEE 754, so one should never
> assume it uses similar semantics.

One should not assume it unless it is documented, of course. Postgres,
for example, half-heartedly embraces IEEE-754 'on platforms that use
it' (see section 8.1.3 of its manual). It documents the fact that +/-
infinity and NaN are useable on such systems.

> Even those databases that do use
> IEEE 754 for a select few of their types have other considerations.
> In the bigger picture, IEEE 754 makes up, at most, a small part of
> the SQL numeric environment.

For SQL: yes. For SQLite though, it is the only option.

> Using 754 as a reference for the rest of the environment strikes me as
> poorly thought out and putting the tail before the dog.

In terms of generic SQL you may be right (although I'd be willing to
argue for it). However, I think that for a specific DB product, it is
a good thing to document without ambiguity what the properties and
guarantees of the numeric types and operations are; and IEEE-754 is
the only game in town when it comes to properly specified floating
point numbers.

I feel this is especially true for the light-weight database system
that is SQLite. I get the impression that you are advocating to keep
floating point operations deliberately vague and underspecified
(please correct me if I am wrong). To me as a developer that is
useless; I will never be able to reason about the correctness of
anything, and I am effectively dependent on the (undocumented) effort
that the makers of the FP implementation did. Effectively, that would
be a return to the pre-IEEE 754 wild west of floating point
calculations.

> [...] Its express purpose was to allow non-technical people to write
> queries and build business applications.

That may have been the optimistic idea 40 years ago, but I think it is
time to admit that this was completely misguided. If 40 years of
relational database experience has taught us anything, it is that
doing proper SQL (anything beyond a basic SELECT) is an actual skill
that requires technical prowess.

> [...] This is what most high-level scripting languages like Perl and Python 
> do.

Perl and Python support NaNs and infinities just fine.

> If you want bare metal IEEE 754 for your scientific computing
> application, then you might want to rethink doing your math operations
> in a data storage system.

You are making it sound as if proper support for IEEE-754 types would
open up some can of worms for regular users, but I really don't see
why you think that is the case. They would see an occasional "NaN"
instead of NULL if they did something naughty; I personally think that
is a lot more informative.

Compare currently:

sqlite> SELECT 0.0/0.0, 1.0/0.0;
|
sqlite>

... versus what I would like to see:

sqlite> SELECT 0.0/0.0, 1.0/0.0;
NaN|Inf
sqlite>

> As you've pointed out, SQLite is more than capable of storing and retrieving 
> non-numeric IEEE 754 values

No, it doesn't support storing and retrieving NaNs. That is an
arbitrary limit that bites those of us who actually know what they are
doing.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Scott Hess]

On Mon, Jun 6, 2011 at 1:46 PM, Nico Williams  wrote:
> I can see two ways to add proper IEEE754 support in a
> backwards-compatible way: a) via a pragma to enable raising exceptions
> or returning +-inf/NaNs, b) by adding a callback via which to report
> such exceptions, with exceptions raised only if the callback is set.
> I would prefer (a), though it'd mean re-compiling statements when the
> pragma is toggled.

You could also add a new type.  Call it ... IEEE754{FLOAT,DOUBLE}.  Or
NATIVE{FLOAT,DOUBLE}, and let the user deal with hardware changes.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Mon, Jun 6, 2011 at 2:55 PM, Jean-Christophe Deschamps
 wrote:
> Allow me to add a humble bit to what Jay just posted.
>
> SQLite, as well as most other RDBMS around, allow you to perform FP
> calculations in SQL statements.  I assume no-one imagines an extended
> FP fine-grain support of hundreds of computation options and status
> reporting be part of SQL or SQLite.  It's just not the purpose of SQL
> and DBMSs in the first place.

I disagree emphatically.

There is one thing that makes numerical support in RDBMSes important:
aggregate functions.

Aggregate functions are critical because they allow one to do much
analysis at the data source, instead of having to transport it
elsewhere for analysis.

Allow me to illustrate via DTrace.  DTrace is a tracing system for
user-land processes and kernel code as well.  It is extremely
powerful, but it can quite easily be used to generate more output than
you might know what to do with, which leads me to the most important
feature of DTrace: aggregation functions.  Just as in SQL, aggregation
functions, and perhaps even more necessary in DTrace than in SQL,
aggregation functions allow one to statistically analyze data at the
source, thus avoiding any need to record or transport DTrace probe
data.

Statistical analysis at the source is a critical performance feature.
It might seem _less_ critical in SQLite3 applications than in
networked RDBMS applications because the "transport" is only within a
single system image, but DTrace proves otherwise.  And even without
the DTrace example, aggregation at the source is still important
because there are complex SQL queries that would require much
programming effort to replicate via simpler queries with aggregation
in the application.  It seems like a shame to say that important
features of this RDBMS are not to be used...

That said, most users of SQLite3 will likely do just fine with IEEE754
doubles.  And those who can't can always build user-defined functions
for doing arithmetic with user-defined REAL types represented as TEXT
or BLOBs.

So I'm not saying that SQLite3 needs to change.  I'm saying that it's
deleterious to say that SQLite3 is not intended to do math.  Because
even if its authors think so, the fact is that statistical analysis at
the source is one of SQL's most powerful features, and SQLite3 _does_
support it, and it'd be quite odd to hear that "well, not really".

> Now if you take scientific computations (there I mean with all
> fine-grain detail support) out of the SQL statements and just look at
> the engine as a mean to store, organize, query data (scientific or
> not), SQLite still offers much flexibility to you.
>
> You have a DOUBLE column where you need to store NaN?  Go ahead and
> store 'NaN' in offending rows.  You need to store +Inf, -Inf, flag
> denormals, a.s.o, just do it by storing such values or flags as BLOBs,
> TEXT or whatever type you find most appropriate.

Storing them is not the issue.  Getting them as results of arithmetic
operations in SQL statements is the issue.

One could use CASE to decide when one would be getting infinities or
NaNs, and then return text or blob representations of those, but means
making statements quite complex just to work around a missing feature.

OTOH, I agree with D.R. Hipp, that more than theoretical justification
is highly desirable here, and backwards-compatibility is highly
desirable too.

I can see two ways to add proper IEEE754 support in a
backwards-compatible way: a) via a pragma to enable raising exceptions
or returning +-inf/NaNs, b) by adding a callback via which to report
such exceptions, with exceptions raised only if the callback is set.
I would prefer (a), though it'd mean re-compiling statements when the
pragma is toggled.

(A way to plug-in a full, transparent, extended-precision numeric
library would also be nice.  This seems feasible via calling out to
the plugin from the relevant OPs in the VM.  A header would be needed
by which to distinguish IEEE754 values from extended precision value
encodings.)

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jean-Christophe Deschamps]

Allow me to add a humble bit to what Jay just posted.

SQLite, as well as most other RDBMS around, allow you to perform FP 
calculations in SQL statements.  I assume no-one imagines an extended 
FP fine-grain support of hundreds of computation options and status 
reporting be part of SQL or SQLite.  It's just not the purpose of SQL 
and DBMSs in the first place.

Now if you take scientific computations (there I mean with all 
fine-grain detail support) out of the SQL statements and just look at 
the engine as a mean to store, organize, query data (scientific or 
not), SQLite still offers much flexibility to you.

You have a DOUBLE column where you need to store NaN?  Go ahead and 
store 'NaN' in offending rows.  You need to store +Inf, -Inf, flag 
denormals, a.s.o, just do it by storing such values or flags as BLOBs, 
TEXT or whatever type you find most appropriate.

Look, I'm a very happy user of Mathematica and some other math 
packages, and I sometimes need to store incredibly large values without 
loss of arbitrary precision.  I don't expect the smallest part of Mma 
being built into SQLite to perform computations.

OTOH, SQLite offers me the opportunity to do so without having to find 
fragile workarounds to get rid of limitations of a fixed, strongly-type 
system.  Should you ever have to perform those fine-grain computations 
with all nickpicking details, then you still can do so either in your 
application or write an extension with all the functions you need, 
without being limited to, for instance, the "naïve" rounding function 
included in SQLite (@DrH: I've no grief here).  You can bring the whole 
Intel decimal FP arbitrary precision library if you whish and still 
keep on using SQLite for what it really is.

My 2 cents

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Jay A. Kreibich]

On Mon, Jun 06, 2011 at 05:36:03PM +0100, Simon Slavin scratched on the wall:

> It's just a shame that the SQL and IEEE standards are mutually incompatible.

  Yes and no.

  First off, I would point out that the SQL standards are *significantly*
  older than the IEEE 754 standard.  Languages that we would recognize
  as early SQL were around 15 years before the first IEEE 754 standard
  was ratified-- and even longer until 754 hardware was available and
  common.


  Secondly, SQL is not really designed around floating point numbers.
  Most databases have a generic "NUMERIC" type that allows arbitrary
  size and precision, and are not tied to any specific floating point
  model.  The fact that SQLite *only* offers the IEEE 754 numbers
  (beyond integers) actually makes it a bit of an oddball.  Traditionally,
  databases use BCD or some similar "bignum" representation of numbers,
  specially to avoid all the representation issues IEEE 754 tries to
  standardize (notice I didn't say "solve").  While most database
  products have moved in the direction of offering more specific types
  that tie directly to common representations, (such as fixed size
  integers and IEEE 754 floating point values), in the 40 year history
  of relational database products, this is a somewhat new thing.

  As such, it makes little sense to define the environment's numeric
  model around a representation that isn't even in the standard.  One
  should never assume a database uses IEEE 754, so one should never
  assume it uses similar semantics.  Even those databases that do use
  IEEE 754 for a select few of their types have other considerations.
  In the bigger picture, IEEE 754 makes up, at most, a small part of
  the SQL numeric environment.  Using 754 as a reference for the rest
  of the environment strikes me as poorly thought out and putting the
  tail before the dog.


  And lastly, even if the IEEE 754 was as standardized back then as it
  is now, and even if SQL did everything in IEEE 754 (as SQLite mostly
  does), I still stand by the idea that it isn't something you want to
  expose to the user in its raw form.  SQL is a user language, not a
  systems programming language.  Its express purpose was to allow
  non-technical people to write queries and build business applications.  
  The vast majority of people working in that environment don't expect
  to deal with specific rounding modes or processor status bits.  They
  just want to do math like their grade school teacher taught them to
  do it.  Adding layers between the computational numeric model and the
  presented numeric model makes sense.  This is what every calculator
  does, be it pocket or desktop.  This is what Excel does.  This is
  what most high-level scripting languages like Perl and Python do.

  If you want bare metal IEEE 754 for your scientific computing
  application, then you might want to rethink doing your math operations
  in a data storage system.  Write a C or assembly program and have all
  the close, fine-grain detail you want.  As you've pointed out, SQLite
  is more than capable of storing and retrieving non-numeric IEEE 754
  values, so it is doing its core job just fine.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 42, Issue 6

[Pete]

Hear, hear!  We're all capable of making our own decisions.  Last time I
looked, the title of this forum was General Discussion of SQLIte database,
hopefully we can get back on topic.
Pete






> Message: 26
> Date: Mon, 6 Jun 2011 01:26:54 +0100
> From: Simon Slavin 
> Subject: Re: [sqlite] Do I need to migrate to MySQL?
> To: General Discussion of SQLite Database 
> Message-ID: <619f829a-7fd4-407a-980a-4a5f0452e...@bigfraud.org>
> Content-Type: text/plain; charset=utf-8
>
>
> On 6 Jun 2011, at 12:20am, Dagdamor wrote:
>
> > Darren Duncan  ?(?) ? ? ?? Mon, 06
> Jun 2011 05:08:45 +0600:
> >
> >> MySQL should not be considered as the default choice of a non-lite SQL
> DBMS, for
> >> projects not currently using it, when you have a choice between multiple
> SQL
> >> DBMSs; instead, the default non-lite choice should be Postgres.
> >
> > Wow, communistic regime is back! Thanks for telling me what I should do
> and what not, what I should use and what not. ;)
>
> Dude, it's just advice.  That's all any of us do: post our opinions.  Calm
> down.
>
> Simon.
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Black, Michael (IS)]

That's the nice thing about standards...there's so many to choose from... :-)



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, June 06, 2011 11:36 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?


On 6 Jun 2011, at 5:30pm, Gabor Grothendieck wrote:

> I and others use SQLite from R and R itself gives Inf, -Inf and NaN
> for 1/0, -1/0 and 0/0 respectively so it would reduce the differences
> between the database and R if it worked in the same way.  Perhaps an
> option could control this behavior so that backwards compatibility
> could be maintained.

While I understand the wish for better support of IEEE-754, I think it might be 
wise to go the whole way rather than make a single improvement.  Ideally some 
sort of maths compatibility PRAGMA which could be set in legacy/IEEE/SQL mode.  
Eventually we could probably lose the legacy mode.

Compatibility with SQL mode is probably just as important as compatibility with 
IEEE, by the way.  Plenty of people seem to prototype their app with SQLite on 
a standalone computer before moving to a multi-user system hosted by another 
implementation of SQL.  So having one SQL act the same as the other is 
important too.  It's just a shame that the SQL and IEEE standards are mutually 
incompatible.

Simon.
___
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] Handling of IEEE-754 nan and +/-inf in SQLite?

[Simon Slavin]

On 6 Jun 2011, at 5:30pm, Gabor Grothendieck wrote:

> I and others use SQLite from R and R itself gives Inf, -Inf and NaN
> for 1/0, -1/0 and 0/0 respectively so it would reduce the differences
> between the database and R if it worked in the same way.  Perhaps an
> option could control this behavior so that backwards compatibility
> could be maintained.

While I understand the wish for better support of IEEE-754, I think it might be 
wise to go the whole way rather than make a single improvement.  Ideally some 
sort of maths compatibility PRAGMA which could be set in legacy/IEEE/SQL mode.  
Eventually we could probably lose the legacy mode.

Compatibility with SQL mode is probably just as important as compatibility with 
IEEE, by the way.  Plenty of people seem to prototype their app with SQLite on 
a standalone computer before moving to a multi-user system hosted by another 
implementation of SQL.  So having one SQL act the same as the other is 
important too.  It's just a shame that the SQL and IEEE standards are mutually 
incompatible.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Gabor Grothendieck]

On Mon, Jun 6, 2011 at 11:54 AM, Richard Hipp  wrote:
> On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot  wrote:
>
>>
>> Would it be useful to open a ticket on this issue, or will it never be
>> changed e.g. for fear of breaking backward compatibility?
>>
>
> There are approx 2 billion legacy apps in the wild that use SQLite.  Not
> breaking things is very important to us, therefore.  So there needs to be a
> compelling reason to make a change like this.  I do not think you have made
> a sufficient case for the change, yet.

I and others use SQLite from R and R itself gives Inf, -Inf and NaN
for 1/0, -1/0 and 0/0 respectively so it would reduce the differences
between the database and R if it worked in the same way.  Perhaps an
option could control this behavior so that backwards compatibility
could be maintained.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Richard Hipp]

On Mon, Jun 6, 2011 at 11:44 AM, Sidney Cadot  wrote:

>
> Would it be useful to open a ticket on this issue, or will it never be
> changed e.g. for fear of breaking backward compatibility?
>

There are approx 2 billion legacy apps in the wild that use SQLite.  Not
breaking things is very important to us, therefore.  So there needs to be a
compelling reason to make a change like this.  I do not think you have made
a sufficient case for the change, yet.


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

[sqlite] Speed comparison

[Cecil Westerhof]

I saw that there is the need for a speed comparison. I have MySQL
(5.1.53) installed (and when necessary I could install PostgreSQL).
Would it be interesting if I made those tests? If yes, what is the
correct way to do it?

I am not using the most recent version (3.7.5), but I suppose that
this would not be a big problem.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

> Ah.  In that case, I /would/ argue that this is bad, and that SQLite should 
> conform to the standard.

That is true, although it is rather unfortunate that the standard
makes this statement, IMHO.

Unfortunately, this doesn't address the point of whether it should be
possible to use NaNs as floating point values, and to store them in
tables. As indicated previously, most databases allow it (at least all
modern ones seem to), and only SQLite makes a positive effort to
handle it as a NULL, introducing (IMHO) strange semantics. I have seen
no convincing argument being put up to defend that design choice, so
far.

Would it be useful to open a ticket on this issue, or will it never be
changed e.g. for fear of breaking backward compatibility?

In any case, I think it would be useful to spend a paragraph or two in
the documentation on the issue of floating point semantics.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Sidney Cadot]

> It'd be OK for NaN to map to NULL, but not for infinity, since there
> is a distinction between positive and negative infinity, and that
> distinction is valuable.

The NaN value in IEEE-754 is also not unique. There is the distinction
between signaling and quiet NaNs, and furthermore mantissa bits can
(and sometimes are) used to carry a tag, conveying information as to
the source of a problem.

Also, I don't particularly see how it is ok for NaN to map to NULL;
they are conceptually quite different. If I may draw an analogy,
mapping NaN to NULL is pretty much the same as mapping the empty
string to NULL.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Howto pivot in SQLite

[Sam Carleton]

Tom,

Stop and think about what you just suggested: The invoice would look
something like this:

 |Qty | Desc  | Price   | Total
-++---+
+| 2  | 5x7   |  9.95   | 18.90
+| 1  | 5x7   |  9.95   | 18.90
+| 1  | 8x10  | 19.95   | 19.95
+| 2  | 16x20 | 49.00   | 98.00
+| 1  | CD| 99.95   | 99.95

Please make note that it looks strange that there are two line items with
5x7's.  Now you add the images, you get the following:

|Qty | Desc  | Imgs | Price   | Total
++---+---
| 2  | 5x7   | Img1 |  9.95   | 18.90
| 1  | 5x7   | Img2 |  9.95   | 18.90
 | 1  | 8x10  | Img2 | 19.95   | 19.95
 | 2  | 16x20 | Img9 | 49.00   | 98.00
| 1  | CD| Img1, Img2, Img3 | 99.95   | 99.95

In a perfect world, I would show the images, too.  That is coming, just not
in the initial release.  There is a lot to come, I just need to get this out
the door as quickly as possible while providing useful information to my
users.

Sam



On Sun, Jun 5, 2011 at 8:17 PM, BareFeetWare wrote:

> On 06/06/2011, at 8:30 AM, Sam Carleton 
> wrote:
>
> > allow the user to select the line and bring up a secondary dialog to
> manage the list of images
>
> You could simply execute a second select when the user asks for the set of
> images for that invoice. It's simpler and more accurate to then iterate
> through the returned rows than to parse a comma separated string.
>
> Tom
> BareFeetWare
> ___
> 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] Handling of IEEE-754 nan and +/-inf in SQLite?

[Simon Slavin]

On 6 Jun 2011, at 1:49pm, Igor Tandetnik wrote:

> Simon Slavin  wrote:
>> 
> 
>> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
> 
> If we talking about the standard, note that both division by zero and numeric 
> overflow are supposed to trigger an error:
> 
> 6.12-General Rules-4: If the value of a divisor is zero, then an exception 
> condition is raised: data exception-division by zero. [snip]
> 
> 
> So SQLite behaves neither in the way expected by the OP, nor in the way 
> prescribed by the standard (I'm not arguing that's a bad thing, just stating 
> the fact).

Ah.  In that case, I /would/ argue that this is bad, and that SQLite should 
conform to the standard.  Whenever someone can get around to it.

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

Re: [sqlite] Bug in .indices

[Cecil Westerhof]

2011/6/6 Igor Tandetnik :
>>> If you are talking about INTEGER PRIMARY KEY column, then no index is shown 
>>> for it because none is created. See also
>>> http://sqlite.org/lang_createtable.html#rowid
>>
>> That is what I am talking about. Something went wrong with my previous 
>> e-mail.
>>
>> The strange thing is that the INTEGER PRIMARY key is not shown when
>> using .indices without an argument, but is shown when I give a table.
>
> It shows nothing for me either way:
>
> sqlite> create table t(x integer primary key);
> sqlite> .indices
> sqlite> .indices t
> sqlite>
>
> Again - no index is created for an INTEGER PRIMARY KEY column, so none is 
> shown.

I was again wrong, but also right. It was not a PRIMARY index, but an
UNIQUE index:
sqlite> .schema weights
CREATE TABLE weights (
  id INTEGERPRIMARY KEY,
  categoryID INTEGERNOT NULL,
  measureDateDATE   DEFAULT (DATE('now')),
  weight FLOAT  NOT NULL,
  fatFLOAT,
  water  FLOAT,
  muscle FLOAT,

  UNIQUE (
categoryID,
measureDate
  )
);
sqlite> .indices
sqlite> .indices weights
sqlite_autoindex_weights_1

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

Re: [sqlite] Bug in .indices

[Igor Tandetnik]

Cecil Westerhof  wrote:
> 2011/6/6 Igor Tandetnik :
>> If you are talking about INTEGER PRIMARY KEY column, then no index is shown 
>> for it because none is created. See also
>> http://sqlite.org/lang_createtable.html#rowid 
> 
> That is what I am talking about. Something went wrong with my previous e-mail.
> 
> The strange thing is that the INTEGER PRIMARY key is not shown when
> using .indices without an argument, but is shown when I give a table.

It shows nothing for me either way:

sqlite> create table t(x integer primary key);
sqlite> .indices
sqlite> .indices t
sqlite>

Again - no index is created for an INTEGER PRIMARY KEY column, so none is shown.
-- 
Igor Tandetnik

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Igor Tandetnik]

Simon Slavin  wrote:
> On 6 Jun 2011, at 4:39am, Sidney Cadot wrote:
>> That's a bit of a philosophical point. In the context of SQLite, the
>> expression 1.0 / 0.0 evaluates to something that is representable,
>> namely NULL, that much is clear. Whether NULL is to be considered a
>> value is up for debate.
> 
> No, it's in the standard.  Unfortunately you have to pay to receive the 
> standards document, but in the draft standard at
> 
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

If we talking about the standard, note that both division by zero and numeric 
overflow are supposed to trigger an error:

6.12-General Rules-4: If the value of a divisor is zero, then an exception 
condition is raised: data exception-division by zero.


6.12-General Rules-6: If the type of the result of an arithmetic operation is 
approximate numeric and the exponent of the approximate mathematical result of 
the operation is not within the implementation-defined exponent range for the 
result type, then an exception condition is raised: data exception-numeric 
value out of range.

3.3.4.1: The phrase "an exception condition is raised:", followed by the name 
of a condition, is used in General Rules and elsewhere to indicate that the 
execution of a statement is unsuccessful, application of General Rules, other 
than those of Subclause 12.3, "", and Subclause 20.1, "", may be terminated, diagnostic information is to be made available, 
and execution of the statement is to have no effect on SQL-data or schemas.


So SQLite behaves neither in the way expected by the OP, nor in the way 
prescribed by the standard (I'm not arguing that's a bad thing, just stating 
the fact).
-- 
Igor Tandetnik

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

Re: [sqlite] Bug in .indices

[Cecil Westerhof]

2011/6/6 Igor Tandetnik :
> Cecil Westerhof  wrote:
>> The help says that .indices shows all indices. But it shows at least
>> not the PRIMARY KEY indices.
>> When using:
>>    .indices
>> I get nothing.
>
> Works for me:
>
> sqlite> create table t(x text primary key);
> sqlite> .indices t
> sqlite_autoindex_t_1
>
> If you are talking about INTEGER PRIMARY KEY column, then no index is shown 
> for it because none is created. See also 
> http://sqlite.org/lang_createtable.html#rowid

That is what I am talking about. Something went wrong with my previous e-mail.

The strange thing is that the INTEGER PRIMARY key is not shown when
using .indices without an argument, but is shown when I give a table.

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

Re: [sqlite] Problem with create table and strftime

[Cecil Westerhof]

2011/6/6 Igor Tandetnik :
>> In a create table I have:
>>      measureDate            DATE           UNIQUE      DEFAULT
>> (strftime('%Y-%m-%d', 'now')),
>> but when I look with .schema, I get:
>>      measureDate            DATE           DEFAULT (Datetime('now')),
>>
>> The UNIQUE constraint is disappeared and the DEFAULT is changed.
>
> This sounds extremely unlikely. If I had to guess, I'd suspect you already 
> have the table in the database, with the old schema, and the new CREATE TABLE 
> statement fails.

You are right. I made a mistake. My excuses for the noise.

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

[sqlite] Bug in .indices

[Cecil Westerhof]

Something went wrong, so again.

The help says that .indices shows all indices. But it shows at least
not the PRIMARY KEY indices.
When using:
   .indices
I get nothing.

When using:
.indices weights
I get:
sqlite_autoindex_weights_1

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

Re: [sqlite] Problem with create table and strftime

[Igor Tandetnik]

Cecil Westerhof  wrote:
> In a create table I have:
>  measureDateDATE   UNIQUE  DEFAULT
> (strftime('%Y-%m-%d', 'now')),
> but when I look with .schema, I get:
>  measureDateDATE   DEFAULT (Datetime('now')),
> 
> The UNIQUE constraint is disappeared and the DEFAULT is changed.

This sounds extremely unlikely. If I had to guess, I'd suspect you already have 
the table in the database, with the old schema, and the new CREATE TABLE 
statement fails.
-- 
Igor Tandetnik

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

Re: [sqlite] Bug in .indices

[Igor Tandetnik]

Cecil Westerhof  wrote:
> The help says that .indices shows all indices. But it shows at least
> not the PRIMARY KEY indices.
> When using:
>.indices
> I get nothing.

Works for me:

sqlite> create table t(x text primary key);
sqlite> .indices t
sqlite_autoindex_t_1

If you are talking about INTEGER PRIMARY KEY column, then no index is shown for 
it because none is created. See also 
http://sqlite.org/lang_createtable.html#rowid
-- 
Igor Tandetnik

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

[sqlite] Bug in .indices

[Cecil Westerhof]

The help says that .indices shows all indices. But it shows at least
not the PRIMARY KEY indices.
When using:
.indices
I get nothing.

When using:


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

Re: [sqlite] Spatialite in Xcode

[BareFeetWare]

On 06/06/2011, at 9:32 PM, Jan wrote:

> did you already check the SpatiaLite website. There is a whole chapter 
> about integrating SpatiaLite in different ways (OSes). I took a look at 
> it myself  yesterday (though Xcode was not the reason) - very good website.

Thanks for the reply.

Yes, I've checked the website, googled, tried, tested, pulled hair etc. No real 
success yet. All my attempts fail to either compile or fail to link.

Anyone done it?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Spatialite in Xcode

[Jan]

Hi,

did you already check the SpatiaLite website. There is a whole chapter 
about integrating SpatiaLite in different ways (OSes). I took a look at 
it myself  yesterday (though Xcode was not the reason) - very good website.

Jan

Am 06.06.2011 13:15, schrieb BareFeetWare:
> Hi all,
>
> I'm trying to add spatialite, a library of geographic/spatial/geometric 
> extensions for SQLite, to my existing SQLite project in Xcode. Has anyone 
> here done this successfully? If so, how, with step by step instructions 
> please :-)
>
> Any help appreciated.
>
> Thanks,
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>   --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> 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

[sqlite] Spatialite in Xcode

[BareFeetWare]

Hi all,

I'm trying to add spatialite, a library of geographic/spatial/geometric 
extensions for SQLite, to my existing SQLite project in Xcode. Has anyone here 
done this successfully? If so, how, with step by step instructions please :-)

Any help appreciated.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Nico Williams]

On Mon, Jun 6, 2011 at 5:57 AM, Simon Slavin  wrote:
> No, it's in the standard.  Unfortunately you have to pay to receive the 
> standards document, but in the draft standard at
>
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
> see the top of page 7:
>
> "null value (null): A special value, or mark, that is used to
>            indicate the absence of any data value."

But since infinity is not "absence of data", nor is NaN...

It'd be OK for NaN to map to NULL, but not for infinity, since there
is a distinction between positive and negative infinity, and that
distinction is valuable.

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

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

[Simon Slavin]

On 6 Jun 2011, at 4:39am, Sidney Cadot wrote:

> Hi Simon,
> 
>> But you were using a SQL command to make the match.
> 
> Well, I was using it to demonstrate some behavior I observed, yes. I
> was not "matching values with NULL". But whatever.
> 
>> You executed a SELECT command and got an answer from SQL.  That answer does 
>> not mean
>> "The result of the calculation '1.0 / 0.0' is the value NULL."
> 
> That's a bit of a philosophical point. In the context of SQLite, the
> expression 1.0 / 0.0 evaluates to something that is representable,
> namely NULL, that much is clear. Whether NULL is to be considered a
> value is up for debate.

No, it's in the standard.  Unfortunately you have to pay to receive the 
standards document, but in the draft standard at

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

see the top of page 7:

"null value (null): A special value, or mark, that is used to
indicate the absence of any data value."

> It is a bit weird to be able to represent
> "that what cannot be represented", so I'd take the IMHO simpler
> interpretation of just accepting NULL as a value of any non NOT NULL
> domain.
> 
>> because you asked SQL, not a maths library.  In this case, it's unknown.  
>> Because SQLite doesn't know how to do that calculation.
> 
> Accepting that, my question becomes: why does SQLite elect to not know
> what to do? The handling of NaN results is special cased in the code
> (see http://www.sqlite.org/cvstrac/chngview?cn=5066). Why not just
> accept the existence of NaN as a valid floating point value? Postgres,
> Mysql, and (from a quick google) Oracle and DB2 do this. SQLite is
> really the odd one out, here.

Yep.  There you have a point.  It's arguable that SQLite's implementation of 
maths should be closer to the IEEE standard.  But SQLite being small and light 
I'm not astounded that it doesn't.

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

Re: [sqlite] Proper way to escape table name and column name

[Richard Hipp]

On Mon, Jun 6, 2011 at 6:14 AM, Jean-Christophe Deschamps
wrote:

>
> >What is the official way to escape table name that contains a space
> >and column name that contain a spaces?
>
> You can use square brakets or double-quotes:
>
> [This is a long name for a small table]
> "This is a long name for a small table as well"
>
>
Double-quotes is the official (portable) SQL way of quoting identifiers.
Square bracket quoting is provided by SQLite for compatibility with SQL
Server.


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



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

Re: [sqlite] Proper way to escape table name and column name

[Jean-Christophe Deschamps]

>What is the official way to escape table name that contains a space 
>and column name that contain a spaces?

You can use square brakets or double-quotes:

[This is a long name for a small table]
"This is a long name for a small table as well"

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

[sqlite] Problem with create table and strftime

[Cecil Westerhof]

In a create table I have:
  measureDateDATE   UNIQUE  DEFAULT
(strftime('%Y-%m-%d', 'now')),
but when I look with .schema, I get:
  measureDateDATE   DEFAULT (Datetime('now')),

The UNIQUE constraint is disappeared and the DEFAULT is changed.

I am using SQLite version 3.7.5.

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

[sqlite] Proper way to escape table name and column name

[Marco Bambini]

What is the official way to escape table name that contains a space and column 
name that contain a spaces?
Thanks a lot.
--
Marco Bambini
http://www.sqlabs.com








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

Re: [sqlite] Big difference in performance between Python and gcc

[Alessandro Marzocchi]

2011/6/1 Dominique Pellé :
> Alessandro Marzocchi wrote:
>
>> Hello,
>>    I made some preliminary tests for an application storing big
>> chunks of data in a sqlite database. I did firsts tests with python
>> and they gave me quite impressive results. I then tried to make the
>> same test using C. I expected to get execution times to be the same of
>> those of python. However I was surprised as the performance got a lot
>> worse, with execution times being more than 3 times more.
>
> ...snip...
>
>>  if(SQLITE_OK!=(ret=sqlite3_prepare_v2(db,
>>    "INSERT INTO helloworld VALUES (?,?,?)",
>>    -1,
>>    _stm,
>>    NULL
>>  )))
>
> Since you're going to always insert 1 in the first
> column, why not use:
>
> INSERT INTO helloworld VALUES (1,?,?)
>
>
>>  {
>>    fprintf(stderr,"sqlite error in prepare() [%d]",ret);
>>    return -1;
>>  };
>>
>>  int i;
>>  char data[1024*8+1];
>>  for(i=0;i<1024*8;i++)data[i]='0';
>>  data[1024*8]='\0';
>
> Since the data column also does not change,
> you could bind it only once before entering
> the for loop (rather than binding it at each loop
> iteration)
>
>
>>  for(i=0;i<10;i++)
>>  {
>>    if(!(i%1))printf("%d\n",i);
>>
>>    if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 1, 1)))
>>    {
>>      fprintf(stderr,"sqlite error in bind()");
>>      return -1;
>>    }
>
> Above bind is useless if you used:
> INSERT INTO helloworld VALUES (1,?,?)
>
>
>>    if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 2, i)))
>>    {
>>      fprintf(stderr,"sqlite error in bind()");
>>      return -1;
>>    }
>>    //if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,-1,
>> SQLITE_STATIC/*SQLITE_TRANSIENT*/)))
>>    if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192,
>> SQLITE_STATIC/*SQLITE_TRANSIENT*/)))
>>    {
>>      fprintf(stderr,"sqlite error in bind_blob() [%d] ok=%d",ret,SQLITE_OK);
>>      return -1;
>>    }
>
> above bind to blob can be moved outside the loop.
>
>
>>    ret=sqlite3_step(db_stm);
>>    if(ret!=SQLITE_DONE)
>>    {
>>      fprintf(stderr,"sqlite error in sqlite3_step() [%d]",ret);
>>      return -1;
>>    }
>>    if(SQLITE_OK!=(ret=sqlite3_reset(db_stm)))
>>    {
>>      fprintf(stderr,"sqlite error in sqlite3_reset() [%d]",ret);
>>      return -1;
>>    }
>>    sqlite3_clear_bindings(db_stm);
>
> Calling sqlite3_clear_binding(...) at each iteration
> is not needed in your example.  In fact, you should
> not call it at all if you decide to move the bind to the blob
> outside the loop.
>
> That should make it faster.
I used this structure as it is more representative of what the queries
the real application will do...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Big difference in performance between Python and gcc

[Alessandro Marzocchi]

On the weekend I had way to test the same code on a linux box...
performance there are as expected (with C performing slightly better
than python with a ~20% difference in execution times between the
two). I'll try disabling thread, as they could give a big performance
hit on win system.


2011/6/3 Roger Binns :
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 06/02/2011 02:17 PM, Simon Slavin wrote:
>>  Do you understand the strange result the OP reported ?
>
> There is no evidence that Python is any way relevant to this issue and the
> OP appears to have gone silent.  I expect the actual cause is how SQLite was
> compiled.  Both Python wrappers include the amalgamation statically within
> the extension (ie there is no DLL or dynamic linking involved) on Windows.
>
> Edzard also showed how much faster my wrapper is that the standard Python
> one.  I aim to please :)  The other differences are documented here:
>
>  http://apidoc.apsw.googlecode.com/hg/pysqlite.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk3oSPUACgkQmOOfHg372QRwxgCgjW9Y4X52DSe9XqUWiOfTkqO1
> tPsAn3sry8hFbioD6mHOXsCfWfJIo3XM
> =KIcg
> -END PGP SIGNATURE-
> ___
> 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] HELP: sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

[Sridhar Polavarapu]

Changing the driver helped in improving the performance drastically.

Sridhar

On 03-06-2011 20:05, Sridhar Polavarapu wrote:
> This is not any update or insert statement. This is just a select 
> statement. Will that help if i change the driver ? I am currently 
> using sqlitejdbc-v056.jar
>
> Thanks
> Sridhar
>
> On 03-06-2011 18:59, Sridhar Polavarapu wrote:
>> Hi
>>
>> One of my sqlite query executes faster in sqlite manager( firefox 
>> addon ) but take a lot of time to execute using a java program. Here 
>> is the snippet
>>
>> PreparedStatement mStatement;
>> ResultSet mResults;
>> CachedRowSetImpl impl=null;
>> Connection mconn = null;
>> try
>> {
>> Class.forName("org.sqlite.JDBC");
>> mconn = DriverManager.getConnection("jdbc:sqlite:"+ 
>> "database.db");
>> mStatement = mconn.prepareStatement(jobQuery);
>> long startTime = System.currentTimeMillis();
>> mResults = mStatement.executeQuery();
>> long endTime = System.currentTimeMillis();
>> System.out.println(jobQuery);
>> System.out.println("Time taken to execute query ==>" 
>> +(endTime - startTime));
>> impl = new CachedRowSetImpl();
>> impl.populate(mResults);
>> mStatement.close();
>> mResults.close();
>>
>> }
>> catch (SQLException e) {
>> e.printStackTrace();
>> }
>>
>> Is there anything that I am missing while connecting using java ? Any 
>> help appreciated.
>>
>> Sridhar
>

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

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

[Sridhar Polavarapu]

Thanks Pavel for your replies. The reason looks to be the driver I am 
using to connect sqlite. I have changed it to use the driver from xerial 
and it is working fine now.

Sridhar

On 03-06-2011 12:00, Sridhar Polavarapu wrote:
> Here is the code of my TestStatusDate
>
> public static void main(String[] args) throws Exception
> {
> String jobQuery = "SELECT j.jobId, c.channelName , 
> datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality, 
> j.jobCompleteDate FROM Job j, Channel c where c.channelId = 
> j.jobChannelId ORDER BY jobId DESC";
>
> long startTime = System.currentTimeMillis();
> Statement mStatement;
> ResultSet mResults;
> CachedRowSetImpl impl=null;
> Connection mconn = null;
> try
> {
> Class.forName("org.sqlite.JDBC");
> mconn = DriverManager.getConnection("jdbc:sqlite:"+ 
> "database.db");
> mStatement = mconn.createStatement();
> mResults = mStatement.executeQuery(jobQuery);
> impl = new CachedRowSetImpl();
> impl.populate(mResults);
> mStatement.close();
> mResults.close();
>
> }
> catch (SQLException e) {
> e.printStackTrace();
> }
> long endTime = System.currentTimeMillis();
>
> Locale l = Locale.getDefault();
> System.out.println("Today's Locale is " + l);
>
> System.out.println("timezone ==>" + (TimeZone.getDefault()));
>
> System.out.println("Time taken to execute query ==>" +(endTime 
> - startTime));
>
> try {
> while(impl.next()){
> StringBuilder sb = new StringBuilder();
> sb.append(impl.getLong(1)+",");
> sb.append(impl.getString(2)+",");
> sb.append(impl.getString(3)+",");
> sb.append(impl.getString(4)+",");
> sb.append(impl.getString(5)+",");
> sb.append(impl.getString(6)+",");
>
> System.out.println(sb.toString());
>
> }
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> }
>
> On 02-06-2011 22:02, Pavel Ivanov wrote:
>> And your TestStatusDate code is?
>>
>> I'd bet now that the problem is in the way you get that time from
>> jdbc. It can convert the time back to utc for you, although it looks a
>> little strange that it converts one date and doesn't convert other
>> one...
>>
>>
>> Pavel
>>
>>
>> On Thu, Jun 2, 2011 at 11:52 AM, Sridhar Polavarapu
>>   wrote:
>>> I am on Windows 7 64 bit; I have tested the program as you 
>>> mentioned, the
>>> good point here was the issue still appears please find logs . Let 
>>> me know
>>> if you infer anything from below.
>>>
>>> C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java
>>>  
>>>
>>> TestStatusDate
>>> Today's Locale is en_IN
>>> timezone
>>> ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=1980,dstSavings=0,useDaylight=false,transitions=6,lastRule=null]
>>>  
>>>
>>> Time taken to execute query ==>3623
>>> 762,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:53,COMPLETE,md,2011-06-02 13:26:14,
>>> 761,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:52,COMPLETE,md,2011-06-02 13:26:07,
>>> 760,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:52,COMPLETE,md,2011-06-02 13:25:59,
>>> 759,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:51,COMPLETE,md,2011-06-02 13:25:41,
>>> 758,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:50,COMPLETE,md,2011-06-02 13:25:36,
>>> 757,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:50,COMPLETE,md,2011-06-02 13:25:30,
>>> 756,automatedTest_13067437059065180049472173,2011-06-02
>>> 13:24:49,COMPLETE,md,2011-06-02 13:25:23,
>>> 755,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:09,COMPLETE,md,2011-06-02 13:17:30,
>>> 754,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:08,COMPLETE,md,2011-06-02 13:17:25,
>>> 753,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:07,COMPLETE,md,2011-06-02 13:17:12,
>>> 752,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:07,COMPLETE,md,2011-06-02 13:16:51,
>>> 751,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:06,COMPLETE,md,2011-06-02 13:16:48,
>>> 750,automatedTest_130675855965820033590511079,2011-06-02
>>> 13:16:05,COMPLETE,md,2011-06-02 13:19:17,
>>> 749,automatedTest_1307018964144181250169479584,2011-06-02
>>> 12:49:30,COMPLETE,hi,2011-06-02 12:57:58,
>>> 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59,
>>> 747,automatedTest_1307018192020180478065994419,2011-06-02
>>> 12:36:39,COMPLETE,hi,2011-06-02 12:56:30,
>>> 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33,
>>> 745,automatedTest_130675855965720033590323324,2011-06-02
>>> 

Re: [sqlite] Do I need to migrate to MySQL?

[Dagdamor]

Darren Duncan  писал(а) в своём письме Mon, 06 Jun 
2011 05:43:47 +0600:

> What I'm saying is analogous to saying people should default to wearing 
> helmets
> when riding bikes and only not wear helmets on bikes when they can justify it.
> You'll still get where you're going either way, but one way is the typically
> safer one.

Actually, what you're saying is: "you should wear red helmets, not the green 
ones", and only because you heard somewhere that red helmets are "better". 
Using Postgre is not much safer than using MySQL; both are programs with 
inevitable bugs and imperfections in the code.

You don't even understand that each tool is for its job; running small and 
medium web application (we aren't talking about monsters like 
Google/Facebook/YouTube/Wikipedia here, although all of them also use MySQL 
:)), especially where transactions aren't needed and SELECT speed is a primary 
goal, is a very common task. You just say: for each new project, Postgre 
"should" be used. No, it shouldn't. It depends on what you are planning to do, 
in the first place.

> The Postgres makers take quality and reliability as top concerns, and have 
> for a
> long time, so to make the product much more solid.  They have high standards 
> for
> declaring the DBMS production ready and lengthy testing/shakeout periods.

Any advertizing of any product says the same. You really believe MySQL team has 
any different approach, that their standards are lower? Up to the recent days, 
Postgre was sadly known as "one of the slowest DBMSes ever". Is this a "high 
standard"? What's the point in "quality and reliability" if you simply can't 
get enough number of queries in a second, if Postgre cannot handle your 
server's load?

By the way, does Postgre support clustering/replication natively? MySQL does.

> I don't believe that MySQL development has anywhere near this kind of rigor.

What makes you say that? I submitted few bug reports/feature requests in the 
past, some of them were approved, some argued, but in all cases, I've got fast 
and professional reply from the team. Just like here :) For a free product, 
this is an excellent approach.

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