[wp-trac] [WordPress Trac] #29062: WP_Query with NOT EXISTS fails during multiple conditions

[WordPress Trac]

#29062: WP_Query with NOT EXISTS fails during multiple conditions
--------------------------+-----------------------------
 Reporter:  johnrom       |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  3.9.1
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 Using WordPress 3.9.1, when querying the meta for a value of '-1' or NOT
 EXISTS, the query fails to produce the proper SQL. When a specific post
 has no post meta at all associated with it, it is excluded completely from
 the INNER JOIN that results from these conditions.

 When NOT EXISTS is the only meta query, it works fine because there is
 only a LEFT JOIN.

 I think whenever a NOT EXISTS statement exists, all joins should be LEFT
 unless a default post meta is added on post creation.

 Meta Query:

 {{{
 array(3) {
   ["relation"]=>
   string(2) "OR"
   [0]=>
   array(3) {
     ["key"]=>
     string(24) "_meta_key"
     ["compare"]=>
     string(10) "NOT EXISTS"
     ["value"]=>
     string(10) "completely"
   }
   [1]=>
   array(2) {
     ["key"]=>
     string(24) "_meta_key"
     ["value"]=>
     string(2) "-1"
   }
 }
 }}}

 Produces:

 {{{
 SELECT SQL_CALC_FOUND_ROWS ms_3_posts.ID
   FROM ms_3_posts
     LEFT JOIN ms_3_postmeta
       ON (
         ms_3_posts.ID = ms_3_postmeta.post_id
         AND ms_3_postmeta.meta_key = '_meta_key'
       )
     INNER JOIN ms_3_postmeta AS mt1
       ON (
         ms_3_posts.ID = mt1.post_id
       )
   WHERE 1=1
     AND ms_3_posts.post_type = 'post'
     AND (
       ms_3_posts.post_status = 'publish'
       OR ms_3_posts.post_status = 'future'
       OR ms_3_posts.post_status = 'draft'
       OR ms_3_posts.post_status = 'pending'
       OR ms_3_posts.post_status = 'private'
     )
     AND (
       ms_3_postmeta.post_id IS NULL
       OR  (
         mt1.meta_key = '_meta_key'
         AND CAST(mt1.meta_value AS CHAR) = '-1')
       )
   GROUP BY ms_3_posts.ID
   ORDER BY ms_3_posts.post_date DESC
   LIMIT 0, 20;
 }}}

 Updated Working Query Switches INNER JOIN to LEFT JOIN:

 {{{
 SELECT SQL_CALC_FOUND_ROWS ms_3_posts.ID
   FROM ms_3_posts
     LEFT JOIN ms_3_postmeta
       ON (
         ms_3_posts.ID = ms_3_postmeta.post_id
         AND ms_3_postmeta.meta_key =  '_meta_key'
       )
     LEFT JOIN ms_3_postmeta AS mt1 ON ( ms_3_posts.ID = mt1.post_id )

   WHERE 1 =1
     AND ms_3_posts.post_type =  'post'
     AND (
       ms_3_posts.post_status =  'publish'
       OR ms_3_posts.post_status =  'future'
       OR ms_3_posts.post_status =  'draft'
       OR ms_3_posts.post_status =  'pending'
       OR ms_3_posts.post_status =  'private'
     )
     AND (
       ms_3_postmeta.post_id IS NULL
       OR (
         mt1.meta_key =  '_meta_key'
         AND CAST( mt1.meta_value AS CHAR ) =  '-1'
       )
     )
 GROUP BY ms_3_posts.ID
 ORDER BY ms_3_posts.post_date DESC
 LIMIT 0 , 20
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29062>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform