Magento 2.2.5和2.2.6的bug 产品设置special price又删除后价格排序有误

magento 2.2.5和2.2.6的bug 产品设置special price又删除后价格排序有误

一、问题描述：版本2.2.5和2.2.6均有此问题，为magento2的系统bug。为产品设置special price，比如0.5元，这个产品按价格由低到高排序时，排在首位；然后删去special price，保存，重建索引后，此产品显示的价格是正确的，但是即使有显示的价格比它还低的，按价格排序这个产品依然排在首位。

二、问题定位：
1、价格排序有问题，肯定是数据保存有问题。先在数据库里找与价格有关的数据表，catalog_product_index_price 和 catalog_category_entity_decimal 放在一起看，发现有问题的产品中，final_price max_price min_price的值都为0，改为和price的值一样时，价格排序正确。有此确定有问题的表出在 catalog_product_index_price。

2、确定产品保存时 catalog_product_index_price 这张表的final price的值为什么会被保存为0。产品保存与 vendor/magento/module-catalog/controller/adminhtml/product/save.php 这个文件有关，断点调试未能发现保存 catalog_product_index_price 的操作。后经同事提醒，产品保存后会进行 reindex 的操作，简单测试发现 catalog_product_index_price 表确实是 reindex 时保存。

3、reindex时，断点调试获取最终插入数据表的 sql语句。只要分析sql语句，就能确定问题的来源。reindex的起始点在文件 vendor/magento/module-indexer/console/command/indexerreindexcommand.php，但是indexer有很多，要准确找到价格的reindex操作，需要花费很大的努力和耐心。最终找到文件 vendor/magento/module-catalog/model/resourcemodel/product/indexer/price/simpleproductprice.php ，从中可以获取插入临时表的sql语句 $query 变量，复制sql语句，放到navicat中执行，可以发现要插入的数据中，final_price为0，下面主要分析这个sql语句。

4、如下面展示的sql语句所示，这个语句很长而且很复杂，要分析清楚它内部的逻辑结构，一是没有相应的分析复杂sql语句的经验而很是犯难，二是非常耗费时间。后经同事演示和提醒，发现分析这个sql语句也没有想象中那么难，因为再复杂的语句都是由基本语句组合而成，不过其中加上了多层嵌套，if语句的判断让它看起来非常复杂罢了，基本的分析方法还是：“分而治之，各个击破”，这句中国的成语包含了丰富的哲理智慧，我发现现实生活中的问题都可以用这套理论去解决。下面详细说明如何“分而治之”，又如何“各个击破”。
分而治之，就是把这段sql语句中无关的东西撇去，只关注核心的数据。final_price有问题，我们就看它的final_price是怎么查出来的，看黄色背景的部分。它最外面是一层ifnull判断，它的意思是第一个参数如果为真，那么返回它本身，否则返回第二个参数，final_price现在为0，那么就可以判断，第一个参数一定为false。但是第一个参数是很长一大段，我们又来仔细分析它，因为嵌套很多，不容易看清楚，我们这时要引入外面的工具，把它格式化，层次结构分明一点，并且可以看到括弧的开头和结束。phpstorm是一个很好用的工具，把这段代码都复制进去，并且格式化后再来分析。
各个击破，因为代码中涉及到值的对比和运算，所以我们要学会将这些不同的值打印显示出来，算出来后一个个拿来进行比较分析。打印（查询）出来也不难，模仿它本身就好了，用ifnull或if语句，就可以查询出来。

5、经过上面的分析，最终确定，问题出在一个叫 special_from_date 的产品属性上。当保存special_price 时，会将这个属性的值也保存起来，但是删除 special_price 时却没有删除，遗留的数据会影响上面sql语句的判断，从而导致final_price的值变为0。

6、定位了问题所在后，就是最终的解决。覆写 vendor/magento/module-catalog/observer/setspecialpricestartdate.php 文件的 execute方法，改为如下。它的作用就是，当有 special_price时，就保存special_from_date,没有special_price时，就删除speical_from_date。更新代码后，问题解决。

 1 /**
 2 * set the current date to special price from attribute if it empty
 3 *
 4 * if special price was deleted, special price from attribute will be deleted
 5 *
 6 * (important! otherwise indexer would be confused)
 7 *
 8 * @param \magento\framework\event\observer $observer
 9 * @return $this
10 */
11 public function execute(\magento\framework\event\observer $observer)
12 {
13 　　/** @var $product \magento\catalog\model\product */
14 　　$product = $observer->getevent()->getproduct();
15 　　if ($product->getspecialprice() && !$product->getspecialfromdate()) {
16 　　　　$product->setdata('special_from_date', $this->localedate->date());
17 　　} elseif (!$product->getspecialprice() && $product->getspecialfromdate()) {
18 　　　　$product->unsetdata('special_from_date');
19 　　}
20 
21 　　return $this;
22 }

 

三、总结：经此，定位问题，解决问题的能力又获得一丁点的提升。主要是学会了对复杂sql语句的初步分析，知道了ifnull、if、least函数的使用，and比or的优先级要高的事实。

sql语句：

 1 insert into `catalog_product_index_price_temp` 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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.value = 0, 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`
 2 cross join `customer_group` as `cg`
 3 inner join `catalog_product_website` as `pw` on pw.product_id = e.entity_id
 4 inner join `catalog_product_index_website` as `cwd` on pw.website_id = cwd.website_id
 5 left join `catalog_product_index_tier_price` as `tp` on tp.entity_id = e.entity_id and tp.customer_group_id = cg.customer_group_id and tp.website_id = pw.website_id
 6 left join `catalog_product_entity_tier_price` as `tier_price_1` on tier_price_1.row_id = e.row_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
 7 left join `catalog_product_entity_tier_price` as `tier_price_2` on tier_price_2.row_id = e.row_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
 8 left join `catalog_product_entity_tier_price` as `tier_price_3` on tier_price_3.row_id = e.row_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
 9 left join `catalog_product_entity_tier_price` as `tier_price_4` on tier_price_4.row_id = e.row_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
10 left join `catalog_product_entity_int` as `tad_tax_class_id` on tad_tax_class_id.row_id = e.row_id and tad_tax_class_id.attribute_id = 149 and tad_tax_class_id.store_id = 0
11 left join `catalog_product_entity_int` as `tas_tax_class_id` on tas_tax_class_id.row_id = e.row_id and tas_tax_class_id.attribute_id = 149 and tas_tax_class_id.store_id = cwd.default_store_id
12 inner join `catalog_product_entity_int` as `tad_status` on tad_status.row_id = e.row_id and tad_status.attribute_id = 97 and tad_status.store_id = 0
13 left join `catalog_product_entity_int` as `tas_status` on tas_status.row_id = e.row_id and tas_status.attribute_id = 97 and tas_status.store_id = cwd.default_store_id
14 left join `catalog_product_entity_decimal` as `ta_price` on ta_price.row_id = e.row_id and ta_price.attribute_id = 77 and ta_price.store_id = 0
15 left join `catalog_product_entity_decimal` as `ta_special_price` on ta_special_price.row_id = e.row_id and ta_special_price.attribute_id = 78 and ta_special_price.store_id = 0
16 left join `catalog_product_entity_datetime` as `tad_special_from_date` on tad_special_from_date.row_id = e.row_id and tad_special_from_date.attribute_id = 79 and tad_special_from_date.store_id = 0
17 left join `catalog_product_entity_datetime` as `tas_special_from_date` on tas_special_from_date.row_id = e.row_id and tas_special_from_date.attribute_id = 79 and tas_special_from_date.store_id = cwd.default_store_id
18 left join `catalog_product_entity_datetime` as `tad_special_to_date` on tad_special_to_date.row_id = e.row_id and tad_special_to_date.attribute_id = 80 and tad_special_to_date.store_id = 0
19 left join `catalog_product_entity_datetime` as `tas_special_to_date` on tas_special_to_date.row_id = e.row_id and tas_special_to_date.attribute_id = 80 and tas_special_to_date.store_id = cwd.default_store_id where ((if(ifnull(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value) = 1) and (e.type_id = 'simple') and (e.entity_id between 2 and 21)) and (e.created_in <= '1546224120') and (e.updated_in > '1546224120')