WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

#29062 closed defect (bug) (fixed)

WP_Meta_Query with NOT EXISTS fails during multiple conditions

Reported by: johnrom Owned by: boonebgorges
Milestone: 4.1 Priority: normal
Severity: normal Version: 3.9.1
Component: Query Keywords:
Focuses: Cc:

Description

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

Attachments (1)

29062.test.patch (1.3 KB) - added by boonebgorges 5 years ago.

Download all attachments as: .zip

Change History (5)

#2 @boonebgorges
5 years ago

  • Keywords needs-patch needs-unit-tests added
  • Milestone changed from Awaiting Review to Future Release
  • Owner set to boonebgorges
  • Status changed from new to reviewing
  • Summary changed from WP_Query with NOT EXISTS fails during multiple conditions to WP_Meta_Query with NOT EXISTS fails during multiple conditions

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

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

#3 @boonebgorges
5 years ago

  • Keywords needs-patch needs-unit-tests removed
  • Milestone changed from Future Release to 4.1

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.

#4 @boonebgorges
5 years ago

  • Resolution set to fixed
  • Status changed from reviewing to closed

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.

Note: See TracTickets for help on using tickets.