Column not found: 1054 Unknown column 'cat_index_position' in 'order clause'

Question

I am getting a SQL error on a few category pages after I upgraded to Magento 2.4.0.

I do not have any errors during re-indexing. Flat tables are also disabled. Also, there is no custom code for the category page. Only static content and Products associated to it. Please suggests what I can do ?

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_index_position' in 'order clause', query was: SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `rt`.`avg_percent`, IFNULL(review_summary.reviews_count, 0) AS `reviews_count`, IFNULL(review_summary.rating_summary, 0) AS `rating_summary`, `stock_status_index`.`stock_status` AS `is_salable` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1'
 INNER JOIN (SELECT `product`.`entity_id` AS `entity_pk_value`, `rt`.`avg_percent` FROM `catalog_product_entity` AS `product`
 LEFT JOIN (SELECT `rova`.`entity_pk_value`, avg(percent_approved) AS `avg_percent` FROM `rating_option_vote_aggregated` AS `rova` WHERE (rova.store_id = 1) GROUP BY `rova`.`entity_pk_value`) AS `rt` ON product.entity_id = rt.entity_pk_value GROUP BY `product`.`entity_id`) AS `rt` ON e.entity_id = rt.entity_pk_value
 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id WHERE (e.entity_id IN (3151, 3165, 3230, 3274, 3484, 3485, 3515, 3563, 3798, 3799, 3812, 3821, 3870, 3894, 3901, 3928, 3929, 3930, 3935, 3936, 3949, 3950, 3952, 3953, 3954, 3957, 3979, 3980, 5101, 5110, 5111, 5112, 5113, 5115, 5116, 5117)) ORDER BY FIELD(e.entity_id,3151,3165,3230,3274,3484,3485,3515,3563,3798,3799,3812,3821,3870,3894,3901,3928,3929,3930,3935,3936,3949,3950,3952,3953,3954,3957,3979,3980,5101,5110,5111,5112,5113,5115,5116,5117), `cat_index_position` ASC, `e`.`created_at` DESC

Answer 1

I did see this is an error and fixed it in the admin. Admin > Store > Configuration > Catalog > Catalog > Use Flat Catalog Product choose No and clear cache problem will be solved.

Answer 2

I have never face this issue.

But I found this blog might help you. Please have a look Link: https://prodevsblog.com/questions/293657/magento-column-not-found-1054-unknown-column-cat-index-position-in-order-cla/

Also in that one of them mention extension for fixing an issue Extension Link: https://github.com/Webtise/cat-index-position-fix

My Suggestion (Try it for once then go for extension)

rm -rf var/cache/* var/generation/* var/page_cache/* var/view_preprocessed/*

if production mode use Di compile command first

php bin/magento setup:upgrade
php bin/magento setup:static-content:deploy -f 
php bin/magento c:f
php bin/magento i:rei