WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 4 years ago

Last modified 3 years ago

#23268 closed defect (bug) (fixed)

NOT EXISTS meta query with OR relation

Reported by: timfield Owned by: wonderboymusic
Milestone: 3.9 Priority: normal
Severity: normal Version: 3.5
Component: Query Keywords: has-patch needs-unit-tests meta-query
Focuses: Cc:

Description

With this meta query ( which is trying to exclude posts that have the app_exclude checkbox checked, without excluding posts that don't have it set at all )

                 $query['meta_query'] = array(
                    'relation' => 'OR',
                    array(
                        'key' => 'app_exclude',
                        'compare' => 'NOT EXISTS'
                    ),
                    array(
                        'key' => 'app_exclude',
                        'compare' => '!=',
                        'value' => '1'
                    ),
                );

I'd expect / hope to get this sql.

SELECT SQL_CALC_FOUND_ROWS
    wp_posts.ID
FROM
    wp_posts
        LEFT JOIN
    wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'app_exclude')
        INNER JOIN
    wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
    1 = 1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.post_id IS NULL 
    OR (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0 , 6

but I get this SQL

SELECT SQL_CALC_FOUND_ROWS
    wp_posts.ID
FROM
    wp_posts
        LEFT JOIN
    wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'app_exclude')
        INNER JOIN
    wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
    1 = 1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') 
    AND (wp_postmeta.post_id IS NULL AND (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0 , 6

Note the... (wp_postmeta.post_id IS NULL AND (mt1.meta_key = 'app_exclude' AND CAST(mt1.meta_value AS CHAR) != '1'))

Attachments (1)

23268.diff (577 bytes) - added by chrisguitarguy 5 years ago.
Make WP_Meta_Query respect NOT EXISTS queries without values

Download all attachments as: .zip

Change History (25)

#1 @hereswhatidid
5 years ago

#23294 was marked as a duplicate.

#2 @panbolec
5 years ago

It is possible to fool WordPress a little :) by asigning anything to 'value' of EXISTS/NOT EXISTS subquery. This workaround works (and may be even funny :-)

                 $query['meta_query'] = array(
                    'relation' => 'OR',
                    array(
                        'key' => 'app_exclude',
                        'compare' => 'NOT EXISTS'
                        'value'   => 'completely'
                    ),
                    array(
                        'key' => 'app_exclude',
                        'compare' => '!=',
                        'value' => '1'
                    ),
                );

The problem is in meta.php, lines:

		if ( 'OR' == $this->relation ) {
			foreach ( $this->queries as $k => $q ) {
				if ( ! isset( $q['value'] ) && ! empty( $q['key'] ) )
					$key_only_queries[$k] = $q;
				else
					$queries[$k] = $q;
			}
		} else {
			$queries = $this->queries;
		}

Following condition:

      if ( ! isset( $q['value'] ) && ! empty( $q['key'] ) )

is the source of the problem.

#4 @desrosj
5 years ago

  • Cc desrosj@… added

#5 @chrisguitarguy
5 years ago

I also ran into this issue. NOT EXISTS depends on a value when using the OR relation when it obviously shouldn't.

Some test cases: https://gist.github.com/chrisguitarguy/5116384

I was able to get this fixed by adding some more conditionals the lines in wp-includes/meta.php that panbolec pointed out. Diff attached.

@chrisguitarguy
5 years ago

Make WP_Meta_Query respect NOT EXISTS queries without values

#6 @chrisguitarguy
5 years ago

  • Cc chrisguitarguy added

#7 @SergeyBiryukov
5 years ago

  • Keywords has-patch added
  • Milestone changed from Awaiting Review to 3.6
  • Version set to 3.5

#8 @ejdanderson
5 years ago

  • Cc ejdanderson@… added

#9 @kovshenin
4 years ago

  • Cc kovshenin added

#10 @SergeyBiryukov
4 years ago

  • Summary changed from NOT EXISTS meta query with OR releation to NOT EXISTS meta query with OR relation

#11 @ryan
4 years ago

  • Milestone changed from 3.6 to Future Release

#12 @tomauger
4 years ago

  • Cc tomaugerdotcom@… added

This is silly. Codex updated for now.

Last edited 4 years ago by tomauger (previous) (diff)

#13 @helen
4 years ago

  • Keywords needs-unit-tests added

#14 @sboisvert
4 years ago

I just made a change to the codex since it implied that this effected EXIST as well as NOT EXIST. This doesn't seem to be the case on 3.6.1.

If you put in a value of any kind when using EXIST you get the behaviour of "=" not the behaviour defined previously defined as being the behaviour of the bug that affects NOT EXISTS.

so to clarify on 3.6.1
this:

array(
'key' => 'disclosure_regular_travel',
'compare' => 'EXISTS'
),

works as expected.
While the following:

array(
'key' => 'disclosure_regular_travel',
'value' => 'non empty value',
'compare' => 'EXISTS'
),

Causes behaviour similar to "=" compare. (Which is not in itself bad, just not what was written up in the codex and referenced this bug report)

#15 @wonderboymusic
4 years ago

  • Keywords meta-query added

#16 follow-up: @knutsp
4 years ago

  • Keywords changed from has-patch, needs-unit-tests, meta-query to has-patch needs-unit-tests meta-query

Ran into this bug today. Please fix for 3.9.

#17 in reply to: ↑ 16 @knutsp
4 years ago

No, I did not change keywords. Trac bug?

The from keywords are comma separated, the to keywords are not. Inconsistent. Well, this belongs in another trac.

Last edited 4 years ago by knutsp (previous) (diff)

#18 @SergeyBiryukov
4 years ago

  • Milestone changed from Future Release to 3.9

This ticket was mentioned in IRC in #wordpress-dev by ehg. View the logs.


4 years ago

#20 @nacin
4 years ago

Let's make sure we get this fixed for 3.9. It results in a funny hack when using meta queries with custom headers. See #21785.

#21 @wonderboymusic
4 years ago

  • Owner set to wonderboymusic
  • Resolution set to fixed
  • Status changed from new to closed

In 27689:

When using meta_query in a WP_Query, passing NOT EXISTS or '' to compare should not require value to be set. The resulting SQL should then produce the appropriate OR clause for existence of non-existence after passing the query to the $key_only_queries stack internally.

Adds unit tests.

Props chrisguitarguy, for the original patch.
Fixes #23268.

#22 @o----o
3 years ago

the problem persists in WP.4.1

	$args = array(
		'post_type' => $page_type,
		'posts_per_page' => -1,
		'author' => $user_ID,
		'post_status' => array('publish', 'draft'),
		'relation' => 'OR',
		'meta_query' => array(
	        array(
	            'key' => 'event_boolean',
	            'compare' => 'NOT EXISTS',
	            'value'   => 'completely'
	        ),
	        array(
	            'key' => 'event_boolean',
	            'compare' => '!=',
	            'value' => '1'
	        ),
        )
	);

one or the other meta comparison returns result, but when I use both no result is returned at all.
Truth is that the value for "NON EXISTS" is not needed but pairing the meta arrays cancel out the functionality.
(in my case I have one post with "event_boolean" empty, and the other with 0, so this should return 2 posts but none is returned)

Last edited 3 years ago by o----o (previous) (diff)

#23 @boonebgorges
3 years ago

@o----o - At a glance, it looks like the problem is that your 'relation' => 'OR' parameter is not within the meta_query like it should be, which would make the query relation default to AND. Try moving it inside of the meta_query array.

If you continue to have problems, please open a new ticket with a reference back to this one - we like to avoid reopening tickets that have been fixed against a closed milestone.

#24 @o----o
3 years ago

hell yea,
sorry and thanks

Note: See TracTickets for help on using tickets.