Drafts with the same title as deleted articles in mainspace

SQL

WITH SearchResults AS (
    SELECT 
        p.page_title,
        p.page_len,
        t.old_text AS content,
        LEVENSHTEIN('pronunciation', 
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    LOWER(t.old_text),
                    'pronunciation',
                    1
                ),
                ' ',
                -1
            )
        ) as distance
    FROM page p
    JOIN revision r ON p.page_latest = r.rev_id
    JOIN text t ON r.rev_text_id = t.old_id
    WHERE p.page_namespace = 0
    AND p.page_is_redirect = 0
    AND CONVERT(t.old_text USING utf8) REGEXP '[[:space:]][[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*[[:space:]]'
    AND CONVERT(t.old_text USING utf8) NOT LIKE '%pronunciation%'
)
SELECT 
    page_title AS article_name,
    page_len AS article_length,
    CONVERT(
        SUBSTRING(
            content, 
            GREATEST(1, LOCATE(
                (
                    SELECT SUBSTRING(CONVERT(content USING utf8), 
                        REGEXP_INSTR(CONVERT(content USING utf8), '[[:space:]][[:alpha:]]*pro[[:alpha:]]*n[[:alpha:]]*n[[:alpha:]]*c[[:alpha:]]*t[[:alpha:]]*n[[:alpha:]]*[[:space:]]'),
                        50
                    )
                ),
                CONVERT(content USING utf8)
            ) - 50),
            100
        ) USING utf8
    ) AS context,
    distance AS levenshtein_distance,
    COUNT(*) OVER () as total_instances
FROM SearchResults
WHERE distance BETWEEN 1 AND 3
ORDER BY distance ASC, page_len DESC
LIMIT 10;
SQL

Checking query status...
