Editing Eurovision Wiki:Village pump (technical) (section)

== Quarry SQL issue ==

I'm trying to run a Quarry search for pjge titles that mix [[Devanagari]] with other scripts, but I can't figure out why it doesn't match anything. In particular, it should have found the deletion log entry for [[भोजपुरी language]]. [https://quarry.wmcloud.org/query/103272 Replacing with a different regex] seems to work, but prepending "\b" fails.
{{collapse top|title=SQL Query}}
<syntaxhighlight lang="SQL">
SET @pat1='\P{L}*\p{Devanagari}.*[^\p{Devanagari}\P{L}].*';
SET @pat2='\P{L}*[^\p{Devanagari}\P{L}].*\p{Devanagari}.*';

WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,
  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),
  (11, 'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,
  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),
  (828, 'Module:'), (829, 'Module talk:'))
SELECT CONCAT('[[',
              COALESCE(ns_s, CONCAT('{{ns:', page_namespace, '}}:')),
              REPLACE(page_title, '_', ' '),
              ']]') AS title,
       EXISTS (SELECT 1
               FROM page p2
               WHERE p2.page_namespace = sq.page_namespace
                 AND p2.page_title = sq.page_title) AS existing,
       COUNT(log_namespace) AS `times deleted`,
       GROUP_CONCAT(log_timestamp SEPARATOR ' // ') AS `deletion timestamps`,
       GROUP_CONCAT(comment_text SEPARATOR ' // ') AS `deletion comments`,
       (SELECT GROUP_CONCAT(comment_text SEPARATOR ' // ')
        FROM logging_logindex
        JOIN comment_logging ON comment_id = log_comment_id
        WHERE log_namespace = page_namespace
          AND log_title = page_title
          AND log_type = 'protect'
          AND log_action = 'protect'
          AND log_params LIKE '%create=%') AS `salt comments`
FROM (SELECT page_namespace, page_title
      FROM page
      WHERE (REPLACE(page_title, '_', ' ') RLIKE @pat1
             OR REPLACE(page_title, '_', ' ') RLIKE @pat2)
         AND page_namespace NOT IN (1,2,3,4,5,6,7)
      
      UNION DISTINCT

      SELECT ar_namespace, ar_title
      FROM archive
      WHERE (REPLACE(ar_title, '_', ' ') RLIKE @pat1
             OR REPLACE(ar_title, '_', ' ') RLIKE @pat2)
         AND ar_namespace NOT IN (1,2,3,4,5,6,7)) sq
LEFT JOIN logging_logindex
       ON log_namespace = page_namespace
      AND log_title = page_title
      AND log_action = 'delete'
      AND log_type = 'delete'
LEFT JOIN comment_logging ON comment_id = log_comment_id
LEFT JOIN ns ON ns_n = page_namespace
GROUP BY page_namespace, page_title
ORDER BY `existing` DESC, page_namespace ASC, page_title ASC;
</syntaxhighlight>
{{collapse bottom}} –[[User:LaundryPizza03|<b style="color:#77b">Laundry</b><b style="color:#fb0">Pizza</b><b style="color:#b00">03</b>]] ([[User talk:LaundryPizza03|<span style="color:#0d0">d</span>]][[Special:Contribs/LaundryPizza03|<span style="color:#0bf">c̄</span>]]) 05:12, 17 March 2026 (UTC)
:Your backslashes aren't escaped. —[[User:Cryptic|Cryptic]] 05:24, 17 March 2026 (UTC)
::Fixing this caused a lengthy delay and a replication lag, and still returned no results. Do I need to escape any characters other than the backslash? –[[User:LaundryPizza03|<b style="color:#77b">Laundry</b><b style="color:#fb0">Pizza</b><b style="color:#b00">03</b>]] ([[User talk:LaundryPizza03|<span style="color:#0d0">d</span>]][[Special:Contribs/LaundryPizza03|<span style="color:#0bf">c̄</span>]]) 06:31, 17 March 2026 (UTC)
:::No.  The reason you're not getting results is that you forked from one of the versions of this query where the pattern I was matching didn't have to worry about non-Latin characters - which is admittedly most of them - so you'll have to explicitly convert page_title and ar_title to utf8.  [[quarry:query/84985]] was the first I found that does that, though most of the rest of it is outdated.  And of course it was slow; not only does it have to do full table scans of both page and archive, both your regexes are catastrophically slow - the ending .* will be optimized away, but the initial \P{L}* likely won't be, and removing both won't change what they match. —[[User:Cryptic|Cryptic]] 07:05, 17 March 2026 (UTC)
::::Also, if you just want mainspace matches, or mainspace and category and template or whatever, say so - page_namespace = 0 or page_namespace IN (0, 10, 14) will be (partially) indexed, page_namespace NOT IN (1,2,3,4,5,6,7) tends not to be; and while a single blacklist regex has to match a candidate by itself, which is why they're written in pairs like (Devanagari character eventually followed by non-Devanagari letter) or (non-Devanagari letter eventually followed by Devanagari character), these queries don't: you're way better off with matching both "Devanagari character" and "non-Devanagari letter".  <syntaxhighlight lang=sql inline>SELECT COUNT(*) FROM page WHERE page_namespace=0 AND CONVERT(page_title USING utf8) RLIKE '\\p{Devanagari}' AND CONVERT(page_title USING utf8) RLIKE '[^\\p{Devanagari}\\P{L}]';</syntaxhighlight> completed in 20 seconds, and the version for archive in 39. —[[User:Cryptic|Cryptic]] 07:42, 17 March 2026 (UTC)
:::::This returned lots of false positives with {{unichar|02BC}}, which is used in the [[Boro language (India)|Boro]] and [[Dogri language]]s, so I had to exclude that as well. The completed query for article and draft space can be found at [[quarry:query/103274]]. –[[User:LaundryPizza03|<b style="color:#77b">Laundry</b><b style="color:#fb0">Pizza</b><b style="color:#b00">03</b>]] ([[User talk:LaundryPizza03|<span style="color:#0d0">d</span>]][[Special:Contribs/LaundryPizza03|<span style="color:#0bf">c̄</span>]]) 08:07, 17 March 2026 (UTC)