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: 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; -- 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 > 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