[5.x]: Pricing catalog generation fails with SQL 1066 due to duplicate alias sitestores (Commerce 5.4.9)

[romainpoirier]

What happened?

Description

With the following setup:

• craftcms/cms: 5.8.19
• craftcms/commerce: 5.4.9
• craftcms/feed-me: 6.11.0

The queue job “Generating catalog pricing data” fails with:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'sitestores'

The generated subquery contains the same LEFT JOINs twice with the same aliases (sitestores and purchasables_stores), which triggers the error.

Representative excerpt (trimmed for brevity):

SELECT `elements`.`id`
FROM (
  SELECT ...
  FROM `elements` `elements`
  INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
  LEFT JOIN `commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
  LEFT JOIN `commerce_purchasables_stores` `purchasables_stores`
    ON `purchasables_stores`.`storeId` = `sitestores`.`storeId`
   AND `purchasables_stores`.`purchasableId` = `elements`.`id`
  -- duplicated joins with identical aliases below
  LEFT JOIN `commerce_site_stores` `sitestores` ON `elements_sites`.`siteId` = `sitestores`.`siteId`
  LEFT JOIN `commerce_purchasables_stores` `purchasables_stores`
    ON `purchasables_stores`.`storeId` = `sitestores`.`storeId`
   AND `purchasables_stores`.`purchasableId` = `elements`.`id`
  WHERE (`purchasables_stores`.`promotable` = TRUE) AND ...
) `subquery`

Steps to reproduce

1. Install Craft CMS 5.8.19, Craft Commerce 5.4.9, and Feed Me 6.11.0.

2. Use a multi-site setup with at least one Store and its Site Stores; have standard purchasables (variants).

3. Trigger pricing catalog generation:

   • via queue (e.g., after a Feed Me import or product changes), or
   • via CLI: php craft commerce/pricing-catalog/generate

4. Observe the job failure with SQL 1066.

Expected behavior

The “Generating catalog pricing data” job completes successfully and does not add duplicate JOINs/aliases.

Actual behavior

The job fails with:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'sitestores'

Inspection of the SQL shows two identical LEFT JOINs to commerce_site_stores and commerce_purchasables_stores using the same aliases (sitestores, purchasables_stores).

Craft CMS version

5.8.19

Craft Commerce version

5.4.9

PHP version

8.2.23

Operating system and version

No response

Database type and version

MySQL 8.0.43

Image driver and version

No response

Installed plugins and versions

[linear]

COM-499

[nfourtythree]

Hi @romainpoirier

Thank you for your message and bringing this to our attention.

Would it be possible to send a backup of your DB to support@craftcms.com (referencing this issue).

This way we can investigate this further.

Thanks!

[romainpoirier]

This error likely stems from the other request « SQL Error in CatalogPricingRule due to duplicate sitestores alias in query » sent via email. My colleague Elodie has already provided you with the database via email. Thanks!

[nfourtythree]

Hi @romainpoirier

Thank you for your message, we followed this up in a support ticket but have yet to hear back.

Is this issue still ongoing?

Thanks!

[romainpoirier]

Hi, unfortunately the issue is still occurring, even after the latest update of Commerce.
If you’re unable to reproduce it, could you please confirm that you’ve tested using Elodie’s database?

[nfourtythree]

Hi @romainpoirier

Correct this was done with the DB from the support ticket. As per the reply we tested it with a fresh install of Craft and Commerce with no plugins installed and were unable to see the issue described under normal operation.

We are obviously keen to get things resolved so if you can provide steps to replicate the issue with an environment as described above. Then we can take this and investigate further.

Thanks!