Category page works fine for all categories except one

[safius]

That one category seems to fetch no products from the database, while all other categories fetch all their products as intended.

When I check from the back-office, I come across this weird behavior: If I look up a product that should be in that category and go to the Associations tab, I can see that it indeed belongs to the category in question.
If, however, I look up the category from the Catalog>Categories page, it brings up no products associated with it.

 

I checked the database directly and I see nothing obviously off. This is a record that DOES NOT show up (top), versus one that DOES show up (from a different category): 

 

 

(cont'd)

 

(cont'd)

 

(cont'd)

 

(cont'd)

 

(cont'd)

 

(cont'd)

 

 

I tried to output the sql query executed in Category::getProducts() and run it independently in my database console, and it fetches 1 product, as there is a "LIMIT 0,1" statement appended at the end of the query.

 

Three issues with that:

1. "LIMIT 0,1" appears in the query for every category, even the working ones that fetch more than 1 products.

2. "LIMIT 0,1" should still return 1 product (and it does when run directly in the console), which is never displayed.

3. If I remove the LIMIT statement, I get 29 results in the query result, but still nothing is displayed in the category page.

[musicmaster]

Are you running multishop? The product may not be present in the active shop when you look at the category.

[safius]

No, I just checked. Multistore is disabled and there is only one id_shop in the database.