View Issue Details

Jump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0006282OXID eShop (all versions)1.03. Basket, checkout processpublic2015-12-07 13:552015-12-07 14:50
Reportermatths Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionduplicate 
Platformx86_64OSUbuntu LinuxOS Version14.04
Product Version4.9.4 / 5.2.4 
Summary0006282: Filter SQL (oxDeliverySetList) doesn't work for MySQL 5.6 and OXID 5.2.4
DescriptionIn step payment within the OXID checkout process, the following SQL query from class oxDeliverySetList, method _getFilterSelect doesn't give the correct result using OXID 5.2.4 and MySQL 5.6

In the checkout one get the message (german): ""Keine Versandarten gefunden. Bitte kontaktieren Sie uns telefonisch oder per E-Mail!""


select oxv_oxdeliveryset_3_de.* from oxv_oxdeliveryset_3_de where ( oxv_oxdeliveryset_3_de.oxactive = 1 or ( oxv_oxdeliveryset_3_de.oxactivefrom < '2015-12-07 11:39:41' and oxv_oxdeliveryset_3_de.oxactiveto > '2015-12-07 11:39:41' ) ) and (
            select
                if(EXISTS(select 1 from oxobject2delivery, oxv_oxcountry_de where oxv_oxcountry_de.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelset' LIMIT 1),
                    EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelset' and oxobject2delivery.OXOBJECTID='a7c40f631fc920687.20179984'),
                    1) &&
                if(EXISTS(select 1 from oxobject2delivery, oxuser where oxuser.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelsetu' LIMIT 1),
                    EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelsetu' and oxobject2delivery.OXOBJECTID='b33570466ec1d89a72d2055d6bcc77e0'),
                    1) &&
                if(EXISTS(select 1 from oxobject2delivery, oxv_oxgroups_de where oxv_oxgroups_de.oxid=oxobject2delivery.oxobjectid and oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelsetg' LIMIT 1),
                    EXISTS(select oxobject2delivery.oxid from oxobject2delivery where oxobject2delivery.oxdeliveryid=oxv_oxdeliveryset_3_de.OXID and oxobject2delivery.oxtype='oxdelsetg' and oxobject2delivery.OXOBJECTID in ('oxidnewcustomer') ),
                    1)
            ) order by oxv_oxdeliveryset_3_de.oxpos
Steps To Reproduce- Use OXID 5.2.4
- Use MySQL 5.6(.27) !
- Multishop

Define different payment methods, etc.
Go to the checkout process into the payment step.
Additional InformationWe are currently doing a release change from 5.0.7 to 5.2.4.
So we know, that the SQL query doesn't differ between those versions.
Of course, it uses views, which have a difference because of the change from shopincl/shopexcl to mapid and the x2shop tables.

So the otherwise identical SQL gives an empty result with OXID 5.2.4 and MySQL 5.6

OXID 5.0.7 - MySQL 5.5 --> works
OXID 5.0.7 - MySQL 5.6 --> works
OXID 5.2.4 - MySQL 5.5 --> works
OXID 5.2.4 - MySQL 5.6 --> doesn't work, empty result

We don't know the full explanation for that, but a workaround, which also doesn't break things for older versions of OXID or MySQL would be to remove the "SELECT" before all the if(EXISTS(),1) statements:

instead of:

and (
            select
                if(EXISTS


it shoud read:

and (
                if(EXISTS
TagsNo tags attached.
ThemeNot defined
BrowserNot defined
PHP Version5.4
Database Version5.6

Relationships

related to 0006247 resolvedanton.fedurtsya SQL Bug in oxDeliverySetList::_getFilterSelect() 

Activities

matths

2015-12-07 13:57

reporter   ~0011363

Last edited: 2015-12-07 13:57

 I know, it's quite close to issue 0006247, but our solution might be different, as countries are not important in our case.

QA

2015-12-07 14:47

administrator   ~0011364

This entry is probably a duplicate of 0006247 and go back to a bug in MySQL , that has already been reported: http://planet.oxidforge.org/2015/11/set-mysql-5-6-optimizer-setting-block_nested_loop-off-for-oxid-eshop-enterprise-edition.html