[sqlite] unexpected behavior with CROSS and LEFT JOIN together
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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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 mabr...@mabrand.nl 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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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
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 ___ 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 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 ___ 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 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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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 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
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
Re: [sqlite] unexpected behavior with CROSS and LEFT JOIN together
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 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
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 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
