cippoco Posted May 9, 2017 Share Posted May 9, 2017 (edited) 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) Link to comment Share on other sites More sharing options...
cippoco Posted May 10, 2017 Author Share Posted May 10, 2017 (edited) 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) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now