SQL error due to ambiguous query

[RobSkilling]

We are getting the following error in our logs from Facetious queries:

Dec 3 02:43:46: WordPress database error Column 'post_date' in where clause is ambiguous for query 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_posts AS p2 ON (wp_posts.post_parent = p2.ID) WHERE 1=1 AND ( ( YEAR( post_date ) = 2012 AND MONTH( post_date ) = 10 ) ) AND ( wp_term_relationships.term_taxonomy_id IN (30) ) AND wp_posts.post_type IN ('post', 'report') AND ((wp_posts.post_status = 'publish') OR (wp_posts.post_status = 'inherit' AND (p2.post_status = 'publish'))) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10 made by require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts

Harry @ DXW suggested the error is generated by line 262 in template.php:

SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month

but looking again now I'm not sure that really matches what we're seeing in the error - it looks like its generated by the main query that Facetious constructs.

[johnbillion]

Hi Rob,

Could you let us know the URL and/or the list of query variables that are on the page when this error occurs please? You can use a debugging plugin such as Query Monitor or Debug Bar to see the current request's query variables.

Thanks!

[RobSkilling]

Hi John

This is the request in the log: GET /2012/10/?s&sector=local-services&pi_area&facetious_post_type HTTP/1.1

Which corresponds to this URL: http://www.nao.org.uk/search/year/2012/monthnum/10/sector/local-services/

Thanks

Rob

[johnbillion]

Thanks Rob, we'll investigate this soon.

[harry-m]

I think this is just that post_date is ambiguous - I think the query needs to be:

AND ( ( YEAR( wp_posts.post_date ) = 2012 AND MONTH( wp_posts.post_date ) = 10 ) ) 

or perhaps p2.post_date - without being familiar with the code, I'm not sure.

[johnbillion]

Turns out this is a bug in core: https://core.trac.wordpress.org/ticket/25775

Leaving this open until the core bug is fixed. It's slated for 3.9.