[wp-trac] [WordPress Trac] #59172: wp_query meta_query NOT EXISTS not creating correct sql query

[WordPress Trac]

#59172: wp_query meta_query NOT EXISTS not creating correct sql query
--------------------------+-----------------------------
 Reporter:  anteatersa    |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  6.3
 Severity:  normal        |   Keywords:  needs-patch
  Focuses:                |
--------------------------+-----------------------------
 With the following wp_query:

 {{{#!php
 <?php
         $args = array(
           "fields" => "ids",
           "post_type" => "shop_order",
           "post_status" => array('wc-processing'),
           "posts_per_page" => "-1",
           "offset" => "0",
           'meta_query' => array(
               'relation' => 'AND',
               array(
                 'key' => 'scg_is_glass',
                 'value' => "1",
               ),
               [
                 'relation' => 'OR',
                 [
                   'key' => 'scg_processed',
                   'value' => "0",
                   'compare' => '=',
                 ],
                 [
                   'key' => 'scg_processed',
                   'value' => "0",
                   'compare' => 'NOT EXISTS',
                 ],
               ]
           ),
           'date_query' => array(
               array(
                 'after' => date('Y-m-d', strtotime('-1 month')),
               ),
           ),
         );
 }}}

 The outputeed sql query is the following:

 SELECT wp_posts.ID
 FROM wp_posts
 LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
 LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
 LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id AND
 mt2.meta_key = 'scg_processed' )
 WHERE 1=1
 AND (wp_posts.post_date > '2023-07-23 23:59:59')
 AND (
 ( wp_postmeta.meta_key = 'scg_is_glass' AND wp_postmeta.meta_value = '1' )
 AND (
 ( mt1.meta_key = 'scg_processed' AND mt1.meta_value = '0' )
 OR **mt2.post_id IS NULL**
 )
 )
 AND wp_posts.post_type = 'shop_order'
 AND ((wp_posts.post_status = 'wc-processing'))
 GROUP BY wp_posts.ID
 ORDER BY wp_posts.post_date DESC

 If you looked at the bolded (mt2.post_id IS NULL) above you would expect
 it to be:

 **( mt2.meta_key = 'scg_processed' AND mt2.meta_value IS NULL )**

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