Taxonomy query matching specified term or NOT EXISTS does not return expected result

[crstauf]

Performing a query on a (custom) taxonomy looking for a specified term or no term specified (NOT EXISTS) does not return the expected results. I discovered when working on a custom post type, but I tested on the post type, and the bug persisted.

Steps to reproduce:

1. Register a custom taxonomy
2. Create two posts, one assigned a term within the custom taxonomy, the other not
3. Perform query on the posts, with the following tax_query array:

   $args['tax_query'] = array(
           'relation' => 'OR',
           array(
                   'taxonomy' => 'custom_tax',
                   'field' => 'slug',
                   'terms' => 'the_term',
           ),
           array(
                   'taxonomy' => 'custom_tax',
                   'operator' => 'NOT EXISTS',
           ),
   );
   

(If you want to get really funky, create another post with a post format; in my discovery moment it was 'audio,' but I suspect it wouldn't matter which format.)

This query selects posts that have the specified term, but not posts that do not have any term assigned (within custom_tax). If you created a post with a format, that post will be included in the results as well.

See also:
Ticket #29181
Changeset [29896] (fixes #29181)

[swissspidy]

I just wrote small test in 36343.diff​ to replicate this and I cannot see any unexpected behaviour (i.e. the test passes). When you look at the test, what kind of result would expect?

[crstauf]

Replying to swissspidy:

I just wrote small test in 36343.diff​ to replicate this and I cannot see any unexpected behaviour (i.e. the test passes). When you look at the test, what kind of result would expect?

Indeed, it seems you are correct. I am attempting to determine the difference between your test case and my case.

[crstauf]

Replying to crstauf:

Replying to swissspidy:

I just wrote small test in 36343.diff​ to replicate this and I cannot see any unexpected behaviour (i.e. the test passes). When you look at the test, what kind of result would expect?

Indeed, it seems you are correct. I am attempting to determine the difference between your test case and my case.

I'm at a loss...

Take a look (this is on the install where I first discovered the issue):
​https://cloudup.com/cBLNqU5N7zQ (the parenthesis at the top is the dump of the custom WP_Query $posts)
​https://cloudup.com/cWfZ5nRaRPw (file in mu-plugins)

• returned: 20, 24
• expected (correct behavior): 20 (Post 1), 24 (Post 3), and 27 (Post 4).

Now this, on an install setup 10 minutes ago:
​https://cloudup.com/c2HeJ1e5I_w
(same file in mu-plugins used)

• returned: 1, 4, 8, 10
• expected (given persistence of bug; incorrect behavior): 1 (Hello World), 4 (Post 1), 8 (Post 3)

The only thing I can figure is that there's something messy in the database, as both sites are running multisite, all plugins are (network) deactivated (other than the one running the test in wp-content/mu-plugins), and Twenty Fifteen activated.

Would something hinky in the database still qualify this as a bug?

[crstauf]

definitely something in the database: used the database of the install where i discovered the bug with the fresh install, and the issue appeared. will continue diagnosing and see what I find.

[crstauf]

@swissspidy your test is successful. however, when i switch the post_type to page or a CPT, it fails.

here's my unit test code: ​https://cloudup.com/ctswe2gWj1B

[swissspidy]

Regular posts have at least 1 assigned term on creation (the default category). Custom post types don't.

And since tax queries perform an INNER JOIN on the wptests_term_relationships table, posts without any assigned terms are not included in the result set.

Changing the tax query to use a LEFT JOIN fixes this problem without causing any of the other tests to fail. 36343.2.diff​ demonstrates this.

@boonebgorges Since you're the taxonomy wizard, what's your opinion on the patch?

[crstauf]

confirmed that 36343.2.diff resolves the unit tests failures, and also resolves the incorrect results on the install/setup where I discovered the bug.

[boonebgorges]

@crstauf Thanks very much for your help debugging this, and for verifying @swissspidy's suggested fix.

@swissspidy Your diagnosis sounds correct. #29062 was a very similar problem, fixed in a similar way in [29890]. My one thought is that perhaps we should only use LEFT JOIN when we know that NOT EXISTS is used in a given clause. INNER JOIN gives the MySQL optimizer more flexibility when deciding the order in which to read the tables, which could result in performance improvements in some cases. (Could someone with more knowledge than me of the optimizer internals verify this line of reasoning? cough @pento cough)

[pento]

Yes, INNER JOIN can be faster in some cases, if the optimiser thinks that reading from the second table first is a better query plan.

That said, the performance difference will either be negligible (in the case that the columns being joined on are properly indexed) or irrelevant (in the case that they're not properly indexed, the query might be optimised from "really slow" to "kinda slow").

I don't think the performance gain is worth adding the code complexity.

[boonebgorges]

Thanks, @pento!

[boonebgorges]

In 37184:

Use LEFT JOIN when building WP_Tax_Query SQL.

LEFT JOIN ensures that NOT EXISTS queries will not miss posts that have
no taxonomy data whatsoever.

Props swissspidy, crstauf.
Fixes #36343.