Category ancestors cycles

SQL

/*
select count(*) from category where cat_subcats = 0;

select count(distinct cl_to) from categorylinks
join page on cl_from = page_id and page_namespace = 14
where page_title in (select cat_title from category where cat_subcats = 0);
*/

/*
with category_2 as (
  select distinct cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  where page_title in (select cat_title from category where cat_subcats = 0)
),
category_3 as (
  select distinct cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  where page_title in (select cl_to from category_2)
)

select count(cat_title) from category where cat_subcats > 0
and cat_title not in (select cl_to from category_2)
and cat_title not in (select cl_to from category_3)
*/

/*
select page_title, category.* from categorylinks
join page on cl_from = page_id and page_namespace = 14
join category on cat_title = cl_to and cat_subcats = 0
-- where cl_to in (select cat_title from category where cat_subcats = 0)
*/

/*
select count(*) from categorylinks
join page on cl_from = page_id and page_namespace = 14;
*/

set max_recursive_iterations=2;

with recursive ancestors (an_from, an_to, path, is_cycle) as (
  select page_title, cl_to, CONCAT(page_title, ',', cl_to), FIND_IN_SET(cl_to, page_title) from subcategories
  -- join category on page_title = cat_title and cat_subcats = 0
  union all
  select an_to, cl_to, CONCAT(path, ',', cl_to), FIND_IN_SET(cl_to, path) from subcategories
  join ancestors on page_title = an_to and not is_cycle
),
subcategories as (
  select replace(page_title, ',', '|') as page_title, replace(cl_to, ',', '|') as cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  where page_title not in (
    "Noindexed_pages", "Hidden_categories", "Tracking_categories", "Wikipedia_template_categories",
    "Container_categories", "Wikipedia_categories", "Commons_category_link_is_on_Wikidata"
  )
)

select * from ancestors where is_cycle;

/*
with recursive ancestors (an_from, an_to, path) as (
  select cat_title, cl_to, CONCAT(cat_title, ',', cl_to) from subcategories
  join category on page_title = cat_title and cat_subcats = 0
  union distinct
  select an_to, cl_to, CONCAT(an_to, ',', cl_to) from subcategories
  join ancestors on page_title = an_to
  union distinct
  select an_from, cl_to, CONCAT(path, ',', cl_to) from subcategories
  join ancestors on page_title = an_to
),
subcategories as (
  select page_title, cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  limit 100
)

select * from ancestors;
*/

-- select count(cat_title) from category where cat_subcats = 0 and rand() < 0.1;
SQL

Checking query status...
