WP_Meta_Query with NOT EXISTS fails during multiple conditions

[johnrom]

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

[SergeyBiryukov]

Related: #23268

[boonebgorges]

I think whenever a NOT EXISTS statement exists, all joins should be LEFT

I think this is probably right. Somewhat related: #29447.

[boonebgorges]

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.

Confirmed. Note that this *only* happens when a given post has no metadata - you can see in 29062.test.patch​ that I needed to delete all the metadata for a post in order reproduce the issue.

LEFT JOIN performs more poorly at scale than INNER JOIN, but we're talking about a relative edge case: the only time when we need to change the JOIN is when the meta query has more than one clause, and when one of the clauses has compare=NOT EXISTS. And I think we all know deep down that WP_Meta_Query doesn't perform all that great at scale in general ;) So let's fix the bug.

[boonebgorges]

In 29890:

Use only LEFT JOINs when a meta_query contains a NOT EXISTS clause.

Mixing LEFT and INNER JOIN in these cases results in posts with no metadata
being improperly excluded from results.

Props johnrom.
Fixes #29062.