title: Spark SQL error cannot evaluate expression, worked in 2.3.2 and fails in 2.4
body: <p>I just posted an issue, I hope I did not over-step the protocol! </p>
  
  <p><a href="https://issues.apache.org/jira/browse/SPARK-26777" rel="nofollow noreferrer">https://issues.apache.org/jira/browse/SPARK-26777</a></p>
  
  <p>I wonder if anyone has hit a problem with SQL Spark 2.4.0 (from Pyspark 3.6)</p>
  
  <pre><code>spark.sql("select partition_year_utc,partition_month_utc,partition_day_utc \
      from datalake_reporting.copy_of_leads_notification \
      where partition_year_utc = (select max(partition_year_utc) from datalake_reporting.copy_of_leads_notification) \
      and partition_month_utc = \
      (select max(partition_month_utc) from datalake_reporting.copy_of_leads_notification as m \
      where \
      m.partition_year_utc = (select max(partition_year_utc) from datalake_reporting.copy_of_leads_notification)) \
      and partition_day_utc = (select max(d.partition_day_utc) from datalake_reporting.copy_of_leads_notification as d \
      where d.partition_month_utc = \
      (select max(m1.partition_month_utc) from datalake_reporting.copy_of_leads_notification as m1 \
      where m1.partition_year_utc = \
      (select max(y.partition_year_utc) from datalake_reporting.copy_of_leads_notification as y) \
      ) \
      ) \
      order by 1 desc, 2 desc, 3 desc limit 1 ").show(1,False)
  </code></pre>
  
  <p>Above PySpark/SQL code works in Presto/Athena and it used to work in Spark 2.3.2 as well.</p>
  
  <p>Now in the latest <strong>Spark 2.4.0 AWS EMR 5.20.0</strong> it fails with <strong>error</strong> (query syntax):</p>
  
  <blockquote>
    <p>py4j.protocol.Py4JJavaError: An error occurred while calling
    o1326.showString. : java.lang.UnsupportedOperationException: Cannot
    evaluate expression: scalar-subquery#4495 []</p>
  </blockquote>
  
  <p>I submitted an issue in Spark but I also wondering if someone knows about it already?</p>
  
  <p>I could re-write this SQL code to break it up into multiple (3-4) simple SQL statements but thought to post it here for opinions as it is rather trivial code.
  Thank you!</p>