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
