I've discovered that some of the SQL queries Magento is using to generate catalogrule discounts are completely nonsensical, leading to products receiving discounts even though they should not. This appears to only happen when flat tables are enabled, as a different process is used when flat tables aren't enabled.
Here are some examples of what I mean:
One such rule was configured to exclude various products based on their SKUs containing a specific string like so:
When Magento writes an SQL query to match against this condition, the query looks like:
SELECT 1, `cg`.`customer_group_id`, `p`.`entity_id`, 3, 0, 0, 'by_percent', 25, 0, 0, '', 0
FROM `catalog_product_entity` AS `p`
INNER JOIN `catalog_product_flat_1` AS `cpf` ON cpf.entity_id = p.entity_id
LEFT JOIN `catalog_category_product` AS `ccp` ON ccp.product_id = p.entity_id
LEFT JOIN `customer_group` AS `cg` ON cg.customer_group_id IN ( '0', '1', '2', '3' )
WHERE (( `cpf`.`sku` NOT LIKE 'this-should-have-percent-signs' ))
AND ( `p`.`entity_id` >= '1' )
AND ( `p`.`entity_id` < 1000001 )
Notice that even though the condition says "contains", Magento is not adding percent signs to indicate to MySQL that the string should be matched as such. When flat tables are not enabled, Magento does matching using the PHP stripos() function, so the flat-table and eav implementations are functionally different.
Another example can be found in the is-not-one-of condition:
In this case, the resulting WHERE condition looks like:
WHERE (( NOT Find_in_set('sku1', `cpf`.`sku`)
OR NOT Find_in_set('sku2', `cpf`.`sku`)
OR NOT Find_in_set('sku3', `cpf`.`sku`) ))
This method of comparison can never work because, if the SKU is sku1, it will match OR NOT Find_in_set('sku2', `cpf`.`sku`) even if it doesn't match NOT Find_in_set('sku1', `cpf`.`sku`). The end result is that all 3 of these SKUs will be matched by the rule, and therefore the exclusion will never happen.
This same issue is also present when excluding products by category ID. In that case, it might make sense to use find_in_set(), but there is no GROUP BY clause on the query, so that column will never be a set to begin with.
These proof-of-concept rules were tested against a fresh install of Magento 1.9.3.6, with no 3rd party modules installed.
Is there a solution to this? Perhaps some way to force Magento to use the non-SQL-driven process of applying rules, even when flat tables are enabled?
