Untitled query #38649

SQL

use commonswiki_p; SELECT log_id, log_page, log_title, rev_id, rev_page
FROM logging
JOIN revision ON log_page = rev_page
JOIN slots ON rev_id = slot_revision_id
-- this log entry restores a file
WHERE log_type = 'delete'
AND log_action = 'restore'
AND log_namespace = 6
-- and there is a corresponding revision, predating the restoration, that already had a mediainfo slot
AND slot_role_id = (SELECT role_id FROM slot_roles WHERE role_name = 'mediainfo')
AND rev_timestamp < log_timestamp
-- captions were introduced in January 2019, so we can skip all earlier revisions and log entries
AND rev_timestamp > 20190101000000
AND log_timestamp > 20190101000000
-- the restoration did not reuse the page ID (which we get from a corresponding deletion)
AND log_page != (SELECT logdel.log_page FROM logging AS logdel WHERE logdel.log_type = 'delete' AND logdel.log_action = 'delete' AND logdel.log_namespace = 6 AND logdel.log_title = logging.log_title LIMIT 1)
LIMIT 10;
SQL

Checking query status...
