[wp-trac] [WordPress Trac] #49911: WP_Tax_Query generates incorrect SQL

[WordPress Trac]

#49911: WP_Tax_Query generates incorrect SQL
--------------------------+------------------------------
 Reporter:  janznordman   |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Query         |     Version:
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------
Description changed by SergeyBiryukov:

Old description:

> When calling WP_Query($arguments)::get_posts() like:
>
> $arguments = [
>   'category__in' = ['7','31','704'],
>   'post_type' => array('post', 'page'),
>   'post_status' => array('publish'),
>   'nopaging' => true
> ];
>
> 704 is the category for posts and 7, 31 for pages. The following query
> will be produced:
>
> SELECT bct_posts.* FROM bct_posts LEFT JOIN bct_term_relationships ON
> (bct_posts.ID = bct_term_relationships.object_id) LEFT JOIN
> bct_term_relationships AS tt1 ON (bct_posts.ID = tt1.object_id) LEFT JOIN
> bct_term_relationships AS tt2 ON (bct_posts.ID = tt2.object_id) WHERE 1=1
> AND ( bct_term_relationships.term_taxonomy_id IN (704) AND
> tt1.term_taxonomy_id IN (704) AND tt2.term_taxonomy_id IN (7,31,704) )
> AND bct_posts.post_type IN ('post', 'page') AND ((bct_posts.post_status =
> 'publish')) GROUP BY bct_posts.ID ORDER BY bct_posts.post_date DESC
>
> instead of
>
> SELECT bct_posts.* FROM bct_posts LEFT JOIN bct_term_relationships ON
> (bct_posts.ID = bct_term_relationships.object_id) WHERE 1=1 AND (
> bct_term_relationships.term_taxonomy_id IN (7,31,704) ) AND
> bct_posts.post_type IN ('post', 'page') AND ((bct_posts.post_status =
> 'publish')) GROUP BY bct_posts.ID ORDER BY bct_posts.post_date DESC
>

> The first query returns only posts, second one posts, and pages. Please
> note that categories and tags are enabled for pages.

New description:

 When calling WP_Query($arguments)::get_posts() like:
 {{{
 $arguments = [
   'category__in' = ['7','31','704'],
   'post_type' => array('post', 'page'),
   'post_status' => array('publish'),
   'nopaging' => true
 ];
 }}}
 704 is the category for posts and 7, 31 for pages. The following query
 will be produced:

 SELECT bct_posts.* FROM bct_posts LEFT JOIN bct_term_relationships ON
 (bct_posts.ID = bct_term_relationships.object_id) LEFT JOIN
 bct_term_relationships AS tt1 ON (bct_posts.ID = tt1.object_id) LEFT JOIN
 bct_term_relationships AS tt2 ON (bct_posts.ID = tt2.object_id) WHERE 1=1
 AND ( bct_term_relationships.term_taxonomy_id IN (704) AND
 tt1.term_taxonomy_id IN (704) AND tt2.term_taxonomy_id IN (7,31,704) ) AND
 bct_posts.post_type IN ('post', 'page') AND ((bct_posts.post_status =
 'publish')) GROUP BY bct_posts.ID ORDER BY bct_posts.post_date DESC

 instead of

 SELECT bct_posts.* FROM bct_posts LEFT JOIN bct_term_relationships ON
 (bct_posts.ID = bct_term_relationships.object_id) WHERE 1=1 AND (
 bct_term_relationships.term_taxonomy_id IN (7,31,704) ) AND
 bct_posts.post_type IN ('post', 'page') AND ((bct_posts.post_status =
 'publish')) GROUP BY bct_posts.ID ORDER BY bct_posts.post_date DESC


 The first query returns only posts, second one posts, and pages. Please
 note that categories and tags are enabled for pages.

--

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/49911#comment:2>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform