Date Parameters + Post Status = Invalid SQL

[restouffer]

When specifying date parameters in the query (year, month, day) and also specifying a post_status, the SQL generated causes the following error:

Column 'post_date' in where clause is ambiguous

This means that no results are returned, even if there are posts matching that date and post_status. The reason for ambiguity in the SQL is that specifying a post_status joins the wp_posts table to itself in order to look for children, and MySQL cannot tell which table to read post_date from.

The ambiguous part of the SQL is generated by WP_Date_Query, which is used to construct the WHERE clause when date parameters are specified, since it uses the column name without the table name (e.g. - "post_date" instead of "$wpdb->posts.post_date"). The WP_Date_Query class provides two ways of passing the column name, but currently WP_Query uses neither and relies on the default value. (wp-includes/query.php:2544)

If the full column definition was passed, it would also require a change to the WP_Date_Query::validate_column method, as currently it will not recognize the column with a table name and will change the full name back into "post_date".

With the validation changed (or removed), the call to the WP_Date_Query constructor could then look like:

$this->date_query = new WP_Date_Query( $q['date_query'], "$wpdb->posts.post_date" );

[danielbachhuber]

Duplicate of #25775.