[FEA] Support left join(SMJ) when lower(String) and in are a part of the join condition on a single side of the join

[viadea]

I wish we can support AST in a left join(SMJ) when lower(String)

Env:
24.08 snapshot jar

Mini repro:

import org.apache.spark.sql.Row
import org.apache.spark.sql.types._
import spark.sqlContext.implicits._
import org.apache.spark.sql.functions._

val data = Seq(
    Row(Row("Adam ","","Green"),"1","M",1000,"2024-01-01"),
    Row(Row("Bob ","Middle","Green"),"2","M",2000,"2024-12-12"),
    Row(Row("Cathy ","","Green"),"3","F",3000,"2022-03-04")
)

val schema = (new StructType()
  .add("name",new StructType()
    .add("firstname",StringType)
    .add("middlename",StringType)
    .add("lastname",StringType)) 
  .add("id",StringType)
  .add("gender",StringType)
  .add("salary",IntegerType)
  .add("birthday_str",StringType))

val df = spark.createDataFrame(spark.sparkContext.parallelize(data),schema).withColumn("birthday_dt",current_date().as("dt"))
df.printSchema

df.write.format("parquet").mode("overwrite").save("/tmp/testparquet")
spark.read.parquet("/tmp/testparquet").createOrReplaceTempView("df")

spark.conf.set("spark.sql.autoBroadcastJoinThreshold","1")

val query="""
select count(*) as cnt 
from df a left join df b 
on lower(a.id) in ('1','2')
and a.name=b.name

"""

spark.sql(query).collect

Not-supported messages:

      !Exec <SortMergeJoinExec> cannot run on GPU because not all expressions can be replaced
        @Expression <AttributeReference> name#321 could run on GPU
        @Expression <AttributeReference> name#334 could run on GPU
        !Expression <In> lower(id#322) IN (1,2) cannot run on GPU because AST is required and this expression does not support AST
          !Expression <Lower> lower(id#322) cannot run on GPU because AST is required and this expression does not support AST
            @Expression <AttributeReference> id#322 could run on GPU

[revans2]

@viadea I am confused by this. lower is only part of the problem with the query you put up. Are you just asking about supporting lower in a non-equi-join case? Or do you want/need to support in also?

In either case we should be able to do this with changes based on #9759, but we would need to be sure to support it for all join cases, not just broadcast.

However that does not mean that we would support lower or in as a part of AST. It just means that we could support the example query you put up. Is that good enough?

[viadea]

@revans2 I am not sure if the title is accurate since I do not know why we have to fallback for such query.
The ask is to make above sample query run on GPU.
TBH, I am not sure it is because of the in or the lower or both, since from the CPU fallback messages, both of them showed there.
Again, as long as the example join query can run on GPU, it is the goal of this FEA.

[revans2]

as long as the example join query can run on GPU, it is the goal of this FEA.

Sounds good we can make that work.