only weekly and monthly caused unexpected results.

[keisukemizuno]

Recently, only 'weekly' and 'monthly' options caused unexpected results.

SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'
FROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews
FROM wp_popularpostssummary
WHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 DAY)
GROUP BY postid
ORDER BY pageviews DESC) v
LEFT JOIN wp_posts p
ON v.postid = p.ID
WHERE 1 = 1
AND p.post_type = 'post'
AND p.post_password = ''
AND p.post_status = 'publish'
LIMIT 10;

This query will return the expected results. The results is ordered by 'pageviews DESC'. But the following query will return the results ordered by 'id DESC'.

SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'
FROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews
FROM wp_popularpostssummary
WHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)
GROUP BY postid
ORDER BY pageviews DESC) v
LEFT JOIN wp_posts p
ON v.postid = p.ID
WHERE 1 = 1
AND p.post_type = 'post'
AND p.post_password = ''
AND p.post_status = 'publish'
LIMIT 10;

'INTERVAL 1 MONTH' is alse same. I tried to examine.

SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews
FROM wp_popularpostssummary
WHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)
GROUP BY postid
ORDER BY pageviews DESC

The above query will return the expected results. The results is ordered by 'pageviews DESC'. But,

WHERE 1 = 1
AND p.post_type = 'post'
AND p.post_password = ''
AND p.post_status = 'publish'

I don't know why, but by adding the above query, it will return the results ordered by 'id DESC'.

On the other hand,

WHERE 1 = 1
AND p.post_type = 'post'
AND p.post_password = ''

In this case, it will return the results ordered by 'pageviews DESC'.

Or, the following query will return the results ordered by 'pageviews DESC'.

SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid', v.pageviews AS 'pageviews'
FROM (SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews
FROM wp_popularpostssummary
WHERE last_viewed > DATE_SUB('2016-03-09 02:55:42', INTERVAL 1 WEEK)
GROUP BY postid
ORDER BY pageviews DESC) v
LEFT JOIN wp_posts p
ON v.postid = p.ID
WHERE 1 = 1
AND p.post_type = 'post'
AND p.post_password = ''
AND p.post_status = 'publish'
ORDER BY pageviews DESC
LIMIT 10;

Maybe, around Jan 2016, both 'weekly option' and 'monthly option' could have return the results ordered by 'pageviews DESC'.

If you know anything, please tell.

[cabrerahector]

Hi Keisuke!

Well, 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.

On 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.

Have you moved your site recently to another server? Changed MySQL versions?

[keisukemizuno]

Hi cabrerahector!

Thank you for answering my questions. I totally agree.

Speaking of which, I recently changed MySQL versions from 5.1.x to 5.6.29!

[keisukemizuno]

ok, I resolved this problem.

wordpress-popular-posts.php L1652

I changed the following query from $from = "(SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews FROM {$prefix}summary WHERE last_viewed > DATE_SUB('{$now}', INTERVAL {$interval}) GROUP BY postid ORDER BY pageviews DESC) v LEFT JOIN {$wpdb->posts} p ON v.postid = p.ID"; to $from = "(SELECT postid, IFNULL(SUM(pageviews), 0) AS pageviews FROM {$prefix}summary WHERE last_viewed > DATE_SUB('{$now}', INTERVAL {$interval}) GROUP BY postid ORDER BY pageviews DESC) v STRAIGHT_JOIN {$wpdb->posts} p ON v.postid = p.ID";

STRAIGHT_JOIN can specify the joined order.

Maybe, MySQL version updating caused this problem. Thank you for answering my questions.

[cabrerahector]

Awesome, thanks for sharing!

I'll look into this as soon as possible. Stay tuned, as I might need your help with testing :)

[keisukemizuno]

Thank you for your time. Please contact me anytime!