You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Yuming Wang (Jira)" <ji...@apache.org> on 2021/07/26 01:41:00 UTC
[jira] [Commented] (SPARK-31809) Infer IsNotNull for non null
intolerant child of null intolerant in join condition
[ https://issues.apache.org/jira/browse/SPARK-31809?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17386991#comment-17386991 ]
Yuming Wang commented on SPARK-31809:
-------------------------------------
{code:java}
spark.sql("create table t1 using parquet select cast(id as string) as a, cast(id as string) as b from range(1)")
spark.sql("create table t2 using parquet select cast(id as string) as x from range(1)")
spark.sql("set spark.sql.autoBroadcastJoinThreshold=-1")
spark.sql("SELECT t1.* FROM t1 JOIN t2 ON coalesce(t1.a, t1.b)=t2.x").show
{code}
The {{Coalesce}} evaluation:
{noformat}
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.Pmod_0$(Unknown Source)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.apply(Unknown Source)
at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$6(ShuffleExchangeExec.scala:311)
at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$6$adapted(ShuffleExchangeExec.scala:311)
at org.apache.spark.sql.execution.exchange.ShuffleExchangeExec$.$anonfun$prepareShuffleDependency$14(ShuffleExchangeExec.scala:380)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$SpecificUnsafeProjection.apply(Unknown Source)
at org.apache.spark.sql.execution.SortExec$$anon$1.computePrefix(SortExec.scala:90)
at org.apache.spark.sql.execution.UnsafeExternalRowSorter.insertRow(UnsafeExternalRowSorter.java:137)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage3.sort_addToSorter_0$(Unknown Source)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage3.processNext(Unknown Source)
at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:759)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.smj_findNextJoinRows_0$(Unknown Source)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.smj_findNextJoinRows_0$(Unknown Source)
at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage5.processNext(Unknown Source)
at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43)
at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$2.hasNext(WholeStageCodegenExec.scala:778)
{noformat}
> Infer IsNotNull for non null intolerant child of null intolerant in join condition
> ----------------------------------------------------------------------------------
>
> Key: SPARK-31809
> URL: https://issues.apache.org/jira/browse/SPARK-31809
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 3.1.0
> Reporter: Yuming Wang
> Assignee: Yuming Wang
> Priority: Major
> Attachments: default.png, infer.png
>
>
> We should infer {{IsNotNull}} for all children of {{NullIntolerant}} expressions. For example:
> {code:sql}
> CREATE TABLE t1(c1 string, c2 string);
> CREATE TABLE t2(c1 string, c2 string);
> EXPLAIN SELECT t1.* FROM t1 JOIN t2 ON coalesce(t1.c1, t1.c2)=t2.c1;
> {code}
> {noformat}
> == Physical Plan ==
> *(4) Project [c1#5, c2#6]
> +- *(4) SortMergeJoin [coalesce(c1#5, c2#6)], [c1#7], Inner
> :- *(1) Sort [coalesce(c1#5, c2#6) ASC NULLS FIRST], false, 0
> : +- Exchange hashpartitioning(coalesce(c1#5, c2#6), 200), true, [id=#33]
> : +- Scan hive default.t1 [c1#5, c2#6], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c1#5, c2#6], Statistics(sizeInBytes=8.0 EiB)
> +- *(3) Sort [c1#7 ASC NULLS FIRST], false, 0
> +- Exchange hashpartitioning(c1#7, 200), true, [id=#46]
> +- *(2) Filter isnotnull(c1#7)
> +- Scan hive default.t2 [c1#7], HiveTableRelation `default`.`t2`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c1#7, c2#8], Statistics(sizeInBytes=8.0 EiB)
> {noformat}
> We should infer {{coalesce(t1.c1, t1.c2) IS NOT NULL}} to improve query performance:
> {noformat}
> == Physical Plan ==
> *(5) Project [c1#23, c2#24]
> +- *(5) SortMergeJoin [coalesce(c1#23, c2#24)], [c1#25], Inner
> :- *(2) Sort [coalesce(c1#23, c2#24) ASC NULLS FIRST], false, 0
> : +- Exchange hashpartitioning(coalesce(c1#23, c2#24), 200), true, [id=#95]
> : +- *(1) Filter isnotnull(coalesce(c1#23, c2#24))
> : +- Scan hive default.t1 [c1#23, c2#24], HiveTableRelation `default`.`t1`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c1#23, c2#24], Statistics(sizeInBytes=8.0 EiB)
> +- *(4) Sort [c1#25 ASC NULLS FIRST], false, 0
> +- Exchange hashpartitioning(c1#25, 200), true, [id=#103]
> +- *(3) Filter isnotnull(c1#25)
> +- Scan hive default.t2 [c1#25], HiveTableRelation `default`.`t2`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, [c1#25, c2#26], Statistics(sizeInBytes=8.0 EiB)
> {noformat}
> Real performance test case:
> !default.png! !infer.png!
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org