On Feb 24, 2010, at 5:20 PM, Pavel Ivanov wrote: >> Does INDEX sqlite_autoindex_currency_1 contain only pointers to the >> identical currencies? > > First of all AFAIK 'PRIMARY KEY' implies uniqueness of the column. And > second: I bet if you execute 'PRAGMA case_sensitive_like = true' then > plans will be the same. > See http://www.sqlite.org/optoverview.html#like_opt for details. >
Guys: Thanks for all the discussion. But I know what is causing the problem. I would have already posted the ticket, but I'm having a little problem with Fossil right this minute and I need to debug that first... The problem is that the query optimizer is using the c1.cur=c2.cur term together with indices to limit the search to only those rows that satisfy the condition. That's the right thing to do in most cases, but not when the expression is on a LEFT JOIN but refers only to tables to the right of the LEFT JOIN. This problem has existed in SQLite forever and has never been seen before. So it is obscure. The simple fix is to put the ON clause on the CROSS JOIN where it belongs. Yes, SQLite should still do the right thing even if the ON is in the wrong place, and I'll fix that directly. Let me get Fossil running again first, though, please. Thanks for reporting the problem and for the analysis. > > Pavel > > On Wed, Feb 24, 2010 at 5:15 PM, ve3meo <[email protected]> > wrote: >> >> "ve3meo" <[email protected]> wrote in >> message news:[email protected]... >>> >>> "Igor Tandetnik" <[email protected]> wrote in >>> message news:[email protected]... >>>> Mark Brand <[email protected]> wrote: >>>>> --Gives unexpected results >>>>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate, x.rate) rate >>>>> FROM currency c1 >>>>> CROSS JOIN currency c2 >>>>> LEFT JOIN exchange x >>>>> ON x.cur1=c1.cur >>>>> AND x.cur2=c2.cur >>>>> LEFT JOIN (SELECT 1 rate) self >>>>> ON c1.cur=c2.cur; >>>>> >>>>> /* results >>>>> >>>>> EUR|EUR|1 >>>>> GBP|GBP|1 >>>>> USD|USD|1 >>>>> >>>>> */ >>>> >>>> Yes, looks like a bug to me. I see no reason why it shouldn't >>>> work. LEFT >>>> JOIN should never produce fewer rows than what left-hand-side table >>>> contains. >>>> >>>> As a workaround, try this instead: >>>> >>>> SELECT c1.cur cur1, c2.cur cur2, >>>> (case when c1.cur=c2.cur then 1 else x.rate) rate >>>> FROM currency c1 >>>> CROSS JOIN currency c2 >>>> LEFT JOIN exchange x >>>> ON x.cur1=c1.cur >>>> AND x.cur2=c2.cur; >>>> >>>> Igor Tandetnik >>> >>> One little change makes it work: >>> >>> SELECT c1.cur cur1, c2.cur cur2, COALESCE(self.rate,x.rate) rate >>> FROM currency c1 >>> CROSS JOIN currency c2 >>> LEFT JOIN exchange x >>> ON x.cur1=c1.cur >>> AND x.cur2=c2.cur >>> LEFT JOIN (SELECT 1 rate) self >>> ON c1.cur LIKE c2.cur; >>> >>> Don't ask me why '=' and 'LIKE' should behave differently in this >>> example. >>> >>> Tom >> >> They do affect the EXPLAIN QUERY PLAN results: >> >> "=" >> order from detail >> 0 0 TABLE currency AS c1 >> 1 1 TABLE currency AS c2 WITH INDEX sqlite_autoindex_currency_1 >> 2 2 TABLE exchange AS x WITH INDEX sqlite_autoindex_exchange_1 >> 3 3 TABLE AS self >> >> "LIKE" >> order from detail >> 0 0 TABLE currency AS c1 >> 1 1 TABLE currency AS c2 >> 2 2 TABLE exchange AS x WITH INDEX sqlite_autoindex_exchange_1 >> 3 3 TABLE AS self >> >> Does INDEX sqlite_autoindex_currency_1 contain only pointers to the >> identical currencies? >> >> Tom >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
