WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

#36169 closed defect (bug) (invalid)

Incorrect results when combining a NOT EXISTS meta query with a comparison.

Reported by: mikejolley Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.4.2
Component: Query Keywords:
Focuses: Cc:
PR Number:

Description (last modified by dd32)

This is the meta_query being used in my code:

  ["meta_query"]=>
  array(3) {
    [0]=>
    array(3) {
      ["relation"]=>
      string(2) "OR"
      [0]=>
      array(3) {
        ["key"]=>
        string(27) "attribute_calligraphy-style"
        ["value"]=>
        array(2) {
          [0]=>
          string(0) ""
          [1]=>
          string(5) "Julia"
        }
        ["compare"]=>
        string(2) "IN"
      }
      [1]=>
      array(3) {
        ["key"]=>
        string(27) "attribute_calligraphy-style"
        ["compare"]=>
        string(10) "NOT EXISTS"
      }
    }
    [1]=>
    array(3) {
      ["relation"]=>
      string(2) "OR"
      [0]=>
      array(3) {
        ["key"]=>
        string(17) "attribute_product"
        ["value"]=>
        array(2) {
          [0]=>
          string(0) ""
          [1]=>
          string(33) "Outer + Inner + Return + Response"
        }
        ["compare"]=>
        string(2) "IN"
      }
      [1]=>
      array(3) {
        ["key"]=>
        string(17) "attribute_product"
        ["compare"]=>
        string(10) "NOT EXISTS"
      }
    }
    [2]=>
    array(3) {
      ["relation"]=>
      string(2) "OR"
      [0]=>
      array(3) {
        ["key"]=>
        string(13) "attribute_ink"
        ["value"]=>
        array(2) {
          [0]=>
          string(0) ""
          [1]=>
          string(38) "Custom (Including all metallic colors)"
        }
        ["compare"]=>
        string(2) "IN"
      }
      [1]=>
      array(3) {
        ["key"]=>
        string(13) "attribute_ink"
        ["compare"]=>
        string(10) "NOT EXISTS"
      }
    }


So for each meta key, e.g. attribute_calligraphy-style, we want to allow , some value, OR where the meta key does not exist.

Looking at the SQL I had this:

SELECT   wp_oiwx_posts.ID FROM wp_oiwx_posts
  LEFT JOIN wp_oiwx_postmeta ON ( wp_oiwx_posts.ID = wp_oiwx_postmeta.post_id )
  LEFT JOIN wp_oiwx_postmeta AS mt1 ON (wp_oiwx_posts.ID = mt1.post_id AND mt1.meta_key = 'attribute_calligraphy-style' )
  LEFT JOIN wp_oiwx_postmeta AS mt2 ON ( wp_oiwx_posts.ID = mt2.post_id )
  LEFT JOIN wp_oiwx_postmeta AS mt3 ON (wp_oiwx_posts.ID = mt3.post_id AND mt3.meta_key = 'attribute_product' )
  LEFT JOIN wp_oiwx_postmeta AS mt4 ON ( wp_oiwx_posts.ID = mt4.post_id )
  LEFT JOIN wp_oiwx_postmeta AS mt5 ON (wp_oiwx_posts.ID = mt5.post_id AND mt5.meta_key = 'attribute_ink' )
 WHERE 1=1  AND wp_oiwx_posts.post_parent = 4790  AND ( 
  ( 
    ( wp_oiwx_postmeta.meta_key = 'attribute_calligraphy-style' AND CAST(wp_oiwx_postmeta.meta_value AS CHAR) IN ('','Julia') ) 
    OR 
    mt1.post_id IS NULL
  ) 
  AND 
  ( 
    ( mt2.meta_key = 'attribute_product' AND CAST(mt2.meta_value AS CHAR) IN ('','Outer + Inner + Return + Response') ) 
    OR 
    mt3.post_id IS NULL
  ) 
  AND 
  ( 
    ( mt4.meta_key = 'attribute_ink' AND CAST(mt4.meta_value AS CHAR) IN ('','Custom (Including all metallic colors)') ) 
    OR 
    mt5.post_id IS NULL
  )
) AND wp_oiwx_posts.post_type = 'product_variation' AND ((wp_oiwx_posts.post_status = 'publish')) GROUP BY wp_oiwx_posts.ID ORDER BY wp_oiwx_posts.post_date DESC LIMIT 0, 5

Notice that the 2nd meta query uses mt3.post_id instead of mt2, and the 3rd query uses mt5.post_id instead of mt3.

I struggled to see what breaks this, but sufficed to say, no results come back for the above query. If I remove the 'not exists' clause, my query returns results without problem.

I'm going to find a different workaround for the above, but thought this should be logged in case the generated query is wrong.

Change History (3)

#1 @dd32
4 years ago

  • Component changed from General to Query
  • Description modified (diff)

Just broke up the JOIN syntax into multiple lines for readability.

#2 @mikejolley
4 years ago

Scrub this request. Although it could be optimised (by combining the two joins), just found it worked for me locally. It was causing this error on the user's site:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

#3 @boonebgorges
4 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

Thanks, @mikejolley. If there's a way to make NOT EXISTS clauses work properly without requiring their own table join, please open a separate ticket with details.

Note: See TracTickets for help on using tickets.