[SPARK-17253] Left join where ON clause does not reference the right table produces analysis error

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: 2.0.0
• Fix Version/s: 2.0.1, 2.1.0
• Component/s: SQL
• Assignee: Herman van Hövell
• Reporter: Josh Rosen
• Created: 26/Aug/16 03:03
• Updated: 07/Sep/16 08:42
• Resolved: 06/Sep/16 22:59

Description

The following query produces an AnalysisException:

CREATE TABLE currency (
 cur CHAR(3)
);

CREATE TABLE exchange (
 cur1 CHAR(3),
 cur2 CHAR(3),
 rate double
);

INSERT INTO currency VALUES ('EUR');
INSERT INTO currency VALUES ('GBP');
INSERT INTO currency VALUES ('USD');

INSERT INTO exchange VALUES ('EUR', 'GBP', 0.85);
INSERT INTO exchange VALUES ('GBP', 'EUR', 1.0/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;

AnalysisException: cannot resolve '`c1.cur`' given input columns: [cur, cur1, cur2, rate]; line 5 pos 13

However, this query is runnable in sqlite3 and postgres. This example query was adapted from https://www.sqlite.org/src/tktview?name=ebdbadade5, a sqlite bug report in which this query gave a wrong answer.

Issue Links

• duplicates: SPARK-17296 Spark SQL: cross join + two joins = BUG