I have a problem when using multiple Inventory stocks.
In my Magento 2.4 (2.4.5-p1) instance, there are multiple websites that act as country stores. All countries except one (USA) are supplied from one inventory stock; in Magento this corresponds to the default source/stock. For the USA I created another source/stock and assigned the USA stock to the website USA.
When I add items to the shopping cart in the USA website, I get the following error message when I go to the shopping cart page:
Integrity constraint violation: 1052 Column ‘sku’ in order clause is
ambiguous, query was: SELECTe
.*,at_stock_status
.stock_status
,
stock_status_index
.is_salable
FROMcatalog_product_entity
ASe
LEFT JOINcataloginventory_stock_status
ASat_stock_status
ON
(at_stock_status.product_id
=e.entity_id) AND
(at_stock_status.stock_id=1) INNER JOINcatalog_product_entity
AS
product
ON product.entity_id = e.entity_id LEFT JOIN
inventory_stock_3
ASstock_status_index
ON product.sku =
stock_status_index.sku WHERE (e
.sku
IN(‘45000794’, ‘45000770’))
AND (at_stock_status.stock_status = 1) ORDER BY FIELD(sku,’45000794′,
‘45000770’)Exception #1 (PDOException): SQLSTATE[23000]: Integrity constraint
violation: 1052 Column ‘sku’ in order clause is ambiguousException #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity
constraint violation: 1052 Column ‘sku’ in order clause is ambiguous,
query was: SELECTe
.*,at_stock_status
.stock_status
,
stock_status_index
.is_salable
FROMcatalog_product_entity
ASe
LEFT JOINcataloginventory_stock_status
ASat_stock_status
ON
(at_stock_status.product_id
=e.entity_id) AND
(at_stock_status.stock_id=1) INNER JOINcatalog_product_entity
AS
product
ON product.entity_id = e.entity_id LEFT JOIN
inventory_stock_3
ASstock_status_index
ON product.sku =
stock_status_index.sku WHERE (e
.sku
IN(‘45000794’, ‘45000770’))
AND (at_stock_status.stock_status = 1) ORDER BY FIELD(sku,’45000794′,
‘45000770’)
In all other websites, the shopping cart works as expected. If I remove the assignment of the USA stock to the website and the default stock gets assigned to the USA website, the shopping cart works as well, so it seems that there is no general problem with the website, but must be related to the stock.
Is this a known bug, or could it be related to a faulty configuration? And more importantly, how can I solve the problem?