EPiServer FindPagesWithCriteria fails when PageArchiveLink has circular references

I got an SQL exception when using FindPagesWithCriteria with the PageArchiveLink property included in a criteria. After some initial debugging I got the following exception message:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression".

Debugging the stored procedure

 The CriteriaCollection was built the ordinary way:
new PropertyCriteria {  Name = "PageArchiveLink"....}

The problem was obviously located on the database level, so I used the the SQL Server Profiler to get the exact database call.

The PageArchiveLink property has special handling in EPiServers FindPagesWithCriteria stored procedure, and after a while I isolated the error to:
SELECT tblPageLanguage.fkPageID FROM tblPageLanguage
INNER JOIN tblTree ON tblTree.fkChildID=tblPageLanguage.fkPageID
INNER JOIN tblPage ON tblPage.pkID=tblPageLanguage.fkPageID
WHERE tblTree.fkParentID=@PageID AND ((SELECT pkID FROM tblPage WHERE PageGUID = tblPage.ArchivePageGUID)<>@PageLink) OR (@PageLink IS NULL AND NOT tblPage.ArchivePageGUID IS NULL)

In my case I had several pages with PageArchiveLink set to self references (i.e. the page is set to be archived under itself) and that caused the SQL exception. You get the SQL exception when you are i.e. are comparing [1,2] to 1.

The solution

To find PageArchiveLink self references, run this query:
SELECT pkID FROM tblPage WHERE PageGUID = tblPage.ArchivePageGUID

To avoid this, EPiServer could implement a check to disallow self references in the PageArchiveLink property when a page is saved.

This problem was encountered in EPiServer version CMS 6 R2 (6.1.379.501).