Maybe duplicate of other tickets but I couldn't found solutions in other questions.
I get no products in category in frontend, Products are in stocks and all other parameters are checked and they are perfect, After debugging I found that in following query if I manually remove following code
INNER JOIN
`search_tmp_5cd96fa8932f95_44809536` AS `search_result`
ON
e.entity_id = search_result.entity_id
then it is returning product list
Following is result of
$_productCollection = $block->getLoadedProductCollection();
echo $_productCollection->getSelect();
SELECT
`e`.*,
`cat_index`.`position` AS `cat_index_position`,
`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`,
`stock_status_index`.`stock_status` AS `is_salable`
FROM
`catalog_product_entity` AS `e`
INNER JOIN
`catalog_category_product_index_store1` AS `cat_index`
ON
cat_index.product_id = e.entity_id AND cat_index.store_id = 1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '85'
INNER JOIN
`catalog_product_index_price` AS `price_index`
ON
price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
INNER JOIN
`search_tmp_5cd96fa8932f95_44809536` AS `search_result`
ON
e.entity_id = search_result.entity_id
LEFT JOIN
`cataloginventory_stock_status` AS `stock_status_index`
ON
e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
WHERE
(e.created_in <= '1557643741') AND(e.updated_in > '1557643741')
ORDER BY
`e`.`entity_id`
DESC
,
`e`.`entity_id`
DESC
LIMIT 12
