[wp-trac] [WordPress Trac] #36169: Incorrect results when combining a NOT EXISTS meta query with a comparison.

[WordPress Trac]

#36169: Incorrect results when combining a NOT EXISTS meta query with a comparison.
--------------------------+-----------------------------
 Reporter:  mikejolley    |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:  4.4.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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.

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