[sqlite] Outer Join with Where terms - possible optimization

[Dan Kennedy]

On 04/29/2016 09:41 PM, Josef Ku?era wrote:
> Hello,
> I have discovered a possible query planner improvement. I am using the SQLite 
> with virtual table modules.
>
> Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, 
> B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE 
> V.B_ROWID=?', where A and B are virtual tables. If I am not mistaken the 
> WHERE term effectively turns the outer join to an inner join and the query 
> could be evaulated as two fast key searches. Unfortunately, currently the 
> join is left as is and the execution peforms a sequentail full-scan of the A 
> table with many key searches to the B table.
>
> Situation 2: How does SQLite evaluate tables to the left of a left join? As 
> it seems it is done by order in the SQL. So query like this: select * from A 
> join B on B.ref=A.key left outer join C on C.ref=A.key where C.Value=? causes 
> a full scan of A and B before even evaulating C. By looking at the query the 
> B table does not seem to be a pre-requisite of table C, although is it marked 
> as one in where trace.

At the moment, SQLite processes everything to the left of the LEFT (or 
CROSS) JOIN before everything to the right of it.

Dan.

[sqlite] Illegal SQL not rejected

[Jann Roder]

> 
>
> If a HAVING clause is specified, it is evaluated once for each group of
> rows as a boolean expression .
> If the result of evaluating the HAVING clause is false, the group is
> discarded. If the HAVING clause is an aggregate expression, it is evaluated
> across all rows in the group. If a HAVING clause is a non-aggregate
> expression, it is evaluated with respect to an arbitrarily selected row
> from the group. The HAVING expression may refer to values, even aggregate
> functions, that are not in the result.
>
> 
>
> So, it turns out that SQLite is documented as accepting the statement. So
> this is not a "bug", per se. But basing a resultant row on "evaluated with
> respect an arbitrarily selected row from the group" seems "un-useful".

Thanks John. That matches the behaviour I observed. I always liked about
SQL (at least how it is implemented in MS SQL Server) that it forces you
to be non-ambiguous, so the result is deterministic.

In this case this led to a quite subtle bug in my program because the
result was right (as in what I expected) most of the time, since it
picked the right row, but not always.

Jann


Winton Capital Management Limited (?Winton?) is a limited company registered in 
England and Wales with its registered offices at 16 Old Bailey, London, EC4M 
7EG (Registered Company No. 3311531). Winton is authorised and regulated by the 
Financial Conduct Authority in the United Kingdom, registered as an investment 
adviser with the US Securities and Exchange Commission, registered with the US 
Commodity Futures Trading Commission and a member of the National Futures 
Association in the United States.

This communication, including any attachments, is confidential and may be 
privileged. This email is for use by the intended recipient only. If you 
receive it in error, please notify the sender and delete it. You should not 
copy or disclose all or any part of this email.

This email does not constitute an offer or solicitation and nothing contained 
in this email constitutes, and should not be construed as, investment advice. 
Prospective investors should request offering materials and consult their own 
advisers with respect to investment decisions and inform themselves as to 
applicable legal requirements, exchange control regulations and taxes in the 
countries of their citizenship, residence or domicile. Past performance is not 
indicative of future results.

Winton takes reasonable steps to ensure the accuracy and integrity of its 
communications, including emails. However Winton accepts no liability for any 
materials transmitted. Emails are not secure and cannot be guaranteed to be 
error free. Winton handles personal information in accordance with its privacy 
notice.

[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used

[Keith Medcalf]

> I am able to recreate the exception here.  The value of Int64.MaxValue is
> 9223372036854775807.  The UnixEpoch values are measured in seconds from
> the epoch 1970-01-01 00:00:00Z.  Adding 9223372036854775807 seconds to the
> UnixEpoch would result in a DateTime far beyond the allowed maximum value
> for DateTime in the .NET Framework, which is -12-31 23:59:59.999.

dotNet uses 0001-01-01 00:00:00.000 as the epoch and integer increment is 
hundredths of a microsecond since the epoch.  This makes the largest dotNet 
timevalue 31550644800 which is a bit more than a third of 
Int64.MaxValue.

The maximum seconds increment of the unix epoch is 253370851200 after which you 
will need 5 digits for the year, and the minimum value is -62135596800, the 
next second below which is 0001/12/31 23:59:59.00 BC.

[sqlite] Illegal SQL not rejected

[Marc L. Allen]

That error is saying that you can't using HAVING on a column unless it's in a 
group by or it's referenced in an aggregate in the HAVING clause

You could say, HAVING SUM(A) <> 0 or something.

The query as stated

SELECT SUM(A)
...
HAVING A<>0

makes no sense because A is not in the select list.  Only SUM(A) is.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
Sent: Friday, April 29, 2016 1:42 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Illegal SQL not rejected

Hi,

On Fri, Apr 29, 2016 at 1:28 PM, John McKown  
wrote:
> On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
> 
> wrote:
>
>> Hi,
>> It seems like a too obvious omission to not be intentional. But I 
>> wonder why a query like
>>
>> SELECT SUM(A)
>> FROM TABLE
>> GROUP BY B
>> HAVING A <> 0
>>
>> Is not rejected. MS SQL server gives you this error message in this case:
>>
>> Column ?A? is invalid in the HAVING clause because it is not 
>> contained in either an aggregate function or the GROUP BY clause.
>>
>
> I agree that it just looks _wrong_. And PostgreSQL certainly complains 
> about it.

This is weird because A is part of sum(A), which IS aggregate function call.
Or am I missing something?

Thank you.

>
>
>
>>
>> It's not even clear to me what SQLite does with a query like that.
>>
>
> I executed the above both with the HAVING clause and without it. I 
> also did an EXPLAIN on both. Judging by the EXPLAIN output and the 
> actual output, what it seems to do is exclude rows which have SUM(A) 
> equal to zero. I.e. the HAVING is acting on the SUM(A).
>
>
>>
>> Jann
>>
>
>
> --
> The unfacts, did we have them, are too imprecisely few to warrant our 
> certitude.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Illegal SQL not rejected

[Jann Roder]

Hi,
It seems like a too obvious omission to not be intentional. But I wonder why a 
query like

SELECT SUM(A)
FROM TABLE
GROUP BY B
HAVING A <> 0

Is not rejected. MS SQL server gives you this error message in this case:

Column ?A? is invalid in the HAVING clause because it is not contained in 
either an aggregate function or the GROUP BY clause.

It's not even clear to me what SQLite does with a query like that.

Jann



Winton Capital Management Limited (?Winton?) is a limited company registered in 
England and Wales with its registered offices at 16 Old Bailey, London, EC4M 
7EG (Registered Company No. 3311531). Winton is authorised and regulated by the 
Financial Conduct Authority in the United Kingdom, registered as an investment 
adviser with the US Securities and Exchange Commission, registered with the US 
Commodity Futures Trading Commission and a member of the National Futures 
Association in the United States.

This communication, including any attachments, is confidential and may be 
privileged. This email is for use by the intended recipient only. If you 
receive it in error, please notify the sender and delete it. You should not 
copy or disclose all or any part of this email.

This email does not constitute an offer or solicitation and nothing contained 
in this email constitutes, and should not be construed as, investment advice. 
Prospective investors should request offering materials and consult their own 
advisers with respect to investment decisions and inform themselves as to 
applicable legal requirements, exchange control regulations and taxes in the 
countries of their citizenship, residence or domicile. Past performance is not 
indicative of future results.

Winton takes reasonable steps to ensure the accuracy and integrity of its 
communications, including emails. However Winton accepts no liability for any 
materials transmitted. Emails are not secure and cannot be guaranteed to be 
error free. Winton handles personal information in accordance with its privacy 
notice.

[sqlite] [System.Data.SQLite] int overflow in date handling when unixepoch is used

[Jann Roder]

Makes sense,
Somehow I had in my head that int64 would still only get you to the year 5000 
or so.
I guess the question is if it should fail like that in this case. Capping it at 
DateTime.MaxValue does not seem ideal either.

Jann

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joe 
Mistachkin
Sent: 28 April 2016 19:54
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [System.Data.SQLite] int overflow in date handling when 
unixepoch is used


Jann Roder wrote:
>
> I just tested the new version that should have the fix for this and
> now I get an ArgumentOutOfRangeException exception when I actually
> have a value of Int64.MaxValue in a date column. Stack trace:
>

I am able to recreate the exception here.  The value of Int64.MaxValue is 
9223372036854775807.  The UnixEpoch values are measured in seconds from the 
epoch 1970-01-01 00:00:00Z.  Adding 9223372036854775807 seconds to the 
UnixEpoch would result in a DateTime far beyond the allowed maximum value for 
DateTime in the .NET Framework, which is -12-31 23:59:59.999.

--
Joe Mistachkin

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



Winton Capital Management Limited (?Winton?) is a limited company registered in 
England and Wales with its registered offices at 16 Old Bailey, London, EC4M 
7EG (Registered Company No. 3311531). Winton is authorised and regulated by the 
Financial Conduct Authority in the United Kingdom, registered as an investment 
adviser with the US Securities and Exchange Commission, registered with the US 
Commodity Futures Trading Commission and a member of the National Futures 
Association in the United States.

This communication, including any attachments, is confidential and may be 
privileged. This email is for use by the intended recipient only. If you 
receive it in error, please notify the sender and delete it. You should not 
copy or disclose all or any part of this email.

This email does not constitute an offer or solicitation and nothing contained 
in this email constitutes, and should not be construed as, investment advice. 
Prospective investors should request offering materials and consult their own 
advisers with respect to investment decisions and inform themselves as to 
applicable legal requirements, exchange control regulations and taxes in the 
countries of their citizenship, residence or domicile. Past performance is not 
indicative of future results.

Winton takes reasonable steps to ensure the accuracy and integrity of its 
communications, including emails. However Winton accepts no liability for any 
materials transmitted. Emails are not secure and cannot be guaranteed to be 
error free. Winton handles personal information in accordance with its privacy 
notice.

[sqlite] Outer Join with Where terms - possible optimization

[Josef Kučera]

Hello,
I have discovered a possible query planner improvement. I am using the SQLite 
with virtual table modules.

Situation 1: Lets have a query like 'SELECT V.* FROM (select A.RowID A_ROWID, 
B.RowID B_ROWID, A.*, B.* from A left outer join B on B.ref=A.key) V WHERE 
V.B_ROWID=?', where A and B are virtual tables. If I am not mistaken the WHERE 
term effectively turns the outer join to an inner join and the query could be 
evaulated as two fast key searches. Unfortunately, currently the join is left 
as is and the execution peforms a sequentail full-scan of the A table with many 
key searches to the B table.

Situation 2: How does SQLite evaluate tables to the left of a left join? As it 
seems it is done by order in the SQL. So query like this: select * from A join 
B on B.ref=A.key left outer join C on C.ref=A.key where C.Value=? causes a full 
scan of A and B before even evaulating C. By looking at the query the B table 
does not seem to be a pre-requisite of table C, although is it marked as one in 
where trace.

Is this correct?

Thanks.
Joe

[sqlite] Illegal SQL not rejected

[Igor Korot]

Marc,

On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen
 wrote:
> That error is saying that you can't using HAVING on a column unless it's in a 
> group by or it's referenced in an aggregate in the HAVING clause
>
> You could say, HAVING SUM(A) <> 0 or something.
>
> The query as stated
>
> SELECT SUM(A)
> ...
> HAVING A<>0
>
> makes no sense because A is not in the select list.  Only SUM(A) is.

Does anybody in MS "speak any English?" (C) ;-)

Thank you.

P.S.: This is a rhetorical question, BTW.

>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Igor 
> Korot
> Sent: Friday, April 29, 2016 1:42 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Illegal SQL not rejected
>
> Hi,
>
> On Fri, Apr 29, 2016 at 1:28 PM, John McKown  gmail.com> wrote:
>> On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder
>> 
>> wrote:
>>
>>> Hi,
>>> It seems like a too obvious omission to not be intentional. But I
>>> wonder why a query like
>>>
>>> SELECT SUM(A)
>>> FROM TABLE
>>> GROUP BY B
>>> HAVING A <> 0
>>>
>>> Is not rejected. MS SQL server gives you this error message in this case:
>>>
>>> Column ?A? is invalid in the HAVING clause because it is not
>>> contained in either an aggregate function or the GROUP BY clause.
>>>
>>
>> I agree that it just looks _wrong_. And PostgreSQL certainly complains
>> about it.
>
> This is weird because A is part of sum(A), which IS aggregate function call.
> Or am I missing something?
>
> Thank you.
>
>>
>>
>>
>>>
>>> It's not even clear to me what SQLite does with a query like that.
>>>
>>
>> I executed the above both with the HAVING clause and without it. I
>> also did an EXPLAIN on both. Judging by the EXPLAIN output and the
>> actual output, what it seems to do is exclude rows which have SUM(A)
>> equal to zero. I.e. the HAVING is acting on the SUM(A).
>>
>>
>>>
>>> Jann
>>>
>>
>>
>> --
>> The unfacts, did we have them, are too imprecisely few to warrant our
>> certitude.
>>
>> Maranatha! <><
>> John McKown
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Illegal SQL not rejected

[Igor Korot]

Hi,

On Fri, Apr 29, 2016 at 1:28 PM, John McKown
 wrote:
> On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
> wrote:
>
>> Hi,
>> It seems like a too obvious omission to not be intentional. But I wonder
>> why a query like
>>
>> SELECT SUM(A)
>> FROM TABLE
>> GROUP BY B
>> HAVING A <> 0
>>
>> Is not rejected. MS SQL server gives you this error message in this case:
>>
>> Column ?A? is invalid in the HAVING clause because it is not contained in
>> either an aggregate function or the GROUP BY clause.
>>
>
> I agree that it just looks _wrong_. And PostgreSQL certainly complains
> about it.

This is weird because A is part of sum(A), which IS aggregate function call.
Or am I missing something?

Thank you.

>
>
>
>>
>> It's not even clear to me what SQLite does with a query like that.
>>
>
> I executed the above both with the HAVING clause and without it. I also
> did an EXPLAIN on both. Judging by the EXPLAIN output and the actual
> output, what it seems to do is exclude rows which have SUM(A) equal to
> zero. I.e. the HAVING is acting on the SUM(A).
>
>
>>
>> Jann
>>
>
>
> --
> The unfacts, did we have them, are too imprecisely few to warrant our
> certitude.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 1:23 PM, Igor Korot  wrote:

> Marc,
>
> On Fri, Apr 29, 2016 at 1:50 PM, Marc L. Allen
>  wrote:
> > That error is saying that you can't using HAVING on a column unless it's
> in a group by or it's referenced in an aggregate in the HAVING clause
> >
> > You could say, HAVING SUM(A) <> 0 or something.
> >
> > The query as stated
> >
> > SELECT SUM(A)
> > ...
> > HAVING A<>0
> >
> > makes no sense because A is not in the select list.  Only SUM(A) is.
>
> Does anybody in MS "speak any English?" (C) ;-)
>

?Yes, they speak the Microsoft revised standard English, which is
incompatibly extended and modified from U.S. English (as opposed to Her
Majesty's English?, and whatever it is that the Australians speak ('strine)
 [giggle]).



>
> Thank you.
>
> P.S.: This is a rhetorical question, BTW.
>
>

-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 12:41 PM, Igor Korot  wrote:

> Hi,
>
> On Fri, Apr 29, 2016 at 1:28 PM, John McKown
>  wrote:
> > On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
> > wrote:
> >
> >> Hi,
> >> It seems like a too obvious omission to not be intentional. But I wonder
> >> why a query like
> >>
> >> SELECT SUM(A)
> >> FROM TABLE
> >> GROUP BY B
> >> HAVING A <> 0
> >>
> >> Is not rejected. MS SQL server gives you this error message in this
> case:
> >>
> >> Column ?A? is invalid in the HAVING clause because it is not contained
> in
> >> either an aggregate function or the GROUP BY clause.
> >>
> >
> > I agree that it just looks _wrong_. And PostgreSQL certainly complains
> > about it.
>
> This is weird because A is part of sum(A), which IS aggregate function
> call.
> Or am I missing something?
>

?It is the syntax. PostgreSQL rejects

SELECT SUM(A) FROM TABLE GROUP BY B HAVING A<>0;

but  accepts

SELECT SUM(A) FROM TABLE GROUP BY B HAVING SUM(A) <>0;

?SQLite is processing the first one as if would the second, as best as I
can tell.

The PostgreSQL documentation states it as I am used to seeing:



HAVING eliminates group rows that do not satisfy the condition. HAVING is
different from WHERE: WHERE filters individual rows before the application
of GROUP BY, while HAVING filters group rows created by GROUP BY. Each
column referenced in conditionmust unambiguously reference a grouping
column, unless the reference appears within an aggregate function or the
ungrouped column is functionally dependent on the grouping columns.

?

The SQLite documentation states (point 3)



If a HAVING clause is specified, it is evaluated once for each group of
rows as a boolean expression .
If the result of evaluating the HAVING clause is false, the group is
discarded. If the HAVING clause is an aggregate expression, it is evaluated
across all rows in the group. If a HAVING clause is a non-aggregate
expression, it is evaluated with respect to an arbitrarily selected row
from the group. The HAVING expression may refer to values, even aggregate
functions, that are not in the result.



?So, it turns out that SQLite is documented as accepting the statement. So
this is not a "bug", per se. But basing a resultant row on "evaluated with
respect an arbitrarily selected row from the group" seems "un-useful".?




> Thank you.
>
>

-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Illegal SQL not rejected

[J Decker]

On Fri, Apr 29, 2016 at 10:00 AM, Jann Roder  
wrote:
> Hi,
> It seems like a too obvious omission to not be intentional. But I wonder why 
> a query like
>
> SELECT SUM(A)
> FROM TABLE
> GROUP BY B
> HAVING A <> 0
>
> Is not rejected. MS SQL server gives you this error message in this case:
>
> Column ?A? is invalid in the HAVING clause because it is not contained in 
> either an aggregate function or the GROUP BY clause.
>
> It's not even clear to me what SQLite does with a query like that.
>

OMG MSSQL should not be the standard for 'error'
There is certainly nothing wrong logically with that statement, nor abiguity.


Like select option_id from option_map join option_value on
option_map.option_id=option_value.option_id

ERROR: abiguous option_id  .  IF it chose either one aribrarily just
on a 'command not found, shall I fake it?' sort of methodology, it's
the same value.

or adding an order by requires me to specifuy each and every column
explcitly instead of allowing *'  (sorry this explation if vague, I
don't really understand why it's an error, I just give up and do it
the way MSSQL wants, MySQL would certainly have no issue with it, nor
I suspect does sqlite.



> Jann
>
>
>
>

[sqlite] Illegal SQL not rejected

[John McKown]

On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
wrote:

> Hi,
> It seems like a too obvious omission to not be intentional. But I wonder
> why a query like
>
> SELECT SUM(A)
> FROM TABLE
> GROUP BY B
> HAVING A <> 0
>
> Is not rejected. MS SQL server gives you this error message in this case:
>
> Column ?A? is invalid in the HAVING clause because it is not contained in
> either an aggregate function or the GROUP BY clause.
>

?I agree that it just looks _wrong_. And PostgreSQL certainly complains
about it.?



>
> It's not even clear to me what SQLite does with a query like that.
>

?I executed the above both with the HAVING clause and without it. I also
did an EXPLAIN on both. Judging by the EXPLAIN output and the actual
output, what it seems to do is exclude rows which have SUM(A) equal to
zero. I.e. the HAVING is acting on the SUM(A).


>
> Jann
>


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

[sqlite] Lemon reduce action merge bug

[Richard Hipp]

On 4/28/16, Kelvin Sherlock  wrote:
> I believe the lemon reduce action optimizer needs to compare the codePrefix
> and codeSuffix.

Thanks for the bug report.  A fix has now been checked in.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Working with blob

[Keith Medcalf]

> I don't know a way to write binary data to a file using the
> command-line tool.  Maybe there's one out there someone else knows of.

The SQL functions readfile and writefile mayhaps?

They have to be loaded as an extension (or compiled into) to lib/dll if you 
want them there.

[sqlite] Working with blob

[Simon Slavin]

On 29 Apr 2016, at 3:00am, James K. Lowden  wrote:

> I don't know a way to write binary data to a file using the
> command-line tool.  Maybe there's one out there someone else knows of.  

Nobody else knew of a way either, so one was specially introduced into a recent 
version.  On this page



look for 'blob'.

Simon.

[sqlite] Working with blob

[R Smith]

On 2016/04/28 8:27 PM, deltagamma1 at gmx.net wrote:
> What is a convenient way to store the path from a external blob (jpg,
> pdf) ?
> How can I retrieve this blob ?
> Is there a Frontend which opens the jpg directly from the listed
> table-content ?
>
> If I store the blob directly in the sqlite, is there a way to open the
> blob directly with the respective programm (e.g. irfanview or a pdf with
> acroread) ? I just discovered recently sqlitespeed, but do not know how
> to handle the problems described above.

A blob is just a set of bytes to the DB engine, it usually knows nothing 
about what those bytes represent. Some DB admin tools or GUIs may 
include ways to display the bytes (if they can detect that they are some 
standard format).  Some other programs, such as Irfanview, might happily 
display images from a DB, but it would likely need a very specific 
schema to go on.

Typically, you would be writing a program that uses the SQLite API to 
store blobs and retrieve them from some byte or stream you choose, into 
and from the formats you choose.

What I do is save both the blob and the file name (and perhaps the 
original path if you want to treat it as a backup) in separate columns 
(fields) and then, once I would like to open it, I just recreate the 
file (in whatever destination I would like, the TEMP folder is a good 
idea) with the correct file name, and then execute/open it - which 
should have your system open it with whatever program is registered to 
open such a file.

SQLite is not really concerned with what is in your blobs - just how to 
best store and retrieve them. Your program can do all kinds of magic 
with the blobs and SQLite will ensure you can save and load them fast 
and easy.

Best of luck,
Ryan

[sqlite] ANALYZE, sqlite_stat1, and query planning

[Rowan Worth]

Hi guys,

In an attempt to understand a slow query I've had a quick look at the
contents of the sqlite_stat1 table. It looks like the stat column contains
a series of integers like:




...

Is this observation correct? And if so, does sqlite essentially assume that
the rows are equally distributed amongst all distinct values of the column?

It seems like an assumption that will definitely fail for certain kinds of
data, and since sqlite generally deals well with contingencies I'd be
surprised if that's the only criteria used to select an index by the query
planner...

It looks like the ENABLE_STAT2/STAT4 options generate histogram data which
would probably help for non-uniform data?

Thanks,
-Rowan