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
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
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
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;
/*
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
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,
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
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
D. Richard Hipp d...@hwaci.com 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
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
On Feb 24, 2010, at 5:45 PM, Igor Tandetnik wrote:
D. Richard Hipp d...@hwaci.com 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
D. Richard Hipp d...@hwaci.com wrote:
On Feb 24, 2010, at 5:45 PM, Igor Tandetnik wrote:
D. Richard Hipp d...@hwaci.com 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
12 matches
Mail list logo
