Make WordPress Core

Opened 11 months ago

Last modified 11 months ago

#59172 new defect (bug)

wp_query meta_query NOT EXISTS not creating correct sql query

Reported by: anteatersa's profile anteatersa Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.3
Component: Database Keywords: needs-testing-info reporter-feedback
Focuses: Cc:

Description

With the following wp_query:

<?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 )

Change History (2)

This ticket was mentioned in Slack in #core-test by ironprogrammer. View the logs.


11 months ago

#2 @ironprogrammer
11 months ago

  • Keywords needs-testing-info reporter-feedback added; needs-patch removed

Welcome to Trac, @anteatersa, and thanks for the report!

This ticket would benefit from review by contributors familiar with meta_query to confirm the expected behavior.

Also, because the report appears to deal with a plugin, it would be helpful to have more details about the environment in question, as plugins may impact WP_Query processing. Please provide additional testing instructions to help in reproducing the issue. Thanks!

Note: See TracTickets for help on using tickets.