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 +0000 > 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
