[SPARK-32535] Query with broadcast hints fail when query has a WITH clause

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Arvind Krishnan
• Created: 05/Aug/20 00:37
• Updated: 05/Aug/20 07:02
• Resolved: 05/Aug/20 07:02

Description

If a query has a WITH clause and a query hint (like `BROADCAST`), the query fails

In the below code sample, executing `sql2` fails, but `sql1` passes.

import spark.implicits._
val df = List(
  ("1", "B", "C"),
  ("A", "2", "C"),
  ("A", "B", "3")
).toDF("COL_A", "COL_B", "COL_C")
df.createOrReplaceTempView("table1")

val df1 = List(
  ("A", "2", "3"),
  ("1", "B", "3"),
  ("1", "2", "C")
).toDF("COL_A", "COL_B", "COL_C")
df1.createOrReplaceTempView("table2")

val sql1 = "select /*+ BROADCAST(a) */ a.COL_A from table1 a inner join table2 b on a.COL_A = b.COL_A"
val sql2 = "with X as (select /*+ BROADCAST(a) */ a.COL_A from table1 a inner join table2 b on a.COL_A = b.COL_A) select X.COL_A from X"
val df2 = spark.sql(sql2)

println(s"Row Count ${df2.count()}")
println("Rows... ")
df2.show(false)

 

I tried executing this sample program with spark2.4.0, and both sql statements work

Issue Links

• duplicates: SPARK-32237 Cannot resolve column when put hint in the views of common table expression