with disamb_page as (

  select p1.page_title as rdt_title, p2.page_title as rdf_title from page p1

  join redirect on rd_title = p1.page_title and rd_namespace = 0

  join page p2 on p2.page_id = rd_from and p2.page_namespace = 0 and p2.page_is_redirect = 1

  where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')

  and p1.page_is_redirect = 0 and p1.page_namespace = 0

)

​

select dp1.rdt_title from disamb_page dp1, disamb_page dp2

where dp1.rdt_title like '%\_(disambiguation)'

and dp2.rdt_title = substring(dp1.rdt_title, 1, INSTR(dp1.rdt_title, '_(disambiguation)')-1)

​

/*

select p1.page_title, p2.page_title from page p1

join page p2 on p2.page_title = substring(p1.page_title, 1, INSTR(p1.page_title, '_(disambiguation)')-1)

and p2.page_namespace = 0 and p2.page_is_redirect = 0

join redirect on rd_from = p1.page_id and rd_namespace = 0 and rd_title != p2.page_title

join page p3 on p3.page_title = rd_title and p3.page_namespace = 0

where p1.page_title like '%\_(disambiguation)' and p1.page_namespace = 0 and p1.page_is_redirect = 1

-- and p1.page_id not in (select rd_from from redirect where rd_title = p2.page_title and rd_namespace = 0)

and p2.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')

and p3.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')

*/

​

/*

(select p1.page_title as dup_disamb, p2.page_title as base_disamb from page p1

 join page p2 on p2.page_title = concat(substring(p1.page_title, 1, INSTR(p1.page_title, '_(')-1), '_(disambiguation)')

 and p2.page_namespace = 0 and p2.page_is_redirect = 0

 where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')

 and p1.page_namespace = 0 and p1.page_is_redirect = 0

 and p1.page_title like '%\_(%)' and p1.page_title not like '%\_(disambiguation)')

union

(select p1.page_title as dup_disamb, rd_title as base_disamb from page p1

 join page p2 on p2.page_title = concat(substring(p1.page_title, 1, INSTR(p1.page_title, '_(')-1), '_(disambiguation)')

 and p2.page_namespace = 0 and p2.page_is_redirect = 1

 join redirect on rd_from = p2.page_id and rd_namespace = 0

 where p1.page_id in (select cl_from from categorylinks where cl_to = 'All_disambiguation_pages' and cl_type = 'page')

 and p1.page_namespace = 0 and p1.page_is_redirect = 0

 and p1.page_title like '%\_(%)' and p1.page_title not like '%\_(disambiguation)')

 */