WordPress.org

Make WordPress Core

Opened 9 years ago

Closed 8 years ago

Last modified 7 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 9 years ago.
Make WP_Meta_Query respect NOT EXISTS queries without values

Download all attachments as: .zip

Change History (25)

#1 @hereswhatidid
9 years ago

#23294 was marked as a duplicate.

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

  • Cc desrosj@… added

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

Make WP_Meta_Query respect NOT EXISTS queries without values

#6 @chrisguitarguy
9 years ago

  • Cc chrisguitarguy added

#7 @SergeyBiryukov
9 years ago

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

#8 @ejdanderson
9 years ago

  • Cc ejdanderson@… added

#9 @kovshenin
8 years ago

  • Cc kovshenin added

#10 @SergeyBiryukov
8 years ago

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

#11 @ryan
8 years ago

  • Milestone changed from 3.6 to Future Release

#12 @tomauger
8 years ago

  • Cc tomaugerdotcom@… added

This is silly. Codex updated for now.

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

#13 @helen
8 years ago

  • Keywords needs-unit-tests added

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

  • Keywords meta-query added

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

#18 @SergeyBiryukov
8 years ago

  • Milestone changed from Future Release to 3.9

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


8 years ago

#20 @nacin
8 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
8 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
7 years ago

one more weirdness
this works

$query['meta_query'] = array(


                    'relation' => 'OR',
                    array(
                        'key' => 'app_exclude',
                        'compare' => 'NOT EXISTS'
                        'value'   => 'completely'
                    ),
                    array(
                        'key' => 'app_exclude',
                        'compare' => '!=',
                        'value' => '1'
                    ),
                );

this doesn't

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

NOT EXISTS has to be defined on first place, or you won't get the result
(WP 4.1)

Version 0, edited 7 years ago by o----o (next)

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

hell yea,
sorry and thanks

Note: See TracTickets for help on using tickets.