Repair the ancestors table job doesn't repair draft data

Issue Summary

Repair the ancestors job compares CONFANCESTORS and CONTENT to check that its' broken, however its target is only "current" pages. Therefore the job doesn't repair if affected data is in draft.

Steps to Reproduce

1. Create a top level page.
2. Create a draft of a page below the page created in step above
3. You will see CONFANCESTORS table as below

   confluence=# select * from confancestors;
    descendentid | ancestorposition | ancestorid
   --------------+------------------+------------
          327703 |                0 |     327683                   <-- Top level page
          327709 |                0 |     327683                   <-- Draft page 
          327709 |                1 |     327703                    <-- Draft page
   

4. Break the data

   update confancestors set ancestorid=327683 where descendentid=327709;
   

   You will see the sql result like this

   confluence=# select * from confancestors;
    descendentid | ancestorposition | ancestorid
   --------------+------------------+------------
          327703 |                0 |     327683                   <-- Top level page
          327709 |                0 |     327683                   <-- Draft page 
          327709 |                1 |     327683                    <-- Draft page
   

5. Run repair ancestors scheduled job.

Expected Results

Repaired as below

confluence=# select * from confancestors;
 descendentid | ancestorposition | ancestorid
--------------+------------------+------------
       327703 |                0 |     327683                   <-- Top level page
       327709 |                0 |     327683                   <-- Draft page 
       327709 |                1 |     327703                    <-- Draft page

Actual Results

Not repaired.

Note

The query to check if repair is needed introduced in Rebuilding the Ancestor Table(6.13 or earlier) returns draft data as well. So it should be included.

SELECT A.ANCESTORID, A.DESCENDENTID, COUNT(A.DESCENDENTID)
FROM CONFANCESTORS A, CONTENT B
WHERE B.CONTENTID = A.DESCENDENTID
GROUP BY A.ANCESTORID, A.DESCENDENTID HAVING COUNT(A.DESCENDENTID) != 1
ORDER BY 3 DESC

Workaround 1

1. Find out the affected rows by following query.

   SELECT 'https://<base-url>/pages/viewpage.action?pageId=' || 
    A.DESCENDENTID
   FROM CONFANCESTORS A, CONTENT B
   WHERE B.CONTENTID = A.DESCENDENTID
   GROUP BY A.ANCESTORID, A.DESCENDENTID HAVING COUNT(A.DESCENDENTID) != 1
   

2. It will returns the affected draft pages, so open the url in the browser, and publish them all.
3. Run repair the ancestors job

Workaround 2

1. Run the following query to determine page name, contentid and creator:

   WITH draft_duplicates as (SELECT D.ANCESTORID, D.DESCENDENTID
   FROM CONFANCESTORS D, CONTENT E
   WHERE E.CONTENTID = D.DESCENDENTID
   AND E.CONTENT_STATUS = 'draft'
   GROUP BY D.ANCESTORID, D.DESCENDENTID HAVING COUNT(D.DESCENDENTID) != 1)
   
   SELECT C.username, A.title, A.CONTENTID
   FROM draft_duplicates as B
   INNER JOIN CONTENT A ON B.DESCENDENTID = A.CONTENTID
   INNER JOIN USER_MAPPING C ON A.creator = C.user_key;
   

2. Have the creator delete the draft or publish it.