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
