Recently I’ve been unable to reindex Product Price (catalog_product_price) on Magento 2.4.6. When I run ‘php bin/magento indexer:reindex catalog_product_price’ from the CLI, I get the following output:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND tad_special_to_date.store_id = 0
LEFT JOIN `catalog_product_entity` AS `...' at line 12, query was: INSERT INTO `catalog_product_index_price_temp` (`entity_id`, `customer_group_id`, `website_id`, `tax_class_id`, `price`, `final_price`, `min_price`, `max_price`, `tier_price`) SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `pw`.`website_id`, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS `tax_class_id`, IFNULL((ta_price.value), 0) AS `price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND (IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date) AND (IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date), ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `final_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND (IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date) AND (IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date), ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `min_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND (IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date) AND (IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date), ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `max_price`, IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.percentage_value IS NOT NULL, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0))) AS `tier_price` FROM `catalog_product_entity` AS `e`
LEFT JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id
LEFT JOIN `catalog_product_index_website` AS `cwd` ON pw.website_id = cwd.website_id
LEFT JOIN `catalog_product_entity_int` AS `tad_tax_class_id` ON tad_tax_class_id.entity_id = e.entity_id AND tad_tax_class_id.attribute_id = 121 AND tad_tax_class_id.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_tax_class_id` ON tas_tax_class_id.entity_id = e.entity_id AND tas_tax_class_id.attribute_id = 121 AND tas_tax_class_id.store_id = cwd.default_store_id
INNER JOIN `catalog_product_entity_int` AS `tad_status` ON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 96 AND tad_status.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_status` ON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 96 AND tas_status.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity_decimal` AS `ta_price` ON ta_price.entity_id = e.entity_id AND ta_price.attribute_id = 75 AND ta_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.entity_id = e.entity_id AND ta_special_price.attribute_id = 76 AND ta_special_price.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_from_date` ON tad_special_from_date.entity_id = e.entity_id AND tad_special_from_date.attribute_id = 77 AND tad_special_from_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_from_date` ON tas_special_from_date.entity_id = e.entity_id AND tas_special_from_date.attribute_id = 77 AND tas_special_from_date.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity` AS `tad_special_to_date` ON tad_special_to_date.entity_id = e.entity_id AND tad_special_to_date.attribute_id = AND tad_special_to_date.store_id = 0
LEFT JOIN `catalog_product_entity` AS `tas_special_to_date` ON tas_special_to_date.entity_id = e.entity_id AND tas_special_to_date.attribute_id = AND tas_special_to_date.store_id = cwd.default_store_id
CROSS JOIN `customer_group` AS `cg`
LEFT JOIN `customer_group_excluded_website` AS `cgw` ON cg.customer_group_id = cgw.customer_group_id AND pw.website_id = cgw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_1` ON tier_price_1.entity_id = e.entity_id AND tier_price_1.all_groups = 0 AND tier_price_1.customer_group_id = cg.customer_group_id AND tier_price_1.qty = 1 AND tier_price_1.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_3` ON tier_price_3.entity_id = e.entity_id AND tier_price_3.all_groups = 1 AND tier_price_3.customer_group_id = 0 AND tier_price_3.qty = 1 AND tier_price_3.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_4` ON tier_price_4.entity_id = e.entity_id AND tier_price_4.all_groups = 1 AND tier_price_4.customer_group_id = 0 AND tier_price_4.qty = 1 AND tier_price_4.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_2` ON tier_price_2.entity_id = e.entity_id AND tier_price_2.all_groups = 0 AND tier_price_2.customer_group_id = cg.customer_group_id AND tier_price_2.qty = 1 AND tier_price_2.website_id = pw.website_id WHERE (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value) = 1) AND (e.type_id = 'simple') AND (cgw.website_id IS NULL) AND (e.entity_id BETWEEN 24 AND 35744) AND (e.entity_id IN(24, 25, 28 [** truncated to fit**] ... 35742, 35743, 35744)) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `customer_group_id` = VALUES(`customer_group_id`), `website_id` = VALUES(`website_id`), `tax_class_id` = VALUES(`tax_class_id`), `price` = VALUES(`price`), `final_price` = VALUES(`final_price`), `min_price` = VALUES(`min_price`), `max_price` = VALUES(`max_price`), `tier_price` = VALUES(`tier_price`)
I have not recently migrated from a previous version.
Lines 12 and 13 show ‘tad_special_to_date.attribute_id’ and ‘tas_special_to_date.attribute_id’ but table catalog_product_entity doesn’t contain a column attribute_id. Also, there are only 25 products that have a row in catalog_product_entity_datetime with attribute_id = 77 (special_to_date).
How can I clear this error and be able to reindex Product Price again?