[sqlite] SQLite parser library

[Kristoffer Danielsson]

Hi,

Some time ago I created a complete parser for SELECT statements in SQLite
(based on the grammar found in the source code). Sample code below!

Understanding how the Lemon parser works, creating the abstract syntax tree
(AST), and implementing the parser classes turned out to be extraordinary
hard - even though I do have experience with Bison and Flex.

This library is indeed a piece of art, and my company has little use of
it. I'm wondering if there is a commercial interest for this C++ library.
Please let me know.

** SAMPLE CODE **

select_parser parser = select_parser::create_parser(sql);

// Visitor pattern to allow arbitrary operations on the abstract syntax
tree (AST).
select_node_visitor snv;
parser.accept();
cout << "Result columns: " << snv.get_result_columns().size() << endl;
cout << "Group columns: " << snv.get_group_by_columns().size() << endl;

// Pretty-printed SQL suitable for editors. (I used it myself together with
Scintilla.)
// E.g.
// "select x,y,foo(x) as f  order by f"
// -->
// "SELECT x, y, foo(x) ORDER BY f"
//
sql_printer printer;
parser.accept();
cout << "Pretty-printed SQL: " << printer.get_sql() << endl;

// Possible to create arbitrary operations on SELECT statements.
my_select_injector injector;
parser.accept();

// E.g.
injector.toggle_sort_order(); // DESC <-> ASC
injector.make_distinct_result(); // SELECT DISTINCT ...
injector.add_row_limit(3); // ... LIMIT 3

// Or maybe just...
injector.perform_crazy_complex_sql_analysis();

Re: [sqlite] Are SQLITE_ENABLE_STAT3 tables created when upgrading?

[Kristoffer Danielsson]

Thanks. The documentation states that this new stat3 is better than stat2. Do 
you have any benchmarks or similar that show the win?
 > From: d...@sqlite.org
> Date: Tue, 29 Nov 2011 07:21:17 -0500
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Are SQLITE_ENABLE_STAT3 tables created when upgrading?
> 
> On Tue, Nov 29, 2011 at 7:00 AM, Kristoffer Danielsson <
> kristoffer.daniels...@live.se> wrote:
> 
> >
> >
> >
> >
> > Consider an SQLite database created with the SQLITE_ENABLE_STAT2 flag set.
> > After updating my software, the new SQLite version seems to use another
> > flag; SQLITE_ENABLE_STAT3. Will these stat3 optimizations be available when
> > opening and old database created with stat2? Or is it perhaps necessary to
> > run the ANALYZE command? Thanks.
> >
> 
> It is necessary to rerun ANALYZE.
> 
> 
> > ___
> > 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
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Are SQLITE_ENABLE_STAT3 tables created when upgrading?

[Kristoffer Danielsson]

Consider an SQLite database created with the SQLITE_ENABLE_STAT2 flag set. 
After updating my software, the new SQLite version seems to use another flag; 
SQLITE_ENABLE_STAT3. Will these stat3 optimizations be available when opening 
and old database created with stat2? Or is it perhaps necessary to run the 
ANALYZE command? Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANALYZE necessary after database upgrade?

[Kristoffer Danielsson]

I did actually report this before, but nobody 
answered:http://www.mailinglistarchive.com/html/sqlite-users@sqlite.org/2011-04/msg00315.html
 As you can see, the query does not contain a LIMIT clause. > From: 
d...@sqlite.org
> Date: Thu, 18 Aug 2011 09:00:23 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANALYZE necessary after database upgrade?
> 
> On Thu, Aug 18, 2011 at 8:50 AM, Kristoffer Danielsson <
> kristoffer.daniels...@live.se> wrote:
> 
> >
> >
> >
> >
> > I have noticed that certain (complex) select queries return unexpected data
> > (missing rows) on my upgraded SQLite databases.My guess is that the
> > optimizer makes an erroneous decision on some index (I'm using both
> > sqlite_stat1 and sqlite_stat2). Is this a reasonable guess? Is it necessary
> > to run the ANALYZE command after upgrading (altering table columns etc) a
> > database? Thanks!
> >
> 
> SQLite should *never* return an incorrect answer because of a failure to
> ANALYZE.  All ANALYZE should do is make the answer come back faster.
> 
> Note that some non-deterministic queries (such as using a LIMIT without an
> ORDER BY) might return different results after ANALYZE because it chooses a
> different query plan.  But in cases like this, that is not an error - the
> use of LIMIT without an ORDER BY gives an undefined result.
> 
> If you find a case where SQLite is giving an incorrect result, please send
> us details so that we can track down and fix the problem.
> 
> 
> 
> > ___
> > 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
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANALYZE necessary after database upgrade?

[Kristoffer Danielsson]

Hi, The problem is that the database is around 100 MB large (the error goes 
away if I remove unimportant data). Also, it contains data I'd like to keep 
private. I do have a specific select query that produces what I believe is a 
data error (tested in the latest SQLite version). Can I zip the database and 
send it to the sqlite team for analysis? > From: slav...@bigfraud.org
> Date: Thu, 18 Aug 2011 13:57:31 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANALYZE necessary after database upgrade?
> 
> 
> On 18 Aug 2011, at 1:50pm, Kristoffer Danielsson wrote:
> 
> > I have noticed that certain (complex) select queries return unexpected data 
> > (missing rows) on my upgraded SQLite databases.
> 
> What do you mean by 'upgraded' ?
> 
> > My guess is that the optimizer makes an erroneous decision on some index 
> > (I'm using both sqlite_stat1 and sqlite_stat2). Is this a reasonable guess?
> 
> Depends what you mean by 'missing rows'.  If you think SQLite is giving you 
> /wrong/ information, please describe it in more detail, showing both the 
> output of the query you think is wrong, and some other output from the 
> database showing why you think it's wrong.
> 
> > Is it necessary to run the ANALYZE command after upgrading (altering table 
> > columns etc) a database? Thanks! 
> 
> ANALYZE just helps SQLite decide what the fastest way to do something is.  It 
> should never change which rows are changed or returned.  So it will 
> definitely not fix a data problem.
> 
> 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

[sqlite] ANALYZE necessary after database upgrade?

[Kristoffer Danielsson]

I have noticed that certain (complex) select queries return unexpected data 
(missing rows) on my upgraded SQLite databases.My guess is that the optimizer 
makes an erroneous decision on some index (I'm using both sqlite_stat1 and 
sqlite_stat2). Is this a reasonable guess? Is it necessary to run the ANALYZE 
command after upgrading (altering table columns etc) a database? Thanks!
   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 44, Issue 4

[Kristoffer Danielsson]

Another solution is to transfer ownership of the handle to the new object. 
E.g.: // Note that this requires the m_sqlite variable to be mutuable (for 
modification of const-object).DatabaseRecord::DatabaseRecord(const 
DatabaseRecord ){ m_sqlite = rhs.m_sqlite; ...  
rhs.m_sqlite = 0;}
> Date: Fri, 5 Aug 2011 20:08:16 +0800
> From: n1kk1...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 44, Issue 4
> 
> >
> > --
> >
> > Message: 4
> > Date: Wed, 3 Aug 2011 16:25:13 +0200
> > From: Stephan Beal 
> > Subject: Re: [sqlite] Deep copy of 2 sqlite3*?
> > To: General Discussion of SQLite Database 
> > Message-ID:
> >

[sqlite] Possible NATURAL JOIN bug

[Kristoffer Danielsson]

Sqlite 3.7.5. Possible NATURAL JOIN bug.
 
I have a ~100 MB database which gives me odd results when running two similar 
queries. I'm currently trying to create a minimal test case, but the error 
seems to go away when I try to narrow it down.
 
In short, the queries look like this (scrambled :P)...
SELECT N FROM (Y JOIN S USING (S_ID) JOIN B USING (A_ID)) NATURAL JOIN ((SELECT 
A_ID, N FROM C NATURAL JOIN A) NATURAL JOIN A) GROUP BY N ORDER BY N;
3 rows returned
 
SELECT N FROM (Y JOIN S USING (S_ID) JOIN B USING (A_ID)) NATURAL JOIN ((SELECT 
A_ID, N FROM C NATURAL JOIN A)) GROUP BY N ORDER BY N;
4 rows returned
 
Obviously, the extra "natural join" eliminates one row somehow. I'm quite 
convinced I'm facing a rare bug here.
 
Where can I send my 100 MB database for assistance? (In case I don't manage to 
narrow it down.)
 
Thanks!   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Nice :)
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Sat, 25 Sep 2010 10:21:18 -0400
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@live.se> wrote:
> >> From: oliver@web.de
> >> a little mistake - here's the correction:
> >> 
> >> SELECT DISTINCT t.Year, b.Name,
> >> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> >> b.Name) AS SomeValue
> >> FROM Test t
> >> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> >> ;
> >
> > Ah, this one's easier to follow.
> 
> See how you like this one:
> 
> select *
> from (select distinct year from Test) as AllYears
> join (select distinct name from Test) as AllNames
> left join Test t on (t.year=AllYears.Year and t.name=AllNames.name)
> left join Test2 t2 on (t.TestId = t2.TestId);
> 
> -- 
> Igor Tandetnik
> 
> 
> ___
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Well, let me explain :)

 

My problem originates from a chart generator which needs all data to be 
explicitly set - including "none" values. Think of this simple example:

2001|B|123

2002|C|234

 

How would you GROUP and COMPARE A and B in a bar chart? Two columns in each 
category...

2001: B=123, C=0

2002: B=0, C=234

 

Either you make the SQL query return those empty parts or you perform these 
extra check afterwards. You effectively helped me with the first approach :)

 

 

Regarding NULL values I tend to avoid them at all costs. NULL values make 
NATURAL JOINs "fail", which increases the chances of doing mistakes.


 
> Date: Sat, 25 Sep 2010 02:47:05 +0200
> From: oliver@web.de
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:
> 
> [...]
> 
> >
> > Because, given a certain algorithm, generating statistics will become a lot 
> > easier if each value combination is represented in the returned row set.
> >
> 
> really? NULL means there are no values present or there are unknown 
> values - statistics with NULL should be without consequences
> 
> if you think you should calculate instead of NULL with 0 (the number) it 
> still isn't correct (try it with an average, ie sales figures: you 
> assume that NULL = 0 but that assumption is a mistake)
> 
> 
> [...]
> 
> Oliver
> ___
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Yeah. USING is good to have :)

 

And yes, 123 might very well be a random number. I should have made that clear!
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:19:44 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@...> writes:
> 
> > 
> > 
> > Ah, this one's easier to follow. What do you mean by "in this case"? What 
> > was
> the condition that made it
> 
> the point was not to remove something but to take into consideration that 
> there
> has to be a JOIN on the TestIDs (in this case results doesn't differ because 
> you
> only have 123 as SomeValue) - and because it's too late/early here is my 
> assumed
> last correction (see the USING I forgot, if you forget this you will get a
> Cartesian Product)
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 USING(TestID) WHERE Year = t.Year
> AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> 
> 
> > possible to remove the rest?
> > 
> > Again, thanks!
> > 
> > 
> > > To: sqlite-us...@...
> > > From: oliver@...
> > > Date: Sat, 25 Sep 2010 00:05:11 +
> > > Subject: Re: [sqlite] Need help with self-join (I think)
> > > 
> > > sry,
> > > 
> > > a little mistake - here's the correction:
> > > 
> > > SELECT DISTINCT t.Year, b.Name,
> > > (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name 
> > > =
> > > b.Name) AS SomeValue
> > > FROM Test t
> > > CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> > > ;
> > > 
> > > 
> > > (results don't differ - in this case!)
> > > 
> > > Oliver
> > > 
> > > ___
> > > sqlite-users mailing list
> > > sqlite-us...@...
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > ___
> > sqlite-users mailing list
> > sqlite-us...@...
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with self-join (I think)

[Kristoffer Danielsson]

Ah, this one's easier to follow. What do you mean by "in this case"? What was 
the condition that made it possible to remove the rest?


Again, thanks!

 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Sat, 25 Sep 2010 00:05:11 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> sry,
> 
> a little mistake - here's the correction:
> 
> SELECT DISTINCT t.Year, b.Name,
> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name =
> b.Name) AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> 
> (results don't differ - in this case!)
> 
> Oliver
> 
> ___
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Neat! Many thanks :)

 

Putting this logic together with my original query will be an interesting 
challenge, hehe. Do you believe this is the best solution?

 

Chris
 
> To: sqlite-users@sqlite.org
> From: oliver@web.de
> Date: Fri, 24 Sep 2010 23:47:59 +
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> Kristoffer Danielsson <kristoffer.daniels...@...> writes:
> 
> SELECT DISTINCT t.Year, b.Name,
> CASE
> WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
> THEN NULL
> ELSE (SELECT SomeValue FROM Test2)
> END AS SomeValue
> FROM Test t
> CROSS JOIN (SELECT DISTINCT Name FROM Test) b
> ;
> 
> Oliver
> 
> ___
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

Thanks for your reply. You're right, it's an outer-join I'm looking for. 
Unfortunately, your suggestion does not do the trick.

 

"You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?"

Because, given a certain algorithm, generating statistics will become a lot 
easier if each value combination is represented in the returned row set.

 

Perhaps a UNION is needed for this type of query?

 

> Date: Fri, 24 Sep 2010 18:17:51 -0500
> From: nicolas.willi...@oracle.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Need help with self-join (I think)
> 
> On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
> > CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT 
> > NOT NULL);
> > INSERT INTO Test (Year, Name) VALUES (2007, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2007, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2008, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'A');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'B');
> > INSERT INTO Test (Year, Name) VALUES (2009, 'C');
> > CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
> > INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);
> > 
> > [...]
> > My problem is that I want each combination of Year+Name to be represented - 
> > with zero/NULL for SomeValue. How do I accomplish this?
> > 
> > 2007|A|123
> > 2007|B|123
> > 2007|C|NULL
> 
> You didn't have a Test1 row for (2007, 'C'), so why would you get
> 2007|C|NULL? You also had one (and just one) row in Test2 for every
> TestID in Test, so there are no NULLs that could appear as you request.
> 
> But, if you did:
> 
> INSERT INTO Test (Year, Name) VALUES (2007, 'C');
> 
> without a corresponding row in Test2, then your SELECT would not return
> 2007|C|NULL. Try this:
> 
> SELECT Year, Name, SomeValue
> FROM Test LEFT OUTER JOIN Test2 ON (Test.TestID = Test2.TestID)
> ORDER BY Year, Name;
> 
> (Self-join is when both sides of the JOIN use the same table. That's
> not the case here. What you were looking for here is an OUTER JOIN
> instead of an INNER JOIN; NATURAL JOINs are a type of INNER JOIN.)
> 
> Nico
> -- 
> ___
> 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] Need help with self-join (I think)

[Kristoffer Danielsson]

Hi,

 

I have an interesting SQL problem where I want certain rows to always be 
represented. It feels like a self-join, but I'm not sure. Please help!

 

Create a database as follows:

 



CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT NOT 
NULL);

INSERT INTO Test (Year, Name) VALUES (2007, 'A');
INSERT INTO Test (Year, Name) VALUES (2007, 'B');
INSERT INTO Test (Year, Name) VALUES (2008, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'A');
INSERT INTO Test (Year, Name) VALUES (2009, 'B');
INSERT INTO Test (Year, Name) VALUES (2009, 'C');

 

CREATE TABLE Test2 (TestID INTEGER NOT NULL UNIQUE, SomeValue INT NOT NULL);

INSERT INTO Test2 (TestID, SomeValue) VALUES (1, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (2, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (3, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (4, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (5, 123);
INSERT INTO Test2 (TestID, SomeValue) VALUES (6, 123);



 

SELECT Year, Name, SomeValue FROM Test NATURAL JOIN Test2 ORDER BY Year, Name;

 

2007|A|123
2007|B|123
2008|B|123
2009|A|123
2009|B|123
2009|C|123

 

My problem is that I want each combination of Year+Name to be represented - 
with zero/NULL for SomeValue. How do I accomplish this?

 

2007|A|123
2007|B|123
2007|C|NULL
2008|A|NULL
2008|B|123
2008|C|NULL
2009|A|123
2009|B|123
2009|C|123

 

If there is an "easy" solution it would be great, as the original query is 
quite complex... :P

 

Thank you for your help!
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which data type is better for date?

[Kristoffer Danielsson]

Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time 
of the day, which may prove useful later on.

 

#2 is non-standard. No time value.

#3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option 
though.

#4 gives room for mistakes; the year may be interpreted as the day.

 
> Date: Sat, 4 Sep 2010 20:31:00 +0900
> From: mikez...@yahoo.co.jp
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Which data type is better for date?
> 
> I try to convert data to SQLite3 for iPad, please give me some detail
> suggestion.
> 
> I think that I can save date value as below to SQLite3, I want to know
> which is better, or anything else if you have good idea.
> 
> 1. integer as seconds since 1970
> 2. integer as days since 1970
> 3. string as '2010-09-03'
> 4. string as '10-09-03'
> ___
> 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] EXTERNAL: Terminate blocking SELECT-operation?

[Kristoffer Danielsson]

Thanks. That's exactly what I need.
 
> Date: Mon, 12 Jul 2010 06:57:13 -0500
> From: michael.bla...@ngc.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] EXTERNAL: Terminate blocking SELECT-operation?
> 
> Do you maybe want a combination of
> 
> http://www.sqlite.org/c3ref/progress_handler.html
> And
> http://www.sqlite.org/c3ref/interrupt.html
> 
> If you really need asynch queries then I think you'll have to thread it or 
> fork it yourself.
> 
> Michael D. Black
> Senior Scientist
> Northrop Grumman Mission Systems
> 
> 
> ____
> 
> From: sqlite-users-boun...@sqlite.org on behalf of Kristoffer Danielsson
> Sent: Mon 7/12/2010 6:39 AM
> To: sqlite-users@sqlite.org
> Subject: EXTERNAL:[sqlite] Terminate blocking SELECT-operation?
> 
> 
> 
> 
> I would like to perform an asynchronous SELECT, terminatable by a semaphore 
> or equivalent. The problem I'm trying to solve is to allow termination of a 
> blocking SELECT-operation that take too long.
> 
> 
> 
> Does SQLite support that?
> 
> _
> Håll skräpposten borta med nya Hotmail. Klicka här!
> http://explore.live.com/windows-live-hotmail
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
  
_
Messenger i mobilen på 5 sekunder!
http://new.windowslivemobile.msn.com/se-SE/Default.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Terminate blocking SELECT-operation?

[Kristoffer Danielsson]

I would like to perform an asynchronous SELECT, terminatable by a semaphore or 
equivalent. The problem I'm trying to solve is to allow termination of a 
blocking SELECT-operation that take too long.

 

Does SQLite support that?
  
_
Håll skräpposten borta med nya Hotmail. Klicka här!
http://explore.live.com/windows-live-hotmail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to determine when to VACUUM?

[Kristoffer Danielsson]

What you are saying makes sense. Thanks for your advice!

 

However, I do believe there are times when vacuuming would be beneficial. For 
instance, if a database for software X is detected to have 90% unused space for 
a couple of weeks, then why bloat the harddrive? (I don't know how to do that 
though :P)

 

In my opinion, the user should always have the option to vacuum the database. 
My goal is to let software X have some logic to give the user a hint when this 
action would be appropriate.


 
> From: slav...@bigfraud.org
> Date: Tue, 6 Jul 2010 22:52:11 +0100
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to determine when to VACUUM?
> 
> 
> On 6 Jul 2010, at 10:45pm, Kristoffer Danielsson wrote:
> 
> > Q: How do I programmatically (through sqlite-APIs?) determine if it's time 
> > to VACUUM a database?
> 
> It's never time to VACUUM a database. The VACUUM command is useful only if 
> you have want to recover unused space from the database file. So if your 
> database file once took up 5 Meg, and you deleted a lot of data from it and 
> it now takes up only 2 Meg, you could recover 3 Megabytes of disk space. But 
> how useful is that 3 Megabytes of space to you ? Are you going to use it for 
> something really valuable ? And how long will it be before you get 3 
> Megabytes more data which will fill it up again ?
> 
> If you're trying to get the database in shape to make copies, e.g. to burn it 
> on a DVD or send it to customers, or put it on a device with limited space, 
> then there might be some reason to use VACUUM. If not, then it's just a waste 
> of resources.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Messenger i mobilen på 5 sekunder!
http://new.windowslivemobile.msn.com/se-SE/Default.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to determine when to VACUUM?

[Kristoffer Danielsson]

I've been reading the documentation. I've been googling and thinking.

 

Still, I can't figure out the best way to determine when to run the 
VACUUM-command. Note that I do NOT want to enable "auto vacuum".

I do remember reading something about calculating empty space, used pages etc 
etc. Still, no perfect answer.

 

Q: How do I programmatically (through sqlite-APIs?) determine if it's time to 
VACUUM a database? In general, what is the best method here?

 

Thanks!

/Chris
  
_
Håll skräpposten borta med nya Hotmail. Klicka här!
http://explore.live.com/windows-live-hotmail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] optimization question

[Kristoffer Danielsson]

I solved my "inner/outer" problems by compiling Sqlite with 
SQLITE_ENABLE_STAT2=1.

That flag makes it better at choosing the inner table!
 
> To: sqlite-users@sqlite.org
> From: itandet...@mvps.org
> Date: Wed, 11 Nov 2009 12:03:06 -0500
> Subject: Re: [sqlite] optimization question
> 
> Tim Romano  wrote:
> > I've read http://www.sqlite.org/optoverview.html but don't find my
> > answer there.
> > 
> > In the following query, WOIDS has 4 million rows and CORNFIX has
> > 25,000 rows.
> > 
> > UPDATE WOIDS
> > SET corn = 1
> > WHERE EXISTS
> > (
> > SELECT *
> > FROM CORNFIX
> > WHERE (cornfix.col_1 = woids.ttl) AND (cornfix.col_2 =
> > woids.pos) AND (cornfix.col_3 = woids.wrdid)
> > )
> 
> Try this instead:
> 
> update WOIDS set corn=1 where rowid in
> (select w2.rowid
> from cornfix join woids w2 on (
> cornfix.col_1 = w2.ttl AND cornfix.col_2 = w2.pos AND cornfix.col_3 = 
> w2.wrdid)
> );
> 
> I'm not sure, but this structure might help SQLite choose cornfix for the 
> outer loop.
> 
> Igor Tandetnik
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding "Ambigious column"?

[Kristoffer Danielsson]

Ok, so parentheses "hide" the columns in the present clause? There must be a 
common rule for this...

 

And yes, I know you can explicitly use "table.column", but in this case the 
code comes from the user and conflicts with my own columns.

Hence, I have to think of a way to avoid column conflicts.

 

Thanks for your reply.
 
> From: slav...@bigfraud.org
> Date: Sat, 7 Nov 2009 23:55:52 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Avoiding "Ambigious column"?
> 
> 
> On 7 Nov 2009, at 10:56pm, Kristoffer Danielsson wrote:
> 
> > SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4) 
> > NATURAL JOIN (T2 NATURAL JOIN T3);
> >
> > Error: Ambigious column name: Salary
> >
> > However, if I add two parentheses around "T2 NATURAL JOIN T4", the 
> > error goes away:
> >
> > SELECT SUM(Salary - TotalSpent) / 100 FROM ((T2 NATURAL JOIN T4)) 
> > NATURAL JOIN (T2 NATURAL JOIN T3);
> >
> > Why is this so?
> 
> Because there's no longer any reason to wonder if the Salary column 
> could come from T2 NATURAL JOIN T4: you haven't referred to either of 
> them directly.
> 
> Another way to get rid of the error message would be to specify 
> T2.Salary or T3.Salary.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Avoiding "Ambigious column"?

[Kristoffer Danielsson]

SQLite 3.6.20.

 

SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4) NATURAL JOIN 
(T2 NATURAL JOIN T3);

 

Error: Ambigious column name: Salary

 

However, if I add two parentheses around "T2 NATURAL JOIN T4", the error goes 
away:

 

SELECT SUM(Salary - TotalSpent) / 100 FROM ((T2 NATURAL JOIN T4)) NATURAL JOIN 
(T2 NATURAL JOIN T3);

 

 

Why is this so?
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.6.20 NATURAL self-join still not fixed

[Kristoffer Danielsson]

Sorry :P

 

Seems my SQLite.exe was still 3.6.19. Downloaded the latest exe and now it 
works \o/

 

Thanks.
 
> From: d...@hwaci.com
> To: sqlite-users@sqlite.org
> Date: Sat, 7 Nov 2009 11:05:39 -0500
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
> 
> 
> On Nov 7, 2009, at 10:59 AM, Kristoffer Danielsson wrote:
> >
> > Present in both 3.6.19 and 3.6.20 (where it was fixed, according to 
> > the ticket).
> 
> 
> In whatever environment you are running your experiment, please also 
> run the following queries and let us know the result:
> 
> SELECT sqlite_version();
> SELECT sqlite_source_id();
> 
> Thanks!
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Lagra alla dina foton på Skydrive. Det är enkelt och säkert!
http://www.skydrive.live.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.6.20 NATURAL self-join still not fixed

[Kristoffer Danielsson]

Like I said, I'm running 3.6.20 now. Same problem.


 
> From: d...@hwaci.com
> To: sqlite-users@sqlite.org
> Date: Sat, 7 Nov 2009 11:04:16 -0500
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
> 
> 
> On Nov 7, 2009, at 10:54 AM, Simon Davies wrote:
> >
> > I used 10,000 entries, and it returned in a few seconds... (using 
> > 3.6.19)
> >
> > But there does appear to be a problem:
> > sqlite> select count(*) from test1 natural join test2;
> > 10
> > sqlite> select count(*) from test1 natural join test1;
> > 100
> 
> 
> Try that again using 3.6.20.
> 
> That problem was fixed by http://www.sqlite.org/src/vinfo/ 
> 6fe63711754on 2009-10-19.
> 
> D. Richard Hipp
> d...@hwaci.com
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.6.20 NATURAL self-join still not fixed

[Kristoffer Danielsson]

Exactly my point. A few seconds is a LOT more than 0 seconds (I cancelled the 
query after a few seconds since it was obvious it did not do what it was 
supposed to).


Just like in my first report, adding parentheses around the table name resolves 
this:

sqlite> select count(*) from (test1) natural join (test1);
10

 

Present in both 3.6.19 and 3.6.20 (where it was fixed, according to the ticket).

 

Thanks.

 
> Date: Sat, 7 Nov 2009 15:54:42 +
> From: simon.james.dav...@googlemail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
> 
> 2009/11/7 Kristoffer Danielsson <kristoffer.daniels...@live.se>:
> >
> > Using SQLite 3.6.20 (SQLITE_ENABLE_STAT2=1).
> >
> > PRAGMA foreign_keys=OFF;
> >
> > BEGIN TRANSACTION;
> >
> > CREATE TABLE Test
> > (
> >  TestID INTEGER PRIMARY KEY,
> >  T1 INTEGER NOT NULL,
> >  T2 INTEGER NOT NULL,
> >  T3 INTEGER NOT NULL,
> >  T4 INTEGER NOT NULL,
> >  T5 INTEGER NOT NULL,
> >  T6 INTEGER NOT NULL,
> >  T7 INTEGER NOT NULL,
> >  DT DATE NOT NULL,
> >  T8 INTEGER NOT NULL,
> >  T9 INTEGER NOT NULL,
> >  T10 INTEGER NOT NULL,
> >  T11 INTEGER NOT NULL,
> >
> >  UNIQUE (T2, T1)
> > );
> >
> > -- Fill with random data!
> >
> > COMMIT TRANSACTION;
> >
> >
> > SELECT COUNT(*) FROM Test; -- Blistering fast!
> >
> > SELECT COUNT(*) FROM Test NATURAL JOIN Test; -- "Never" terminates
> >
> > I have over 50,000 entries...
> 
> I used 10,000 entries, and it returned in a few seconds... (using 3.6.19)
> 
> But there does appear to be a problem:
> 
> sqlite> CREATE TABLE Test1
> ...> (
> ...> T1 INTEGER NOT NULL,
> ...> T2 INTEGER NOT NULL
> ...> );
> sqlite>
> sqlite> CREATE TABLE Test2
> ...> (
> ...> T1 INTEGER NOT NULL,
> ...> T2 INTEGER NOT NULL
> ...> );
> sqlite>
> sqlite> insert into test1 values( 1, 1 );
> sqlite> insert into test1 values( 2, 2 );
> sqlite> insert into test1 values( 3, 3 );
> sqlite> insert into test1 values( 4, 4 );
> sqlite> insert into test1 values( 5, 5 );
> sqlite> insert into test1 values( 6, 6 );
> sqlite> insert into test1 values( 7, 7 );
> sqlite> insert into test1 values( 8, 8 );
> sqlite> insert into test1 values( 9, 9 );
> sqlite> insert into test1 values( 10, 10 );
> sqlite>
> sqlite> insert into test2 values( 1, 1 );
> sqlite> insert into test2 values( 2, 2 );
> sqlite> insert into test2 values( 3, 3 );
> sqlite> insert into test2 values( 4, 4 );
> sqlite> insert into test2 values( 5, 5 );
> sqlite> insert into test2 values( 6, 6 );
> sqlite> insert into test2 values( 7, 7 );
> sqlite> insert into test2 values( 8, 8 );
> sqlite> insert into test2 values( 9, 9 );
> sqlite> insert into test2 values( 10, 10 );
> sqlite>
> sqlite>
> sqlite> select count(*) from test1 natural join test2;
> 10
> sqlite> select count(*) from test1 natural join test1;
> 100
> sqlite> select count(*) from test1 as t1 natural join test1;
> 10
> sqlite>
> 
> >
> > Thanks.
> >
> >> From: danielk1...@gmail.com
> >> To: sqlite-users@sqlite.org
> >> Date: Sat, 7 Nov 2009 11:36:52 +0700
> >> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
> >>
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.6.20 NATURAL self-join still not fixed

[Kristoffer Danielsson]

Using SQLite 3.6.20 (SQLITE_ENABLE_STAT2=1).

 

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE Test
(
 TestID INTEGER PRIMARY KEY,
 T1 INTEGER NOT NULL,
 T2 INTEGER NOT NULL,
 T3 INTEGER NOT NULL,
 T4 INTEGER NOT NULL,
 T5 INTEGER NOT NULL,
 T6 INTEGER NOT NULL,
 T7 INTEGER NOT NULL,
 DT DATE NOT NULL,
 T8 INTEGER NOT NULL,
 T9 INTEGER NOT NULL,
 T10 INTEGER NOT NULL,
 T11 INTEGER NOT NULL,
 
 UNIQUE (T2, T1)
);

 

-- Fill with random data!

 

COMMIT TRANSACTION;

 

 

SELECT COUNT(*) FROM Test; -- Blistering fast!

SELECT COUNT(*) FROM Test NATURAL JOIN Test; -- "Never" terminates

 

I have over 50,000 entries...

 

Thanks.
 
> From: danielk1...@gmail.com
> To: sqlite-users@sqlite.org
> Date: Sat, 7 Nov 2009 11:36:52 +0700
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
> 
> 
> On Nov 7, 2009, at 5:25 AM, Kristoffer Danielsson wrote:
> 
> >
> > http://www.sqlite.org/src/info/b73fb0bd64
> >
> >
> >
> > Just tried this statement in SQLite 3.6.20:
> >
> > SELECT COUNT(*) FROM X NATURAL JOIN X; <--- "never" terminates
> 
> What is the schema and contents of table X that causes this?
> 
> Dan.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

Yes!!

SQLITE_ENABLE_STAT2 did the trick. I can see that the SQLite library increased 
some 10 kB after this change. No big deal.

 

Why isn't this flag on by default?


 
> From: kristoffer.daniels...@live.se
> To: sqlite-users@sqlite.org
> Date: Sun, 1 Nov 2009 17:04:41 +0100
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> 
> The parentheses around A is a workaround to avoid the "natural self-join" bug 
> (fixed in 3.6.20+ as far as I know). In this particular case they can be 
> omitted, with no difference. The parenthesis around B+C+D are there to get a 
> valid inner join - B and D do not contain A_ID, but C does.
> 
> 
> 
> These are ultra-fast:
> 
> SELECT COUNT(A_ID) FROM A INNER JOIN (B NATURAL JOIN C NATURAL JOIN D) USING 
> (A_ID);
> 
> SELECT COUNT(A_ID) FROM B NATURAL JOIN C NATURAL JOIN D NATURAL JOIN A;
> 
> 
> 
> These are ultra-slow:
> 
> SELECT COUNT(A_ID) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
> 
> SELECT COUNT(A.A_ID) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A;
> 
> 
> 
> I'm wondering if the optimizer makes a stupid choice due to the 
> SQLITE_ENABLE_STAT2 flag, which was not set during compilation. I'll try the 
> ANALYZE command after recompiling with this flag activated, and see if it 
> makes any difference.
> 
> 
> 
> And if it doesn't, I'll make sure to produce a sample that illustrates the 
> problem.
> 
> 
> 
> Thanks.
> 
> 
> 
> > From: paiva...@gmail.com
> > Date: Sun, 1 Nov 2009 10:33:58 -0400
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] How to decide which table is the outer table and 
> > which table is the inner table?
> > 
> > > (B + C + D) have two columns that are present in A, of which only one is 
> > > indexed. Could that be the culprit?
> > 
> > There's an easy way to check that: just re-write your query so that it
> > contains joins with explicitly pointed condition which columns join
> > should be made on.
> > 
> > BTW, why are you putting parenthesis there? Maybe it makes less
> > freedom to choose for SQLite's optimizer?
> > 
> > Pavel
> > 
> > On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
> > <kristoffer.daniels...@live.se> wrote:
> > >
> > > All I know is that SQLite chooses the wrong order when I give it my query 
> > > at this form:
> > >
> > >
> > >
> > > SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN 
> > > D);
> > >
> > >
> > >
> > > Where:
> > >
> > > A = 50,000 rows.
> > >
> > > B = 2 rows.
> > >
> > > C = 10,000 rows
> > >
> > > D = 250,000 rows
> > >
> > >
> > >
> > >
> > >
> > > (B + C + D) have two columns that are present in A, of which only one is 
> > > indexed. Could that be the culprit?
> > >
> > >
> > >
> > > Though, if I put A last, the query is blistering fast. Clearly, SQLite 
> > > does something wrong in this case.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >> From: paiva...@gmail.com
> > >> Date: Fri, 30 Oct 2009 12:16:17 -0400
> > >> To: sqlite-users@sqlite.org
> > >> Subject: Re: [sqlite] How to decide which table is the outer table and 
> > >> which table is the inner table?
> > >>
> > >> Of course SQLite wasn't changed much in this part since November 2008
> > >> but the citation you gave is either wrong or the key words in it are
> > >> "something like" in phrase "SQlite does something like this". Because
> > >> SQLite is smart enough to choose smaller table as an outer one and
> > >> bigger table as an inner one. Although it can choose other way round
> > >> if you have index on Id in smaller table and don't have index on Id in
> > >> bigger table. And in this case there's no performance hit in such
> > >> decision, only benefit.
> > >>
> > >> Pavel
> > >>
> > >> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> > >> <kristoffer.daniels...@live.se> wrote:
> > >> >
> > >> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> > >> >
> > >> >
> > >> >
> > >> > SQLite uses only nested loops to implement join

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

The parentheses around A is a workaround to avoid the "natural self-join" bug 
(fixed in 3.6.20+ as far as I know). In this particular case they can be 
omitted, with no difference. The parenthesis around B+C+D are there to get a 
valid inner join - B and D do not contain A_ID, but C does.

 

These are ultra-fast:

SELECT COUNT(A_ID) FROM A INNER JOIN (B NATURAL JOIN C NATURAL JOIN D) USING 
(A_ID);

SELECT COUNT(A_ID) FROM B NATURAL JOIN C NATURAL JOIN D NATURAL JOIN A;

 

These are ultra-slow:

SELECT COUNT(A_ID) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

SELECT COUNT(A.A_ID) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A;

 

I'm wondering if the optimizer makes a stupid choice due to the 
SQLITE_ENABLE_STAT2 flag, which was not set during compilation. I'll try the 
ANALYZE command after recompiling with this flag activated, and see if it makes 
any difference.

 

And if it doesn't, I'll make sure to produce a sample that illustrates the 
problem.

 

Thanks.


 
> From: paiva...@gmail.com
> Date: Sun, 1 Nov 2009 10:33:58 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> > (B + C + D) have two columns that are present in A, of which only one is 
> > indexed. Could that be the culprit?
> 
> There's an easy way to check that: just re-write your query so that it
> contains joins with explicitly pointed condition which columns join
> should be made on.
> 
> BTW, why are you putting parenthesis there? Maybe it makes less
> freedom to choose for SQLite's optimizer?
> 
> Pavel
> 
> On Sat, Oct 31, 2009 at 7:27 PM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > All I know is that SQLite chooses the wrong order when I give it my query 
> > at this form:
> >
> >
> >
> > SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);
> >
> >
> >
> > Where:
> >
> > A = 50,000 rows.
> >
> > B = 2 rows.
> >
> > C = 10,000 rows
> >
> > D = 250,000 rows
> >
> >
> >
> >
> >
> > (B + C + D) have two columns that are present in A, of which only one is 
> > indexed. Could that be the culprit?
> >
> >
> >
> > Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
> > something wrong in this case.
> >
> >
> >
> >
> >
> >
> >
> >> From: paiva...@gmail.com
> >> Date: Fri, 30 Oct 2009 12:16:17 -0400
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] How to decide which table is the outer table and 
> >> which table is the inner table?
> >>
> >> Of course SQLite wasn't changed much in this part since November 2008
> >> but the citation you gave is either wrong or the key words in it are
> >> "something like" in phrase "SQlite does something like this". Because
> >> SQLite is smart enough to choose smaller table as an outer one and
> >> bigger table as an inner one. Although it can choose other way round
> >> if you have index on Id in smaller table and don't have index on Id in
> >> bigger table. And in this case there's no performance hit in such
> >> decision, only benefit.
> >>
> >> Pavel
> >>
> >> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> >> <kristoffer.daniels...@live.se> wrote:
> >> >
> >> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> >> >
> >> >
> >> >
> >> > SQLite uses only nested loops to implement joins. Given a query like the 
> >> > following:
> >> >
> >> > SELECT ...
> >> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> >> >
> >> >
> >> >
> >> > SQlite does something like this:
> >> >
> >> >
> >> >
> >> > for each row in OuterTable
> >> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> >> > end for each
> >> >
> >> >
> >> >
> >> > I assume SQLite has not improved on JOIN precedence since then, which 
> >> > means that if OuterTable is HUGE, there will be an huge performance hit!
> >> >
> >> >
> >> >
> >> > "Some database engines like SQL Server decide which table is the outer 
> >> > table and which table is the inner table"
> >> >
>

Re: [sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

All I know is that SQLite chooses the wrong order when I give it my query at 
this form:

 

SELECT COUNT(A_ID) FROM (A) NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

 

Where:

A = 50,000 rows.

B = 2 rows.

C = 10,000 rows

D = 250,000 rows

 

 

(B + C + D) have two columns that are present in A, of which only one is 
indexed. Could that be the culprit?

 

Though, if I put A last, the query is blistering fast. Clearly, SQLite does 
something wrong in this case.

 

 

 

> From: paiva...@gmail.com
> Date: Fri, 30 Oct 2009 12:16:17 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to decide which table is the outer table and which 
> table is the inner table?
> 
> Of course SQLite wasn't changed much in this part since November 2008
> but the citation you gave is either wrong or the key words in it are
> "something like" in phrase "SQlite does something like this". Because
> SQLite is smart enough to choose smaller table as an outer one and
> bigger table as an inner one. Although it can choose other way round
> if you have index on Id in smaller table and don't have index on Id in
> bigger table. And in this case there's no performance hit in such
> decision, only benefit.
> 
> Pavel
> 
> On Fri, Oct 30, 2009 at 12:07 PM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx
> >
> >
> >
> > SQLite uses only nested loops to implement joins. Given a query like the 
> > following:
> >
> > SELECT ...
> > FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id
> >
> >
> >
> > SQlite does something like this:
> >
> >
> >
> > for each row in OuterTable
> >  Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
> > end for each
> >
> >
> >
> > I assume SQLite has not improved on JOIN precedence since then, which means 
> > that if OuterTable is HUGE, there will be an huge performance hit!
> >
> >
> >
> > "Some database engines like SQL Server decide which table is the outer 
> > table and which table is the inner table"
> >
> >
> > How do I simulate that behavior in SQLite? A misformed SQL statement from 
> > the user results in unacceptable lockups...
> >
> > _
> > Nya Windows 7 - Hitta en dator som passar dig! Mer information.
> > http://windows.microsoft.com/shop
> > ___
> > 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

  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to decide which table is the outer table and which table is the inner table?

[Kristoffer Danielsson]

Quote from: http://sqlite.phxsoftware.com/forums/p/1495/6629.aspx

 

SQLite uses only nested loops to implement joins. Given a query like the 
following:

SELECT ...
FROM OuterTable O INNER JOIN InnerTable I ON O.Id = I.Id

 

SQlite does something like this:

 

for each row in OuterTable
 Seek all rows in InnerTable where InnerTable.Id = OuterTable.Id
end for each

 

I assume SQLite has not improved on JOIN precedence since then, which means 
that if OuterTable is HUGE, there will be an huge performance hit!

 

"Some database engines like SQL Server decide which table is the outer table 
and which table is the inner table"


How do I simulate that behavior in SQLite? A misformed SQL statement from the 
user results in unacceptable lockups...
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regarding NATURAL JOIN (implicit cartesian product)

[Kristoffer Danielsson]

I'm not sure if the bug-fix for "natural self-join" solves this, but here we go.

 

This query yields a cartesian product:

SELECT COUNT(X) FROM A NATURAL JOIN (B NATURAL JOIN C NATURAL JOIN D);

 

However, if I rearrange the tables I get the desired result:

SELECT COUNT(X) FROM (B NATURAL JOIN C NATURAL JOIN D) NATURAL JOIN A;

 

The relationship between A and D is a "1 to N" relationship, where as B, C and 
D all have a "1 to 1" relationship.

Hence, A has fewer entries than D for each row. B+C+D limits the search space 
radically, so I would expect both the queries above to be fast.

 

Why is the first one extremely slow? Is it a bug?

 

Thanks.
  
_
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT * vs SELECT columns ?

[Kristoffer Danielsson]

I don't know about SQLite, but in all SQL courses you learn that you should 
NEVER use the asterisk.

 

The asterisk is merely there to let you quickly view data _manually_.

 
> Date: Wed, 28 Oct 2009 16:02:01 +0200
> From: mi...@limbasan.ro
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] SELECT * vs SELECT columns ?
> 
> I would expect there to be a speed and memory performance *impact* if 
> the result set contains columns other than the three specified ones, 
> since obviously the library will need to allocate more memory to hold 
> the extra data.
> 
> On 10/28/2009 03:52 PM, Pete56 wrote:
> > I am searching across two joined tables and am interested in a few
> > parameters:
> >
> > SELECT a.first a.third b.first FROM a JOIN b ON a.RowID = b.RowID WHERE
> > value = :value
> >
> > Is there any speed or memory performance improvement by using SELECT *,
> > rather than SELECT ?
> >
> > If I know there will only be one item (unique :value) selected (LIMIT 1) can
> > I make any performance improvements ?
> > 
> 
> -- 
> Cu stima,
> Mihai Limbasan
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issues for "WITH x IN (y)" - fixed with "x = y"

[Kristoffer Danielsson]

I've seen this too!

Had to refactor my "x IN (y)" code...

 

Perhaps the optimizer can be improved for this particular case?
 
> Date: Tue, 27 Oct 2009 11:06:14 -0700
> From: t...@zimbra.com
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Performance issues for "WITH x IN (y)" - fixed with "x = y"
> 
> While doing performance optimizations for queries on a table with 100k rows
> of email msg details and numerous indexes (Zimbra desktop email client), I
> found that select queries including x IN (y) can have a severe speed issue,
> especially when there is an ORDER by component as well. This is eliminated
> by converting to ³x=y² which allows the query optimizer to pick the proper
> indexes and ORDER BY when using ³explain query plain².
> 
> Also, converting x IN (y, z) to (x = y OR x = z) and expanding for up to 4-5
> conditions is also faster by about 4 msec (5+% on a Macbook pro). After
> about 7 conditions, IN becomes faster.
> 
> Slow query:
> 
> sqlite> explain query plan SELECT mi.id, mi.date AS sortcol FROM mail_item
> AS mi WHERE ((type = 16 OR type = 5) AND mi.folder_id IN (2)) ORDER BY
> sortcol DESC LIMIT 0,119;
> 0|0|TABLE mail_item AS mi WITH INDEX i_mail_item_folder_id_date
> 
> 
> Fast query:
> 
> sqlite> explain query plan SELECT mi.id, mi.date AS sortcol FROM mail_item
> AS mi WHERE ((type = 16 OR type = 5) AND mi.folder_id = 2) ORDER BY sortcol
> DESC LIMIT 0,119;
> 0|0|TABLE mail_item AS mi WITH INDEX i_mail_item_folder_id_date ORDER BY
> 
> 
> Database definition:
> 
> CREATE TABLE IF NOT EXISTS mail_item (
> id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
> type TINYINT NOT NULL,
> parent_id INTEGER UNSIGNED,
> folder_id INTEGER UNSIGNED,
> index_id VARCHAR(225),
> imap_id INTEGER UNSIGNED,
> date INTEGER UNSIGNED NOT NULL,
> size BIGINT UNSIGNED NOT NULL,
> volume_id VARCHAR(255),
> blob_digest VARCHAR(28),
> unread INTEGER UNSIGNED,
> flags INTEGER NOT NULL DEFAULT 0,
> tags BIGINT NOT NULL DEFAULT 0,
> sender VARCHAR(128),
> subject TEXT,
> name VARCHAR(128),
> metadata MEDIUMTEXT,
> mod_metadata INTEGER UNSIGNED NOT NULL,
> change_date INTEGER UNSIGNED,
> mod_content INTEGER UNSIGNED NOT NULL,
> change_mask INTEGER UNSIGNED,
> 
> CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (parent_id) REFERENCES
> mail_item(id),
> CONSTRAINT fk_mail_item_folder_id FOREIGN KEY (folder_id) REFERENCES
> mail_item(id)
> );
> 
> CREATE INDEX IF NOT EXISTS i_mail_item_type ON mail_item(type);
> CREATE INDEX IF NOT EXISTS i_mail_item_parent_id ON mail_item(parent_id);
> CREATE INDEX IF NOT EXISTS i_mail_item_folder_id_date ON
> mail_item(folder_id, date DESC);
> CREATE INDEX IF NOT EXISTS i_mail_item_index_id ON mail_item(index_id);
> CREATE INDEX IF NOT EXISTS i_mail_item_unread ON mail_item(unread);
> CREATE INDEX IF NOT EXISTS i_mail_item_date ON mail_item(date DESC);
> CREATE INDEX IF NOT EXISTS i_mail_item_mod_metadata ON
> mail_item(mod_metadata);
> CREATE INDEX IF NOT EXISTS i_mail_item_tags_date ON mail_item(tags, date
> DESC);
> CREATE INDEX IF NOT EXISTS i_mail_item_flags_date ON mail_item(flags, date
> DESC);
> CREATE INDEX IF NOT EXISTS i_mail_item_change_mask ON mail_item(change_mask,
> date);
> 
> 
> I am using sqlite 3.6.18.
> 
> Thank you,
> Todd Richmond
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea for improving page cache

[Kristoffer Danielsson]

I really like the concept of ULL. Check this one out:

 

http://blogs.msdn.com/devdev/archive/2005/08/22/454887.aspx

 

Don't know if would be of any use for SQLite, but it does indeed provide an 
advantage compared to regular linked lists.
 
> Date: Tue, 27 Oct 2009 14:59:36 -0400
> From: johncrens...@priacta.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Idea for improving page cache
> 
> Supposing that the reduced cache misses are worth it, I think it would be 
> better to simply allocate the nodes from a pool. Allocating from a pool 
> maximizes locality and prevents the overhead involved in each allocation. 
> Since the nodes have static size, pool allocation is easy. This doesn't save 
> the size of the pointers, but let's face it, a couple of pointers doesn't add 
> up to much here. Pool allocation also doesn't impose any of the additional 
> limitations that ULL would (for example, migration from list to btree would 
> still be easy).
> 
> John
> 
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Tuesday, October 27, 2009 1:38 PM
> To: kennethinbox-sql...@yahoo.com; General Discussion of SQLite Database
> Subject: Re: [sqlite] Idea for improving page cache
> 
> Are you sure that there will be improvement with ULL?
> If you're talking about improving due to CPU internal cache then first
> of all you have to store in the list pointers to pages, not pages
> themselves (you don't want to store several pages in one chunk of
> memory, do you?). So you're getting one more pointer dereference every
> time you go to the list. Then you have to store additional information
> in the page to remember where in the list pointer to this page is
> stored. And each time list nodes are split or combined you have to
> change this information in each page.
> And now the main argument: ULL is good when you want to save memory
> overhead (which is very questionable in case of page cache) and good
> in getting elements by index and traversal of the whole list. Last two
> operations are never executed in SQLite.
> So looking at all this I don't see how performance can be improved
> (for me it seems that it's quite the opposite). Did I overlook
> something?
> 
> Pavel
> 
> On Tue, Oct 27, 2009 at 1:07 PM, Ken  wrote:
> > Hi All,
> >
> > I have an idea that could improve the page cache performance.
> >
> > Instead of using a regular linked list to connect pages that are on the 
> > cache use an "unrolled linked list".  On some architectures due to the CPU 
> > caching the ULL is about 40 times faster.
> >
> > Still this is mostly insignificant to the speed of disk i/o but every bit 
> > helps...
> >
> > Just an idea, not sure if its been considered, feasible or even worthwhile.
> >
> > Ken
> > ___
> > 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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea for improving page cache

[Kristoffer Danielsson]

In game development you seldom use linked list altogether due to the increased 
rate of cache-misses.

 

Why not use an array with some smart lookup-algorithm?
 
> From: paiva...@gmail.com
> Date: Tue, 27 Oct 2009 13:38:27 -0400
> To: kennethinbox-sql...@yahoo.com; sqlite-users@sqlite.org
> Subject: Re: [sqlite] Idea for improving page cache
> 
> Are you sure that there will be improvement with ULL?
> If you're talking about improving due to CPU internal cache then first
> of all you have to store in the list pointers to pages, not pages
> themselves (you don't want to store several pages in one chunk of
> memory, do you?). So you're getting one more pointer dereference every
> time you go to the list. Then you have to store additional information
> in the page to remember where in the list pointer to this page is
> stored. And each time list nodes are split or combined you have to
> change this information in each page.
> And now the main argument: ULL is good when you want to save memory
> overhead (which is very questionable in case of page cache) and good
> in getting elements by index and traversal of the whole list. Last two
> operations are never executed in SQLite.
> So looking at all this I don't see how performance can be improved
> (for me it seems that it's quite the opposite). Did I overlook
> something?
> 
> Pavel
> 
> On Tue, Oct 27, 2009 at 1:07 PM, Ken  wrote:
> > Hi All,
> >
> > I have an idea that could improve the page cache performance.
> >
> > Instead of using a regular linked list to connect pages that are on the 
> > cache use an "unrolled linked list".  On some architectures due to the CPU 
> > caching the ULL is about 40 times faster.
> >
> > Still this is mostly insignificant to the speed of disk i/o but every bit 
> > helps...
> >
> > Just an idea, not sure if its been considered, feasible or even worthwhile.
> >
> > Ken
> > ___
> > 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
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does SQLite treat repeated expressions?

[Kristoffer Danielsson]

Sure I can test this specific case, but my question is still relevant. Does 
SQLite or does it not optimize repeated expressions?

 

Am I supposed to brute-force all possible expression repetitions to find it 
out, when a simple "yes" or "no" from anybody - who knows about the inner 
workings of SQLite - would suffice?
 
> Date: Sun, 25 Oct 2009 10:08:01 -0500
> From: punk.k...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How does SQLite treat repeated expressions?
> 
> On Sun, Oct 25, 2009 at 9:49 AM, Igor Tandetnik <itandet...@mvps.org> wrote:
> > Kristoffer Danielsson wrote:
> >> A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);
> >>
> >> B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;
> >>
> >> Is statement B faster than A? Why?
> >
> > Last time I checked, SQLite didn't perform any kind of common subexpression 
> > elimitation. Its expression evaluator was very simple-minded, closely 
> > following the syntax tree.
> >
> > Things might have improved since then, but I wouldn't be surprised if 
> > statement A calls count() twice per row while statement B only once. I also 
> > think that any resulting difference in performance is likely to be 
> > immeasurably small. Why don't you test it and find out, if you are really 
> > curious?
> >
> 
> 
> +1 re "Why don't you test it and find out, if you are really curious?"
> 
> While I can certainly understand a question of the sort,
> 
> "I did A and I did B, and I found B to be faster than A; why?"
> 
> I really can't understand the point of questions such as,
> 
> "Here is A and B; which one will be faster?"
> 
> since the best person to answer the latter question is the person
> posing the question -- just test it and find out.
> 
> Now, it could be that implementing A or B just for testing purposes
> might be so difficult that the poster might want to get a sense
> *before* diving into the test. That is understandable, but,
> implementing something based solely on the unmeasured advice of others
> is itself questionable.
> 
> 
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> Sent from Madison, WI, United States
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How does SQLite treat repeated expressions?

[Kristoffer Danielsson]

Consider the two statements below.

 

A: SELECT COUNT(DataID) FROM Data GROUP BY DataID ORDER BY COUNT(DataID);

B: SELECT COUNT(DataID) AS X FROM Data GROUP BY DataID ORDER BY X;

 

Is statement B faster than A? Why?
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prevent cartesian products altogether?

[Kristoffer Danielsson]

I want to my SQLite database to NEVER accept cartesian products. That is, if 
the user makes a query that results in a cartesian product (explicit or 
implicit), the execution of it must fail!

 

Is this possible?
  
_
Nya Windows 7 - Hitta en dator som passar dig! Mer information. 
http://windows.microsoft.com/shop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Null-values in NATURAL JOIN

[Kristoffer Danielsson]

Is this the right behavior?

 

CREATE TABLE t1(x INTEGER NOT NULL, y INTEGER);


INSERT INTO t1(x, y) VALUES (1, 0);
INSERT INTO t1(x, y) VALUES (2, 1);
INSERT INTO t1(x, y) VALUES (3, NULL);


sqlite> SELECT * FROM t1 A NATURAL JOIN t1 B;
1|0
2|1


I sense this could be right, but anyway... why isn't the null-row added to the 
result?

 

Sure, NULL != NULL, but in this case it's the very same value in the database.

 

Please enlighten me!

Thanks
  
_
Hitta kärleken nu i vår!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

It makes sense.

 

I'll investigate this bug later. I'm quite sure it's no more than a couple of 
months old, since I only got these crazy performance hits just after the summer 
(I upgrade regularly, but haven't tested my software thoroughly - until now :P).

 

Thanks for your info.

 
> Date: Mon, 19 Oct 2009 12:19:02 -0700
> From: rog...@rogerbinns.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Kristoffer Danielsson wrote:
> > When can we expect a bug fix? Days, weeks, months?
> 
> Go to http://www.sqlite.org/src/reportlist and you can see all open tickets.
> (Currently there is no report in priority order). SQLite consortium
> members and paying customers get first priority.
> 
> You can see the timeline for what work is being done:
> 
> http://www.sqlite.org/src/timeline
> 
> Other than that (and I do not speak for the SQLite team here - just a long
> time observer) criteria such as how many people are affected, the severity,
> the difficulty of the fix, the likelihood of the fix breaking something else
> etc are used. For example if this bug has been present in SQLite since 3.0
> (5 years ago) then many people don't hit it. If you can narrow it down to
> the release (or even better checkin) that caused the problem then that makes
> it considerably easier to fix. You can do a bisection search to find the
> culprit. (Sadly it doesn't appear that fossil can do bisection so you'll
> have to do it manually.)
> 
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> 
> iEYEARECAAYFAkrcu6IACgkQmOOfHg372QSYHQCfbQ6XSVCvbJXn3eR2u9L5WHuq
> +wwAoIQuky96CdIQypBHBMkhpSX9wWkz
> =dxic
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Håll dina vänner uppdaterade om vad du gör online.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_1:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

Hi,

 

Great to hear. I was starting to think my code was damaged. Anyway, please 
beware of cases such as this:

 

SELECT * FROM X NATURAL JOIN (X NATURAL JOIN Y);

 

This yields the same error. Probably the same bug, but you never know.

 

When can we expect a bug fix? Days, weeks, months?

 

Thanks.
 
> From: danielk1...@gmail.com
> To: sqlite-users@sqlite.org
> Date: Mon, 19 Oct 2009 14:44:27 +0700
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
> 
> 
> On Oct 19, 2009, at 2:05 PM, Kristoffer Danielsson wrote:
> 
> >
> > I did provide a sample with test data in my original post. However, 
> > I posted it here too:
> >
> > http://www.sqlite.org/cvstrac/tktview?tn=4043
> >
> >
> >
> > The tracker is obviously closed, but still, there it is :P. Don't 
> > know where else to post it.
> 
> Thanks for finding this bug. There is now a ticket here:
> 
> http://www.sqlite.org/src/info/b73fb0bd64
> 
> Current policy on bug reports is here:
> 
> http://www.sqlite.org/src/wiki?name=Bug+Reports
> 
> IMO it is a good idea to include "BUG" in the subject (as you have 
> done in
> this thread) if your mail is a bug report. This will help make sure that
> the report doesn't get missed.
> 
> Dan.
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Hitta kärleken nu i vår!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

I did provide a sample with test data in my original post. However, I posted it 
here too:

http://www.sqlite.org/cvstrac/tktview?tn=4043

 

The tracker is obviously closed, but still, there it is :P. Don't know where 
else to post it.

 

/Chris
 
> Date: Sun, 18 Oct 2009 16:25:48 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
> 
> Kristoffer Danielsson wrote:
> > NATURAL INNER JOIN yields a cartesian product too. Just tried it!
> 
> Well then, *surely* that at least has to be a SQLite bug.
> 
> That said, it would be useful for clarity if you posted to the list exactly 
> how 
> you wrote your NATURAL INNER, what the exact SQL you used was, with test data 
> and result, so we know you didn't just make a typo in your test.
> 
> Use the simplest possible example that illustrates the point, such as with 
> Jay's 
> example.
> 
> And I would expect NATURAL JOIN to default to INNER even if JOIN defaults to 
> CROSS, just because that makes the most sense. When people specify NATURAL, 
> then what they expect is INNER semantics in the general case.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Hitta kärleken nu i vår!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

NATURAL INNER JOIN yields a cartesian product too. Just tried it!
 
> Date: Sun, 18 Oct 2009 15:54:11 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "x NATURAL JOIN x" BUG
> 
> Jay A. Kreibich wrote:
> > On Sun, Oct 18, 2009 at 02:17:42PM +0200, Kristoffer Danielsson scratched 
> > on the wall:
> >> Clearly, SQLite executes a cartesian product!
> > 
> > Look at the output. It does not produce a Cartesian product. All
> > the rows are valid:
> > 
> > SQLite version 3.6.19
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table t (c1, c2);
> > sqlite> insert into t values ( 1, 2 );
> > sqlite> insert into t values ( 3, 4 );
> > sqlite> insert into t values ( 5, 6 );
> > sqlite> select * from t natural join t; 
> > 1|2
> > 1|2
> > 1|2
> > 3|4
> > 3|4
> > 3|4
> > 5|6
> > 5|6
> > 5|6
> > 
> > I'm not sure I'd call it correct, but it isn't a product.
> 
> Jay, you've just proven Kristoffer's point. That result demonstrates that a 
> cartesian product *was* produced. The table t had 3 rows, and the result had 
> 3*3 rows, which is a cartesian product by definition.
> 
> Your query should have produced the same result as this query:
> 
> select t1.* from t as t1 inner join t as t2 using (c1,c2);
> 
> ... but instead it produced the same result as this query:
> 
> select t1.* from t as t1 cross join t as t2 using (c1,c2);
> 
> Now I would be ready to consider that SQLite has a bug, but then looking at 
> the 
> syntax at http://sqlite.org/lang_select.html I see that SQLite defines 
> multiple 
> versions of natural join; it has *both* NATURAL INNER JOIN and NATURAL CROSS 
> JOIN, and I'm guessing that if you leave the middle word out it is using 
> CROSS 
> by default, ostensibly for consistency for when you simply say JOIN.
> 
> So if that is the case, then the current behavior is clearly documented as 
> expected and so not an implementation bug. And so then you would have to say 
> this:
> 
> select * from t natural inner join t;
> 
> ... to get the expected result of 3 rows.
> 
> This all being said, the whole mess strikes me as a *design bug*. It simply 
> doesn't make sense to have both NATURAL INNER and NATURAL CROSS syntax. One 
> should simply be able to say NATURAL and it would do the right thing, which 
> is a 
> cartesian product when no column names are the same, an intersect when all 
> column names are the same, and an inner join otherwise.
> 
> My proposal is certainly logically sound. A natural join by definition only 
> has 
> a result row for each distinct pair of source rows that have the same values 
> for 
> the subset of their columns with the same names; a cartesian product is a 
> degenerate case where that subset of columns has zero members, and so since 
> the 
> empty set matches the empty set every row from each source rowsets would 
> match 
> every row from the other rowsets.
> 
> The only variations that make sense on a natural join is OUTER.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på 
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "x NATURAL JOIN x" BUG

[Kristoffer Danielsson]

Thanks Duncan, for your feedback.

 

This is indeed a bug (currently running SQLite 3.6.18). I guess this is where 
you report bugs?

 

1) Run the C++ program below to generate the necessary SQL data.

2) Then run sqlite3.exe and read it in! (.read test.sql)

3) Execute the following queries:

 

Blistering fast:

SELECT COUNT(*) FROM Item;

Result: 1

 

Slow! WRONG result:

SELECT COUNT(*) FROM Item NATURAL JOIN Item;

Result: 1

 

Clearly, SQLite executes a cartesian product!

 

Oddly, this works as intented:

SELECT COUNT(*) FROM (Item) NATURAL JOIN (Item);

Result: 1

 

 

Unfortunately, this is absolutely crucial for my application. I hope this can 
be fixed very soon!

 

//

#include 

int main()
{
 std::ofstream file ("test.sql");
 
 file << "CREATE TABLE IF NOT EXISTS Item (ItemID INTEGER PRIMARY KEY, A 
INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER NOT NULL, D INTEGER NOT NULL, E 
INTEGER NOT NULL, UNIQUE (A, B));\r\n";
 file << "BEGIN TRANSACTION;\r\n";
 for (int i = 0; i < 1; ++i)
 {
  file << "INSERT INTO Item (A, B, C, D, E) VALUES ("
<< i << ", " << i + 1 << ", " << i + 2 << ", "
<< i + 3 << ", " << i + 4 << ");\r\n";
 }
 file << "COMMIT TRANSACTION;\r\n";
 
 file.close();

 return 0;
}

//

 

 

 

 

 

 

 

 


 
> Date: Sat, 17 Oct 2009 23:02:10 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign keys + NATURAL JOIN
> 
> Kristoffer Danielsson wrote:
> > Thanks.
> > 
> > This leads me to the next question.
> > 
> > Why does the statement below yield a cartesian product?
> > 
> > SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw!
> 
> It doesn't. In fact "t1 NATURAL JOIN t1" would do the exact opposite, because 
> *all* of the columns have the same names, and moreover because both rowsets 
> being joined are the same rowset, the result should be identical to if you 
> said 
> "t1 INTERSECT t1", which is the same as if you simply said "t1" without a 
> join 
> at all. Natural joining something with itself results in itself, and is 
> analogous to "1 * 1 = 1" in math.
> 
> > Why does the statement below NOT yield a cartesian product?
> > 
> > SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster 
> > than the query above!
> 
> This statement should have an identical result to the first one. Having 
> parenthesis around each t1 should make no difference.
> 
> > Sure, the query is brain-damaged, but this could happen "by accident" in my 
> > software.
> > 
> > I'd expect SQLite to optimize this to simply "t1"!
> 
> If SQLite is treating the above 2 queries differently, I would think that an 
> error. Are you sure that's what's happening?
> 
> If you are natural joining a table to itself, or intersecting a table with 
> itself, or unioning a table with itself, then hopefully the optimizer is 
> smart 
> enough to replace that operation with simply the table itself.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på 
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign keys + NATURAL JOIN

[Kristoffer Danielsson]

Thanks.

 

This leads me to the next question.

 

Why does the statement below yield a cartesian product?

SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw!

 

Why does the statement below NOT yield a cartesian product?

SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster than 
the query above!

 

Sure, the query is brain-damaged, but this could happen "by accident" in my 
software.

 

I'd expect SQLite to optimize this to simply "t1"!
 
> Date: Sat, 17 Oct 2009 21:08:31 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign keys + NATURAL JOIN
> 
> Kristoffer Danielsson wrote:
> > Q: Does foreign keys affect NATURAL JOINs?
> > 
> > I haven't tested this. Does this NATURAL JOIN produce an inner join or a 
> > cartesian product?
> 
> The presence of foreign key constraints has no effect on the results of any 
> queries, using natural joins or otherwise.
> 
> A natural join between 2 tables/rowsets gives you a cartesian product if and 
> only if none of the column names are the same. A natural join gives you an 
> inner join if and only if at least one column name is the same in both 
> tables/rowsets.
> 
> > Anyway, I think this should be documented.
> 
> What I have described above is normal/proper behavior in relational or SQL 
> DBMSs. What details exactly are you proposing need explicit documentation 
> versus what would be going too far or stating the obvious?
> 
> > SQLite 3.6.18 sample (NO foreign keys):
> > 
> > CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);
> > 
> > CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL);
> > 
> > SELECT * FROM a NATURAL JOIN b; -- Cartesian product!
> 
> I think you meant to say:
> 
> SELECT * FROM t1 NATURAL JOIN t2;
> 
> ... and yes, this is indeed a cartesian product since (a,b)∩(x,y) is the 
> empty set.
> 
> > SQLite 3.6.19 sample (using foreign keys):
> > 
> > CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);
> > 
> > CREATE TABLE t2 (x INTEGER PRIMARY KEY, FOREIGN KEY(y) REFERENCES t1(a) );
> > 
> > SELECT * FROM a NATURAL JOIN b; -- Inner join??
> 
> I think you meant to say:
> 
> CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL, FOREIGN KEY(y) 
> REFERENCES t1(a) );
> 
> SELECT * FROM t1 NATURAL JOIN t2;
> 
> ... and no, this is also a cartesian product since (a,b)∩(x,y) is the empty 
> set.
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign keys + NATURAL JOIN

[Kristoffer Danielsson]

Q: Does foreign keys affect NATURAL JOINs?

 

SQLite 3.6.18 sample (NO foreign keys):

CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);

CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL);

SELECT * FROM a NATURAL JOIN b; -- Cartesian product!

 

SQLite 3.6.19 sample (using foreign keys):

CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL);

CREATE TABLE t2 (x INTEGER PRIMARY KEY, FOREIGN KEY(y) REFERENCES t1(a) );

 

SELECT * FROM a NATURAL JOIN b; -- Inner join??

 

I haven't tested this. Does this NATURAL JOIN produce an inner join or a 
cartesian product?

 

 

Anyway, I think this should be documented.
  
_
Hitta hetaste singlarna på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple NATURAL JOIN precedence?

[Kristoffer Danielsson]

Thanks for your clarification, Pavel!
 
> From: paiva...@gmail.com
> Date: Wed, 7 Oct 2009 09:28:37 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multiple NATURAL JOIN precedence?
> 
> > Q: When joining two tables, does SQLite choose the smaller one as the 
> > driving table? If I understand things correctly, this yields a performance 
> > gain.
> 
> In fact this is not always true. For example let's say you're doing
> 
> SELECT * FROM t1 INNER JOIN t2 ON t1.x = t2.x ORDER BY t2.y
> 
> If t1 and t2 do not have any indexes then it will not matter much what
> table is driving one - SQLite will have to make cartesian join anyway,
> doing cnt_t1 * cnt_t2 iterations (if cnt_t1 is number of rows in t1
> and cnt_t2 is number of rows in t2). If the only index in the system
> is on t2.y then making t2 driving table will have benefit no matter
> big it or small. If the only index is on t2.x then t1 is better to be
> driving no matter how big it is, although if t2 is too small benefit
> will be negligible. But the biggest benefit will be if there're 2
> indexes - on t2.y and t1.x and if t2 is driving table. And it doesn't
> matter again if t2 big or small.
> But in case if you do another query:
> 
> SELECT * FROM t1 INNER JOIN t2 ON t1.x = t2.x WHERE t1.y = c1 AND t2.z = c2
> 
> And you have 2 indexes on t1.x and t2.x then indeed having smaller
> table as driving table will be better.
> 
> All these are pretty simple theoretical examples but I believe they
> all are implemented in SQLite optimizer.
> 
> 
> Pavel
> 
> On Wed, Oct 7, 2009 at 9:08 AM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > I see my problem now. You said it below: "cartesian product". I believe 
> > "NATURAL JOIN" should be used with caution.
> >
> >
> >
> > I'll demonstrate my mistake with a small sample:
> >
> >
> >
> > CREATE TABLE t1 (x INTEGER PRIMARY KEY, a INTEGER NOT NULL);
> >
> > CREATE TABLE t2 (x INTEGER PRIMARY KEY, b INTEGER NOT NULL);
> >
> > CREATE TABLE t3 (y INTEGER PRIMARY KEY, c INTEGER NOT NULL);
> >
> > CREATE TABLE t4 (y INTEGER PRIMARY KEY, x INTEGER NOT NULL);
> >
> >
> >
> > SELECT COUNT(*) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4; -- 
> > wrong... and slow due to cartesian product!
> >
> > SELECT COUNT(*) FROM t1 NATURAL JOIN t2 NATURAL JOIN t4 NATURAL JOIN t3; -- 
> > correct... and fast!
> >
> >
> >
> > So SQLite did the right thing here. I have one more question though:
> >
> >
> >
> > Q: When joining two tables, does SQLite choose the smaller one as the 
> > driving table? If I understand things correctly, this yields a performance 
> > gain.
> >
> >
> >
> >
> >
> > Thanks.
> >
> >
> >
> >
> >> Date: Tue, 6 Oct 2009 20:33:17 -0700
> >> From: dar...@darrenduncan.net
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] Multiple NATURAL JOIN precedence?
> >>
> >> Kristoffer Danielsson wrote:
> >> > All my tables have unique column names, so I tend to use "NATURAL JOIN" 
> >> > for
> >> > my joins (all join-columns are indexed). When I have multiple tables, I
> >> > sometimes get incredibly slow queries (not sure if they even terminate).
> >>
> >> If all of your tables have unique column names, then a natural join would
> >> degenerate to a cartesian product, because there are no column pairs across
> >> different tables for which only rows having matching values are kept. A
> >> cartesian product would almost always be slow regardless of your JOIN 
> >> syntax.
> >> I'll assume that you meant to say that all of your columns *except* the 
> >> ones you
> >> are joining on, have unique column names, which makes more sense. Correct 
> >> me if
> >> I'm wrong.
> >>
> >> > SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 
> >> > GROUP
> >> > BY x ORDER BY x; -- takes forever, whereas:
> >> >
> >> > SELECT SUM(x) FROM t2 NATURAL JOIN t3 NATURAL JOIN t4 NATURAL JOIN t1 
> >> > GROUP
> >> > BY x ORDER BY x; -- takes one second
> >>
> >> Are all of those "x" supposed to be the same column?
> >>
> >> I don't think it makes sense to do all of [SUM(x), GROUP BY x, ORDER BY x] 
> >> in
> >> the same query.
> >>
> >> If the query is supposed to have exactl

Re: [sqlite] Multiple NATURAL JOIN precedence?

[Kristoffer Danielsson]

I see my problem now. You said it below: "cartesian product". I believe 
"NATURAL JOIN" should be used with caution.

 

I'll demonstrate my mistake with a small sample:

 

CREATE TABLE t1 (x INTEGER PRIMARY KEY, a INTEGER NOT NULL);

CREATE TABLE t2 (x INTEGER PRIMARY KEY, b INTEGER NOT NULL);

CREATE TABLE t3 (y INTEGER PRIMARY KEY, c INTEGER NOT NULL);

CREATE TABLE t4 (y INTEGER PRIMARY KEY, x INTEGER NOT NULL);

 

SELECT COUNT(*) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4; -- 
wrong... and slow due to cartesian product!

SELECT COUNT(*) FROM t1 NATURAL JOIN t2 NATURAL JOIN t4 NATURAL JOIN t3; -- 
correct... and fast!

 

So SQLite did the right thing here. I have one more question though:

 

Q: When joining two tables, does SQLite choose the smaller one as the driving 
table? If I understand things correctly, this yields a performance gain.

 

 

Thanks.

 

 
> Date: Tue, 6 Oct 2009 20:33:17 -0700
> From: dar...@darrenduncan.net
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multiple NATURAL JOIN precedence?
> 
> Kristoffer Danielsson wrote:
> > All my tables have unique column names, so I tend to use "NATURAL JOIN" for
> > my joins (all join-columns are indexed). When I have multiple tables, I
> > sometimes get incredibly slow queries (not sure if they even terminate).
> 
> If all of your tables have unique column names, then a natural join would 
> degenerate to a cartesian product, because there are no column pairs across 
> different tables for which only rows having matching values are kept. A 
> cartesian product would almost always be slow regardless of your JOIN syntax. 
> I'll assume that you meant to say that all of your columns *except* the ones 
> you 
> are joining on, have unique column names, which makes more sense. Correct me 
> if 
> I'm wrong.
> 
> > SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 GROUP
> > BY x ORDER BY x; -- takes forever, whereas:
> > 
> > SELECT SUM(x) FROM t2 NATURAL JOIN t3 NATURAL JOIN t4 NATURAL JOIN t1 GROUP
> > BY x ORDER BY x; -- takes one second
> 
> Are all of those "x" supposed to be the same column?
> 
> I don't think it makes sense to do all of [SUM(x), GROUP BY x, ORDER BY x] in 
> the same query.
> 
> If the query is supposed to have exactly 1 output row, counting the number of 
> groups resulting from the GROUP BY, then the ORDER BY is useless, and makes 
> the 
> query slower (unless a smart optimizer eliminates it from ever running).
> 
> If the query is supposed to have an output row for each distinct value of x 
> from 
> the GROUP BY, then SUM(x) would presumably be the same number as x for every 
> row.
> 
> Did you mean this?:
> 
> SELECT foo, SUM(bar) FROM ... GROUP BY foo ORDER BY foo
> 
> -- Darren Duncan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
_
Windows Live: Gör det enklare för dina vänner att se vad du håller på med på 
Facebook.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_2:092009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple NATURAL JOIN precedence?

[Kristoffer Danielsson]

All my tables have unique column names, so I tend to use "NATURAL JOIN" for my 
joins (all join-columns are indexed). When I have multiple tables, I sometimes 
get incredibly slow queries (not sure if they even terminate).

 

E.g.

SELECT SUM(x) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3 NATURAL JOIN t4 GROUP BY 
x ORDER BY x; -- takes forever, whereas:

SELECT SUM(x) FROM t2 NATURAL JOIN t3 NATURAL JOIN t4 NATURAL JOIN t1 GROUP BY 
x ORDER BY x; -- takes one second

 

I've run the ANALYZE-command, but the order of my tables still seems to be 
crucial. In this case T1 has some 50,000 entries whereas T2 only has 100.

 

Q: Do I have to choose the order of my columns with caution? Is the optimizer 
stupid when it comes to this?
  
_
Windows Live: Håll dina vänner uppdaterade om vad du gör online.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_1:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Stored procedures" performance issue

[Kristoffer Danielsson]

This makes sense. Though, I think the documentation should cover this.

 

Thanks for your response.
 
> From: paiva...@gmail.com
> Date: Mon, 5 Oct 2009 09:31:10 -0400
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] "Stored procedures" performance issue
> 
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> > Is this possible? If so, how?
> 
> Even if this was possible it would be useless because you cannot say
> if SQLite traverses table via index or makes just full table scan. And
> in your particular case SQLite will see that there's no benefit in
> using any index and will just scan the whole table.
> The general advice in this case is to add column to the table which
> will store the result of your function, create an index on that
> additional column and then use that column in the query. In this case
> SQLite will understand that it's better to use index and will use it.
> 
> Pavel
> 
> On Mon, Oct 5, 2009 at 9:24 AM, Kristoffer Danielsson
> <kristoffer.daniels...@live.se> wrote:
> >
> > When I create my own "stored procedures" using sqlite3_create_function, I 
> > get horrible performance (which I expected) even though the column of 
> > interest is INDEXED.
> >
> >
> > Consider this sample (it's stupid, but it shows my problem):
> >
> > SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate);
> >
> >
> >
> > IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
> > function, if I encounter a date greater than my birthday, then I want to 
> > tell SQLite to stop searching, since the date is indexed.
> >
> >
> >
> > Is this possible? If so, how?
> >
> >
> > Thanks.
> >
> > _
> > Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg 
> > när de skickar e-post till dig.
> > http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
> > ___
> > 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
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Stored procedures" performance issue

[Kristoffer Danielsson]

When I create my own "stored procedures" using sqlite3_create_function, I get 
horrible performance (which I expected) even though the column of interest is 
INDEXED.

 
Consider this sample (it's stupid, but it shows my problem):

SELECT * FROM MyTable WHERE IS_MY_BIRTHDAY(IndexedDate); 

 

IS_MY_BIRTHDAY returns 1 when the date is my birthday. Now, inside this 
function, if I encounter a date greater than my birthday, then I want to tell 
SQLite to stop searching, since the date is indexed.

 

Is this possible? If so, how?


Thanks.
  
_
Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när 
de skickar e-post till dig.
http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Merging two databases

[Kristoffer Danielsson]

Consider this simple database:

 

CREATE TABLE T1

(TestID INTEGER PRIMARY KEY, X INTEGER NOT NULL, Y NOT NULL, Z NOT NULL, 
UNIQUE(X, Y));

 

CREATE TABLE T2

(TestID INTEGER NOT NULL, X2 INTEGER NOT NULL, Y2 NOT NULL, Z2 NOT NULL);

 

What would be the most efficient way of copying all T1 entries + their 
corresponding 0..N T2 entries from one database to another (both with the same 
schema)? Note that TestID=1 in Database1 could be a totally different item than 
TestID=1 in Database2! Also pay attention to the UNIQUE-clause. It implies 
"semi-unique coordinates" in this case, which makes the merge a bit harder 
(perhaps an "INSERT OR IGNORE" is sufficient here?)!

 

I tried "INSERT INTO T1 from (SELECT * FROM Database2.T1)" but soon realized 
SQLite does not handle this properly. E.g. SQL Server takes care of the primary 
key in this case. SQLite doesn't!

Also, how would I copy all T2-items properly? Using triggers?

 

Thanks for your help!

/Chris

_
Vem är du? Gör personlighetstestet på MSN Dejting!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multiple SQL statements for regression tests?

[Kristoffer Danielsson]

I have made a topdown parser of a subset of the SQLite syntax. Now I need to 
verify its correctness.
 
Does the SQLite developer team make use of some kind of regression tests where 
complex statements are being tested?
It would really be great if I could find some 100 SELECT-statements for testing!
 
Thanks.
_

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

Re: [sqlite] HOWTO: Parse user SQL and add persistent SQL

[Kristoffer Danielsson]

Kristoffer Danielsson wrote:> Consider an application where the user enters a 
search string:> SELECT Year FROM Car WHERE Brand = 'Audi'>  > Now I want to add 
some conditions, depending on settings in the application. For instance, I 
would like to modify the above SQL string to get this:> SELECT Year, Color FROM 
Car WHERE Brand = 'Audi' AND DateCreated > '2008-10-10'>  > Obviously, making 
string replacements is not a good idea. The only smart solution is to somehow 
edit the tree-representation of the SQL statement. Probably the same technique 
used for statement optimization.>  > The parser is already there so the 
question is; how do I edit the SQL-statement on a low level, before executing 
it?>  > Thanks. 
Darren Duncan wrote:> A better safer solution for most cases is to not let user 
type raw SQL at all, > but rather you can just provide a collection of form 
fields where they enter the > basic information and then you generate a SQL 
query from it.  In that case, you > are *starting* with the tree as it were. -- 
Darren Duncan
Thanks, but that's plan B. However, if it turns out that parsing the 
VM-structure is incredibly hard, then I will stick to your solution.
_
Var sommaren för kort? Här hittar du solen!
http://resor.se.msn.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] HOWTO: Parse user SQL and add persistent SQL

[Kristoffer Danielsson]

Consider an application where the user enters a search string:
SELECT Year FROM Car WHERE Brand = 'Audi'
 
Now I want to add some conditions, depending on settings in the application. 
For instance, I would like to modify the above SQL string to get this:
SELECT Year, Color FROM Car WHERE Brand = 'Audi' AND DateCreated > '2008-10-10'
 
Obviously, making string replacements is not a good idea. The only smart 
solution is to somehow edit the tree-representation of the SQL statement. 
Probably the same technique used for statement optimization.
 
The parser is already there so the question is; how do I edit the SQL-statement 
on a low level, before executing it?
 
Thanks.
_
Senaste sportnyheterna & rykande färska resultat!
http://sport.msn.se/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users