Magento 2.3.6 Layered Navigation Select Exception Error

Question

I am running Magento 2.3.6 with MariaDB 10.4.17 successfully, but am running into a problem when I use layered navigation and use a filter that is not a boolean filter.

It seems to be caused by the search creating a temp table and then when inserting into that temp table an "unknown column" error comes up.

The error occurs when running below query:

INSERT INTO `sk1search_tmp_5fd0fbe8de0532_40559134`
SELECT `main_select`.`entity_id`, SUM(score) AS `relevance`
FROM (
    SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0)) * 1) AS `score`
    FROM `sk1catalog_product_index_eav` AS `search_index`
    INNER JOIN `sk1cataloginventory_stock_status` AS `stock_index`
        ON stock_index.product_id = search_index.entity_id
        AND `stock_index`.`website_id` = 0
        AND `stock_index`.`stock_id` = 1 
    INNER JOIN (
        SELECT `entity_id`, `store_id`, `attribute_id`, `value`
        FROM sk1catalog_product_index_eav
        WHERE `attribute_id`=137 AND `store_id`=1
        GROUP BY CONCAT(`entity_id`," ",`attribute_id`," ",`store_id`," ",`value`)
    ) AS `acidity_filter` 
        ON `search_index`.`entity_id` = `acidity_filter`.`entity_id`
        AND `acidity_filter`.`attribute_id` = 137
        AND `acidity_filter`.`store_id` = 1
    INNER JOIN `sk1cataloginventory_stock_status` AS `acidity_filter_stock_index`
        ON acidity_filter_stock_index.product_id = acidity_filter.source_id
        AND `acidity_filter_stock_index`.`website_id` = 0
        AND `acidity_filter_stock_index`.`stock_id` = 1
    INNER JOIN `sk1catalog_category_product_index_store1` AS `category_ids_index`
        ON search_index.entity_id = category_ids_index.product_id
        AND category_ids_index.store_id = '1'
    WHERE (search_index.store_id = '1')
        AND (`search_index`.`attribute_id` = 137)
        AND (`search_index`.`value` = '28')
        AND (category_ids_index.category_id in ('58'))
        AND (acidity_filter.value = '28')
) AS `main_select`
GROUP BY `entity_id`
ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 10000

Error message:

Column not found: 1054 Unknown column 'acidity_filter.source_id' in 'on clause',"

Any help or pointers on getting this fixed is appreciated

Answer 1

I had this issue, I solved it by removing the "patches/module-catalog-search-fix-insert-into-hanging.patch" patch from my composer.json. It's not compatible with 2.3.6