[jira] [Commented] (SPARK-18578) Full outer join in correlated subquery returns incorrect results

["Nattavut Sutyanyong (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-18578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15693478#comment-15693478 ] 

Nattavut Sutyanyong commented on SPARK-18578:
---------------------------------------------

Note that correlated predicates in the right operand of the FOJ is not a problem because it is protected in the block of code below in 

{code}
private def pullOutCorrelatedPredicates( ... )
...
        case j @ Join(_, right, jt, _) if !jt.isInstanceOf[InnerLike] =>
          failOnOuterReference(j)
          failOnOuterReferenceInSubTree(right, "a LEFT (OUTER) JOIN")
          j
{code}

However, the error message is misleading as it reports an error on a LEFT JOIN.

{code}
Accessing outer query column is not allowed in a LEFT (OUTER) JOIN: 
{code}

I propose to block the incorrect result case in the FOJ at this moment and change the misleading error message case.

> Full outer join in correlated subquery returns incorrect results
> ----------------------------------------------------------------
>
>                 Key: SPARK-18578
>                 URL: https://issues.apache.org/jira/browse/SPARK-18578
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.0.0
>            Reporter: Nattavut Sutyanyong
>
> Full outer join with a correlated predicate in the left operand in a subquery may return incorrect results.
> Example:
> {code}
> Seq(1).toDF("c1").createOrReplaceTempView("t1")
> Seq(2).toDF("c1").createOrReplaceTempView("t2")
> Seq(1).toDF("c1").createOrReplaceTempView("t3")
> // Test case: 01 EXISTS subquery context
> // Expected result: 1 row
> // Actual result:   0 row
> sql("select * from t1 where exists (select 1 from (select c1 from t2 where t1.c1 = 2) t2 full join t3 on t2.c1=t3.c1)").show
> // Test case: 02 Scalar subquery context
> // Expected result: 1 row of value 1
> // Actual result:   1 row of NULL
> // Note adding the meaningless equi-join T1.c1=T2.c1 to get through the CROSS JOIN restriction
> sql("select (select max(1) from (select c1 from t2 where t1.c1 = 2 and t1.c1=t2.c1) t2 full join t3 on t2.c1=t3.c1) from t1").show
> {code}



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