Make WordPress Core

Opened 11 years ago

Closed 10 years ago

Last modified 9 years ago

#23268 closed defect (bug) (fixed)

NOT EXISTS meta query with OR relation

Reported by: timfield's profile timfield Owned by: wonderboymusic's profile 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 11 years ago.
Make WP_Meta_Query respect NOT EXISTS queries without values

Download all attachments as: .zip

Change History (25)

#1 @hereswhatidid
11 years ago

#23294 was marked as a duplicate.

#2 @panbolec
11 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
11 years ago

  • Cc desrosj@… added

#5 @chrisguitarguy
11 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
11 years ago

Make WP_Meta_Query respect NOT EXISTS queries without values

#6 @chrisguitarguy
11 years ago

  • Cc chrisguitarguy added

#7 @SergeyBiryukov
11 years ago

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

#8 @ejdanderson
11 years ago

  • Cc ejdanderson@… added

#9 @kovshenin
11 years ago

  • Cc kovshenin added

#10 @SergeyBiryukov
11 years ago

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

#11 @ryan
11 years ago

  • Milestone changed from 3.6 to Future Release

#12 @tomauger
10 years ago

  • Cc tomaugerdotcom@… added

This is silly. Codex updated for now.

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

#13 @helen
10 years ago

  • Keywords needs-unit-tests added

#14 @sboisvert
10 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
10 years ago

  • Keywords meta-query added

#16 follow-up: @knutsp
10 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
10 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 10 years ago by knutsp (previous) (diff)

#18 @SergeyBiryukov
10 years ago

  • Milestone changed from Future Release to 3.9

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


10 years ago

#20 @nacin
10 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
10 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
9 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 9 years ago by o----o (previous) (diff)

#23 @boonebgorges
9 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
9 years ago

hell yea,
sorry and thanks

Note: See TracTickets for help on using tickets.