Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
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
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
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
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
Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
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
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
Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
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