Open ioweb-gr opened 3 years ago
Hi @ioweb-gr. Thank you for your report. To help us process this issue please make sure that you provided sufficient information.
Please, add a comment to assign the issue: @magento I am working on this
After further investigating I notice that on the stock index for the specific store view, the configurable product's total salable quantity is incorrectly counted. It appears as instock
with salable quantity = 2 because the enabled/disabled status is not taken into account correctly.
Could you point me to the code responsible for building the index of the total salable quantity of the configurable products to debug it?
I ended up tracking it all the way to this function
\Magento\InventoryIndexer\Indexer\SelectBuilder::execute
Which results in the following SQL in my case
SELECT `source_item`.`sku`,
SUM(IF(source_item.status = 0, 0, quantity)) AS `quantity`,
IF((legacy_stock_item.use_config_backorders = 0 AND legacy_stock_item.backorders <> 0 AND
(legacy_stock_item.min_qty >= 0 OR legacy_stock_item.qty > legacy_stock_item.min_qty)) OR
((legacy_stock_item.use_config_manage_stock = 0 AND legacy_stock_item.manage_stock = 0)) OR
((legacy_stock_item.use_config_min_qty = 1 AND SUM(IF(source_item.status = 0, 0, quantity)) > 0) OR
(legacy_stock_item.use_config_min_qty = 0 AND
SUM(IF(source_item.status = 0, 0, quantity)) > legacy_stock_item.min_qty)) OR (product.sku IS NULL), 1,
0) AS `is_salable`
FROM `inventory_source_item` AS `source_item`
LEFT JOIN `catalog_product_entity` AS `product` ON product.sku = source_item.sku
LEFT JOIN `cataloginventory_stock_item` AS `legacy_stock_item`
ON product.entity_id = legacy_stock_item.product_id
WHERE (source_item.source_code IN
('glyfada', 'junior_running', 'kentriki_apothiki_melenikou', 'marousi', 'outlet_running', 'outlet_tennis'))
GROUP BY `sku`
Which seems to calculate the total quantity available of the product in the required sources but fails to take into account the store view and the simple product (child of configurable in this case) status in the store view.
If the focus here is just to mark the salable quantity and then filter it out somewhere else I'm not sure where this would happen. However it seems that the isSalable function will look in the inventory_stock_X tables and if the product is salable it will appear in the lists even though it shouldn't.
The end result on inventory_stock table is this one
But since
1041A004-011-45
and 1041A004-011-46
are not enabled in the store view, the product should actually be not salable.
Looking up at Configurable product indexer the query is
SELECT `parent_product_entity`.`sku`, SUM(stock.quantity) AS `quantity`, MAX(stock.is_salable) AS `is_salable`
FROM `inventory_stock_5` AS `stock`
INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.sku = stock.sku
INNER JOIN `catalog_product_super_link` AS `parent_link` ON parent_link.product_id = product_entity.entity_id
INNER JOIN `catalog_product_entity` AS `parent_product_entity`
ON parent_product_entity.entity_id = parent_link.parent_id
GROUP BY `parent_product_entity`.`sku`
Yielding the result as above
and again skipping the fact that the offending products have disabled status
+---------------+-----+--------+
|sku |value|store_id|
+---------------+-----+--------+
|1041A004-011-45|1 |0 |
|1041A004-011-45|2 |1 |
|1041A004-011-45|2 |4 |
|1041A004-011-45|2 |5 |
|1041A004-011-45|2 |6 |
|1041A004-011-45|2 |7 |
|1041A004-011-45|2 |8 |
|1041A004-011-45|2 |9 |
|1041A004-011-45|1 |10 |
|1041A004-011-46|1 |0 |
|1041A004-011-46|2 |1 |
|1041A004-011-46|2 |4 |
|1041A004-011-46|2 |5 |
|1041A004-011-46|2 |6 |
|1041A004-011-46|2 |7 |
|1041A004-011-46|2 |8 |
|1041A004-011-46|2 |9 |
|1041A004-011-46|1 |10 |
+---------------+-----+--------+
This seems related to #2413
I don't think this issue will ever get worked on. This is because a Stock in MSI is shared between different sales channel - and product status is a website scoped attribute. I have had to bring the logic for filtering out configurable products with disabled (but saleable) children when building. the query for PLP
Several years later we also see this exact bug. We managed to create a query that finds the problematic products.
SELECT
stock.sku,
stock.quantity,
parent_products.quantity,
stock.is_salable,
parent_products.is_salable
FROM
inventory_stock_1 AS stock
LEFT JOIN
(
SELECT `parent_product_entity`.`sku`, SUM(stock.quantity) AS `quantity`, MAX(stock.is_salable) AS `is_salable`
FROM `inventory_stock_1` AS `stock`
INNER JOIN `catalog_product_entity` AS `product_entity` ON product_entity.sku = stock.sku
INNER JOIN `catalog_product_super_link` AS `parent_link` ON parent_link.product_id = product_entity.entity_id
INNER JOIN `catalog_product_entity` AS `parent_product_entity`
ON parent_product_entity.entity_id = parent_link.parent_id
GROUP BY `parent_product_entity`.`sku`
) AS parent_products
ON
parent_products.sku = stock.sku
WHERE
parent_products.is_salable = 0 AND stock.is_salable=1
Preconditions (*)
Steps to reproduce (*)
Expected result (*)
Actual result (*)
e.g. in category
in product