title: What does "Correlated scalar subqueries must be Aggregated" mean?
body: <p>I use Spark 2.0.</p>
  
  <p>I'd like to execute the following SQL query:</p>
  
  <pre><code>val sqlText = """
  select
    f.ID as TID,
    f.BldgID as TBldgID,
    f.LeaseID as TLeaseID,
    f.Period as TPeriod,
    coalesce(
      (select
         f ChargeAmt
       from
         Fact_CMCharges f
       where
         f.BldgID = Fact_CMCharges.BldgID
       limit 1),
       0) as TChargeAmt1,
    f.ChargeAmt as TChargeAmt2,
    l.EFFDATE as TBreakDate
  from
    Fact_CMCharges f
  join
    CMRECC l on l.BLDGID = f.BldgID and l.LEASID = f.LeaseID and l.INCCAT = f.IncomeCat and date_format(l.EFFDATE,'D')&lt;&gt;1 and f.Period=EFFDateInt(l.EFFDATE) 
  where
    f.ActualProjected = 'Lease'
  except(
    select * from TT1 t2 left semi join Fact_CMCharges f2 on t2.TID=f2.ID) 
  """
  val query = spark.sql(sqlText)
  query.show()
  </code></pre>
  
  <p>It seems that the inner statement in <code>coalesce</code> gives the following error:</p>
  
  <pre><code>pyspark.sql.utils.AnalysisException: u'Correlated scalar subqueries must be Aggregated: GlobalLimit 1\n+- LocalLimit 1\n
  </code></pre>
  
  <p>What's wrong with the query?</p>