[SPARK-45895] Combine multiple like to like all

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 11/Nov/23 12:42
• Updated: 11/Nov/23 12:42

Description

   spark.sql("create table t(a string, b string, c string) using parquet")
    spark.sql(
      """
        |select * from t where
        |substr(a, 1, 5) like '%a%' and
        |substr(a, 1, 5) like '%b%'
        |""".stripMargin).explain(true)

We can optimize the query to:

    spark.sql(
      """
        |select * from t where
        |substr(a, 1, 5) like all('%a%', '%b%')
        |""".stripMargin).explain(true)