SQLite

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);

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;

The output consists of only three rows -- only rows for which the right table of the second LEFT JOIN is non-null. Nine rows should be returned.

This is a long-standing bug in SQLite. The same incorrect answer is returned by all versions from 3.6.22 going back to 3.1.3 (2005-02-19).

The work-around is simple. Move the ON clause up to the CROSS JOIN where it belongs. But the problem should still be fixed.