Speedy deletion category counts

Miscounts in {{PAGESINCATEGORY}} for the categories linked from [[Template:CSD-categories]].

COUNT(page_id) is the expected result.
COUNT(*) shows that the rows in the categorylinks table aren't being deleted when the corresponding rows in the page table are (the new bug).
cat_pages+cat_subcats+cat_files shows the old bug where the counts stored in the category table aren't reliably updated, now exacerbated by the new bug.

For [[WP:VPT#Problems with speedy deletion category counts]] circa 14 January 2022.

SQL

SELECT cl_to, COUNT(page_id), COUNT(*), cat_pages+cat_subcats+cat_files
FROM categorylinks
LEFT JOIN page ON page_id = cl_from
LEFT JOIN category ON cat_title = cl_to
WHERE cl_to IN (SELECT pl_title
                FROM pagelinks
                WHERE pl_namespace = 14
                  AND pl_from = (SELECT page_id
                                 FROM page
                                 WHERE page_namespace = 10
                                   AND page_title='CSD-categories'))
GROUP BY cl_to;

​x
 
SELECT cl_to, COUNT(page_id), COUNT(*), cat_pages+cat_subcats+cat_filesFROM categorylinksLEFT JOIN page ON page_id = cl_fromLEFT JOIN category ON cat_title = cl_toWHERE cl_to IN (SELECT pl_title                FROM pagelinks                WHERE pl_namespace = 14                  AND pl_from = (SELECT page_id                                 FROM page                                 WHERE page_namespace = 10                                   AND page_title='CSD-categories'))GROUP BY cl_to;​

SQL

Checking query status...