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

["Herman van Hovell (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-17253?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Herman van Hovell updated SPARK-17253:
--------------------------------------
    Fix Version/s: 2.0.1

> 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
>            Assignee: Herman van Hovell
>            Priority: Minor
>             Fix For: 2.0.1, 2.1.0
>
>
> 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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org