3.6.20 NATURAL self-join still not fixed

[Kristoffer Danielsson]

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
     
_________________________________________________________________
Hitta kärleken i vinter!
http://dejting.se.msn.com/channel/index.aspx?trackingid=1002952
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Dan Kennedy-4]

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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dan Kennedy wrote:
> 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?

For the record I can't reproduce it with the schema in the ticket and 3.6.20.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkr1AewACgkQmOOfHg372QQoEQCff+JT5TenavLPcmgAlNfRgPtq
6tQAoNT0cwxmt/Xvl80EcCHvjXeQ0OOb
=MOyt
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[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: danielk1977@...
> To: sqlite-users@...
> 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@...
> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[SimonDavies]

2009/11/7 Kristoffer Danielsson <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...

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: danielk1977@...
>> To: sqlite-users@...
>> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[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 +0000
> From: simon.james.davies@...
> To: sqlite-users@...
> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
>
> 2009/11/7 Kristoffer Danielsson <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...
>
> 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: danielk1977@...
> >> To: sqlite-users@...
> >> 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@...
> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[D. Richard Hipp]

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
drh@...



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

[D. Richard Hipp]

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
drh@...



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

[Kristoffer Danielsson]

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


 

> From: drh@...
> To: sqlite-users@...
> 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
> drh@...
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kristoffer Danielsson]

Sorry :P

 

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

 

Thanks.
 

> From: drh@...
> To: sqlite-users@...
> 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
> drh@...
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> 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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users