CoAuthors Plus potential incompatibility with WordPress 4.6

[kevinlisota]

Upgraded to WordPress 4.6 last night, and now our install is receiving the following error when viewing our author archive pages.

We use a custom query on our author archives like this:

$curauth = $coauthors_plus->get_coauthor_by( 'user_nicename', $author_name );

$featured_posts_author = new WP_Query(
array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'posts_per_page' => 4,
    'author_name' => $curauth->user_nicename,
    'tax_query' => [
        [
            'taxonomy' => 'display_options',
            'field' => 'slug',
            'terms' => 'featured'
        ]
    ],
    'orderby' => 'date',
    'order' => 'DESC'
));

This throws the following database error. Did not happen prior to WP 4.6. Any thoughts on why this is happening?

WordPress database error Not unique table/alias: 'wp_term_relationships' for query SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2569)\n) AND ((wp_posts.post_author = 9962 OR (wp_term_taxonomy.taxonomy = 'author' AND wp_term_taxonomy.term_id = '8109'))) AND wp_posts.post_type IN ('post') AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID HAVING MAX( IF ( wp_term_taxonomy.taxonomy = 'author', IF ( wp_term_taxonomy.term_id = '8109',2,1 ),0 ) ) <> 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 4 /*

[kevinlisota]

Specifically, I think the plugin is adding a duplicate INNER JOIN. There is code in the posts_join_filter function that checks for this, but appears to no longer work properly on WP 4.6

[mjangda]

Looks like this happens when you combine a taxonomy query (tag, category, etc.) and an author query.

The source of the issue looks like this switch to using LEFT JOIN instead of INNER JOIN: https://core.trac.wordpress.org/changeset/37184

Specifically, the issue is that we don't find an INNER JOIN in the query (like WordPress used to add) so we add a LEFT JOIN instead: https://github.com/Automattic/Co-Authors-Plus/blob/master/co-authors-plus.php#L613

For 4.6+, we'll likely need to add check for LEFT JOIN and bail if found. Even better might be if we just move to standard tax queries and let WordPress do the heavy lifting.

[pstonier]

I've recently run into the same issue. Our server was overloaded and support has said that we had a large number of SQL connections that were in sleep mode b/c the connections were not closed and they said that this was the query causing it.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE 1=1 AND wp_posts.ID NOT IN (46240,46241,45336) AND ((wp_posts.post_author = 37 OR (wp_term_taxonomy.taxonomy = 'author' AND wp_term_taxonomy.term_id = '620'))) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID HAVING MAX( IF ( wp_term_taxonomy.taxonomy = 'author', IF ( wp_term_taxonomy.term_id = '620',2,1 ),0 ) ) <> 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 10 SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (42931) ORDER BY meta_id ASC

[mjangda]

@kevinlisota Did the query ever work (i.e. return valid results) prior to 4.6? The duplicate LEFT JOIN is definitely an issue on 4.6 but in my testing on 4.5, I'm not able to get an author_name + tax_query to return any posts.

[kevinlisota]

It worked fine on our site prior to 4.6. The query we use in production is as I wrote above. However, there is one line of code missing at the top, which is global $coauthors_plus;

Obviously we have a custom taxonomy in that query, but you get the idea.

[mjangda]

Thanks for the quick follow-up. Two more questions:

• Which version of the plugin are you using?
• Any chance you can run either of the following queries to see what the result is:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2569)) AND ((wp_posts.post_author = 9962 OR (wp_term_taxonomy.taxonomy = 'author' AND wp_term_taxonomy.term_id = '8109'))) AND wp_posts.post_type IN ('post') AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID HAVING MAX( IF ( wp_term_taxonomy.taxonomy = 'author', IF ( wp_term_taxonomy.term_id = '8109',2,1 ),0 ) ) <> 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 4

and

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2569)) AND ((wp_posts.post_author = 9962 OR (wp_term_taxonomy.taxonomy = 'author' AND wp_term_taxonomy.term_id = '8109'))) AND wp_posts.post_type IN ('post') AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID HAVING MAX( IF ( wp_term_taxonomy.taxonomy = 'author', IF ( wp_term_taxonomy.term_id = '8109',2,1 ),0 ) ) <> 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 4

The first is what the query will look like under 4.6. Second is from 4.5.

[mjangda]

@pstonier Your issue seems unrelated. I'd recommend looking at ways to optimize the query like the suggestion at: #319

[kevinlisota]

@mjangda I ran both of those queries successfully in phpMyAdmin. Both queries returned 4 post IDs with no errors. We are using the latest version of CoAuthors Plus (3.2.1 I think)

[mjangda]

Thanks for confirming @kevinlisota. #376 should fix the "Not unique table/alias" errors.

However, in testing we've found a few other issues (although, these are not specific to 4.6 as we've replicated them in 4.5 as well):

• using author as an arg in WP_Query fails, because our regex doesn't match the query that is generated (post_author = vs post_author IN).
• author_name + tax_query will fail to return results if you have post_author lookups disabled or if the author is a coauthor on any matching posts. This is because we need to add JOINs with custom aliases to avoid re-using the JOIN from the tax_query.

[Oneill38]

Is there any update on when this will be completed? One of my team's sites uses this plugin w/4.6 and we're not having any luck.

[RoccoMarco]

Waiting for a fix to this

[trepmal]

There's a pending pull request (#381, needs a little love) that should solve the problem that arises when you mix author_name and tax_query.

[philipjohn]

#381 is merged, so I'm going to close this off