Josh Rosen created SPARK-17253:
----------------------------------
Summary: Left join where ON clause does not reference the right
table produces analysis error
Key: SPARK-17253
URL: https://issues.apache.org/jira/browse/SPARK-17253
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 2.0.0
Reporter: Josh Rosen
Priority: Minor
The following query produces an AnalysisException:
{code}
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;
{code}
{code}
AnalysisException: cannot resolve '`c1.cur`' given input columns: [cur, cur1,
cur2, rate]; line 5 pos 13
{code}
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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
