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

[mikejolley]

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.

[dd32]

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

[mikejolley]

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

[boonebgorges]

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.