Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Igor Tandetnik]

D. Richard Hipp  wrote:
> On Feb 24, 2010, at 5:45 PM, Igor Tandetnik wrote:
> 
>> D. Richard Hipp  wrote:
>>> 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.
>> 
>> Does CROSS JOIN allow an ON clause? That doesn't make much sense. I
>> guess I'm missing something obvious.
> 
> It does in SQLite.  Maybe that isn't really sensible, but SQLite does
> it anyway.  If it concerns you, simply rewrite as an INNER JOIN.

That would change the meaning of the OP's statement though. The goal is not to 
limit the resultset to rows where c1.cur=c2.cur, but to annotate such rows in a 
special way.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not necessarily a 
good idea. It is hard to be sure where they are going to land, and it could be 
dangerous sitting under them as they fly overhead. -- RFC 1925


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[D. Richard Hipp]

On Feb 24, 2010, at 5:45 PM, Igor Tandetnik wrote:

> D. Richard Hipp  wrote:
>> 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.
>
> Does CROSS JOIN allow an ON clause? That doesn't make much sense. I  
> guess I'm missing something obvious.

It does in SQLite.  Maybe that isn't really sensible, but SQLite does  
it anyway.  If it concerns you, simply rewrite as an INNER JOIN.

>
> Igor Tandetnik
>
> ___
> 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

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[ve3meo]

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"  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 
>> 
>> wrote:
>>>
>>> "ve3meo"  wrote 
>>> in
>>> message news:hm47t5$5l...@dough.gmane.org...

 "Igor Tandetnik"  wrote in
 message news:hm45gu$s5...@dough.gmane.org...
> Mark Brand  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
>> 

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Igor Tandetnik]

D. Richard Hipp  wrote:
> 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.

Does CROSS JOIN allow an ON clause? That doesn't make much sense. I guess I'm 
missing something obvious.

Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[D. Richard Hipp]

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   
> wrote:
>>
>> "ve3meo"  wrote in
>> message news:hm47t5$5l...@dough.gmane.org...
>>>
>>> "Igor Tandetnik"  wrote in
>>> message news:hm45gu$s5...@dough.gmane.org...
 Mark Brand  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

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Pavel Ivanov]

> 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  wrote:
>
> "ve3meo"  wrote in
> message news:hm47t5$5l...@dough.gmane.org...
>>
>> "Igor Tandetnik"  wrote in
>> message news:hm45gu$s5...@dough.gmane.org...
>>> Mark Brand  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

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[ve3meo]

"ve3meo"  wrote in 
message news:hm47t5$5l...@dough.gmane.org...
>
> "Igor Tandetnik"  wrote in 
> message news:hm45gu$s5...@dough.gmane.org...
>> Mark Brand  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

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[ve3meo]

"Igor Tandetnik"  wrote in message 
news:hm45gu$s5...@dough.gmane.org...
> Mark Brand  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 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Igor Tandetnik]

Mark Brand  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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Mark Brand]

> It's pretty strange how you try to join with some table not even
> mentioning any column of that table in the joining condition. I bet
> behavior is not defined for such cases in SQL standard and you're
> getting some interpretation of such query.
>   

I'm not aware of any requirement that a JOIN condition refer to the
table on the right side of the join. Anyway, you can see this effect
even if you do mention the table.

Also, here is a modified version of my example where the JOIN condition
still does not mention the joined table. This one works.

SELECT c.cur, c2.flag
FROM currency c
LEFT JOIN (SELECT 1 flag) c2
  ON c.cur='USD';

result:

EUR|
GBP|
USD|1


So far, to see this effect, I have to use both CROSS JOIN and LEFT JOIN.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together

[Pavel Ivanov]

It's pretty strange how you try to join with some table not even
mentioning any column of that table in the joining condition. I bet
behavior is not defined for such cases in SQL standard and you're
getting some interpretation of such query.

Probably this query will return what you want:

SELECT c1.cur cur1, c2.cur cur2,
CASE WHEN c1.cur = c2.cur THEN 1 ELSE x.rate END rate
FROM currency c1
CROSS JOIN currency c2
LEFT JOIN exchange x
   ON x.cur1=c1.cur
   AND x.cur2=c2.cur


Pavel

On Wed, Feb 24, 2010 at 3:36 PM, Mark Brand  wrote:
> Hi,
>
> I've run into some puzzling behavior. I've tried to distill it to a
> minimal case. In the final SELECT query below, the last LEFT JOIN clause
> seems have the effect of an INNER JOIN in that its condition limits the
> rows returned.   I can rewrite the query to get the desired result using
> a UNION or CASE, but I'm curious to understand what's going on here.
>
> This SQLite 3.6.22.
>
> regards,
>
> Mark
>
> CREATE TABLE currency (
>  cur CHAR(3),
>  PRIMARY KEY (cur)
> );
>
> CREATE TABLE exchange (
>  cur1 CHAR(3),
>  cur2 CHAR(3),
>  rate REAL,
>  PRIMARY KEY (cur1, cur2)
> );
>
> INSERT INTO currency (cur) VALUES ('EUR');
> INSERT INTO currency (cur) VALUES ('GBP');
> INSERT INTO currency (cur) VALUES ('USD');
>
> INSERT INTO exchange (cur1, cur2, rate) VALUES ('EUR', 'GBP', 0.85);
> INSERT INTO exchange (cur1, cur2, rate) VALUES ('GBP', 'EUR', 1/0.85);
>
> --Expected results
> SELECT c1.cur cur1, c2.cur cur2, x.rate
> FROM currency c1
> CROSS JOIN currency c2
> LEFT JOIN exchange x
>    ON x.cur1=c1.cur
>    AND x.cur2=c2.cur;
>
> /* results
> EUR|EUR|
> EUR|GBP|0.85
> EUR|USD|
> GBP|EUR|1.17647058823529
> GBP|GBP|
> GBP|USD|
> USD|EUR|
> USD|GBP|
> USD|USD|
> */
>
> --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
>
> */
>
> ___
> 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