AngersZhuuuu commented on issue #26406: [WIP][SPARK-29145][SQL][FOLLOW-UP] Port in-subquery-on-join-condition.sql URL: https://github.com/apache/spark/pull/26406#issuecomment-550159730 ``` create temporary view s1 as select * from values (1), (3), (5), (7), (9) as s1(id); create temporary view s2 as select * from values (1), (3), (4), (6), (9) as s2(id); create temporary view s3 as select * from values (3), (4), (6), (9) as s3(id); explain extended SELECT s1.id, s2.id as id2 FROM s1 LEFT OUTER JOIN s2 ON s1.id = s2.id AND EXISTS (SELECT * FROM s3 WHERE s3.id > 6) ``` LogicalPlan ``` == Parsed Logical Plan == 'Project ['s1.id, 's2.id AS id2#291] +- 'Join LeftOuter, (('s1.id = 's2.id) AND exists#290 []) : +- 'Project [*] : +- 'Filter ('s3.id > 6) : +- 'UnresolvedRelation [s3] :- 'UnresolvedRelation [s1] +- 'UnresolvedRelation [s2] == Analyzed Logical Plan == id: int, id2: int Project [id#244, id#250 AS id2#291] +- Join LeftOuter, ((id#244 = id#250) AND exists#290 []) : +- Project [id#256] : +- Filter (id#256 > 6) : +- SubqueryAlias `s3` : +- Project [value#253 AS id#256] : +- LocalRelation [value#253] :- SubqueryAlias `s1` : +- Project [value#241 AS id#244] : +- LocalRelation [value#241] +- SubqueryAlias `s2` +- Project [value#247 AS id#250] +- LocalRelation [value#247] == Optimized Logical Plan == Project [id#244, id#250 AS id2#291] +- Join LeftOuter, (exists#290 [] AND (id#244 = id#250)) : +- Project [value#253 AS id#256] : +- Filter (value#253 > 6) : +- LocalRelation [value#253] :- Project [value#241 AS id#244] : +- LocalRelation [value#241] +- Project [value#247 AS id#250] +- LocalRelation [value#247] == Physical Plan == *(2) Project [id#244, id#250 AS id2#291] +- *(2) BroadcastHashJoin [id#244], [id#250], LeftOuter, BuildRight, exists#290 [] : +- Project [value#253 AS id#256] : +- Filter (value#253 > 6) : +- LocalRelation [value#253] :- *(2) Project [value#241 AS id#244] : +- *(2) LocalTableScan [value#241] +- BroadcastExchange HashedRelationBroadcastMode(List(cast(input[0, int, false] as bigint))), [id=#670] +- *(1) Project [value#247 AS id#250] +- *(1) LocalTableScan [value#247] ``` Exist can't be change to other form of the join, and `exists` 's child plan was not change to Physical Plan in the whole Physic plan. ``` +- *(2) BroadcastHashJoin [id#244], [id#250], LeftOuter, BuildRight, exists#290 [] : +- Project [value#253 AS id#256] : +- Filter (value#253 > 6) : +- LocalRelation [value#253] ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
