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?

[irozum]

Triaged against trunk @ c5690fd7e8 (WordPress 7.1-beta3).

Steps taken: Created a parent category with one child, published a post in the child, then called wp_list_categories() with child_of => $parent, depth => 1, exclude => $child_id, hierarchical => true, hide_empty => false, show_count => 1 — the exact args from the original report.

Result: Still throws the identical malformed-SQL database error:

WordPress database error You have an error in your SQL syntax; ... near ') AND post_type IN (''post'') AND post_status = ''publish''' at line 1 for query
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'

Verdict: Still reproduces, confirmed live on trunk @ c5690fd7e8.

Full call stack: wp_list_categories → get_categories → get_terms → WP_Term_Query->query → WP_Term_Query->get_terms → _pad_term_counts. The bug is in _pad_term_counts() (wp-includes/taxonomy.php) — it builds term_taxonomy_id IN (...) from implode( ',', array_keys( $term_ids ) ) with no guard for $term_ids being empty. WP_Term_Query::get_terms() does have an empty( $terms ) early-return (class-wp-term-query.php), but that only catches the case where the initial SQL query returns nothing — here the initial query still returns the (soon-to-be-excluded) child term, and it's the later child_of/exclude processing that empties $term_objects before it reaches _pad_term_counts(). So the early-return added since doesn't cover this path.

The 9-year-old suggested fix ("check if there are other categories to be displayed and if not, just return the empty result") still applies — a one-line if ( empty( $term_ids ) ) { return; } at the top of _pad_term_counts() would close this.

Suggested keyword changes: remove reporter-feedback, add needs-patch

_pad_term_counts() (wp-includes/taxonomy.php) builds a term_taxonomy_id IN (...) SQL clause from implode( ',', array_keys( $term_ids ) ) with no guard for $term_ids being empty. When wp_list_categories() is called with child_of + exclude args that exclude every matching child category, the term list passed into _pad_term_counts() ends up empty, and the query becomes literally ... WHERE term_taxonomy_id IN () AND ... — a WordPress database error, visible to end users when WP_DEBUG_DISPLAY is on.

Repro:

$parent = wp_insert_term( 'Parent Cat', 'category' );
$child  = wp_insert_term( 'Only Child', 'category', array( 'parent' => $parent['term_id'] ) );
// ...a published post in $child...
wp_list_categories( array(
        'child_of'     => $parent['term_id'],
        'depth'        => 1,
        'exclude'      => $child['term_id'],
        'hierarchical' => true,
        'hide_empty'   => false,
        'show_count'   => 1,
) );

Fix is a one-line guard, matching the existing empty( $term_hier ) early-return already in the function:

if ( empty( $term_ids ) ) {
        return;
}

Added test_pad_counts_should_not_generate_a_query_error_when_child_of_and_exclude_empty_the_result() to Tests_Term_getTerms, reproducing the same underlying condition through a custom hierarchical taxonomy and get_terms() directly (the bug isn't category-specific — it's in _pad_term_counts(), which any hierarchical taxonomy with pad_counts goes through).

Verified the test is real regression coverage: reverted just the src/ change and confirmed the test fails with the exact malformed-SQL error, then restored the fix and confirmed green. Ran the full getTerms.php file (115 tests) and the whole taxonomy group (879 tests) after — no regressions. Lint and PHPStan clean.

This is the same root cause a WordPress Emeritus Committer (boonebgorges) already pointed at in the ticket's comment history — _pad_term_counts() — this PR implements the one-line fix the original reporter suggested nine years ago.

## Use of AI Tools

AI assistance: Yes
Tool(s): Claude Code
Model(s): Claude Opus 5
Used for: Root-cause tracing, implementation, test authoring, and mutation-testing verification. Reviewed by @irozum.