View Issue Details

IDProjectCategoryView StatusLast Update
0007148OXID eShop (all versions)1.02. Price calculations (discounts, coupons, additional costs etc.)public2020-06-12 12:39
Reporterfcos 
PrioritynormalSeveritymajorReproducibilityunable to reproduce
Status closedResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary0007148: Discount filter sql can cause mysql bug and return wrong results
DescriptionHi,

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 InformationMentioned 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)
    )";
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP VersionNot defined
Database VersionMySQL 5.7

Activities

QA

2020-06-09 11:27

administrator   ~0013253

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

QA

2020-06-12 12:38

administrator   ~0013254

Last edited: 2020-06-12 12:39

View 2 revisions

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]