{"action":"created","issue":{"url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104","repository_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts","labels_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/labels{/name}","comments_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/comments","events_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104/events","html_url":"https://github.com/cabrerahector/wordpress-popular-posts/issues/104","id":139360504,"number":104,"title":"only weekly and monthly caused unexpected results.","user":{"login":"keisukemizuno","id":1429563,"avatar_url":"https://avatars.githubusercontent.com/u/1429563?v=3","gravatar_id":"","url":"https://api.github.com/users/keisukemizuno","html_url":"https://github.com/keisukemizuno","followers_url":"https://api.github.com/users/keisukemizuno/followers","following_url":"https://api.github.com/users/keisukemizuno/following{/other_user}","gists_url":"https://api.github.com/users/keisukemizuno/gists{/gist_id}","starred_url":"https://api.github.com/users/keisukemizuno/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/keisukemizuno/subscriptions","organizations_url":"https://api.github.com/users/keisukemizuno/orgs","repos_url":"https://api.github.com/users/keisukemizuno/repos","events_url":"https://api.github.com/users/keisukemizuno/events{/privacy}","received_events_url":"https://api.github.com/users/keisukemizuno/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"cabrerahector","id":3186556,"avatar_url":"https://avatars.githubusercontent.com/u/3186556?v=3","gravatar_id":"","url":"https://api.github.com/users/cabrerahector","html_url":"https://github.com/cabrerahector","followers_url":"https://api.github.com/users/cabrerahector/followers","following_url":"https://api.github.com/users/cabrerahector/following{/other_user}","gists_url":"https://api.github.com/users/cabrerahector/gists{/gist_id}","starred_url":"https://api.github.com/users/cabrerahector/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cabrerahector/subscriptions","organizations_url":"https://api.github.com/users/cabrerahector/orgs","repos_url":"https://api.github.com/users/cabrerahector/repos","events_url":"https://api.github.com/users/cabrerahector/events{/privacy}","received_events_url":"https://api.github.com/users/cabrerahector/received_events","type":"User","site_admin":false},"milestone":null,"comments":0,"created_at":"2016-03-08T18:48:51Z","updated_at":"2016-03-08T19:18:01Z","closed_at":null,"body":"Recently,\r\nonly 'weekly' and 'monthly' options caused unexpected results.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 DAY)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nLIMIT 10;\r\n```\r\n\r\nThis query will return the expected results.\r\nThe results is ordered by 'pageviews DESC'.\r\nBut the following query will return the results ordered by 'id DESC'.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nLIMIT 10;\r\n```\r\n\r\n'INTERVAL 1 MONTH' is alse same.\r\nI tried to examine.\r\n\r\n```\r\nSELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC\r\n```\r\n\r\nThe above query will return the expected results.\r\nThe results is ordered by 'pageviews DESC'.\r\nBut,\r\n\r\n```\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\n```\r\nI don't know why, but\r\nby adding the above query, it will return the results ordered by 'id DESC'.\r\n\r\nOn the other hand,\r\n\r\n```\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\n```\r\n\r\nIn this case, it will return the results ordered by 'pageviews DESC'.\r\n\r\n\r\nOr, the following query will return the results ordered by 'pageviews DESC'.\r\n\r\n```\r\nSELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'\r\nFROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews\r\nFROM wp_popularpostssummary\r\nWHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)\r\nGROUP BY postid\r\nORDER BY pageviews DESC) v\r\nLEFT JOIN wp_posts p\r\nON v.postid = p.ID\r\nWHERE 1 = 1\r\nAND p.post_type = 'post'\r\nAND p.post_password = ''\r\nAND p.post_status = 'publish'\r\nORDER BY pageviews DESC\r\nLIMIT 10;\r\n```\r\n\r\n\r\n\r\nMaybe, around Jan 2016,\r\nboth 'weekly option' and 'monthly option' could have return the results ordered by 'pageviews DESC'.\r\n\r\nIf you know anything, please tell."},"comment":{"url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/comments/193926258","html_url":"https://github.com/cabrerahector/wordpress-popular-posts/issues/104#issuecomment-193926258","issue_url":"https://api.github.com/repos/cabrerahector/wordpress-popular-posts/issues/104","id":193926258,"user":{"login":"cabrerahector","id":3186556,"avatar_url":"https://avatars.githubusercontent.com/u/3186556?v=3","gravatar_id":"","url":"https://api.github.com/users/cabrerahector","html_url":"https://github.com/cabrerahector","followers_url":"https://api.github.com/users/cabrerahector/followers","following_url":"https://api.github.com/users/cabrerahector/following{/other_user}","gists_url":"https://api.github.com/users/cabrerahector/gists{/gist_id}","starred_url":"https://api.github.com/users/cabrerahector/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cabrerahector/subscriptions","organizations_url":"https://api.github.com/users/cabrerahector/orgs","repos_url":"https://api.github.com/users/cabrerahector/repos","events_url":"https://api.github.com/users/cabrerahector/events{/privacy}","received_events_url":"https://api.github.com/users/cabrerahector/received_events","type":"User","site_admin":false},"created_at":"2016-03-08T19:18:01Z","updated_at":"2016-03-08T19:18:01Z","body":"Hi Keisuke!\r\n\r\nWell, the part of the plugin that handles the BD queries hasn't been updated in a long time. No recent changes on WPP could have caused this behavior. Actually, the difference between the two first queries you posted is in the INTERVAL clause: DAY vs WEEK - the rest of the query is exactly the same, so the results should be as expected in both cases.\r\n\r\nOn the other hand,  I've seen this happening under some MySQL versions. Unfortunately, I haven't been able to identify which one(s) yet since I can't reproduce the issue locally.\r\n\r\nHave you moved your site recently to another server? Changed MySQL versions?"}}