View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0007148 | OXID eShop (all versions) | 1.02. Price calculations (discounts, coupons, additional costs etc.) | public | 2020-06-09 10:11 | 2020-06-12 12:39 |
| Reporter | fcos | Assigned To | |||
| Priority | normal | Severity | major | Reproducibility | unable to reproduce |
| Status | closed | Resolution | open | ||
| Summary | 0007148: Discount filter sql can cause mysql bug and return wrong results | ||||
| Description | Hi, it seems that the sql from DiscountList::_getFilterSelect() can cause an mysql bug. A customer had the problem that discounts didnt apply correctly and after some debugging i found that the sql query sometimes returns different results. I also tested the query directly in mysql so no php cache. After that i contaced the hoster and he thinks its the following bug: https://bugs.mysql.com/bug.php?id=80789 The hoster also found the problem, it seems that its the "select" directly before the if(EXISTS.. statement. Unfortunatly the ticket system dosnt allow to post the query, i always get "you are blocked" after sending the form. The "select" is not needed for the query and after fixing that i always get the wanted result. My guess is that the "select" puts the query in the mysql query cache and than messes up the result. I assume the problem can appear in all OXID Versions because the query is the same. MySQL: 5.7.30-1 Greetings Oliver | ||||
| Additional Information | Mentioned SQL: from DiscountList::_getFilterSelect() $sQ .= "and ( select if(EXISTS(select 1 from oxobject2discount, $sCountryTable where $sCountryTable.oxid=oxobject2discount.oxobjectid and oxobject2discount.OXDISCOUNTID=$sTable.OXID and oxobject2discount.oxtype='oxcountry' LIMIT 1), $sCountrySql, 1) && if(EXISTS(select 1 from oxobject2discount, $sUserTable where $sUserTable.oxid=oxobject2discount.oxobjectid and oxobject2discount.OXDISCOUNTID=$sTable.OXID and oxobject2discount.oxtype='oxuser' LIMIT 1), $sUserSql, 1) && if(EXISTS(select 1 from oxobject2discount, $sGroupTable where $sGroupTable.oxid=oxobject2discount.oxobjectid and oxobject2discount.OXDISCOUNTID=$sTable.OXID and oxobject2discount.oxtype='oxgroups' LIMIT 1), $sGroupSql, 1) )"; | ||||
| Tags | No tags attached. | ||||
| Theme | Not defined | ||||
| Browser | Not defined | ||||
| PHP Version | Not defined | ||||
| Database Version | MySQL 5.7 | ||||
|
|
Hi fcos thank you for your report! I'd like to ask you to send the further information like the mysql query, the exact shop version and also to name the line(s) in question to [email protected]. Furthemore a step by step guide would be really helpful to reproduce the case fast. I will try to add those details to this entry afterwards. Thank you for your help! Greetings -MK |
|
|
Hello, I analyzed your issue and have to tell I was unable to reproduce it. However, regarding your feedback per e-mail this is not a big surprise, right? Unfortunately we can only work on issues we are able to reproduce. I also checked the MySQL "bug" you've linked above. This is again only an issue report which was never acknowledged by the MySQL QA. No feedback from the reporter was provided since 2016. Combined with the fact that no other customer/user reported such issue to us for a long time now I can't acknowledge the issue. If the removing of "select" right before "if(EXISTS..." really fixes the issue on your one customer system withour any side effects, I recommend developing a simple module overriding the DiscountList class and modify the _getFilterSelect() method. [sp] |
