"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
