PAGESINCATEGORY miscount (analysis)

This query is marked as a draft This query has been published by Cryptic.

Pages whose rows in categorylinks for [[CAT:G12]] weren't deleted when the pages were.

This causes a miscount in {{PAGESINCATEGORY}} for the categories they're in, in such a way that purging the category page - the usual solution - doesn't fix it.  (Speedy deletion-related categories are particularly prone to such errors.) The problem is that rows in categorylinks tying the old page id to the category remain, and the recount triggered by purging doesn't check to see if those pages still exist. Restoring then re-deleting the page usually does fix it, though only if no other revisions have been placed at that page title in the meantime (which assigns a new page_id).

(Extended results) for [[WP:VPT#Resetting the page count in CAT:DBHOAX]] circa 18 November 2022.

SQL

-- query 1: identify problematic page
SET @cat = REPLACE('Candidates for speedy deletion as copyright violations', ' ', '_');
SELECT DISTINCT log_page, log_namespace, log_title
FROM logging_logindex
JOIN categorylinks ON cl_from = log_page
LEFT JOIN page ON page_id = log_page
JOIN archive ON ar_namespace = log_namespace AND ar_title = log_title AND ar_page_id = log_page -- this filters out redirects left by moves
WHERE cl_to = @cat
  AND page_id IS NULL;

-- queries 2-5: pre-fix status
SELECT * FROM category WHERE cat_title = @cat;
SELECT * FROM categorylinks WHERE cl_to = @cat;
SELECT * FROM page WHERE page_id = 72260446;
SELECT * FROM categorylinks WHERE cl_from = 72260446;

-- query 6: proper recount
SELECT COUNT(*)
FROM categorylinks
JOIN page ON page_id = cl_from -- missing from the recount caused by purging
WHERE cl_to = @cat;

​x
 
-- query 1: identify problematic pageSET @cat = REPLACE('Candidates for speedy deletion as copyright violations', ' ', '_');SELECT DISTINCT log_page, log_namespace, log_titleFROM logging_logindexJOIN categorylinks ON cl_from = log_pageLEFT JOIN page ON page_id = log_pageJOIN archive ON ar_namespace = log_namespace AND ar_title = log_title AND ar_page_id = log_page -- this filters out redirects left by movesWHERE cl_to = @cat  AND page_id IS NULL;​-- queries 2-5: pre-fix statusSELECT * FROM category WHERE cat_title = @cat;SELECT * FROM categorylinks WHERE cl_to = @cat;SELECT * FROM page WHERE page_id = 72260446;SELECT * FROM categorylinks WHERE cl_from = 72260446;​-- query 6: proper recountSELECT COUNT(*)FROM categorylinksJOIN page ON page_id = cl_from -- missing from the recount caused by purgingWHERE cl_to = @cat;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...