[jira] [Commented] (SPARK-17348) Incorrect results from subquery transformation

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

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

Nattavut Sutyanyong commented on SPARK-17348:
---------------------------------------------

I propose to use this JIRA to just close off the incorrect result scenario as an error case. Or I could open a new JIRA for it and maintain this JIRA for a general solution of supporting subquery processing in run-time.

I also propose that we should separate the subquery to join transform from Analyzer phase to Optimizer phase. My understanding is the objective of Analyzer phase is to process any unresolved constructs from the LogicalPlan output from Parser phase to resolved LogicalPlan. Then Optimizer phase will take the resolved LogicalPlan from Analyzer and make it an optimal LogicalPlan.

> Incorrect results from subquery transformation
> ----------------------------------------------
>
>                 Key: SPARK-17348
>                 URL: https://issues.apache.org/jira/browse/SPARK-17348
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.0.0
>            Reporter: Nattavut Sutyanyong
>              Labels: correctness
>
> {noformat}
> Seq((1,1)).toDF("c1","c2").createOrReplaceTempView("t1")
> Seq((1,1),(2,0)).toDF("c1","c2").createOrReplaceTempView("t2")
> sql("select c1 from t1 where c1 in (select max(t2.c1) from t2 where t1.c2 >= t2.c2)").show
> +---+
> | c1|
> +---+
> |  1|
> +---+
> {noformat}
> The correct result of the above query should be an empty set. Here is an explanation:
> Both rows from T2 satisfies the correlated predicate T1.C2 >= T2.C2 when T1.C1 = 1 so both rows needs to be processed in the same group of the aggregation process in the subquery. The result of the aggregation yields MAX(T2.C1) as 2. Therefore, the result of the evaluation of the predicate T1.C1 (which is 1) IN MAX(T2.C1) (which is 2) should be an empty set.



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