PS 1.7 catalog rules work only under 1000 articles

[cippoco]

Dear all, i've encounter this trouble on ps 1.7.1.1, when products in catalog exceed around 1000 reference (one website 1000, second 1002 and third 1004) counted in catalog-->products every catalog rules stop to work, i've installed 3 prestashop website to replicate the issue from clear installation to full configured and in every one is the same, when i exceed the 1004 reference the SQL query retrieve the specific_price_rule change and the new one return anything.

 

Here the query executed under 1000 articles differences is on the 'id_product':

 

SELECT SQL_NO_CACHE *, ( IF (`id_group` = 1, 2, 0) +  IF (`id_country` = 10, 4, 0) +  IF (`id_currency` = 1, 8, 0) +  IF (`id_shop` = 1, 16, 0) +  IF (`id_customer` = 0, 32, 0)) AS `score`
FROM `ps_specific_price`
WHERE
`id_shop` IN (0, 1) AND
`id_currency` IN (0, 1) AND
`id_country` IN (0, 10) AND
`id_group` IN (0, 1) AND `id_product` IN (0, 852) AND `id_customer` = 0 AND `id_product_attribute` = 0  AND (`from` = '0000-00-00 00:00:00' OR '2017-05-09 00:00:00' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2017-05-09 00:00:00' <= `to`)
AND IF(`from_quantity` > 1, `from_quantity`, 0) <= 1 ORDER BY `id_product_attribute` DESC, `id_cart` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC, `to` DESC, `from` DESC LIMIT 1

 

SELECT *, ( IF (`id_group` = XX, XX, XX) +  IF (`id_country` = XX, XX, XX) +  IF (`id_currency` = XX, XX, XX) +  IF (`id_shop` = XX, XX, XX) +  IF (`id_customer` = XX, XX, XX)) AS `score`
FROM `ps_specific_price`
WHERE
                `id_shop` IN (XX, XX) AND
                `id_currency` IN (XX, XX) AND
                `id_country` IN (XX, XX) AND
                `id_group` IN (XX, XX) AND `id_product` IN (XX, XX) AND `id_customer` = XX AND `id_product_attribute` = XX  AND (`from` = 'XX-XX-XX XX:XX:XX' OR 'XX-XX-XX XX:XX:XX' >= `from`) AND (`to` = 'XX-XX-XX XX:XX:XX' OR 'XX-XX-XX XX:XX:XX' <= `to`)
AND IF(`from_quantity` > XX, `from_quantity`, XX) <= XX ORDER BY `id_product_attribute` DESC, `id_cart` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC, `to` DESC, `from` DESC LIMIT XX

 

Instead this one is the query executed over 1000 articles:

 

SELECT SQL_NO_CACHE *, ( IF (`id_group` = 1, 2, 0) +  IF (`id_country` = 10, 4, 0) +  IF (`id_currency` = 1, 8, 0) +  IF (`id_shop` = 1, 16, 0) +  IF (`id_customer` = 0, 32, 0)) AS `score`
FROM `ps_specific_price`
WHERE
`id_shop` IN (0, 1) AND
`id_currency` IN (0, 1) AND
`id_country` IN (0, 10) AND
`id_group` IN (0, 1) AND `id_product` = 0 AND `id_customer` = 0 AND `id_product_attribute` = 0  AND (`from` = '0000-00-00 00:00:00' OR '2017-05-09 00:00:00' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2017-05-09 00:00:00' <= `to`)
AND IF(`from_quantity` > 1, `from_quantity`, 0) <= 1 ORDER BY `id_product_attribute` DESC, `id_cart` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC, `to` DESC, `from` DESC LIMIT 1

 

SELECT *, ( IF (`id_group` = XX, XX, XX) +  IF (`id_country` = XX, XX, XX) +  IF (`id_currency` = XX, XX, XX) +  IF (`id_shop` = XX, XX, XX) +  IF (`id_customer` = XX, XX, XX)) AS `score`
FROM `ps_specific_price`
WHERE
                `id_shop` IN (XX, XX) AND
                `id_currency` IN (XX, XX) AND
                `id_country` IN (XX, XX) AND
                `id_group` IN (XX, XX) AND `id_product` = XX AND `id_customer` = XX AND `id_product_attribute` = XX  AND (`from` = 'XX-XX-XX XX:XX:XX' OR 'XX-XX-XX XX:XX:XX' >= `from`) AND (`to` = 'XX-XX-XX XX:XX:XX' OR 'XX-XX-XX XX:XX:XX' <= `to`)
AND IF(`from_quantity` > XX, `from_quantity`, XX) <= XX ORDER BY `id_product_attribute` DESC, `id_cart` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC, `to` DESC, `from` DESC LIMIT XX

 

Both queries are taken from /classes/SpecificPrice.php:362

 

 

 

Edited May 9, 2017 by cippoco (see edit history)

[cippoco]

So i've investigate more, the issue appear a real bug, in /classes/SpecificPrice.php:362 query is build dynamically trought the method: 

public static function getSpecificPrice($id_product, $id_shop, $id_currency, $id_country, $id_group, $quantity, 
$id_product_attribute = null, $id_customer = 0, $id_cart = 0, $real_quantity = 0)

It call, to build part of the query, the function:

protected static function computeExtraConditions($id_product, $id_product_attribute, $id_customer, $id_cart, $beginning = null, $ending = null)

Which in turn, call:

protected static function filterOutField($field_name, $field_value, $threshold = 1000)

Here is the method limited to 1000 articles, for now i've applied a temporary patch raising to 100000 the limit, but it need to investigate further and understand why this method falls compiling the query. I would like to do, but unfortunally to do that it need a debugger attached, i'm not able to debug prestashop.

 

Waiting the answer from some gurus!

Edited May 10, 2017 by cippoco (see edit history)