Answering 1) & 2) (others already pointed out No.1 in the comments, added additional reference):

• 1. Why didn't broadcast join work for the left table?

  • The left table should be the bigger one, as others already suggested in the comments. Based on v3.2 codebase, canSplitRightSide() does not support LeftOuter: adaptive/OptimizeSkewedJoin.scala#L89

    • For reference, LeftOuter is supported by canSplitLeftSide(): adaptive/OptimizeSkewedJoin.scala#L84

• 2) Why didn't AQE detect and optimize the skewed join?

  • You can also try increasing config "spark.sql.autoBroadcastJoinThreshold" to allow your dataset size. Default is 10 MB (documentation) which could be another reason why Spark ignored the broadcast hint.