Easy SQL mistakes: Accidental Correlated Subquery

Just a fun note, because it was a moment of pure puzzlement for me. I’m writing a new SP for Minion Backup (you knew it had to be Minion related, didn’t you?) and I just got the oddest error. Here’s how it went.

First, I got a list of backups from the log:

SELECT ExecutionDateTime
    , Status
    , PctComplete
    , DBName
    , DBType
    , BackupType
    , BackupStartDateTime
    , BackupEndDateTime
 FROM Minion.BackupLogDetails;

But I wanted to limit the resulting list based on an existing temporary table, so I joined them, adding simple table aliases as I went (“D” and “B”):

SELECT D.ExecutionDateTime
    , D.Status
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B ON D.DBName = B.DBName;

But the important thing was, I wanted this new result set to be of backups PRIOR TO the most recent backup set. That’s easy, I’ll just get the max date from BackupLogDetails, and pull a less than:

SELECT D.ExecutionDateTime
    , D.Status
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B ON D.DBName = B.DBName
 WHERE D.ExecutionDateTime < ( SELECT   MAX(D.ExecutionDateTime)
    FROM Minion.BackupLogDetails
 );

Aaaaand then I get the weird error:

Msg 147, Level 15, State 1, Line 50
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

What the what?? I literally JUST ran a query exactly like this, but without the join. I haven’t mixed aggregate and non-aggregate columns in the query without a GROUP BY…the only aggregate is in the subquery, and it’s all by its little lonesome!

Well, you likely read the blog title, so it’s no mystery to you: by adding that table alias, “D.”, to the subquery, I accidentally made this a correlated subquery…the thing was now trying to pull MAX(ExecutionDateTime) from that outer table, instead of the inner table.  That’s not at ALL what I wanted to do!

The fix here is easy – just take out that table alias from the subquery:

  SELECT D.ExecutionDateTime
    , D.STATUS
    , D.PctComplete
    , D.DBName
    , D.DBType
    , D.BackupType
    , D.BackupStartDateTime
    , D.BackupEndDateTime
 FROM Minion.BackupLogDetails as D
 JOIN #Backups as B
 ON D.DBName = B.DBName
 WHERE D.ExecutionDateTime < ( SELECT MAX(ExecutionDateTime)
      FROM Minion.BackupLogDetails );

If you wanted to be really really pedantic, you could of course add a new table alias to the subquery table – say, “sub” – and then select MAX(sub.ExecutionDateTime), etc etc. It’s all up to you. Just, you know, don’t accidentally correlate when you didn’t meant to!

Happy days,
Jen McCown
http://www.midnightdba.com/Jen/