I posted the following and it didn't appear - I probably hit Reply to Sender
instead of Reply Group:
> Does INDEX sqlite_autoindex_currency_1 contain only pointers to the
> identical currencies?
>
> Tom
This little change also works:
ON +c1.cur=c2.cur;
or
ON c1.cur=+c2.cur;
Along with LIKE, that seems to hide the condition from being subject to the
query optimiser and its use of the autoindex with just the cur field in it.
That ON phrase relates to the CROSS JOIN so that effectively you have this:
SELECT c1.cur cur1, c2.cur cur2
FROM currency c1
CROSS JOIN currency c2
ON c1.cur = c2.cur;
results:
cur1 cur2
EUR EUR
GBP GBP
USD USD
Tom
Reading Richard's post below, I'm confused. Is the bug that:
a) that ON condition should not be picked up by the query optimiser as
relating to the CROSS JOIN but to the LEFT JOIN immediately ahead of it?
b) should the query optimiser use the autoindex anyway? (because of the
CROSS JOIN between c1 and c2, regardless of condition)
"D. Richard Hipp" <d...@hwaci.com> wrote in
message news:d097a35f-b1ca-4131-b11d-03c11afb1...@hwaci.com...
>
> 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
>> <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
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> _______________________________________________
> 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
