[SPARK-33861] Simplify conditional in predicate

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 3.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 20/Dec/20 15:54
• Updated: 20/Sep/22 05:23
• Resolved: 03/Sep/22 08:53

Description

The use case is:

spark.sql("create table t1 using parquet as select id as a, id as b from range(10)")
spark.sql("select * from t1 where CASE WHEN a > 2 THEN b + 10 END > 5").explain()

Before this pr:

== Physical Plan ==
*(1) Filter CASE WHEN (a#3L > 2) THEN ((b#4L + 10) > 5) END
+- *(1) ColumnarToRow
   +- FileScan parquet default.t1[a#3L,b#4L] Batched: true, DataFilters: [CASE WHEN (a#3L > 2) THEN ((b#4L + 10) > 5) END], Format: Parquet, Location: InMemoryFileIndex[file:/Users/yumwang/opensource/spark/spark-warehouse/org.apache.spark.sql.DataF..., PartitionFilters: [], PushedFilters: [], ReadSchema: struct<a:bigint,b:bigint>

After this pr:

== Physical Plan ==
*(1) Filter (((isnotnull(a#3L) AND isnotnull(b#4L)) AND (a#3L > 2)) AND ((b#4L + 10) > 5))
+- *(1) ColumnarToRow
   +- FileScan parquet default.t1[a#3L,b#4L] Batched: true, DataFilters: [isnotnull(a#3L), isnotnull(b#4L), (a#3L > 2), ((b#4L + 10) > 5)], Format: Parquet, Location: InMemoryFileIndex[file:/Users/yumwang/opensource/spark/spark-warehouse/org.apache.spark.sql.DataF..., PartitionFilters: [], PushedFilters: [IsNotNull(a), IsNotNull(b), GreaterThan(a,2)], ReadSchema: struct<a:bigint,b:bigint>

Issue Links

• causes: SPARK-40493 Revert "[SPARK-33861][SQL] Simplify conditional in predicate"
• links to: [Github] Pull Request #30865 (wangyum)
• links to: [Github] Pull Request #30865 (wangyum)
• links to: [Github] Pull Request #31067 (wangyum)
• links to: [Github] Pull Request #37729 (wangyum)
• links to: [Github] Pull Request #37729 (wangyum)