Description
An example to reproduce the bug.
create table t1 as select 1 c1, 2 c2; create table t2 as select 1 c1, 2 c2; create table t3 as select 1 c1, 2 c2; set spark.sql.adaptive.autoBroadcastJoinThreshold=-1; select /*+ merge(t3) */ * from t1 left join ( select c1 as c from t3 ) t3 on t1.c1 = t3.c left join ( select /*+ repartition(c1) */ c1 from t2 ) t2 on t1.c1 = t2.c1;
The key to produce this bug is that a bhj convert to smj/shj without introducing extra shuffe and AQE does not think the join can be planned as bhj.