wp_list_categories throws SQL error if list is empty

[firebird75]

If via the args, we exclude the only category available in the list, then the SQL query will end up wrongly formated (empty : IN() statement).

See full query here :

SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN () AND post_type IN
('post') AND post_status = 'publish';

Sample php code :

<?php
$args = array('taxonomy' => 'category',
              'child_of' => $queried_object->category_parent,
              'depth' => 1,
              'exclude' => $queried_object->cat_ID,
              'echo' => 0,
              'hierarchical' => true,
              'hide_empty' => false,
              'show_count' => 1,
              'title_li' => '');                
echo wp_list_categories($args);

The solution would be to check if there are other categories to be displayed and if not, then just return the empty result.

[subrataemfluence]

I created a custom post type (movie) and custom taxonomy (genre, associated with movie). The taxonomy has only one term "Comedy" and it has no parent.

I also have a post under movie which where I have associated the only taxonomy term.

Here is my code:

<?php
$args = array('taxonomy' => 'movies_taxonomy',
    'depth' => 1,
    'exclude' => 16,
    'echo' => 0,
    'hierarchical' => true,
    'hide_empty' => false,
    'show_count' => 1,
    'title_li' => '');

echo wp_list_categories($args);
global $wpdb;
echo $wpdb->last_query;

As expected wp_list_categories($args) does not come up with any taxonomy item since I have only one (ID = 16) and that has been excluded in the query.

And here is the output of $wpdb->last_query:

SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('genre') AND t.term_id NOT IN (16) ORDER BY t.name ASC, which is different from yours.

Can you please tell me the steps to reproduce?

[firebird75]

Wow, what a great and fast reply!!!

The query you are showing up is the second one. There is one before that. You won't be able to see it with the last_query. You need to install a plugin like blackbox to see all the queries and so the one throwing the error.

Thanks!

[subrataemfluence]

@firebird75 thank you for explaining how-to. I will install the plugin soon! Btw, would like to know what makes you feel my reply a great one... lol!

Replying to firebird75:

Wow, what a great and fast reply!!!

The query you are showing up is the second one. There is one before that. You won't be able to see it with the last_query. You need to install a plugin like blackbox to see all the queries and so the one throwing the error.

Thanks!

[firebird75]

@subrataemfluence Here are the reasons :

• you replied very quickly
• you understood the issue
• you were able to quickly attempt reproducing it

What else? :)

[subrataemfluence]

Hi, first of all thank you for your appreciation and of course driving me towards the Blackbox plugin! Its truly a handy debugging tool.

As far as the query output is concerned, I am still unable to get it similar to yours, even with the only movies_taxonomy term (ID = 16) excluded (this term has post count 0).

Here is the output in Blackbox console:

SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('movies_taxonomy') AND
t.term_id NOT IN (16) ORDER BY t.name ASC;

I have attached a screenshot.

However, there are two differences I have in my query compared to yours:

1. 'taxonomy' => 'movies_taxonomy' (you have 'category')
2. 'child_of' => 0

To my understanding, above will not make any difference in building the query, as the same function is being invoked. But if you think I am missing something or doing wrong, please help me with exact steps to follow.

Thank you!

Replying to firebird75:

@subrataemfluence Here are the reasons :

• you replied very quickly
• you understood the issue
• you were able to quickly attempt reproducing it

What else? :)

[subrataemfluence]

Query output is highlighted

[firebird75]

The query you have highlighted is not the same as the one causing the trouble. I have checked your screenshot and I can't see the same type of query into it. This is probably why you aren't seeing the issue.

Maybe try to make it hierarchical taxonomy and make the call at the deepest level as this is the only way I can see the issue.

Thanks again my friend!

[subrataemfluence]

Ok, I will try the way you described! :)

[boonebgorges]

Looking at the format of the query involved, it appear that it's coming from _pad_term_counts(): https://core.trac.wordpress.org/browser/tags/5.0.3/src/wp-includes/taxonomy.php?marks=3359#L3335

In this location, I'm only able to trigger the error by passing either an empty array or an array that only contains things that aren't WP_Term objects to _pad_term_counts(). _pad_term_counts(), for its part, is called just once in WP, by WP_Term_Query. It should never receive an empty array in this instance, since empty $terms array cause the method to return early. See https://core.trac.wordpress.org/browser/tags/5.0.3/src/wp-includes/class-wp-term-query.php?marks=699-702,716#L696 So it must be that $terms passed to this function are not WP_Term objects. Perhaps this is a result of a corrupted 'terms' cache?

I suspect that _pad_term_counts() is being called by