> 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. Pavel On Wed, Feb 24, 2010 at 5:15 PM, ve3meo <holden_fam...@sympatico.ca> wrote: > > "ve3meo" <holden_fam...@sympatico.ca> wrote in > message news:hm47t5$5l...@dough.gmane.org... >> >> "Igor Tandetnik" <itandet...@mvps.org> wrote in >> message news:hm45gu$s5...@dough.gmane.org... >>> Mark Brand <mabr...@mabrand.nl> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
