MusikAnimal / sql-optimizer

Run EXPLAIN on Wikimedia Toolforge SQL queries
https://sql-optimizer.toolforge.org
MIT License
8 stars 2 forks source link

"Query error: You have an error in your SQL syntax" #29

Open NovemLinguae opened 11 months ago

NovemLinguae commented 11 months ago

Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* FROM user_groups WHERE ug_user = user_id AND ug_group = 'sysop' ) AND NOT ...' at line 4

I get this error with the explainer tool, but not with Quarry. The fact that these aren't behaving 1:1 suggests something odd.

SELECT user_editcount, user_name, user_registration
FROM user
WHERE user_editcount > 10000
AND NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = user_id AND ug_group = 'sysop' )
AND NOT EXISTS (SELECT * FROM user_groups WHERE ug_user = user_id AND ug_group = 'bot' )
NovemLinguae commented 7 months ago

Another one:

Query error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'distinct log_actor, log_page, page_is_redirect, MONTH(log_timestamp) as month...' at line 3

 SELECT actor_name AS `Reviewer`, year, month, count(*) as reviews
       FROM (
          SELECT distinct log_actor, log_page, page_is_redirect, MONTH(log_timestamp) as month, YEAR(log_timestamp) as year
          FROM logging_userindex
          JOIN page ON page_title = log_title AND page_namespace = log_namespace
          AND 
          ((log_type = "patrol"  AND log_action = "patrol") OR
           (log_type = "pagetriage-curation" AND log_action in ("reviewed", "reviewed-article")))
          AND log_namespace = 0
        ) logtemp
        JOIN actor ON actor_id = log_actor
        JOIN user ON actor_user = user_id
        and logtemp.page_is_redirect = 0
       GROUP BY reviewer, year, month
having reviews >= 30;