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: 10000
Slooooow! WRONG result:
SELECT COUNT(*) FROM Item NATURAL JOIN Item;
Result: 100000000
Clearly, SQLite executes a cartesian product!
Oddly, this works as intented:
SELECT COUNT(*) FROM (Item) NATURAL JOIN (Item);
Result: 10000
Unfortunately, this is absolutely crucial for my application. I hope this can
be fixed very soon!
//////////////////////////////////////////////////////////////////////////
#include <fstream>
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 < 10000; ++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: [email protected]
> To: [email protected]
> 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; -- Sloooooooow!
>
> 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
> [email protected]
> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
